如何使用 Apache POI 设置数据透视表字段编号格式单元格

我想设置数字格式单元格数据透视表的值字段余额总和 as # ##0.

使用基于代码创建的数据透视表官方 POI 示例 CreatePivotTable

下面的代码做create and get CTPivotField pivotField。但如何设置其数字格式呢？

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

在 MS Excel 中，这是通过以下步骤完成的（参见屏幕截图）：

1. 右键单击余额总和数据透视表值
2. select 字段设置
3. click 数字...
4. set 设置单元格格式

请帮助决定、建议或任何想法。

数据透视表字段的格式设置为CTDataField.setNumFmtId(long numFmtId) 为了价值观 and CTPivotField.setNumFmtId(long numFmtId) 对于列和行.

numFmtId is id number of format code. Available format codes are represented in Format cells list - Custom category: 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 @    

预定义格式代码的完整列表MSDN NumberingFormat 类

以下是应用格式数据透视表字段的示例：

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