Hi martin Have you looked at what the garbage collector is doing. I have seen this type of behaviour in applications and it is nearly always due to memory not being released causing the gc to take more and more time from the app. Rich -----Original Message----- From: Martin Asenov <[email protected]> Date: Fri, 23 Apr 2010 19:07:47 To: POI Users List<[email protected]> Subject: RE: flush workbook from time to time
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]
