You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2023/12/19 09:05:25 UTC
[Bug 59677] HSSFWorkbook#setSheetOrder corrupts cross-sheet references when there are complicated external sheet indices
https://bz.apache.org/bugzilla/show_bug.cgi?id=59677
--- Comment #2 from Matti Kannala <ma...@solibri.com> ---
Another example code to reproduce this:
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class SheetTest {
public static void main(String[] args) {
testHSSFWorkbookSetSheetOrderFormulaBug(new XSSFWorkbook());
testHSSFWorkbookSetSheetOrderFormulaBug(new HSSFWorkbook());
}
private static void testHSSFWorkbookSetSheetOrderFormulaBug(Workbook
workbook) {
Sheet sheetA = workbook.createSheet("SheetA");
Sheet sheetB = workbook.createSheet("SheetB");
Sheet sheet = workbook.createSheet("Sheet1");
// Add some data to the sheet A
Row rowA = sheetA.createRow(0);
rowA.createCell(0).setCellValue("Value");
rowA.createCell(1).setCellValue("A");
// Add some data to the sheet B
Row rowB = sheetB.createRow(0);
rowB.createCell(0).setCellValue("Value");
rowB.createCell(1).setCellValue("B");
// Add a formula to the main sheet referring to the sheet B
Row row = sheet.createRow(0);
String formula = "VLOOKUP(\"Value\",SheetB!A1:B2,2)";
row.createCell(0).setCellFormula(formula);
Cell cell = sheet.getRow(0).getCell(0);
System.out.println();
System.out.println("TESTING: " + workbook.getClass().getSimpleName());
System.out.println("ORIGINAL FORMULA: " + formula);
System.out.println();
System.out.println("BEFORE setSheetOrder: " + cell.getCellFormula());
// Move the sheet B before the sheet A
workbook.setSheetOrder("Sheet1", 0);
// Formula changes to refer to the sheet A
System.out.println("AFTER setSheetOrder: " + cell.getCellFormula());
System.out.println(cell.getCellFormula().equals(formula) ? "PASS" :
"FAIL");
}
}
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org