Named range
Hey everybody! It´s even more fun now with my named range problem. When I give the sheets these names: mWb.setSheetName(0, first sheet); mWb.setSheetName(1, second sheet); mWb.setSheetName(2, third sheet); It´s no problem. But when I give them these names or any other: mWb.setSheetName(0, tech sheet); mWb.setSheetName(1, log sheet); mWb.setSheetName(2, com sheet); I get the message: Name darf integriertem Namen nicht gleichen (Name may not equal integrated name) Alter Titel: Drucktitel Neuer Titel: ... Any ideas?
named range
Hi everybody! I have the following problem: I have a workbook with two sheets. Now I would like to have repeating rows for both of them like this: mWb.setRepeatingRowsAndColumns(0, -1, -1, 0, 2); mWb.setRepeatingRowsAndColumns(1, -1, -1, 0, 2); When I open the generated workbook I get this message: Name darf integriertem Namen nicht gleichen It seems like the named range for the print title is injured. How can I solve this problem? Best regards, Philipp sdm - 25 Jahre Kompetenz im Software-Engineering -- Philipp Landmann mailto:[EMAIL PROTECTED] sdm AG http://www.sdm.de software design management Carl-Wery-Str. 42, 81739 München Tel. +49 89 63812-xxx, Fax -490 Vorstand: Edmund Kuepper (Vorsitzender), Burkhard Kehrbusch, Ruediger Azone, Dr. Uwe Dumslaff, Kai Grambow, Dr. Michael Rading Aufsichtsrat: Pierre Hessler (Vorsitzender) Sitz und Amtsgericht: Muenchen HRB 126057
Named Range / Formula
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.
AW: How to modify cells using the named range
Hello! Thanks for the reply! I tried the following code using poi 1.10.0dev: HSSFName myName = wb.getNameAt(wb.getNameIndex(Test)); cat.debug(HSSFName= + myName.toString()); String ref = myName.getReference(); cat.debug(String of reference= + ref); AreaReference myAreaReference = new AreaReference(ref); CellReference[] myCellReference = myAreaReference.getCells(); HSSFSheet mySheet = wb.getSheetAt(0); cat.debug(Length of CellReference= + myCellReference.length); HSSFRow myRow = mySheet.getRow(myCellReference[0].getRow()); HSSFCell myCell = myRow.getCell((short) myCellReference[0].getCol()); myCell.setCellValue(Test); When the program executes I get the following output: [EMAIL PROTECTED] String of reference=Tabelle1!$B$5 and an Exception occurs getting the AreaReference: java.lang.StringIndexOutOfBoundsException: String index out of range: -1 I think the reference String looks quite nice. Where's the fault? Any help is welcome! Regards, Arne -Ursprüngliche Nachricht- Von: Danny Mui [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 25. März 2003 19:04 An: POI Users List Betreff: Re: How to modify cells using the named range That method simply gives you a HSSFName object that gives you the formula-based reference (i.e Sheet1!A2:A5). You can use: http://jakarta.apache.org/poi/javadocs/org/apache/poi/hssf/uti l/AreaReference.html |AreaReference ref = new AreaReference (name.getReference()); That will get you your cell indices, albeit the long way. Maybe this should be easier? danny | Clauss, Arne wrote: Hello! I would like to modify an excel-file in an web-application. Because the design of the excel-file could be changed I would like to use the named ranges for putting the information into the excel-file. I think, that this make me independent of design changes (adding new rows an columns). I just want to get a cell for modifying it by the using the named range of the cell to find it. I have found in the class HSSFWorkbook the method getNameIndex. What should I do with the returned index. I haven't found a method for getting a HSSFCell with the returned index. Any help is appreciated. Kind regards, Arne - 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] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
