Hello Martin, Glad you found the problem, I have just got in from work, was just sitting down with a cup of tea to look through the code when I read your message(s).
Yours Mark B Martin Asenov wrote: > > > 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] > > > -- View this message in context: http://old.nabble.com/flush-workbook-from-time-to-time-tp28341613p28364974.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]
