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
338 lines
14 KiB
Python
338 lines
14 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Combo Charts Showcase — column+line, column+area, secondary axes, and styling.
|
|
|
|
Generates: charts-combo.xlsx
|
|
|
|
SDK twin of charts-combo.sh (officecli CLI). Both produce an equivalent
|
|
charts-combo.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 a single `doc.batch(...)` round-trip.
|
|
Each item is the same `{"command","parent"/"path","type","props"}` dict you'd
|
|
put in an `officecli batch` list.
|
|
|
|
16 combo charts across 4 sheets:
|
|
1-Combo Fundamentals — comboSplit, secondaryAxis, combotypes per-series
|
|
2-Combo Styling — title.font, legendfont, axisfont, gradients, shadow,
|
|
dataLabels, plotFill/chartFill, roundedCorners
|
|
3-Combo Advanced — referenceLine, gridlines, axisMin/Max, dispUnits,
|
|
plotLayout, multi-line markers
|
|
4-Combo Effects — title.glow/shadow, chartArea/plotArea border,
|
|
colorRule, 5-series dashboard
|
|
|
|
Usage:
|
|
pip install officecli-sdk # plus the `officecli` binary on PATH
|
|
python3 charts-combo.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-combo.xlsx")
|
|
|
|
|
|
def sheet(name):
|
|
"""One `add sheet` item in batch-shape."""
|
|
return {"command": "add", "parent": "/", "type": "sheet", "props": {"name": name}}
|
|
|
|
|
|
def chart(parent, **props):
|
|
"""One `add chart` item in batch-shape."""
|
|
return {"command": "add", "parent": parent, "type": "chart", "props": props}
|
|
|
|
|
|
print(f"Building {FILE} ...")
|
|
|
|
with officecli.create(FILE, "--force") as doc:
|
|
items = [
|
|
# ==================================================================
|
|
# Sheet: 1-Combo Fundamentals
|
|
# ==================================================================
|
|
sheet("1-Combo Fundamentals"),
|
|
|
|
# Chart 1: Basic combo with comboSplit (2 bar series + 1 line)
|
|
# Features: chartType=combo, comboSplit=2 (first 2 as bars, rest as lines)
|
|
chart("/1-Combo Fundamentals",
|
|
chartType="combo",
|
|
title="Revenue vs Expenses vs Margin",
|
|
series1="Revenue:120,145,160,180,195",
|
|
series2="Expenses:90,100,110,115,125",
|
|
series3="Margin %:25,31,31,36,36",
|
|
categories="Q1,Q2,Q3,Q4,Q5",
|
|
comboSplit="2",
|
|
colors="4472C4,ED7D31,70AD47",
|
|
x="0", y="0", width="12", height="18",
|
|
legend="bottom"),
|
|
|
|
# Chart 2: Combo with secondaryAxis (line on right Y-axis)
|
|
# Features: secondaryAxis=2 (series 2 on right Y-axis), catTitle, axisTitle
|
|
chart("/1-Combo Fundamentals",
|
|
chartType="combo",
|
|
title="Sales & Growth Rate",
|
|
series1="Sales ($K):320,380,420,510,560",
|
|
series2="Growth %:8,19,11,21,10",
|
|
categories="2021,2022,2023,2024,2025",
|
|
comboSplit="1",
|
|
secondaryAxis="2",
|
|
colors="2E75B6,C00000",
|
|
x="13", y="0", width="12", height="18",
|
|
legend="bottom",
|
|
catTitle="Year", axisTitle="Sales ($K)"),
|
|
|
|
# Chart 3: combotypes per-series type control
|
|
# Features: combotypes=column,column,line,area (per-series type)
|
|
chart("/1-Combo Fundamentals",
|
|
chartType="combo",
|
|
title="Mixed Series Types",
|
|
series1="Product A:50,65,70,80,90",
|
|
series2="Product B:40,55,60,72,85",
|
|
series3="Trend:48,62,68,78,88",
|
|
series4="Forecast:30,40,50,55,65",
|
|
categories="Jan,Feb,Mar,Apr,May",
|
|
combotypes="column,column,line,area",
|
|
colors="4472C4,ED7D31,70AD47,BDD7EE",
|
|
x="0", y="19", width="12", height="18",
|
|
legend="bottom"),
|
|
|
|
# Chart 4: combotypes with secondaryAxis
|
|
# Features: combotypes + secondaryAxis together
|
|
chart("/1-Combo Fundamentals",
|
|
chartType="combo",
|
|
title="Revenue Mix & Margin",
|
|
series1="Domestic:200,220,250,270,300",
|
|
series2="Export:80,95,110,130,150",
|
|
series3="Net Margin %:18,20,22,24,26",
|
|
categories="2021,2022,2023,2024,2025",
|
|
combotypes="column,column,line",
|
|
secondaryAxis="3",
|
|
colors="4472C4,9DC3E6,C00000",
|
|
x="13", y="19", width="12", height="18",
|
|
legend="bottom",
|
|
catTitle="Year"),
|
|
|
|
# ==================================================================
|
|
# Sheet: 2-Combo Styling
|
|
# ==================================================================
|
|
sheet("2-Combo Styling"),
|
|
|
|
# Chart 1: Title, legend, axisfont styling
|
|
# Features: title.font/size/color/bold, legendfont, axisfont
|
|
chart("/2-Combo Styling",
|
|
chartType="combo",
|
|
title="Styled Combo Chart",
|
|
series1="Revenue:150,175,200,220",
|
|
series2="COGS:100,110,130,140",
|
|
series3="Profit %:33,37,35,36",
|
|
categories="Q1,Q2,Q3,Q4",
|
|
comboSplit="2",
|
|
colors="1F4E79,5B9BD5,70AD47",
|
|
x="0", y="0", width="12", height="18",
|
|
**{"title.font": "Georgia", "title.size": "16",
|
|
"title.color": "1F4E79", "title.bold": "true"},
|
|
legend="bottom", legendfont="10:333333:Calibri",
|
|
axisfont="9:666666"),
|
|
|
|
# Chart 2: Series shadow, gradients
|
|
# Features: gradients (per-bar-series), series.shadow
|
|
chart("/2-Combo Styling",
|
|
chartType="combo",
|
|
title="Gradient & Shadow Effects",
|
|
series1="Actual:85,92,105,120,135",
|
|
series2="Budget:80,90,100,110,120",
|
|
series3="Variance:5,2,5,10,15",
|
|
categories="Jan,Feb,Mar,Apr,May",
|
|
comboSplit="2",
|
|
x="13", y="0", width="12", height="18",
|
|
gradients="1F4E79-5B9BD5:90;C55A11-F4B183:90",
|
|
**{"series.shadow": "000000-4-315-2-30"},
|
|
legend="bottom"),
|
|
|
|
# Chart 3: dataLabels on line series
|
|
# Features: dataLabels=true, labelPos=top, labelFont
|
|
chart("/2-Combo Styling",
|
|
chartType="combo",
|
|
title="Data Labels on Lines",
|
|
series1="Units:500,620,710,800",
|
|
series2="Avg Price:45,48,52,55",
|
|
categories="Q1,Q2,Q3,Q4",
|
|
comboSplit="1",
|
|
secondaryAxis="2",
|
|
colors="4472C4,ED7D31",
|
|
x="0", y="19", width="12", height="18",
|
|
dataLabels="true", labelPos="top",
|
|
labelFont="9:333333:true",
|
|
legend="bottom"),
|
|
|
|
# Chart 4: plotFill, chartFill, roundedCorners
|
|
# Features: plotFill, chartFill, roundedCorners
|
|
chart("/2-Combo Styling",
|
|
chartType="combo",
|
|
title="Chart Area Styling",
|
|
series1="Online:180,210,240,260,290",
|
|
series2="Retail:150,140,135,130,120",
|
|
series3="Growth %:5,12,15,10,12",
|
|
categories="2021,2022,2023,2024,2025",
|
|
comboSplit="2",
|
|
colors="2E75B6,ED7D31,70AD47",
|
|
x="13", y="19", width="12", height="18",
|
|
plotFill="F0F4F8", chartFill="FAFAFA",
|
|
roundedCorners="true",
|
|
legend="bottom"),
|
|
|
|
# ==================================================================
|
|
# Sheet: 3-Combo Advanced
|
|
# ==================================================================
|
|
sheet("3-Combo Advanced"),
|
|
|
|
# Chart 1: referenceLine, gridlines
|
|
# Features: referenceLine=value:label:color, gridlines
|
|
chart("/3-Combo Advanced",
|
|
chartType="combo",
|
|
title="Target Reference Line",
|
|
series1="Actual:95,105,115,125,130",
|
|
series2="Forecast:90,100,110,120,130",
|
|
categories="Jan,Feb,Mar,Apr,May",
|
|
comboSplit="1",
|
|
colors="4472C4,BDD7EE",
|
|
x="0", y="0", width="12", height="18",
|
|
referenceLine="110:C00000:Target",
|
|
gridlines="D9D9D9:0.5",
|
|
legend="bottom"),
|
|
|
|
# Chart 2: axisMin/Max, dispUnits
|
|
# Features: axisMin/Max, dispUnits=thousands
|
|
chart("/3-Combo Advanced",
|
|
chartType="combo",
|
|
title="Axis Scaling & Units",
|
|
series1="Revenue:1200000,1450000,1600000,1800000",
|
|
series2="Profit %:18,22,25,28",
|
|
categories="2022,2023,2024,2025",
|
|
comboSplit="1",
|
|
secondaryAxis="2",
|
|
colors="2E75B6,70AD47",
|
|
x="13", y="0", width="12", height="18",
|
|
axisMin="1000000", axisMax="2000000",
|
|
dispUnits="thousands",
|
|
legend="bottom"),
|
|
|
|
# Chart 3: Manual layout
|
|
# Features: plotLayout=left,top,width,height (manual plot area)
|
|
chart("/3-Combo Advanced",
|
|
chartType="combo",
|
|
title="Manual Layout",
|
|
series1="Plan:100,120,140,160",
|
|
series2="Actual:95,125,135,170",
|
|
series3="Delta %:-5,4,-4,6",
|
|
categories="Q1,Q2,Q3,Q4",
|
|
comboSplit="2",
|
|
secondaryAxis="3",
|
|
colors="4472C4,ED7D31,70AD47",
|
|
x="0", y="19", width="12", height="18",
|
|
plotLayout="0.1,0.15,0.85,0.75",
|
|
legend="bottom"),
|
|
|
|
# Chart 4: Multiple line series with markers + bar series
|
|
# Features: multiple line series on secondary axis, markers
|
|
chart("/3-Combo Advanced",
|
|
chartType="combo",
|
|
title="Multi-Line with Markers",
|
|
series1="Units Sold:800,920,1050,1200,1350",
|
|
series2="North:30,35,38,42,45",
|
|
series3="South:25,28,32,36,40",
|
|
series4="West:20,24,28,32,35",
|
|
categories="Q1,Q2,Q3,Q4,Q5",
|
|
comboSplit="1",
|
|
secondaryAxis="2,3,4",
|
|
colors="4472C4,C00000,70AD47,FFC000",
|
|
x="13", y="19", width="12", height="18",
|
|
markers="circle-6",
|
|
legend="bottom"),
|
|
|
|
# ==================================================================
|
|
# Sheet: 4-Combo Effects
|
|
# ==================================================================
|
|
sheet("4-Combo Effects"),
|
|
|
|
# Chart 1: title.glow, title.shadow
|
|
# Features: title.glow=color-radius, title.shadow
|
|
chart("/4-Combo Effects",
|
|
chartType="combo",
|
|
title="Glowing Title",
|
|
series1="Metric A:60,72,85,90,100",
|
|
series2="Metric B:40,50,55,62,70",
|
|
series3="Ratio:67,69,65,69,70",
|
|
categories="W1,W2,W3,W4,W5",
|
|
comboSplit="2",
|
|
colors="4472C4,ED7D31,70AD47",
|
|
x="0", y="0", width="12", height="18",
|
|
**{"title.glow": "4472C4-6",
|
|
"title.shadow": "000000-3-315-2-30"},
|
|
legend="bottom"),
|
|
|
|
# Chart 2: chartArea.border, plotArea.border
|
|
# Features: chartArea.border=color-width, plotArea.border
|
|
chart("/4-Combo Effects",
|
|
chartType="combo",
|
|
title="Bordered Areas",
|
|
series1="Income:250,280,310,340",
|
|
series2="Costs:180,195,210,225",
|
|
series3="Margin %:28,30,32,34",
|
|
categories="Q1,Q2,Q3,Q4",
|
|
comboSplit="2",
|
|
colors="2E75B6,ED7D31,548235",
|
|
x="13", y="0", width="12", height="18",
|
|
**{"chartArea.border": "333333:1.5",
|
|
"plotArea.border": "999999:0.75"},
|
|
legend="bottom"),
|
|
|
|
# Chart 3: colorRule
|
|
# Features: colorRule=threshold:belowColor:aboveColor
|
|
chart("/4-Combo Effects",
|
|
chartType="combo",
|
|
title="Color Rule Combo",
|
|
series1="Performance:72,85,65,90,78",
|
|
series2="Target:80,80,80,80,80",
|
|
categories="Team A,Team B,Team C,Team D,Team E",
|
|
comboSplit="1",
|
|
colors="4472C4,C00000",
|
|
x="0", y="19", width="12", height="18",
|
|
colorRule="80:C00000:70AD47",
|
|
legend="bottom"),
|
|
|
|
# Chart 4: Complex combo with 5+ series
|
|
# Features: 5 series, mixed combotypes, secondary axis
|
|
chart("/4-Combo Effects",
|
|
chartType="combo",
|
|
title="Full Business Dashboard",
|
|
series1="Revenue:500,550,600,650,700",
|
|
series2="COGS:300,320,340,360,380",
|
|
series3="OpEx:100,105,110,115,120",
|
|
series4="Net Income:100,125,150,175,200",
|
|
series5="Margin %:20,23,25,27,29",
|
|
categories="2021,2022,2023,2024,2025",
|
|
combotypes="column,column,column,area,line",
|
|
secondaryAxis="5",
|
|
colors="4472C4,ED7D31,A5A5A5,BDD7EE,C00000",
|
|
x="13", y="19", width="12", height="18",
|
|
legend="bottom",
|
|
gridlines="E0E0E0:0.5"),
|
|
|
|
# Remove blank default Sheet1 (all data is inline)
|
|
{"command": "remove", "path": "/Sheet1"},
|
|
]
|
|
|
|
doc.batch(items)
|
|
print(f" added {len(items)} sheets/charts/ops")
|
|
doc.send({"command": "save"})
|
|
|
|
print(f"Generated: {FILE}")
|
|
print(" 4 chart sheets, 16 charts total")
|