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]
