java - java:如何使用 apache poi 创建一个数据透视表?
问题描述
我正在尝试使用 apache poi 创建一个 Excel Pivot,但我真的不明白如何创建我想要的格式。我使用此代码创建,但我得到空值:
XSSFPivotTable pivotTable = sheet.createPivotTable(aref, pos);
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 2, colNames[2]);
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3, colNames[3]);
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 0, colNames[0]);
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1, colNames[1]);
我的数据如下所示:
我希望它看起来像这样:
我该如何做到这一点?
解决方案
Apache poi
仅对创建数据透视表提供基本支持。它仅创建默认数据透视表,但无法创建特殊设置而不回退到底层ooxml-schemas
类。
对于您的数据源,以下代码会创建apache poi
默认支持的内容:
import java.io.FileOutputStream;
import java.io.FileInputStream;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.*;
class CreatePivotTableDefault {
public static void main(String[] args) throws Exception {
try (XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("ExcelSource.xlsx"));
FileOutputStream fileout = new FileOutputStream("ExcelResult.xlsx") ) {
XSSFSheet dataSheet = workbook.getSheetAt(0);
XSSFSheet pivotSheet = workbook.createSheet("Pivot");
AreaReference areaReference = new AreaReference("A1:D5", SpreadsheetVersion.EXCEL2007);
XSSFPivotTable pivotTable = pivotSheet.createPivotTable(areaReference, new CellReference("A1"), dataSheet);
pivotTable.addRowLabel(2);
pivotTable.addRowLabel(3);
pivotTable.addRowLabel(0);
pivotTable.addRowLabel(1);
workbook.write(fileout);
}
}
}
这将创建一个大纲格式的数据透视表,这是默认设置。
如果您想创建一个不是大纲格式并且不显示每个字段的小计的数据透视表,我们需要使用基础ooxml-schemas
类。我们需要创建正确的数据透视表字段项。我们需要构建一个缓存定义,其中包含这些项目的共享元素。另请参阅Apache POI XSSFPivotTable setDefaultSubtotal。
以下代码应从您的数据源创建您想要的数据透视表:
import java.io.FileOutputStream;
import java.io.FileInputStream;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotField;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.*;
class CreatePivotTable {
static void addRowLabel(XSSFPivotTable pivotTable, XSSFSheet dataSheet, AreaReference areaReference, int column) {
DataFormatter formatter = new DataFormatter(java.util.Locale.US);
//create row label - apache poi creates as much fields for each as rows are in the pivot table data range
pivotTable.addRowLabel(column);
//determine unique labels in column
java.util.TreeSet<String> uniqueItems = new java.util.TreeSet<String>(String.CASE_INSENSITIVE_ORDER);
for (int r = areaReference.getFirstCell().getRow()+1; r < areaReference.getLastCell().getRow()+1; r++) {
uniqueItems.add(formatter.formatCellValue(dataSheet.getRow(r).getCell(column)));
}
//System.out.println(uniqueItems);
//build pivot table and cache
CTPivotField ctPivotField = pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(column);
int i = 0;
for (String item : uniqueItems) {
//take the items as numbered items: <item x="0"/><item x="1"/>
ctPivotField.getItems().getItemArray(i).unsetT();
ctPivotField.getItems().getItemArray(i).setX((long)i);
//build a cache definition which has shared elements for those items
pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(column)
.getSharedItems().addNewS().setV(item);
i++;
}
//set pivot field settings
ctPivotField.setOutline(false); // no outline format
ctPivotField.setDefaultSubtotal(false); // no subtotals for this field
//remove further items
if (ctPivotField.getDefaultSubtotal()) i++; //let one default item be if there shall be subtotals
for (int k = ctPivotField.getItems().getItemList().size()-1; k >= i; k--) {
ctPivotField.getItems().removeItem(k);
}
ctPivotField.getItems().setCount(i);
}
public static void main(String[] args) throws Exception {
try (XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("ExcelSource.xlsx"));
FileOutputStream fileout = new FileOutputStream("ExcelResult.xlsx") ) {
XSSFSheet dataSheet = workbook.getSheetAt(0);
XSSFSheet pivotSheet = workbook.createSheet("Pivot");
AreaReference areaReference = new AreaReference("A1:D5", SpreadsheetVersion.EXCEL2007);
XSSFPivotTable pivotTable = pivotSheet.createPivotTable(areaReference, new CellReference("A1"), dataSheet);
addRowLabel(pivotTable, dataSheet, areaReference, 2);
addRowLabel(pivotTable, dataSheet, areaReference, 3);
addRowLabel(pivotTable, dataSheet, areaReference, 0);
addRowLabel(pivotTable, dataSheet, areaReference, 1);
workbook.write(fileout);
}
}
}
如apache poi FAQ-N10025所述,此代码使用当前代码进行测试apache poi 4.1.2
,并且需要所有模式的完整 jar ooxml-schemas-1.4.jar
(旧版本的较低版本)。
推荐阅读
- ruby-on-rails - Rails API 模式 ApplicationController 忽略 Rails.application.config.action_controller.allow_forgery_protection
- javascript - 如果我单击菜单项,防止下拉菜单关闭
- linux - linux:无法使用 cpupower 设置 cpu 频率调节器(ACPI 故障)
- java - AndroidStudio 项目编译错误:无法下载 gradle 4.1.1 - 无法找到请求目标的有效认证路径
- python - 此代码中使用了哪些 OOP 技术?是聚合、组合、泛化、关联还是依赖?
- fiddler - 如何在使用 Fiddler(脚本)发送请求之前编辑正文
- groovy - 在 Jenkinsfile 中调用可变参数函数意外失败
- python - 使用过滤器时关键字不能是表达式错误
- typescript - 打字稿中装饰器的别名
- c - 尝试删除树中的节点时出现问题