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
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.
*** Bug 57474 has been marked as a duplicate of this bug. ***
Fixed via r1722410, we now try to adjust references to sheets in formulas correctly when sheets are re-ordered.