| Summary: | unreadable content after remove formula | ||
|---|---|---|---|
| Product: | POI | Reporter: | guoh0115 |
| Component: | XSSF | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | Keywords: | APIBug |
| Priority: | P2 | ||
| Version: | 3.12-FINAL | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | All | ||
| Attachments: | test file | ||
This happens when formulas are "shared", e.g. by draging the cell with a formula over other cells. Excel stores this in the copied-to cells as <f si="0" t="shared"/> without storing the actual formula multiple times. The source-cell stores the original formula: <f ref="B13:G13" si="0" t="shared">SUM(B1:B3)</f> So when the formula with the shared formula is removed, either the next one needs to be made the "master"-formula or the sharing needs to be undone for all formulas. Not sure how we can solve this, it would require to "transpose" the formula from one Cell to the next one depending on the reference in the Formula of the removed cell
We can detect this case with the following in XSSFWorkbook.onDeleteFormula(), but I am not sure how we would go about fixing this :(
protected void onDeleteFormula(XSSFCell cell){
if(calcChain != null) {
// TODO: check for shared formulas!
CTCellFormula f = cell.getCTCell().getF();
if(f.getT().equals(org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType.SHARED) &&
f.getRef() != null &&
f.getStringValue() != null) {
// shared formula and needs adjustment as the formula is stored here...
System.out.println("Need to adjust for shared formula here!");
}
...
}
}
I wonder if the "shift rows" formula changing logic could be re-used for this? |
Created attachment 32882 [details] test file Here's my code. test.xlsx is attached. Workbook workbook = WorkbookFactory.create(new File("test.xlsx")); workbook.getSheetAt(0).getRow(12).getCell(1).setCellType(Cell.CELL_TYPE_NUMERIC); workbook.write(new FileOutputStream("test2.xlsx")); the workbook contains some formular cells. after I remove one of the formular cell. below error occurs. Removed Records: Shared formular from /xl/worksheets/sheet1.xml part Removed Records: Formula from /xl/calcChain.xml part (Calculation properties) I checked the sheet1.xml in the xlsx file and found the formular with shared properties.If I remove the formular of B13. the result will be abnormal. <f ref="B13:G13" si="0" t="shared">SUM(B1:B3)</f>