Purpose
This article is designed to provide the developers with a detailed understanding on how to create Pivot Tables and Pivot Charts using Aspose.Cells component.
A P ivot T able is an interactive summary of records. For example, you may have hundreds of invoice entries in a list on your worksheet. A P ivot T able can total the invoices by customer, product or date. In MS Excel, y ou can quickly re - arrange the information in the P ivot T able by dragging the buttons to a new position. A P ivot C hart is an interactive graphical representation of the data in a P ivot T able. This feature was firstly introduced in Excel 2000. Using a P ivot C hart makes it even easier to understand your data since the P ivot T able creates your subtotals and totals for you automatically . Aspose.Cells is a feature rich component that supports to create P ivot T ables and P ivot C harts for your requirement. Aspose.Cells provides some special set of classes in Aspose.Cells.Pivot namespace that are used to create and set the PivotTables. These classes are used to create and set PivotTable objects, which act as the basic building blocks of a PivotTable. These PivotTable objects are listed below:
- PivotField, represents a field in a PivotTable report.
- PivotFieldCollection, represents a collection of all the PivotField objects in the PivotTable.
- PivotTable, represents a PivotTable report on a worksheet.
- PivotTableColllection, represents the collection of all the PivotTable objects on the worksheet.
This article is designed to provide the developers with a detailed understanding on how to create Pivot Tables and Pivot Charts using Aspose.Cells component.
| A P ivot T able is an interactive summary of records. For example, you may have hundreds of invoice entries in a list on your worksheet. A P ivot T able can total the invoices by customer, product or date. In MS Excel, y ou can quickly re - arrange the information in the P ivot T able by dragging the buttons to a new position. A P ivot C hart is an interactive graphical representation of the data in a P ivot T able. This feature was firstly introduced in Excel 2000. Using a P ivot C hart makes it even easier to understand your data since the P ivot T able creates your subtotals and totals for you automatically . Aspose.Cells is a feature rich component that supports to create P ivot T ables and P ivot C harts for your requirement. Aspose.Cells provides some special set of classes in Aspose.Cells.Pivot namespace that are used to create and set the PivotTables. These classes are used to create and set PivotTable objects, which act as the basic building blocks of a PivotTable. These PivotTable objects are listed below:
|
Add a PivotTable
To create a PivotTable using Aspose.Cells, you may follow the steps below:* Add some data to the worksheet cells by using the PutValue/setValue method of a Cell object. You may use an existing template file filled with data too. This data will be used as a data source for the PivotTable.
- Add a PivotTable to the worksheet by calling the add method of PivotTables collection, which is encapsulated in the Worksheet object.
- Access the newly added PivotTable object from the PivotTables collection by passing the PivotTable index. Use any of the PivotTable objects encapsulated in the PivotTable object to manage your PivotTable.
To create a PivotTable using Aspose.Cells, you may follow the steps below:* Add some data to the worksheet cells by using the PutValue/setValue method of a Cell object. You may use an existing template file filled with data too. This data will be used as a data source for the PivotTable.
- Add a PivotTable to the worksheet by calling the add method of PivotTables collection, which is encapsulated in the Worksheet object.
- Access the newly added PivotTable object from the PivotTables collection by passing the PivotTable index. Use any of the PivotTable objects encapsulated in the PivotTable object to manage your PivotTable.
Add a PivotChart
To create a PivotChart using Aspose.Cells, you may follow the steps below:* Add your desired chart using Aspose.Cells API.
- Set the PivotSource of the chart to refer it to existing PivotTable in the spreadsheet.
- Set other attributes if needed.
You might try Office Automation but Office automation has its own drawbacks. There are several reasons and issues involved: E.g., Security, Stability, Scalability/Speed, Price, Features etc. In Short, there are many reasons, with the top one being that Microsoft themselves strongly recommends against Office automation from software solutions.
In this article, we create a console C# application in Visual Studio.Net to perform the task using Aspose.Cells API.
To create a PivotChart using Aspose.Cells, you may follow the steps below:* Add your desired chart using Aspose.Cells API.
- Set the PivotSource of the chart to refer it to existing PivotTable in the spreadsheet.
- Set other attributes if needed.
You might try Office Automation but Office automation has its own drawbacks. There are several reasons and issues involved: E.g., Security, Stability, Scalability/Speed, Price, Features etc. In Short, there are many reasons, with the top one being that Microsoft themselves strongly recommends against Office automation from software solutions.
In this article, we create a console C# application in Visual Studio.Net to perform the task using Aspose.Cells API.
Task List:
Following is the task list:
- Using Aspose.Cells to create a Pivot Table in the Workbook.
- Using Aspose.Cells to create a Pivot Chart based on the Pivot Table.
Following is the task list:
- Using Aspose.Cells to create a Pivot Table in the Workbook.
- Using Aspose.Cells to create a Pivot Chart based on the Pivot Table.
Task 1: Using Aspose.Cells to create a Pivot Table in the Workbook.
Please see the following simple steps which you need to perform to get your desired results,
Please see the following simple steps which you need to perform to get your desired results,
Step 1: Download and Install Aspose.Cells
First, you need to download Aspose.Cells for .Net. Install it on your development computer. All Aspose components, when installed, work in evaluation mode. The evaluation mode has no time limit and it only injects watermarks into produced documents. To work with the component in its full capacity you do need to have a valid license.
First, you need to download Aspose.Cells for .Net. Install it on your development computer. All Aspose components, when installed, work in evaluation mode. The evaluation mode has no time limit and it only injects watermarks into produced documents. To work with the component in its full capacity you do need to have a valid license.
Step 2: Create a Project
Start Visual Studio.Net and create a new console application. This example will show a C# console application, but you can use VB.NET too.
Start Visual Studio.Net and create a new console application. This example will show a C# console application, but you can use VB.NET too.
Step 3: Add References
This project will use Aspose.Cells for .NET . So, you have to add reference to Aspose.Cells component in your project. E.g., add a reference to ….\Program Files\Aspose\Aspose.Cells\Bin\Net1.0\Aspose.Cells.dll
This project will use Aspose.Cells for .NET . So, you have to add reference to Aspose.Cells component in your project. E.g., add a reference to ….\Program Files\Aspose\Aspose.Cells\Bin\Net1.0\Aspose.Cells.dll
Step 4: Create a Pivot Table in the Workbook (Code Snippet)
[C#]
//Instantiating an Workbook object
Workbook workbook = new Workbook();
//Obtaining the reference of the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Name the sheet
sheet.Name = "Data";
Cells cells = sheet.Cells;
//Setting the values to the cells
Cell cell = cells["A1"];
cell.PutValue("Employee");
cell = cells["B1"];
cell.PutValue("Quarter");
cell = cells["C1"];
cell.PutValue("Product");
cell = cells["D1"];
cell.PutValue("Continent");
cell = cells["E1"];
cell.PutValue("Country");
cell = cells["F1"];
cell.PutValue("Sale");
cell = cells["A2"];
cell.PutValue("David");
cell = cells["A3"];
cell.PutValue("David");
cell = cells["A4"];
cell.PutValue("David");
cell = cells["A5"];
cell.PutValue("David");
cell = cells["A6"];
cell.PutValue("James");
cell = cells["A7"];
cell.PutValue("James");
cell = cells["A8"];
cell.PutValue("James");
cell = cells["A9"];
cell.PutValue("James");
cell = cells["A10"];
cell.PutValue("James");
cell = cells["A11"];
cell.PutValue("Miya");
cell = cells["A12"];
cell.PutValue("Miya");
cell = cells["A13"];
cell.PutValue("Miya");
cell = cells["A14"];
cell.PutValue("Miya");
cell = cells["A15"];
cell.PutValue("Miya");
cell = cells["A16"];
cell.PutValue("Miya");
cell = cells["A17"];
cell.PutValue("Miya");
cell = cells["A18"];
cell.PutValue("Elvis");
cell = cells["A19"];
cell.PutValue("Elvis");
cell = cells["A20"];
cell.PutValue("Elvis");
cell = cells["A21"];
cell.PutValue("Elvis");
cell = cells["A22"];
cell.PutValue("Elvis");
cell = cells["A23"];
cell.PutValue("Elvis");
cell = cells["A24"];
cell.PutValue("Elvis");
cell = cells["A25"];
cell.PutValue("Jean");
cell = cells["A26"];
cell.PutValue("Jean");
cell = cells["A27"];
cell.PutValue("Jean");
cell = cells["A28"];
cell.PutValue("Ada");
cell = cells["A29"];
cell.PutValue("Ada");
cell = cells["A30"];
cell.PutValue("Ada");
cell = cells["B2"];
cell.PutValue("1");
cell = cells["B3"];
cell.PutValue("2");
cell = cells["B4"];
cell.PutValue("3");
cell = cells["B5"];
cell.PutValue("4");
cell = cells["B6"];
cell.PutValue("1");
cell = cells["B7"];
cell.PutValue("2");
cell = cells["B8"];
cell.PutValue("3");
cell = cells["B9"];
cell.PutValue("4");
cell = cells["B10"];
cell.PutValue("4");
cell = cells["B11"];
cell.PutValue("1");
cell = cells["B12"];
cell.PutValue("1");
cell = cells["B13"];
cell.PutValue("2");
cell = cells["B14"];
cell.PutValue("2");
cell = cells["B15"];
cell.PutValue("3");
cell = cells["B16"];
cell.PutValue("4");
cell = cells["B17"];
cell.PutValue("4");
cell = cells["B18"];
cell.PutValue("1");
cell = cells["B19"];
cell.PutValue("1");
cell = cells["B20"];
cell.PutValue("2");
cell = cells["B21"];
cell.PutValue("3");
cell = cells["B22"];
cell.PutValue("3");
cell = cells["B23"];
cell.PutValue("4");
cell = cells["B24"];
cell.PutValue("4");
cell = cells["B25"];
cell.PutValue("1");
cell = cells["B26"];
cell.PutValue("2");
cell = cells["B27"];
cell.PutValue("3");
cell = cells["B28"];
cell.PutValue("1");
cell = cells["B29"];
cell.PutValue("2");
cell = cells["B30"];
cell.PutValue("3");
cell = cells["C2"];
cell.PutValue("Maxilaku");
cell = cells["C3"];
cell.PutValue("Maxilaku");
cell = cells["C4"];
cell.PutValue("Chai");
cell = cells["C5"];
cell.PutValue("Maxilaku");
cell = cells["C6"];
cell.PutValue("Chang");
cell = cells["C7"];
cell.PutValue("Chang");
cell = cells["C8"];
cell.PutValue("Chang");
cell = cells["C9"];
cell.PutValue("Chang");
cell = cells["C10"];
cell.PutValue("Chang");
cell = cells["C11"];
cell.PutValue("Geitost");
cell = cells["C12"];
cell.PutValue("Chai");
cell = cells["C13"];
cell.PutValue("Geitost");
cell = cells["C14"];
cell.PutValue("Geitost");
cell = cells["C15"];
cell.PutValue("Maxilaku");
cell = cells["C16"];
cell.PutValue("Geitost");
cell = cells["C17"];
cell.PutValue("Geitost");
cell = cells["C18"];
cell.PutValue("Ikuru");
cell = cells["C19"];
cell.PutValue("Ikuru");
cell = cells["C20"];
cell.PutValue("Ikuru");
cell = cells["C21"];
cell.PutValue("Ikuru");
cell = cells["C22"];
cell.PutValue("Ipoh Coffee");
cell = cells["C23"];
cell.PutValue("Ipoh Coffee");
cell = cells["C24"];
cell.PutValue("Ipoh Coffee");
cell = cells["C25"];
cell.PutValue("Chocolade");
cell = cells["C26"];
cell.PutValue("Chocolade");
cell = cells["C27"];
cell.PutValue("Chocolade");
cell = cells["C28"];
cell.PutValue("Chocolade");
cell = cells["C29"];
cell.PutValue("Chocolade");
cell = cells["C30"];
cell.PutValue("Chocolade");
cell = cells["D2"];
cell.PutValue("Asia");
cell = cells["D3"];
cell.PutValue("Asia");
cell = cells["D4"];
cell.PutValue("Asia");
cell = cells["D5"];
cell.PutValue("Asia");
cell = cells["D6"];
cell.PutValue("Europe");
cell = cells["D7"];
cell.PutValue("Europe");
cell = cells["D8"];
cell.PutValue("Europe");
cell = cells["D9"];
cell.PutValue("Europe");
cell = cells["D10"];
cell.PutValue("Europe");
cell = cells["D11"];
cell.PutValue("America");
cell = cells["D12"];
cell.PutValue("America");
cell = cells["D13"];
cell.PutValue("America");
cell = cells["D14"];
cell.PutValue("America");
cell = cells["D15"];
cell.PutValue("America");
cell = cells["D16"];
cell.PutValue("America");
cell = cells["D17"];
cell.PutValue("America");
cell = cells["D18"];
cell.PutValue("Europe");
cell = cells["D19"];
cell.PutValue("Europe");
cell = cells["D20"];
cell.PutValue("Europe");
cell = cells["D21"];
cell.PutValue("Oceania");
cell = cells["D22"];
cell.PutValue("Oceania");
cell = cells["D23"];
cell.PutValue("Oceania");
cell = cells["D24"];
cell.PutValue("Oceania");
cell = cells["D25"];
cell.PutValue("Africa");
cell = cells["D26"];
cell.PutValue("Africa");
cell = cells["D27"];
cell.PutValue("Africa");
cell = cells["D28"];
cell.PutValue("Africa");
cell = cells["D29"];
cell.PutValue("Africa");
cell = cells["D30"];
cell.PutValue("Africa");
cell = cells["E2"];
cell.PutValue("China");
cell = cells["E3"];
cell.PutValue("India");
cell = cells["E4"];
cell.PutValue("Korea");
cell = cells["E5"];
cell.PutValue("India");
cell = cells["E6"];
cell.PutValue("France");
cell = cells["E7"];
cell.PutValue("France");
cell = cells["E8"];
cell.PutValue("Germany");
cell = cells["E9"];
cell.PutValue("Italy");
cell = cells["E10"];
cell.PutValue("France");
cell = cells["E11"];
cell.PutValue("U.S.");
cell = cells["E12"];
cell.PutValue("U.S.");
cell = cells["E13"];
cell.PutValue("Brazil");
cell = cells["E14"];
cell.PutValue("U.S.");
cell = cells["E15"];
cell.PutValue("U.S.");
cell = cells["E16"];
cell.PutValue("Canada");
cell = cells["E17"];
cell.PutValue("U.S.");
cell = cells["E18"];
cell.PutValue("Italy");
cell = cells["E19"];
cell.PutValue("France");
cell = cells["E20"];
cell.PutValue("Italy");
cell = cells["E21"];
cell.PutValue("New Zealand");
cell = cells["E22"];
cell.PutValue("Australia");
cell = cells["E23"];
cell.PutValue("Australia");
cell = cells["E24"];
cell.PutValue("New Zealand");
cell = cells["E25"];
cell.PutValue("S.Africa");
cell = cells["E26"];
cell.PutValue("S.Africa");
cell = cells["E27"];
cell.PutValue("S.Africa");
cell = cells["E28"];
cell.PutValue("Egypt");
cell = cells["E29"];
cell.PutValue("Egypt");
cell = cells["E30"];
cell.PutValue("Egypt");
cell = cells["F2"];
cell.PutValue(2000);
cell = cells["F3"];
cell.PutValue(500);
cell = cells["F4"];
cell.PutValue(1200);
cell = cells["F5"];
cell.PutValue(1500);
cell = cells["F6"];
cell.PutValue(500);
cell = cells["F7"];
cell.PutValue(1500);
cell = cells["F8"];
cell.PutValue(800);
cell = cells["F9"];
cell.PutValue(900);
cell = cells["F10"];
cell.PutValue(500);
cell = cells["F11"];
cell.PutValue(1600);
cell = cells["F12"];
cell.PutValue(600);
cell = cells["F13"];
cell.PutValue(2000);
cell = cells["F14"];
cell.PutValue(500);
cell = cells["F15"];
cell.PutValue(900);
cell = cells["F16"];
cell.PutValue(700);
cell = cells["F17"];
cell.PutValue(1400);
cell = cells["F18"];
cell.PutValue(1350);
cell = cells["F19"];
cell.PutValue(300);
cell = cells["F20"];
cell.PutValue(500);
cell = cells["F21"];
cell.PutValue(1000);
cell = cells["F22"];
cell.PutValue(1500);
cell = cells["F23"];
cell.PutValue(1500);
cell = cells["F24"];
cell.PutValue(1600);
cell = cells["F25"];
cell.PutValue(1000);
cell = cells["F26"];
cell.PutValue(1200);
cell = cells["F27"];
cell.PutValue(1300);
cell = cells["F28"];
cell.PutValue(1500);
cell = cells["F29"];
cell.PutValue(1400);
cell = cells["F30"];
cell.PutValue(1000);
//Adding a new sheet
Worksheet sheet2 = workbook.Worksheets[workbook.Worksheets.Add()];
//Naming the sheet
sheet2.Name = "PivotTable";
//Getting the pivottables collection in the sheet
Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet2.PivotTables;
//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=Data!A1:F30", "B3", "PivotTable1");
//Accessing the instance of the newly added PivotTable
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];
//Showing the grand totals
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;
//Setting the PivotTable report is automatically formatted
pivotTable.IsAutoFormat = true;
//Setting the PivotTable autoformat type.
pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report6;
//Draging the first field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);
//Draging the third field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 2);
//Draging the second field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 1);
//Draging the fourth field to the column area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 3);
//Draging the fifth field to the data area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 5);
//Setting the number format of the first data field
pivotTable.DataFields[0].NumberFormat = "$#,##0.00";
//Saving the Excel file
workbook.Save("f:\\test\\pivotTable_test.xls");
[VB]
'Instantiating an Workbook object
Dim workbook As New Workbook()
'Obtaining the reference of the first worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
'Name the sheet
sheet.Name = "Data"
Dim cells As Cells = sheet.Cells
'Setting the values to the cells
Dim cell As Cell = cells("A1")
cell.PutValue("Employee")
cell = cells("B1")
cell.PutValue("Quarter")
cell = cells("C1")
cell.PutValue("Product")
cell = cells("D1")
cell.PutValue("Continent")
cell = cells("E1")
cell.PutValue("Country")
cell = cells("F1")
cell.PutValue("Sale")
cell = cells("A2")
cell.PutValue("David")
cell = cells("A3")
cell.PutValue("David")
cell = cells("A4")
cell.PutValue("David")
cell = cells("A5")
cell.PutValue("David")
cell = cells("A6")
cell.PutValue("James")
cell = cells("A7")
cell.PutValue("James")
cell = cells("A8")
cell.PutValue("James")
cell = cells("A9")
cell.PutValue("James")
cell = cells("A10")
cell.PutValue("James")
cell = cells("A11")
cell.PutValue("Miya")
cell = cells("A12")
cell.PutValue("Miya")
cell = cells("A13")
cell.PutValue("Miya")
cell = cells("A14")
cell.PutValue("Miya")
cell = cells("A15")
cell.PutValue("Miya")
cell = cells("A16")
cell.PutValue("Miya")
cell = cells("A17")
cell.PutValue("Miya")
cell = cells("A18")
cell.PutValue("Elvis")
cell = cells("A19")
cell.PutValue("Elvis")
cell = cells("A20")
cell.PutValue("Elvis")
cell = cells("A21")
cell.PutValue("Elvis")
cell = cells("A22")
cell.PutValue("Elvis")
cell = cells("A23")
cell.PutValue("Elvis")
cell = cells("A24")
cell.PutValue("Elvis")
cell = cells("A25")
cell.PutValue("Jean")
cell = cells("A26")
cell.PutValue("Jean")
cell = cells("A27")
cell.PutValue("Jean")
cell = cells("A28")
cell.PutValue("Ada")
cell = cells("A29")
cell.PutValue("Ada")
cell = cells("A30")
cell.PutValue("Ada")
cell = cells("B2")
cell.PutValue("1")
cell = cells("B3")
cell.PutValue("2")
cell = cells("B4")
cell.PutValue("3")
cell = cells("B5")
cell.PutValue("4")
cell = cells("B6")
cell.PutValue("1")
cell = cells("B7")
cell.PutValue("2")
cell = cells("B8")
cell.PutValue("3")
cell = cells("B9")
cell.PutValue("4")
cell = cells("B10")
cell.PutValue("4")
cell = cells("B11")
cell.PutValue("1")
cell = cells("B12")
cell.PutValue("1")
cell = cells("B13")
cell.PutValue("2")
cell = cells("B14")
cell.PutValue("2")
cell = cells("B15")
cell.PutValue("3")
cell = cells("B16")
cell.PutValue("4")
cell = cells("B17")
cell.PutValue("4")
cell = cells("B18")
cell.PutValue("1")
cell = cells("B19")
cell.PutValue("1")
cell = cells("B20")
cell.PutValue("2")
cell = cells("B21")
cell.PutValue("3")
cell = cells("B22")
cell.PutValue("3")
cell = cells("B23")
cell.PutValue("4")
cell = cells("B24")
cell.PutValue("4")
cell = cells("B25")
cell.PutValue("1")
cell = cells("B26")
cell.PutValue("2")
cell = cells("B27")
cell.PutValue("3")
cell = cells("B28")
cell.PutValue("1")
cell = cells("B29")
cell.PutValue("2")
cell = cells("B30")
cell.PutValue("3")
cell = cells("C2")
cell.PutValue("Maxilaku")
cell = cells("C3")
cell.PutValue("Maxilaku")
cell = cells("C4")
cell.PutValue("Chai")
cell = cells("C5")
cell.PutValue("Maxilaku")
cell = cells("C6")
cell.PutValue("Chang")
cell = cells("C7")
cell.PutValue("Chang")
cell = cells("C8")
cell.PutValue("Chang")
cell = cells("C9")
cell.PutValue("Chang")
cell = cells("C10")
cell.PutValue("Chang")
cell = cells("C11")
cell.PutValue("Geitost")
cell = cells("C12")
cell.PutValue("Chai")
cell = cells("C13")
cell.PutValue("Geitost")
cell = cells("C14")
cell.PutValue("Geitost")
cell = cells("C15")
cell.PutValue("Maxilaku")
cell = cells("C16")
cell.PutValue("Geitost")
cell = cells("C17")
cell.PutValue("Geitost")
cell = cells("C18")
cell.PutValue("Ikuru")
cell = cells("C19")
cell.PutValue("Ikuru")
cell = cells("C20")
cell.PutValue("Ikuru")
cell = cells("C21")
cell.PutValue("Ikuru")
cell = cells("C22")
cell.PutValue("Ipoh Coffee")
cell = cells("C23")
cell.PutValue("Ipoh Coffee")
cell = cells("C24")
cell.PutValue("Ipoh Coffee")
cell = cells("C25")
cell.PutValue("Chocolade")
cell = cells("C26")
cell.PutValue("Chocolade")
cell = cells("C27")
cell.PutValue("Chocolade")
cell = cells("C28")
cell.PutValue("Chocolade")
cell = cells("C29")
cell.PutValue("Chocolade")
cell = cells("C30")
cell.PutValue("Chocolade")
cell = cells("D2")
cell.PutValue("Asia")
cell = cells("D3")
cell.PutValue("Asia")
cell = cells("D4")
cell.PutValue("Asia")
cell = cells("D5")
cell.PutValue("Asia")
cell = cells("D6")
cell.PutValue("Europe")
cell = cells("D7")
cell.PutValue("Europe")
cell = cells("D8")
cell.PutValue("Europe")
cell = cells("D9")
cell.PutValue("Europe")
cell = cells("D10")
cell.PutValue("Europe")
cell = cells("D11")
cell.PutValue("America")
cell = cells("D12")
cell.PutValue("America")
cell = cells("D13")
cell.PutValue("America")
cell = cells("D14")
cell.PutValue("America")
cell = cells("D15")
cell.PutValue("America")
cell = cells("D16")
cell.PutValue("America")
cell = cells("D17")
cell.PutValue("America")
cell = cells("D18")
cell.PutValue("Europe")
cell = cells("D19")
cell.PutValue("Europe")
cell = cells("D20")
cell.PutValue("Europe")
cell = cells("D21")
cell.PutValue("Oceania")
cell = cells("D22")
cell.PutValue("Oceania")
cell = cells("D23")
cell.PutValue("Oceania")
cell = cells("D24")
cell.PutValue("Oceania")
cell = cells("D25")
cell.PutValue("Africa")
cell = cells("D26")
cell.PutValue("Africa")
cell = cells("D27")
cell.PutValue("Africa")
cell = cells("D28")
cell.PutValue("Africa")
cell = cells("D29")
cell.PutValue("Africa")
cell = cells("D30")
cell.PutValue("Africa")
cell = cells("E2")
cell.PutValue("China")
cell = cells("E3")
cell.PutValue("India")
cell = cells("E4")
cell.PutValue("Korea")
cell = cells("E5")
cell.PutValue("India")
cell = cells("E6")
cell.PutValue("France")
cell = cells("E7")
cell.PutValue("France")
cell = cells("E8")
cell.PutValue("Germany")
cell = cells("E9")
cell.PutValue("Italy")
cell = cells("E10")
cell.PutValue("France")
cell = cells("E11")
cell.PutValue("U.S.")
cell = cells("E12")
cell.PutValue("U.S.")
cell = cells("E13")
cell.PutValue("Brazil")
cell = cells("E14")
cell.PutValue("U.S.")
cell = cells("E15")
cell.PutValue("U.S.")
cell = cells("E16")
cell.PutValue("Canada")
cell = cells("E17")
cell.PutValue("U.S.")
cell = cells("E18")
cell.PutValue("Italy")
cell = cells("E19")
cell.PutValue("France")
cell = cells("E20")
cell.PutValue("Italy")
cell = cells("E21")
cell.PutValue("New Zealand")
cell = cells("E22")
cell.PutValue("Australia")
cell = cells("E23")
cell.PutValue("Australia")
cell = cells("E24")
cell.PutValue("New Zealand")
cell = cells("E25")
cell.PutValue("S.Africa")
cell = cells("E26")
cell.PutValue("S.Africa")
cell = cells("E27")
cell.PutValue("S.Africa")
cell = cells("E28")
cell.PutValue("Egypt")
cell = cells("E29")
cell.PutValue("Egypt")
cell = cells("E30")
cell.PutValue("Egypt")
cell = cells("F2")
cell.PutValue(2000)
cell = cells("F3")
cell.PutValue(500)
cell = cells("F4")
cell.PutValue(1200)
cell = cells("F5")
cell.PutValue(1500)
cell = cells("F6")
cell.PutValue(500)
cell = cells("F7")
cell.PutValue(1500)
cell = cells("F8")
cell.PutValue(800)
cell = cells("F9")
cell.PutValue(900)
cell = cells("F10")
cell.PutValue(500)
cell = cells("F11")
cell.PutValue(1600)
cell = cells("F12")
cell.PutValue(600)
cell = cells("F13")
cell.PutValue(2000)
cell = cells("F14")
cell.PutValue(500)
cell = cells("F15")
cell.PutValue(900)
cell = cells("F16")
cell.PutValue(700)
cell = cells("F17")
cell.PutValue(1400)
cell = cells("F18")
cell.PutValue(1350)
cell = cells("F19")
cell.PutValue(300)
cell = cells("F20")
cell.PutValue(500)
cell = cells("F21")
cell.PutValue(1000)
cell = cells("F22")
cell.PutValue(1500)
cell = cells("F23")
cell.PutValue(1500)
cell = cells("F24")
cell.PutValue(1600)
cell = cells("F25")
cell.PutValue(1000)
cell = cells("F26")
cell.PutValue(1200)
cell = cells("F27")
cell.PutValue(1300)
cell = cells("F28")
cell.PutValue(1500)
cell = cells("F29")
cell.PutValue(1400)
cell = cells("F30")
cell.PutValue(1000)
'Adding a new sheet
Dim sheet2 As Worksheet = workbook.Worksheets(workbook.Worksheets.Add())
'Naming the sheet
sheet2.Name = "PivotTable"
'Getting the pivottables collection in the sheet
Dim pivotTables As Aspose.Cells.Pivot.PivotTableCollection = sheet2.PivotTables
'Adding a PivotTable to the worksheet
Dim index As Integer = pivotTables.Add("=Data!A1:F30", "B3", "PivotTable1")
'Accessing the instance of the newly added PivotTable
Dim pivotTable As Aspose.Cells.Pivot.PivotTable = pivotTables(index)
'Showing the grand totals
pivotTable.RowGrand = True
pivotTable.ColumnGrand = True
'Setting the PivotTable report is automatically formatted
pivotTable.IsAutoFormat = True
'Setting the PivotTable autoformat type.
pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report6
'Draging the first field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0)
'Draging the third field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 2)
'Draging the second field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 1)
'Draging the fourth field to the column area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 3)
'Draging the fifth field to the data area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 5)
'Setting the number format of the first data field
pivotTable.DataFields(0).NumberFormat = "$#,##0.00"
'Saving the Excel file
workbook.Save("f:\test\pivotTable_test.xls")
[C#]
//Instantiating an Workbook object
Workbook workbook = new Workbook();
//Obtaining the reference of the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Name the sheet
sheet.Name = "Data";
Cells cells = sheet.Cells;
//Setting the values to the cells
Cell cell = cells["A1"];
cell.PutValue("Employee");
cell = cells["B1"];
cell.PutValue("Quarter");
cell = cells["C1"];
cell.PutValue("Product");
cell = cells["D1"];
cell.PutValue("Continent");
cell = cells["E1"];
cell.PutValue("Country");
cell = cells["F1"];
cell.PutValue("Sale");
cell = cells["A2"];
cell.PutValue("David");
cell = cells["A3"];
cell.PutValue("David");
cell = cells["A4"];
cell.PutValue("David");
cell = cells["A5"];
cell.PutValue("David");
cell = cells["A6"];
cell.PutValue("James");
cell = cells["A7"];
cell.PutValue("James");
cell = cells["A8"];
cell.PutValue("James");
cell = cells["A9"];
cell.PutValue("James");
cell = cells["A10"];
cell.PutValue("James");
cell = cells["A11"];
cell.PutValue("Miya");
cell = cells["A12"];
cell.PutValue("Miya");
cell = cells["A13"];
cell.PutValue("Miya");
cell = cells["A14"];
cell.PutValue("Miya");
cell = cells["A15"];
cell.PutValue("Miya");
cell = cells["A16"];
cell.PutValue("Miya");
cell = cells["A17"];
cell.PutValue("Miya");
cell = cells["A18"];
cell.PutValue("Elvis");
cell = cells["A19"];
cell.PutValue("Elvis");
cell = cells["A20"];
cell.PutValue("Elvis");
cell = cells["A21"];
cell.PutValue("Elvis");
cell = cells["A22"];
cell.PutValue("Elvis");
cell = cells["A23"];
cell.PutValue("Elvis");
cell = cells["A24"];
cell.PutValue("Elvis");
cell = cells["A25"];
cell.PutValue("Jean");
cell = cells["A26"];
cell.PutValue("Jean");
cell = cells["A27"];
cell.PutValue("Jean");
cell = cells["A28"];
cell.PutValue("Ada");
cell = cells["A29"];
cell.PutValue("Ada");
cell = cells["A30"];
cell.PutValue("Ada");
cell = cells["B2"];
cell.PutValue("1");
cell = cells["B3"];
cell.PutValue("2");
cell = cells["B4"];
cell.PutValue("3");
cell = cells["B5"];
cell.PutValue("4");
cell = cells["B6"];
cell.PutValue("1");
cell = cells["B7"];
cell.PutValue("2");
cell = cells["B8"];
cell.PutValue("3");
cell = cells["B9"];
cell.PutValue("4");
cell = cells["B10"];
cell.PutValue("4");
cell = cells["B11"];
cell.PutValue("1");
cell = cells["B12"];
cell.PutValue("1");
cell = cells["B13"];
cell.PutValue("2");
cell = cells["B14"];
cell.PutValue("2");
cell = cells["B15"];
cell.PutValue("3");
cell = cells["B16"];
cell.PutValue("4");
cell = cells["B17"];
cell.PutValue("4");
cell = cells["B18"];
cell.PutValue("1");
cell = cells["B19"];
cell.PutValue("1");
cell = cells["B20"];
cell.PutValue("2");
cell = cells["B21"];
cell.PutValue("3");
cell = cells["B22"];
cell.PutValue("3");
cell = cells["B23"];
cell.PutValue("4");
cell = cells["B24"];
cell.PutValue("4");
cell = cells["B25"];
cell.PutValue("1");
cell = cells["B26"];
cell.PutValue("2");
cell = cells["B27"];
cell.PutValue("3");
cell = cells["B28"];
cell.PutValue("1");
cell = cells["B29"];
cell.PutValue("2");
cell = cells["B30"];
cell.PutValue("3");
cell = cells["C2"];
cell.PutValue("Maxilaku");
cell = cells["C3"];
cell.PutValue("Maxilaku");
cell = cells["C4"];
cell.PutValue("Chai");
cell = cells["C5"];
cell.PutValue("Maxilaku");
cell = cells["C6"];
cell.PutValue("Chang");
cell = cells["C7"];
cell.PutValue("Chang");
cell = cells["C8"];
cell.PutValue("Chang");
cell = cells["C9"];
cell.PutValue("Chang");
cell = cells["C10"];
cell.PutValue("Chang");
cell = cells["C11"];
cell.PutValue("Geitost");
cell = cells["C12"];
cell.PutValue("Chai");
cell = cells["C13"];
cell.PutValue("Geitost");
cell = cells["C14"];
cell.PutValue("Geitost");
cell = cells["C15"];
cell.PutValue("Maxilaku");
cell = cells["C16"];
cell.PutValue("Geitost");
cell = cells["C17"];
cell.PutValue("Geitost");
cell = cells["C18"];
cell.PutValue("Ikuru");
cell = cells["C19"];
cell.PutValue("Ikuru");
cell = cells["C20"];
cell.PutValue("Ikuru");
cell = cells["C21"];
cell.PutValue("Ikuru");
cell = cells["C22"];
cell.PutValue("Ipoh Coffee");
cell = cells["C23"];
cell.PutValue("Ipoh Coffee");
cell = cells["C24"];
cell.PutValue("Ipoh Coffee");
cell = cells["C25"];
cell.PutValue("Chocolade");
cell = cells["C26"];
cell.PutValue("Chocolade");
cell = cells["C27"];
cell.PutValue("Chocolade");
cell = cells["C28"];
cell.PutValue("Chocolade");
cell = cells["C29"];
cell.PutValue("Chocolade");
cell = cells["C30"];
cell.PutValue("Chocolade");
cell = cells["D2"];
cell.PutValue("Asia");
cell = cells["D3"];
cell.PutValue("Asia");
cell = cells["D4"];
cell.PutValue("Asia");
cell = cells["D5"];
cell.PutValue("Asia");
cell = cells["D6"];
cell.PutValue("Europe");
cell = cells["D7"];
cell.PutValue("Europe");
cell = cells["D8"];
cell.PutValue("Europe");
cell = cells["D9"];
cell.PutValue("Europe");
cell = cells["D10"];
cell.PutValue("Europe");
cell = cells["D11"];
cell.PutValue("America");
cell = cells["D12"];
cell.PutValue("America");
cell = cells["D13"];
cell.PutValue("America");
cell = cells["D14"];
cell.PutValue("America");
cell = cells["D15"];
cell.PutValue("America");
cell = cells["D16"];
cell.PutValue("America");
cell = cells["D17"];
cell.PutValue("America");
cell = cells["D18"];
cell.PutValue("Europe");
cell = cells["D19"];
cell.PutValue("Europe");
cell = cells["D20"];
cell.PutValue("Europe");
cell = cells["D21"];
cell.PutValue("Oceania");
cell = cells["D22"];
cell.PutValue("Oceania");
cell = cells["D23"];
cell.PutValue("Oceania");
cell = cells["D24"];
cell.PutValue("Oceania");
cell = cells["D25"];
cell.PutValue("Africa");
cell = cells["D26"];
cell.PutValue("Africa");
cell = cells["D27"];
cell.PutValue("Africa");
cell = cells["D28"];
cell.PutValue("Africa");
cell = cells["D29"];
cell.PutValue("Africa");
cell = cells["D30"];
cell.PutValue("Africa");
cell = cells["E2"];
cell.PutValue("China");
cell = cells["E3"];
cell.PutValue("India");
cell = cells["E4"];
cell.PutValue("Korea");
cell = cells["E5"];
cell.PutValue("India");
cell = cells["E6"];
cell.PutValue("France");
cell = cells["E7"];
cell.PutValue("France");
cell = cells["E8"];
cell.PutValue("Germany");
cell = cells["E9"];
cell.PutValue("Italy");
cell = cells["E10"];
cell.PutValue("France");
cell = cells["E11"];
cell.PutValue("U.S.");
cell = cells["E12"];
cell.PutValue("U.S.");
cell = cells["E13"];
cell.PutValue("Brazil");
cell = cells["E14"];
cell.PutValue("U.S.");
cell = cells["E15"];
cell.PutValue("U.S.");
cell = cells["E16"];
cell.PutValue("Canada");
cell = cells["E17"];
cell.PutValue("U.S.");
cell = cells["E18"];
cell.PutValue("Italy");
cell = cells["E19"];
cell.PutValue("France");
cell = cells["E20"];
cell.PutValue("Italy");
cell = cells["E21"];
cell.PutValue("New Zealand");
cell = cells["E22"];
cell.PutValue("Australia");
cell = cells["E23"];
cell.PutValue("Australia");
cell = cells["E24"];
cell.PutValue("New Zealand");
cell = cells["E25"];
cell.PutValue("S.Africa");
cell = cells["E26"];
cell.PutValue("S.Africa");
cell = cells["E27"];
cell.PutValue("S.Africa");
cell = cells["E28"];
cell.PutValue("Egypt");
cell = cells["E29"];
cell.PutValue("Egypt");
cell = cells["E30"];
cell.PutValue("Egypt");
cell = cells["F2"];
cell.PutValue(2000);
cell = cells["F3"];
cell.PutValue(500);
cell = cells["F4"];
cell.PutValue(1200);
cell = cells["F5"];
cell.PutValue(1500);
cell = cells["F6"];
cell.PutValue(500);
cell = cells["F7"];
cell.PutValue(1500);
cell = cells["F8"];
cell.PutValue(800);
cell = cells["F9"];
cell.PutValue(900);
cell = cells["F10"];
cell.PutValue(500);
cell = cells["F11"];
cell.PutValue(1600);
cell = cells["F12"];
cell.PutValue(600);
cell = cells["F13"];
cell.PutValue(2000);
cell = cells["F14"];
cell.PutValue(500);
cell = cells["F15"];
cell.PutValue(900);
cell = cells["F16"];
cell.PutValue(700);
cell = cells["F17"];
cell.PutValue(1400);
cell = cells["F18"];
cell.PutValue(1350);
cell = cells["F19"];
cell.PutValue(300);
cell = cells["F20"];
cell.PutValue(500);
cell = cells["F21"];
cell.PutValue(1000);
cell = cells["F22"];
cell.PutValue(1500);
cell = cells["F23"];
cell.PutValue(1500);
cell = cells["F24"];
cell.PutValue(1600);
cell = cells["F25"];
cell.PutValue(1000);
cell = cells["F26"];
cell.PutValue(1200);
cell = cells["F27"];
cell.PutValue(1300);
cell = cells["F28"];
cell.PutValue(1500);
cell = cells["F29"];
cell.PutValue(1400);
cell = cells["F30"];
cell.PutValue(1000);
//Adding a new sheet
Worksheet sheet2 = workbook.Worksheets[workbook.Worksheets.Add()];
//Naming the sheet
sheet2.Name = "PivotTable";
//Getting the pivottables collection in the sheet
Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet2.PivotTables;
//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=Data!A1:F30", "B3", "PivotTable1");
//Accessing the instance of the newly added PivotTable
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];
//Showing the grand totals
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;
//Setting the PivotTable report is automatically formatted
pivotTable.IsAutoFormat = true;
//Setting the PivotTable autoformat type.
pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report6;
//Draging the first field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);
//Draging the third field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 2);
//Draging the second field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 1);
//Draging the fourth field to the column area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 3);
//Draging the fifth field to the data area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 5);
//Setting the number format of the first data field
pivotTable.DataFields[0].NumberFormat = "$#,##0.00";
//Saving the Excel file
workbook.Save("f:\\test\\pivotTable_test.xls");
[VB]
'Instantiating an Workbook object
Dim workbook As New Workbook()
'Obtaining the reference of the first worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
'Name the sheet
sheet.Name = "Data"
Dim cells As Cells = sheet.Cells
'Setting the values to the cells
Dim cell As Cell = cells("A1")
cell.PutValue("Employee")
cell = cells("B1")
cell.PutValue("Quarter")
cell = cells("C1")
cell.PutValue("Product")
cell = cells("D1")
cell.PutValue("Continent")
cell = cells("E1")
cell.PutValue("Country")
cell = cells("F1")
cell.PutValue("Sale")
cell = cells("A2")
cell.PutValue("David")
cell = cells("A3")
cell.PutValue("David")
cell = cells("A4")
cell.PutValue("David")
cell = cells("A5")
cell.PutValue("David")
cell = cells("A6")
cell.PutValue("James")
cell = cells("A7")
cell.PutValue("James")
cell = cells("A8")
cell.PutValue("James")
cell = cells("A9")
cell.PutValue("James")
cell = cells("A10")
cell.PutValue("James")
cell = cells("A11")
cell.PutValue("Miya")
cell = cells("A12")
cell.PutValue("Miya")
cell = cells("A13")
cell.PutValue("Miya")
cell = cells("A14")
cell.PutValue("Miya")
cell = cells("A15")
cell.PutValue("Miya")
cell = cells("A16")
cell.PutValue("Miya")
cell = cells("A17")
cell.PutValue("Miya")
cell = cells("A18")
cell.PutValue("Elvis")
cell = cells("A19")
cell.PutValue("Elvis")
cell = cells("A20")
cell.PutValue("Elvis")
cell = cells("A21")
cell.PutValue("Elvis")
cell = cells("A22")
cell.PutValue("Elvis")
cell = cells("A23")
cell.PutValue("Elvis")
cell = cells("A24")
cell.PutValue("Elvis")
cell = cells("A25")
cell.PutValue("Jean")
cell = cells("A26")
cell.PutValue("Jean")
cell = cells("A27")
cell.PutValue("Jean")
cell = cells("A28")
cell.PutValue("Ada")
cell = cells("A29")
cell.PutValue("Ada")
cell = cells("A30")
cell.PutValue("Ada")
cell = cells("B2")
cell.PutValue("1")
cell = cells("B3")
cell.PutValue("2")
cell = cells("B4")
cell.PutValue("3")
cell = cells("B5")
cell.PutValue("4")
cell = cells("B6")
cell.PutValue("1")
cell = cells("B7")
cell.PutValue("2")
cell = cells("B8")
cell.PutValue("3")
cell = cells("B9")
cell.PutValue("4")
cell = cells("B10")
cell.PutValue("4")
cell = cells("B11")
cell.PutValue("1")
cell = cells("B12")
cell.PutValue("1")
cell = cells("B13")
cell.PutValue("2")
cell = cells("B14")
cell.PutValue("2")
cell = cells("B15")
cell.PutValue("3")
cell = cells("B16")
cell.PutValue("4")
cell = cells("B17")
cell.PutValue("4")
cell = cells("B18")
cell.PutValue("1")
cell = cells("B19")
cell.PutValue("1")
cell = cells("B20")
cell.PutValue("2")
cell = cells("B21")
cell.PutValue("3")
cell = cells("B22")
cell.PutValue("3")
cell = cells("B23")
cell.PutValue("4")
cell = cells("B24")
cell.PutValue("4")
cell = cells("B25")
cell.PutValue("1")
cell = cells("B26")
cell.PutValue("2")
cell = cells("B27")
cell.PutValue("3")
cell = cells("B28")
cell.PutValue("1")
cell = cells("B29")
cell.PutValue("2")
cell = cells("B30")
cell.PutValue("3")
cell = cells("C2")
cell.PutValue("Maxilaku")
cell = cells("C3")
cell.PutValue("Maxilaku")
cell = cells("C4")
cell.PutValue("Chai")
cell = cells("C5")
cell.PutValue("Maxilaku")
cell = cells("C6")
cell.PutValue("Chang")
cell = cells("C7")
cell.PutValue("Chang")
cell = cells("C8")
cell.PutValue("Chang")
cell = cells("C9")
cell.PutValue("Chang")
cell = cells("C10")
cell.PutValue("Chang")
cell = cells("C11")
cell.PutValue("Geitost")
cell = cells("C12")
cell.PutValue("Chai")
cell = cells("C13")
cell.PutValue("Geitost")
cell = cells("C14")
cell.PutValue("Geitost")
cell = cells("C15")
cell.PutValue("Maxilaku")
cell = cells("C16")
cell.PutValue("Geitost")
cell = cells("C17")
cell.PutValue("Geitost")
cell = cells("C18")
cell.PutValue("Ikuru")
cell = cells("C19")
cell.PutValue("Ikuru")
cell = cells("C20")
cell.PutValue("Ikuru")
cell = cells("C21")
cell.PutValue("Ikuru")
cell = cells("C22")
cell.PutValue("Ipoh Coffee")
cell = cells("C23")
cell.PutValue("Ipoh Coffee")
cell = cells("C24")
cell.PutValue("Ipoh Coffee")
cell = cells("C25")
cell.PutValue("Chocolade")
cell = cells("C26")
cell.PutValue("Chocolade")
cell = cells("C27")
cell.PutValue("Chocolade")
cell = cells("C28")
cell.PutValue("Chocolade")
cell = cells("C29")
cell.PutValue("Chocolade")
cell = cells("C30")
cell.PutValue("Chocolade")
cell = cells("D2")
cell.PutValue("Asia")
cell = cells("D3")
cell.PutValue("Asia")
cell = cells("D4")
cell.PutValue("Asia")
cell = cells("D5")
cell.PutValue("Asia")
cell = cells("D6")
cell.PutValue("Europe")
cell = cells("D7")
cell.PutValue("Europe")
cell = cells("D8")
cell.PutValue("Europe")
cell = cells("D9")
cell.PutValue("Europe")
cell = cells("D10")
cell.PutValue("Europe")
cell = cells("D11")
cell.PutValue("America")
cell = cells("D12")
cell.PutValue("America")
cell = cells("D13")
cell.PutValue("America")
cell = cells("D14")
cell.PutValue("America")
cell = cells("D15")
cell.PutValue("America")
cell = cells("D16")
cell.PutValue("America")
cell = cells("D17")
cell.PutValue("America")
cell = cells("D18")
cell.PutValue("Europe")
cell = cells("D19")
cell.PutValue("Europe")
cell = cells("D20")
cell.PutValue("Europe")
cell = cells("D21")
cell.PutValue("Oceania")
cell = cells("D22")
cell.PutValue("Oceania")
cell = cells("D23")
cell.PutValue("Oceania")
cell = cells("D24")
cell.PutValue("Oceania")
cell = cells("D25")
cell.PutValue("Africa")
cell = cells("D26")
cell.PutValue("Africa")
cell = cells("D27")
cell.PutValue("Africa")
cell = cells("D28")
cell.PutValue("Africa")
cell = cells("D29")
cell.PutValue("Africa")
cell = cells("D30")
cell.PutValue("Africa")
cell = cells("E2")
cell.PutValue("China")
cell = cells("E3")
cell.PutValue("India")
cell = cells("E4")
cell.PutValue("Korea")
cell = cells("E5")
cell.PutValue("India")
cell = cells("E6")
cell.PutValue("France")
cell = cells("E7")
cell.PutValue("France")
cell = cells("E8")
cell.PutValue("Germany")
cell = cells("E9")
cell.PutValue("Italy")
cell = cells("E10")
cell.PutValue("France")
cell = cells("E11")
cell.PutValue("U.S.")
cell = cells("E12")
cell.PutValue("U.S.")
cell = cells("E13")
cell.PutValue("Brazil")
cell = cells("E14")
cell.PutValue("U.S.")
cell = cells("E15")
cell.PutValue("U.S.")
cell = cells("E16")
cell.PutValue("Canada")
cell = cells("E17")
cell.PutValue("U.S.")
cell = cells("E18")
cell.PutValue("Italy")
cell = cells("E19")
cell.PutValue("France")
cell = cells("E20")
cell.PutValue("Italy")
cell = cells("E21")
cell.PutValue("New Zealand")
cell = cells("E22")
cell.PutValue("Australia")
cell = cells("E23")
cell.PutValue("Australia")
cell = cells("E24")
cell.PutValue("New Zealand")
cell = cells("E25")
cell.PutValue("S.Africa")
cell = cells("E26")
cell.PutValue("S.Africa")
cell = cells("E27")
cell.PutValue("S.Africa")
cell = cells("E28")
cell.PutValue("Egypt")
cell = cells("E29")
cell.PutValue("Egypt")
cell = cells("E30")
cell.PutValue("Egypt")
cell = cells("F2")
cell.PutValue(2000)
cell = cells("F3")
cell.PutValue(500)
cell = cells("F4")
cell.PutValue(1200)
cell = cells("F5")
cell.PutValue(1500)
cell = cells("F6")
cell.PutValue(500)
cell = cells("F7")
cell.PutValue(1500)
cell = cells("F8")
cell.PutValue(800)
cell = cells("F9")
cell.PutValue(900)
cell = cells("F10")
cell.PutValue(500)
cell = cells("F11")
cell.PutValue(1600)
cell = cells("F12")
cell.PutValue(600)
cell = cells("F13")
cell.PutValue(2000)
cell = cells("F14")
cell.PutValue(500)
cell = cells("F15")
cell.PutValue(900)
cell = cells("F16")
cell.PutValue(700)
cell = cells("F17")
cell.PutValue(1400)
cell = cells("F18")
cell.PutValue(1350)
cell = cells("F19")
cell.PutValue(300)
cell = cells("F20")
cell.PutValue(500)
cell = cells("F21")
cell.PutValue(1000)
cell = cells("F22")
cell.PutValue(1500)
cell = cells("F23")
cell.PutValue(1500)
cell = cells("F24")
cell.PutValue(1600)
cell = cells("F25")
cell.PutValue(1000)
cell = cells("F26")
cell.PutValue(1200)
cell = cells("F27")
cell.PutValue(1300)
cell = cells("F28")
cell.PutValue(1500)
cell = cells("F29")
cell.PutValue(1400)
cell = cells("F30")
cell.PutValue(1000)
'Adding a new sheet
Dim sheet2 As Worksheet = workbook.Worksheets(workbook.Worksheets.Add())
'Naming the sheet
sheet2.Name = "PivotTable"
'Getting the pivottables collection in the sheet
Dim pivotTables As Aspose.Cells.Pivot.PivotTableCollection = sheet2.PivotTables
'Adding a PivotTable to the worksheet
Dim index As Integer = pivotTables.Add("=Data!A1:F30", "B3", "PivotTable1")
'Accessing the instance of the newly added PivotTable
Dim pivotTable As Aspose.Cells.Pivot.PivotTable = pivotTables(index)
'Showing the grand totals
pivotTable.RowGrand = True
pivotTable.ColumnGrand = True
'Setting the PivotTable report is automatically formatted
pivotTable.IsAutoFormat = True
'Setting the PivotTable autoformat type.
pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report6
'Draging the first field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0)
'Draging the third field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 2)
'Draging the second field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 1)
'Draging the fourth field to the column area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 3)
'Draging the fifth field to the data area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 5)
'Setting the number format of the first data field
pivotTable.DataFields(0).NumberFormat = "$#,##0.00"
'Saving the Excel file
workbook.Save("f:\test\pivotTable_test.xls")
Result Task 1:
After executing the above code, the following file is generated. Please see the snap shots of " pivotTable_test.xls " file.
After executing the above code, the following file is generated. Please see the snap shots of " pivotTable_test.xls " file.
1) The Data sheet
2) The PivotTable sheet
Task 2: Using Aspose.Cells to create a Pivot Chart based on the Pivot Table.
Create a Pivot Chart based on the Pivot Table (Code Snippet)
[C#]
//Instantiating an Workbook object
//Opening the excel file
Workbook workbook = new Workbook("f:\\test\\pivotTable_test.xls");
//Adding a new sheet
Worksheet sheet3 = workbook.Worksheets[workbook.Worksheets.Add(SheetType.Chart)];
//Naming the sheet
sheet3.Name = "PivotChart";
//Adding a column chart
int index = sheet3.Charts.Add(Aspose.Cells.Charts.ChartType.Column, 0, 5, 28, 16);
//Setting the pivot chart data source
sheet3.Charts[index].PivotSource = "PivotTable!PivotTable1";
sheet3.Charts[index].HidePivotFieldButtons = false;
//Saving the Excel file
workbook.Save("f:\\test\\pivotChart_test.xls");
[VB]
'Instantiating an Workbook object
'Opening the excel file
Dim workbook As New Workbook("f:\test\pivotTable_test.xls")
'Adding a new sheet
Dim sheet3 As Worksheet = workbook.Worksheets(workbook.Worksheets.Add(SheetType.Chart))
'Naming the sheet
sheet3.Name = "PivotChart"
'Adding a column chart
Dim index As Integer = sheet3.Charts.Add(Aspose.Cells.Charts.ChartType.Column, 0, 5, 28, 16)
'Setting the pivot chart data source
sheet3.Charts(index).PivotSource = "PivotTable!PivotTable1"
sheet3.Charts(index).HidePivotFieldButtons = False
'Saving the Excel file
workbook.Save("f:\test\pivotChart_test.xls")
[C#]
//Instantiating an Workbook object
//Opening the excel file
Workbook workbook = new Workbook("f:\\test\\pivotTable_test.xls");
//Adding a new sheet
Worksheet sheet3 = workbook.Worksheets[workbook.Worksheets.Add(SheetType.Chart)];
//Naming the sheet
sheet3.Name = "PivotChart";
//Adding a column chart
int index = sheet3.Charts.Add(Aspose.Cells.Charts.ChartType.Column, 0, 5, 28, 16);
//Setting the pivot chart data source
sheet3.Charts[index].PivotSource = "PivotTable!PivotTable1";
sheet3.Charts[index].HidePivotFieldButtons = false;
//Saving the Excel file
workbook.Save("f:\\test\\pivotChart_test.xls");
[VB]
'Instantiating an Workbook object
'Opening the excel file
Dim workbook As New Workbook("f:\test\pivotTable_test.xls")
'Adding a new sheet
Dim sheet3 As Worksheet = workbook.Worksheets(workbook.Worksheets.Add(SheetType.Chart))
'Naming the sheet
sheet3.Name = "PivotChart"
'Adding a column chart
Dim index As Integer = sheet3.Charts.Add(Aspose.Cells.Charts.ChartType.Column, 0, 5, 28, 16)
'Setting the pivot chart data source
sheet3.Charts(index).PivotSource = "PivotTable!PivotTable1"
sheet3.Charts(index).HidePivotFieldButtons = False
'Saving the Excel file
workbook.Save("f:\test\pivotChart_test.xls")
Result Task 2:
After executing the above code, the following file is generated. Please see the snap shot of " pivotChart_test.xls " file.
The PivotChart sheet.
After executing the above code, the following file is generated. Please see the snap shot of " pivotChart_test.xls " file.
The PivotChart sheet.
Conclusion:
In this article I have presented how can we create Pivot Table and Pivot Chart using Aspose.Cells component. Hopefully, it will give you some insight, and you will be able to utilize these features according to your different scenarios.
Aspose.Cells can offer more flexibility than others for solutions and provides outstanding speed, efficiency and reliability to meet specific business application requirements. The results do show that Aspose.Cells has benefited from years of research, design and careful tuning.
We heartily welcome your queries, comments and suggestions at Aspose.Cells Forum. We warranty a prompt reply within minutes or hours, Thank you!
In this article I have presented how can we create Pivot Table and Pivot Chart using Aspose.Cells component. Hopefully, it will give you some insight, and you will be able to utilize these features according to your different scenarios.
Aspose.Cells can offer more flexibility than others for solutions and provides outstanding speed, efficiency and reliability to meet specific business application requirements. The results do show that Aspose.Cells has benefited from years of research, design and careful tuning.
We heartily welcome your queries, comments and suggestions at Aspose.Cells Forum. We warranty a prompt reply within minutes or hours, Thank you!