You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Andrew Feller <an...@tracesecurity.com> on 2009/12/14 21:19:00 UTC
NPE while retrieving / creating rows within spreadsheet
Hello,
I am using POI 3.0.1 to dynamically populate an Excel spreadsheet, however I continually encounter NullPointerException whenever I try to retrieve or create rows at the end of the document. Apparently, it doesn’t see any more rows “defined” and thus returns NPE.
QUESTION: Is it possible to tell POI to continually retrieve / create rows regardless of being touched (formatting, borders, etc)?
Thank you,
Andrew R. Feller
Software Engineer
TraceSecurity, Inc.
5615 Corporate Blvd Ste 200A
Baton Rouge, LA 70808
Phone: (225) 612-2121 X 31004
Fax: (225) 612-2115
Re: NPE while retrieving / creating rows within spreadsheet
Posted by MSB <ma...@tiscali.co.uk>.
Morning Anrdew,
Can we see some of the sort of code you are using please because I am not at
all certain I understand what you are asking here?
To deal with your second question firstly, if you are adding rows to the end
of an existing worksheet, the idiom I would adopt (as if that is any
recommendation!) is this;
...
row = worksheet.getRow(10);
if(row == null) {
row = worksheet.createRow(10);
}
.....
that way should deal with the situation where you try to retrieve a row that
did not exist in the original file so you lazilly create it and add it to
the worksheet; and of course I am assuming that the worksheet file exists
and that you have already opened it and retrieved a worksheet from it.
The reason, I suspect, ou have encountered this problem is that the Excel
file will only contain records for rows that have been populated. Therefore,
if you try to recover row seven for example even if rows six and eight have
been defined it may not exist if the cells on that row are empty. The way I
like to think about it all is MVC, the model view controller pattern. The
Excel file is the model and Excel itself is both the view and the
controller; the view does not need the file to contain records for every
cell on every row, it is able to create the display independant of the model
and simply calls upon that element for the data it needs and the formatting
information it requires.
Yours
Mark B
Andrew Feller-2 wrote:
>
> Hello,
>
>
>
> I am using POI 3.0.1 to dynamically populate an Excel spreadsheet, however
> I continually encounter NullPointerException whenever I try to retrieve or
> create rows at the end of the document. Apparently, it doesn’t see any
> more rows “defined” and thus returns NPE.
>
>
>
> QUESTION: Is it possible to tell POI to continually retrieve / create rows
> regardless of being touched (formatting, borders, etc)?
>
>
>
> Thank you,
>
>
>
> Andrew R. Feller
> Software Engineer
> TraceSecurity, Inc.
> 5615 Corporate Blvd Ste 200A
> Baton Rouge, LA 70808
> Phone: (225) 612-2121 X 31004
> Fax: (225) 612-2115
>
>
>
>
--
View this message in context: http://old.nabble.com/NPE-while-retrieving---creating-rows-within-spreadsheet-tp26784101p26790762.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: NPE while retrieving / creating rows within spreadsheet
Posted by MSB <ma...@tiscali.co.uk>.
....and I should have said that you can do something similar with cells, try
to get the cell from the row, check what the call returns and if it is null
lazilly create the cell. Just to be a PTBO (a Professor of The B&**%in
Obvious (Copyright Bob Crowe 1996)) if that solves your problems, there is
no need to post your code here.
Yours
Mark B
Andrew Feller-2 wrote:
>
> Hello,
>
>
>
> I am using POI 3.0.1 to dynamically populate an Excel spreadsheet, however
> I continually encounter NullPointerException whenever I try to retrieve or
> create rows at the end of the document. Apparently, it doesn’t see any
> more rows “defined” and thus returns NPE.
>
>
>
> QUESTION: Is it possible to tell POI to continually retrieve / create rows
> regardless of being touched (formatting, borders, etc)?
>
>
>
> Thank you,
>
>
>
> Andrew R. Feller
> Software Engineer
> TraceSecurity, Inc.
> 5615 Corporate Blvd Ste 200A
> Baton Rouge, LA 70808
> Phone: (225) 612-2121 X 31004
> Fax: (225) 612-2115
>
>
>
>
--
View this message in context: http://old.nabble.com/NPE-while-retrieving---creating-rows-within-spreadsheet-tp26784101p26790989.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: NPE while retrieving / creating rows within spreadsheet
Posted by MSB <ma...@tiscali.co.uk>.
Hello again Andrew,
As a general 'rule of thumb', I think it is safe to say that if you are
using POI to create a file from scratch, then you will always be creating
new sheets/rows/cell. On the other hand, if you are opening and modifying an
existing file then you do need to write code that checks to make sure it has
been possible to get an object and if not to lazilly create that object. So
when you try to get a sheet/row/cell, you can use that general pattern that
I outlined in my previous reply; check the returned value from the getXXX()
method call and create the object if it is equal to null.
Yours
Mark B
Andrew Feller-2 wrote:
>
> Morning Mark,
>
> Thank you for your reply.
>
> My code was strictly using the getRow() method though I was aware of
> createRow(). As an experiment, I switched all getRow() to createRow() and
> the problem persisted; I thought perhaps createRow() was intelligent
> enough to attempt to retrieve the row via getRow() first and only create
> it if necessary. I must admit I was quite confused when the Javadocs
> continually explained it only retrieves "defined" rows with no mention of
> how to define a row. I include a sample at the bottom.
>
> I will see about making the changes you suggested and give it a whirl. I
> appreciate your help. It would be nice if either A) getRow() had an
> alternative signature getRow(Boolean create) or B) createRow() would
> implicitly check for existing rows before creating new ones.
>
> Regards,
> Andrew
>
> SAMPLE CODE
>
> InputStream xls = new FileInputStream(SPREADSHEET_SOURCE);
> HSSFWorkbook wb = new HSSFWorkbook(xls);
> HSSFSheet sheet = wb.getSheetAt(0);
>
> int rowNumber = 2;
>
> List<People> people = PeopleUtils.fullList();
>
> for (People person : people) {
> HSSFRow row = sheet.getRow(rowNumber);
> FillNameDetails(wb, people, row);
> FillAddressDetails(wb, people, row);
> rowNumber++;
> }
>
>
> Andrew R. Feller
> Software Engineer
> TraceSecurity, Inc.
> 5615 Corporate Blvd Ste 200A
> Baton Rouge, LA 70808
> Phone: (225) 612-2121 X 31004
> Fax: (225) 612-2115
>
>
> -----Original Message-----
> From: MSB [mailto:markbrdsly@tiscali.co.uk]
> Sent: Tuesday, December 15, 2009 1:56 AM
> To: user@poi.apache.org
> Subject: Re: NPE while retrieving / creating rows within spreadsheet
>
>
> ....and I should have said that you can do something similar with cells,
> try
> to get the cell from the row, check what the call returns and if it is
> null
> lazilly create the cell. Just to be a PTBO (a Professor of The B&**%in
> Obvious (Copyright Bob Crowe 1996)) if that solves your problems, there is
> no need to post your code here.
>
> Yours
>
> Mark B
>
>
> Andrew Feller-2 wrote:
>>
>> Hello,
>>
>>
>>
>> I am using POI 3.0.1 to dynamically populate an Excel spreadsheet,
>> however
>> I continually encounter NullPointerException whenever I try to retrieve
>> or
>> create rows at the end of the document. Apparently, it doesn’t see any
>> more rows “defined” and thus returns NPE.
>>
>>
>>
>> QUESTION: Is it possible to tell POI to continually retrieve / create
>> rows
>> regardless of being touched (formatting, borders, etc)?
>>
>>
>>
>> Thank you,
>>
>>
>>
>> Andrew R. Feller
>> Software Engineer
>> TraceSecurity, Inc.
>> 5615 Corporate Blvd Ste 200A
>> Baton Rouge, LA 70808
>> Phone: (225) 612-2121 X 31004
>> Fax: (225) 612-2115
>>
>>
>>
>>
>
> --
> View this message in context:
> http://old.nabble.com/NPE-while-retrieving---creating-rows-within-spreadsheet-tp26784101p26790989.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://old.nabble.com/NPE-while-retrieving---creating-rows-within-spreadsheet-tp26784101p26797901.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org