You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by suriz4u <su...@gmail.com> on 2008/06/04 14:57:00 UTC
Urgent::POI is too slow for generating excel for more than 60000
rows
HI Every one
Iam using POI in my application.I have 76281 records.It took more
than an 1 hour.Iam not sure,being unpatient i stopped running it iam sure
some thing is wrong .Iam pasting my code here by.
Can any one help me out please
public class NewWorkbook
{
public static void main(String[] args)
throws Exception
{
CustomerProfileDAO cDAO=new CustomerProfileDAO();
TestExcelDAO tDAO=new TestExcelDAO();
List excelList=(List)tDAO.getExcelReport();
System.out.println("list size"+excelList.size());
String fields[][]={ {"shipperCode","Shipper",""},
{"packageQuantity","Quantity",""},
{"billOptionDesc","Bill Option",""},
{"netAmount","Net Charges",""},
{"incentiveAmount","Discount",""},
{"trackingNumber","Tracking Number",""},
{"billedWeight","Billed Weight",""},
{"enteredWeight","Actual Weight",""},
{"billedWeightUnitOfMeasure","Measure",""},
{"billedWeightType","Weight Type",""},
{"packageDimensions","Pack Dim",""},
{"zone","Zone",""},
};
NewWorkbook newWorkbook=new NewWorkbook();
OutputStream f2 = new FileOutputStream("D:/POI/testing.xls");
newWorkbook.makeExcel(excelList,fields,f2 );
f2.close();
}
public void makeExcel(List l,String fields[][],OutputStream out) throws
Exception
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r = null;
HSSFCell c = null;
HSSFCellStyle cs = wb.createCellStyle();
HSSFDataFormat df = wb.createDataFormat();
HSSFCellStyle special = wb.createCellStyle();
HSSFCellStyle csDate = wb.createCellStyle();
HSSFFont f = wb.createFont();
f.setFontHeightInPoints((short) 12);
f.setColor( (short)0xc );
f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cs.setFont(f);
wb.setSheetName(0, "HSSF Test1",
HSSFWorkbook.ENCODING_COMPRESSED_UNICODE );
Iterator it =l.iterator();
int maxCount=65000;
short fieldSize=0;
Urgent::POI is too slow for generating excel for more than 60000 rows
int rownum=0;
r = s.createRow(rownum++);
for(short j=0;j<fields.length;j++)
{
c=r.createCell(j);
c.setCellValue(fields[j][1]);
c.setCellStyle(cs);
}
IBean iBean=null ;
boolean flag=true;
while(it.hasNext())
{
r = s.createRow(rownum++);
iBean = (IBean)it.next();
if(rownum>=maxCount && flag)
{
flag=false;
s= wb.createSheet();
wb.setSheetName(1, "HSSF Sheet2",
HSSFWorkbook.ENCODING_COMPRESSED_UNICODE );
rownum=0;
}
// System.out.println("rownum="+rownum);
for(short i=0;i<fields.length;i++)
{
try
{
Object
value=PropertyUtils.getNestedProperty(iBean,fields[i][0]);
s.autoSizeColumn((short)i);
if(value == null)
value=""; // initialize the null values to empty.
c=r.createCell((short)i);
c.setCellValue( (value).toString() );
}
catch(Exception ex)
{
throw ex;
}
}
}
wb.write(out);
}
}
--
View this message in context: http://www.nabble.com/Urgent%3A%3APOI-is-too-slow-for-generating-excel-for-more-than-60000-rows-tp17646122p17646122.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: Urgent::POI is too slow for generating excel for more than 60000 rows
Posted by Anthony Andrews <py...@yahoo.com>.
As with the maximum number of rows, that will be determined by Excel itself, not by POI.
Have a look at this url for the definitive answer:
http://office.microsoft.com/en-us/excel/HP051992911033.aspxSorry about the font, cut it straight out of a Google results page!!
--- On Sun, 6/8/08, suriz4u <suriz4u@gmail.com> wrote:
From: suriz4u <suriz4u@gmail.com>
Subject: Re: Urgent::POI is too slow for generating excel for more than 60000 rows
To: user@poi.apache.org
Date: Sunday, June 8, 2008, 9:51 PM
Yeah its due to JVM.Its working fine now.Thanks..
Also May i know.Whats maximum number of tabs we can create using POI??
Thanks
Suresh.G
Daniel Noll-3 wrote:
>
> On Wednesday 04 June 2008 22:57:00 suriz4u wrote:
>> HI Every one
>> Iam using POI in my application.I have 76281 records.It took
>> more
>> than an 1 hour.Iam not sure,being unpatient i stopped running it iam
sure
>> some thing is wrong .Iam pasting my code here by.
>> Can any one help me out please
>
> This is a stab in the dark, but perhaps the JVM is running out of memory
> and
> thus the majority of your time is spent doing garbage collection?
>
> Daniel
>
> ---------------------------------------------------------------------
> 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://www.nabble.com/Urgent%3A%3APOI-is-too-slow-for-generating-excel-for-more-than-60000-rows-tp17646122p17726297.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: Urgent::POI is too slow for generating excel for more than
60000 rows
Posted by suriz4u <su...@gmail.com>.
Yeah its due to JVM.Its working fine now.Thanks..
Also May i know.Whats maximum number of tabs we can create using POI??
Thanks
Suresh.G
Daniel Noll-3 wrote:
>
> On Wednesday 04 June 2008 22:57:00 suriz4u wrote:
>> HI Every one
>> Iam using POI in my application.I have 76281 records.It took
>> more
>> than an 1 hour.Iam not sure,being unpatient i stopped running it iam sure
>> some thing is wrong .Iam pasting my code here by.
>> Can any one help me out please
>
> This is a stab in the dark, but perhaps the JVM is running out of memory
> and
> thus the majority of your time is spent doing garbage collection?
>
> Daniel
>
> ---------------------------------------------------------------------
> 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://www.nabble.com/Urgent%3A%3APOI-is-too-slow-for-generating-excel-for-more-than-60000-rows-tp17646122p17726297.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: Urgent::POI is too slow for generating excel for more than 60000 rows
Posted by Daniel Noll <da...@nuix.com>.
On Wednesday 04 June 2008 22:57:00 suriz4u wrote:
> HI Every one
> Iam using POI in my application.I have 76281 records.It took more
> than an 1 hour.Iam not sure,being unpatient i stopped running it iam sure
> some thing is wrong .Iam pasting my code here by.
> Can any one help me out please
This is a stab in the dark, but perhaps the JVM is running out of memory and
thus the majority of your time is spent doing garbage collection?
Daniel
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Urgent::POI is too slow for generating excel for more than
60000 rows
Posted by MSB <ma...@tiscali.co.uk>.
I am not offering this explanation as an excuse but merely as what it is, an
explanation. The main reason HSSF/XSSF are so memory intensive is that a
complete in-memory 'model' of the workbook must be built before it can be
converted into a correctly formatted file when written out to disk. Sadly,
there is no way to build the .xls file incrementally owing to the constaints
of the BIFF8 file format so, typically, the first step is to increase the
amount of memory available to the jvm using the -X switches.
Having said that, can I ask what it is you are doing? Are you simply
creating a sheet full of numbers? Is the formatting applied to the cells
important - do you need to see negative numbers coloured red for example? Do
you create formulae and add them to the sheet to perform calculations for
you? Do you require labels at the top of the columns/beginning of each row?
If not, then you could think about creating a .csv file using core java code
and then relying on Excel's import feature to bring the data into a
worksheet for you.
Just as an aside, can I ask what you mean by "an excel document stream from
a file"?
vince_neil wrote:
>
> I am experiencing this issue. I am creating an HSSFWorkbook with an excel
> document stream from a file, but it takes more than an hour for the
> HSSFWorkbook to be created, and it crashes before it can finish due to
> java heap space running out of memory!
>
> The file is 24 megabytes, and simply consists of thousands of rows with
> whole numbers in each cell. There are tens of thousands of rows. Is POI
> really slow and inefficient? Is there a way to fix this problem? One
> would think that an HSSFWorkbook, even with the number of rows and cells I
> am dealing with, could be instantiated much faster than this! Please help
> if you know what the problem is.
>
--
View this message in context: http://www.nabble.com/Urgent%3A%3APOI-is-too-slow-for-generating-excel-for-more-than-60000-rows-tp17646122p23665402.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: Urgent::POI is too slow for generating excel for more than
60000 rows
Posted by vince_neil <vi...@yahoo.com>.
I am experiencing this issue. I am creating an HSSFWorkbook with an excel
document stream from a file, but it takes more than an hour for the
HSSFWorkbook to be created, and it crashes before it can finish due to java
heap space running out of memory!
The file is 24 megabytes, and simply consists of thousands of rows with
whole numbers in each cell. There are tens of thousands of rows. Is POI
really slow and inefficient? Is there a way to fix this problem? One would
think that an HSSFWorkbook, even with the number of rows and cells I am
dealing with, could be instantiated much faster than this! Please help if
you know what the problem is.
--
View this message in context: http://www.nabble.com/Urgent%3A%3APOI-is-too-slow-for-generating-excel-for-more-than-60000-rows-tp17646122p23655642.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