0

I am trying to add calculated field through Apache POI and I am facing issue when there is only one non-calculated field and rest others are calculated fields I am getting an error while opening excel sheet as below

Error Image

But when there are more than one non-calculated fields excel sheet doesn't throw any error and calculated field displayed fine.

Here is my code:

public static void main(String[] args) throws FileNotFoundException, IOException, InvalidFormatException {
        createPivotTable();
    }

    private static void createPivotTable() throws IOException, FileNotFoundException {
        try (XSSFWorkbook wb = new XSSFWorkbook()) {
            XSSFSheet sheet = wb.createSheet("1econtent");
            XSSFSheet sheet1 = wb.createSheet("1e");

            sheet1.setDisplayGridlines(false);
            setCellData(sheet,wb);

            AreaReference source = new AreaReference("A1:F5", SpreadsheetVersion.EXCEL2007);
            CellReference position = new CellReference(0,0);

            XSSFPivotTable pivotTable = sheet1.createPivotTable(source, position,wb.getSheet("1econtent"));
            pivotTable.addRowLabel(2);
            pivotTable.addRowLabel(0);


            pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3);


            CTCacheFields ctCacheFields = pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields();
            CTCacheField ctCacheField = ctCacheFields.addNewCacheField();
            ctCacheField.setName("Field");
            ctCacheField.setFormula("'Actuals' / 'Estimates'");
            ctCacheField.setDatabaseField(false);
            ctCacheFields.setCount(ctCacheFields.sizeOfCacheFieldArray());

            CTPivotField pivotField = pivotTable.getCTPivotTableDefinition().getPivotFields().addNewPivotField();
            pivotField.setDataField(true);
            pivotField.setDragToCol(false);
            pivotField.setDragToPage(false);
            pivotField.setDragToRow(false);
            pivotField.setShowAll(false);
            pivotField.setDefaultSubtotal(false);

            CTDataFields dataFields;
            if(pivotTable.getCTPivotTableDefinition().getDataFields() != null) {
                dataFields = pivotTable.getCTPivotTableDefinition().getDataFields();
            } else {
                dataFields = pivotTable.getCTPivotTableDefinition().addNewDataFields();
            }
            CTDataField dataField = dataFields.addNewDataField();
            dataField.setName("Calculated Field");
dataField.setFld(pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCount()-1);
            dataField.setBaseItem(0);
            dataField.setBaseField(0);
            DataFormat dataformat =  pivotTable.getParentSheet().getWorkbook().createDataFormat();
            short numFmtId = dataformat.getFormat("0.0%");
            dataField.setNumFmtId(numFmtId);

            try (FileOutputStream fileOut = new FileOutputStream("Output.xlsx")) {
                wb.write(fileOut);
            }
        }
    }

    public static void setCellData(XSSFSheet sheet,XSSFWorkbook wb){
        Row row1 = sheet.createRow(0);
        // Create a cell and put a value in it.
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("Names");
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("Age");
        Cell cell13 = row1.createCell(2);
        cell13.setCellValue("Dept");
        Cell cell14 = row1.createCell(3);
        cell14.setCellValue("Salary");
        Cell cell15 = row1.createCell(4);
        cell15.setCellValue("Actuals");
        Cell cell16 = row1.createCell(5);
        cell16.setCellValue("Estimates");


        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("Adam");
        Cell cell22 = row2.createCell(1);
        cell22.setCellValue(22);
        Cell cell23 = row2.createCell(2);
        cell23.setCellValue("Sales");
        Cell cell24 = row2.createCell(3);
        cell24.setCellValue(10);
        Cell cell25 = row2.createCell(4);
        cell25.setCellValue(12);
        Cell cell26 = row2.createCell(5);
        cell26.setCellValue(60);

        Row row3 = sheet.createRow(2);
        Cell cell31 = row3.createCell(0);
        cell31.setCellValue("Bran");
        Cell cell32 = row3.createCell(1);
        cell32.setCellValue(24);
        Cell cell33 = row3.createCell(2);
        cell33.setCellValue("Finance");
        Cell cell34 = row3.createCell(3);
        cell34.setCellValue(20);
        Cell cell35 = row3.createCell(4);
        cell35.setCellValue(24);
        Cell cell36 = row3.createCell(5);
        cell36.setCellValue(60);

        Row row4 = sheet.createRow(3);
        Cell cell41 = row4.createCell(0);
        cell41.setCellValue("Jane");
        Cell cell42 = row4.createCell(1);
        cell42.setCellValue(23);
        Cell cell43 = row4.createCell(2);
        cell43.setCellValue("IT");
        Cell cell44 = row4.createCell(3);
        cell44.setCellValue(30);
        Cell cell45 = row4.createCell(4);
        cell45.setCellValue(30);
        Cell cell46 = row4.createCell(5);
        cell46.setCellValue(60);

        Row row5 = sheet.createRow(4);
        Cell cell211 = row5.createCell(0);
        cell211.setCellValue("Dave");
        Cell cell221 = row5.createCell(1);
        cell221.setCellValue(30);
        Cell cell231 = row5.createCell(2);
        cell231.setCellValue("Sales");
        Cell cell241 = row5.createCell(3);
        cell241.setCellValue(50);
        Cell cell251 = row5.createCell(4);
        cell251.setCellValue(6);
        Cell cell261 = row5.createCell(5);
        cell261.setCellValue(60);

    }

There is no error when I add one or more column labels. Could someone please help me with this.

Actual Output

Expected output is only one non-calculated column and a calculated column as below.

Expected output

Vishnu Teja
  • 3
  • 2
  • 1
    Have you forgotten to close the workbook instance (`wb.close()`)? Your `FileOutputStream` is closed automatically, but the workbook won't. – deHaar Oct 10 '19 at 12:40
  • @deHaar try with resources would handle the closing part, still tried closing it. didn't work – Vishnu Teja Oct 10 '19 at 13:23
  • The reason I wrote this is my own experience with apache poi creating unreadable workbooks. I ended up not using a `try` with resources because of several things:. `try` with resources does not `flush()` the `FileOutputStream`. I always use a regular `try`-`catch` and the following methods in exactly that order: `workbook.write(fileOutputStream)`, `fileOutputStream.flush()`, `fileOutputStream.close()` and `workbook.close()`. – deHaar Oct 10 '19 at 13:32

1 Answers1

0

There is an element colFields in pivot table definition which needs at least one field if there are multiple data fields as column fields. But apache poi does not add that field if only one column label is set because it is not needed if only one column field is present.

But since you low level adds a data field as column field it then lacks that colFields field. That's why the error while opening the file in Excel.

If multiple column labels are set then apache poi adds that colFields field. That's why it works then.

So add following to your code after your low level creating the data field:

...
   // at least one field in colFields is needed if there are multiple data fields
   CTColFields colFields;
   if(pivotTable.getCTPivotTableDefinition().getColFields() != null) {
    colFields = pivotTable.getCTPivotTableDefinition().getColFields();
   } else {
    colFields = pivotTable.getCTPivotTableDefinition().addNewColFields();
   }
   CTField field;
   if (colFields.getFieldList().size() == 0) {
    field = colFields.addNewField();
    field.setX(-2);
   }
...

This adds a new colFields element if not already present. And it adds that field element there, if not already present.

The complete example again:

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 {

 public static void main(String[] args) throws Exception {
  createPivotTable();
 }

 private static void createPivotTable() throws Exception {
  try (XSSFWorkbook wb = new XSSFWorkbook(); 
       FileOutputStream fileOut = new FileOutputStream("Output.xlsx")) {

   XSSFSheet dataSheet = wb.createSheet("1econtent");
   XSSFSheet pivotSheet = wb.createSheet("1e");

   setCellData(dataSheet, wb);

   AreaReference source = new AreaReference("A1:F5", SpreadsheetVersion.EXCEL2007);
   CellReference position = new CellReference(0,0);

   XSSFPivotTable pivotTable = pivotSheet.createPivotTable(source, position,wb.getSheet("1econtent"));
   pivotTable.addRowLabel(2);
   pivotTable.addRowLabel(0);

   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3);

   pivotSheet.setDisplayGridlines(false);

   wb.setSelectedTab(1);
   wb.setActiveSheet(1);

   // customize pivot table
   CTCacheFields ctCacheFields = pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields();
   CTCacheField ctCacheField = ctCacheFields.addNewCacheField();
   ctCacheField.setName("Field");
   ctCacheField.setFormula("'Actuals' / 'Estimates'");
   ctCacheField.setDatabaseField(false);
   ctCacheFields.setCount(ctCacheFields.sizeOfCacheFieldArray());

   CTPivotField pivotField = pivotTable.getCTPivotTableDefinition().getPivotFields().addNewPivotField();
   pivotField.setDataField(true);
   pivotField.setDragToCol(false);
   pivotField.setDragToPage(false);
   pivotField.setDragToRow(false);
   pivotField.setShowAll(false);
   pivotField.setDefaultSubtotal(false);

   CTDataFields dataFields;
   if(pivotTable.getCTPivotTableDefinition().getDataFields() != null) {
    dataFields = pivotTable.getCTPivotTableDefinition().getDataFields();
   } else {
    dataFields = pivotTable.getCTPivotTableDefinition().addNewDataFields();
   }
   CTDataField dataField = dataFields.addNewDataField();
   dataField.setName("Calculated Field");
   dataField.setFld(pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCount()-1);
   dataField.setBaseItem(0);
   dataField.setBaseField(0);
   DataFormat dataformat =  pivotTable.getParentSheet().getWorkbook().createDataFormat();
   short numFmtId = dataformat.getFormat("0.0%");
   dataField.setNumFmtId(numFmtId);

   // at least one field in colFields is needed if there are multiple data fields
   CTColFields colFields;
   if(pivotTable.getCTPivotTableDefinition().getColFields() != null) {
    colFields = pivotTable.getCTPivotTableDefinition().getColFields();
   } else {
    colFields = pivotTable.getCTPivotTableDefinition().addNewColFields();
   }
   CTField field;
   if (colFields.getFieldList().size() == 0) {
    field = colFields.addNewField();
    field.setX(-2);
   }

   wb.write(fileOut);
  }
 }

 public static void setCellData(XSSFSheet sheet, XSSFWorkbook wb){
  Object[][] data = new Object[][] {
   new Object[] {"Names", "Age", "Dept", "Salary", "Actuals", "Estimates"},
   new Object[] {"Adam", 22d, "Sales", 10d, 12d, 60d},
   new Object[] {"Bran", 24d, "Finance", 20d, 24d, 60d},
   new Object[] {"Jane", 23d, "IT", 30d, 30d, 60d},
   new Object[] {"Dave", 30d, "Sales", 50d, 6d, 60d}
  };

  XSSFRow row;
  XSSFCell cell;
  for (int r = 0; r < data.length; r++) {
   row = sheet.createRow(r);
   for (int c = 0 ; c < data[r].length; c++) {
    cell = row.createCell(c);
    if (data[r][c] instanceof String) {
     cell.setCellValue((String)data[r][c]);
    } else if (data[r][c] instanceof Double) {
     cell.setCellValue((Double)data[r][c]);
    }
   }
  }
 } 
}
Axel Richter
  • 56,077
  • 6
  • 60
  • 87