Hi,

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.

    @Test
    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();
        namedCell.setNameName(cname);
        String reference = sname+"!A1:A1"; // area reference
        namedCell.setRefersToFormula(reference);
        
        // 2. rename the sheet 
        wb.setSheetName(wb.getSheetIndex(sheet), "newName");
        
        FileOutputStream fout = new FileOutputStream(file); 
        wb.write(fout);
    }

Any workarounds?

Regards,
Carl Pritchett

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. 


Yegor

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();
    namedRange.setRefersToFormula(formula);
}

I plan to work on it. Hope to get it fixed by POI 3.8 (early April 2011). 

Yegor

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");
 name.setNameName("sale_2");

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

Yegor

Thanks Yegor, 

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