Actually I removed all unnecessary references, and do autoSizeColumn after the loop finishes.
It's bad debugging someone else's code :)))) Thank you all for the efforts! Best, Martin -----Original Message----- From: Richard Holmes [mailto:[email protected]] Sent: Monday, April 26, 2010 3:55 PM To: POI Users List Subject: Re: flush workbook from time to time Sorry just saw that too, On other thing that I noticed, I declared HSSFRow currentContactRow; Cell cellNumber; Cell cellFirstName; Cell cellLastName; Cell cellCurrentGroup; Outside of the loop, this had a marked impact too although the autoResizeColumn was worse, it definatly overloaded the memory. On 26 April 2010 13:51, Martin Asenov <[email protected]> wrote: > Hello, everyone! > > The problem was that sheet.autoResizeColumn() was called on every single > cell creation. Fortunately, I saw where the delay was coming from! > > Thank you all for the help! > > Best, > Martin > > -----Original Message----- > From: Richard Holmes [mailto:[email protected]] > Sent: Monday, April 26, 2010 10:23 AM > To: Martin Asenov > Subject: Re: flush workbook from time to time > > Hi martin > > Ok was just wondering how the iterators worked specifically the inner > iteration does it use queries against the database and are you explicitly > closing result sets > > Richard > -----Original Message----- > From: Martin Asenov <[email protected]> > Date: Mon, 26 Apr 2010 10:16:09 > To: POI Users List<[email protected]>; [email protected]< > [email protected]> > Subject: RE: flush workbook from time to time > > Hi, Richard! > > The two classes are too big and there is nothing special about them - the > manager only uses underlying Hibernate layer to do queries, and the Contact > class is just a bean. > > The problem here is as the workbook grows up, things are terribly slowed > down. > > Best, > Martin > > -----Original Message----- > From: Richard Holmes [mailto:[email protected]] > Sent: Monday, April 26, 2010 10:12 AM > To: POI Users List > Subject: Re: flush workbook from time to time > > Hi martin > > Can you send the manager and contact class too > > I will look later when I am at my computer > Thanks > Richard > -----Original Message----- > From: Martin Asenov <[email protected]> > Date: Mon, 26 Apr 2010 09:46:35 > To: POI Users List<[email protected]> > Subject: RE: flush workbook from time to time > > > 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] > > > --------------------------------------------------------------------- > 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]
