You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Eric Gravel <Er...@intervalintl.com> on 2007/09/28 20:54:58 UTC
Having problems with using named cell in formula
I'm trying to set a cell's formula as such:
D10 * Shared Metrics!UIRpgJava
What I'm trying to do with this formula is multiply the value of D10 by
the value of a cell, on the "Shared Metrics" sheet within the same
workbook, named "UIRpgJava".
Here's the code and error I get (these are just the relevant code
extracts):
cell = row.createCell((short)5);
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
String uiSubTotalFormula = null;
try
{
uiSubTotalFormula =
DecompEstimateFormulas.getUISubTotalFormula(currentRowIndex);
cell.setCellFormula(uiSubTotalFormula);
}
catch (Exception e)
{
log.error("Encountered error while attempting to set the UI SubTotal
formula for row " +
currentRowIndex + ", module [" + uiModuleName + "]. Formula: [" +
uiSubTotalFormula
+ "]. Error: " + e.getMessage());
}
public class DecompEstimateFormulas
{
public static final String UI_SUBTOTAL_FORUMLA =
"(D{::RowIndex::} * 'Shared Metrics!UIRpgJava')";
public static String getUISubTotalFormula(int rowIndex)
{
String formula =
StringUtils.replace(UI_SUBTOTAL_FORUMLA, "{::RowIndex::}",
String.valueOf(rowIndex + 1));
return formula;
}
}
[Friday, September 28, 2007 - 14:20:58:765 EDT] ERROR:
Encountered error while attempting to set the UI SubTotal formula for
row 14, module [EDPEMLCLNT]. Formula: [(D15 * 'Shared
Metrics!UIRpgJava')]. Error: Invalid Formula cell reference: 'SHARED
METRICS!UIRPGJAVA'
Just in case, I also tried the formula with quotes around my named cell.
This yields the same outcome.
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: eric.gravel@intervalintl.com
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
_____________________________________________________________________________
Re: Having problems with using named cell in formula
Posted by Michael Kimberlin <mk...@gmail.com>.
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 <Er...@intervalintl.com> 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: eric.gravel@intervalintl.com
>
> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
M. Kimberlin
Systems Development Consultant
www.amateuratbest.com
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Having problems with using named cell in formula
Posted by Eric Gravel <Er...@intervalintl.com>.
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: eric.gravel@intervalintl.com
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Having problems with using named cell in formula
Posted by M Kimberlin <mk...@gmail.com>.
From my understanding, names are inserted at the workbook level, not
the worksheet level.
Furthermore, I don't believe that there is any way to insert a function
that takes a named object as a parameter. I have been fighting that
issue for quite some time. From what I have seen, it seems that POI can
parse and preserve such cells, but doesn't provide a means of setting
the formula value. If you create a cell in excel with a formula taking
a name as a parameter, then load that up in POI and get the formula
value from that cell, it spits back something like: COUNT( NO IDEA ) or
AVERAGE( NO IDEA ).
In excel you reference such ranges as: =COUNT("MyFile.xls"!MyName).
This doesn't work through POI that I have been able to find. What I
have done to get around this is VEEERY sick, but it works. In the cells
that I know will need formulas like that, I first put in a "static"
reference: COUNT(A:A) Then I wrote the following method to replace the
static reference with one of the named ranges that I create:
private void hackFunctionCellToSetNamePointer(HSSFWorkbook results,
HSSFCell targetcell,
String name) {
try {
short nameIndex = (short)(results.getNameIndex(name)+1);
// Get the original formula record aggregate. This must be
done via reflection, as it
// is not an accessible method.
Method method =
HSSFCell.class.getDeclaredMethod("getCellValueRecord", new Class[] {});
method.setAccessible(true);
FormulaRecordAggregate original = (FormulaRecordAggregate)
method.invoke(targetcell);
// A static area reference is a couple of bytes larger than
a name reference. So, if
// we don't reduce the formula record size, we will get
corruption of the produced file.
List parsedExpression =
original.getFormulaRecord().getParsedExpression();
short length = (short)
(original.getFormulaRecord().getExpressionLength()-2);
original.getFormulaRecord().setExpressionLength(length);
// Now we want to make sure that we cause the cell to be
calculated on-load. The following
// line should accomplish that. Note, that if you have
other options on the cell, they will
// be wiped out by this. Chances are that won't effect you
though.
original.getFormulaRecord().setOptions((short)2);
// Now we create a name pointer...the constructor I'm using
here has not been implemented yet.
// If it were, we would likely not neeed to set the
"field_2_ilbl" value. From what I can
// tell, this field is essentially the pointer to the name
table in the workbook. We give it
// the name index and leave the rest alone.
NameXPtg nameptr = new NameXPtg(name);
Field nameTableIndex =
NameXPtg.class.getDeclaredField("field_2_ilbl");
nameTableIndex.setAccessible(true);
nameTableIndex.setShort(nameptr, nameIndex);
// Now we remove the static area reference from the parsed
expression and add our name
// pointer in it's place. Please note that I have only
tried this with functions that take
// a single name pointer and of those, only functions with 1
or 2 total parameters. In the case
// of two parameters, I have only tested when the second
parameter is a constant. For example,
// This should work with: COUNT("MyFile.xls"!MyName) or
AVERAGE("MyFile.xls"!MyName) or even
// QUARTILE("MyFile.xls"!MyName, 1). I would imagine that
the process is similar for more complex
// function cells, but this is all I have needed, so that's
as far as I've gone. My guess is that
// the length would have to be decremented once for each
Area to Name conversion you do and I have
// no speculation about where the other area pointers might
land in the parsed expression (in terms
// of their index).
parsedExpression.remove(0);
parsedExpression.add(0, nameptr);
} catch (Exception e) {
// Do something with this...
log.error("Bad developer...BAD!", e);
}
}
I don't know if that will help you any and I won't claim that there
isn't a better way to do this...obviously I had to delve pretty deep
into the POI representation of the workbook, which is far from ideal.
However, it does work!
Good luck and let me know if you have questions.
-michael
Eric Gravel wrote:
> Hi Anthony,
>
> The formula I'm writing is referencing a cell on another sheet of the
> same workbook. If I write 'Shared Metrics'!C1 then it works but if in I
> assign
> a name to that cell, in my template before running the app, and call it
> MyVariable then the following doesn't seem to work:
>
> 'Shared Metrics'!MyVariable
>
>
> Is there no way of using named cells? This would make the programming
> much
> aasier should the named cell change position from C1 to D1 for example.
>
>
> 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: eric.gravel@intervalintl.com
>
> http://www.intervalworld.com
> http://friendsandfamily.condodirect.com/web/cs?a=5&clubCode=interval
> http://www.liveitup.com
>
> -----Original Message-----
> From: Anthony Andrews [mailto:pythonaddict@yahoo.com]
> Sent: Saturday, September 29, 2007 6:30 AM
> To: POI Users List
> Subject: Re: Having problems with using named cell in formula
>
> I could be wrong, but that formula does not look absolutely correct to
> my eyes. If I want to reference a cell on a sheet within another
> workbook, I would enter a formula something like;
>
> =[Book1]Sheet1!B1
>
> where Book1 is the name of the workbook, Sheet1 that of the sheet and B1
> the reference to the cell whose contents I want to access. So, you could
> try;
>
> =[UIRpgJava]Shared Metrics!C1
>
> assuming of course that C1 is the cell you wish to reference of course.
> IF all of the sheets are contained within a single workbook, then you do
> not need the reference to the workbook and the formula could simply
> become;
>
> =Shared Metrics!C1
>
> You may need to fiddle around a bit with enclosing quotation marks as
> the name of the sheet contains a space but I am not even sure that you
> will need to do that.
>
>
> _____________________________________________________________________________
> Scanned by IBM Email Security Management Services powered by MessageLabs. For more information please visit http://www.ers.ibm.com
> _____________________________________________________________________________
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Having problems with using named cell in formula
Posted by Eric Gravel <Er...@intervalintl.com>.
Hi Anthony,
The formula I'm writing is referencing a cell on another sheet of the
same workbook. If I write 'Shared Metrics'!C1 then it works but if in I
assign
a name to that cell, in my template before running the app, and call it
MyVariable then the following doesn't seem to work:
'Shared Metrics'!MyVariable
Is there no way of using named cells? This would make the programming
much
aasier should the named cell change position from C1 to D1 for example.
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: eric.gravel@intervalintl.com
http://www.intervalworld.com
http://friendsandfamily.condodirect.com/web/cs?a=5&clubCode=interval
http://www.liveitup.com
-----Original Message-----
From: Anthony Andrews [mailto:pythonaddict@yahoo.com]
Sent: Saturday, September 29, 2007 6:30 AM
To: POI Users List
Subject: Re: Having problems with using named cell in formula
I could be wrong, but that formula does not look absolutely correct to
my eyes. If I want to reference a cell on a sheet within another
workbook, I would enter a formula something like;
=[Book1]Sheet1!B1
where Book1 is the name of the workbook, Sheet1 that of the sheet and B1
the reference to the cell whose contents I want to access. So, you could
try;
=[UIRpgJava]Shared Metrics!C1
assuming of course that C1 is the cell you wish to reference of course.
IF all of the sheets are contained within a single workbook, then you do
not need the reference to the workbook and the formula could simply
become;
=Shared Metrics!C1
You may need to fiddle around a bit with enclosing quotation marks as
the name of the sheet contains a space but I am not even sure that you
will need to do that.
_____________________________________________________________________________
Scanned by IBM Email Security Management Services powered by MessageLabs. For more information please visit http://www.ers.ibm.com
_____________________________________________________________________________
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Having problems with using named cell in formula
Posted by Anthony Andrews <py...@yahoo.com>.
I could be wrong, but that formula does not look absolutely correct to my eyes. If I want to reference a cell on a sheet within another workbook, I would enter a formula something like;
=[Book1]Sheet1!B1
where Book1 is the name of the workbook, Sheet1 that of the sheet and B1 the reference to the cell whose contents I want to access. So, you could try;
=[UIRpgJava]Shared Metrics!C1
assuming of course that C1 is the cell you wish to reference of course. IF all of the sheets are contained within a single workbook, then you do not need the reference to the workbook and the formula could simply become;
=Shared Metrics!C1
You may need to fiddle around a bit with enclosing quotation marks as the name of the sheet contains a space but I am not even sure that you will need to do that.
Eric Gravel <Er...@intervalintl.com> wrote: I'm trying to set a cell's formula as such:
D10 * Shared Metrics!UIRpgJava
What I'm trying to do with this formula is multiply the value of D10 by
the value of a cell, on the "Shared Metrics" sheet within the same
workbook, named "UIRpgJava".
Here's the code and error I get (these are just the relevant code
extracts):
cell = row.createCell((short)5);
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
String uiSubTotalFormula = null;
try
{
uiSubTotalFormula =
DecompEstimateFormulas.getUISubTotalFormula(currentRowIndex);
cell.setCellFormula(uiSubTotalFormula);
}
catch (Exception e)
{
log.error("Encountered error while attempting to set the UI SubTotal
formula for row " +
currentRowIndex + ", module [" + uiModuleName + "]. Formula: [" +
uiSubTotalFormula
+ "]. Error: " + e.getMessage());
}
public class DecompEstimateFormulas
{
public static final String UI_SUBTOTAL_FORUMLA =
"(D{::RowIndex::} * 'Shared Metrics!UIRpgJava')";
public static String getUISubTotalFormula(int rowIndex)
{
String formula =
StringUtils.replace(UI_SUBTOTAL_FORUMLA, "{::RowIndex::}",
String.valueOf(rowIndex + 1));
return formula;
}
}
[Friday, September 28, 2007 - 14:20:58:765 EDT] ERROR:
Encountered error while attempting to set the UI SubTotal formula for
row 14, module [EDPEMLCLNT]. Formula: [(D15 * 'Shared
Metrics!UIRpgJava')]. Error: Invalid Formula cell reference: 'SHARED
METRICS!UIRPGJAVA'
Just in case, I also tried the formula with quotes around my named cell.
This yields the same outcome.
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: eric.gravel@intervalintl.com
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
_____________________________________________________________________________
---------------------------------
Shape Yahoo! in your own image. Join our Network Research Panel today!