58746
2015-12-17 09:51:19 +0000
Formula: changed sheet name after re-order
2015-12-30 20:34:43 +0000
1
1
1
Unclassified
POI
HSSF
3.13-FINAL
PC
All
RESOLVED
FIXED
P2
major
---
1
michele.bissoli.83
dev
chiranjeeviavvaru
onealj
0
oldest_to_newest
186997
0
michele.bissoli.83
2015-12-17 09:51:19 +0000
Hi all,
when I create a xls that contains a cell formula that refers to other cells in others sheets AND after I re-order the sheet with this formula, the cell doesn't contain the correct formula: POI has changed the sheet names into the formula!
Example code:
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet first = workbook.createSheet("first");
first.createRow(0).createCell(0).setCellValue(1);
HSSFSheet second = workbook.createSheet("second");
second.createRow(0).createCell(0).setCellValue(2);
HSSFSheet third = workbook.createSheet("third");
HSSFRow row = third.createRow(0);
row.createCell(0).setCellFormula("first!A1");
row.createCell(1).setCellFormula("second!A1");
// re-order for sheet "third"
workbook.setSheetOrder("third", 0);
What I obtain into the sheet "third":
- A1: "=second!A1" > it must be "=first!A1"!!
- B1: "=first!A1" > it must be "=second!A1"!!
Could you please correct this bug as soon as possible? This is very important for the work i'm doing.
This bug is the same of 57474 but here I provide a simple code for reproduce the problem.
Thanks very much,
Michele
187114
1
michele.bissoli.83
2015-12-24 10:55:16 +0000
For everyone who have this problem, I have found a workaround: at start create all the sheets and only after insert all the data, so without execute a re-order.
Example:
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet first = workbook.createSheet("first");
HSSFSheet second = workbook.createSheet("second");
HSSFSheet third = workbook.createSheet("third");
first.createRow(0).createCell(0).setCellValue(1);
second.createRow(0).createCell(0).setCellValue(2);
HSSFRow row = third.createRow(0);
row.createCell(0).setCellFormula("first!A1");
row.createCell(1).setCellFormula("second!A1");
This is only a workaround, the problem still exists.
187202
2
dominik.stadler
2015-12-29 08:20:31 +0000
*** Bug 57474 has been marked as a duplicate of this bug. ***
187272
3
dominik.stadler
2015-12-30 20:34:43 +0000
Fixed via r1722410, we now try to adjust references to sheets in formulas correctly when sheets are re-ordered.