That approach should definitely work...it's just that the formulas
will have the static ranges and it forces you to go doing a bunch of
string replacement. The code that I posted there will preserve the
use of the names in the output excel file. Judging from the formulas
that you listed, I think your FormulaRecord will be more complex than
the ones that I sent. However, what you can do to see what they look
like is create a formula like that in a cell, read in the file with
poi and then use the debugger to get down to the FormulaRecord and see
what the parsed formula looks like (this is a list of Ptg objects).
Following the calls in my code should get you there. You will see
NameXPtg or NamePtg in the list where the name references are or you
will see AreaPtg if you use a static range as a parameter. You can
then follow the same pattern that I did in that code and replace
AreaPtgs with your desired NameXPtgs.
So, if you are trying to populate a formula you could do something 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("(F10 * A:A * (1 - A:A))");
modifiedHackReplaceAreaWithName(0,"MyName");
modifiedHackReplaceAreaWithName(1,"MyOtherName");
Then base modifiedHackReplaceAreaWithName off of the code I sent, but
extend it to support replacement based on the index of the AreaPtg
that is to be replaced. The code I sent is tinkering with POIs
internal representation of the Excel file...so POI doesn't have to be
able to "parse" the formula into a string that we can see. It just
needs to be able to persist it, which it can! Excel turns the
tinkering back into something readable when it loads the produced
file.
If keeping the names intact and in use in the formulas is important to
you, I would be more than happy to discuss this over the phone. There
is a lot of opportunity to cross wires in email on a topic like this.
I am pretty positive that we have been fighting the same battle, just
different ways.
good luck,
-michael
On 10/1/07, Eric Gravel <[EMAIL PROTECTED]> wrote:
> 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]
>
>
--
M. Kimberlin
Systems Development Consultant
www.amateuratbest.com
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
