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
402 lines
16 KiB
Bash
Executable File
402 lines
16 KiB
Bash
Executable File
#!/bin/bash
|
|
# Pivot Table Showcase — generates pivot-tables.xlsx with 17 pivot tables.
|
|
# CLI twin of pivot-tables.py (officecli Python SDK). Both produce an
|
|
# equivalent pivot-tables.xlsx. See pivot-tables.md for a per-sheet guide.
|
|
#
|
|
# Usage: ./pivot-tables.sh [officecli path]
|
|
# NOTE: intentionally NO `set -e`. Like the SDK twin's doc.batch, this script
|
|
# tolerates forward-compat 'UNSUPPORTED props' warnings (officecli exit 2) and
|
|
# keeps building so the full document is produced.
|
|
CLI="${1:-officecli}"
|
|
FILE="$(dirname "$0")/pivot-tables.xlsx"
|
|
|
|
rm -f "$FILE"
|
|
$CLI create "$FILE"
|
|
$CLI open "$FILE"
|
|
|
|
# ==========================================================================
|
|
# Source data
|
|
# ==========================================================================
|
|
$CLI set "$FILE" /Sheet1/A1 --prop text=Region
|
|
$CLI set "$FILE" /Sheet1/B1 --prop text=Category
|
|
$CLI set "$FILE" /Sheet1/C1 --prop text=Product
|
|
$CLI set "$FILE" /Sheet1/D1 --prop text=Quarter
|
|
$CLI set "$FILE" /Sheet1/E1 --prop text=Sales
|
|
$CLI set "$FILE" /Sheet1/F1 --prop text=Quantity
|
|
$CLI set "$FILE" /Sheet1/G1 --prop text=Cost
|
|
$CLI set "$FILE" /Sheet1/H1 --prop text=Channel
|
|
$CLI set "$FILE" /Sheet1/I1 --prop text=Priority
|
|
$CLI set "$FILE" /Sheet1/J1 --prop text=Date
|
|
|
|
# rows: Region Category Product Quarter Sales Quantity Cost Channel Priority Date
|
|
write_row() {
|
|
local r="$1"; shift
|
|
local cols=(A B C D E F G H I J)
|
|
local i=0
|
|
for v in "$@"; do
|
|
$CLI set "$FILE" "/Sheet1/${cols[$i]}${r}" --prop text="$v"
|
|
i=$((i + 1))
|
|
done
|
|
}
|
|
|
|
write_row 2 North Electronics Laptop Q1 12500 45 7500 Online High 2025-01-15
|
|
write_row 3 North Electronics Phone Q1 8900 120 5340 Retail High 2025-02-10
|
|
write_row 4 North Electronics Tablet Q2 6200 38 3720 Online Medium 2025-04-20
|
|
write_row 5 North Electronics Laptop Q2 15800 55 9480 Retail High 2025-05-08
|
|
write_row 6 North Electronics Phone Q3 11200 150 6720 Online High 2025-07-12
|
|
write_row 7 North Electronics Tablet Q4 9500 62 5700 Retail Medium 2025-10-05
|
|
write_row 8 North Clothing Jacket Q1 4200 85 2100 Retail Low 2025-01-22
|
|
write_row 9 North Clothing Shoes Q2 5600 70 2800 Online Medium 2025-04-15
|
|
write_row 10 North Clothing Hat Q3 1800 110 900 Retail Low 2025-08-03
|
|
write_row 11 North Clothing Jacket Q4 7800 95 3900 Online High 2025-11-18
|
|
write_row 12 North Food Coffee Q1 2400 200 1200 Retail Low 2025-03-01
|
|
write_row 13 North Food Snacks Q2 1500 180 750 Online Low 2025-06-10
|
|
write_row 14 North Food Juice Q3 1900 160 950 Retail Medium 2025-09-20
|
|
write_row 15 North Food Coffee Q4 3200 220 1600 Online Medium 2025-12-01
|
|
write_row 16 South Electronics Phone Q1 18500 200 11100 Online High 2024-01-20
|
|
write_row 17 South Electronics Laptop Q2 22000 72 13200 Retail High 2024-05-15
|
|
write_row 18 South Electronics Tablet Q3 7800 48 4680 Online Medium 2024-08-22
|
|
write_row 19 South Electronics Phone Q4 14200 165 8520 Retail High 2024-11-30
|
|
write_row 20 South Clothing Shoes Q1 9200 110 4600 Retail Medium 2024-02-14
|
|
write_row 21 South Clothing Jacket Q2 6500 78 3250 Online Low 2024-06-01
|
|
write_row 22 South Clothing Hat Q3 3100 130 1550 Retail Low 2024-09-10
|
|
write_row 23 South Clothing Shoes Q4 8800 98 4400 Online Medium 2024-12-20
|
|
write_row 24 South Food Juice Q1 1800 240 900 Retail Low 2024-03-08
|
|
write_row 25 South Food Coffee Q2 3500 280 1750 Online Medium 2024-04-25
|
|
write_row 26 South Food Snacks Q3 2200 190 1100 Retail Low 2024-07-14
|
|
write_row 27 South Food Juice Q4 2800 210 1400 Online Medium 2024-10-18
|
|
write_row 28 East Electronics Tablet Q1 5400 35 3240 Online Medium 2025-02-28
|
|
write_row 29 East Electronics Laptop Q2 19500 65 11700 Retail High 2025-05-20
|
|
write_row 30 East Electronics Phone Q3 13800 180 8280 Online High 2025-08-15
|
|
write_row 31 East Electronics Tablet Q4 8200 52 4920 Retail Medium 2025-11-02
|
|
write_row 32 East Clothing Hat Q1 2800 140 1400 Retail Low 2025-01-05
|
|
write_row 33 East Clothing Jacket Q2 7200 60 3600 Online Medium 2025-06-18
|
|
write_row 34 East Clothing Shoes Q3 5500 88 2750 Retail Medium 2025-09-25
|
|
write_row 35 East Clothing Hat Q4 3600 105 1800 Online Low 2025-12-10
|
|
write_row 36 East Food Snacks Q1 1200 300 600 Retail Low 2025-03-15
|
|
write_row 37 East Food Juice Q2 2100 170 1050 Online Medium 2025-04-30
|
|
write_row 38 East Food Coffee Q3 2800 230 1400 Retail Medium 2025-07-22
|
|
write_row 39 East Food Snacks Q4 1600 250 800 Online Low 2025-10-28
|
|
write_row 40 West Electronics Laptop Q1 20500 68 12300 Online High 2024-01-10
|
|
write_row 41 West Electronics Phone Q2 16800 190 10080 Retail High 2024-04-05
|
|
write_row 42 West Electronics Tablet Q3 8900 55 5340 Online Medium 2024-08-12
|
|
write_row 43 West Electronics Laptop Q4 25000 82 15000 Retail High 2024-11-15
|
|
write_row 44 West Clothing Jacket Q1 11000 88 5500 Retail Medium 2024-02-22
|
|
write_row 45 West Clothing Shoes Q2 7500 95 3750 Online Medium 2024-05-30
|
|
write_row 46 West Clothing Hat Q3 4200 120 2100 Retail Low 2024-09-08
|
|
write_row 47 West Clothing Jacket Q4 13500 105 6750 Online High 2024-12-01
|
|
write_row 48 West Food Coffee Q1 4500 350 2250 Online Medium 2024-03-18
|
|
write_row 49 West Food Snacks Q2 2800 280 1400 Online Medium 2024-06-22
|
|
write_row 50 West Food Juice Q3 3200 260 1600 Retail Low 2024-07-30
|
|
write_row 51 West Food Coffee Q4 5800 400 2900 Online High 2024-10-25
|
|
|
|
# Chinese-locale source sheet
|
|
$CLI add "$FILE" / --type sheet --prop name=CNData
|
|
$CLI set "$FILE" /CNData/A1 --prop text=地区
|
|
$CLI set "$FILE" /CNData/B1 --prop text=品类
|
|
$CLI set "$FILE" /CNData/C1 --prop text=销售额
|
|
|
|
write_cn() { $CLI set "$FILE" "/CNData/A$1" --prop text="$2"; $CLI set "$FILE" "/CNData/B$1" --prop text="$3"; $CLI set "$FILE" "/CNData/C$1" --prop text="$4"; }
|
|
write_cn 2 华东 电子产品 18000
|
|
write_cn 3 华东 服装 9500
|
|
write_cn 4 华东 食品 4200
|
|
write_cn 5 华南 电子产品 22000
|
|
write_cn 6 华南 服装 12000
|
|
write_cn 7 华南 食品 5800
|
|
write_cn 8 华北 电子产品 15000
|
|
write_cn 9 华北 服装 7800
|
|
write_cn 10 华北 食品 3600
|
|
write_cn 11 西南 电子产品 11000
|
|
write_cn 12 西南 服装 6500
|
|
write_cn 13 西南 食品 2900
|
|
|
|
# ==========================================================================
|
|
# 17 Pivot Tables
|
|
# ==========================================================================
|
|
|
|
# Sheet: 1-Sales Overview
|
|
# Features: tabular layout, 2-level rows, column axis, 3 value fields,
|
|
# Cost as percent_of_row, dual page filters, repeat item labels, desc sort
|
|
$CLI add "$FILE" / --type sheet --prop name="1-Sales Overview"
|
|
$CLI add "$FILE" "/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
|
|
|
|
# Sheet: 2-Market Share
|
|
# Features: outline layout, percent_of_col (each region's share per category)
|
|
$CLI add "$FILE" / --type sheet --prop name="2-Market Share"
|
|
$CLI add "$FILE" "/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
|
|
|
|
# Sheet: 3-Product Deep Dive
|
|
# Features: 5 value fields (sum, average, max), no column axis — values
|
|
# become column headers via synthetic "Values" axis, row grand totals only
|
|
$CLI add "$FILE" / --type sheet --prop name="3-Product Deep Dive"
|
|
$CLI add "$FILE" "/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
|
|
|
|
# Sheet: 4-Channel Analysis
|
|
# Features: percent_of_total (global share), no filters
|
|
$CLI add "$FILE" / --type sheet --prop name="4-Channel Analysis"
|
|
$CLI add "$FILE" "/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
|
|
|
|
# Sheet: 5-Priority Matrix
|
|
# Features: blankRows — empty line after each outer group for visual separation
|
|
$CLI add "$FILE" / --type sheet --prop name="5-Priority Matrix"
|
|
$CLI add "$FILE" "/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
|
|
|
|
# Sheet: 6-Compact 3-Level
|
|
# Features: compact layout — 3-level hierarchy in one indented column
|
|
$CLI add "$FILE" / --type sheet --prop name="6-Compact 3-Level"
|
|
$CLI add "$FILE" "/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
|
|
|
|
# Sheet: 7-No Subtotals
|
|
# Features: subtotals=off (flat view), grandtotals=cols (bottom row only),
|
|
# repeatlabels=true (essential when subtotals off — otherwise outer labels vanish)
|
|
$CLI add "$FILE" / --type sheet --prop name="7-No Subtotals"
|
|
$CLI add "$FILE" "/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
|
|
|
|
# Sheet: 8-Date Grouping
|
|
# Features: automatic date grouping — Date:year creates "2024","2025" buckets,
|
|
# Date:quarter creates "2024-Q1",... sub-buckets. Uses native Excel fieldGroup XML.
|
|
$CLI add "$FILE" / --type sheet --prop name="8-Date Grouping"
|
|
$CLI add "$FILE" "/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
|
|
|
|
# Sheet: 9-Top 5 Products
|
|
# Features: topN=5 (only top 5 products by first value field), grandtotals=none
|
|
$CLI add "$FILE" / --type sheet --prop name="9-Top 5 Products"
|
|
$CLI add "$FILE" "/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
|
|
|
|
# Sheet: 10-Ultimate
|
|
# Features: ALL features combined — tabular + repeatLabels + blankRows +
|
|
# dual filters + 3 mixed-aggregation values + row-only grand totals
|
|
$CLI add "$FILE" / --type sheet --prop name="10-Ultimate"
|
|
$CLI add "$FILE" "/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
|
|
|
|
# Sheet: 11-Chinese Locale
|
|
# Features: sort=locale (Chinese pinyin: 华北 < 华东 < 华南 < 西南),
|
|
# grandTotalCaption=合计 (custom grand total label)
|
|
$CLI add "$FILE" / --type sheet --prop name="11-Chinese Locale"
|
|
$CLI add "$FILE" "/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
|
|
|
|
# Sheet: 12-Position + Aggregates
|
|
# 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)
|
|
$CLI add "$FILE" / --type sheet --prop name="12-Position + Aggregates"
|
|
$CLI add "$FILE" "/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
|
|
|
|
# Sheet: 13-Calculated Field
|
|
# 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).
|
|
$CLI add "$FILE" / --type sheet --prop name="13-Calculated Field"
|
|
$CLI add "$FILE" "/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
|
|
|
|
# Sheet: 14-Statistical
|
|
# 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.
|
|
$CLI add "$FILE" / --type sheet --prop name="14-Statistical"
|
|
$CLI add "$FILE" "/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
|
|
|
|
# Sheet: 15-Independent Totals
|
|
# 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: 西南 > 华南 > 华东 > 华北).
|
|
$CLI add "$FILE" / --type sheet --prop name="15-Independent Totals"
|
|
$CLI add "$FILE" "/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
|
|
|
|
# Sheet: 16-Style Flags
|
|
# 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.
|
|
$CLI add "$FILE" / --type sheet --prop name="16-Style Flags"
|
|
$CLI add "$FILE" "/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
|
|
|
|
# Sheet: 17-Display Toggles
|
|
# Features: showDrill=false (hide +/- expand-collapse buttons on every field),
|
|
# mergeLabels=true (merge & center repeated outer-axis item cells —
|
|
# <pivotTableDefinition mergeItem='1'>).
|
|
$CLI add "$FILE" / --type sheet --prop name="17-Display Toggles"
|
|
$CLI add "$FILE" "/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
|
|
|
|
$CLI close "$FILE"
|
|
|
|
$CLI validate "$FILE"
|
|
echo "Generated: $FILE"
|
|
echo " 19 sheets (Sheet1 + CNData + 17 pivot tables)"
|