Return-Path: Delivered-To: apmail-jakarta-poi-user-archive@www.apache.org Received: (qmail 12970 invoked from network); 19 Apr 2006 13:54:59 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 19 Apr 2006 13:54:59 -0000 Received: (qmail 24349 invoked by uid 500); 19 Apr 2006 13:54:53 -0000 Delivered-To: apmail-jakarta-poi-user-archive@jakarta.apache.org Received: (qmail 24216 invoked by uid 500); 19 Apr 2006 13:54:52 -0000 Mailing-List: contact poi-user-help@jakarta.apache.org; run by ezmlm Precedence: bulk List-Unsubscribe: List-Help: List-Post: List-Id: "POI Users List" Reply-To: "POI Users List" Delivered-To: mailing list poi-user@jakarta.apache.org Received: (qmail 24094 invoked by uid 99); 19 Apr 2006 13:54:52 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 19 Apr 2006 06:54:52 -0700 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests=HTML_MESSAGE,UNPARSEABLE_RELAY X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: local policy) Received: from [216.221.240.217] (HELO premierinc.com) (216.221.240.217) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 19 Apr 2006 06:54:51 -0700 Received: from ([10.32.2.81]) by c3smtp04.premierinc.com with ESMTP id 4028744.42516343; Wed, 19 Apr 2006 09:54:07 -0400 Received: from C3EXCHANGE.corp.premierinc.com ([10.32.2.83]) by c3piexchfe2.corp.premierinc.com with Microsoft SMTPSVC(6.0.3790.1830); Wed, 19 Apr 2006 09:54:07 -0400 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Subject: Named Range / Formula Date: Wed, 19 Apr 2006 09:54:06 -0400 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Named Range / Formula Thread-Index: AcZjuLmu9krv4kJXRMWl6ATgT5Ooog== From: "Thompson, Marshall" To: X-OriginalArrivalTime: 19 Apr 2006 13:54:07.0277 (UTC) FILETIME=[BA06ADD0:01C663B8] Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C663B8.B988AFDC" X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N ------_=_NextPart_001_01C663B8.B988AFDC Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable In the excel UI, I can manually create a named range with the formula:=0D= =0A=3DOFFSET(Sheet1!$B$30,,,1,COUNTA(Sheet1!$B$30:$L$30))=0D=0AThis lets my= range refer to any cells in row 30 between B and L that=0D=0Ahave data=2E= =0D=0A=0D=0AI am generating a spreadsheet with Java / POI=2E I would like = to do the=0D=0Asame thing programattically=2E The user will use my code to= extract a=0D=0Aspreadsheet from the database on demand=2E They will have = excel charts=0D=0Apointing at the extracted spreadsheet=2E The data from o= ne extract to the=0D=0Anext may appear at a different location on the extra= cted spreadsheet=2E=0D=0AThe charts refer to named ranges, so it doesn't ma= tter that the data has=0D=0Amoved, as long as I can create a named range po= inting at the appropriate=0D=0Alocation=2E=0D=0A=0D=0AThus, I need to be ab= le to create a named range with a formula as=0D=0Aindicated above=2E=0D=0A= =0D=0AI can create named ranges -- this works:=0D=0A=0D=0A HSSFName = testName =3D pWorkbook=2EcreateName();=0D=0A testName=2EsetNameName(= "thenamedrange");=0D=0A testName=2EsetReference("Data!$H$26:$K$26");= =0D=0A=0D=0ABut, I cannot create them with a formula -- this fails:=0D=0A= =0D=0A HSSFName labels =3D pWorkbook=2EcreateName();=0D=0A la= bels=2EsetNameName("labels");=0D=0A =0D=0Alabels=2EsetReference("OFFSET(She= et1!$H$25,,,1,COUNTA(Sheet1!$H$25:$K$25))=0D=0A"); <=3D=3Dfails on this li= ne=0D=0A=0D=0AError is:=0D=0Ajava=2Elang=2ENumberFormatException: For input= string:=0D=0A"25,,,1,COUNTA(Sheet1!$H$25"=0D=0A at=0D=0Ajava=2Elang=2ENumb= erFormatException=2EforInputString(NumberFormatException=2Ejav=0D=0Aa:48)= =0D=0A at java=2Elang=2EInteger=2EparseInt(Integer=2Ejava:477)=0D=0A at jav= a=2Elang=2EInteger=2EparseInt(Integer=2Ejava:518)=0D=0A at=0D=0Aorg=2Eapach= e=2Epoi=2Ehssf=2Eutil=2ECellReference=2E(CellReference=2Ejava:48)=0D= =0A at=0D=0Aorg=2Eapache=2Epoi=2Ehssf=2Eutil=2EAreaReference=2E(AreaR= eference=2Ejava:33)=0D=0A at=0D=0Aorg=2Eapache=2Epoi=2Ehssf=2Erecord=2Eform= ula=2EArea3DPtg=2EsetArea(Area3DPtg=2Ejava:243)=0D=0A at=0D=0Aorg=2Eapache= =2Epoi=2Ehssf=2Erecord=2ENameRecord=2EsetAreaReference(NameRecord=2Ejava:7= =0D=0A10)=0D=0A at=0D=0Aorg=2Eapache=2Epoi=2Ehssf=2Eusermodel=2EHSSFName=2E= setReference(HSSFName=2Ejava:125)=0D=0A at=0D=0Acom=2Epremierinc=2Emetrics= =2Eweb=2EExcelView=2EbuildWorkbookContents(ExcelView=2Ejav=0D=0Aa:249)=0D= =0A=0D=0AI have examined the API, I am afraid that I cannot do what I need = to do=2E=0D=0AHopefully, I have missed something=2E Any help is greatly ap= preciated=2E=0D=0A=0D=0AMarshall B Thompson=0D=0APremier, Web Solutions=0D= =0A=0D=0A=0D=0A=0D=0A-----------------------------------------=0D=0A***Note= :The information contained in this message may be privileged=0D=0Aand confi= dential and protected from disclosure=2E If the reader of=0D=0Athis message= is not the intended recipient, or an employee or agent=0D=0Aresponsible fo= r delivering this message to the intended recipient,=0D=0Ayou are hereby no= tified that any dissemination, distribution or=0D=0Acopying of this communi= cation is strictly prohibited=2E If you have=0D=0Areceived this communicati= on in error, please notify the Sender=0D=0Aimmediately by replying to the m= essage and deleting it from your=0D=0Acomputer=2E Thank you=2E Premier Inc= =2E=0D=0A ------_=_NextPart_001_01C663B8.B988AFDC--