How to Create Excel Charts (Including Combo and Stock Charts) Using OfficeCLI

OfficeCLI creates Excel charts by routing add chart commands through the ExcelHandler, which uses the OpenXML SDK to build DrawingsPart elements, with specialized logic for combo charts (mixing column/line series) and stock charts (requiring four specific series) in files like ExcelHandler.Add.Chart.cs and ChartHelper.cs.

OfficeCLI is a command-line interface for manipulating Microsoft Office documents without external binaries. The iOfficeAI/OfficeCLI repository provides dedicated handlers for each document type, with the ExcelHandler managing all chart operations through pure C# OpenXML manipulation. Whether you need simple column charts or complex financial visualizations, you can create Excel charts using OfficeCLI by passing structured properties to the add command.

The Chart Creation Pipeline in OfficeCLI

When you execute an add chart command, OfficeCLI follows a strict internal pipeline defined in CommandBuilder.cs. First, the CLI parses the verb and routes the request to OfficeCli.Handlers.ExcelHandler. The ExcelHandler.AddChart method (located in src/officecli/Handlers/Excel/ExcelHandler.Add.Chart.cs) then coordinates property extraction and OpenXML part generation.

The handler delegates chart-type resolution to ChartHelper.ParseChartType in ChartHelper.cs, which normalizes input strings like combo or stock and validates modifiers for 3D or stacked variants. This method returns a tuple containing the chart kind, 3D flags, and stacking options that determine the final XML structure.

Creating Standard Column and Bar Charts

For basic charts, OfficeCLI constructs a new DrawingsPart if one does not exist, then builds the chart XML using the OpenXML SDK's DocumentFormat.OpenXml.Drawing.Charts namespace. The handler caches series data in <c:numCache> or <c:strCache> elements to support immediate HTML preview rendering.

To add a simple column chart, specify the charttype, series properties, and categories:

officecli add sales.xlsx /sheet[Q1] \
    --type chart \
    --prop charttype=column \
    --prop series1="Revenue:100,150,200" \
    --prop series2="Profit:30,45,60" \
    --prop categories="Jan,Feb,Mar"

This command creates a column chart on sheet Q1 with two data series and category labels. The ExcelHandler automatically handles the TwoCellAnchor positioning if no specific coordinates are provided.

Building Combo Charts in OfficeCLI

Combo charts require special handling because they combine multiple chart types (typically column and line) within a single plot area. In ChartHelper.Builder.cs, the generic builder creates a <c:plotArea> that mixes series groups and maps each series to the correct <c:axId> for primary or secondary axes, as implemented in ChartHelper.Setter.cs (lines 415-418).

The comboTypes property controls the visual representation of each series. If omitted, the handler defaults to a column-plus-line split at the first series boundary.

To create a combo chart with explicit type mapping:

officecli add finance.xlsx /sheet[Overview] \
    --type chart \
    --prop charttype=combo \
    --prop series1="Revenue:120,180,240" \
    --prop series2="Profit:40,70,110" \
    --prop series3="GrowthRate:5,7,9" \
    --prop comboTypes=column,column,line \
    --prop categories="Q1,Q2,Q3"

The comboTypes=column,column,line instruction renders the first two series as columns and the third as a line, automatically creating a secondary axis for the line series. The handler validates that the number of types matches the number of series before emitting the OpenXML.

Generating Stock Charts with OfficeCLI

Stock charts enforce strict data requirements: they require exactly four series representing Open, High, Low, and Close values. The ChartHelper.ParseChartType method contains specific validation logic for the stock type (lines 55-56) that throws an informative ArgumentException if the supplied data does not match this four-series structure.

The builder creates a <c:stockChart> element and expects the series in the specific order enforced by the parser.

To add a stock chart:

officecli add prices.xlsx /sheet[Tech] \
    --type chart \
    --prop charttype=stock \
    --prop series1="Open:150,152,151" \
    --prop series2="High:155,158,156" \
    --prop series3="Low:148,149,147" \
    --prop series4="Close:152,154,150" \
    --prop categories="01-Jan,02-Jan,03-Jan"

This generates a fully functional stock chart with the required four series. The handler stores the literal values in the chart's number cache so the visualization renders correctly in both Excel and the CLI's HTML preview.

Data Binding and Chart Positioning

OfficeCLI supports two data input methods: inline literal values (as shown above) or cell references using the datarange property. When datarange= is supplied, ExcelHandler.AddChart calls ParseDataRangeForChart to extract values from the worksheet, back-filling the chart caches so the HTML preview renders immediately.

To reference existing cells instead of inline data:

officecli add report.xlsx /sheet[Data] \
    --type chart \
    --prop charttype=combo \
    --prop datarange=Data!$A$2:$D$6 \
    --prop categories=Data!$A$2:$A$6 \
    --prop series1=Data!$B$2:$B$6 \
    --prop series2=Data!$C$2:$C$6 \
    --prop series3=Data!$D$2:$D$6 \
    --prop comboTypes=column,line,line

For positioning, use the anchor property with a cell range like D5:H15, which TryParseCellRangeAnchor converts into a TwoCellAnchor. When anchor is present, the CLI ignores separate x, y, width, and height properties (see lines 23-31 of the handler).

officecli add dashboard.xlsx /sheet[Summary] \
    --type chart \
    --prop charttype=column \
    --prop series1="Sales:200,250,300" \
    --prop categories="East,West,North" \
    --prop anchor=D5:H15

Summary

• OfficeCLI routes all chart commands through ExcelHandler.AddChart in ExcelHandler.Add.Chart.cs, which manipulates OpenXML parts directly without external binaries.
• The ChartHelper.ParseChartType method in ChartHelper.cs validates chart types and handles special flags for 3D and stacked variants.
• Combo charts use the comboTypes property to map series to mixed visual types (column, line, bar) and automatically manage secondary axes via ChartHelper.Setter.cs.
• Stock charts require exactly four series (Open, High, Low, Close) and generate a <c:stockChart> element with strict validation.
• Use datarange to bind charts to existing worksheet cells, or anchor to position charts using cell references rather than absolute coordinates.

Frequently Asked Questions

What is the minimum number of series required for a stock chart in OfficeCLI?

OfficeCLI requires exactly four series for stock charts: Open, High, Low, and Close. The ChartHelper.ParseChartType method enforces this structure and throws an ArgumentException if you provide fewer or more than four series when charttype=stock is specified.

How does OfficeCLI handle the secondary axis in combo charts?

The ChartHelper.Setter.cs file (lines 415-418) automatically assigns line series to a secondary axis when using combo charts. You control which series use which chart type via the comboTypes property (e.g., column,column,line), and the builder maps each series to the appropriate <c:axId> element in the OpenXML plot area.

Can I use existing Excel data instead of inline values when creating charts?

Yes. Use the datarange property to specify a cell range (e.g., Data!$A$2:$D$6), or assign individual series using series1=Sheet!$B$2:$B$6. The ExcelHandler.AddChart method calls ParseDataRangeForChart to read the values and populate the chart's <c:numCache> or <c:strCache>, ensuring the chart renders in both Excel and the CLI's HTML preview.

Where is the chart positioning logic implemented in the OfficeCLI source code?

Positioning logic resides in ExcelHandler.Add.Chart.cs (lines 23-49). The method TryParseCellRangeAnchor converts cell range strings like D5:H15 into TwoCellAnchor objects. If you provide an anchor property, the handler ignores any separate x, y, width, or height values and anchors the chart to the specified cell corners.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}