Summary: | HSSFWorkbook#setSheetOrder corrupts cross-sheet references when there are complicated external sheet indices | ||
---|---|---|---|
Product: | POI | Reporter: | Matthew Wightman <msww-asfbugs> |
Component: | HSSF | Assignee: | POI Developers List <dev> |
Status: | NEW --- | ||
Severity: | major | ||
Priority: | P2 | ||
Version: | 3.14-FINAL | ||
Target Milestone: | --- | ||
Hardware: | All | ||
OS: | All |
Description
Matthew Wightman
2016-06-09 08:57:39 UTC
For a Workbook with sheets A, B and C, if someone creates a formula covering a range of sheets A and Bm then re-orders C before B, then having it cover A+C+B seems logical to me I guess that setSheetOrder should do something for the external sheet records too. Would the delete sheet case need to do so as well, or is that already covered? As a first step, maybe it would be good to go through the code you've been looking at, and identify any places where internal and external sheet indexes get incorrectly treated as equivalent, then add TODOs / Warnings there. Also in methods where there's a risk of confusion, rename the variables to things like internalSheetIndex and externalSheetIndex to make it clearer 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"); } } |