logo标签列表

XSSF（POI）-将“公式”列添加到数据透视表

javaexcelapache-poipivot-tablexssf
8

我正在使用POI 3.12-beta1:

<!-- Apache POI (for Excel) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.12-beta1</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.12-beta1</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>ooxml-schemas</artifactId>
    <version>1.1</version>
</dependency>

我试图创建一个计算的数据透视表列，定义如下：= 'Ended' / 'Generated' * 100。

我手动在Excel中编辑了工作表以使其正常工作，当我把*.xlsx文件反向转换为ZIP目录并浏览时，在\xl\pivotCache\pivotCacheDefinition1.xml中发现了以下代码：

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<pivotCacheDefinition xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:id="rId1" refreshOnLoad="1" refreshedBy="vyasrav" refreshedDate="42110.580247453705" createdVersion="3" refreshedVersion="3" minRefreshableVersion="3" recordCount="352">
    <cacheSource type="worksheet">
        <worksheetSource ref="A1:O353" sheet="Data"/>
    </cacheSource>
    <cacheFields count="16">
        <!-- OMITTED -->
        <cacheField name="Avg Pct Processed" numFmtId="0" formula="'Ended' / 'Generated' * 100" databaseField="0"/>
    </cacheFields>
</pivotCacheDefinition>

所以我回到我的Java程序，添加了以下代码来自动生成它，但它没有注册数据列“15”，我得到了一个IndexOutOfBounds错误。

// Add pivot (pivot table):
Sheet pivotSheet = workbook.createSheet("Pivot");
LOGGER.trace("Created sheet: '" + String.valueOf(pivotSheet) + "'.");

XSSFPivotTable pivotTable = ((XSSFSheet)pivotSheet).createPivotTable(new AreaReference(tableRange), new CellReference("A1"), dataSheet);
CTPivotTableDefinition ctPivotTableDefinition = pivotTable.getCTPivotTableDefinition();
CTPivotTableStyle ctPivotTableStyle = ctPivotTableDefinition.getPivotTableStyleInfo();
ctPivotTableStyle.setName("PivotStyleMedium4");

// Row Labels:
pivotTable.addRowLabel(...); // ...
...

// Add column 15 (this is a calculated column):
CTCacheFields ctCacheFields = pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields();
CTCacheField ctCacheField = ctCacheFields.addNewCacheField();
ctCacheField.setName("Avg Pct Processed");
ctCacheField.setFormula("'Ended' / 'Generated' * 100");

// Column Labels:
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 8, "Sum of Generated");
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 12, "Sum of Ended");
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 13, "Sum of Unended");
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 15, "Average of Processed Percent");
...

在上述加粗行发生的IndexOutOfBoundsException的StackTrace如下： 
Exception in thread "main" java.lang.IndexOutOfBoundsException
    at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTPivotFieldsImpl.setPivotFieldArray(Unknown Source)
    at org.apache.poi.xssf.usermodel.XSSFPivotTable.addDataColumn(XSSFPivotTable.java:372)
    at org.apache.poi.xssf.usermodel.XSSFPivotTable.addColumnLabel(XSSFPivotTable.java:296)
    at com...
有人知道如何使用POI来生成这一列吗？ 编辑： 我尝试了以下两种方法：
CTPivotTableDefinition ctPivotTableDefinition = pivotTable.getCTPivotTableDefinition();

并且

CTCacheField ctCacheField = ctCacheFields.insertNewCacheField(15);

无论哪种情况，当执行这一行代码时，都会出现相同的异常：
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 15, "Average of Processed Percent");

作为一条注释，我确实尝试注释掉添加新列标签的那一行，当我这样做时，如果我在Excel 2010中打开工作簿，它会在启动时出现以下错误消息： 
已删除功能：/xl/pivotTables/pivotTable1.xml 部分的数据透视表报告（数据透视表视图）
已删除记录：/xl/workbook.xml 部分的工作簿属性（工作簿）
谢谢！
1
你尝试过使用insertNewCacheField(int i)函数吗？ - cronos2546
@cronos2546：感谢您的建议，我已更改代码中的行：CTCacheField ctCacheField = ctCacheFields.addNewCacheField(); 为 CTCacheField ctCacheField = ctCacheFields.insertNewCacheField(15);，但是当脚本尝试调用 pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 15, "Average of Processed Percent"); 时仍然出现相同的 java.lang.IndexOutOfBoundsException 错误，其位置在 at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTPivotFieldsImpl.setPivotFieldArray(Unknown Source)。 - anonymous
真是太烦人了。你的平均公式为什么没有“=”符号呢？ - cronos2546
@cronos2546：我刚试了一下，但没有任何区别，我还回到了xlsx（未压缩）文件夹，在那里我使用Excel添加了公式，生成的标签看起来像\xl\pivotCache\pivotCacheDefinition1.xml：<cacheField name="Avg Pct Processed" numFmtId="0" formula="'Ended' /'Generated' * 100" databaseField="0"/>。没有=, 这似乎是Excel的一个细微差别，表示它是一个公式，但由于标记已经使用了公式标签，因此可能不需要在那里使用它，我对Excel标记并不是很了解。 - anonymous
1个回答
5
我用下面的方法解决了你的问题：

//... get or create pivotTable

//Use first column as row label
    pivotTable.addRowLabel(0);
// 1. Add Formula to cache
    addFormulaToCache(pivotTable);
// 2. Add PivotField for Formula column
    addPivotFieldForNewColumn(pivotTable);
// 3. Add all column labels before our function..
    pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1);
    //Set the third column as filter
    pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 2);
// 4. Add formula column
    addFormulaColumn(pivotTable);

下面是方法的实现：

private static void addFormulaToCache(XSSFPivotTable pivotTable) {
    CTCacheFields ctCacheFields = pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields();
    CTCacheField ctCacheField = ctCacheFields.addNewCacheField();
    ctCacheField.setName("Field1"); // Any field name
    ctCacheField.setFormula("'Ended' / 'Generated' * 100");
    ctCacheField.setDatabaseField(false);
    ctCacheField.setNumFmtId(0);
    ctCacheFields.setCount(ctCacheFields.sizeOfCacheFieldArray()); //!!! update count of fields directly
}

private static void addPivotFieldForNewColumn(XSSFPivotTable pivotTable) {
    CTPivotField pivotField = pivotTable.getCTPivotTableDefinition().getPivotFields().addNewPivotField();
    pivotField.setDataField(true);
    pivotField.setDragToCol(false);
    pivotField.setDragToPage(false);
    pivotField.setDragToRow(false);
    pivotField.setShowAll(false);
    pivotField.setDefaultSubtotal(false);
}

private static void addFormulaColumn(XSSFPivotTable pivotTable) {
    CTDataFields dataFields;
    if(pivotTable.getCTPivotTableDefinition().getDataFields() != null) {
        dataFields = pivotTable.getCTPivotTableDefinition().getDataFields();
    } else {
        // can be null if we have not added any column labels yet
        dataFields = pivotTable.getCTPivotTableDefinition().addNewDataFields();
    }
    CTDataField dataField = dataFields.addNewDataField();
    dataField.setName("Avg Pct Processed");
    // set index of cached field with formula - it is the last one!!!
    dataField.setFld(pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCount()-1);
    dataField.setBaseItem(0);
    dataField.setBaseField(0);
}
抱歉回复晚了，我度假去了，直到现在才看到。上面的代码帮助我很多，但是当我添加字段定义时仍然出现空指针异常，但是通过上述方法（作为“public”）在我命名为“XSSFPivotUtils”的类中，我能够调用：XSSFPivotUtils.addFormulaToCache(pivotTable, "Avg Pct Processed", "'Ended' / 'Generated' * 100"); XSSFPivotUtils.addPivotFieldForNewColumn(pivotTable); XSSFPivotUtils.addFormulaColumn(pivotTable, "Avg Pct Processed"); 将该列添加为可选择的数据字段添加到数据透视表中。 - anonymous
经过进一步评估，我发现我应该用新函数XSSFPivotUtils.addFormulaColumn(pivotTable, "Average of Processed Percent");替换带有空指针异常的行pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 15, "Average of Processed Percent");。非常感谢您对此的所有帮助！ - anonymous
@valerii ryzhuk，我在你的答案中添加了一行代码，以将公式格式化为百分比。通过试错，NumFmtIds 9 ##% 和 10 ##.##% 看起来适合提问者。但愿我知道这些神奇数字对应什么。 - DKATyler
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接