poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject DO NOT REPLY [Bug 45353] Formula calcuation does not support named cells
Date Fri, 11 Jul 2008 13:50:11 GMT
https://issues.apache.org/bugzilla/show_bug.cgi?id=45353


Brad Sneade <bsneade+apache@gmail.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Severity|enhancement                 |normal
             Status|ASSIGNED                    |NEEDINFO




--- Comment #2 from Brad Sneade <bsneade+apache@gmail.com>  2008-07-11 06:50:09 PST
---
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).


-- 
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org


Mime
View raw message