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

Question

我想将数据透视表值字段余额和的数字格式单元格设置为#

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

下面的代码可以创建和获取数据透视字段。但是如何设置它的数字格式呢？

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

在MS Excel中，这是按以下步骤进行的（请参见屏幕截图）：

1. 右键单击余额总和透视表值
2. 选择字段设置
3. 点击号码...
4. 设置格式化单元格

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




Answer 1

透视表字段的格式由CTDataField设置。setNumFmtId（长numFmtId）用于值和CTPivotField。setNumFmtId（长numFmtId）用于列

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