Dmitry Stolbov Dmitry Stolbov - 27 days ago 16
Java Question

How to set PivotTable Field Number Format Cell with Apache POI

I'd like to set number format cell of pivot table Value field Sum of Balance as

# ##0
.

Pivot table created with code based on Official POI Sample CreatePivotTable

Code below do
create
and
get
CTPivotField pivotField
. But how to set its number format?



pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 2);
CTPivotField pivotField = pivotTable
.getCTPivotTableDefinition()
.getPivotFields()
.getPivotFieldArray(2);


In MS Excel this is doing by next steps (see screenshot):


  1. right click on Sum of Balance pivot table Value

  2. select Field Settings

  3. click Number...

  4. set Format Cells



Help please with decide, advice or any idea.

Setting number format cell of pivot table with Microsoft Excel

Answer

Format of pivot table fields is setting by CTDataField.setNumFmtId(long numFmtId) for values and CTPivotField.setNumFmtId(long numFmtId) for columns & rows.

numFmtId is id number of format code. Available format codes are represented in Format cells list - Custom category: enter image description here Predefined format codes, thanks to Ji Zhou - MSFT, is here:

1 0    
2 0.00    
3 #,##0    
4 #,##0.00    
5 $#,##0_);($#,##0)    
6 $#,##0_);[Red]($#,##0)    
7 $#,##0.00_);($#,##0.00)    
8 $#,##0.00_);[Red]($#,##0.00)    
9 0%    
10 0.00%    
11 0.00E+00    
12 # ?/?    
13 # ??/??    
14 m/d/yyyy    
15 d-mmm-yy    
16 d-mmm    
17 mmm-yy    
18 h:mm AM/PM    
19 h:mm:ss AM/PM    
20 h:mm    
21 h:mm:ss    
22 m/d/yyyy h:mm    
37 #,##0_);(#,##0)    
38 #,##0_);[Red](#,##0)    
39 #,##0.00_);(#,##0.00)    
40 #,##0.00_);[Red](#,##0.00)    
45 mm:ss    
46 [h]:mm:ss    
47 mm:ss.0    
48 ##0.0E+0    
49 @    

Full list of predefined format codes in MSDN NumberingFormat Class

Here is an example of applying format pivot table fields:

package ru.inkontext.poi;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.DataConsolidateFunction;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFPivotTable;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataFields;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Optional;

public class CreatePivotTableSimple {

    private static void setFormatPivotField(XSSFPivotTable pivotTable,
                                                long fieldIndex,
                                                Integer numFmtId) {
        Optional.ofNullable(pivotTable
                .getCTPivotTableDefinition()
                .getPivotFields())
                .map(pivotFields -> pivotFields
                        .getPivotFieldArray((int) fieldIndex))
                .ifPresent(pivotField -> pivotField
                        .setNumFmtId(numFmtId));
    }

    private static void setFormatDataField(XSSFPivotTable pivotTable,
                                               long fieldIndex,
                                               long numFmtId) {
        Optional.ofNullable(pivotTable
                .getCTPivotTableDefinition()
                .getDataFields())
                .map(CTDataFields::getDataFieldList)
                .map(List::stream)
                .ifPresent(stream -> stream
                        .filter(dataField -> dataField.getFld() == fieldIndex)
                        .findFirst()
                        .ifPresent(dataField -> dataField.setNumFmtId(numFmtId)));
    }    

    public static void main(String[] args) throws IOException, InvalidFormatException {

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet();

        //Create some data to build the pivot table on
        setCellData(sheet);

        XSSFPivotTable pivotTable = sheet.createPivotTable(
                new AreaReference("A1:C6", SpreadsheetVersion.EXCEL2007),
                new CellReference("E3"));

        pivotTable.addRowLabel(1); // set second column as 1-th level of rows
        setFormatPivotField(pivotTable, 1, 9); //set format of row field numFmtId=9 0%
        pivotTable.addRowLabel(0); // set first column as 2-th level of rows
        pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 2); // Sum up the second column
        setFormatDataField(pivotTable, 2, 3); //set format of value field numFmtId=3 # ##0

        FileOutputStream fileOut = new FileOutputStream("stackoverflow-pivottable.xlsx");
        wb.write(fileOut);
        fileOut.close();
        wb.close();
    }

    private static void setCellData(XSSFSheet sheet) {

        String[] names = {"Jane", "Tarzan", "Terk", "Kate", "Dmitry"};
        Double[] percents = {0.25, 0.5, 0.75, 0.25, 0.5};
        Integer[] balances = {107634, 554234, 10234, 22350, 15234};

        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue("Name");
        row.createCell(1).setCellValue("Percents");
        row.createCell(2).setCellValue("Balance");

        for (int i = 0; i < names.length; i++) {
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(names[i]);
            row.createCell(1).setCellValue(percents[i]);
            row.createCell(2).setCellValue(balances[i]);
        }
    }
}

https://github.com/stolbovd/PoiSamples