Working with Pivot Charts in Excel Library

24 Jan 202310 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;
  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
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;

  //Gets assembly
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;

  //Gets input Excel document from embedded resource collection
  Stream inputStream = assembly.GetManifestResourceStream("PivotChart.PivotTable.xlsx");
  IWorkbook workbook = await application.Workbooks.OpenAsync(inputStream);
  IWorksheet worksheet = workbook.Worksheets[0];
  IPivotTable pivotTable = workbook.Worksheets[1].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;

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "PivotChart";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);
}
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;

  //Gets assembly
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;

  //Gets input Excel document from embedded resource collection
  Stream inputStream = assembly.GetManifestResourceStream("PivotChart.PivotTable.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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
  MemoryStream outputStream = new MemoryStream();
  workbook.SaveAs(outputStream);

  //Save the stream as an Excel document and view the saved document
  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.
  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
    Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView(fileName, "application/msexcel", outputStream);
  }
  else
  {
    Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView(fileName, "application/msexcel", outputStream);
  }

  //Dispose the input and output stream instances
  inputStream.Dispose();
  outputStream.Dispose();
}

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;
'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
//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;
//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;

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;
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;