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]
