wehub-resource-sync
8cb1f9f479
Publish SDK (PyPI) / publish (push) Has been cancelled
Publish SDK (npm) / publish (@aionui/officecli-sdk) (push) Has been cancelled
SDK smoke / smoke (windows-latest) (push) Has been cancelled
Publish SDK (npm) / publish (@officecli/officecli-sdk) (push) Has been cancelled
Publish SDK (npm) / publish (@officecli/sdk) (push) Has been cancelled
Publish SDK (npm) / publish (officecli-sdk) (push) Has been cancelled
SDK smoke / smoke (macos-latest) (push) Has been cancelled
SDK smoke / smoke (ubuntu-latest) (push) Has been cancelled
Skill parity / diff (push) Has been cancelled
311 lines
12 KiB
Python
311 lines
12 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Advanced Charts Showcase — generates charts-advanced.xlsx exercising the
|
|
xlsx `chart` element's advanced chart types: scatter, bubble, combo, radar,
|
|
and stock (OHLC). 12 charts across 3 sheets.
|
|
|
|
SDK twin of charts-advanced.sh (officecli CLI). Both produce an equivalent
|
|
charts-advanced.xlsx. This one drives the **officecli Python SDK**
|
|
(`pip install officecli-sdk`): one resident is started and every sheet and
|
|
chart is shipped over the named pipe in `doc.batch(...)` round-trips. Each
|
|
item is the same `{"command","parent","type","props"}` dict you'd put in an
|
|
`officecli batch` list.
|
|
|
|
3 sheets, by chart family:
|
|
1-Scatter & Bubble — scatter(markers/line, smooth+trendline, per-series
|
|
markers) + bubble(market size)
|
|
2-Combo & Radar — combo(comboSplit, secondaryAxis, combotypes) +
|
|
radar(marker style)
|
|
3-Stock & Radar — stock(daily OHLC, weekly OHLC) + radar(filled) +
|
|
bubble(single series)
|
|
|
|
Usage:
|
|
pip install officecli-sdk # plus the `officecli` binary on PATH
|
|
python3 charts-advanced.py
|
|
"""
|
|
|
|
import os
|
|
import sys
|
|
|
|
# --- locate the SDK: prefer an installed `officecli-sdk`, else the in-repo copy
|
|
try:
|
|
import officecli # pip install officecli-sdk
|
|
except ImportError:
|
|
sys.path.insert(0, os.path.join(os.path.dirname(os.path.abspath(__file__)),
|
|
"..", "..", "..", "sdk", "python"))
|
|
import officecli
|
|
|
|
FILE = os.path.join(os.path.dirname(os.path.abspath(__file__)), "charts-advanced.xlsx")
|
|
|
|
|
|
def add_sheet(name):
|
|
"""One `add sheet` item in batch-shape."""
|
|
return {"command": "add", "parent": "/", "type": "sheet", "props": {"name": name}}
|
|
|
|
|
|
def chart(sheet, **props):
|
|
"""One `add chart` item in batch-shape (parent = the sheet)."""
|
|
return {"command": "add", "parent": f"/{sheet}", "type": "chart", "props": props}
|
|
|
|
|
|
print(f"Building {FILE} ...")
|
|
|
|
with officecli.create(FILE, "--force") as doc:
|
|
|
|
# ======================================================================
|
|
# Sheet: 1-Scatter & Bubble
|
|
# ======================================================================
|
|
print("--- 1-Scatter & Bubble ---")
|
|
items = [add_sheet("1-Scatter & Bubble")]
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 1: Scatter with markers — circle markers, line connecting points
|
|
# Features: chartType=scatter, categories as X values, marker=circle,
|
|
# markerSize, lineWidth, legend=bottom
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(
|
|
"1-Scatter & Bubble",
|
|
chartType="scatter",
|
|
title="Scatter: Markers & Line",
|
|
categories="1,2,3,4,5,6",
|
|
series1="SeriesA:10,25,15,40,30,50",
|
|
series2="SeriesB:5,18,22,35,28,42",
|
|
colors="4472C4,ED7D31",
|
|
x="0", y="0", width="12", height="18",
|
|
marker="circle", markerSize="8",
|
|
lineWidth="1.5",
|
|
legend="bottom",
|
|
))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 2: Scatter with smooth curve and trendline (reference line)
|
|
# Features: smooth=true (smooth curve), marker=diamond,
|
|
# referenceLine (trendline overlay), axisTitle, catTitle
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(
|
|
"1-Scatter & Bubble",
|
|
chartType="scatter",
|
|
title="Scatter: Smooth + Trendline",
|
|
categories="1,2,3,4,5,6,7,8",
|
|
series1="Growth:3,7,12,20,28,35,40,45",
|
|
colors="70AD47",
|
|
x="13", y="0", width="12", height="18",
|
|
smooth="true",
|
|
marker="diamond", markerSize="7",
|
|
referenceLine="25:FF0000:Target:dash",
|
|
axisTitle="Value", catTitle="Period",
|
|
))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 3: Scatter with varied marker styles per series
|
|
# Features: per-series marker style (series{N}.marker), gridlines styling
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(
|
|
"1-Scatter & Bubble",
|
|
chartType="scatter",
|
|
title="Scatter: Marker Styles",
|
|
categories="10,20,30,40,50",
|
|
series1="Squares:8,22,18,35,30",
|
|
series2="Triangles:15,10,28,20,42",
|
|
series3="Stars:5,30,12,45,25",
|
|
colors="4472C4,ED7D31,70AD47",
|
|
x="0", y="19", width="12", height="18",
|
|
**{"series1.marker": "square",
|
|
"series2.marker": "triangle",
|
|
"series3.marker": "star"},
|
|
markerSize="9",
|
|
lineWidth="1",
|
|
gridlines="D9D9D9:0.5:dot",
|
|
))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 4: Bubble chart with size data
|
|
# Features: chartType=bubble, categories as X, series as Y values,
|
|
# bubble sizes default to Y values, bubbleScale to control sizing
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(
|
|
"1-Scatter & Bubble",
|
|
chartType="bubble",
|
|
title="Bubble: Market Size",
|
|
categories="10,25,40,60,80",
|
|
series1="ProductA:30,50,20,70,45",
|
|
series2="ProductB:15,35,55,40,60",
|
|
colors="4472C4,ED7D31",
|
|
x="13", y="19", width="12", height="18",
|
|
bubbleScale="80",
|
|
legend="right",
|
|
))
|
|
|
|
doc.batch(items)
|
|
|
|
# ======================================================================
|
|
# Sheet: 2-Combo & Radar
|
|
# ======================================================================
|
|
print("--- 2-Combo & Radar ---")
|
|
items = [add_sheet("2-Combo & Radar")]
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 1: Combo chart — bar+line with comboSplit
|
|
# Features: chartType=combo, comboSplit=2 (first 2 series as bars,
|
|
# remaining as lines), categories as X labels
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(
|
|
"2-Combo & Radar",
|
|
chartType="combo",
|
|
title="Combo: Sales (Bar) + Growth % (Line)",
|
|
categories="Jan,Feb,Mar,Apr,May,Jun",
|
|
series1="Revenue:120,145,132,168,155,180",
|
|
series2="Expenses:80,92,85,98,90,105",
|
|
series3="Growth:8,12,6,15,10,16",
|
|
colors="4472C4,ED7D31,70AD47",
|
|
x="0", y="0", width="12", height="18",
|
|
comboSplit="2",
|
|
legend="bottom",
|
|
axisTitle="Amount", catTitle="Month",
|
|
))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 2: Combo with secondary axis
|
|
# Features: comboSplit=1, secondaryAxis=2 (series 2 on right Y-axis)
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(
|
|
"2-Combo & Radar",
|
|
chartType="combo",
|
|
title="Combo: Volume (Bar) + Price (Line, 2nd Axis)",
|
|
categories="Q1,Q2,Q3,Q4",
|
|
series1="Volume:1200,1450,1320,1680",
|
|
series2="AvgPrice:45,52,48,58",
|
|
colors="5B9BD5,FF0000",
|
|
x="13", y="0", width="12", height="18",
|
|
comboSplit="1",
|
|
secondaryAxis="2",
|
|
legend="bottom",
|
|
))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 3: Combo with combotypes — per-series type control
|
|
# Features: combotypes (per-series type: column, column, line, area)
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(
|
|
"2-Combo & Radar",
|
|
chartType="combo",
|
|
title="Combo: Mixed Types (combotypes)",
|
|
categories="A,B,C,D,E",
|
|
series1="Bars:30,45,28,52,40",
|
|
series2="MoreBars:20,30,22,38,28",
|
|
series3="Lines:12,18,15,22,16",
|
|
series4="Area:8,12,10,15,11",
|
|
colors="4472C4,5B9BD5,ED7D31,70AD47",
|
|
x="0", y="19", width="12", height="18",
|
|
combotypes="column,column,line,area",
|
|
legend="bottom",
|
|
))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 4: Radar (spider) chart with multiple series
|
|
# Features: chartType=radar, categories as spoke labels,
|
|
# multiple series, radarStyle=marker
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(
|
|
"2-Combo & Radar",
|
|
chartType="radar",
|
|
title="Radar: Skills Comparison",
|
|
categories="Speed,Strength,Stamina,Agility,Accuracy",
|
|
series1="AthleteA:80,65,90,75,85",
|
|
series2="AthleteB:70,85,60,90,70",
|
|
series3="AthleteC:90,70,75,65,80",
|
|
colors="4472C4,ED7D31,70AD47",
|
|
x="13", y="19", width="12", height="18",
|
|
radarStyle="marker",
|
|
legend="bottom",
|
|
))
|
|
|
|
doc.batch(items)
|
|
|
|
# ======================================================================
|
|
# Sheet: 3-Stock & Radar
|
|
# ======================================================================
|
|
print("--- 3-Stock & Radar ---")
|
|
items = [add_sheet("3-Stock & Radar")]
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 1: Stock (OHLC) chart — Open-High-Low-Close
|
|
# Features: chartType=stock, 4 series (Open/High/Low/Close),
|
|
# categories as date labels, catTitle, axisTitle
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(
|
|
"3-Stock & Radar",
|
|
chartType="stock",
|
|
title="Stock: OHLC Daily Prices",
|
|
categories="Mon,Tue,Wed,Thu,Fri",
|
|
series1="Open:145,148,150,147,152",
|
|
series2="High:152,155,157,153,160",
|
|
series3="Low:143,146,148,144,150",
|
|
series4="Close:148,150,147,152,158",
|
|
x="0", y="0", width="14", height="18",
|
|
legend="bottom",
|
|
catTitle="Day", axisTitle="Price",
|
|
))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 2: Stock chart — weekly OHLC with date categories
|
|
# Features: stock chart with 6 weeks of OHLC, gridlines styling
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(
|
|
"3-Stock & Radar",
|
|
chartType="stock",
|
|
title="Stock: Weekly OHLC (6 Weeks)",
|
|
categories="W1,W2,W3,W4,W5,W6",
|
|
series1="Open:100,104,102,108,105,110",
|
|
series2="High:106,110,108,115,112,118",
|
|
series3="Low:98,101,100,105,103,107",
|
|
series4="Close:104,102,108,105,110,115",
|
|
x="15", y="0", width="14", height="18",
|
|
gridlines="E0E0E0:0.75",
|
|
legend="bottom",
|
|
))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 3: Radar — filled style (spider web)
|
|
# Features: radarStyle=filled, transparency (fill alpha), multiple series
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(
|
|
"3-Stock & Radar",
|
|
chartType="radar",
|
|
title="Radar: Product Ratings (Filled)",
|
|
categories="Quality,Price,Design,Support,Delivery",
|
|
series1="BrandX:85,70,90,75,80",
|
|
series2="BrandY:70,90,65,85,75",
|
|
colors="4472C4,70AD47",
|
|
x="0", y="19", width="14", height="18",
|
|
radarStyle="filled",
|
|
transparency="40",
|
|
legend="bottom",
|
|
))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 4: Bubble — single series with explicit large differences in size
|
|
# Features: bubble with single series, bubbleScale=100, legend=none,
|
|
# axisTitle and catTitle labels
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(
|
|
"3-Stock & Radar",
|
|
chartType="bubble",
|
|
title="Bubble: Regional Opportunity",
|
|
categories="5,15,30,50,70,90",
|
|
series1="Regions:20,45,30,80,55,65",
|
|
colors="4472C4",
|
|
x="15", y="19", width="14", height="18",
|
|
bubbleScale="100",
|
|
legend="none",
|
|
axisTitle="Revenue", catTitle="Market Size",
|
|
))
|
|
|
|
doc.batch(items)
|
|
|
|
doc.send({"command": "save"})
|
|
|
|
print(f"Generated: {FILE}")
|
|
print(" 3 sheets (1-Scatter & Bubble, 2-Combo & Radar, 3-Stock & Radar)")
|
|
print(" 12 charts total: scatter(3), bubble(2), combo(3), radar(2), stock(2)")
|