#!/bin/bash # Extended Chart Types Showcase — full feature coverage for waterfall, funnel, # treemap, sunburst, histogram, boxWhisker (cx:chart family) plus pareto and # chart-meta knobs (anchor, preset, autotitledeleted, plotvisonly). # # CLI twin of charts-extended.py (officecli Python SDK). Both produce an # equivalent charts-extended.xlsx. # # Usage: # ./charts-extended.sh # 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. FILE="$(dirname "$0")/charts-extended.xlsx" rm -f "$FILE" officecli create "$FILE" officecli open "$FILE" # ========================================================================== # Sheet 1: Waterfall & Funnel # ========================================================================== officecli add "$FILE" / --type sheet --prop name="1-Waterfall & Funnel" # Chart 1: Waterfall — increase/decrease/total colors + data labels + title glow # Features: chartType=waterfall, increaseColor, decreaseColor, totalColor, # dataLabels, title.glow officecli add "$FILE" "/1-Waterfall & Funnel" --type chart \ --prop chartType=waterfall \ --prop title="Cash Flow Bridge" \ --prop data=Start:1000,Revenue:500,Costs:-300,Tax:-100,Net:1100 \ --prop increaseColor=70AD47 \ --prop decreaseColor=FF0000 \ --prop totalColor=4472C4 \ --prop dataLabels=true \ --prop title.glow=00D2FF-6-60 \ --prop x=0 --prop y=0 --prop width=13 --prop height=18 # Chart 2: Waterfall — chart-area fill + legend + custom label font # Features: waterfall with legend=bottom, chartFill (solid hex — cx charts # don't support gradient fills, use plain RGB), labelFont "size:color:bold" officecli add "$FILE" "/1-Waterfall & Funnel" --type chart \ --prop chartType=waterfall \ --prop title="Budget vs Actual" \ --prop data=Budget:5000,Sales:2000,Marketing:-800,Ops:-600,Net:5600 \ --prop increaseColor=2E75B6 \ --prop decreaseColor=C00000 \ --prop totalColor=FFC000 \ --prop legend=bottom \ --prop chartFill=F0F4FA \ --prop dataLabels=true \ --prop labelFont=9:333333:true \ --prop x=14 --prop y=0 --prop width=13 --prop height=18 # Chart 3: Funnel — sales pipeline with title shadow # Features: chartType=funnel, descending pipeline values, dataLabels, # title.shadow "COLOR-BLUR-ANGLE-DIST-OPACITY" officecli add "$FILE" "/1-Waterfall & Funnel" --type chart \ --prop chartType=funnel \ --prop title="Sales Pipeline" \ --prop series1=Pipeline:1200,850,600,300,120 \ --prop categories=Leads,Qualified,Proposal,Negotiation,Won \ --prop dataLabels=true \ --prop title.shadow=000000-4-45-2-40 \ --prop x=0 --prop y=19 --prop width=13 --prop height=18 # Chart 4: Funnel — marketing conversion + legend/axis fonts # Features: funnel, legendfont "size:color:fontname", axisfont, 6-stage, dataLabels # NOTE: `colors=` palette is intentionally omitted. On cx:chart single-series # types (funnel/treemap/sunburst) the CLI only applies the first palette color # to the whole series, so all bars would render the same color. Let Excel's # theme pick the default accent color. officecli add "$FILE" "/1-Waterfall & Funnel" --type chart \ --prop chartType=funnel \ --prop title="Marketing Funnel" \ --prop series1=Users:10000,6500,3200,1800,900,450 \ --prop categories=Impressions,Clicks,Signups,Active,Paying,Retained \ --prop dataLabels=true \ --prop "legendfont=9:8B949E:Helvetica Neue" \ --prop "axisfont=10:58626E:Helvetica Neue" \ --prop x=14 --prop y=19 --prop width=13 --prop height=18 # ========================================================================== # Sheet 2: Treemap & Sunburst # ========================================================================== officecli add "$FILE" / --type sheet --prop name="2-Treemap & Sunburst" # Chart 1: Treemap — parentLabelLayout=overlapping + dataLabels # Features: chartType=treemap, parentLabelLayout=overlapping, dataLabels. # NOTE: `colors=` is omitted — see Funnel Chart 4 note: cx single-series # charts only pick up the first palette color. Excel's theme auto-rainbows. officecli add "$FILE" "/2-Treemap & Sunburst" --type chart \ --prop chartType=treemap \ --prop title="Revenue by Product" \ --prop series1=Revenue:450,380,310,280,210,180,150,120 \ --prop categories=Laptops,Phones,Tablets,TVs,Cameras,Audio,Gaming,Wearables \ --prop parentLabelLayout=overlapping \ --prop dataLabels=true \ --prop x=0 --prop y=0 --prop width=13 --prop height=18 # Chart 2: Treemap — parentLabelLayout=banner + bold title # Features: treemap parentLabelLayout=banner, title.bold/size/color officecli add "$FILE" "/2-Treemap & Sunburst" --type chart \ --prop chartType=treemap \ --prop title="Department Budget" \ --prop series1=Budget:900,750,600,500,420,350,280 \ --prop categories=Engineering,Sales,Marketing,Support,Finance,HR,Legal \ --prop parentLabelLayout=banner \ --prop title.bold=true \ --prop title.size=14 \ --prop title.color=2E5090 \ --prop x=14 --prop y=0 --prop width=13 --prop height=18 # Chart 3: Treemap — parentLabelLayout=none (no parent label strip) # Features: treemap parentLabelLayout=none (all labels inline, no header strip), # dataLabels on leaf tiles officecli add "$FILE" "/2-Treemap & Sunburst" --type chart \ --prop chartType=treemap \ --prop title="Flat Treemap (no parent labels)" \ --prop series1=Units:250,200,180,160,140,120,100,80,60,40 \ --prop categories=A,B,C,D,E,F,G,H,I,J \ --prop parentLabelLayout=none \ --prop dataLabels=true \ --prop x=0 --prop y=19 --prop width=13 --prop height=18 # Chart 4: Sunburst — radial hierarchy + chartFill (solid) + plotFill # Features: chartType=sunburst, radial hierarchical layout, chartFill (solid hex), # plotFill (solid hex), dataLabels. # NOTE 1: cx:chart's chart/plot fill only accepts solid color — not gradient # (unlike regular cChart). Use a single hex like "F8FAFC" or "none". # NOTE 2: `colors=` palette is omitted — cx single-series charts paint only # the first palette entry. Let Excel's theme drive per-segment coloring. officecli add "$FILE" "/2-Treemap & Sunburst" --type chart \ --prop chartType=sunburst \ --prop title="Market Share by Region" \ --prop series1=Share:35,25,20,15,30,25,20,10,15 \ --prop categories=North,South,East,West,Urban,Suburban,Rural,Online,Retail \ --prop chartFill=F8FAFC \ --prop plotFill=FFFFFF \ --prop dataLabels=true \ --prop x=14 --prop y=19 --prop width=13 --prop height=18 # ========================================================================== # Sheet 3: Histogram & Box Whisker # ========================================================================== officecli add "$FILE" / --type sheet --prop name="3-Histogram & BoxWhisker" # Chart 1: Histogram — auto-binning (Excel picks bin count) # Features: chartType=histogram, no binning knobs → Excel auto-selects bins officecli add "$FILE" "/3-Histogram & BoxWhisker" --type chart \ --prop chartType=histogram \ --prop title="Test Scores (auto bins)" \ --prop series1=Scores:45,52,58,61,63,65,67,68,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,97,99 \ --prop x=0 --prop y=0 --prop width=13 --prop height=18 # Chart 2: Histogram — explicit binCount=5 with title glow # Features: histogram binCount (explicit bin count), title.glow officecli add "$FILE" "/3-Histogram & BoxWhisker" --type chart \ --prop chartType=histogram \ --prop title="Sales (binCount=5)" \ --prop series1=Sales:120,135,148,155,162,170,175,183,191,200,210,220,235,250,265,280,295,310,340,380,420,480,550,620,700 \ --prop binCount=5 \ --prop title.glow=FFC000-6-50 \ --prop x=14 --prop y=0 --prop width=13 --prop height=18 # Chart 3: Histogram — explicit binSize=50 (fixed bin width) + label font # Features: histogram binSize (explicit bin width — mutually exclusive with # binCount), dataLabels, labelFont officecli add "$FILE" "/3-Histogram & BoxWhisker" --type chart \ --prop chartType=histogram \ --prop title="Sales (binSize=50)" \ --prop series1=Sales:120,135,148,155,162,170,175,183,191,200,210,220,235,250,265,280,295,310,340,380,420,480,550,620,700 \ --prop binSize=50 \ --prop dataLabels=true \ --prop labelFont=9:FFFFFF:true \ --prop x=28 --prop y=0 --prop width=13 --prop height=18 # Chart 4: Histogram — overflow/underflow bins + intervalClosed=l # Features: histogram underflowBin (cutoff for N), # intervalClosed=l (bins are [a,b) — left-closed; default "r" is (a,b]), # legend=none officecli add "$FILE" "/3-Histogram & BoxWhisker" --type chart \ --prop chartType=histogram \ --prop title="Response Time (outlier bins)" \ --prop series1=ms:40,55,68,75,82,88,95,102,110,118,125,135,150,175,220,280,350 \ --prop underflowBin=60 \ --prop overflowBin=200 \ --prop intervalClosed=l \ --prop dataLabels=true \ --prop legend=none \ --prop x=0 --prop y=19 --prop width=13 --prop height=18 # Chart 5: Box & Whisker — two teams, quartileMethod=exclusive # Features: chartType=boxWhisker, two-series comparison, quartileMethod=exclusive, # legend=bottom, outlier detection (built-in) officecli add "$FILE" "/3-Histogram & BoxWhisker" --type chart \ --prop chartType=boxWhisker \ --prop title="Response Time by Team (ms)" \ --prop "series1=TeamA:42,55,61,68,72,75,78,81,85,88,92,97,105,120" \ --prop "series2=TeamB:30,38,45,52,58,62,65,68,71,74,78,85,92,110" \ --prop quartileMethod=exclusive \ --prop legend=bottom \ --prop x=14 --prop y=19 --prop width=13 --prop height=18 # Chart 6: Box & Whisker — three departments, quartileMethod=inclusive + glow # Features: boxWhisker three-series, quartileMethod=inclusive (different quartile # formula from exclusive), title.glow, mean markers (default on) officecli add "$FILE" "/3-Histogram & BoxWhisker" --type chart \ --prop chartType=boxWhisker \ --prop title="Salary Distribution (\$k)" \ --prop "series1=Engineering:85,92,95,98,102,105,108,112,118,125,135,150,180" \ --prop "series2=Marketing:60,65,68,72,75,78,80,83,88,92,98,110" \ --prop "series3=Sales:55,62,68,75,82,90,98,105,115,125,140,160,190" \ --prop quartileMethod=inclusive \ --prop title.glow=00D2FF-6-60 \ --prop legend=bottom \ --prop x=28 --prop y=19 --prop width=13 --prop height=18 # ========================================================================== # Sheet 4: Pareto # ========================================================================== officecli add "$FILE" / --type sheet --prop name="4-Pareto" # Chart 1: Pareto — defect analysis, raw counts auto-sorted + cumul% overlay # Features: chartType=pareto (2-series under the hood — clusteredColumn bars + # paretoLine cumulative %), automatic descending sort, cumulative % computed # server-side, dataLabels on both series. officecli add "$FILE" "/4-Pareto" --type chart \ --prop chartType=pareto \ --prop title="Defect Pareto" \ --prop series1=Count:45,30,10,8,5,2 \ --prop categories=Scratches,Dents,Cracks,Chips,Stains,Other \ --prop dataLabels=true \ --prop x=0 --prop y=0 --prop width=13 --prop height=18 # Chart 2: Pareto — root cause analysis, 10 categories, out-of-order input # Features: pareto with unsorted input values (12, 87, 5, ...) — officecli # re-sorts by value desc and re-aligns categories. title.glow + legend=bottom. officecli add "$FILE" "/4-Pareto" --type chart \ --prop chartType=pareto \ --prop title="Root Cause Pareto" \ --prop series1=Tickets:12,87,5,45,3,120,22,67,8,31 \ --prop categories=Network,Auth,DB,Cache,UI,Config,Deploy,Monitor,Queue,Storage \ --prop title.glow=FFC000-6-50 \ --prop legend=bottom \ --prop x=14 --prop y=0 --prop width=13 --prop height=18 # ========================================================================== # Sheet 5: Chart Meta # ========================================================================== officecli add "$FILE" / --type sheet --prop name="5-Chart Meta" # Chart 1: anchor (cell-range placement), preset (named style bundle) # Features: anchor="A1:M20" (position chart at exact cell-range instead of # x/y/width/height), preset=corporate (named style bundle: colors, fonts, # fill, border in one shot; values: minimal, dark, corporate, magazine, # dashboard, colorful, monochrome) officecli add "$FILE" "/5-Chart Meta" --type chart \ --prop chartType=column \ --prop title="anchor + preset=corporate" \ --prop series1=Revenue:120,145,132,160 \ --prop categories=Q1,Q2,Q3,Q4 \ --prop anchor=A1:M20 \ --prop preset=corporate # Chart 2: autotitledeleted, plotvisonly # Features: autotitledeleted=true (suppress the auto "Chart Title" placeholder), # plotvisonly=true (skip plotting hidden rows/columns) officecli add "$FILE" "/5-Chart Meta" --type chart \ --prop chartType=bar \ --prop series1=Sales:80,95,88,110 \ --prop categories=Q1,Q2,Q3,Q4 \ --prop x=0 --prop y=22 --prop width=12 --prop height=18 \ --prop autotitledeleted=true \ --prop plotvisonly=true # Chart 3: preset variants — minimal # Features: preset=minimal (strip: removes gridlines, legend, border, most # styling; exposes the data with minimal chrome) officecli add "$FILE" "/5-Chart Meta" --type chart \ --prop chartType=line \ --prop title="preset=minimal" \ --prop series1=A:10,20,15,25 \ --prop series2=B:8,14,12,20 \ --prop categories=W1,W2,W3,W4 \ --prop x=13 --prop y=0 --prop width=12 --prop height=18 \ --prop preset=minimal # Chart 4: preset=dark # Features: preset=dark (dark background, light-colored series and text) officecli add "$FILE" "/5-Chart Meta" --type chart \ --prop chartType=column \ --prop title="preset=dark" \ --prop series1=Sales:45,60,55,80 \ --prop categories=Q1,Q2,Q3,Q4 \ --prop x=13 --prop y=22 --prop width=12 --prop height=18 \ --prop preset=dark # Remove blank default Sheet1 (all data is inline) officecli remove "$FILE" /Sheet1 officecli close "$FILE" officecli validate "$FILE" echo "Generated: $FILE"