Bug 57861 - Unreadable content after setCellFormula(null) and value used in another sheet
Summary: Unreadable content after setCellFormula(null) and value used in another sheet
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.11-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-04-27 13:23 UTC by thorsen83
Modified: 2015-04-27 15:38 UTC (History)
0 users



Attachments
Template used to create the graph (50.49 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-04-27 15:30 UTC, thorsen83
Details
Template used to create the graph (50.49 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-04-27 15:31 UTC, thorsen83
Details

Note You need to log in before you can comment on or make changes to this bug.
Description thorsen83 2015-04-27 13:23:57 UTC
After clearing a XSSFCell formula with setCellformula(null), if this cell is used in another cell in other sheet, Excel claims "unreadable content" because of the change. In the other hand, if it isn't used in other place, everything goes fine.
Comment 1 Dominik Stadler 2015-04-27 14:09:05 UTC
Not sure if I understand completely what you are trying to do, but it is probably not a good idea to "re-use" objects across rows/sheets as they have links internally. Especially if you remove objects they should usually not be used any more after that. 

If you think this is still a valid bug, then please post some code, preferably in self-sufficient unit-test form so we can try to comment on it and/or reproduce it locally.
Comment 2 thorsen83 2015-04-27 15:30:27 UTC
Created attachment 32689 [details]
Template used to create the graph
Comment 3 thorsen83 2015-04-27 15:31:10 UTC
Created attachment 32690 [details]
Template used to create the graph
Comment 4 thorsen83 2015-04-27 15:37:24 UTC
The use case is a basic xlsx file with two sheets, one for data the other for a graph. This xlsx file is used as a template, so everytime the app is asked to generate a new graph it reads the template, sets cell values and evaluates formula cells in order to generate the graph. Finally, the data sheet is hid and the graph sheet selected as active and thus the app is able to send it to printer and only get the generated graph.
In the data sheet, we have a final column with formulae that sometimes we want to override with database-generated values and that's why we need to clear the formulae. Those values or formulae are being used in the graph sheet and there is where we're experiencing this bug. If we remove the graph sheet, Excel doesn't have the unreadable content problem otherwise has.

Code used:

XSSFCell cell;

[...]
cell.setCellFormula(null);
cell.setCellValue(value);
[...]
XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
workbook.setSheetHidden(workbook.getSheetIndex(dataSheet), true);
workbook.setActiveSheet(workbook.getSheetIndex(graphSheet));