Hi, I guess I have created the cell styles and row objects in pretty much the same manner as you have. The difference is that while you have been creating 40000 rows having about 4 columns in it I have tried creating about 5000-6000 odd rows with about 60 columns each. Each record hence becomes huge. Such memory issues come up because of the totality of the object sizes, which would be a combination of both the number of columns as well as the number of rows. I have tried running the same for 6 columns and 11000 rows and I have no issues so far! I guess this pretty much answers your question Balaji! Thanks a lot for the inputs so far!
Regards, Suchitra. -----Original Message----- From: Balaji [mailto:[EMAIL PROTECTED] Sent: Friday, March 04, 2005 9:48 PM To: POI Users List Cc: Suchitra Sundararajan Subject: Re: Regarding HSSF Formatting issues Hi Suchi, I have tried a piece of code, i am able to print upto 40000 rows, you just go through the below code. ---------------- import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.usermodel.HSSFSheet; // public class TestExcel { /** Creates a new instance of TestExcel */ public TestExcel() { // java.util.Calendar cal = java.util.Calendar.getInstance(); System.out.println("Start Time Minutes: "+cal.get(java.util.Calendar.MINUTE)+"Seconds: "+cal.get(java.util.Calendar.SECOND)+" Milliseconds :"+cal.get(java.util.Calendar.MILLISECOND)); // HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet("Sheet1"); HSSFRow row = null; HSSFCell cell = null; // HSSFFont font = book.createFont(); HSSFCellStyle cellStyle = book.createCellStyle(); font.setColor(HSSFFont.COLOR_RED); font.setItalic(false); font.setFontName("Times New Roman"); cellStyle.setFont(font); cellStyle.setAlignment(cellStyle.ALIGN_JUSTIFY); cellStyle.setWrapText(true); // for(int i=0; i<40000; i++) { row = sheet.createRow((short)i); cell = row.createCell((short)0); // cell.setCellStyle(cellStyle); cell.setCellValue("First"); // cell = row.createCell((short)1); // cell.setCellStyle(cellStyle); cell.setCellValue("Second"); // cell = row.createCell((short)2); // cell.setCellStyle(cellStyle); cell.setCellValue("Third"); // cell = row.createCell((short)3); // cell.setCellStyle(cellStyle); cell.setCellValue("Fourth"); // if(i % 5000 == 0) { System.out.println("Inside the mod "+i); System.gc(); } } System.gc(); // try { book.write(new ava.io.FileOutputStream("C:\\Test.xls")); } catch(Exception e) {} cal = java.util.Calendar.getInstance(); // System.out.println("End Time Minutes: "+cal.get(java.util.Calendar.MINUTE)+"Seconds: "+cal.get(java.util.Calendar.SECOND)+" Milliseconds :"+cal.get(java.util.Calendar.MILLISECOND)); } public static void main(String[] args) { new TestExcel(); } } give a try and update me Regards, Balaji ----- Original Message ----- From: "Suchitra Sundararajan" <[EMAIL PROTECTED]> To: "POI Users List" <[email protected]> Sent: Friday, March 04, 2005 6:09 PM Subject: RE: Regarding HSSF Formatting issues Hi, I tried increasing the heap size but to no avail. It still gives the memory issues. I gave 512 mb but still it gave me Out Of Memory Exception. How can I destroy that object? Regards, Suchitra. -----Original Message----- From: Balaji [mailto:[EMAIL PROTECTED] Sent: Friday, March 04, 2005 4:37 PM To: POI Users List Subject: Re: Regarding HSSF Formatting issues hi, yes, its possible to increase the JVM size, execute your class/jar file through batch file. which shud have following lines. java [youclass] -Xms128m -Xmx512m -Xms is minimum memory, mx is maximum Regards Balaji. ----- Original Message ----- From: "Suchitra Sundararajan" <[EMAIL PROTECTED]> To: <[email protected]> Cc: <[EMAIL PROTECTED]> Sent: Friday, March 04, 2005 4:24 PM Subject: RE: Regarding HSSF Formatting issues 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:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] > 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 03, 2005 8:05 PM > To: [email protected] > 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:[EMAIL PROTECTED] > > > 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: [EMAIL PROTECTED] > > > 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: [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/ --------------------------------------------------------------------- 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/
