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