Working with Pivot Charts in Excel Library

24 Jul 20265 minutes to read

Pivot charts are interactive graphical representations of pivot table data that allow rapid analysis of the displayed data. In XlsIO, a pivot chart is created by instantiating an IChart and setting its PivotSource to the pivot table.

NOTE

XlsIO supports pivot charts only for the XLSX format.

To create a pivot table, refer to Create Pivot Table. The examples in this topic assume that the input workbook already contains a pivot table.

The following code snippet illustrates how to create a pivot chart.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Open("PivotTable.xlsx");
  IWorksheet worksheet = workbook.Worksheets[0];
  IPivotTable pivotTable = worksheet.PivotTables[0];

  //Adding a chart to workbook
  IChart pivotChart = workbook.Charts.Add();

  //Set PivotTable as PivotSource to the chart
  pivotChart.PivotSource = pivotTable;

  //Set PivotChart type
  pivotChart.PivotChartType = ExcelChartType.Column_Clustered;

  string fileName = "PivotChart.xlsx";

  //Saving the workbook 
  workbook.SaveAs(fileName);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Open("PivotTable.xlsx");
  IWorksheet worksheet = workbook.Worksheets[0];
  IPivotTable pivotTable = worksheet.PivotTables[0];

  //Adding a chart to workbook
  IChart pivotChart = workbook.Charts.Add();

  //Set PivotTable as PivotSource to the chart
  pivotChart.PivotSource = pivotTable;

  //Set PivotChart type
  pivotChart.PivotChartType = ExcelChartType.Column_Clustered;

  workbook.SaveAs("PivotChart.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Open("PivotTable.xlsx")
  Dim worksheet As IWorksheet = workbook.Worksheets(0)
  Dim pivotTable As IPivotTable = worksheet.PivotTables(0)

  'Adding a chart to workbook
  Dim pivotChart As IChart = workbook.Charts.Add()

  'Set PivotTable as PivotSource to the chart
  pivotChart.PivotSource = pivotTable

  'Set PivotChart type
  pivotChart.PivotChartType = ExcelChartType.Column_Clustered

  workbook.SaveAs("PivotChart.xlsx")
End Using

Pivot chart options

The following snippet hides all five field-button groups on the pivot chart (ShowAllFieldButtons and the four individual toggles, which default to true). All buttons are members of the IChart interface and operate at runtime on the chart object created above.

NOTE

The pivot chart field-button properties are supported exclusively from Excel 2010 onwards.

//Adding PivotChart to the workbook
IChart pivotChart = workbook.Charts.Add();

//Set Field Buttons
pivotChart.ShowAllFieldButtons = false;
pivotChart.ShowAxisFieldButtons = false;
pivotChart.ShowLegendFieldButtons = false;
pivotChart.ShowReportFilterFieldButtons = false;
pivotChart.ShowValueFieldButtons = false;
//Adding PivotChart to the workbook
IChart pivotChart = workbook.Charts.Add();

//Set Field Buttons
pivotChart.ShowAllFieldButtons = false;
pivotChart.ShowAxisFieldButtons = false;
pivotChart.ShowLegendFieldButtons = false;
pivotChart.ShowReportFilterFieldButtons = false;
pivotChart.ShowValueFieldButtons = false;
'Insert the pivot chart into the workbook
Dim pivotChart As IChart = workbook.Charts.Add()

'Set Field Buttons
pivotChart.ShowAllFieldButtons = False
pivotChart.ShowAxisFieldButtons = False
pivotChart.ShowLegendFieldButtons = False
pivotChart.ShowReportFilterFieldButtons = False
pivotChart.ShowValueFieldButtons = False

A complete working example to create a pivot chart in C# is present on GitHub: Create Pivot Chart example.

Pivot chart series

When a pivot chart is created with a pivot table as its data source, XlsIO does not auto-generate the underlying chart series, because the pivot range cannot be expressed as a regular worksheet range. To customize series-level formatting, add series manually using IChart.Series.Add(ExcelChartType).

//Uses the pivotChart variable from the Create chart sample.
pivotChart.Series.Add(ExcelChartType.Column_Stacked);
pivotChart.Series[0].SerieFormat.CommonSerieOptions.Overlap = 100;
//Uses the pivotChart variable from the Create chart sample.
pivotChart.Series.Add(ExcelChartType.Column_Stacked);
pivotChart.Series[0].SerieFormat.CommonSerieOptions.Overlap = 100;
'Uses the pivotChart variable from the Create chart sample.
pivotChart.Series.Add(ExcelChartType.Column_Stacked)
pivotChart.Series(0).SerieFormat.CommonSerieOptions.Overlap = 100