It causes the problem :) Best, Martin
-----Original Message----- From: MSB [mailto:[email protected]] Sent: Monday, April 26, 2010 10:00 AM To: [email protected] Subject: RE: flush workbook from time to time Morning Martin, Forgive the stupid question but does this code solve the problem or is it exhibiting the issue you described? Yours Mark B Martin Asenov wrote: > > > Hello, everyone! > > I provide the snippet of code I'm using: > > @Component > public class PhonebookExporterXLS { > public static final String GROUP_STR = "*"; > public static final String OUTPUT_FILE_NAME = "contactsExportToXLS"; > > private ManagerBean manager; > private SMSOfficeProperties props; > > public void setProps(SMSOfficeProperties props) { > this.props = props; > } > > private Domain domain; > private User actor; > > FileOutputStream fos; > > public void setManager(ManagerBean manager) { > this.manager = manager; > } > > public PhonebookExporterXLS() { > } > > public PhonebookExporterXLS(User actor, Domain domain) { > this.domain = domain; > this.actor = actor; > } > > // Excel tools > HSSFWorkbook wb; > HSSFSheet sheet; > > int rowCounterContacts = 0; > > public File export(String path) throws SMSOfficeException { > > File exportedFile = new File(path + > String.format(props.get(OUTPUT_FILE_NAME), > StatisticsExporter.fileDateFormat.format(new Date()))); > exportedFile.getParentFile().mkdirs(); > > int currentRowCellsCount; > > try { > fos = new FileOutputStream(exportedFile); > wb = new HSSFWorkbook(); > sheet = wb.createSheet("Contacts"); > > for (Contact currentContact : > manager.getContacts(domain)) { > HSSFRow currentContactRow = > sheet.createRow(rowCounterContacts++); > > currentRowCellsCount = 0; > > // Number cell(s) - (String type) > for (PhoneNumber currentNumber : > currentContact.getNumbers()) { > Cell cellNumber = > currentContactRow.createCell(currentRowCellsCount++); > > cellNumber.setCellValue(currentNumber.getFullNumber()); > > sheet.autoSizeColumn(currentRowCellsCount - 1); > } > > // First name cell - (String type) > Cell cellFirstName = > currentContactRow.createCell(currentRowCellsCount++); > > cellFirstName.setCellValue(currentContact.getFirstName()); > sheet.autoSizeColumn(currentRowCellsCount - 1); > > // Last name cell - (String type) > Cell cellLastName = > currentContactRow.createCell(currentRowCellsCount++); > > cellLastName.setCellValue(currentContact.getLastName()); > sheet.autoSizeColumn(currentRowCellsCount - 1); > > for (Group currentGroup : > currentContact.getGroups()) { > Cell cellCurrentGroup = > currentContactRow.createCell(currentRowCellsCount++); > cellCurrentGroup.setCellValue(GROUP_STR > + currentGroup.getName()); > > sheet.autoSizeColumn(currentRowCellsCount - 1); > } > } > > // Writing the workbook to FileOutputStream... > wb.write(fos); > fos.flush(); > > } catch (IOException ioe) { > ioe.printStackTrace(); > } finally { > rowCounterContacts = 0; > > try { > fos.close(); > } catch (IOException e) { > e.printStackTrace(); > } > } > > return exportedFile; > > } > > public void setUser(User user) { > actor = user; > } > > public void setDomain(Domain domain) { > this.domain = domain; > } > } > > Best, > Martin > > -----Original Message----- > From: MSB [mailto:[email protected]] > Sent: Sunday, April 25, 2010 10:45 AM > To: [email protected] > Subject: RE: flush workbook from time to time > > > I should have expected you to look into CSV files, sorry about that. Must > admit, I also like Richard's suggestion; it could all be down to the JVM > not > reclaiming space. > > Until Monday. > > Yours > > Mark B > > > Martin Asenov wrote: >> >> Hi Mark, >> >> Well I've got both CSV and XLS export - you know about line separators >> and >> all this stuff that is system dependent, so we provide the both variants. >> >> Let's discuss it in Monday... Have a nice weekend >> >> Thank you, >> Martin >> >> -----Original Message----- >> From: MSB [mailto:[email protected]] >> Sent: Friday, April 23, 2010 6:59 PM >> To: [email protected] >> Subject: RE: flush workbook from time to time >> >> >> Old age is really slowing me down these days - and I can blame working >> for >> the last few days on a 1 in 3 slope removing Hawthorn, still digging the >> thorns out of my fingers now! - but have you looked into generating a CSV >> file and then 'opening' this with Excel? >> >> I have never tried it myself but I think it should be possible to create >> a >> template using Excel - set all of the cell formats correctly and so on. >> Then >> you could simply open a CSV file and populate the respective cells with >> data >> and I am guessing that the formatting information will be preserved; your >> application would then only have to create/edit the CSV file. >> >> It should be easy enough to mock up a simple test to see if it would work >> and although it would add some complexity to the task - opening the Excel >> template and then importing the data - that might be worthwhile. A macro >> could even be used to automate the process - something that runs when the >> workbook is opened to import the data from the CSV file maybe. >> >> Yours >> >> Mark B >> >> >> Martin Asenov wrote: >>> >>> No, I've been using old good system.out.println(); statements - and >>> noticed that the gap between different insertions into the workbook >>> slighly increases on every insertion. Actually, the output file was 212 >>> kb >>> large - it is surprising for me that file of such size could be >>> generated >>> in so large amount of time. Something must be wrong... >>> >>> Best, >>> Martin >>> >>> -----Original Message----- >>> From: MSB [mailto:[email protected]] >>> Sent: Friday, April 23, 2010 6:24 PM >>> To: [email protected] >>> Subject: RE: flush workbook from time to time >>> >>> >>> Oh, well in that case the BigGridDemo would not work for you as it was >>> only >>> applicable to the OOXML based file format. If you are using the older >>> binary >>> format (.xls) then the answer is a certain no to streaming the file in >>> chunks I am sorry to say. The reason lies in the structure of the Excel >>> file >>> itself, I am not completely certain of the details but understand that >>> it >>> consists of a series of streams and is block structured - that is why >>> the >>> files sizes are always multiples of 512 or 4096 I believe. As a result, >>> a >>> model of the file must be assembled in memory firstly, formatted and >>> then >>> written out. >>> >>> Must admit that I am surprised you are experiencing problems with >>> performance if you are using the HSSF stream as that is a more common >>> problem with the OOXML files. Have you tried running a profiler on the >>> code >>> to see where the bottlenecks are? >>> >>> Yours >>> >>> Mark B >>> >>> >>> Martin Asenov wrote: >>>> >>>> Hi, Mark, thanks for the quick reply. >>>> >>>> I don't know exactly what format we're exporting into, but I think it's >>>> the .xls one. In code we're using HSSFWorkbook class. Anyway, the whole >>>> thing is not written by me, so I'm unaware of more details about it. >>>> >>>> Every single contact expands from 2 to up to 20 horizontal cells, but >>>> commonly in 4-5 horizontal cells. >>>> >>>> The point is that in the UI when the user clicks on 'export' we're >>>> supposed to supply him with the exported file. Delaying is not a >>>> problem, >>>> unless it's 7-8 mins for a workbook of 2000 contacts :-) I hope there's >>>> a >>>> way to work around this... >>>> >>>> Best, >>>> Martin >>>> >>>> >>>> -----Original Message----- >>>> From: MSB [mailto:[email protected]] >>>> Sent: Friday, April 23, 2010 5:17 PM >>>> To: [email protected] >>>> Subject: Re: flush workbook from time to time >>>> >>>> >>>> Can I ask which version of the file format you are targetting please, >>>> the >>>> binary (.xls) or OOXML (.xlsx) one? If it is the latter, then it may be >>>> worthwhile looking at Yegor's BigGridDemo program - sorry but I do not >>>> have >>>> the link at hand currently - where he streams the contents of a file >>>> into >>>> a >>>> template to create large worksheets without encountering out of memory >>>> exceptions. It is not a fully fledged utility but rather a proof that >>>> you >>>> may - and that is may - be able to adapt to handle your current >>>> requirement. >>>> I do not know if you would be able to modify the program to, for >>>> example, >>>> fill rows 0 to 2000 with data and then fill rows 2001 to 4000 with data >>>> at >>>> a >>>> later date but I feel it could be worth looking into. >>>> >>>> Aside from that, there is no way to currently stream the document out >>>> bit >>>> by >>>> bit I am sorry to say. It should be possible and has, I believe, been >>>> discussed by the developers but would require some programming effort >>>> and >>>> no >>>> one has stepped up to sponsor the work. >>>> >>>> Yours >>>> >>>> Mark B >>>> >>>> >>>> Martin Asenov wrote: >>>>> >>>>> Hello, everyone! >>>>> >>>>> I've got some DB which contains some contacts. I'm using the Apache >>>>> POI >>>>> in >>>>> order to export that DB to an Excel file. Anyway, let's presume the >>>>> contacts are 2000. When I start creating the workbook, the contacts >>>>> are >>>>> inserted pretty quick, but when the workbook begins to get bigger and >>>>> bigger, things are terribly slowed down. >>>>> >>>>> Is there a way to flush the workbook from time to time? I didn't see >>>>> such >>>>> opportunity in the API. >>>>> >>>>> Best, >>>>> Martin >>>>> >>>>> >>>> >>>> -- >>>> View this message in context: >>>> http://old.nabble.com/flush-workbook-from-time-to-time-tp28341613p28341925.html >>>> Sent from the POI - User mailing list archive at Nabble.com. >>>> >>>> >>>> --------------------------------------------------------------------- >>>> To unsubscribe, e-mail: [email protected] >>>> For additional commands, e-mail: [email protected] >>>> >>>> >>>> --------------------------------------------------------------------- >>>> To unsubscribe, e-mail: [email protected] >>>> For additional commands, e-mail: [email protected] >>>> >>>> >>>> >>> >>> -- >>> View this message in context: >>> http://old.nabble.com/flush-workbook-from-time-to-time-tp28341613p28342812.html >>> Sent from the POI - User mailing list archive at Nabble.com. >>> >>> >>> --------------------------------------------------------------------- >>> To unsubscribe, e-mail: [email protected] >>> For additional commands, e-mail: [email protected] >>> >>> >>> --------------------------------------------------------------------- >>> To unsubscribe, e-mail: [email protected] >>> For additional commands, e-mail: [email protected] >>> >>> >>> >> >> -- >> View this message in context: >> http://old.nabble.com/flush-workbook-from-time-to-time-tp28341613p28343277.html >> Sent from the POI - User mailing list archive at Nabble.com. >> >> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: [email protected] >> For additional commands, e-mail: [email protected] >> >> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: [email protected] >> For additional commands, e-mail: [email protected] >> >> >> > > -- > View this message in context: > http://old.nabble.com/flush-workbook-from-time-to-time-tp28341613p28354716.html > Sent from the POI - User mailing list archive at Nabble.com. > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > > > -- View this message in context: http://old.nabble.com/flush-workbook-from-time-to-time-tp28341613p28361478.html Sent from the POI - User mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
