poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thompson, Marshall" <Marshall_Thomp...@PremierInc.com>
Subject Named Range / Formula
Date Wed, 19 Apr 2006 13:54:06 GMT
In the excel UI, I can manually create a named range with the formula:
=OFFSET(Sheet1!$B$30,,,1,COUNTA(Sheet1!$B$30:$L$30))
This lets my range refer to any cells in row 30 between B and L that
have data.

I am generating a spreadsheet with Java / POI.  I would like to do the
same thing programattically.  The user will use my code to extract a
spreadsheet from the database on demand.  They will have excel charts
pointing at the extracted spreadsheet.  The data from one extract to the
next may appear at a different location on the extracted spreadsheet.
The charts refer to named ranges, so it doesn't matter that the data has
moved, as long as I can create a named range pointing at the appropriate
location.

Thus, I need to be able to create a named range with a formula as
indicated above.

I can create named ranges -- this works:

        HSSFName testName = pWorkbook.createName();
        testName.setNameName("thenamedrange");
        testName.setReference("Data!$H$26:$K$26");

But, I cannot create them with a formula -- this fails:

        HSSFName labels = pWorkbook.createName();
        labels.setNameName("labels");
 
labels.setReference("OFFSET(Sheet1!$H$25,,,1,COUNTA(Sheet1!$H$25:$K$25))
");  <==fails on this line

Error is:
java.lang.NumberFormatException: For input string:
"25,,,1,COUNTA(Sheet1!$H$25"
	at
java.lang.NumberFormatException.forInputString(NumberFormatException.jav
a:48)
	at java.lang.Integer.parseInt(Integer.java:477)
	at java.lang.Integer.parseInt(Integer.java:518)
	at
org.apache.poi.hssf.util.CellReference.<init>(CellReference.java:48)
	at
org.apache.poi.hssf.util.AreaReference.<init>(AreaReference.java:33)
	at
org.apache.poi.hssf.record.formula.Area3DPtg.setArea(Area3DPtg.java:243)
	at
org.apache.poi.hssf.record.NameRecord.setAreaReference(NameRecord.java:7
10)
	at
org.apache.poi.hssf.usermodel.HSSFName.setReference(HSSFName.java:125)
	at
com.premierinc.metrics.web.ExcelView.buildWorkbookContents(ExcelView.jav
a:249)

I have examined the API, I am afraid that I cannot do what I need to do.
Hopefully, I have missed something.  Any help is greatly appreciated.

Marshall B Thompson
Premier, Web Solutions



-----------------------------------------
***Note:The information contained in this message may be privileged
and confidential and protected from disclosure. If the reader of
this message is not the intended recipient, or an employee or agent
responsible for delivering this message to the intended recipient,
you are hereby notified that any dissemination, distribution or
copying of this communication is strictly prohibited. If you have
received this communication in error, please notify the Sender
immediately by replying to the message and deleting it from your
computer. Thank you. Premier Inc.

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message