#!/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 — # ). # -------------------------------------------------------------------------- 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)")