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: [EMAIL PROTECTED]
http://www.intervalworld.com
http://friendsandfamily.condodirect.com/web/cs?a=5&clubCode=interval
http://www.liveitup.com
-----Original Message-----
From: Anthony Andrews [mailto:[EMAIL PROTECTED]
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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
