Bug 44495

Summary: named cell references in formulas get erroneously capitalized
Product: POI Reporter: David Smiley <dsmiley>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: major    
Priority: P2    
Version: 3.0-FINAL   
Target Milestone: ---   
Hardware: Other   
OS: All   
Attachments: svn diff of single line changes to 2 files

Description David Smiley 2008-02-26 22:23:49 UTC
I have a workbook that has formulas that refer to named cells.  For example "=actStart".  Very simple.  Using a technique I saw on the list, in my code I iterate through all cells to ensure that Excel re-evaluates the references.  I do this by getting the cell formula and setting the formula value back:
               String formula = cell.getCellFormula();
               cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
               cell.setCellFormula(formula);

However, this seems to have the effect of modifying my formulas to refer to the name in all upper case, like "=ACTSTART" for the example above.  That's wrong and breaks, causing a bunch of "#NAME?" to show up on the sheet.

By the way, I can't simply use sheet.setForceFormulaRecalculation(true) because it doesn't always work for me, but that's a different issue.
Comment 1 Josh Micich 2008-02-27 11:39:30 UTC
I am in the process of extending the formula parser to support a few more things (operator precedence, error literals, named ranges, external functions, etc).  As currently coded, POI assumes all identifiers in formulas should be converted to uppercase.  In my opinion, this is NQR, because Excel is not simply "case insensitive", but also "case aware".

Here is a test case which hopefully targets the problem you are describing:

    public void testParseNamedRangesCaseSensitivity() {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        wb.setSheetName(0, "Sheet1");
        HSSFCell cell = sheet.createRow(0).createCell((short)0);

        HSSFName hssfName = wb.createName();
        hssfName.setNameName("myNamedCell"); // camelcase !
        
        cell.setCellFormula("mynamedcell");
        String actualFormula=cell.getCellFormula();
        assertEquals("myNamedCell", actualFormula); // not "MYNAMEDCELL", not "mynamedcell"
   }

If you put a breakpoint in the no-arg constructor of NameRecord, you can see why this is occurring.

NameRecord.<init>() line: 124	
NamePtg.<init>(String, Workbook) line: 58	<<<< problem in this method
FormulaParser.Ident() line: 336	                <<<< another problem here
FormulaParser.Factor() line: 522	
FormulaParser.Term() line: 660	
FormulaParser.Expression() line: 708	
FormulaParser.parse() line: 812	
HSSFCell.setCellFormula(String) line: 643	
TestFormulaParser.testParseNamedRangesCaseSensitivity() line: 3742	

I made a patch diff file that fixes _just_ this issue.
Comment 2 Josh Micich 2008-02-27 11:41:04 UTC
Created attachment 21597 [details]
svn diff of single line changes to 2 files
Comment 3 Nick Burch 2008-03-03 07:25:57 UTC
Thanks for this patch Josh, applied to svn trunk