Thanks Michael,
I think I may have found my answer in your email. I haven't read
through
all of the code you provided but the first few lines gave me an idea.
As I stated in my previous emails, the problem I'm to solve is that I
have
Formulas that referenced named cell on the same or other sheets of the
Workbook. For example:
(U15 * 'Shared Metrics'!Remainder * (1 - 'Shared
Metrics'!Reeng))
When POI parses this formula, it chokes on Remainder and Reeng since
they
aren't cell ranges. So the apparent solution is to parse & translate
those
cell reference names to known cell ranges. This defeats the purpose of
naming cells and not worrying if they are moved around on the sheet.
Else
you'd have to continually update your code. So that's what I did.
I wrote a parser that will attempt to find any of my cell reference
names
And when encountered, translates it to the cell range. Example:
'Shared Metrics'!Remainder becomes 'Shared Metrics'!E4
Looking at your code, I think I can enhance my parser/translator so that
it keeps the dynamic nature of named cells. As you stated, POI does
parse
all the name cells so there is a way of finding out the cell's location.
When my parser would find one of my cell names, rather than using
the hardcoded cell location value, it would query the workbook.
Here's what I think the code will look like
HSSFSheet sheet = workbook.getSheetAt(1);
HSSFRow row = sheet.getRow(15);
HSSFCell cell = row.getCell((short) 5);
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula(
FormulaParser.getSubTotalFormula(workbook, "F", 10));
FormulaParse:
public static String = UI_SUBTOTAL_FORMULA =
"({::ColumnLetter::}{::RowIndex::} * " +
"'Shared Metrics'!Remainder * " +
"(1 - 'Shared Metrics'!Reeng))";
public static String getSubTotalFormula(
HSSFWorkbook workbook,
String columnLetter,
int rowIndex)
{
String formula = StringUtils.replace(
UI_SUBTOTAL_FORUMLA,
"{::ColumnLetter::}",
columnLetter);
formula = StringUtils.replace(
formula
"{::RowIndex::}",
String.valueOf(rowIndex + 1));
formula = parseNamedCellTranslation(workbook, formula);
return formula;
}
protected static String parseNamedCellTranslation(
HSSFWorkbook workbook,
String formula)
{
String newFormula = formula;
for (String namedCell : this.NAMED_CELLS)
{
if (StringUtils.contains(newFormula, namedCell)
== true) {
int namedCellIndex =
workbook.getNameIndex("Remainder");
HSSFName namedCellObject =
workbook.getNameAt(namedCellIndex);
// HSSFName.getReference will return
$E$4.
String namedCellLocation =
namedCellObject.getReference();
newFormula = StringUtils.replace(
newFormula, namedCell,
namedCellLocation);
}
}
return newFormula;
}
Eric A. Gravel
Senior Java Programmer/Analyst
Internet Development, Application Services, I.T.
Interval International
6262 Sunset Drive, PH-1
Miami, FL, 33143
Office: (305) 666-1861 x7315
Fax: (305) 668-3409
Email: [EMAIL PROTECTED]
http://www.intervalworld.com
http://friendsandfamily.condodirect.com/web/cs?a=5&clubCode=interval
http://www.liveitup.com
_____________________________________________________________________________
Scanned by IBM Email Security Management Services powered by MessageLabs. For
more information please visit http://www.ers.ibm.com
_____________________________________________________________________________
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
