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
321 lines
13 KiB
Python
321 lines
13 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Bubble Charts Showcase — bubble scale, size representation, and styling.
|
|
|
|
Generates: charts-bubble.xlsx — 4 chart sheets, 14 charts total
|
|
exercising chartType=bubble (X;Y;Size data), bubbleScale, sizeRepresents,
|
|
dataLabels, ARGB transparency, gridlines/axis styling, plot/chart fills,
|
|
series shadow, secondaryAxis, referenceLine, log scale, trendline,
|
|
shownegbubbles, and series1.bubbleSize range references.
|
|
|
|
SDK twin of charts-bubble.sh (officecli CLI). Both produce an equivalent
|
|
charts-bubble.xlsx. This one drives the **officecli Python SDK**
|
|
(`pip install officecli-sdk`): one resident is started and every sheet,
|
|
cell and chart is shipped over the named pipe — grouped per sheet into
|
|
`doc.batch(...)` round-trips. Each item is the same
|
|
`{"command","parent","type","props"}` dict you'd put in an `officecli
|
|
batch` list.
|
|
|
|
Usage:
|
|
pip install officecli-sdk # plus the `officecli` binary on PATH
|
|
python3 charts-bubble.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-bubble.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}
|
|
|
|
|
|
def cell(parent, ref, value):
|
|
"""One `add cell` item in batch-shape (matches the CLI's
|
|
`add ... --type cell --prop ref=.. --prop value=..`)."""
|
|
return {"command": "add", "parent": parent, "type": "cell",
|
|
"props": {"ref": ref, "value": value}}
|
|
|
|
|
|
print(f"Building {FILE} ...")
|
|
|
|
with officecli.create(FILE, "--force") as doc:
|
|
|
|
# ======================================================================
|
|
# Sheet: 1-Bubble Fundamentals
|
|
# ======================================================================
|
|
print("\n--- 1-Bubble Fundamentals ---")
|
|
S1 = "/1-Bubble Fundamentals"
|
|
items = [
|
|
sheet("1-Bubble Fundamentals"),
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 1: Basic bubble chart with 2 series
|
|
# Features: chartType=bubble, X;Y;Size triplets, catTitle, axisTitle
|
|
# ------------------------------------------------------------------
|
|
chart(S1,
|
|
chartType="bubble",
|
|
title="Market Analysis",
|
|
series1="Enterprise:80,45,60",
|
|
series2="Consumer:50,35,70",
|
|
colors="4472C4,ED7D31",
|
|
x="0", y="0", width="12", height="18",
|
|
catTitle="Market Size", axisTitle="Growth Rate",
|
|
legend="bottom"),
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 2: bubbleScale=100 with dataLabels
|
|
# Features: bubbleScale=100, dataLabels with center positioning
|
|
# ------------------------------------------------------------------
|
|
chart(S1,
|
|
chartType="bubble",
|
|
title="Product Portfolio",
|
|
series1="Products:90,50,70,40",
|
|
colors="2E75B6",
|
|
x="13", y="0", width="12", height="18",
|
|
bubbleScale="100",
|
|
dataLabels="true", labelPos="center",
|
|
labelFont="9:FFFFFF:true",
|
|
legend="bottom"),
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 3: bubbleScale=50 (smaller bubbles)
|
|
# Features: bubbleScale=50
|
|
# ------------------------------------------------------------------
|
|
chart(S1,
|
|
chartType="bubble",
|
|
title="Small Bubbles (Scale 50)",
|
|
series1="Tech:60,80,45",
|
|
series2="Finance:55,70,35",
|
|
colors="70AD47,FFC000",
|
|
x="0", y="19", width="12", height="18",
|
|
bubbleScale="50",
|
|
legend="bottom"),
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 4: sizeRepresents=width
|
|
# Features: sizeRepresents=width (bubble diameter proportional to value)
|
|
# ------------------------------------------------------------------
|
|
chart(S1,
|
|
chartType="bubble",
|
|
title="Size by Width",
|
|
series1="Regions:70,40,55,85",
|
|
colors="5B9BD5",
|
|
x="13", y="19", width="12", height="18",
|
|
sizeRepresents="width",
|
|
bubbleScale="100",
|
|
legend="bottom"),
|
|
]
|
|
doc.batch(items)
|
|
|
|
# ======================================================================
|
|
# Sheet: 2-Bubble Styling
|
|
# ======================================================================
|
|
print("--- 2-Bubble Styling ---")
|
|
S2 = "/2-Bubble Styling"
|
|
items = [
|
|
sheet("2-Bubble Styling"),
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 1: Title styling, legend positioning
|
|
# Features: title.font/size/color/bold, legend=right, legendfont
|
|
# ------------------------------------------------------------------
|
|
chart(S2,
|
|
chartType="bubble",
|
|
title="Styled Bubble Chart",
|
|
series1="SegmentA:65,50,80",
|
|
series2="SegmentB:45,60,40",
|
|
colors="1F4E79,C55A11",
|
|
x="0", y="0", width="12", height="18",
|
|
**{"title.font": "Georgia", "title.size": "16",
|
|
"title.color": "1F4E79", "title.bold": "true"},
|
|
legend="right", legendfont="10:333333:Calibri"),
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 2: Series colors, transparency
|
|
# Features: ARGB colors with alpha (80=50% transparency)
|
|
# ------------------------------------------------------------------
|
|
chart(S2,
|
|
chartType="bubble",
|
|
title="Transparent Overlapping Bubbles",
|
|
series1="GroupX:75,60,90,50",
|
|
series2="GroupY:65,55,80,45",
|
|
colors="804472C4,80ED7D31",
|
|
x="13", y="0", width="12", height="18",
|
|
bubbleScale="120",
|
|
legend="bottom"),
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 3: gridlines, axisfont, axisLine
|
|
# Features: gridlines, axisfont, axisLine
|
|
# ------------------------------------------------------------------
|
|
chart(S2,
|
|
chartType="bubble",
|
|
title="Grid & Axis Styling",
|
|
series1="Div1:55,70,45",
|
|
series2="Div2:60,40,75",
|
|
colors="2E75B6,548235",
|
|
x="0", y="19", width="12", height="18",
|
|
gridlines="D9D9D9:0.5",
|
|
axisfont="9:666666",
|
|
axisLine="333333:1",
|
|
legend="bottom"),
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 4: plotFill, chartFill, series.shadow
|
|
# Features: plotFill, chartFill, series.shadow
|
|
# ------------------------------------------------------------------
|
|
chart(S2,
|
|
chartType="bubble",
|
|
title="Shadow & Fill Effects",
|
|
series1="Portfolio:80,55,65,45",
|
|
colors="4472C4",
|
|
x="13", y="19", width="12", height="18",
|
|
plotFill="F0F4F8", chartFill="FAFAFA",
|
|
**{"series.shadow": "000000-4-315-2-30"},
|
|
legend="bottom"),
|
|
]
|
|
doc.batch(items)
|
|
|
|
# ======================================================================
|
|
# Sheet: 3-Bubble Advanced
|
|
# ======================================================================
|
|
print("--- 3-Bubble Advanced ---")
|
|
S3 = "/3-Bubble Advanced"
|
|
items = [
|
|
sheet("3-Bubble Advanced"),
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 1: secondaryAxis
|
|
# Features: secondaryAxis on bubble chart
|
|
# ------------------------------------------------------------------
|
|
chart(S3,
|
|
chartType="bubble",
|
|
title="Dual-Axis Bubble",
|
|
series1="Domestic:70,85,60,90",
|
|
series2="International:45,55,80,65",
|
|
categories="1,2,3,4",
|
|
colors="4472C4,ED7D31",
|
|
x="0", y="0", width="12", height="18",
|
|
secondaryAxis="2",
|
|
legend="bottom"),
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 2: referenceLine
|
|
# Features: referenceLine on bubble chart
|
|
# ------------------------------------------------------------------
|
|
chart(S3,
|
|
chartType="bubble",
|
|
title="Growth Threshold",
|
|
series1="Products:60,80,45,55",
|
|
categories="1,2,3,4",
|
|
colors="70AD47",
|
|
x="13", y="0", width="12", height="18",
|
|
referenceLine="50:C00000:Target",
|
|
bubbleScale="80",
|
|
legend="bottom"),
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 3: axisMin/Max, logBase
|
|
# Features: axisMin/Max, logBase=10 (logarithmic scale)
|
|
# ------------------------------------------------------------------
|
|
chart(S3,
|
|
chartType="bubble",
|
|
title="Log Scale Analysis",
|
|
series1="Markets:5,15,50,120",
|
|
categories="1,2,3,4",
|
|
colors="2E75B6",
|
|
x="0", y="19", width="12", height="18",
|
|
axisMin="1", axisMax="200",
|
|
logBase="10",
|
|
bubbleScale="80",
|
|
legend="bottom"),
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 4: chartArea.border, plotArea.border, trendline
|
|
# Features: chartArea.border, plotArea.border, trendline=linear
|
|
# ------------------------------------------------------------------
|
|
chart(S3,
|
|
chartType="bubble",
|
|
title="Trend & Borders",
|
|
series1="Investments:20,55,95,140,180",
|
|
categories="1,2,3,4,5",
|
|
colors="4472C4",
|
|
x="13", y="19", width="12", height="18",
|
|
**{"chartArea.border": "333333:1.5",
|
|
"plotArea.border": "999999:0.75"},
|
|
trendline="linear",
|
|
legend="bottom"),
|
|
]
|
|
doc.batch(items)
|
|
|
|
# ======================================================================
|
|
# Sheet: 4-Bubble Series Data
|
|
# ======================================================================
|
|
print("--- 4-Bubble Series Data ---")
|
|
S4 = "/4-Bubble Series Data"
|
|
items = [
|
|
sheet("4-Bubble Series Data"),
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 1: shownegbubbles — render bubbles for negative size values
|
|
# Features: shownegbubbles=true (render bubbles whose size value is
|
|
# negative by reflecting them — Excel hides them by default)
|
|
# ------------------------------------------------------------------
|
|
chart(S4,
|
|
chartType="bubble",
|
|
title="shownegbubbles — negative sizes visible",
|
|
series1="Data:60,30,90",
|
|
series2="Neg:40,50,70",
|
|
colors="4472C4,C00000",
|
|
x="0", y="0", width="12", height="18",
|
|
shownegbubbles="true",
|
|
bubbleScale="80",
|
|
legend="bottom"),
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 2: series1.bubbleSize (range ref) — sizes from worksheet cells
|
|
#
|
|
# Populate some size data first, then reference it. Demonstrates the
|
|
# bubbleSize + bubbleSizeRef round-trip: Excel re-computes when the
|
|
# source cells change; bubbleSizeRef is emitted on Get alongside the
|
|
# cached literal bubbleSize values.
|
|
# ------------------------------------------------------------------
|
|
cell(S4, "A1", "10"),
|
|
cell(S4, "A2", "25"),
|
|
cell(S4, "A3", "40"),
|
|
chart(S4,
|
|
chartType="bubble",
|
|
title="series1.bubbleSize — range ref",
|
|
series1="Sizes:80,45,60",
|
|
**{"series1.bubbleSize": "4-Bubble Series Data!$A$1:$A$3"},
|
|
colors="70AD47",
|
|
x="13", y="0", width="12", height="18",
|
|
bubbleScale="100", legend="bottom"),
|
|
]
|
|
doc.batch(items)
|
|
|
|
# Remove blank default Sheet1 (all data is inline)
|
|
doc.send({"command": "remove", "path": "/Sheet1"})
|
|
|
|
doc.send({"command": "save"})
|
|
# context exit closes the resident, flushing the workbook to disk.
|
|
|
|
print(f"\nDone! Generated: {FILE}")
|
|
print(" 4 chart sheets, 14 charts total")
|