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]
