使用 Apache POI 将列标签插入数据透视表? [英] Insert Column Label into pivot table by using Apache POI?
本文介绍了使用 Apache POI 将列标签插入数据透视表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我使用 Apache POI 3.11 创建了一个数据透视表.像这样:
I've created a pivot table using Apache POI 3.11. like this:
FileInputStream file = new FileInputStream(new File(path+fname));
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
//area of pivot data
AreaReference a=new AreaReference("A1:J4");
CellReference b=new CellReference("N5");
XSSFPivotTable pivotTable = sheet.createPivotTable(a,b);
//insert row
pivotTable.addRowLabel(3);
pivotTable.addRowLabel(6);
//insert column
pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 5);
//export
FileOutputStream output_file =
new FileOutputStream(new File(path+"POI_XLS_Pivot_Example.xlsx"));
workbook.write(output_file);//write excel document to output stream
output_file.close(); //close the file
生成报告后,它正确显示了该行.但它不显示列标签:
After I generated the report, it shows the row correctly. But it doesn't show a column label:
我想像这样在数据透视表中显示列标签:
I want to display the column label in my pivot table like this:
(来源:pivot-table.com)
有人知道这个问题的解决方案吗?
Does anyone know the solution for this problem?
谢谢.
推荐答案
以下方法(XSSFPivotTable.addRowLabel 的稍微修改版本)添加了一个正常"的透视列标签:
The following method (a slightly modified version of XSSFPivotTable.addRowLabel) adds a "normal" pivot column label:
public static void addColLabel(XSSFPivotTable pivotTable, int columnIndex) {
AreaReference pivotArea = new AreaReference(pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition()
.getCacheSource().getWorksheetSource().getRef());
int lastRowIndex = pivotArea.getLastCell().getRow() - pivotArea.getFirstCell().getRow();
int lastColIndex = pivotArea.getLastCell().getCol() - pivotArea.getFirstCell().getCol();
if (columnIndex > lastColIndex) {
throw new IndexOutOfBoundsException();
}
CTPivotFields pivotFields = pivotTable.getCTPivotTableDefinition().getPivotFields();
CTPivotField pivotField = CTPivotField.Factory.newInstance();
CTItems items = pivotField.addNewItems();
pivotField.setAxis(STAxis.AXIS_COL);
pivotField.setShowAll(false);
for (int i = 0; i <= lastRowIndex; i++) {
items.addNewItem().setT(STItemType.DEFAULT);
}
items.setCount(items.sizeOfItemArray());
pivotFields.setPivotFieldArray(columnIndex, pivotField);
CTColFields rowFields;
if (pivotTable.getCTPivotTableDefinition().getColFields() != null) {
rowFields = pivotTable.getCTPivotTableDefinition().getColFields();
} else {
rowFields = pivotTable.getCTPivotTableDefinition().addNewColFields();
}
rowFields.addNewField().setX(columnIndex);
rowFields.setCount(rowFields.sizeOfFieldArray());
}
这篇关于使用 Apache POI 将列标签插入数据透视表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文