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.
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.
Created attachment 21597 [details] svn diff of single line changes to 2 files
Thanks for this patch Josh, applied to svn trunk