0

The problem originates when programmaticly creating pivot tables in .xslx file using the POI 4.1 library.

Part of the upgrade of POI to 4.1 from 3.X is the removal of setting cell types. Thus in 4.X calling and setting of cell types is automatically handled. In my case, I had thought my issue resulted from this change. Example of a refactor below

   currentRow.createCell(i, Cell.CELL_TYPE_NUMERIC).setCellValue(Double.parseDouble(str[i]));

to 

   currentRow.createCell(i).setCellValue(Double.parseDouble(str[i]));

When creating a pivot table, using the function .createPivotTable() I was getting the below error.

Exception in thread "main" java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell
at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:1003)
at org.apache.poi.xssf.usermodel.XSSFCell.getRichStringCellValue(XSSFCell.java:389)
at org.apache.poi.xssf.usermodel.XSSFCell.getStringCellValue(XSSFCell.java:341)
at org.apache.poi.xssf.usermodel.XSSFPivotCacheDefinition.createCacheFields(XSSFPivotCacheDefinition.java:182)
at org.apache.poi.xssf.usermodel.XSSFSheet.createPivotTable(XSSFSheet.java:4452)
at org.apache.poi.xssf.usermodel.XSSFSheet.createPivotTable(XSSFSheet.java:4421)

From the code line

    XSSFPivotTable pivotTable = 
    pivot_sheet.createPivotTable(aref,pos,sheet);

I had thought this had to do with the data being formatted for the pivot table due to the change in the cell creation.

The issue ended up being that my data had created a table of raw numeric cells but without any header. The .createPivotTable() will call the 'first cell' as a String assuming that it is a header. This is a default operation of the function.

This default operation should be documented in the function or a better exception written to clarify if thrown error was from a data cell or header cell.

TLDR. createPivotTable() requires a String header to set the column name.

SolutionFindingBrowser
  • 63
  • 8
  • Looks like a bug to me. Can you create a very small testcase that shows the problem? If so, upload it to [Apache POI bug tracking system](https://bz.apache.org/bugzilla/enter_bug.cgi?product=POI) – Gagravarr Apr 26 '19 at 15:52
  • @gagravarr The solution is that it requires a header in the data to work correctly. I don't think its a bug, but rather a missing documentation on function operation. – SolutionFindingBrowser Apr 26 '19 at 15:57

0 Answers0