Working with Pivot Charts

21 May 2018 / 9 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)
        await DependencyService.Get<ISaveWindowsPhone>().SaveAndView(fileName, "application/msexcel", outputStream);
    else
        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;