Morning Martin,
Forgive the stupid question but does this code solve the problem or is it
exhibiting the issue you described?
Yours
Mark B
Martin Asenov wrote:
>
>
> 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]
>
>
>
--
View this message in context:
http://old.nabble.com/flush-workbook-from-time-to-time-tp28341613p28361478.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]
