You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Prashant Dube <pd...@rocketmail.com> on 2004/06/26 08:06:24 UTC
Creating Excel File
Is there a limit to the number of lines in an excel file created using POI? I am getting a java.lang.OutOfMemoryError in a test that attempts to create an excel file having 2 sheets and 5000 rows of data with 50 columns in each row. Also is there a way to write the file in bits (i.e. execute wb.write(fout) method) to avoid memory loadup.
Below is the POI code that uses POI version 2.5.
File xlfile = new File("C:\temp\test.xls");
FileOutputStream fout = new FileOutputStream(xlfile);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet("sheet1");
for (int j=0;j<=5000;j++) {
row = sheet1.createRow(j);
for (int i=0;i<=50;i++) {
cell = row.createCell((short)i);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("Hello");
}
}
HSSFSheet sheet2 = wb.createSheet("sheet2");
for (int j=0;j<=5000;j++) {
row = sheet2.createRow(j);
for (int i=0;i<=50;i++) {
cell = row.createCell((short)i);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("World");
}
}
wb.write(fout);
fout.close();
Thanks
PD
Re: Creating Excel File
Posted by Davinder Kohli <kd...@yahoo.com>.
The product that I work on is called ConverterPro,
data migration tool that moves data across different
dbms. I am sure you can find more on this on the
internet.
So, to that end, I am moving this table from SQL
Server containing 1mil. rows to excel. As I also
suggested in my email, I was considering moving these
rows across to say 4 workbooks each containing 4
sheets.
--- Ryan Ackley <sa...@cfl.rr.com> wrote:
> Just out of curiousity, what are you doing that
> requires you to output 1
> million rows? It seems like it would be hard to find
> stuff in a workbook
> that has a million rows. I would think that it would
> be better to break them
> into smaller files named appropriately for a
> subcategory or something.
>
> -Ryan
>
> ----- Original Message -----
> From: "Davinder Kohli" <kd...@yahoo.com>
> To: "POI Users List" <po...@jakarta.apache.org>
> Sent: Monday, June 28, 2004 10:21 AM
> Subject: Re: Creating Excel File
>
>
> > An excel sheet can hold upto 65536 rows. This is
> limit
> > by MS Excel and not POI. If you need to move/write
> > rows > 65536, you may have to write them across
> > multiple sheets.
> >
> > To get rid of the out of memory problem, icrease
> your
> > JVM memory size to say 512M using -Xmx.
> >
> > In fact, I am running into the same problem,
> however,
> > in my case I am trying to move 1 million row table
> to
> > excel and there is so much I can do by increasing
> the
> > memory size.
> >
> > My question to HSSF gurus:
> > Can I append to an existing worksheet in a
> workbook
> > without reading in the entire workbook into the
> user
> > model?
> >
> > I am also thinking of storing those million rows
> > across multiple workbooks instead of having 16,17
> > workseets withing a single workbook.
> >
> > Thanks,
> > Davinder
> >
> > --- Prashant Dube <pd...@rocketmail.com> wrote:
> > > Is there a limit to the number of lines in an
> excel
> > > file created using POI? I am getting a
> > > java.lang.OutOfMemoryError in a test that
> attempts
> > > to create an excel file having 2 sheets and 5000
> > > rows of data with 50 columns in each row. Also
> is
> > > there a way to write the file in bits (i.e.
> execute
> > > wb.write(fout) method) to avoid memory loadup.
> > >
> > > Below is the POI code that uses POI version 2.5.
> > >
> > > File xlfile = new File("C:\temp\test.xls");
> > > FileOutputStream fout = new
> > > FileOutputStream(xlfile);
> > > HSSFWorkbook wb = new HSSFWorkbook();
> > > HSSFSheet sheet1 = wb.createSheet("sheet1");
> > > for (int j=0;j<=5000;j++) {
> > > row = sheet1.createRow(j);
> > > for (int i=0;i<=50;i++) {
> > > cell = row.createCell((short)i);
> > > cell.setCellType(HSSFCell.CELL_TYPE_STRING);
> > > cell.setCellValue("Hello");
> > > }
> > > }
> > > HSSFSheet sheet2 = wb.createSheet("sheet2");
> > > for (int j=0;j<=5000;j++) {
> > > row = sheet2.createRow(j);
> > > for (int i=0;i<=50;i++) {
> > > cell = row.createCell((short)i);
> > > cell.setCellType(HSSFCell.CELL_TYPE_STRING);
> > > cell.setCellValue("World");
> > > }
> > > }
> > > wb.write(fout);
> > > fout.close();
> > >
> > > Thanks
> > > PD
> > >
> >
> >
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Read only the mail you want - Yahoo! Mail
> SpamGuard.
> > http://promotions.yahoo.com/new_mail
> >
> >
>
---------------------------------------------------------------------
> > To unsubscribe, e-mail:
> poi-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail:
> poi-user-help@jakarta.apache.org
> >
>
>
>
---------------------------------------------------------------------
> To unsubscribe, e-mail:
> poi-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail:
> poi-user-help@jakarta.apache.org
>
>
__________________________________
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
http://promotions.yahoo.com/new_mail
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
Re: Creating Excel File
Posted by Ryan Ackley <sa...@cfl.rr.com>.
Just out of curiousity, what are you doing that requires you to output 1
million rows? It seems like it would be hard to find stuff in a workbook
that has a million rows. I would think that it would be better to break them
into smaller files named appropriately for a subcategory or something.
-Ryan
----- Original Message -----
From: "Davinder Kohli" <kd...@yahoo.com>
To: "POI Users List" <po...@jakarta.apache.org>
Sent: Monday, June 28, 2004 10:21 AM
Subject: Re: Creating Excel File
> An excel sheet can hold upto 65536 rows. This is limit
> by MS Excel and not POI. If you need to move/write
> rows > 65536, you may have to write them across
> multiple sheets.
>
> To get rid of the out of memory problem, icrease your
> JVM memory size to say 512M using -Xmx.
>
> In fact, I am running into the same problem, however,
> in my case I am trying to move 1 million row table to
> excel and there is so much I can do by increasing the
> memory size.
>
> My question to HSSF gurus:
> Can I append to an existing worksheet in a workbook
> without reading in the entire workbook into the user
> model?
>
> I am also thinking of storing those million rows
> across multiple workbooks instead of having 16,17
> workseets withing a single workbook.
>
> Thanks,
> Davinder
>
> --- Prashant Dube <pd...@rocketmail.com> wrote:
> > Is there a limit to the number of lines in an excel
> > file created using POI? I am getting a
> > java.lang.OutOfMemoryError in a test that attempts
> > to create an excel file having 2 sheets and 5000
> > rows of data with 50 columns in each row. Also is
> > there a way to write the file in bits (i.e. execute
> > wb.write(fout) method) to avoid memory loadup.
> >
> > Below is the POI code that uses POI version 2.5.
> >
> > File xlfile = new File("C:\temp\test.xls");
> > FileOutputStream fout = new
> > FileOutputStream(xlfile);
> > HSSFWorkbook wb = new HSSFWorkbook();
> > HSSFSheet sheet1 = wb.createSheet("sheet1");
> > for (int j=0;j<=5000;j++) {
> > row = sheet1.createRow(j);
> > for (int i=0;i<=50;i++) {
> > cell = row.createCell((short)i);
> > cell.setCellType(HSSFCell.CELL_TYPE_STRING);
> > cell.setCellValue("Hello");
> > }
> > }
> > HSSFSheet sheet2 = wb.createSheet("sheet2");
> > for (int j=0;j<=5000;j++) {
> > row = sheet2.createRow(j);
> > for (int i=0;i<=50;i++) {
> > cell = row.createCell((short)i);
> > cell.setCellType(HSSFCell.CELL_TYPE_STRING);
> > cell.setCellValue("World");
> > }
> > }
> > wb.write(fout);
> > fout.close();
> >
> > Thanks
> > PD
> >
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Read only the mail you want - Yahoo! Mail SpamGuard.
> http://promotions.yahoo.com/new_mail
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
Re: Creating Excel File
Posted by Prashant Dube <pd...@rocketmail.com>.
POI Community;
I was able to resolve the problem. It turned out to be a bug in my code. There was a "stray" line of code that was causing the problem. So instead of having
1) cell = row.createCell(cellNbr);
I had
2)
cell = row.createCell(rowNbr); // this line is not necessary
cell = row.createCell(cellNbr);
The cell = row.createCell(rowNbr) statement was trying to create cells beyond cell # 255 (remember excel can only handle 255 cells in a row) - rowNbr looped from 0 to the maximum number to rows to be written in a sheet.
The struts application is using POI version 1.5, which does not throw any exception when creating more than 255 cells in an excel. I was able to identify the issue using POI version 2.5, which throws an exeption for the same case.
Thank you for your help and support.
My faith in POI is restored! :-)
Danny Mui <da...@muibros.com> wrote:
help us debug the rogue sheet.
1) does this occur with that one sheet alone?
basically if u can reproduce this in as little code/data as possible it
is a likely case that somebody can help you out.
of course we need examples etc and the best place to put this is into a
bug to stamp out.
Prashant Dube wrote:
> I realize that excel has a 65k limit. I have a struts based app that is writing to a 2 sheet excel file from 2 DB tables - each table goes to its own sheet. The first sheet has order items while the second has products. The products are usually < 50 but the order items for an order can be 1000+
>
> I have tested orders for 10, 100, 200 and 300. the excel file is created successfuly in all cases. however, excels created for the order with 300 items does not open. excel fails on win2k with a "memory not available" error even tho the file is just 85k. i dont believe this is a data error. in my tests, i was able to open an exel cantaining the first 260 items of the same order and also the last 260 of the same order i.e. i can only 260 lines of an order in an excel file. the POI code runs successfully with no errors or exceptions. have no clue as to whats happening!
>
> in a separate test (simple java code using main method) i attempted to write an excel file having 2 sheets . i wrote out 10k rows having 50 cols in both the 2 sheets without any error. i used 512m for the JVM memory size. the same test gave an OUT OF MEMORY error for the JVM when trying to create a workbook with 50k rows of 50 cols in the same 2 sheets. the file opens without an errors thru excel.
>
> i understand the JVM limitation, but what i cant fathom is why the file created from the struts app fails to open even tho it isnt failing with a JVM error
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
Re: Creating Excel File
Posted by Danny Mui <da...@muibros.com>.
help us debug the rogue sheet.
1) does this occur with that one sheet alone?
basically if u can reproduce this in as little code/data as possible it
is a likely case that somebody can help you out.
of course we need examples etc and the best place to put this is into a
bug to stamp out.
Prashant Dube wrote:
> I realize that excel has a 65k limit. I have a struts based app that is writing to a 2 sheet excel file from 2 DB tables - each table goes to its own sheet. The first sheet has order items while the second has products. The products are usually < 50 but the order items for an order can be 1000+
>
> I have tested orders for 10, 100, 200 and 300. the excel file is created successfuly in all cases. however, excels created for the order with 300 items does not open. excel fails on win2k with a "memory not available" error even tho the file is just 85k. i dont believe this is a data error. in my tests, i was able to open an exel cantaining the first 260 items of the same order and also the last 260 of the same order i.e. i can only 260 lines of an order in an excel file. the POI code runs successfully with no errors or exceptions. have no clue as to whats happening!
>
> in a separate test (simple java code using main method) i attempted to write an excel file having 2 sheets . i wrote out 10k rows having 50 cols in both the 2 sheets without any error. i used 512m for the JVM memory size. the same test gave an OUT OF MEMORY error for the JVM when trying to create a workbook with 50k rows of 50 cols in the same 2 sheets. the file opens without an errors thru excel.
>
> i understand the JVM limitation, but what i cant fathom is why the file created from the struts app fails to open even tho it isnt failing with a JVM error
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
Re: Creating Excel File
Posted by Prashant Dube <pd...@rocketmail.com>.
I realize that excel has a 65k limit. I have a struts based app that is writing to a 2 sheet excel file from 2 DB tables - each table goes to its own sheet. The first sheet has order items while the second has products. The products are usually < 50 but the order items for an order can be 1000+
I have tested orders for 10, 100, 200 and 300. the excel file is created successfuly in all cases. however, excels created for the order with 300 items does not open. excel fails on win2k with a "memory not available" error even tho the file is just 85k. i dont believe this is a data error. in my tests, i was able to open an exel cantaining the first 260 items of the same order and also the last 260 of the same order i.e. i can only 260 lines of an order in an excel file. the POI code runs successfully with no errors or exceptions. have no clue as to whats happening!
in a separate test (simple java code using main method) i attempted to write an excel file having 2 sheets . i wrote out 10k rows having 50 cols in both the 2 sheets without any error. i used 512m for the JVM memory size. the same test gave an OUT OF MEMORY error for the JVM when trying to create a workbook with 50k rows of 50 cols in the same 2 sheets. the file opens without an errors thru excel.
i understand the JVM limitation, but what i cant fathom is why the file created from the struts app fails to open even tho it isnt failing with a JVM error
Re: Creating Excel File
Posted by Davinder Kohli <kd...@yahoo.com>.
An excel sheet can hold upto 65536 rows. This is limit
by MS Excel and not POI. If you need to move/write
rows > 65536, you may have to write them across
multiple sheets.
To get rid of the out of memory problem, icrease your
JVM memory size to say 512M using -Xmx.
In fact, I am running into the same problem, however,
in my case I am trying to move 1 million row table to
excel and there is so much I can do by increasing the
memory size.
My question to HSSF gurus:
Can I append to an existing worksheet in a workbook
without reading in the entire workbook into the user
model?
I am also thinking of storing those million rows
across multiple workbooks instead of having 16,17
workseets withing a single workbook.
Thanks,
Davinder
--- Prashant Dube <pd...@rocketmail.com> wrote:
> Is there a limit to the number of lines in an excel
> file created using POI? I am getting a
> java.lang.OutOfMemoryError in a test that attempts
> to create an excel file having 2 sheets and 5000
> rows of data with 50 columns in each row. Also is
> there a way to write the file in bits (i.e. execute
> wb.write(fout) method) to avoid memory loadup.
>
> Below is the POI code that uses POI version 2.5.
>
> File xlfile = new File("C:\temp\test.xls");
> FileOutputStream fout = new
> FileOutputStream(xlfile);
> HSSFWorkbook wb = new HSSFWorkbook();
> HSSFSheet sheet1 = wb.createSheet("sheet1");
> for (int j=0;j<=5000;j++) {
> row = sheet1.createRow(j);
> for (int i=0;i<=50;i++) {
> cell = row.createCell((short)i);
> cell.setCellType(HSSFCell.CELL_TYPE_STRING);
> cell.setCellValue("Hello");
> }
> }
> HSSFSheet sheet2 = wb.createSheet("sheet2");
> for (int j=0;j<=5000;j++) {
> row = sheet2.createRow(j);
> for (int i=0;i<=50;i++) {
> cell = row.createCell((short)i);
> cell.setCellType(HSSFCell.CELL_TYPE_STRING);
> cell.setCellValue("World");
> }
> }
> wb.write(fout);
> fout.close();
>
> Thanks
> PD
>
__________________________________
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
http://promotions.yahoo.com/new_mail
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org