如何使用Aspose.Cells添加数据透视图

什么是数据透视图

数据透视表是数据的可视表示。数据透视图提供了一种总结、分析、探索和展示汇总数据的方法。以下是数据透视表的一些关键特性和方面：

1. 动态数据表示：数据透视图会自动更新以反映数据透视表中的更改。如果在数据透视表中添加或删除字段，数据透视图会相应地更新。

2. 交互式：数据透视图是交互式的，允许用户对数据进行筛选、排序和深入分析。这使得探索数据集的不同方面变得容易。

3. 灵活布局：用户可以通过拖放字段来更改数据透视图的布局，从而灵活可视化数据。

4. 各种图表类型：根据数据的性质和您希望获得的见解，可以使用各种图表类型来创建数据透视图，例如柱状图、折线图、饼图等。

5. 汇总：数据透视图总结大量数据并可显示总计、平均值、计数或其他汇总统计信息。

6. 筛选：它们提供筛选功能，允许您仅显示符合特定标准的数据。

数据透视图通常用于商业智能和数据分析，以对复杂数据集提供清晰简洁的可视汇总。它们是做出基于数据的决策的强大工具。

如何使用Aspose.Cells添加数据透视图

创建数据透视表

使用Aspose.Cells创建数据透视表：

1. 使用Cell对象的PutValue/setValue方法向工作表单元格添加一些数据。您还可以使用已填充数据的模板文件。这些数据将用作数据透视表的数据源。
2. 通过调用PivotTables集合的add方法（封装在Worksheet对象中）向工作表添加一个数据透视表。
3. 通过传递其索引从PivotTables集合中访问新的PivotTable对象。
4. 使用PivotTable对象中封装的任何数据透视表对象来管理表。

下面给出了一段代码示例。执行该代码将生成一个新文件：pivotTable_test.xls。

输入数据

输出的数据透视表

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getDataDir(CreatePivotTable.class);
// 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(dataDir + "pivotTable_test.xls");

基于数据透视表创建数据透视图

使用Aspose.Cells创建一个数据透视图:

1. 添加图表。
2. 将图表的数据源设置为引用电子表格中的现有数据透视表。
3. 设置其他属性。

以下是组件用于完成任务的代码。执行代码会生成一个新文件：pivotChart_test.xls。

数据透视图表工作表

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getDataDir(CreatePivotChartbasedonPivotTable.class);
// Instantiating an Workbook object
Workbook workbook = new Workbook(dataDir + "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(dataDir + "pivotChart_test.xls");

本文介绍了如何使用Aspose.Cells创建数据透视表和数据透视图表。希望能帮助您在自己的场景中使用这些功能。

Aspose.Cells 从多年的研究、设计和精心调整中受益。

我们欢迎您在Aspose.Cells 论坛上提出问题、评论和建议。我们保证会及时回复。