Dynamic Named Ranges?

[Michael Kimberlin <mk...@gmail.com>]

So, I posted this on the jXLS (http://jxls.sourceforge.net) lists
first, because it is making the call to POI, but I think that this
might be a limitation of POI or the way jXLS is using it.  So, I
thought I would see if anyone on here has some insight as to what my
problem might be.  Essentially, I fear it might be the way named
ranges are dealt with in functions through POI.  All that is pure
conjecture though...

To give you all some context (in case you don't know), jXLS does
population into Excel template files via POI using a modified
Expression Language for marking up the template.

If anybody has any thoughts on what might be going on are appreciated.
 The original description of the problem are below.

Thanks,
-michael

---------------

I have the following in a row:

${period.startDate} | $[COUNT('myReport.xls'!Period1)] |
$[QUARTILE('myReport.xls'!Period1,1)] | $[H2-F2] |
$[QUARTILE('myReport.xls'!Period1,3)]

for reference, period is a collection and I would REALLY like to be doing:

$[COUNT('myReport.xls'!Period${period.startDate.time)]

instead of the Period1 stuff...that would allow me to access a
"dynamically named" named range, which is my holy grail here. so
there's my first problem, does anyone know if there is a way to
accomplish that? when I do it as above it complains about not being
able to parse the formula.

More importantly, the references to named ranges are not working as I
would expect. They end up output like:

1-Jan | 0.00 | #REF! | #REF! | #REF!

looking at what is inside the cells, I see:

1/1/2007 | =COUNT(#REF!B1) | =QUARTILE(#REF!B1,1) | =H2-F2 |
=QUARTILE(#REF!B1,3)

So, the first and 3rd cells are correct, but the ones referencing
named ranges are not.

Furthermore, I have a different cell that references a different named
range and it exhibits similar behavior, but instead of the B1 I get a
HL1:

=AVERAGE(#REF!HL1)

I should note that this even occurs when the named ranges refer to the
exact same range (same definition).

Not sure if this matters, but the named ranges reference cells on a
different sheet: ='Claim Data'!$G$2:$G$11

The G2 cell included in that reference contains a collection
reference: ${item.numberOfDays}

I don't need the G2 to expand with the collection or anything, as my
intention is to dynamically define those named ranges and set them via
POI...the only reason I even mention that part is I thought it may
play a role in all this.

Does anyone have any suggestions for me on these? Any help is greatly
appreciated.

-michael

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org