我在通过 Apache POI 添加计算字段时遇到问题

Question

我正在尝试通过 Apache POI 添加计算字段，当只有一个非计算字段而其余其他字段是计算字段时，我遇到问题 我在打开 excel 工作表时遇到错误，如下所示

错误图像

但是，当有多个非计算字段时，Excel 工作表不会抛出任何错误，并且计算字段显示正常。

添加一个或多个列标签时没有错误。有人可以帮我解决这个问题吗？

实际产量

预期输出只有一个非计算列和一个计算列，如下所示。

预期产出

Answer 1

数据透视表定义中有一个元素，如果有多个数据字段作为列字段，colFields则至少需要一个元素。field但apache poi如果仅设置一个列标签，则不会添加该字段，因为如果仅存在一个列字段，则不需要该字段。但由于您低级别添加了一个数据字段作为列字段，因此它缺少该 colFields字段。这就是为什么在打开文件时出错的原因Excel。如果设置了多个列标签，则apache poi添加该colFields字段。这就是它起作用的原因。因此，在低级别创建数据字段后，将以下内容添加到您的代码中：...&nbsp; &nbsp;// at least one field in colFields is needed if there are multiple data fields&nbsp; &nbsp;CTColFields colFields;&nbsp; &nbsp;if(pivotTable.getCTPivotTableDefinition().getColFields() != null) {&nbsp; &nbsp; colFields = pivotTable.getCTPivotTableDefinition().getColFields();&nbsp; &nbsp;} else {&nbsp; &nbsp; colFields = pivotTable.getCTPivotTableDefinition().addNewColFields();&nbsp; &nbsp;}&nbsp; &nbsp;CTField field;&nbsp; &nbsp;if (colFields.getFieldList().size() == 0) {&nbsp; &nbsp; field = colFields.addNewField();&nbsp; &nbsp; field.setX(-2);&nbsp; &nbsp;}...这将添加一个新colFields元素（如果尚不存在）。它会field在那里添加该元素（如果尚未存在）。再次完整的例子：import java.io.FileOutputStream;import org.apache.poi.ss.SpreadsheetVersion;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.*;import org.apache.poi.xssf.usermodel.*;import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;class CreatePivotTableCalculatedFields {&nbsp;public static void main(String[] args) throws Exception {&nbsp; createPivotTable();&nbsp;}&nbsp;private static void createPivotTable() throws Exception {&nbsp; try (XSSFWorkbook wb = new XSSFWorkbook();&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;FileOutputStream fileOut = new FileOutputStream("Output.xlsx")) {&nbsp; &nbsp;XSSFSheet dataSheet = wb.createSheet("1econtent");&nbsp; &nbsp;XSSFSheet pivotSheet = wb.createSheet("1e");&nbsp; &nbsp;setCellData(dataSheet, wb);&nbsp; &nbsp;AreaReference source = new AreaReference("A1:F5", SpreadsheetVersion.EXCEL2007);&nbsp; &nbsp;CellReference position = new CellReference(0,0);&nbsp; &nbsp;XSSFPivotTable pivotTable = pivotSheet.createPivotTable(source, position,wb.getSheet("1econtent"));&nbsp; &nbsp;pivotTable.addRowLabel(2);&nbsp; &nbsp;pivotTable.addRowLabel(0);&nbsp; &nbsp;pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3);&nbsp; &nbsp;pivotSheet.setDisplayGridlines(false);&nbsp; &nbsp;wb.setSelectedTab(1);&nbsp; &nbsp;wb.setActiveSheet(1);&nbsp; &nbsp;// customize pivot table&nbsp; &nbsp;CTCacheFields ctCacheFields = pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields();&nbsp; &nbsp;CTCacheField ctCacheField = ctCacheFields.addNewCacheField();&nbsp; &nbsp;ctCacheField.setName("Field");&nbsp; &nbsp;ctCacheField.setFormula("'Actuals' / 'Estimates'");&nbsp; &nbsp;ctCacheField.setDatabaseField(false);&nbsp; &nbsp;ctCacheFields.setCount(ctCacheFields.sizeOfCacheFieldArray());&nbsp; &nbsp;CTPivotField pivotField = pivotTable.getCTPivotTableDefinition().getPivotFields().addNewPivotField();&nbsp; &nbsp;pivotField.setDataField(true);&nbsp; &nbsp;pivotField.setDragToCol(false);&nbsp; &nbsp;pivotField.setDragToPage(false);&nbsp; &nbsp;pivotField.setDragToRow(false);&nbsp; &nbsp;pivotField.setShowAll(false);&nbsp; &nbsp;pivotField.setDefaultSubtotal(false);&nbsp; &nbsp;CTDataFields dataFields;&nbsp; &nbsp;if(pivotTable.getCTPivotTableDefinition().getDataFields() != null) {&nbsp; &nbsp; dataFields = pivotTable.getCTPivotTableDefinition().getDataFields();&nbsp; &nbsp;} else {&nbsp; &nbsp; dataFields = pivotTable.getCTPivotTableDefinition().addNewDataFields();&nbsp; &nbsp;}&nbsp; &nbsp;CTDataField dataField = dataFields.addNewDataField();&nbsp; &nbsp;dataField.setName("Calculated Field");&nbsp; &nbsp;dataField.setFld(pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCount()-1);&nbsp; &nbsp;dataField.setBaseItem(0);&nbsp; &nbsp;dataField.setBaseField(0);&nbsp; &nbsp;DataFormat dataformat =&nbsp; pivotTable.getParentSheet().getWorkbook().createDataFormat();&nbsp; &nbsp;short numFmtId = dataformat.getFormat("0.0%");&nbsp; &nbsp;dataField.setNumFmtId(numFmtId);&nbsp; &nbsp;// at least one field in colFields is needed if there are multiple data fields&nbsp; &nbsp;CTColFields colFields;&nbsp; &nbsp;if(pivotTable.getCTPivotTableDefinition().getColFields() != null) {&nbsp; &nbsp; colFields = pivotTable.getCTPivotTableDefinition().getColFields();&nbsp; &nbsp;} else {&nbsp; &nbsp; colFields = pivotTable.getCTPivotTableDefinition().addNewColFields();&nbsp; &nbsp;}&nbsp; &nbsp;CTField field;&nbsp; &nbsp;if (colFields.getFieldList().size() == 0) {&nbsp; &nbsp; field = colFields.addNewField();&nbsp; &nbsp; field.setX(-2);&nbsp; &nbsp;}&nbsp; &nbsp;wb.write(fileOut);&nbsp; }&nbsp;}&nbsp;public static void setCellData(XSSFSheet sheet, XSSFWorkbook wb){&nbsp; Object[][] data = new Object[][] {&nbsp; &nbsp;new Object[] {"Names", "Age", "Dept", "Salary", "Actuals", "Estimates"},&nbsp; &nbsp;new Object[] {"Adam", 22d, "Sales", 10d, 12d, 60d},&nbsp; &nbsp;new Object[] {"Bran", 24d, "Finance", 20d, 24d, 60d},&nbsp; &nbsp;new Object[] {"Jane", 23d, "IT", 30d, 30d, 60d},&nbsp; &nbsp;new Object[] {"Dave", 30d, "Sales", 50d, 6d, 60d}&nbsp; };&nbsp; XSSFRow row;&nbsp; XSSFCell cell;&nbsp; for (int r = 0; r < data.length; r++) {&nbsp; &nbsp;row = sheet.createRow(r);&nbsp; &nbsp;for (int c = 0 ; c < data[r].length; c++) {&nbsp; &nbsp; cell = row.createCell(c);&nbsp; &nbsp; if (data[r][c] instanceof String) {&nbsp; &nbsp; &nbsp;cell.setCellValue((String)data[r][c]);&nbsp; &nbsp; } else if (data[r][c] instanceof Double) {&nbsp; &nbsp; &nbsp;cell.setCellValue((Double)data[r][c]);&nbsp; &nbsp; }&nbsp; &nbsp;}&nbsp; }&nbsp;}&nbsp;}