XSSF (POI) - Добавление столбца формулы в сводную таблицу

Я использую 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");
...

StackTrace для исключения IndexOutOfBoundsException, которое встречается в выделенной жирным шрифтом строке выше:

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, при запуске я получаю следующее сообщение об ошибке:

Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part (PivotTable view)
Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)

Спасибо!


person anonymous    schedule 16.04.2015    source источник
comment
Вы пробовали insertNewCacheField (int i);   -  person cronos2546    schedule 20.04.2015
comment
@ cronos2546: Спасибо за предложение, я изменил строку в моем коде выше: CTCacheField ctCacheField = ctCacheFields.addNewCacheField(); на CTCacheField ctCacheField = ctCacheFields.insertNewCacheField(15);, но я все еще получаю то же java.lang.IndexOutOfBoundsException на at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTPivotFieldsImpl.setPivotFieldArray(Unknown Source), когда скрипт пытается вызвать: pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 15, "Average of Processed Percent");.   -  person anonymous    schedule 20.04.2015
comment
Какая боль. Есть ли причина, по которой в вашей формуле среднего значения нет знака =?   -  person cronos2546    schedule 20.04.2015
comment
@ cronos2546: Я просто попробовал это, и это не имело значения, я также вернулся в папку xlsx (разархивированную), где я использовал excel для добавления forumula, и сгенерированный тег выглядит как \xl\pivotCache\pivotCacheDefinition1.xml: <cacheField name="Avg Pct Processed" numFmtId="0" formula="'Ended' /'Generated' * 100" databaseField="0"/>. без = кажется, что это нюанс Excel, который указывает на то, что это формула, но поскольку в разметке уже используется тег формулы, он может и не понадобиться, я действительно не слишком много знаю о разметке Excel.   -  person anonymous    schedule 20.04.2015


Ответы (1)


Я решил вашу проблему следующим образом:

//... 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);
}
person valerii ryzhuk    schedule 24.04.2015
comment
Извините за поздний ответ, я взял отпуск и не видел этого до сих пор. Приведенный выше код помог мне немного продвинуться вперед, у меня все еще есть исключение с нулевым указателем при добавлении определения поля, но с помощью вышеуказанных методов (как public) в классе, который я назвал XSSFPivotUtils, я смог вызвать: XSSFPivotUtils.addFormulaToCache(pivotTable, "Avg Pct Processed", "'Ended' / 'Generated' * 100"); XSSFPivotUtils.addPivotFieldForNewColumn(pivotTable); XSSFPivotUtils.addFormulaColumn(pivotTable, "Avg Pct Processed");, чтобы добавить столбец в качестве поля данных для выбора, которое будет добавлено в сводную таблицу. - person anonymous; 27.04.2015
comment
Nvm, после дальнейшей оценки я вижу, что я должен заменить строку с исключением нулевого указателя pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 15, "Average of Processed Percent"); новой функцией XSSFPivotUtils.addFormulaColumn(pivotTable, "Average of Processed Percent");. Большое вам спасибо за вашу помощь в этом! - person anonymous; 27.04.2015
comment
@valerii ryzhuk Я добавил строчку к вашему ответу, чтобы отформатировать формулу в процентах. Методом проб и ошибок NumFmtIds 9 ##% и 10 ##. ##% кажутся подходящими для спрашивающего. Хотел бы я знать, с чем связаны эти волшебные числа. - person DKATyler; 24.09.2015