Create Pivot Tables & Pivot Charts in an Excel Worksheet

				This technical tip show how developers can create Pivot Tables and Pivot Charts using Aspose.Cells component. A Pivot Table is an interactive summary of records. For example, you may have hundreds of invoice entries in a list on your worksheet. A Pivot Table can total the invoices by customer, product or date. In MS Excel, y ou can quickly re - arrange the information in the Pivot Table by dragging the buttons to a new position. A Pivot Chart is an interactive graphical representation of the data in a Pivot Table. This feature was firstly introduced in Excel 2000. Using a Pivot Chart makes it even easier to understand your data since the Pivot Table creates your subtotals and totals for you automatically. Aspose.Cells is a feature rich component that supports to create Pivot Tables and Pivot Charts for your requirement. Aspose.Cells provides some special set of classes 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 .
Create a Pivot Table in the Workbook (Code Snippet).
[Java]
 
//Instantiating an Workbook object
 Workbook workbook = new Workbook();
 //Obtaining the reference of the first worksheet
 Worksheet sheet = workbook.getWorksheets().get(0);
 //Name the sheet
 sheet.setName("Data");
 Cells cells = sheet.getCells();
 
 //Setting the values to the cells
 Cell cell = cells.get("A1");
 cell.setValue("Employee");
 cell = cells.get("B1");
 cell.setValue("Quarter");
 cell = cells.get("C1");
 cell.setValue("Product");
 cell = cells.get("D1");
 cell.setValue("Continent");
 cell = cells.get("E1");
 cell.setValue("Country");
 cell = cells.get("F1");
 cell.setValue("Sale");
 cell = cells.get("A2");
 cell.setValue("David");
 cell = cells.get("A3");
 cell.setValue("David");
 cell = cells.get("A4");
 cell.setValue("David");
 cell = cells.get("A5");
 cell.setValue("David");
 cell = cells.get("A6");
 cell.setValue("James");
 cell = cells.get("A7");
 cell.setValue("James");
 cell = cells.get("A8");
 cell.setValue("James");
 cell = cells.get("A9");
 cell.setValue("James");
 cell = cells.get("A10");
 cell.setValue("James");
 cell = cells.get("A11");
 cell.setValue("Miya");
 cell = cells.get("A12");
 cell.setValue("Miya");
 cell = cells.get("A13");
 cell.setValue("Miya");
 cell = cells.get("A14");
 cell.setValue("Miya");
 cell = cells.get("A15");
 cell.setValue("Miya");
 cell = cells.get("A16");
 cell.setValue("Miya");
 cell = cells.get("A17");
 cell.setValue("Miya");
 cell = cells.get("A18");
 cell.setValue("Elvis");
 cell = cells.get("A19");
 cell.setValue("Elvis");
 cell = cells.get("A20");
 cell.setValue("Elvis");
 cell = cells.get("A21");
 cell.setValue("Elvis");
 cell = cells.get("A22");
 cell.setValue("Elvis");
 cell = cells.get("A23");
 cell.setValue("Elvis");
 cell = cells.get("A24");
 cell.setValue("Elvis");
 cell = cells.get("A25");
 cell.setValue("Jean");
 cell = cells.get("A26");
 cell.setValue("Jean");
 cell = cells.get("A27");
 cell.setValue("Jean");
 cell = cells.get("A28");
 cell.setValue("Ada");
 cell = cells.get("A29");
 cell.setValue("Ada");
 cell = cells.get("A30");
 cell.setValue("Ada");
 cell = cells.get("B2");
 cell.setValue("1");
 cell = cells.get("B3");
 cell.setValue("2");
 cell = cells.get("B4");
 cell.setValue("3");
 cell = cells.get("B5");
 cell.setValue("4");
 cell = cells.get("B6");
 cell.setValue("1");
 cell = cells.get("B7");
 cell.setValue("2");
 cell = cells.get("B8");
 cell.setValue("3");
 cell = cells.get("B9");
 cell.setValue("4");
 cell = cells.get("B10");
 cell.setValue("4");
 cell = cells.get("B11");
 cell.setValue("1");
 cell = cells.get("B12");
 cell.setValue("1");
 cell = cells.get("B13");
 cell.setValue("2");
 cell = cells.get("B14");
 cell.setValue("2");
 cell = cells.get("B15");
 cell.setValue("3");
 cell = cells.get("B16");
 cell.setValue("4");
 cell = cells.get("B17");
 cell.setValue("4");
 cell = cells.get("B18");
 cell.setValue("1");
 cell = cells.get("B19");
 cell.setValue("1");
 cell = cells.get("B20");
 cell.setValue("2");
 cell = cells.get("B21");
 cell.setValue("3");
 cell = cells.get("B22");
 cell.setValue("3");
 cell = cells.get("B23");
 cell.setValue("4");
 cell = cells.get("B24");
 cell.setValue("4");
 cell = cells.get("B25");
 cell.setValue("1");
 cell = cells.get("B26");
 cell.setValue("2");
 cell = cells.get("B27");
 cell.setValue("3");
 cell = cells.get("B28");
 cell.setValue("1");
 cell = cells.get("B29");
 cell.setValue("2");
 cell = cells.get("B30");
 cell.setValue("3");
 cell = cells.get("C2");
 cell.setValue("Maxilaku");
 cell = cells.get("C3");
 cell.setValue("Maxilaku");
 cell = cells.get("C4");
 cell.setValue("Chai");
 cell = cells.get("C5");
 cell.setValue("Maxilaku");
 cell = cells.get("C6");
 cell.setValue("Chang");
 cell = cells.get("C7");
 cell.setValue("Chang");
 cell = cells.get("C8");
 cell.setValue("Chang");
 cell = cells.get("C9");
 cell.setValue("Chang");
 cell = cells.get("C10");
 cell.setValue("Chang");
 cell = cells.get("C11");
 cell.setValue("Geitost");
 cell = cells.get("C12");
 cell.setValue("Chai");
 cell = cells.get("C13");
 cell.setValue("Geitost");
 cell = cells.get("C14");
 cell.setValue("Geitost");
 cell = cells.get("C15");
 cell.setValue("Maxilaku");
 cell = cells.get("C16");
 cell.setValue("Geitost");
 cell = cells.get("C17");
 cell.setValue("Geitost");
 cell = cells.get("C18");
 cell.setValue("Ikuru");
 cell = cells.get("C19");
 cell.setValue("Ikuru");
 cell = cells.get("C20");
 cell.setValue("Ikuru");
 cell = cells.get("C21");
 cell.setValue("Ikuru");
 cell = cells.get("C22");
 cell.setValue("Ipoh Coffee");
 cell = cells.get("C23");
 cell.setValue("Ipoh Coffee");
 cell = cells.get("C24");
 cell.setValue("Ipoh Coffee");
 cell = cells.get("C25");
 cell.setValue("Chocolade");
 cell = cells.get("C26");
 cell.setValue("Chocolade");
 cell = cells.get("C27");
 cell.setValue("Chocolade");
 cell = cells.get("C28");
 cell.setValue("Chocolade");
 cell = cells.get("C29");
 cell.setValue("Chocolade");
 cell = cells.get("C30");
 cell.setValue("Chocolade");
 cell = cells.get("D2");
 cell.setValue("Asia");
 cell = cells.get("D3");
 cell.setValue("Asia");
 cell = cells.get("D4");
 cell.setValue("Asia");
 cell = cells.get("D5");
 cell.setValue("Asia");
 cell = cells.get("D6");
 cell.setValue("Europe");
 cell = cells.get("D7");
 cell.setValue("Europe");
 cell = cells.get("D8");
 cell.setValue("Europe");
 cell = cells.get("D9");
 cell.setValue("Europe");
 cell = cells.get("D10");
 cell.setValue("Europe");
 cell = cells.get("D11");
 cell.setValue("America");
 cell = cells.get("D12");
 cell.setValue("America");
 cell = cells.get("D13");
 cell.setValue("America");
 cell = cells.get("D14");
 cell.setValue("America");
 cell = cells.get("D15");
 cell.setValue("America");
 cell = cells.get("D16");
 cell.setValue("America");
 cell = cells.get("D17");
 cell.setValue("America");
 cell = cells.get("D18");
 cell.setValue("Europe");
 cell = cells.get("D19");
 cell.setValue("Europe");
 cell = cells.get("D20");
 cell.setValue("Europe");
 cell = cells.get("D21");
 cell.setValue("Oceania");
 cell = cells.get("D22");
 cell.setValue("Oceania");
 cell = cells.get("D23");
 cell.setValue("Oceania");
 cell = cells.get("D24");
 cell.setValue("Oceania");
 cell = cells.get("D25");
 cell.setValue("Africa");
 cell = cells.get("D26");
 cell.setValue("Africa");
 cell = cells.get("D27");
 cell.setValue("Africa");
 cell = cells.get("D28");
 cell.setValue("Africa");
 cell = cells.get("D29");
 cell.setValue("Africa");
 cell = cells.get("D30");
 cell.setValue("Africa");
 cell = cells.get("E2");
 cell.setValue("China");
 cell = cells.get("E3");
 cell.setValue("India");
 cell = cells.get("E4");
 cell.setValue("Korea");
 cell = cells.get("E5");
 cell.setValue("India");
 cell = cells.get("E6");
 cell.setValue("France");
 cell = cells.get("E7");
 cell.setValue("France");
 cell = cells.get("E8");
 cell.setValue("Germany");
 cell = cells.get("E9");
 cell.setValue("Italy");
 cell = cells.get("E10");
 cell.setValue("France");
 cell = cells.get("E11");
 cell.setValue("U.S.");
 cell = cells.get("E12");
 cell.setValue("U.S.");
 cell = cells.get("E13");
 cell.setValue("Brazil");
 cell = cells.get("E14");
 cell.setValue("U.S.");
 cell = cells.get("E15");
 cell.setValue("U.S.");
 cell = cells.get("E16");
 cell.setValue("Canada");
 cell = cells.get("E17");
 cell.setValue("U.S.");
 cell = cells.get("E18");
 cell.setValue("Italy");
 cell = cells.get("E19");
 cell.setValue("France");
 cell = cells.get("E20");
 cell.setValue("Italy");
 cell = cells.get("E21");
 cell.setValue("New Zealand");
 cell = cells.get("E22");
 cell.setValue("Australia");
 cell = cells.get("E23");
 cell.setValue("Australia");
 cell = cells.get("E24");
 cell.setValue("New Zealand");
 cell = cells.get("E25");
 cell.setValue("S.Africa");
 cell = cells.get("E26");
 cell.setValue("S.Africa");
 cell = cells.get("E27");
 cell.setValue("S.Africa");
 cell = cells.get("E28");
 cell.setValue("Egypt");
 cell = cells.get("E29");
 cell.setValue("Egypt");
 cell = cells.get("E30");
 cell.setValue("Egypt");
 cell = cells.get("F2");
 cell.setValue(2000);
 cell = cells.get("F3");
 cell.setValue(500);
 cell = cells.get("F4");
 cell.setValue(1200);
 cell = cells.get("F5");
 cell.setValue(1500);
 cell = cells.get("F6");
 cell.setValue(500);
 cell = cells.get("F7");
 cell.setValue(1500);
 cell = cells.get("F8");
 cell.setValue(800);
 cell = cells.get("F9");
 cell.setValue(900);
 cell = cells.get("F10");
 cell.setValue(500);
 cell = cells.get("F11");
 cell.setValue(1600);
 cell = cells.get("F12");
 cell.setValue(600);
 cell = cells.get("F13");
 cell.setValue(2000);
 cell = cells.get("F14");
 cell.setValue(500);
 cell = cells.get("F15");
 cell.setValue(900);
 cell = cells.get("F16");
 cell.setValue(700);
 cell = cells.get("F17");
 cell.setValue(1400);
 cell = cells.get("F18");
 cell.setValue(1350);
 cell = cells.get("F19");
 cell.setValue(300);
 cell = cells.get("F20");
 cell.setValue(500);
 cell = cells.get("F21");
 cell.setValue(1000);
 cell = cells.get("F22");
 cell.setValue(1500);
 cell = cells.get("F23");
 cell.setValue(1500);
 cell = cells.get("F24");
 cell.setValue(1600);
 cell = cells.get("F25");
 cell.setValue(1000);
 cell = cells.get("F26");
 cell.setValue(1200);
 cell = cells.get("F27");
 cell.setValue(1300);
 cell = cells.get("F28");
 cell.setValue(1500);
 cell = cells.get("F29");
 cell.setValue(1400);
 cell = cells.get("F30");
 cell.setValue(1000);
 //Adding a new sheet
 int sheetIndex = workbook.getWorksheets().add();
 Worksheet sheet2 = workbook.getWorksheets().get(sheetIndex);
 //Naming the sheet
 sheet2.setName("PivotTable");
 //Getting the pivottables collection in the sheet
 PivotTableCollection pivotTables = sheet2.getPivotTables();
 //Adding a PivotTable to the worksheet
 int index = pivotTables.add("=Data!A1:F30", "B3", "PivotTable1");
 //Accessing the instance of the newly added PivotTable
 PivotTable pivotTable = pivotTables.get(index);
 //Showing the grand totals
 pivotTable.setRowGrand(true);
 pivotTable.setColumnGrand(true);
 //Setting the PivotTable report is automatically formatted
 pivotTable.setAutoFormat(true);
 //Setting the PivotTable autoformat type.
 pivotTable.setAutoFormatType(PivotTableAutoFormatType.REPORT_6); 
 //Draging the first field to the row area.
 pivotTable.addFieldToArea(PivotFieldType.ROW, 0);
 //Draging the third field to the row area.
 pivotTable.addFieldToArea(PivotFieldType.ROW, 2);
 //Draging the second field to the row area.
 pivotTable.addFieldToArea(PivotFieldType.ROW, 1);
 //Draging the fourth field to the column area.
 pivotTable.addFieldToArea(PivotFieldType.COLUMN, 3);
 //Draging the fifth field to the data area.
 pivotTable.addFieldToArea(PivotFieldType.DATA, 5);
 //Setting the number format of the first data field
 pivotTable.getDataFields().get(0).setNumber(7);
 //Saving the Excel file 
 workbook.save("f:\\test\\pivotTable_test.xls");
 
 
Task2: Create a Pivot Chart based on the Pivot Table
[Java]
 
//Instantiating an Workbook object
 Workbook workbook = new Workbook("f:\\test\\pivotTable_test.xls");
 //Adding a new sheet
 int sheetIndex = workbook.getWorksheets().add(SheetType.CHART);
 Worksheet sheet3 = workbook.getWorksheets().get(sheetIndex);
 //Naming the sheet
 sheet3.setName("PivotChart");
 //Adding a column chart
int chartIndex = sheet3.getCharts().add(ChartType.COLUMN, 0, 5, 28, 16);
Chart chart = sheet3.getCharts().get(chartIndex);
//Setting the pivot chart data source
chart.setPivotSource("PivotTable!PivotTable1");
chart.setHidePivotFieldButtons(false);
//Saving the Excel file 
workbook.save("f:\\test\\pivotChart_test.xls");
 
More about Aspose.Cells for Java
- Homepage of Aspose.Cells for Java: http://www.aspose.com/java/excel-component.aspx
- Download Aspose.Cells for Java: http://www.aspose.com/community/files/72/java-components/aspose.cells-for-java/default.aspx
- Create Pivot Tables and Pivot Charts in .NET Applications: http://www.aspose.com/docs/display/cellsnet/Create+Pivot+Tables+and+Pivot+Charts