Bug 47100 - Change Worksheet name, related formula are not updated
Summary: Change Worksheet name, related formula are not updated
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.7-FINAL
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2009-04-27 00:01 UTC by Matthew
Modified: 2011-03-04 06:32 UTC (History)
1 user (show)

Original.xlsx (8.16 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2009-04-27 00:01 UTC, Matthew

Note You need to log in before you can comment on or make changes to this bug.
Description Matthew 2009-04-27 00:01:30 UTC
Created attachment 23546 [details]

I write following codes to change a worksheet name, but the related named range "OriginalValue" does not update its formula: expected "New!$A$1" but actual "Original!$A$1".


Workbook sourceWb = (open "Original.xlsx"...)
int sheetIdx = sourceWb.getSheetIndex("Original");
sourceWb.setSheetName(sheetIdx, "New");
sourceWb.write(new FileOutputStream("New.xlsx"));


The Excel 2007 file for testing is attached.
Comment 1 Carl Pritchett 2011-02-27 22:46:19 UTC

When I create a HSSFWorkbook with a named range and then set the sheet name with Workbook.setSheetName() all the named ranges' formulas are renamed also. 

When I do this with a XSSFWorkbook the named ranges' formulas are not renamed and thus are broken (have the value of #REF!").

Here is a JUnit test that creates a file with such a broken named range.

    public void create() throws IOException
        String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";
        // HSSFWorkbook handles rename
        // Workbook wb = new HSSFWorkbook();
        //File file = new File("c:\\test.xls");
        // XSSFWorkbook does not handle the rename
        Workbook wb = new XSSFWorkbook();
        File file = new File("c:\\test.xlsx");
        Sheet sheet = wb.createSheet(sname);
        sheet.createRow(0).createCell((short) 0).setCellValue(cvalue);

        // 1. create named range for a single cell using areareference
        Name namedCell = wb.createName();
        String reference = sname+"!A1:A1"; // area reference
        // 2. rename the sheet 
        wb.setSheetName(wb.getSheetIndex(sheet), "newName");
        FileOutputStream fout = new FileOutputStream(file); 

Any workarounds?

Carl Pritchett
Comment 2 Yegor Kozlov 2011-03-01 11:36:29 UTC
It is a bug in XSSF. 

In the binary .xls format formulas are stored in parsed form as sequences of tokens. The Sheet Name token just points to the corresponding SheetRecord and when you change that SheetRecord  all depending formulas are automatically updated. 

The .xlsx format is totally different. It is a zip of xml files and formulas are stored in plain text. XSSFWorkbook.setSheetName just updates the corresponding bits in workbook.xml and that's all. No formulas are updated.  

To fix your problem,  XSSFWorkbook.setSheetName should iterate over all formulas and named ranges and update the sheet name. 

Comment 3 Carl Pritchett 2011-03-01 16:58:12 UTC
Thanks for the explanation Yegor,

As a workaround, I made methods to update related named ranges when a sheet name changes - could something similar be added to XSSFWorkbook? May not be the most efficient or the best use of the POI APIs...

public void setSheetName(Workbook wb, Sheet sheet, String name)
    String newName = WorkbookUtil.createSafeSheetName(name);
    String oldName = sheet.getSheetName();
    wb.setSheetName(wb.getSheetIndex(sheet), newName);
    if (wb instanceof XSSFWorkbook)
        updateNamedRangesWithSheetName(wb, sheet, oldName);

public void updateNamedRangesWithSheetName(Workbook wb, Sheet sheet, String oldName)
    int numNames = wb.getNumberOfNames();     
    for (int i = 0; i < numNames; i++)
        Name namedRange = wb.getNameAt(i);
        if (namedRange == null || namedRange.isDeleted() || !oldName.equals(namedRange.getSheetName())) continue;
        AreaReference areaRef = new AreaReference(namedRange.getRefersToFormula());

        CellReference firstCell = areaRef.getFirstCell();
        CellReference lastCell = areaRef.getLastCell();
        updateNamedRange(sheet, namedRange, firstCell.getRow(), firstCell.getCol(), lastCell.getRow(), lastCell.getCol());

private void updateNamedRange(Sheet sheet, Name namedRange, int firstRow, int firstCol, int lastRow, int lastCol)
    CellReference firstCellRef = new CellReference(firstRow, firstCol, true, true);
    CellReference lastCellRef = new CellReference(lastRow, lastCol, true, true);
    AreaReference ref = new AreaReference(firstCellRef, lastCellRef);
    String formula = "'" + sheet.getSheetName() + "'!" + ref.formatAsString();
Comment 4 Yegor Kozlov 2011-03-02 06:40:53 UTC
I plan to work on it. Hope to get it fixed by POI 3.8 (early April 2011). 

Comment 5 Yegor Kozlov 2011-03-04 06:17:18 UTC
Fixed in r1077878, junit added

The fix that you suggested is naive and works only in simple cases. The correct approach is to parse every formula in the workbook and render it back to string with the updated sheet name. This way we ensure that the updated formulas are syntactically correct and parsable. 

P.S. we may need a similar fix for named ranges. Consider a case when you open a workbook and then change a name of a named range, e.g.

 XSSFWorkbook wb = new XSSFWorkbook();
 XSSFName name = wb.getName("sale_1");

 Similar to changing sheet name, all dependent formulas and named ranges should be updated. I'm leaving it for a new Bugzilla ticket.

Comment 6 Carl Pritchett 2011-03-04 06:32:22 UTC
Thanks Yegor, 

What depends on a named range that could be broken if the named ranged name was changed?