dimanche 19 avril 2015

XSSF (POI) - Adding "formula" column to pivot table

I am using 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>


I am trying to create a calculated pivot table column which is defined as: = 'Ended' / 'Generated' * 100.


I went ahead and manually edited the sheet in Excel to get this to work, and when I reversed the *.xlsx file into a ZIP directory and looked through it, I found the following code in \xl\pivotCache\pivotCacheDefinition1.xml:



<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<pivotCacheDefinition xmlns="http://ift.tt/K9sZsw" xmlns:r="http://ift.tt/1bA4cfb" 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>


So I went back to my java program and added the following code to generate it automatically, but it isn't registering data column "15" and I am getting an IndexOutOfBounds error.



<pre><code>// 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(...); // ...
...

<b><i><span style='background:FFFF00;'>// 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");</span></i></b>

// 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");
<b><i>pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 15, "Average of Processed Percent");</i></b>
...
</code></pre>


The StackTrace of the IndexOutOfBoundsException which occurs on the bolded line above is:



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...

Does anyone know how can I use POI to generate this column?


Thanks!


Aucun commentaire:

Enregistrer un commentaire