https://issues.apache.org/bugzilla/show_bug.cgi?id=45353
Brad Sneade <[EMAIL PROTECTED]> changed:
What |Removed |Added
----------------------------------------------------------------------------
Severity|enhancement |normal
Status|ASSIGNED |NEEDINFO
--- Comment #2 from Brad Sneade <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
