Insert Pivot Chart in XLSM Documents via C#
Native and high performance Microsoft Excel XLSM spreadsheet with Pivot Chart creation programmatically using server side .NET APIs.
How to insert Pivot Chart in XLSM via C#
It is easy for the developers to insert Pivot Chart in XLSM format spreadsheet within running different reporting applications for data processing in just a few lines of code.
- Include the namespace in your class file
- Create Workbook class instance.
- Access the first worksheet of the workbook.
- Get the desired cell(s) of the worksheet and put the value in the cell(s).
- Insert PivotTable and set the style
- Insert Pivot Chart using the data of PivotTable
- Use Save method to save the workbook as XLSM file.
System Requirements
Just make sure that system have Microsoft Windows or a compatible OS with .NET Framework, .NET Core, Windows Azure, Mono or Xamarin Platforms as well as development environment like Microsoft Visual Studio.
- Install from command line as
nuget install Aspose.Cells
or via Package Manager Console of Visual Studio withInstall-Package Aspose.Cells
. - Alternatively, get the offline MSI installer or all DLLs in a ZIP file from downloads
Insert Pivot Chart in XLSM - C#
//Create a new workbook | |
Workbook book = new Workbook(); | |
//Get the first worksheet | |
Worksheet sheet = book.Worksheets[0]; | |
//add some data | |
Cells cells = sheet.Cells; | |
cells[0, 0].Value = "fruit"; | |
cells[1, 0].Value = "grape"; | |
cells[2, 0].Value = "blueberry"; | |
cells[3, 0].Value = "kiwi"; | |
cells[4, 0].Value = "cherry"; | |
cells[5, 0].Value = "grape"; | |
cells[6, 0].Value = "blueberry"; | |
cells[7, 0].Value = "kiwi"; | |
cells[8, 0].Value = "cherry"; | |
cells[0, 1].Value = "year"; | |
cells[1, 1].Value = 2020; | |
cells[2, 1].Value = 2020; | |
cells[3, 1].Value = 2020; | |
cells[4, 1].Value = 2020; | |
cells[5, 1].Value = 2021; | |
cells[6, 1].Value = 2021; | |
cells[7, 1].Value = 2021; | |
cells[8, 1].Value = 2021; | |
cells[0, 2].Value = "amount"; | |
cells[1, 2].Value = 50; | |
cells[2, 2].Value = 60; | |
cells[3, 2].Value = 70; | |
cells[4, 2].Value = 80; | |
cells[5, 2].Value = 90; | |
cells[6, 2].Value = 100; | |
cells[7, 2].Value = 110; | |
cells[8, 2].Value = 120; | |
PivotTableCollection pivots = sheet.PivotTables; | |
//Add a PivotTable | |
int pivotIndex = pivots.Add("=Sheet1!A1:C9", "A12", "TestPivotTable"); | |
PivotTable pivot = pivots[pivotIndex]; | |
//Add PivotField for row area | |
pivot.AddFieldToArea(PivotFieldType.Row, "fruit"); | |
//Add PivotField for column area | |
pivot.AddFieldToArea(PivotFieldType.Column, "year"); | |
//Add PivotField for data area | |
pivot.AddFieldToArea(PivotFieldType.Data, "amount"); | |
//Set the style of PivotTable | |
pivot.PivotTableStyleType = PivotTableStyleType.PivotTableStyleMedium9; | |
//Refresh and calculate data of PivotTable | |
pivot.RefreshData(); | |
pivot.CalculateData(); | |
//Construct data range for chart object | |
StringBuilder dataRange = new StringBuilder(); | |
dataRange.Append("="); | |
dataRange.Append(sheet.Name); | |
dataRange.Append("!"); | |
dataRange.Append(CellsHelper.CellIndexToName(pivot.TableRange1.StartRow + 1, pivot.TableRange1.StartColumn)); | |
dataRange.Append(":"); | |
dataRange.Append(CellsHelper.CellIndexToName(pivot.TableRange1.EndRow, pivot.TableRange1.EndColumn)); | |
ChartCollection charts = sheet.Charts; | |
//Add Cylinder chart | |
int index = charts.Add(ChartType.Cylinder, dataRange.ToString(), false, 10, 5, 25, 15); | |
charts[index].RefreshPivotData(); | |
book.Save("out.xlsm"); |
XLSM What is XLSM File Format?
Files with XLSM extension is a type of Spreadsheet files that support Macros. From application point of view, a Macro is set of instructions that are used for automating processes. A macro is used to record the steps that are performed repeatedly and facilitates performing the actions by running the macro again. Macros are programmed with Microsoft’s Visual Basic for Applications (VBA) from within the Excel Workbook using the Visual Basic Editor and can be run/debug directly from there.
Read More