Also getting Coding Error: Expected ExpPtg to be converted from Shared to Non-Shared Formula exception
I would have responded to the original chain on this, but I already deleted that from my email. I just noticed a new exception in my POI-based application: Coding Error: Expected ExpPtg to be converted from Shared to Non-Shared Formula I tried downloading the latest release (the ftp site says 3.0-FINAL, but the zip file says 3.0-rc4), and I still get this. Failing a quick fix in POI for this, what exactly does this error mean, and is there something I can change in my spreadsheet to work around this problem? I need to fix this pretty quickly (today). - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: How to get String result from a cell with a formula behind
I'm new at this also, but you should probably use the org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator class from the scratchpad jar. -Original Message- From: ChrisHauser [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 04, 2007 8:49 AM To: poi-user@jakarta.apache.org Subject: How to get String result from a cell with a formula behind Trying to retrieve the result of a cell where I have the following formula: =SUBTOTAL(9,P4:P27) I have tried the following methods: HSSFCell.getNumericCellValue() HSSFCell.getRichStringCellValue().getString() -- returns empty string All these results do not match to what I have in my MSExcel spread sheet. Any help is greatly appreciated. Chris -- View this message in context: http://www.nabble.com/How-to-get-String-result-from-a-cell-wit h-a-formula-behind-tf3526392.html#a9839101 Sent from the POI - User mailing list archive at Nabble.com. - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: empty cells with iterators
Perhaps you're looking to check whether the cell type is blank. Once you have a HSSFCell object, call getCellType() and compare it against HSSFCell.CELL_TYPE_BLANK. -Original Message- From: Hugo Osorio [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 8:50 AM To: poi-user@jakarta.apache.org Subject: empty cells with iterators Hello, How can i ask in the statement while(cellsIterator.hasNext()) AND (some cell is not empty) since this cell is my control for registering thank you - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: Get column index by specifying Excel column index (i.e.C for col C)
You could give these a try. I didn't find this functionality in POI. private int colNameToNum(final String colName) { int result = 0; String lcColName = colName.toLowerCase(); for (int ctr = 0; ctr lcColName.length(); ++ ctr) result = (result * 26) + (lcColName.charAt(ctr) - 'a' + 1); -- result; return (result); } private String colNumToName(int colNum) { StringBuffersb = new StringBuffer(); int cycleNum= colNum / 26; int withinNum = colNum - (cycleNum * 26); if (cycleNum 0) sb.append((char) ((cycleNum - 1) + 'a')); sb.append((char) (withinNum + 'a')); return (sb.toString()); } -Original Message- From: ChrisHauser [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 9:52 AM To: poi-user@jakarta.apache.org Subject: Get column index by specifying Excel column index (i.e.C for col C) I'm working on a method where I want to pass the MS Excel column identifier (i.e.C or AH) and want to retrieve the according numerical index. I.e. 1 for A or 34 for AH. Was looking through all the objects (i.e. HSSFSheet and others) but coul not find this convertion. Any help is greatly appreciated. Chris -- View this message in context: http://www.nabble.com/Get-column-index-by-specifying-Excel-col umn-index-%28i.e.%22C%22-for-col-C%29-tf3517898.html#a9818468 Sent from the POI - User mailing list archive at Nabble.com. - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: empty cells with iterators
How do you know the cell is blank? Because you're going by the row/column number you think you're looking at? Remember that the row/column numbers are 0-based, not 1-based. Because of issues like this, I like to have a dumpCellData method that goes through every row and column, printing out the row/column numbers, the cell type, and the string value of the cell. -Original Message- From: Hugo Osorio [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 12:48 PM To: POI Users List Subject: Re: empty cells with iterators thank you, i have used this field, but, the cell seems not to be blank, but it still appears to have data despite it is empty... 2007/4/3, Karr, David [EMAIL PROTECTED]: Perhaps you're looking to check whether the cell type is blank. Once you have a HSSFCell object, call getCellType() and compare it against HSSFCell.CELL_TYPE_BLANK. -Original Message- From: Hugo Osorio [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 03, 2007 8:50 AM To: poi-user@jakarta.apache.org Subject: empty cells with iterators Hello, How can i ask in the statement while(cellsIterator.hasNext()) AND (some cell is not empty) since this cell is my control for registering thank you - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: Getting class file has wrong version error on poi-scratchpad-3.0-rc2 jar
In the real world, upgrading platforms is not something individuals can control. If it were up to me, I would be running close to the edge all the time. I wouldn't be surprised if I'm still doing 1.4.2 work at the end of this year. -Original Message- From: Andrew C. Oliver [mailto:[EMAIL PROTECTED] Sent: Friday, March 30, 2007 4:55 AM To: POI Users List Subject: Re: Getting class file has wrong version error on poi-scratchpad-3.0-rc2 jar Why exactly? Running a JDK that is 2 versions back tends to be a rather large faux pas as well. Is there still a platform that 1.5 isn't available for? Karr, David wrote: I assume you're only half serious. Dropping 1.4.2 at this point would be a big mistake. In any case, I tried manually building the jars from source with JDK 1.4.2, and it works fine. -Original Message- From: Andrew C. Oliver [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 12:47 PM To: POI Users List Subject: Re: Getting class file has wrong version error on poi-scratchpad-3.0-rc2 jar yeah unless we've dropped 1.42 then it should be compiled on 1.42. So the last build is invalid or we need a vote to drop 1.42 and req 5+ -andy David Fisher wrote: That class file was compiled with JDK 5.0 See http://blogs.sun.com/sundararajan/entry/java_class_ic_errors On Mar 29, 2007, at 2:25 PM, Karr, David wrote: Obviously, I was wrong to refer to this as the scratchpad jar. It's the main poi jar. -Original Message- From: Karr, David Sent: Thursday, March 29, 2007 12:06 PM To: POI Users List Subject: Getting class file has wrong version error on poi-scratchpad-3.0-rc2 jar I'm trying to use 3.0rc2, and I'm trying to use the scratchpad jar for the first time. I'm using JDK 1.4.2. When I try to compile my code with Ant, I get the following: bad class file: ...\lib\poi-3.0-rc2-20070329.jar(org/apache/poi/hssf/model/Wor kbook.clas s) class file has wrong version 49.0, should be 48.0 Please remove or make sure it appears in the correct subdirectory of the classpath. import org.apache.poi.hssf.model.Workbook; - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ -- From Windows/Exchange to Linux/Meldware Buni Meldware Communication Suite Email, Calendaring, ease of configuration/administration http://buni.org - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ -- From Windows/Exchange to Linux/Meldware Buni Meldware Communication Suite Email, Calendaring, ease of configuration/administration http://buni.org - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
Why does POI javadoc not include scratchpad classes?
I noticed that the javadoc for POI does not include the scratchpad classes. Is this intentional? Is the Javadoc for these classes provide somewhere else? - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
Getting class file has wrong version error on poi-scratchpad-3.0-rc2 jar
I'm trying to use 3.0rc2, and I'm trying to use the scratchpad jar for the first time. I'm using JDK 1.4.2. When I try to compile my code with Ant, I get the following: bad class file: ...\lib\poi-3.0-rc2-20070329.jar(org/apache/poi/hssf/model/Workbook.clas s) class file has wrong version 49.0, should be 48.0 Please remove or make sure it appears in the correct subdirectory of the classpath. import org.apache.poi.hssf.model.Workbook; - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: Getting class file has wrong version error on poi-scratchpad-3.0-rc2 jar
Obviously, I was wrong to refer to this as the scratchpad jar. It's the main poi jar. -Original Message- From: Karr, David Sent: Thursday, March 29, 2007 12:06 PM To: POI Users List Subject: Getting class file has wrong version error on poi-scratchpad-3.0-rc2 jar I'm trying to use 3.0rc2, and I'm trying to use the scratchpad jar for the first time. I'm using JDK 1.4.2. When I try to compile my code with Ant, I get the following: bad class file: ...\lib\poi-3.0-rc2-20070329.jar(org/apache/poi/hssf/model/Wor kbook.clas s) class file has wrong version 49.0, should be 48.0 Please remove or make sure it appears in the correct subdirectory of the classpath. import org.apache.poi.hssf.model.Workbook; - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
HSSFFormulaEvaluator.evaluate(cell) fails with NPE because row is not set
Is the formula evaluation code in the scratchpad supposed to work? I tried to evaluate a formula in a cell, but it gets the following exception: java.lang.NullPointerException at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.internalEvaluate(HSSF FormulaEvaluator.java:293) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaE valuator.java:192) I loaded up the source (using 3.0rc2), and it shows this is happening on the following line: int srcRowNum = srcRow.getRowNum(); Because srcRow is null. The calling method sends the row instance variable, which obviously isn't set. Am I supposed to call evaluator.setCurrentRow(row) before I call the evaluator on a cell in that row? The documentation I found for the evaluator doesn't mention this. If I'm supposed to do this, is there something else I need to call for the evaluator to work? - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
Formula Evaluator fails on #REF instead of number
Using 3.0rc2, the formula evaluator failed on a particular cell, with the stacktrace following this. Before I file an issue for this, is this expected to fail this way? Is there any workaround I can implement? Error: Integer Expected java.lang.RuntimeException: Cannot Parse, sorry : Integer Expected @ 28 [Formula String was: 'IF(D37=0,0,IF(AND(ISNUMBER(#REF!),ISNUMBER(D37)),#REF!/D37*100,0))'] at org.apache.poi.hssf.model.FormulaParser.Abort(FormulaParser.java:114) at org.apache.poi.hssf.model.FormulaParser.Expected(FormulaParser.java:121) at org.apache.poi.hssf.model.FormulaParser.GetNum(FormulaParser.java:255) at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:512) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:606) at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:65 4) at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:469 ) at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:341) at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:280) at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:505) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:606) at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:65 4) at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:469 ) at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:341) at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:280) at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:505) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:606) at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:65 4) at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:469 ) at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:341) at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:280) at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:505) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:606) at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:65 4) at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:479 ) at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:341) at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:280) at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:505) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:606) at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:65 4) at org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:758) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.internalEvaluate(HSSF FormulaEvaluator.java:296) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaE valuator.java:192) - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: Formula Evaluator fails on #REF instead of number
Never mind, we've changed the formula to something more reasonable, and it's able to process it. -Original Message- From: Karr, David Sent: Thursday, March 29, 2007 1:26 PM To: POI Users List Subject: Formula Evaluator fails on #REF instead of number Using 3.0rc2, the formula evaluator failed on a particular cell, with the stacktrace following this. Before I file an issue for this, is this expected to fail this way? Is there any workaround I can implement? Error: Integer Expected java.lang.RuntimeException: Cannot Parse, sorry : Integer Expected @ 28 [Formula String was: 'IF(D37=0,0,IF(AND(ISNUMBER(#REF!),ISNUMBER(D37)),#REF!/D37*100,0))'] at org.apache.poi.hssf.model.FormulaParser.Abort(FormulaParser.java:114) at org.apache.poi.hssf.model.FormulaParser.Expected(FormulaParser .java:121) at org.apache.poi.hssf.model.FormulaParser.GetNum(FormulaParser.java:255) at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:512) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:606) at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaPars er.java:65 4) at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParse r.java:469 ) at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser .java:341) at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:280) at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:505) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:606) at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaPars er.java:65 4) at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParse r.java:469 ) at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser .java:341) at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:280) at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:505) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:606) at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaPars er.java:65 4) at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParse r.java:469 ) at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser .java:341) at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:280) at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:505) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:606) at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaPars er.java:65 4) at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParse r.java:479 ) at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser .java:341) at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:280) at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:505) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:606) at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaPars er.java:65 4) at org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:758) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.internalEva luate(HSSF FormulaEvaluator.java:296) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HS SFFormulaE valuator.java:192) - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: Getting class file has wrong version error on poi-scratchpad-3.0-rc2 jar
I assume you're only half serious. Dropping 1.4.2 at this point would be a big mistake. In any case, I tried manually building the jars from source with JDK 1.4.2, and it works fine. -Original Message- From: Andrew C. Oliver [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 12:47 PM To: POI Users List Subject: Re: Getting class file has wrong version error on poi-scratchpad-3.0-rc2 jar yeah unless we've dropped 1.42 then it should be compiled on 1.42. So the last build is invalid or we need a vote to drop 1.42 and req 5+ -andy David Fisher wrote: That class file was compiled with JDK 5.0 See http://blogs.sun.com/sundararajan/entry/java_class_ic_errors On Mar 29, 2007, at 2:25 PM, Karr, David wrote: Obviously, I was wrong to refer to this as the scratchpad jar. It's the main poi jar. -Original Message- From: Karr, David Sent: Thursday, March 29, 2007 12:06 PM To: POI Users List Subject: Getting class file has wrong version error on poi-scratchpad-3.0-rc2 jar I'm trying to use 3.0rc2, and I'm trying to use the scratchpad jar for the first time. I'm using JDK 1.4.2. When I try to compile my code with Ant, I get the following: bad class file: ...\lib\poi-3.0-rc2-20070329.jar(org/apache/poi/hssf/model/Wor kbook.clas s) class file has wrong version 49.0, should be 48.0 Please remove or make sure it appears in the correct subdirectory of the classpath. import org.apache.poi.hssf.model.Workbook; - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ -- From Windows/Exchange to Linux/Meldware Buni Meldware Communication Suite Email, Calendaring, ease of configuration/administration http://buni.org - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
POI251: Worked fine reading one spreadsheet, new one throws RecordFormatException creating workbook
I have an app using POI 2.5.1 that reads a spreadsheet and generates some other files from it (in text files). I had it working with one version of the spreadsheet. I now have a new version of the spreadsheet that is similar, but slightly different. I need to update the app to use the new spreadsheet. I use the following code to initially load the spreadsheet: POIFSFileSystem fileSystem = new POIFSFileSystem(new FileInputStream(getExcelInputFile())); HSSFWorkbookworkbook= new HSSFWorkbook(fileSystem); HSSFSheet worksheet = workbook.getSheet(getWorksheetName()); When I run my app with the new spreadsheet, the creation of the HSSFWorkbook fails with the exception info that follows this. Again, the previous version of the spreadsheet worked fine here. What could be wrong here? java.lang.reflect.InvocationTargetException at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorA ccessorImpl.java:39) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingCons tructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:274) at org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFactory.java :224) at org.apache.poi.hssf.record.RecordFactory.createRecords(RecordFactory.jav a:160) at org.apache.poi.hssf.usermodel.HSSFWorkbook.init(HSSFWorkbook.java:163) at org.apache.poi.hssf.usermodel.HSSFWorkbook.init(HSSFWorkbook.java:130) at com.wamu.uia.riskrating.genmessages.GenRiskRatingMessageFiles.go(GenRisk RatingMessageFiles.java:282) at com.wamu.uia.riskrating.genmessages.GenRiskRatingMessageFiles.main(GenRi skRatingMessageFiles.java:221) Caused by: java.lang.ArrayIndexOutOfBoundsException at java.lang.System.arraycopy(Native Method) at org.apache.poi.hssf.record.UnknownRecord.init(UnknownRecord.java:62) at org.apache.poi.hssf.record.SubRecord.createSubRecord(SubRecord.java:57) at org.apache.poi.hssf.record.ObjRecord.fillFields(ObjRecord.java:99) at org.apache.poi.hssf.record.Record.fillFields(Record.java:90) at org.apache.poi.hssf.record.Record.init(Record.java:55) at org.apache.poi.hssf.record.ObjRecord.init(ObjRecord.java:61) ... 10 more org.apache.poi.hssf.record.RecordFormatException: Unable to construct record instance, the following exception occured: null at org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFactory.java :237) at org.apache.poi.hssf.record.RecordFactory.createRecords(RecordFactory.jav a:160) at org.apache.poi.hssf.usermodel.HSSFWorkbook.init(HSSFWorkbook.java:163) at org.apache.poi.hssf.usermodel.HSSFWorkbook.init(HSSFWorkbook.java:130) at com.wamu.uia.riskrating.genmessages.GenRiskRatingMessageFiles.go(GenRisk RatingMessageFiles.java:282) at com.wamu.uia.riskrating.genmessages.GenRiskRatingMessageFiles.main(GenRi skRatingMessageFiles.java:221) - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: How to read just one sheet out of a large excel file?
The options -Xms -Xmx are intended to be supplied with actual memory values. Specifying -Xmx by itself doesn't do any good. You might try -Xmx1024m. -Original Message- From: Matthias Knapp [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 27, 2007 2:54 AM To: poi-user@jakarta.apache.org Subject: How to read just one sheet out of a large excel file? Hi, I´d like to read and write within an existing excel file (10 MB) via HSSF, but I always encounter an OutOfMemoryError (not enough heap space). The options java -Xms -Xmx don´t work. So my question is, if there is any possibility to read (and write) only one sheet out of the whole excel file to avoid the heap space problem? I only need to change data in one sheet, so the other sheets (main part of the 10 MB) don´t interest. Please anyone suggest me how to do this thanks and rgds Matthias - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: POI251: Worked fine reading one spreadsheet, new one throws RecordFormatException creating workbook
If it matters, the only thing I know of that's different in this version of the spreadsheet is that there are some cells with dropdown values. The previous version did not do this. I find it hard to believe this could possibly cause the HSSFWorkbook load to fail, but that is the only difference that I'm aware of (besides simple differences like column names and content). -Original Message- From: Karr, David Sent: Tuesday, March 27, 2007 3:23 PM To: POI Users List Subject: POI251: Worked fine reading one spreadsheet, new one throws RecordFormatException creating workbook I have an app using POI 2.5.1 that reads a spreadsheet and generates some other files from it (in text files). I had it working with one version of the spreadsheet. I now have a new version of the spreadsheet that is similar, but slightly different. I need to update the app to use the new spreadsheet. I use the following code to initially load the spreadsheet: POIFSFileSystem fileSystem = new POIFSFileSystem(new FileInputStream(getExcelInputFile())); HSSFWorkbookworkbook= new HSSFWorkbook(fileSystem); HSSFSheet worksheet = workbook.getSheet(getWorksheetName()); When I run my app with the new spreadsheet, the creation of the HSSFWorkbook fails with the exception info that follows this. Again, the previous version of the spreadsheet worked fine here. What could be wrong here? java.lang.reflect.InvocationTargetException at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeCo nstructorA ccessorImpl.java:39) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Dele gatingCons tructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:274) at org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFa ctory.java :224) at org.apache.poi.hssf.record.RecordFactory.createRecords(RecordF actory.jav a:160) at org.apache.poi.hssf.usermodel.HSSFWorkbook.init(HSSFWorkbook .java:163) at org.apache.poi.hssf.usermodel.HSSFWorkbook.init(HSSFWorkbook .java:130) at com.wamu.uia.riskrating.genmessages.GenRiskRatingMessageFiles. go(GenRisk RatingMessageFiles.java:282) at com.wamu.uia.riskrating.genmessages.GenRiskRatingMessageFiles. main(GenRi skRatingMessageFiles.java:221) Caused by: java.lang.ArrayIndexOutOfBoundsException at java.lang.System.arraycopy(Native Method) at org.apache.poi.hssf.record.UnknownRecord.init(UnknownRecord.java:62) at org.apache.poi.hssf.record.SubRecord.createSubRecord(SubRecord .java:57) at org.apache.poi.hssf.record.ObjRecord.fillFields(ObjRecord.java:99) at org.apache.poi.hssf.record.Record.fillFields(Record.java:90) at org.apache.poi.hssf.record.Record.init(Record.java:55) at org.apache.poi.hssf.record.ObjRecord.init(ObjRecord.java:61) ... 10 more org.apache.poi.hssf.record.RecordFormatException: Unable to construct record instance, the following exception occured: null at org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFa ctory.java :237) at org.apache.poi.hssf.record.RecordFactory.createRecords(RecordF actory.jav a:160) at org.apache.poi.hssf.usermodel.HSSFWorkbook.init(HSSFWorkbook .java:163) at org.apache.poi.hssf.usermodel.HSSFWorkbook.init(HSSFWorkbook .java:130) at com.wamu.uia.riskrating.genmessages.GenRiskRatingMessageFiles. go(GenRisk RatingMessageFiles.java:282) at com.wamu.uia.riskrating.genmessages.GenRiskRatingMessageFiles. main(GenRi skRatingMessageFiles.java:221) - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: POI251: Worked fine reading one spreadsheet, new one throws RecordFormatException creating workbook
I noticed from a FAQ entry that this could happen if the spreadsheet uses Excel features not supported by POI. Is there anything I can look at in Excel that will give me a clue on this? -Original Message- From: Karr, David Sent: Tuesday, March 27, 2007 3:23 PM To: POI Users List Subject: POI251: Worked fine reading one spreadsheet, new one throws RecordFormatException creating workbook I have an app using POI 2.5.1 that reads a spreadsheet and generates some other files from it (in text files). I had it working with one version of the spreadsheet. I now have a new version of the spreadsheet that is similar, but slightly different. I need to update the app to use the new spreadsheet. I use the following code to initially load the spreadsheet: POIFSFileSystem fileSystem = new POIFSFileSystem(new FileInputStream(getExcelInputFile())); HSSFWorkbookworkbook= new HSSFWorkbook(fileSystem); HSSFSheet worksheet = workbook.getSheet(getWorksheetName()); When I run my app with the new spreadsheet, the creation of the HSSFWorkbook fails with the exception info that follows this. Again, the previous version of the spreadsheet worked fine here. What could be wrong here? java.lang.reflect.InvocationTargetException at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeCo nstructorA ccessorImpl.java:39) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Dele gatingCons tructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:274) at org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFa ctory.java :224) at org.apache.poi.hssf.record.RecordFactory.createRecords(RecordF actory.jav a:160) at org.apache.poi.hssf.usermodel.HSSFWorkbook.init(HSSFWorkbook .java:163) at org.apache.poi.hssf.usermodel.HSSFWorkbook.init(HSSFWorkbook .java:130) at com.wamu.uia.riskrating.genmessages.GenRiskRatingMessageFiles. go(GenRisk RatingMessageFiles.java:282) at com.wamu.uia.riskrating.genmessages.GenRiskRatingMessageFiles. main(GenRi skRatingMessageFiles.java:221) Caused by: java.lang.ArrayIndexOutOfBoundsException at java.lang.System.arraycopy(Native Method) at org.apache.poi.hssf.record.UnknownRecord.init(UnknownRecord.java:62) at org.apache.poi.hssf.record.SubRecord.createSubRecord(SubRecord .java:57) at org.apache.poi.hssf.record.ObjRecord.fillFields(ObjRecord.java:99) at org.apache.poi.hssf.record.Record.fillFields(Record.java:90) at org.apache.poi.hssf.record.Record.init(Record.java:55) at org.apache.poi.hssf.record.ObjRecord.init(ObjRecord.java:61) ... 10 more org.apache.poi.hssf.record.RecordFormatException: Unable to construct record instance, the following exception occured: null at org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFa ctory.java :237) at org.apache.poi.hssf.record.RecordFactory.createRecords(RecordF actory.jav a:160) at org.apache.poi.hssf.usermodel.HSSFWorkbook.init(HSSFWorkbook .java:163) at org.apache.poi.hssf.usermodel.HSSFWorkbook.init(HSSFWorkbook .java:130) at com.wamu.uia.riskrating.genmessages.GenRiskRatingMessageFiles. go(GenRisk RatingMessageFiles.java:282) at com.wamu.uia.riskrating.genmessages.GenRiskRatingMessageFiles. main(GenRi skRatingMessageFiles.java:221) - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: POI251: Worked fine reading one spreadsheet, new one throws RecordFormatException creating workbook
Ok, well, that fixed it. I guess I'll have to use the rc for now. Thanks. -Original Message- From: David Fisher [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 27, 2007 4:10 PM To: POI Users List Subject: Re: POI251: Worked fine reading one spreadsheet, new one throws RecordFormatException creating workbook You might try the latest 3.0 RC1 at http://people.apache.org/~nick/ POI-3.0-RC1/ I know that bugs related to macros were repaired in the last couple of months. Regards, Dave On Mar 27, 2007, at 6:01 PM, Karr, David wrote: I noticed from a FAQ entry that this could happen if the spreadsheet uses Excel features not supported by POI. Is there anything I can look at in Excel that will give me a clue on this? -Original Message- From: Karr, David Sent: Tuesday, March 27, 2007 3:23 PM To: POI Users List Subject: POI251: Worked fine reading one spreadsheet, new one throws RecordFormatException creating workbook I have an app using POI 2.5.1 that reads a spreadsheet and generates some other files from it (in text files). I had it working with one version of the spreadsheet. I now have a new version of the spreadsheet that is similar, but slightly different. I need to update the app to use the new spreadsheet. I use the following code to initially load the spreadsheet: POIFSFileSystem fileSystem = new POIFSFileSystem(new FileInputStream(getExcelInputFile())); HSSFWorkbookworkbook= new HSSFWorkbook(fileSystem); HSSFSheet worksheet = workbook.getSheet(getWorksheetName()); When I run my app with the new spreadsheet, the creation of the HSSFWorkbook fails with the exception info that follows this. Again, the previous version of the spreadsheet worked fine here. What could be wrong here? java.lang.reflect.InvocationTargetException at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeCo nstructorA ccessorImpl.java:39) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Dele gatingCons tructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:274) at org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFa ctory.java :224) at org.apache.poi.hssf.record.RecordFactory.createRecords(RecordF actory.jav a:160) at org.apache.poi.hssf.usermodel.HSSFWorkbook.init(HSSFWorkbook .java:163) at org.apache.poi.hssf.usermodel.HSSFWorkbook.init(HSSFWorkbook .java:130) at com.wamu.uia.riskrating.genmessages.GenRiskRatingMessageFiles. go(GenRisk RatingMessageFiles.java:282) at com.wamu.uia.riskrating.genmessages.GenRiskRatingMessageFiles. main(GenRi skRatingMessageFiles.java:221) Caused by: java.lang.ArrayIndexOutOfBoundsException at java.lang.System.arraycopy(Native Method) at org.apache.poi.hssf.record.UnknownRecord.init(UnknownRecord.java: 62) at org.apache.poi.hssf.record.SubRecord.createSubRecord(SubRecord .java:57) at org.apache.poi.hssf.record.ObjRecord.fillFields(ObjRecord.java:99) at org.apache.poi.hssf.record.Record.fillFields(Record.java:90) at org.apache.poi.hssf.record.Record.init(Record.java:55) at org.apache.poi.hssf.record.ObjRecord.init(ObjRecord.java:61) ... 10 more org.apache.poi.hssf.record.RecordFormatException: Unable to construct record instance, the following exception occured: null at org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFa ctory.java :237) at org.apache.poi.hssf.record.RecordFactory.createRecords(RecordF actory.jav a:160) at org.apache.poi.hssf.usermodel.HSSFWorkbook.init(HSSFWorkbook .java:163) at org.apache.poi.hssf.usermodel.HSSFWorkbook.init(HSSFWorkbook .java:130) at com.wamu.uia.riskrating.genmessages.GenRiskRatingMessageFiles. go(GenRisk RatingMessageFiles.java:282) at com.wamu.uia.riskrating.genmessages.GenRiskRatingMessageFiles. main(GenRi skRatingMessageFiles.java:221) - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi
RE: How to determine what color a cell is from the style color index?
If anyone's wondering, I gave up on this approach. It's more effective to use merged regions. -Original Message- From: Karr, David Sent: Saturday, March 10, 2007 11:57 AM To: POI Users List Subject: How to determine what color a cell is from the style color index? I'm reading a spreadsheet, and I have a cell and it's cellstyle, so I can get the fillbg index. How do I tell what color that is? I can't even figure it out by looking at the Constant Field Values link in the javadoc. A cell that appears to be white shows a value of 65, and a cell that appears to be grey shows a value of 64. I don't even see those values as values of the index field in the constants page. - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
Confused about reading fill pattern
In my spreadsheet, I have a sample region of rows in a single column where the first row has the value A, and the cell has a white background. The rest of the cells in the column are grey, and are in a single merged region. So, when I click on the A cell, it focuses on just that cell. When I click in the cell just below it, it focuses on the entire merged region. When I parse this with HSSF, when it gets to the cell just below the cell with A (the top of the grey merged region), it says that the cell is BLANK, the fill pattern is 1, and the fill foreground index is 22. We'll call this cell ONE. When it gets to the cell below that, which is still in the grey merged region, seemingly identical to the cell above it, it says that the cell is BLANK, but the fill pattern is 0, and the fill foreground index is 64. We'll call this cell TWO. Why is this happening? Is there something special about the top cell in a merged region? I need to be able to tell that cells ONE and TWO are equivalent. - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
How to determine what color a cell is from the style color index?
I'm reading a spreadsheet, and I have a cell and it's cellstyle, so I can get the fillbg index. How do I tell what color that is? I can't even figure it out by looking at the Constant Field Values link in the javadoc. A cell that appears to be white shows a value of 65, and a cell that appears to be grey shows a value of 64. I don't even see those values as values of the index field in the constants page. - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
Problems getting ? out of Excel cell
I have a spreadsheet with cells that have values like ≤ and ≥. I guess these are unicode, but I'm not certain. When I read these values with HSSFCell.getStringCellValue(), it seems to corrupt those characters. Is there anything reasonable I can do about this? - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: Problems getting ? out of Excel cell
Never mind. This isn't a POI problem. I'm having trouble using Writer to write these characters out. It seems to corrupt these characters when I write them out, or perhaps I'm getting confused about file encodings. -Original Message- From: Karr, David Sent: Thursday, January 11, 2007 7:32 AM To: poi-user@jakarta.apache.org Subject: Problems getting ? out of Excel cell I have a spreadsheet with cells that have values like ≤ and ≥. I guess these are unicode, but I'm not certain. When I read these values with HSSFCell.getStringCellValue(), it seems to corrupt those characters. Is there anything reasonable I can do about this? - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
How to read single value from merged cells
I haven't used POI in a long time, and I need to throw something together pretty quickly to read specific data out of a somewhat complicated spreadsheet. In the spreadsheet, I'm going to need to read a value from each of many merged cell blocks. When I view the sheet in Excel, the value is at the top of the merged cell range. The cells after the cell in the range that has the value are all blank. If I have to, I can hardcode the starting cell number and the length of the range, but it would be great if POI can tell me how large the range is that the cell is in. I would like to know this because the next read I have to do is in the cell range that comes right after the first one (and so on). What classes/methods in POI can help me with this? - To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/