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]
>
>
