Apache POI XSSFPivotTable setDefaultSubtotal [英] Apache POI XSSFPivotTable setDefaultSubtotal
问题描述
我在使用axisRow pivotFields 的setDefaultSubtotal 方法时遇到问题.当我将 RowLabel 字段的值设置为 false 时，Excel 不喜欢它.在 Excel 中手动设置我想要的东西，然后保存会产生截然不同的 XML，这太不同了，我无法考虑在幕后修复.我无法(到目前为止)操作 XML 来让 POI 电子表格在 Excel 中干净地打开.
I am having an issue with the setDefaultSubtotal method for axisRow pivotFields. When I set the value to false for a RowLabel field Excel doesn't like it. Manually setting things up how I want them in Excel and then saving produces dramatically different XML, too different for me to contemplate fixing behind the scenes. I can't (so far) manipulate the XML to get the POI spreadsheet to open cleanly in Excel.
实际代码有点长且难以发布，但这里有一个具有相同问题的可运行代码:
The actual code is a little long and unwieldy to post, but here is a runnable piece with the same problem:
private static void sample() throws IOException{
Workbook wb = new XSSFWorkbook();
String[][] data = new String[][]{{"STATUS","PASSED","VALUE"},{"BLUE","Y","20"},{"RED","N","10"},{"BLUE","N","30"}};
XSSFSheet sheet = (XSSFSheet) wb.createSheet("data");
XSSFSheet pivot = (XSSFSheet) wb.createSheet("summary");
for(String[] dataRow : data){
XSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows());
for(String dataCell : dataRow){
XSSFCell cell = row.createCell(row.getPhysicalNumberOfCells());
cell.setCellValue(dataCell);
}
}
XSSFTable table = sheet.createTable();
CTTable cttable = table.getCTTable();
table.setDisplayName("table");
cttable.setRef("A1:C4");
cttable.setId(1);
CTTableColumns columns = cttable.addNewTableColumns();
columns.setCount(3);
int i = 1;
for (String colName : data[0]){
CTTableColumn column = columns.addNewTableColumn();
column.setId(++i);
column.setName(colName);
}
XSSFPivotTable pivotTable = pivot.createPivotTable(new AreaReference("A1:C4", SpreadsheetVersion.EXCEL2007), new CellReference("A4"), sheet);
pivotTable.addRowLabel(0);
CTPivotField fld = pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldList().get(0);
fld.setOutline(false);
//fld.setDefaultSubtotal(false); // uncomment and Excel has problems
pivotTable.addRowLabel(1);
fld = pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldList().get(1);
fld.setOutline(false);
//fld.setDefaultSubtotal(false); // uncomment and Excel has problems
pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 2, "test");
FileOutputStream fileOut = new FileOutputStream("c:/temp/pivotsample.xlsx");
wb.write(fileOut);
wb.close();
}
当我生成 POI 版本时，这些是存档内的 pivotTable XML 的 location 和 pivotFields 元素:
When I generate the POI version, these are the location and pivotFields elements of the pivotTable XML inside the archive:
<location firstDataCol="1" firstDataRow="1" firstHeaderRow="1" ref="F10:G11" colPageCount="1"/>
<pivotFields count="5">
<pivotField axis="axisRow" showAll="false" compact="true" outline="false" defaultSubtotal="false">
<items count="4">
<item t="default"/>
<item t="default"/>
<item t="default"/>
<item t="default"/>
</items>
</pivotField>
<pivotField dataField="true" showAll="false"/>
<pivotField dataField="true" showAll="false"/>
<pivotField axis="axisPage" showAll="false">
<items count="4">
<item t="default"/>
<item t="default"/>
<item t="default"/>
<item t="default"/>
</items>
</pivotField>
<pivotField axis="axisRow" showAll="false" compact="true" outline="false" defaultSubtotal="false">
<items count="4">
<item t="default"/>
<item t="default"/>
<item t="default"/>
<item t="default"/>
</items>
</pivotField>
</pivotFields>
当我在 Excel 中打开并允许它恢复工作表时，我然后在 Excel 中进行更改以执行我想要的操作，即没有表格形式的小计的两个行标签，这就是 Excel 保存的内容:
When I open in Excel and allow it to recover the sheet, I then make the changes in Excel to do what I'd like, namely two row labels without subtotals in tabular form, this is what Excel saves:
<location ref="F10:I15" firstHeaderRow="1" firstDataRow="2" firstDataCol="2" rowPageCount="1" colPageCount="1"/>
<pivotFields count="5">
<pivotField axis="axisRow" outline="0" showAll="0" defaultSubtotal="0">
<items count="3">
<item x="0"/>
<item x="1"/>
<item x="2"/>
</items>
</pivotField>
<pivotField dataField="1" showAll="0"/>
<pivotField dataField="1" showAll="0"/>
<pivotField axis="axisPage" showAll="0">
<items count="3">
<item x="0"/>
<item x="1"/>
<item t="default"/>
</items>
</pivotField>
<pivotField axis="axisRow" outline="0" showAll="0" defaultSubtotal="0">
<items count="2">
<item x="0"/>
<item x="1"/>
</items>
</pivotField>
</pivotFields>
我已经尝试了几乎所有的方法，并且我了解 Excel 格式，但这取决于pivotCacheRecords，所以我最终不得不编写代码来填充它.如果有人能明白为什么这段代码会失败，我会很感激的.
I have tried just about everything, and I understand the Excel format, but it depends on pivotCacheRecords, so I'd end up having to write code to populate that. If there's anyone who can maybe see why this code fails, I'd appreciate a pointer.
推荐答案
另见 如何在 Apache POI 数据透视表报表过滤器中设置默认值.
到目前为止，apache poi 添加了与数据范围中存在的行一样多的默认"类型 (<item t="default"/>) 数据透视字段项目，如果数据透视表用作轴字段的字段.这是因为他们不想查看数据，因此他们假设不同的值与数据中的行数一样多.
Until now apache poi adds as much pivot field items of type "default" (<item t="default"/>) as rows are present in the data range, if the pivot fields where used as axis fields. This is because they don't want to have a look at the data, and so they are assuming as much different values as rows are in the data.
这很好，因为 Excel 将在打开时重建其数据透视缓存.但是如果我们想改变默认值，那么这不好.然后我们必须知道有哪些项目.
This is fine because Excel will rebuild its pivot cache while opening. But if we want changing defaults, then this is not fine. Then we must know what items there are.
所以我们至少需要与数据中不同的项目一样多的项目，作为编号项目:<item x="0"/><item x="1"/>
So we need at least as much items, as are different ones in the data, as numbered items: <item x="0"/><item x="1"/>
我们需要构建一个缓存定义，其中包含这些项目的共享元素.
And we need to build a cache definition which has shared elements for those items.
示例:
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.ss.*;
import java.io.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;
class PivotTableTest6 {
public static void main(String[] args) throws IOException{
Workbook wb = new XSSFWorkbook();
String[][] data = new String[][]{{"STATUS","PASSED","VALUE"},{"BLUE","Y","20"},{"RED","N","10"},{"BLUE","N","30"}};
XSSFSheet sheet = (XSSFSheet) wb.createSheet("data");
XSSFSheet pivot = (XSSFSheet) wb.createSheet("summary");
for(String[] dataRow : data){
XSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows());
for(String dataCell : dataRow){
XSSFCell cell = row.createCell(row.getPhysicalNumberOfCells());
cell.setCellValue(dataCell);
}
}
XSSFTable table = sheet.createTable();
CTTable cttable = table.getCTTable();
table.setDisplayName("table");
cttable.setRef("A1:C4");
cttable.setId(1);
CTTableColumns columns = cttable.addNewTableColumns();
columns.setCount(3);
int i = 1;
for (String colName : data[0]){
CTTableColumn column = columns.addNewTableColumn();
column.setId(++i);
column.setName(colName);
}
XSSFPivotTable pivotTable = pivot.createPivotTable(new AreaReference("A1:C4", SpreadsheetVersion.EXCEL2007), new CellReference("A4"), sheet);
pivotTable.addRowLabel(0);
CTPivotField fld = pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldList().get(0);
fld.setOutline(false);
/*
Apache poi adds 4 pivot field items of type "default" (<item t="default"/>) here.
This is because there are 4 rows (A1:C4) and, because they don't have a look at the data,
they are assuming max 4 different values. This is fine because Excel will rebuild its pivot cache while opening.
But if we want changing defaults, then this is not fine. Then we must know what items there are.
So we need at least as much items, as are different ones in the data, as numbered items: <item x="0"/><item x="1"/>
And we must build a cache definition which has shared elements for those items.
*/
for (i = 0; i < 2; i++) {
//take the first 2 items as numbered items: <item x="0"/><item x="1"/>
fld.getItems().getItemArray(i).unsetT();
fld.getItems().getItemArray(i).setX((long)i);
}
for (i = 3; i > 1; i--) {
//remove further items
fld.getItems().removeItem(i);
}
//set new items count
fld.getItems().setCount(2);
//build a cache definition which has shared elements for those items
//<sharedItems><s v="Y"/><s v="N"/></sharedItems>
pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldList().get(0).getSharedItems().addNewS().setV("Y");
pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldList().get(0).getSharedItems().addNewS().setV("N");
fld.setDefaultSubtotal(false);
pivotTable.addRowLabel(1);
fld = pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldList().get(1);
fld.setOutline(false);
pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 2, "test");
FileOutputStream fileOut = new FileOutputStream("pivotsample6.xlsx");
wb.write(fileOut);
wb.close();
}
}
这篇关于Apache POI XSSFPivotTable setDefaultSubtotal的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
