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
703 lines
31 KiB
Python
703 lines
31 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Pivot Table Showcase — generates pivot-tables.xlsx with 17 pivot tables.
|
|
|
|
Each pivot table demonstrates different officecli features.
|
|
See pivot-tables.md for a guide to each sheet in the generated file.
|
|
|
|
SDK twin of pivot-tables.sh (officecli CLI). Both produce an equivalent
|
|
pivot-tables.xlsx. This one drives the **officecli Python SDK**
|
|
(`pip install officecli-sdk`): one resident is started, the 500+ source-data
|
|
cell writes ship in a single `doc.batch(...)` round-trip, and each pivot table
|
|
is one `add pivottable` item shipped over the named pipe. 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 pivot-tables.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__)), "pivot-tables.xlsx")
|
|
|
|
|
|
def add_sheet(name):
|
|
"""One `add sheet` item in batch-shape."""
|
|
return {"command": "add", "parent": "/", "type": "sheet", "props": {"name": name}}
|
|
|
|
|
|
def pivot(sheet, **props):
|
|
"""One `add pivottable` item in batch-shape, anchored on the given sheet."""
|
|
return {"command": "add", "parent": f"/{sheet}", "type": "pivottable", "props": props}
|
|
|
|
|
|
print(f"Building {FILE} ...")
|
|
|
|
with officecli.create(FILE, "--force") as doc:
|
|
|
|
# ==========================================================================
|
|
# Source data — batch is used here only for speed (500+ cell writes).
|
|
# ==========================================================================
|
|
print("\n--- Populating source data ---")
|
|
|
|
data_items = []
|
|
for j, h in enumerate(["Region", "Category", "Product", "Quarter", "Sales",
|
|
"Quantity", "Cost", "Channel", "Priority", "Date"]):
|
|
data_items.append({"command": "set", "path": f"/Sheet1/{'ABCDEFGHIJ'[j]}1",
|
|
"props": {"text": h}})
|
|
|
|
rows = [
|
|
("North", "Electronics", "Laptop", "Q1", 12500, 45, 7500, "Online", "High", "2025-01-15"),
|
|
("North", "Electronics", "Phone", "Q1", 8900, 120, 5340, "Retail", "High", "2025-02-10"),
|
|
("North", "Electronics", "Tablet", "Q2", 6200, 38, 3720, "Online", "Medium", "2025-04-20"),
|
|
("North", "Electronics", "Laptop", "Q2", 15800, 55, 9480, "Retail", "High", "2025-05-08"),
|
|
("North", "Electronics", "Phone", "Q3", 11200, 150, 6720, "Online", "High", "2025-07-12"),
|
|
("North", "Electronics", "Tablet", "Q4", 9500, 62, 5700, "Retail", "Medium", "2025-10-05"),
|
|
("North", "Clothing", "Jacket", "Q1", 4200, 85, 2100, "Retail", "Low", "2025-01-22"),
|
|
("North", "Clothing", "Shoes", "Q2", 5600, 70, 2800, "Online", "Medium", "2025-04-15"),
|
|
("North", "Clothing", "Hat", "Q3", 1800, 110, 900, "Retail", "Low", "2025-08-03"),
|
|
("North", "Clothing", "Jacket", "Q4", 7800, 95, 3900, "Online", "High", "2025-11-18"),
|
|
("North", "Food", "Coffee", "Q1", 2400, 200, 1200, "Retail", "Low", "2025-03-01"),
|
|
("North", "Food", "Snacks", "Q2", 1500, 180, 750, "Online", "Low", "2025-06-10"),
|
|
("North", "Food", "Juice", "Q3", 1900, 160, 950, "Retail", "Medium", "2025-09-20"),
|
|
("North", "Food", "Coffee", "Q4", 3200, 220, 1600, "Online", "Medium", "2025-12-01"),
|
|
("South", "Electronics", "Phone", "Q1", 18500, 200, 11100, "Online", "High", "2024-01-20"),
|
|
("South", "Electronics", "Laptop", "Q2", 22000, 72, 13200, "Retail", "High", "2024-05-15"),
|
|
("South", "Electronics", "Tablet", "Q3", 7800, 48, 4680, "Online", "Medium", "2024-08-22"),
|
|
("South", "Electronics", "Phone", "Q4", 14200, 165, 8520, "Retail", "High", "2024-11-30"),
|
|
("South", "Clothing", "Shoes", "Q1", 9200, 110, 4600, "Retail", "Medium", "2024-02-14"),
|
|
("South", "Clothing", "Jacket", "Q2", 6500, 78, 3250, "Online", "Low", "2024-06-01"),
|
|
("South", "Clothing", "Hat", "Q3", 3100, 130, 1550, "Retail", "Low", "2024-09-10"),
|
|
("South", "Clothing", "Shoes", "Q4", 8800, 98, 4400, "Online", "Medium", "2024-12-20"),
|
|
("South", "Food", "Juice", "Q1", 1800, 240, 900, "Retail", "Low", "2024-03-08"),
|
|
("South", "Food", "Coffee", "Q2", 3500, 280, 1750, "Online", "Medium", "2024-04-25"),
|
|
("South", "Food", "Snacks", "Q3", 2200, 190, 1100, "Retail", "Low", "2024-07-14"),
|
|
("South", "Food", "Juice", "Q4", 2800, 210, 1400, "Online", "Medium", "2024-10-18"),
|
|
("East", "Electronics", "Tablet", "Q1", 5400, 35, 3240, "Online", "Medium", "2025-02-28"),
|
|
("East", "Electronics", "Laptop", "Q2", 19500, 65, 11700, "Retail", "High", "2025-05-20"),
|
|
("East", "Electronics", "Phone", "Q3", 13800, 180, 8280, "Online", "High", "2025-08-15"),
|
|
("East", "Electronics", "Tablet", "Q4", 8200, 52, 4920, "Retail", "Medium", "2025-11-02"),
|
|
("East", "Clothing", "Hat", "Q1", 2800, 140, 1400, "Retail", "Low", "2025-01-05"),
|
|
("East", "Clothing", "Jacket", "Q2", 7200, 60, 3600, "Online", "Medium", "2025-06-18"),
|
|
("East", "Clothing", "Shoes", "Q3", 5500, 88, 2750, "Retail", "Medium", "2025-09-25"),
|
|
("East", "Clothing", "Hat", "Q4", 3600, 105, 1800, "Online", "Low", "2025-12-10"),
|
|
("East", "Food", "Snacks", "Q1", 1200, 300, 600, "Retail", "Low", "2025-03-15"),
|
|
("East", "Food", "Juice", "Q2", 2100, 170, 1050, "Online", "Medium", "2025-04-30"),
|
|
("East", "Food", "Coffee", "Q3", 2800, 230, 1400, "Retail", "Medium", "2025-07-22"),
|
|
("East", "Food", "Snacks", "Q4", 1600, 250, 800, "Online", "Low", "2025-10-28"),
|
|
("West", "Electronics", "Laptop", "Q1", 20500, 68, 12300, "Online", "High", "2024-01-10"),
|
|
("West", "Electronics", "Phone", "Q2", 16800, 190, 10080, "Retail", "High", "2024-04-05"),
|
|
("West", "Electronics", "Tablet", "Q3", 8900, 55, 5340, "Online", "Medium", "2024-08-12"),
|
|
("West", "Electronics", "Laptop", "Q4", 25000, 82, 15000, "Retail", "High", "2024-11-15"),
|
|
("West", "Clothing", "Jacket", "Q1", 11000, 88, 5500, "Retail", "Medium", "2024-02-22"),
|
|
("West", "Clothing", "Shoes", "Q2", 7500, 95, 3750, "Online", "Medium", "2024-05-30"),
|
|
("West", "Clothing", "Hat", "Q3", 4200, 120, 2100, "Retail", "Low", "2024-09-08"),
|
|
("West", "Clothing", "Jacket", "Q4", 13500, 105, 6750, "Online", "High", "2024-12-01"),
|
|
("West", "Food", "Coffee", "Q1", 4500, 350, 2250, "Online", "Medium", "2024-03-18"),
|
|
("West", "Food", "Snacks", "Q2", 2800, 280, 1400, "Online", "Medium", "2024-06-22"),
|
|
("West", "Food", "Juice", "Q3", 3200, 260, 1600, "Retail", "Low", "2024-07-30"),
|
|
("West", "Food", "Coffee", "Q4", 5800, 400, 2900, "Online", "High", "2024-10-25"),
|
|
]
|
|
C = "ABCDEFGHIJ"
|
|
for i, row in enumerate(rows):
|
|
for j, val in enumerate(row):
|
|
data_items.append({"command": "set", "path": f"/Sheet1/{C[j]}{i+2}",
|
|
"props": {"text": str(val)}})
|
|
|
|
data_items.append(add_sheet("CNData"))
|
|
for j, h in enumerate(["地区", "品类", "销售额"]):
|
|
data_items.append({"command": "set", "path": f"/CNData/{C[j]}1",
|
|
"props": {"text": h}})
|
|
for i, (r, c, s) in enumerate([
|
|
("华东", "电子产品", 18000), ("华东", "服装", 9500), ("华东", "食品", 4200),
|
|
("华南", "电子产品", 22000), ("华南", "服装", 12000), ("华南", "食品", 5800),
|
|
("华北", "电子产品", 15000), ("华北", "服装", 7800), ("华北", "食品", 3600),
|
|
("西南", "电子产品", 11000), ("西南", "服装", 6500), ("西南", "食品", 2900),
|
|
]):
|
|
for j, val in enumerate([r, c, s]):
|
|
data_items.append({"command": "set", "path": f"/CNData/{C[j]}{i+2}",
|
|
"props": {"text": str(val)}})
|
|
|
|
doc.batch(data_items)
|
|
|
|
# ==========================================================================
|
|
# 17 Pivot Tables
|
|
#
|
|
# Each section below shows the exact officecli command in a comment block,
|
|
# then ships it as an `add pivottable` batch item. You can copy any command
|
|
# block and run it in a terminal.
|
|
# ==========================================================================
|
|
|
|
# --------------------------------------------------------------------------
|
|
# Sheet: 1-Sales Overview
|
|
#
|
|
# officecli add pivot-tables.xlsx "/1-Sales Overview" --type pivottable \
|
|
# --prop source=Sheet1!A1:J51 \
|
|
# --prop rows=Region,Category \
|
|
# --prop cols=Quarter \
|
|
# --prop 'values=Sales:sum,Quantity:sum,Cost:sum:percent_of_row' \
|
|
# --prop 'filters=Channel,Priority' \
|
|
# --prop layout=tabular \
|
|
# --prop repeatlabels=true \
|
|
# --prop grandtotals=both \
|
|
# --prop subtotals=on \
|
|
# --prop sort=desc \
|
|
# --prop name=SalesOverview \
|
|
# --prop style=PivotStyleDark2
|
|
#
|
|
# Features: tabular layout, 2-level rows, column axis, 3 value fields,
|
|
# Cost as percent_of_row, dual page filters, repeat item labels, desc sort
|
|
# --------------------------------------------------------------------------
|
|
print("\n--- 1-Sales Overview ---")
|
|
doc.send(add_sheet("1-Sales Overview"))
|
|
doc.send(pivot("1-Sales Overview",
|
|
source="Sheet1!A1:J51",
|
|
rows="Region,Category",
|
|
cols="Quarter",
|
|
values="Sales:sum,Quantity:sum,Cost:sum:percent_of_row",
|
|
filters="Channel,Priority",
|
|
layout="tabular",
|
|
repeatlabels="true",
|
|
grandtotals="both",
|
|
subtotals="on",
|
|
sort="desc",
|
|
name="SalesOverview",
|
|
style="PivotStyleDark2"))
|
|
|
|
# --------------------------------------------------------------------------
|
|
# Sheet: 2-Market Share
|
|
#
|
|
# officecli add pivot-tables.xlsx "/2-Market Share" --type pivottable \
|
|
# --prop source=Sheet1!A1:J51 \
|
|
# --prop rows=Region \
|
|
# --prop cols=Category \
|
|
# --prop 'values=Sales:sum:percent_of_col' \
|
|
# --prop filters=Channel \
|
|
# --prop layout=outline \
|
|
# --prop grandtotals=both \
|
|
# --prop name=MarketShare \
|
|
# --prop style=PivotStyleMedium4
|
|
#
|
|
# Features: outline layout, percent_of_col (each region's share per category)
|
|
# --------------------------------------------------------------------------
|
|
print("\n--- 2-Market Share ---")
|
|
doc.send(add_sheet("2-Market Share"))
|
|
doc.send(pivot("2-Market Share",
|
|
source="Sheet1!A1:J51",
|
|
rows="Region",
|
|
cols="Category",
|
|
values="Sales:sum:percent_of_col",
|
|
filters="Channel",
|
|
layout="outline",
|
|
grandtotals="both",
|
|
name="MarketShare",
|
|
style="PivotStyleMedium4"))
|
|
|
|
# --------------------------------------------------------------------------
|
|
# Sheet: 3-Product Deep Dive
|
|
#
|
|
# officecli add pivot-tables.xlsx "/3-Product Deep Dive" --type pivottable \
|
|
# --prop source=Sheet1!A1:J51 \
|
|
# --prop rows=Category,Product \
|
|
# --prop 'values=Sales:sum,Sales:average,Sales:max,Quantity:sum,Cost:sum' \
|
|
# --prop filters=Region \
|
|
# --prop layout=tabular \
|
|
# --prop grandtotals=rows \
|
|
# --prop subtotals=on \
|
|
# --prop sort=desc \
|
|
# --prop name=ProductDeepDive \
|
|
# --prop style=PivotStyleMedium9
|
|
#
|
|
# Features: 5 value fields (sum, average, max), no column axis — values
|
|
# become column headers via synthetic "Values" axis, row grand totals only
|
|
# --------------------------------------------------------------------------
|
|
print("\n--- 3-Product Deep Dive ---")
|
|
doc.send(add_sheet("3-Product Deep Dive"))
|
|
doc.send(pivot("3-Product Deep Dive",
|
|
source="Sheet1!A1:J51",
|
|
rows="Category,Product",
|
|
values="Sales:sum,Sales:average,Sales:max,Quantity:sum,Cost:sum",
|
|
filters="Region",
|
|
layout="tabular",
|
|
grandtotals="rows",
|
|
subtotals="on",
|
|
sort="desc",
|
|
name="ProductDeepDive",
|
|
style="PivotStyleMedium9"))
|
|
|
|
# --------------------------------------------------------------------------
|
|
# Sheet: 4-Channel Analysis
|
|
#
|
|
# officecli add pivot-tables.xlsx "/4-Channel Analysis" --type pivottable \
|
|
# --prop source=Sheet1!A1:J51 \
|
|
# --prop rows=Channel \
|
|
# --prop cols=Quarter \
|
|
# --prop 'values=Sales:sum:percent_of_total,Quantity:sum' \
|
|
# --prop layout=outline \
|
|
# --prop grandtotals=both \
|
|
# --prop name=ChannelTrend \
|
|
# --prop style=PivotStyleLight21
|
|
#
|
|
# Features: percent_of_total (global share), no filters
|
|
# --------------------------------------------------------------------------
|
|
print("\n--- 4-Channel Analysis ---")
|
|
doc.send(add_sheet("4-Channel Analysis"))
|
|
doc.send(pivot("4-Channel Analysis",
|
|
source="Sheet1!A1:J51",
|
|
rows="Channel",
|
|
cols="Quarter",
|
|
values="Sales:sum:percent_of_total,Quantity:sum",
|
|
layout="outline",
|
|
grandtotals="both",
|
|
name="ChannelTrend",
|
|
style="PivotStyleLight21"))
|
|
|
|
# --------------------------------------------------------------------------
|
|
# Sheet: 5-Priority Matrix
|
|
#
|
|
# officecli add pivot-tables.xlsx "/5-Priority Matrix" --type pivottable \
|
|
# --prop source=Sheet1!A1:J51 \
|
|
# --prop rows=Priority,Region \
|
|
# --prop cols=Category \
|
|
# --prop 'values=Sales:sum,Cost:sum:percent_of_row' \
|
|
# --prop filters=Channel \
|
|
# --prop layout=tabular \
|
|
# --prop blankrows=true \
|
|
# --prop grandtotals=both \
|
|
# --prop subtotals=on \
|
|
# --prop sort=asc \
|
|
# --prop name=PriorityMatrix \
|
|
# --prop style=PivotStyleDark6
|
|
#
|
|
# Features: blankRows — empty line after each outer group for visual separation
|
|
# --------------------------------------------------------------------------
|
|
print("\n--- 5-Priority Matrix ---")
|
|
doc.send(add_sheet("5-Priority Matrix"))
|
|
doc.send(pivot("5-Priority Matrix",
|
|
source="Sheet1!A1:J51",
|
|
rows="Priority,Region",
|
|
cols="Category",
|
|
values="Sales:sum,Cost:sum:percent_of_row",
|
|
filters="Channel",
|
|
layout="tabular",
|
|
blankrows="true",
|
|
grandtotals="both",
|
|
subtotals="on",
|
|
sort="asc",
|
|
name="PriorityMatrix",
|
|
style="PivotStyleDark6"))
|
|
|
|
# --------------------------------------------------------------------------
|
|
# Sheet: 6-Compact 3-Level
|
|
#
|
|
# officecli add pivot-tables.xlsx "/6-Compact 3-Level" --type pivottable \
|
|
# --prop source=Sheet1!A1:J51 \
|
|
# --prop rows=Region,Category,Product \
|
|
# --prop 'values=Sales:sum,Quantity:sum' \
|
|
# --prop filters=Priority \
|
|
# --prop layout=compact \
|
|
# --prop grandtotals=both \
|
|
# --prop subtotals=on \
|
|
# --prop sort=desc \
|
|
# --prop name=Compact3Level \
|
|
# --prop style=PivotStyleMedium14
|
|
#
|
|
# Features: compact layout — 3-level hierarchy in one indented column
|
|
# --------------------------------------------------------------------------
|
|
print("\n--- 6-Compact 3-Level ---")
|
|
doc.send(add_sheet("6-Compact 3-Level"))
|
|
doc.send(pivot("6-Compact 3-Level",
|
|
source="Sheet1!A1:J51",
|
|
rows="Region,Category,Product",
|
|
values="Sales:sum,Quantity:sum",
|
|
filters="Priority",
|
|
layout="compact",
|
|
grandtotals="both",
|
|
subtotals="on",
|
|
sort="desc",
|
|
name="Compact3Level",
|
|
style="PivotStyleMedium14"))
|
|
|
|
# --------------------------------------------------------------------------
|
|
# Sheet: 7-No Subtotals
|
|
#
|
|
# officecli add pivot-tables.xlsx "/7-No Subtotals" --type pivottable \
|
|
# --prop source=Sheet1!A1:J51 \
|
|
# --prop rows=Region,Category \
|
|
# --prop cols=Quarter \
|
|
# --prop values=Sales:sum \
|
|
# --prop layout=tabular \
|
|
# --prop repeatlabels=true \
|
|
# --prop grandtotals=cols \
|
|
# --prop subtotals=off \
|
|
# --prop sort=asc \
|
|
# --prop name=FlatView \
|
|
# --prop style=PivotStyleLight1
|
|
#
|
|
# Features: subtotals=off (flat view), grandtotals=cols (bottom row only),
|
|
# repeatlabels=true (essential when subtotals off — otherwise outer labels vanish)
|
|
# --------------------------------------------------------------------------
|
|
print("\n--- 7-No Subtotals ---")
|
|
doc.send(add_sheet("7-No Subtotals"))
|
|
doc.send(pivot("7-No Subtotals",
|
|
source="Sheet1!A1:J51",
|
|
rows="Region,Category",
|
|
cols="Quarter",
|
|
values="Sales:sum",
|
|
layout="tabular",
|
|
repeatlabels="true",
|
|
grandtotals="cols",
|
|
subtotals="off",
|
|
sort="asc",
|
|
name="FlatView",
|
|
style="PivotStyleLight1"))
|
|
|
|
# --------------------------------------------------------------------------
|
|
# Sheet: 8-Date Grouping
|
|
#
|
|
# officecli add pivot-tables.xlsx "/8-Date Grouping" --type pivottable \
|
|
# --prop source=Sheet1!A1:J51 \
|
|
# --prop 'rows=Date:year,Date:quarter' \
|
|
# --prop 'values=Sales:sum,Cost:sum' \
|
|
# --prop filters=Region \
|
|
# --prop layout=outline \
|
|
# --prop grandtotals=both \
|
|
# --prop subtotals=on \
|
|
# --prop name=DateGrouping \
|
|
# --prop style=PivotStyleMedium7
|
|
#
|
|
# Features: automatic date grouping — Date:year creates "2024","2025" buckets,
|
|
# Date:quarter creates "2024-Q1",... sub-buckets. Uses native Excel fieldGroup XML.
|
|
# --------------------------------------------------------------------------
|
|
print("\n--- 8-Date Grouping ---")
|
|
doc.send(add_sheet("8-Date Grouping"))
|
|
doc.send(pivot("8-Date Grouping",
|
|
source="Sheet1!A1:J51",
|
|
rows="Date:year,Date:quarter",
|
|
values="Sales:sum,Cost:sum",
|
|
filters="Region",
|
|
layout="outline",
|
|
grandtotals="both",
|
|
subtotals="on",
|
|
name="DateGrouping",
|
|
style="PivotStyleMedium7"))
|
|
|
|
# --------------------------------------------------------------------------
|
|
# Sheet: 9-Top 5 Products
|
|
#
|
|
# officecli add pivot-tables.xlsx "/9-Top 5 Products" --type pivottable \
|
|
# --prop source=Sheet1!A1:J51 \
|
|
# --prop rows=Product \
|
|
# --prop 'values=Sales:sum,Quantity:sum,Cost:sum' \
|
|
# --prop layout=tabular \
|
|
# --prop grandtotals=none \
|
|
# --prop topN=5 \
|
|
# --prop sort=desc \
|
|
# --prop name=Top5Products \
|
|
# --prop style=PivotStyleDark1
|
|
#
|
|
# Features: topN=5 (only top 5 products by first value field), grandtotals=none
|
|
# --------------------------------------------------------------------------
|
|
print("\n--- 9-Top 5 Products ---")
|
|
doc.send(add_sheet("9-Top 5 Products"))
|
|
doc.send(pivot("9-Top 5 Products",
|
|
source="Sheet1!A1:J51",
|
|
rows="Product",
|
|
values="Sales:sum,Quantity:sum,Cost:sum",
|
|
layout="tabular",
|
|
grandtotals="none",
|
|
topN="5",
|
|
sort="desc",
|
|
name="Top5Products",
|
|
style="PivotStyleDark1"))
|
|
|
|
# --------------------------------------------------------------------------
|
|
# Sheet: 10-Ultimate
|
|
#
|
|
# officecli add pivot-tables.xlsx "/10-Ultimate" --type pivottable \
|
|
# --prop source=Sheet1!A1:J51 \
|
|
# --prop rows=Region,Category \
|
|
# --prop cols=Quarter \
|
|
# --prop 'values=Sales:sum,Quantity:average,Cost:sum:percent_of_row' \
|
|
# --prop 'filters=Channel,Priority' \
|
|
# --prop layout=tabular \
|
|
# --prop repeatlabels=true \
|
|
# --prop blankrows=true \
|
|
# --prop grandtotals=rows \
|
|
# --prop subtotals=on \
|
|
# --prop sort=desc \
|
|
# --prop name=UltimatePivot \
|
|
# --prop style=PivotStyleDark11
|
|
#
|
|
# Features: ALL features combined — tabular + repeatLabels + blankRows +
|
|
# dual filters + 3 mixed-aggregation values + row-only grand totals
|
|
# --------------------------------------------------------------------------
|
|
print("\n--- 10-Ultimate ---")
|
|
doc.send(add_sheet("10-Ultimate"))
|
|
doc.send(pivot("10-Ultimate",
|
|
source="Sheet1!A1:J51",
|
|
rows="Region,Category",
|
|
cols="Quarter",
|
|
values="Sales:sum,Quantity:average,Cost:sum:percent_of_row",
|
|
filters="Channel,Priority",
|
|
layout="tabular",
|
|
repeatlabels="true",
|
|
blankrows="true",
|
|
grandtotals="rows",
|
|
subtotals="on",
|
|
sort="desc",
|
|
name="UltimatePivot",
|
|
style="PivotStyleDark11"))
|
|
|
|
# --------------------------------------------------------------------------
|
|
# Sheet: 11-Chinese Locale
|
|
#
|
|
# officecli add pivot-tables.xlsx "/11-Chinese Locale" --type pivottable \
|
|
# --prop source=CNData!A1:C13 \
|
|
# --prop rows=地区,品类 \
|
|
# --prop values=销售额:sum \
|
|
# --prop layout=tabular \
|
|
# --prop grandtotals=both \
|
|
# --prop subtotals=on \
|
|
# --prop sort=locale \
|
|
# --prop grandTotalCaption=合计 \
|
|
# --prop name=ChineseLocale \
|
|
# --prop style=PivotStyleMedium2
|
|
#
|
|
# Features: sort=locale (Chinese pinyin: 华北 < 华东 < 华南 < 西南),
|
|
# grandTotalCaption=合计 (custom grand total label)
|
|
# --------------------------------------------------------------------------
|
|
print("\n--- 11-Chinese Locale ---")
|
|
doc.send(add_sheet("11-Chinese Locale"))
|
|
doc.send(pivot("11-Chinese Locale",
|
|
source="CNData!A1:C13",
|
|
rows="地区,品类",
|
|
values="销售额:sum",
|
|
layout="tabular",
|
|
grandtotals="both",
|
|
subtotals="on",
|
|
sort="locale",
|
|
grandTotalCaption="合计",
|
|
name="ChineseLocale",
|
|
style="PivotStyleMedium2"))
|
|
|
|
# --------------------------------------------------------------------------
|
|
# Sheet: 12-Position + Aggregates
|
|
#
|
|
# officecli add pivot-tables.xlsx "/12-Position + Aggregates" --type pivottable \
|
|
# --prop source=Sheet1!A1:J51 \
|
|
# --prop position=D2 \
|
|
# --prop rows=Category \
|
|
# --prop 'values=Sales:count,Quantity:min,Quantity:product,Sales:countNums' \
|
|
# --prop aggregate=avg \
|
|
# --prop layout=tabular \
|
|
# --prop grandtotals=both \
|
|
# --prop name=PositionAggs \
|
|
# --prop style=PivotStyleLight16
|
|
#
|
|
# Features: position=D2 (anchor cell override, default is auto-place after source),
|
|
# aggregate=avg (default agg when omitted from a value tuple),
|
|
# value aggregations: count, min, product, countNums (sum/avg/max shown elsewhere)
|
|
# --------------------------------------------------------------------------
|
|
print("\n--- 12-Position + Aggregates ---")
|
|
doc.send(add_sheet("12-Position + Aggregates"))
|
|
doc.send(pivot("12-Position + Aggregates",
|
|
source="Sheet1!A1:J51",
|
|
position="D2",
|
|
rows="Category",
|
|
values="Sales:count,Quantity:min,Quantity:product,Sales:countNums",
|
|
aggregate="avg",
|
|
layout="tabular",
|
|
grandtotals="both",
|
|
name="PositionAggs",
|
|
style="PivotStyleLight16"))
|
|
|
|
# --------------------------------------------------------------------------
|
|
# Sheet: 13-Calculated Field
|
|
#
|
|
# officecli add pivot-tables.xlsx "/13-Calculated Field" --type pivottable \
|
|
# --prop source=Sheet1!A1:J51 \
|
|
# --prop 'calculatedField1=Margin:=Sales-Cost' \
|
|
# --prop 'calculatedField2=Tax:=Sales*0.1' \
|
|
# --prop rows=Region \
|
|
# --prop values=Sales:sum \
|
|
# --prop 'labelFilter=Region:beginsWith:N' \
|
|
# --prop layout=tabular \
|
|
# --prop grandtotals=both \
|
|
# --prop name=CalcField \
|
|
# --prop style=PivotStyleMedium3
|
|
#
|
|
# Features: calculatedField1/2 — user-defined formula fields auto-added as
|
|
# data fields (no need to mention in values=). labelFilter — pre-cache row
|
|
# filter ('Region:beginsWith:N' keeps only Region values starting with N).
|
|
# --------------------------------------------------------------------------
|
|
print("\n--- 13-Calculated Field ---")
|
|
doc.send(add_sheet("13-Calculated Field"))
|
|
doc.send(pivot("13-Calculated Field",
|
|
source="Sheet1!A1:J51",
|
|
calculatedField1="Margin:=Sales-Cost",
|
|
calculatedField2="Tax:=Sales*0.1",
|
|
rows="Region",
|
|
values="Sales:sum",
|
|
labelFilter="Region:beginsWith:N",
|
|
layout="tabular",
|
|
grandtotals="both",
|
|
name="CalcField",
|
|
style="PivotStyleMedium3"))
|
|
|
|
# --------------------------------------------------------------------------
|
|
# Sheet: 14-Statistical
|
|
#
|
|
# officecli add pivot-tables.xlsx "/14-Statistical" --type pivottable \
|
|
# --prop source=Sheet1!A1:J51 \
|
|
# --prop rows=Region \
|
|
# --prop cols=Quarter \
|
|
# --prop 'values=Sales:var,Sales:varP,Sales:sum' \
|
|
# --prop showDataAs=running_total \
|
|
# --prop layout=tabular \
|
|
# --prop grandtotals=both \
|
|
# --prop name=Statistical \
|
|
# --prop style=PivotStyleLight10
|
|
#
|
|
# Features: var / varP (sample + population variance) — completes the aggregate
|
|
# set. showDataAs=running_total as a standalone --prop (vs the tuple form
|
|
# 'Field:agg:mode'); applies as default display for all value fields.
|
|
# --------------------------------------------------------------------------
|
|
print("\n--- 14-Statistical ---")
|
|
doc.send(add_sheet("14-Statistical"))
|
|
doc.send(pivot("14-Statistical",
|
|
source="Sheet1!A1:J51",
|
|
rows="Region",
|
|
cols="Quarter",
|
|
values="Sales:var,Sales:varP,Sales:sum",
|
|
showDataAs="running_total",
|
|
layout="tabular",
|
|
grandtotals="both",
|
|
name="Statistical",
|
|
style="PivotStyleLight10"))
|
|
|
|
# --------------------------------------------------------------------------
|
|
# Sheet: 15-Independent Totals
|
|
#
|
|
# officecli add pivot-tables.xlsx "/15-Independent Totals" --type pivottable \
|
|
# --prop source=CNData!A1:C13 \
|
|
# --prop rows=地区 \
|
|
# --prop cols=品类 \
|
|
# --prop values=销售额:sum \
|
|
# --prop rowGrandTotals=true \
|
|
# --prop colGrandTotals=false \
|
|
# --prop defaultSubtotal=true \
|
|
# --prop layout=outline \
|
|
# --prop subtotals=on \
|
|
# --prop sort=locale-desc \
|
|
# --prop name=IndepTotals \
|
|
# --prop style=PivotStyleMedium11
|
|
#
|
|
# Features: rowGrandTotals + colGrandTotals as independent toggles
|
|
# (vs the combined grandtotals=both/rows/cols/none), defaultSubtotal=true
|
|
# (default-subtotal flag on every pivotField), sort=locale-desc (reverse
|
|
# pinyin: 西南 > 华南 > 华东 > 华北).
|
|
# --------------------------------------------------------------------------
|
|
print("\n--- 15-Independent Totals ---")
|
|
doc.send(add_sheet("15-Independent Totals"))
|
|
doc.send(pivot("15-Independent Totals",
|
|
source="CNData!A1:C13",
|
|
rows="地区",
|
|
cols="品类",
|
|
values="销售额:sum",
|
|
rowGrandTotals="true",
|
|
colGrandTotals="false",
|
|
defaultSubtotal="true",
|
|
layout="outline",
|
|
subtotals="on",
|
|
sort="locale-desc",
|
|
name="IndepTotals",
|
|
style="PivotStyleMedium11"))
|
|
|
|
# --------------------------------------------------------------------------
|
|
# Sheet: 16-Style Flags
|
|
#
|
|
# officecli add pivot-tables.xlsx "/16-Style Flags" --type pivottable \
|
|
# --prop source=Sheet1!A1:J51 \
|
|
# --prop rows=Region,Category \
|
|
# --prop cols=Quarter \
|
|
# --prop values=Sales:sum \
|
|
# --prop showRowStripes=true \
|
|
# --prop showColStripes=true \
|
|
# --prop showRowHeaders=true \
|
|
# --prop showColHeaders=true \
|
|
# --prop showLastColumn=true \
|
|
# --prop layout=tabular \
|
|
# --prop grandtotals=both \
|
|
# --prop name=StyleFlags \
|
|
# --prop style=PivotStyleMedium17
|
|
#
|
|
# Features: every pivotTableStyleInfo flag wired up — row/col banding,
|
|
# row/col header emphasis, last-column highlight. These map to the five
|
|
# checkboxes in Excel's PivotTable Styles ribbon.
|
|
# --------------------------------------------------------------------------
|
|
print("\n--- 16-Style Flags ---")
|
|
doc.send(add_sheet("16-Style Flags"))
|
|
doc.send(pivot("16-Style Flags",
|
|
source="Sheet1!A1:J51",
|
|
rows="Region,Category",
|
|
cols="Quarter",
|
|
values="Sales:sum",
|
|
showRowStripes="true",
|
|
showColStripes="true",
|
|
showRowHeaders="true",
|
|
showColHeaders="true",
|
|
showLastColumn="true",
|
|
layout="tabular",
|
|
grandtotals="both",
|
|
name="StyleFlags",
|
|
style="PivotStyleMedium17"))
|
|
|
|
# --------------------------------------------------------------------------
|
|
# Sheet: 17-Display Toggles
|
|
#
|
|
# officecli add pivot-tables.xlsx "/17-Display Toggles" --type pivottable \
|
|
# --prop source=Sheet1!A1:J51 \
|
|
# --prop rows=Region,Category \
|
|
# --prop values=Sales:sum \
|
|
# --prop showDrill=false \
|
|
# --prop mergeLabels=true \
|
|
# --prop layout=outline \
|
|
# --prop grandtotals=both \
|
|
# --prop subtotals=on \
|
|
# --prop name=DisplayToggles \
|
|
# --prop style=PivotStyleLight19
|
|
#
|
|
# Features: showDrill=false (hide +/- expand-collapse buttons on every field),
|
|
# mergeLabels=true (merge & center repeated outer-axis item cells —
|
|
# <pivotTableDefinition mergeItem='1'>).
|
|
# --------------------------------------------------------------------------
|
|
print("\n--- 17-Display Toggles ---")
|
|
doc.send(add_sheet("17-Display Toggles"))
|
|
doc.send(pivot("17-Display Toggles",
|
|
source="Sheet1!A1:J51",
|
|
rows="Region,Category",
|
|
values="Sales:sum",
|
|
showDrill="false",
|
|
mergeLabels="true",
|
|
layout="outline",
|
|
grandtotals="both",
|
|
subtotals="on",
|
|
name="DisplayToggles",
|
|
style="PivotStyleLight19"))
|
|
|
|
doc.send({"command": "save"})
|
|
|
|
print(f"\nDone! Generated: {FILE}")
|
|
print(" 19 sheets (Sheet1 + CNData + 17 pivot tables)")
|