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
602 lines
25 KiB
Python
Executable File
602 lines
25 KiB
Python
Executable File
#!/usr/bin/env python3
|
|
"""
|
|
Column & Bar Charts Showcase — column, columnStacked, columnPercentStacked, and column3d with all variations.
|
|
|
|
Generates: charts-column.xlsx
|
|
|
|
SDK twin of charts-column.sh (officecli CLI). Both produce an equivalent
|
|
charts-column.xlsx. This one drives the **officecli Python SDK**
|
|
(`pip install officecli-sdk`): one resident is started, the shared source data
|
|
and every chart are shipped over the named pipe, and each sheet's charts are
|
|
applied in `doc.batch(...)` round-trips. Each item is the same
|
|
`{"command","parent","type","props"}` dict you'd put in an `officecli batch` list.
|
|
|
|
Every column chart feature officecli supports is demonstrated at least once:
|
|
gap width, overlap, bar shapes, axis scaling, gridlines, data labels,
|
|
legend positioning, reference lines, secondary axis, gradients,
|
|
transparency, shadows, manual layout, and 3D rotation.
|
|
|
|
8 sheets (Sheet1 data + 7 chart sheets), 28 charts total.
|
|
|
|
1-Column Fundamentals 4 charts — data input variants, axis titles, inline/cell-range/data
|
|
2-Column Variants 4 charts — columnStacked, columnPercentStacked, column3d
|
|
3-Column Styling 4 charts — title styling, series effects, gradients, transparency
|
|
4-Axis & Gridlines 4 charts — axis scaling, log scale, reverse, display units
|
|
5-Labels & Legend 4 charts — data labels, custom labels, legend layout
|
|
6-Effects & Advanced 4 charts — secondary axis, reference line, glow/shadow, colorRule
|
|
7-Bar Shape & Gap 4 charts — gapwidth, overlap, 3D shapes (cylinder, cone, pyramid)
|
|
|
|
Usage:
|
|
pip install officecli-sdk # plus the `officecli` binary on PATH
|
|
python3 charts-column.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-column.xlsx")
|
|
|
|
|
|
def add_sheet(name):
|
|
"""One `add sheet` item in batch-shape."""
|
|
return {"command": "add", "parent": "/", "type": "sheet", "props": {"name": name}}
|
|
|
|
|
|
def cell(path, **props):
|
|
"""One `set` item writing a cell in batch-shape."""
|
|
return {"command": "set", "path": path, "props": props}
|
|
|
|
|
|
def chart(sheet, **props):
|
|
"""One `add chart` item in batch-shape, anchored on a sheet."""
|
|
return {"command": "add", "parent": sheet, "type": "chart", "props": props}
|
|
|
|
|
|
print(f"Building {FILE} ...")
|
|
|
|
with officecli.create(FILE, "--force") as doc:
|
|
|
|
# ======================================================================
|
|
# Source data — shared across all charts (Sheet1)
|
|
# ======================================================================
|
|
print("--- Populating source data ---")
|
|
data_items = []
|
|
for j, h in enumerate(["Month", "East", "South", "North", "West"]):
|
|
data_items.append(cell(f"/Sheet1/{'ABCDE'[j]}1", text=h, bold="true"))
|
|
|
|
months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]
|
|
east = [120, 135, 148, 162, 155, 178, 195, 210, 188, 172, 165, 198]
|
|
south = [95, 108, 115, 128, 142, 155, 168, 175, 160, 148, 135, 158]
|
|
north = [88, 92, 105, 118, 125, 138, 145, 152, 140, 130, 122, 142]
|
|
west = [110, 118, 130, 145, 138, 162, 175, 190, 170, 155, 148, 180]
|
|
|
|
for i in range(12):
|
|
r = i + 2
|
|
for j, val in enumerate([months[i], east[i], south[i], north[i], west[i]]):
|
|
data_items.append(cell(f"/Sheet1/{'ABCDE'[j]}{r}", text=str(val)))
|
|
doc.batch(data_items)
|
|
|
|
# ======================================================================
|
|
# Sheet: 1-Column Fundamentals
|
|
# ======================================================================
|
|
print("--- 1-Column Fundamentals ---")
|
|
s = "/1-Column Fundamentals"
|
|
items = [add_sheet("1-Column Fundamentals")]
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 1: Basic column with dataRange and axis titles
|
|
# Features: chartType=column, dataRange, catTitle, axisTitle, axisfont,
|
|
# gridlines, colors
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Monthly Sales by Region",
|
|
dataRange="Sheet1!A1:E13",
|
|
x="0", y="0", width="12", height="18",
|
|
catTitle="Month", axisTitle="Revenue",
|
|
axisfont="9:58626E:Arial",
|
|
gridlines="D9D9D9:0.5:dot",
|
|
colors="4472C4,ED7D31,70AD47,FFC000"))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 2: Inline series with custom colors and gap width
|
|
# Features: inline series (series1=Name:v1,v2,...), colors, gapwidth,
|
|
# legend=bottom
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Q1 Product Sales",
|
|
series1="Laptops:320,280,350,310",
|
|
series2="Phones:450,420,480,460",
|
|
series3="Tablets:180,160,200,190",
|
|
categories="Jan,Feb,Mar,Apr",
|
|
colors="2E75B6,C00000,70AD47",
|
|
x="13", y="0", width="12", height="18",
|
|
gapwidth="80",
|
|
legend="bottom"))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 3: Dotted syntax with cell ranges
|
|
# Features: series.name/values/categories (cell range via dotted syntax),
|
|
# minorGridlines
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="East vs South (Cell Range)",
|
|
**{"series1.name": "East",
|
|
"series1.values": "Sheet1!B2:B13",
|
|
"series1.categories": "Sheet1!A2:A13",
|
|
"series2.name": "South",
|
|
"series2.values": "Sheet1!C2:C13",
|
|
"series2.categories": "Sheet1!A2:A13"},
|
|
x="0", y="19", width="12", height="18",
|
|
colors="4472C4,ED7D31",
|
|
gridlines="D9D9D9:0.5:dot",
|
|
minorGridlines="EEEEEE:0.3:dot"))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 4: data= shorthand format
|
|
# Features: data (inline shorthand Name:v1;Name2:v2), legend=right
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Weekly Output",
|
|
data="Team A:85,92,78,95,88;Team B:70,80,85,90,75",
|
|
categories="Mon,Tue,Wed,Thu,Fri",
|
|
colors="0070C0,FF6600",
|
|
x="13", y="19", width="12", height="18",
|
|
legend="right"))
|
|
doc.batch(items)
|
|
|
|
# ======================================================================
|
|
# Sheet: 2-Column Variants
|
|
# ======================================================================
|
|
print("--- 2-Column Variants ---")
|
|
s = "/2-Column Variants"
|
|
items = [add_sheet("2-Column Variants")]
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 1: Stacked column with center data labels and series outline
|
|
# Features: columnStacked, dataLabels=center, series.outline
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="columnStacked",
|
|
title="Stacked Sales by Region",
|
|
dataRange="Sheet1!A1:E7",
|
|
x="0", y="0", width="12", height="18",
|
|
colors="4472C4,ED7D31,70AD47,FFC000",
|
|
dataLabels="center",
|
|
**{"series.outline": "FFFFFF-0.5"},
|
|
legend="bottom"))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 2: 100% stacked column with axis number format
|
|
# Features: columnPercentStacked, axisNumFmt=0%, legend=bottom
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="columnPercentStacked",
|
|
title="Regional Contribution %",
|
|
dataRange="Sheet1!A1:E7",
|
|
x="13", y="0", width="12", height="18",
|
|
colors="1F4E79,2E75B6,9DC3E6,BDD7EE",
|
|
axisNumFmt="0%",
|
|
legend="bottom",
|
|
gridlines="E0E0E0:0.5:solid"))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 3: 3D column with perspective and style
|
|
# Features: column3d, view3d (rotX,rotY,perspective), style (preset 1-48)
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column3d",
|
|
title="3D Regional Trends",
|
|
dataRange="Sheet1!A1:E7",
|
|
x="0", y="19", width="12", height="18",
|
|
view3d="15,20,30",
|
|
colors="4472C4,ED7D31,70AD47,FFC000",
|
|
chartFill="F8F8F8",
|
|
style="3"))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 4: 3D stacked column with gap depth
|
|
# Features: column3d stacked, gapDepth=200 (3D depth spacing)
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column3d",
|
|
title="3D Stacked with Gap Depth",
|
|
series1="East:120,135,148,162,155,178",
|
|
series2="South:95,108,115,128,142,155",
|
|
series3="North:88,92,105,118,125,138",
|
|
categories="Jan,Feb,Mar,Apr,May,Jun",
|
|
x="13", y="19", width="12", height="18",
|
|
view3d="15,20,30",
|
|
gapDepth="200",
|
|
colors="2E75B6,ED7D31,70AD47",
|
|
legend="right"))
|
|
doc.batch(items)
|
|
|
|
# ======================================================================
|
|
# Sheet: 3-Column Styling
|
|
# ======================================================================
|
|
print("--- 3-Column Styling ---")
|
|
s = "/3-Column Styling"
|
|
items = [add_sheet("3-Column Styling")]
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 1: Title styling — font, size, color, bold
|
|
# Features: title.font=Georgia, title.size=16, title.color=1F4E79,
|
|
# title.bold=true
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Styled Title Demo",
|
|
dataRange="Sheet1!A1:E7",
|
|
x="0", y="0", width="12", height="18",
|
|
**{"title.font": "Georgia", "title.size": "16",
|
|
"title.color": "1F4E79", "title.bold": "true"},
|
|
colors="4472C4,ED7D31,70AD47,FFC000",
|
|
legend="bottom"))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 2: Series shadow and outline effects
|
|
# Features: series.shadow (color-blur-angle-dist-opacity),
|
|
# series.outline (color-width)
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Shadow & Outline Effects",
|
|
series1="Revenue:320,280,350,310,340",
|
|
series2="Cost:210,195,230,220,215",
|
|
categories="Q1,Q2,Q3,Q4,Q5",
|
|
x="13", y="0", width="12", height="18",
|
|
colors="4472C4,C00000",
|
|
**{"series.shadow": "000000-4-315-2-40",
|
|
"series.outline": "FFFFFF-0.5"},
|
|
gapwidth="100",
|
|
legend="bottom"))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 3: Per-series gradient fills
|
|
# Features: gradients (per-series gradient fills, start-end:angle)
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Gradient Columns",
|
|
series1="East:120,135,148,162",
|
|
series2="South:95,108,115,128",
|
|
series3="North:88,92,105,118",
|
|
categories="Q1,Q2,Q3,Q4",
|
|
x="0", y="19", width="12", height="18",
|
|
gradients="4472C4-BDD7EE:90;ED7D31-FBE5D6:90;70AD47-C5E0B4:90",
|
|
legend="bottom"))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 4: Transparency + plotFill gradient + chartFill + roundedCorners
|
|
# Features: transparency=30, plotFill gradient, chartFill, roundedCorners
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Transparent Columns on Gradient",
|
|
dataRange="Sheet1!A1:E7",
|
|
x="13", y="19", width="12", height="18",
|
|
transparency="30",
|
|
plotFill="F0F4F8-D6E4F0:90",
|
|
chartFill="FFFFFF",
|
|
colors="1F4E79,2E75B6,5B9BD5,9DC3E6",
|
|
roundedCorners="true",
|
|
legend="bottom"))
|
|
doc.batch(items)
|
|
|
|
# ======================================================================
|
|
# Sheet: 4-Axis & Gridlines
|
|
# ======================================================================
|
|
print("--- 4-Axis & Gridlines ---")
|
|
s = "/4-Axis & Gridlines"
|
|
items = [add_sheet("4-Axis & Gridlines")]
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 1: Custom axis scaling — min, max, majorUnit, minorUnit
|
|
# Features: axisMin, axisMax, majorUnit, minorUnit,
|
|
# axisLine (value axis line styling), catAxisLine (category axis line)
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Custom Axis Scale (50-250)",
|
|
dataRange="Sheet1!A1:E13",
|
|
x="0", y="0", width="12", height="18",
|
|
axisMin="50", axisMax="250", majorUnit="50",
|
|
minorUnit="25",
|
|
gridlines="D0D0D0:0.5:solid",
|
|
minorGridlines="EEEEEE:0.3:dot",
|
|
axisLine="C00000:1.5:solid",
|
|
catAxisLine="2E75B6:1.5:solid",
|
|
colors="4472C4,ED7D31,70AD47,FFC000"))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 2: Logarithmic scale with reversed axis
|
|
# Features: logBase=10 (logarithmic scale), axisReverse=true
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Log Scale (Base 10)",
|
|
series1="Growth:1,10,100,1000,5000",
|
|
categories="Year 1,Year 2,Year 3,Year 4,Year 5",
|
|
x="13", y="0", width="12", height="18",
|
|
logBase="10",
|
|
axisReverse="true",
|
|
colors="C00000",
|
|
axisTitle="Value (log)",
|
|
catTitle="Year",
|
|
gridlines="E0E0E0:0.5:dash"))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 3: Display units and axis number format
|
|
# Features: dispUnits=thousands, axisNumFmt=#,##0,
|
|
# majorTickMark=outside, minorTickMark=inside
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Revenue (in Thousands)",
|
|
series1="Revenue:12000,18500,22000,31000,45000,52000",
|
|
series2="Cost:8000,11000,14000,19500,28000,33000",
|
|
categories="2020,2021,2022,2023,2024,2025",
|
|
x="0", y="19", width="12", height="18",
|
|
dispUnits="thousands",
|
|
axisNumFmt="#,##0",
|
|
colors="2E75B6,C00000",
|
|
catTitle="Year", axisTitle="Amount (K)",
|
|
majorTickMark="outside", minorTickMark="inside",
|
|
legend="bottom"))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 4: Hidden axes with data table
|
|
# Features: gridlines=none, axisVisible=false, dataTable=true, legend=none
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Minimal Chart with Data Table",
|
|
dataRange="Sheet1!A1:E7",
|
|
x="13", y="19", width="12", height="18",
|
|
gridlines="none",
|
|
axisVisible="false",
|
|
dataTable="true",
|
|
legend="none",
|
|
colors="4472C4,ED7D31,70AD47,FFC000"))
|
|
doc.batch(items)
|
|
|
|
# ======================================================================
|
|
# Sheet: 5-Labels & Legend
|
|
# ======================================================================
|
|
print("--- 5-Labels & Legend ---")
|
|
s = "/5-Labels & Legend"
|
|
items = [add_sheet("5-Labels & Legend")]
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 1: Data labels with number format and styled label font
|
|
# Features: dataLabels=true, labelPos=outsideEnd, labelFont (size:color:bold),
|
|
# dataLabels.numFmt
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Sales with Labels",
|
|
series1="Revenue:120,180,210,250,280",
|
|
categories="Jan,Feb,Mar,Apr,May",
|
|
x="0", y="0", width="12", height="18",
|
|
colors="4472C4",
|
|
dataLabels="true", labelPos="outsideEnd",
|
|
labelFont="9:333333:true",
|
|
**{"dataLabels.numFmt": "#,##0"}))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 2: Custom individual labels — delete some, highlight peak
|
|
# Features: dataLabel{N}.delete, dataLabel{N}.text, point{N}.color
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Peak Highlight",
|
|
series1="Sales:88,120,165,210,195,178",
|
|
categories="Jan,Feb,Mar,Apr,May,Jun",
|
|
x="13", y="0", width="12", height="18",
|
|
colors="2E75B6",
|
|
dataLabels="true", labelPos="outsideEnd",
|
|
**{"dataLabel1.delete": "true", "dataLabel2.delete": "true",
|
|
"dataLabel3.delete": "true",
|
|
"point4.color": "C00000",
|
|
"dataLabel4.text": "Peak!",
|
|
"dataLabel5.delete": "true", "dataLabel6.delete": "true"}))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 3: Legend positioning and overlay with styled legend font
|
|
# Features: legend=right, legend.overlay=true, legendfont (size:color:fontname),
|
|
# plotFill
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Legend Overlay on Chart",
|
|
dataRange="Sheet1!A1:E7",
|
|
x="0", y="19", width="12", height="18",
|
|
colors="4472C4,ED7D31,70AD47,FFC000",
|
|
legend="right",
|
|
**{"legend.overlay": "true"},
|
|
legendfont="10:333333:Calibri",
|
|
plotFill="F5F5F5"))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 4: Manual layout — plotArea, title, and legend positioning
|
|
# Features: plotArea.x/y/w/h, title.x/y, legend.x/y/w/h (manual layout)
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Manual Layout Control",
|
|
dataRange="Sheet1!A1:E7",
|
|
x="13", y="19", width="12", height="18",
|
|
colors="2E75B6,ED7D31,70AD47,FFC000",
|
|
**{"plotArea.x": "0.12", "plotArea.y": "0.18",
|
|
"plotArea.w": "0.82", "plotArea.h": "0.55",
|
|
"title.x": "0.25", "title.y": "0.02",
|
|
"legend.x": "0.15", "legend.y": "0.82",
|
|
"legend.w": "0.7", "legend.h": "0.12",
|
|
"title.font": "Arial", "title.size": "13",
|
|
"title.bold": "true"}))
|
|
doc.batch(items)
|
|
|
|
# ======================================================================
|
|
# Sheet: 6-Effects & Advanced
|
|
# ======================================================================
|
|
print("--- 6-Effects & Advanced ---")
|
|
s = "/6-Effects & Advanced"
|
|
items = [add_sheet("6-Effects & Advanced")]
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 1: Secondary axis — dual Y-axis
|
|
# Features: secondaryAxis=2 (series 2 on right-hand axis)
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Revenue vs Growth Rate",
|
|
series1="Revenue:120,180,250,310,380,420",
|
|
series2="Growth %:50,33,39,24,23,11",
|
|
categories="2020,2021,2022,2023,2024,2025",
|
|
x="0", y="0", width="12", height="18",
|
|
secondaryAxis="2",
|
|
colors="2E75B6,C00000",
|
|
catTitle="Year", axisTitle="Revenue",
|
|
dataLabels="true", labelPos="outsideEnd",
|
|
legend="bottom"))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 2: Reference line (target/threshold)
|
|
# referenceLine format: value:color:width:dash
|
|
# Features: referenceLine (horizontal target line)
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="vs Target (150)",
|
|
dataRange="Sheet1!A1:C13",
|
|
x="13", y="0", width="12", height="18",
|
|
colors="4472C4,70AD47",
|
|
referenceLine="150:FF0000:1.5:dash",
|
|
legend="bottom"))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 3: Title glow and shadow effects
|
|
# Features: title.glow (color-radius-opacity), title.shadow,
|
|
# series.shadow on column charts
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Glow & Shadow Effects",
|
|
series1="East:120,135,148,162,155,178",
|
|
series2="West:110,118,130,145,138,162",
|
|
categories="Jan,Feb,Mar,Apr,May,Jun",
|
|
x="0", y="19", width="12", height="18",
|
|
colors="4472C4,ED7D31",
|
|
**{"title.glow": "4472C4-8-60",
|
|
"title.shadow": "000000-3-315-2-40",
|
|
"title.font": "Calibri", "title.size": "16",
|
|
"title.bold": "true", "title.color": "1F4E79",
|
|
"series.shadow": "000000-3-315-1-30"},
|
|
plotFill="F0F4F8", chartFill="FFFFFF"))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 4: Conditional coloring with chart/plot borders
|
|
# colorRule format: threshold:belowColor:aboveColor
|
|
# Features: colorRule (threshold-based conditional coloring),
|
|
# chartArea.border, plotArea.border
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Profit: Conditional Colors",
|
|
series1="Profit:80,120,-30,160,-50,200,140,-20,180,90",
|
|
categories="Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct",
|
|
x="13", y="19", width="12", height="18",
|
|
colors="2E75B6",
|
|
colorRule="0:C00000:70AD47",
|
|
referenceLine="0:888888:1:solid",
|
|
**{"chartArea.border": "D0D0D0:1:solid",
|
|
"plotArea.border": "E0E0E0:0.5:dot"},
|
|
dataLabels="true", labelPos="outsideEnd",
|
|
labelFont="8:666666:false"))
|
|
doc.batch(items)
|
|
|
|
# ======================================================================
|
|
# Sheet: 7-Bar Shape & Gap
|
|
# ======================================================================
|
|
print("--- 7-Bar Shape & Gap ---")
|
|
s = "/7-Bar Shape & Gap"
|
|
items = [add_sheet("7-Bar Shape & Gap")]
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 1: Narrow gap width (bars close together)
|
|
# Features: gapwidth=30 (narrow gaps between column groups)
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Narrow Gap (30%)",
|
|
dataRange="Sheet1!A1:E7",
|
|
x="0", y="0", width="12", height="18",
|
|
gapwidth="30",
|
|
colors="4472C4,ED7D31,70AD47,FFC000",
|
|
legend="bottom"))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 2: Wide gap with negative overlap (separated bars within group)
|
|
# Features: gapwidth=200 (wide gap), overlap=-50 (negative = bars separated)
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column",
|
|
title="Wide Gap + Negative Overlap",
|
|
dataRange="Sheet1!A1:E7",
|
|
x="13", y="0", width="12", height="18",
|
|
gapwidth="200",
|
|
overlap="-50",
|
|
colors="2E75B6,ED7D31,70AD47,FFC000",
|
|
legend="bottom"))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 3: 3D column with cylinder shape
|
|
# Features: shape=cylinder (3D column bar shape)
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column3d",
|
|
title="Cylinder Shape",
|
|
series1="East:120,135,148,162,155,178",
|
|
series2="South:95,108,115,128,142,155",
|
|
categories="Jan,Feb,Mar,Apr,May,Jun",
|
|
x="0", y="19", width="12", height="18",
|
|
shape="cylinder",
|
|
view3d="15,20,30",
|
|
colors="4472C4,ED7D31",
|
|
legend="bottom"))
|
|
|
|
# ------------------------------------------------------------------
|
|
# Chart 4: 3D column with cone/pyramid shapes
|
|
# Features: shape=cone (3D column bar shape — also supports pyramid)
|
|
# ------------------------------------------------------------------
|
|
items.append(chart(s,
|
|
chartType="column3d",
|
|
title="Cone Shape",
|
|
series1="North:88,92,105,118,125,138",
|
|
series2="West:110,118,130,145,138,162",
|
|
categories="Jan,Feb,Mar,Apr,May,Jun",
|
|
x="13", y="19", width="12", height="18",
|
|
shape="cone",
|
|
view3d="15,20,30",
|
|
colors="70AD47,FFC000",
|
|
legend="bottom"))
|
|
doc.batch(items)
|
|
|
|
doc.send({"command": "save"})
|
|
# context exit closes the resident, flushing the workbook to disk.
|
|
|
|
print(f"Generated: {FILE}")
|
|
print(" 8 sheets (Sheet1 data + 7 chart sheets, 28 charts total)")
|