Bug 44495 - named cell references in formulas get erroneously capitalized
Summary: named cell references in formulas get erroneously capitalized
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-FINAL
Hardware: Other All
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2008-02-26 22:23 UTC by David Smiley
Modified: 2008-03-03 07:25 UTC (History)
0 users

svn diff of single line changes to 2 files (1.45 KB, patch)
2008-02-27 11:41 UTC, Josh Micich
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
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();

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 !
        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