How to Use OfficeCLI to Add Charts to Excel Spreadsheets

OfficeCLI enables you to insert charts into Excel workbooks via command-line arguments that specify chart type, data range, title, and positioning, using the ExcelHandler.AddChart method behind the scenes.

The iOfficeAI/OfficeCLI repository provides a command-line interface for manipulating Office documents programmatically. When you need to use OfficeCLI to add charts to Excel spreadsheets, the tool leverages the ExcelHandler.AddChart implementation to generate Open XML drawing parts and embed them in your target worksheet.

Command Syntax and Basic Usage

To add a chart to an Excel file, run the officecli add command with the workbook path, target sheet name, and chart-specific properties. The CLI resolves these requests through CommandBuilder.Add.cs and delegates to the ExcelHandler.AddChart method in src/officecli/Handlers/Excel/ExcelHandler.Add.Chart.cs.

officecli add myWorkbook.xlsx SheetName --charttype=column --title="Sales Data" --datarange="Sheet1!A1:B5"

The handler extracts the target sheet from the path structure and processes the following key flags:

• --charttype: Specifies the visualization type (e.g., column, line, pie, treemap, funnel)
• --title: Sets the chart title text
• --datarange: References cells containing the data (e.g., Sheet1!A1:D5)
• --anchor: Defines the chart area using a cell range (e.g., D3:J15)
• --x, --y, --width, --height: Alternative positioning using explicit coordinates

Defining Chart Data Sources

OfficeCLI accepts data through two mutually exclusive approaches. In ExcelHandler.Add.Chart.cs, the method first checks for datarange; if absent, it parses inline series and categories properties.

Using Cell Ranges

When you provide --datarange, the handler calls ParseDataRangeForChart to convert the Excel notation into series data and category labels. The source code reads the specified range from the worksheet and constructs the chart data source accordingly.

officecli add myWorkbook.xlsx Sheet1 \
    --charttype=column \
    --title="Quarterly Sales" \
    --datarange="Sheet1!A1:B5"

Using Inline Series and Categories

Alternatively, specify data directly via multiple --series flags and optional --categories. The parser supports the format Name:val1,val2,val3 and may back-fill literal values into the worksheet to ensure proper rendering in HTML previews.

officecli add myWorkbook.xlsx Sheet1 \
    --charttype=line \
    --title="Revenue Trend" \
    --series="2020:100,200,300" \
    --series="2021:150,250,350" \
    --categories="Sheet1!A1:A3" \
    --anchor="D3:J15"

Supported Chart Types

The implementation distinguishes between classic and extended chart types. ChartHelper.ParseChartType validates standard types like column, line, and pie, while ChartExBuilder handles modern visualizations including treemap and funnel.

For extended charts, the code generates an ExtendedChartPart rather than a standard ChartPart, writes inline data to the host sheet when necessary, and strips or remaps external data references to ensure self-contained workbooks. Visual styling for these charts derives from src/officecli/Resources/chartex-style.xml and src/officecli/Resources/chartex-colors.xml.

officecli add myWorkbook.xlsx Sheet1 \
    --charttype=treemap \
    --title="Product Mix" \
    --series="North:40,South:30,East:20,West:10" \
    --anchor="E2:H12"

Positioning and Anchoring

OfficeCLI computes chart placement using either explicit dimensions or a cell-based anchor. If you provide --anchor with a range like D3:J15, the code calculates a two-cell anchor covering that rectangle. Otherwise, it uses the --x, --y, --width, and --height values (supporting units like cm, pt, or in) to create the drawing boundary.

The handler adds or reuses a DrawingsPart on the target worksheet and inserts the chart into the sheet's drawing hierarchy at the computed location.

Open XML Generation Process

Behind the scenes, the AddChart method performs the following steps as implemented in the source:

1. Property Parsing: Extracts chart type, title, and positioning flags from the command line
2. Data Resolution: Calls ParseDataRangeForChart for range-based data or parses inline series definitions
3. Type Validation: Routes to ChartHelper.ParseChartType for classic charts or ChartExBuilder for extended types
4. Part Creation: Generates a ChartPart or ExtendedChartPart and attaches it to a DrawingsPart on the worksheet
5. Anchor Computation: Determines the two-cell anchor from the --anchor range or explicit coordinates
6. Persistence: Saves the workbook with the new chart embedded in the Open XML structure

Documentation for these features is embedded in schemas/help/_shared/ole.json, which powers the officecli help add chart output.

Summary

• Primary Entry Point: The officecli add command routes to ExcelHandler.AddChart in src/officecli/Handlers/Excel/ExcelHandler.Add.Chart.cs
• Data Flexibility: Supply data via --datarange for cell references or --series/--categories for inline values
• Chart Variety: Standard types use ChartHelper.ParseChartType; extended types (treemap, funnel) use ChartExBuilder
• Position Control: Use --anchor for cell-based positioning or explicit coordinates (--x, --y, --width, --height) for precise placement
• Documentation: Run officecli help add chart to view property definitions loaded from schemas/help/_shared/ole.json

Frequently Asked Questions

What chart types does OfficeCLI support?

OfficeCLI supports classic chart types including column, line, and pie through ChartHelper.ParseChartType, plus extended types like treemap and funnel via ChartExBuilder. Extended charts generate ExtendedChartPart XML elements and apply styles from the bundled chartex-style.xml and chartex-colors.xml resources.

How do I position a chart using existing cells in the worksheet?

Use the --anchor flag followed by a cell range such as D3:J15. The AddChart method computes a two-cell anchor from this range and places the chart within those boundaries. This overrides any explicit --x, --y, --width, or --height values you might provide.

Can I create a chart without referencing existing cell data?

Yes. Instead of --datarange, use multiple --series flags with the format Name:value1,value2,value3 and optionally specify --categories. The handler parses these inline definitions and may write literal values back to the worksheet to ensure the chart renders correctly in HTML previews.

Where does OfficeCLI store the chart implementation code?

The core logic resides in src/officecli/Handlers/Excel/ExcelHandler.Add.Chart.cs, which implements the AddChart method. Command routing occurs in src/officecli/CommandBuilder.Add.cs, while help documentation is defined in schemas/help/_shared/ole.json.

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