You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by an...@superlinksoftware.com on 2005/03/04 17:39:16 UTC
Re: Regarding HSSF Formatting issues (That time of year again)
If you store 40000 rows in Excel then WHOA you probably deserve what you
get. Excel and OLE format is the WORST format you could possibly use
for large data transfers. Its BAD BAD BAD. Some people think I was
unclear on this: "Poor Obfuscation Implementation" and "Horrible Spread
Sheet Format" and "Horrible Word Processing Format" and "Horrible
Property Set Format" -- however the whole idea of this project when it
started was to do something HARD. These formats are so absolutely
HORRIBLE that learning to parse them and do it in a language with a BAD
IO impelmetnation was a HARD thing to do! Now its easy because we're
practiced.
I DO know how to do this with minimum memory consumption. I have guys
who can do this and can make the changes completely to HSSF to cut it
down to like 1/8th (guess) of present memory consumption with about a
50-75% performance improvement by using NIO and memory mapped files (the
format is not streamable). However, we pretty much do things these days
when someone funds the work. Apache is a wonderful place to start a
project into wide use but its a horrible place to run an open source
business (which is needed to sustain things that are hard).
So if someone wants it....they'll eventually pay us to do it and it will
get added. Its not exactly hard, its just a lot of work (but we will
have to drop JDK 1.3 support for good).
Suchitra Sundararajan wrote:
>Hi ,
>Thanks Amol for the suggestions but I still require further help.
>Is there any other way other than increasing the JVM size, with respect
>to destroying the java objects and recreating them in say batches of
>1000?
>I tried creating the File and Workbook objects inside the while loop and
>even though the file was created in append mode it had only one row,
>presumably the last row.
>Even when I am adding data in batches, without recreating the file and
>workbook objects that is, creating it outside the while loop, the object
>size keeps growing as, at no point are we destroying the object itself
>and every time I write the object it is actually overwriting the earlier
>file.
>Can you help me with this?
>I have attached the java file.
>The above code throws an out of memory exception.
>What I want is this.
>/ Is there some way in which the object can be set to null and then
>created again and the file appended. I created the file in append mode
>but still no avail.
>/ In case I increase the heap size how reliable is it?
>/ What if I have applications having about 40-50 thousand rows. I mean
>there might be a case wherein even the max heap size might be exceeded?
>
>Can you please provide me info about these?
>
>-----Original Message-----
>From: Amol Deshmukh [mailto:adeshmukh@hobsons-us.com]
>Sent: Thursday, March 03, 2005 8:33 PM
>To: 'POI Users List'
>Subject: RE: Regarding HSSF Formatting issues
>
>try increasing JVM heap size (-Xmx 512 etc.) at startup.
>~ amol
>
>
>
>>-----Original Message-----
>>From: Suchitra Sundararajan [mailto:Suchitra_S01@infosys.com]
>>Sent: Thursday, March 03, 2005 10:03 AM
>>To: POI Users List
>>Subject: RE: Regarding HSSF Formatting issues
>>
>>
>>Hi,
>>Further to the suggestions made by all of you, I no longer
>>get the cell
>>style formatting issues. Thanks a lot!!
>>But then the Out of memory error persists , in my particular case when
>>the number of rows are 3998 and the columns are of the order of 60. I
>>checked out that quite a few people have reported such errors in some
>>of the mailing lists. Has a possible work around been got for
>>this case?
>>Is this a bug in this utility?
>>
>>Regards,
>>Suchitra
>>
>>-----Original Message-----
>>From: Suchitra Sundararajan
>>Sent: Thursday, March 03, 2005 8:14 PM
>>To: POI Users List
>>Subject: RE: Regarding HSSF Formatting issues
>>
>>Hi,
>>
>>Thanks a lot for the suggestions. I was earlier (before u guys mailed
>>out) creating a new cell style for every row. I changed this to create
>>one instance of the cell style and just keep the setCellStyle method
>>within the loop but I am still encountering the same problem.
>>
>>I have attached the piece of code below. Kindly have a look at it and
>>tell me if I am missing out anything.
>>
>>
>>
>>HSSFFont font=hsf.createFont();
>>
>> HSSFCellStyle cellStyle= hsf.createCellStyle();
>>
>> font.setColor(HSSFFont.COLOR_NORMAL);
>>
>> font.setItalic(false);
>>
>> font.setFontName("Times New Roman");
>>
>> cellStyle.setFont(font);
>>
>> cellStyle.setAlignment(cellStyle.ALIGN_JUSTIFY);
>>
>> cellStyle.setWrapText(true);
>>
>>
>>
>> while(rs.next())
>>
>> {
>>
>> int iBatchCount=0;
>>
>>
>>
>> HSSFRow row = sheet.createRow((short)RowNum);
>>
>>
>>
>> for(int i=1;i<=rsmd.getColumnCount();i++)
>>
>> {
>>
>>
>>
>> HSSFCell cell =
>>row.createCell((short)(i-1));
>>
>> cell.setCellStyle(cellStyle);
>>
>> cell.setCellValue(rs.getString(i));
>>
>> }
>>
>> RowNum++;
>>
>> iBatchCount++;
>>
>> System.out.println("Row num is " + RowNum);
>>
>> if(iBatchCount==1000)
>>
>> {
>>
>> hsf.write(fout);
>>
>> iBatchCount=0;
>>
>>
>>
>> }
>>
>> }
>>
>> hsf.write(fout);
>>
>>
>>
>>Thanks and Regards,
>>
>>Suchitra.
>>
>>
>>
>>-----Original Message-----
>>From: Frans.Flippo@INGBank.com [mailto:Frans.Flippo@INGBank.com]
>>Sent: Thursday, March 03, 2005 8:05 PM
>>To: poi-user@jakarta.apache.org
>>Subject: RE: Regarding HSSF Formatting issues
>>
>>
>>
>>
>>
>>Are you creating a new HSSFStyle for each cell or using a
>>single shared
>>
>>style for all cells?
>>
>>
>>
>>Regards,
>>
>>Frans
>>
>>
>>
>>
>>
>>
>>
>>>-----Original Message-----
>>>
>>>
>>>From: Suchitra Sundararajan [mailto:Suchitra_S01@infosys.com]
>>>
>>>
>>>Sent: Thursday, March 03, 2005 3:24 PM
>>>
>>>
>>>To: POI Users List
>>>
>>>
>>>Subject: Regarding HSSF Formatting issues
>>>
>>>
>>>Hi,
>>>
>>>
>>>I have used the HSSF class to write the results of my select
>>>
>>>
>>>query into
>>>
>>>
>>>an excel sheet. I noticed that even if there were just 2 columns and
>>>
>>>
>>>about 1000 rows, there are some formatting issues. My data
>>>
>>>
>>is supposed
>>
>>
>>
>>>to be in TimesNewRoman size 10 but beyond some rows, in this
>>>
>>>
>>>case around
>>>
>>>
>>>506 itself the format changes to Arial 10 and it reports Some text
>>>
>>>
>>>formatting might have changed because the maximum number of
>>>
>>>
>>fonts has
>>
>>
>>
>>>been exceeded. I have attached the screen shot of the
>>>
>>>
>>error. The data
>>
>>
>>
>>>seems to be fine but the format has changed
>>>
>>>
>>>Also once this error has been encountered, the next time i run the
>>>
>>>
>>>program without closing the java file (i was running it
>>>
>>>
>>from Textpad)
>>
>>
>>
>>>even if the selected rows are only 10 the same formatting
>>>
>>>
>>error comes
>>
>>
>>
>>>up.
>>>
>>>
>>>Also when the number of rows are very huge, about 10000 or
>>>
>>>
>>so it gives
>>
>>
>>
>>>me a run time exception of out of memory exception. I thought
>>>
>>>
>>>initially
>>>
>>>
>>>this was because I was writing to the file at the end of
>>>
>>>
>>all the rows.
>>
>>
>>
>>>So I started writing for every row into the sheet but that made the
>>>
>>>
>>>whole process too slow. It was taking several minutes. So I started
>>>
>>>
>>>writing to the excel sheets in batches of 1000 but even
>>>
>>>
>>that threw an
>>
>>
>>
>>>Out of bound exception. Is this a reported bug in the feature? Am I
>>>
>>>
>>>missing out some very important thing here? Is there some
>>>
>>>
>>clearing of
>>
>>
>>
>>>the workbook object that I should do?
>>>
>>>
>>>Thanks and Regards,
>>>
>>>
>>>Suchitra
>>>
>>>
>>---------------------------------------------------------------------
>>
>>
>>
>>>To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>>>
>>>
>>>Mailing List: http://jakarta.apache.org/site/mail2.html#poi
>>>
>>>
>>>The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>>>
>>>
>>
>>
>>
>>
>>-----------------------------------------------------------------
>>
>>ATTENTION:
>>
>>The information in this electronic mail message is private and
>>
>>confidential, and only intended for the addressee. Should you
>>
>>receive this message by mistake, you are hereby notified that
>>
>>any disclosure, reproduction, distribution or use of this
>>
>>message is strictly prohibited. Please inform the sender by
>>
>>reply transmission and delete the message without copying or
>>
>>opening it.
>>
>>
>>
>>Messages and attachments are scanned for all viruses known.
>>
>>If this message contains password-protected attachments, the
>>
>>files have NOT been scanned for viruses by the ING mail domain.
>>
>>Always scan attachments before opening them.
>>
>>-----------------------------------------------------------------
>>
>>
>>
>>
>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>>Mailing List: http://jakarta.apache.org/site/mail2.html#poi
>>The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>>
>>
>>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>Mailing List: http://jakarta.apache.org/site/mail2.html#poi
>The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
>
>
>
>
>------------------------------------------------------------------------
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>Mailing List: http://jakarta.apache.org/site/mail2.html#poi
>The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/