Working with Pivot Charts in Excel Library

12 May 20235 minutes to read

PivotCharts are interactive graphical representations of PivotTable data that allows rapid analysis of the displayed data. In XlsIO, PivotCharts are created by IChart interface by setting its pivot source as PivotTable.

NOTE

XlsIO supports PivotCharts only for XLSX format.

To create a pivot table, refer Create Pivot Table.

The following code snippet illustrates how to create a PivotChart.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  FileStream fileStream = new FileStream("PivotTable.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  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 as stream
  FileStream stream = new FileStream(fileName, FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
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

PivotChart Options

The following code snippet shows how to set field buttons in a pivot chart.

NOTE

The PivotChart 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 PivotChart sheet to the workbook
Dim pivotChartSheet As IChart = workbook.Charts.Add()

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

A complete working example to create pivot chart in C# is present on this GitHub page.

PivotChart Series

When pivot chart is created with pivot table as data source, Syncfusion XlsIO cannot create the series because the range of the pivot table is different from normal worksheet range. This is the limitation of XlsIO. To use any chart series formatting, the series should be added manually.

pivotChartSheet.Series.Add(ExcelChartType.Column_Stacked);
pivotChartSheet.Series[0].SerieFormat.CommonSerieOptions.Overlap = 100;
pivotChartSheet.Series.Add(ExcelChartType.Column_Stacked);
pivotChartSheet.Series[0].SerieFormat.CommonSerieOptions.Overlap = 100;
pivotChartSheet.Series.Add(ExcelChartType.Column_Stacked)
pivotChartSheet.Series(0).SerieFormat.CommonSerieOptions.Overlap = 100