使用Apache POI生成excel pivot table

大耳独孤吉利
1,530 阅读6分钟

1 什么是数据透视表（Pivot Table）

数据透视表（Pivot Table）是Excel中用于数据汇总、优化数据显示和数据处理的强大工具。通过数据透视表可以快速分类汇总、比较大量的数据，并且可以根据用户的业务需求，快速变换统计分析维度来查看统计结果。如果你要对海量的数据进行多条件统计，从而快速提取最有价值的信息，那么使用数据透视表将是最佳选择之一。

2 简单介绍怎么使用excel创建数据透视表

2.1. 首先，选中原始数据区域，点击【插入】-【数据透视表】。

image.png

2.2. 如果刚才已经选中了数据区域，在【数据透视表向导】中最上面的选择区域框可以看到选中的单元格区域，如果刚才没有选中，也可以在此选取。

image.png

2.3. 数据透视表提供了在新工作表创建和在当前工作表创建的选择，如果数据透视表较大，内容很多，建议在新工作表中生成透视表（此文档中选择现有工作表）。

image.png

2.4. 根据实际情况选择完上述两个设置后，点击【确定】按钮；在指定区域就出现了一块空白的透视表区域，右边是数据透视表字段列表，可以拖动和设置。

image.png

2.5. 数据透视表列表字段处显示的字段名称是原始数据区域的标题，可以拖动到下面的四个框中。

image.png

2.6. 我们把【胜负】字段拖到行标签位置，【得分】拖动到【数值】位置。这时，我们可以看到左方的数据透视表已经按照胜负将分数进行了汇总。

image.png

此时我们就已经创建好了一个简易的数据透视表了。此处我们只介绍到这，更多复杂功能请自行学习。接下来进入主题，如何使用Apache POI生成Excel Pivot Table

3 使用Apache POI生成Excel Pivot Table

本文将某球员的比赛数据作为数据集进行讲解：

3.1. 首先读取数据，创建workbook和sheet

String path = "C:\\Users\\admin\\Desktop\\james_harden.xlsx";
FileInputStream fileInputStream = new FileInputStream(path);
XSSFWorkbook wb = new XSSFWorkbook(fileInputStream);
XSSFSheet sheet = wb.getSheetAt(0);

3.2. 创建数据透视表

现在让我们创建数据透视表。我们希望数据透视表中包含所有数据。因此，我们使用以下范围来创建单元格引用。

 // 获取数据sheet的总行数
int rowNum = sheet.getLastRowNum();
// 数据透视表生产的起点单元格位置
CellReference ptStartCell = new CellReference("M1");
//指定原始数据范围
AreaReference areaR= new AreaReference("A1:J" + rowNum, SpreadsheetVersion.EXCEL2007);
//从sheet的选定数据范围内数据生成数据透视表
XSSFPivotTable pivotTable = sheet.createPivotTable(areaR, ptStartCell);

3.3. 设置行标签及数据值

在这个示例中，我们按照 主客场 和 胜负 对数据进行分组。它们分别是列2和列1（从0开始计数）。对于汇总，我们选择对这些列的得分，篮板求平均值，分别是列9和列7。

//添加行标签
pivotTable.addRowLabel(2);
pivotTable.addRowLabel(1);
//添加列值 设置格式为保留两位小数
pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 9,"得分", "0.00");
pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 7,"篮板", "0.00");

3.4. 最后保存excel,关闭流

FileOutputStream fileOut = new FileOutputStream("PivotTableTest4.xlsx");
wb.write(fileOut);
fileOut.close();
wb.close();

运行程序，此时我们就生成了一个简易的数据透视表

image.png

4 进阶

4.1. 添加过滤器

如果我们想根据不同的对手来筛选该球员的数据，该如何操作？代码如下

//添加过滤器 因为【对手】在列0，所以filterIndex = 0 
int filterIndex = 0;
pivotTable.addReportFilter(filterIndex);

效果如下，顶部出现了一个筛选对手的过滤器

image.png

那么如果我想给这个过滤器设置个默认值该如何操作？ 到目前为止，使用apache poi创建复杂数据透视表还不太成熟，很难找到相关中文文档。那么如果我们需要设置某种复杂的样式，可以尝试以下方式解决：

Tips：
怎么设置各种复杂的样式，查各种api？
新建一个excel，将需要的样式，在一个Excel中实现想要的功能，然后重命名为zip解压，打开：xl\charts\chart1.xml
对于有数据透视表的excel,解压后会有pivotCache,pivotTables文件夹，里面包含了数据透视表的相关信息

将我们上面得到的excel重命名为.zip,然后解压，打开pivotTables/pivotTable1.xml，我们可以看到很多item t="default"。 目前，如果数据透视字段用作轴字段(也就是上面讲到到筛选、列、行、值四个区域添加的字段),apache poi 添加的数据透视字段item类型为“default”（item t="default"）数量与数据区域中存在的行一样多（见下图）。

image.png

这是因为Excel会在在打开时重建其数据透视缓存。但是如果我们想要设置默认值，那就不行了。要设置默认值我们就必须知道有哪些值可以被选择。 所以我们至少需要与我们想要默认选中的值一样多的item，作为编号项目：...... 我们需要构建一个缓存定义，其中包含这些项目的共享元素。 假如我们缓存“勇士”和“湖人”，设置“勇士”为默认值，代码如下：

List<String> values = Arrays.asList("勇士", "湖人");
for (int i = 0; i < values.size(); i++) {            
pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(filterIndex).getItems().getItemArray(i).unsetT();        
pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(filterIndex).getItems().getItemArray(i).setX((long) i);
//build a cache definition which has shared elements for those items
//<sharedItems><s v="湖人"/><s v="勇士"/></sharedItems>            
pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(0).getSharedItems().addNewS().setV(values.get(i));
}
//设置默认值 setItem(0)即设置默认值为“勇士”
pivotTable.getCTPivotTableDefinition().getPageFields().getPageFieldArray(filterIndex).setItem(0);

效果如下图，默认选中了“勇士”

image.png

重新将新生成的excel重命名为zip,解压缩，打开pivotTables/pivotTable1.xml,此时我们可以看到xml中前两个item已经不是default了

image.png

此时对比pivotCache/pivotCacheDefinition1.xml中可以看到我们给【对手】字段添加了两个sharedItems

image.png

4.2. 设置报表布局

数据透视表共有5种报表布局，下面我们只讲解前三种布局

image.png

  1. 以压缩形式显示（默认） 以压缩形式显示则选中多个行标签会在一列展示，如我们刚生成的数据透视表，选中的两个行标签在同一列展示

image.png

  1. 以大纲形式显示 以大纲形式显示则选中多个行标签分多列展示，但是多格不在同一行展示，如下图

image.png

  1. 以表格形式显示 以表格形式显示则选中多个行标签多列展示，且开始数据在同一行，像正常表格一样，如下图

image.png

代码如下：

/**
   * 数据透视表共三种布局方式：
   * 压缩（compact）:选中多个行标签会在一列展示
   * 大纲（outline）:选中多个行标签分多列展示，但是不在一行
   * 表格：选中多个行标签多列展示，且开始数据在同一行
   * 因为XSSFPivotTable不提供直接设置为表格模式，所以我们取了个巧。数据透视表总共只有3中布局模式，大纲(outline)，压缩(compact)和表格，将另外两种设置成false就只有表格模式了
   *
*/
CTPivotFields pivotFields = pivotTable.getCTPivotTableDefinition().getPivotFields();
for (CTPivotField ctPivotField : pivotFields.getPivotFieldList()) {
  ctPivotField.setOutline(false);
  ctPivotField.setCompact(false);
}
pivotTable.getCTPivotTableDefinition().setMergeItem(true);//合并相同的单元格

4.3. 其它

  1. 取消总计
pivotTable.getCTPivotTableDefinition().setRowGrandTotals(false);//行总计
pivotTable.getCTPivotTableDefinition().setColGrandTotals(false);//列总计

5. 待研究问题

当设置布局为表格形式时，会默认添加分类汇总，目前不知道如何取消

image.png

avatar
文章
阅读
粉丝