#13 Cannot transform an XLSX with a pivot table in it

[Aron Gombas]

I have two columsn, "Status" (text in $B) and "Value" (numeric in $C). After the rendering it looks like this, for example:

    | STATUS      | VALUE
| Open        | 1
| Open        | 5
| Closed      | 2
| Pending     | 3

In the template, I created a named range called "statusValue" that covers these cells. Then still in the template, I added a pivot table with "statusValue" as input.

I'd expect that after opening the rendered spreadsheet, where the 2 columns are filled with data, my pivot table is evaluated.

Now I'm trying to transform this spreadsheet, but it crashes:

java.lang.IllegalStateException: Cannot get a text value from a numeric cell
[INFO] [talledLocalContainer]   at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:845)
[INFO] [talledLocalContainer]   at org.apache.poi.xssf.usermodel.XSSFCell.getRichStringCellValue(XSSFCell.java:294)
[INFO] [talledLocalContainer]   at org.apache.poi.xssf.usermodel.XSSFCell.getRichStringCellValue(XSSFCell.java:64)
[INFO] [talledLocalContainer]   at net.sf.jett.transform.CollectionsTransformer.findCollectionsInBlock(CollectionsTransformer.java:349)
[INFO] [talledLocalContainer]   at net.sf.jett.transform.CollectionsTransformer.transform(CollectionsTransformer.java:138)
[INFO] [talledLocalContainer]   at net.sf.jett.transform.CellTransformer.transform(CellTransformer.java:110)
[INFO] [talledLocalContainer]   at net.sf.jett.transform.BlockTransformer.transform(BlockTransformer.java:72)
[INFO] [talledLocalContainer]   at net.sf.jett.transform.BlockTransformer.transform(BlockTransformer.java:34)
[INFO] [talledLocalContainer]   at net.sf.jett.transform.SheetTransformer.transform(SheetTransformer.java:69)
[INFO] [talledLocalContainer]   at net.sf.jett.transform.ExcelTransformer.transform(ExcelTransformer.java:353)
[INFO] [talledLocalContainer]   at net.sf.jett.transform.ExcelTransformer.transform(ExcelTransformer.java:328)

[Aron Gombas]

What makes it even worse that I cannot know which exact cell causes this exception. That should always be included in the exception message in situations like this.

[Etienne Canaud]

I've been transforming many spreadsheets with Pivot tables in them, and none of them had issue. There's two "gotchas" to keep in mind when doing so:

1) Be sure to enable "Refresh Data when opening the file" in Pivot table Options/Data

2) You should double check that the pivot table in the template does NOT contain any evaluation tags coming from the source range. In order to do so, I create the Pivot table from some "real" data from a rendered template, and when I have the output right, I replace the data with the template tags (but I don't refresh the pivot table contents). If for some reason I need to refresh the pivot table contents and that tags appear, I need to delete all of them (I replace all labels with a space character, and data gets re-inserted at runtime). It's a bit tedious, but it works.

That being said, I second that every time there's an exception in JETT (be it for JEXL evaluation or JETT transformation), it would be quite helpful to have the spreadsheet name and cell # in the Exception message, if that's possible of course.

Thanks,
Etienne.

[Aron Gombas]

Yea, Etienne, I also figured out the work around you describe in point 2.

What I actually do is that I create the source data with evaluation tags, then create the pivot table from it. And, finally, I edit the cells of the pivot table that contain evaluation tags and replace them with static text. For instance, I overwrite "${project.name}" with just "Project".

To me, the best solution would be if the transformer could skip the pivot table cells, but with this work around it is less critical.