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版本时，这些是位置和pivotTield中的pivotTable XML元素在档案中：
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添加了类似于default的枢纽字段项（< 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屋！
