Bug 45353 - Formula calcuation does not support named cells
Summary: Formula calcuation does not support named cells
Status: RESOLVED WORKSFORME
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: PC Linux
: P2 enhancement (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2008-07-07 09:43 UTC by Brad Sneade
Modified: 2017-02-18 21:33 UTC (History)
1 user (show)



Attachments
Example referenced in the description (6.53 KB, application/java-archive)
2008-07-07 09:43 UTC, Brad Sneade
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Brad Sneade 2008-07-07 09:43:50 UTC
Created attachment 22222 [details]
Example referenced in the description

Note: the version is 3.1-FINAL (but there is no selection for it in the Version field)

When calculating a formula that has "named" cell references I get the following exception.

Exception in thread "main" java.lang.ClassCastException: org.apache.poi.hssf.record.formula.eval.NameEval cannot be cast to org.apache.poi.hssf.record.formula.eval.ValueEval
	at org.apache.poi.hssf.record.formula.eval.NumericOperationEval.singleOperandEvaluate(NumericOperationEval.java:62)
	at org.apache.poi.hssf.record.formula.eval.AddEval.evaluate(AddEval.java:67)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.invokeOperation(HSSFFormulaEvaluator.java:473)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateCell(HSSFFormulaEvaluator.java:386)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.internalEvaluate(HSSFFormulaEvaluator.java:338)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCell(HSSFFormulaEvaluator.java:183)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:278)
	at NamedCellTest.main(NamedCellTest.java:20)

I've provided two examples:

* named-cell-test has one named cell and a second cell getting its value (=NAME)
* named-cell-in-formula-test has two named cells and a third adding them together (=sum(name1+name2))
Comment 1 Nick Burch 2008-07-10 14:58:08 UTC
Currently, named cell references are not supported

Josh tells me it's something he hopes to add support for at some point, but there's no ETA as yet.
Comment 2 Brad Sneade 2008-07-11 06:50:09 UTC
Ah, thanks for the help anyways.  I ended up rewriting all the name references before processing the formulas.  Its not very elegant, but here is the code I used as the workaround:

protected void rewriteFormulas(final HSSFWorkbook workbook) {
    // build up a cache of names
    // this is just an easy way of fetching the HSSFName based on the string
    // representation of the name
    final Map<String, HSSFName> nameCache = new HashMap<String, HSSFName>(
            workbook.getNumberOfNames());
    for (int i = 0; i < workbook.getNumberOfNames(); i++) {
        final HSSFName name = workbook.getNameAt(i);
        nameCache.put(name.getNameName(), name);
    }
    // remove all the sheet names from the name references, having the sheet
    // names around messes up the formulas
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        nameCache.remove(workbook.getSheetName(i));
    }
    LOG.info("Names: " + nameCache.keySet());

    // loop over all the cells and rewrite the formula ones
    for (int sheetCount = 0; sheetCount < workbook.getNumberOfSheets(); sheetCount++) {
        final HSSFSheet sheet = workbook.getSheetAt(sheetCount);
        for (final Iterator rowIterator = sheet.rowIterator(); rowIterator
                .hasNext();) {
            final HSSFRow row = (HSSFRow) rowIterator.next();
            for (final Iterator cellIterator = row.cellIterator(); cellIterator
                    .hasNext();) {
                final HSSFCell cell = (HSSFCell) cellIterator.next();
                if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                    String formula = cell.getCellFormula();
                    for (final String name : nameCache.keySet()) {
                        final Pattern pattern = Pattern.compile("(\\W|^)"
                                + name + "(\\W|$)",
                                Pattern.CASE_INSENSITIVE);
                        final HSSFName hssfName = nameCache.get(name);
                        formula = pattern.matcher(formula).replaceAll(
                                "$1"
                                        + hssfName.getReference().replace(
                                                "$", "\\$") + "$2");
                    }
                    LOG.info("Resetting Cell (" + cell.toString()
                            + ") Formula:" + formula);
                    cell.setCellFormula(formula);
                } // end if
            } // end for
        } // end for
    } // end for
}

This seems to work with the formulas I was able to test.  Unfortunatly I've had to abandon this effort because of performance problems with the HSSFFormulaEvaluator (I'll submit another ticket for that).
Comment 3 Dominik Stadler 2017-02-18 21:33:56 UTC
This seems to nowadays as I could not reproduce this any more, please reopen with more information if this is still a problem for you.