创建数据透视表和数据透视图
数据透视表是记录的互动摘要。例如,您可能有数百个工作表中列表中的发票条目。数据透视表可以按客户、产品或日期总计发票。使用Microsoft Excel,可以通过将按钮拖到新位置,快速重新排列数据透视表中的信息。
数据透视图图表是数据透视表中数据的交互式图形表示。数据透视图图表在Excel 2000中引入。使用数据透视图图表使数据更容易理解,因为数据透视表会自动创建小计和总计。
添加数据透视表和数据透视图
Aspose.Cells 提供了一组特殊的类,用于创建数据透视表。这些类用于创建和设置 PivotTable 对象,作为数据透视表对象的基本构建快:
- PivotField,数据透视表报告中的字段。
- PivotFields,数据透视表中所有 PivotField 对象的集合。
- 一个工作表上的数据透视表。
- 数据透视表是工作表上所有数据透视表对象的集合。
准备使用Aspose.Cells
- 下载并安装Aspose.Cells.Zip:
- 下载Aspose.Cells for Java.
- 在开发计算机上解压缩。 所有 Aspose 组件在安装后都是以评估模式运行。 评估模式没有时间限制,只会在生成的文档中添加水印。
- 创建一个项目
- 您可以使用Java编辑器(例如Eclipse)创建项目,或者使用NotePad创建一个简单的程序。
- 添加类路径:
在Eclipse中设置类路径:
- 从 Aspose.Cells.zip 中提取 Aspose.Cells.jar 和 dom4j_1.6.1.jar。
- 在 Eclipse 的项目中设置类路径:
- 在Eclipse中选择您的项目,然后点击菜单Project-Properties。
- 在弹出窗口的左侧选择“Java Build Path”,然后选择“Libraries”选项卡,单击“Add JARs”或“Add External JARs”以选择Aspose.Cells.jar和dom4j_1.6.1.jar,并将它们添加到构建路径中。
- 编写调用Aspose组件API的应用程序。 或者您可以在Windows的dos提示符下在运行时设置它。
javac \-classpath %classpath%;e:\Aspose.Cells.jar; ClassName .javajava \-classpath %classpath%;e:\Aspose.Cells.jar; ClassName
创建数据透视表
使用Aspose.Cells创建数据透视表:
- 使用Cell对象的PutValue/setValue方法向工作表单元格添加一些数据。您还可以使用已填充数据的模板文件。这些数据将用作数据透视表的数据源。
- 通过调用PivotTables集合的add方法(封装在Worksheet对象中)向工作表添加一个数据透视表。
- 通过传递其索引从PivotTables集合中访问新的PivotTable对象。
- 使用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创建一个数据透视图:
- 添加图表。
- 将图表的数据源设置为引用电子表格中的现有数据透视表。
- 设置其他属性。
以下是组件用于完成任务的代码。执行代码会生成一个新文件: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 论坛上提出问题、评论和建议。我们保证会及时回复。