Hello!
We have
requirement for generating very large excel workbooks to end user
throught the Servlet API. The idea was to write workbook data directly
into HTTP output stream without waiting for complete workbook generation
into memory, because very large workbooks will cause OutOfMemoryError.
Does the Aspose.Cells providing such capability?
Many thanks,
Artem.
This message was posted using Email2Forum by ShL77.
Hi,
I am afraid, we don’t support to write workbook into HTTP output streams. I have logged your feature request into our issue tracking system with an id: CELLSJAVA-20137. We need to investigate if this is actually possible or is there any other way around, we will get back to you soon.
Thank you.
Aspose.Cells does offer the LightCell API, which is a streaming API for reading Excel files.
For writing large Excel files, you might want to take a look at
http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java
You can perform the same thing using Aspose.Cells: Create a template XLSX file, save to disk, then open file using ZipFile, and update the sheet1.xml file manually (e.g. using STAX or XStream to write the XML using a stream api)
It would be great if Aspose.Cells would extend the LightCell API to support writing using a streaming API !
Barry
Hi Barry,
Many thanks for the help and direction. We will investigate it thoroughly and will update you with possibilities accordingly.
Thanks,
Hi Barry,
In case of creating xlsx files, we use to push the data directly to the output stream rather then creating the resultant file into memory. So, please try to save the workbook directly to the HTTP output stream for saving xlsx files.
Thanks,
Hi Salman,
I believe the topic poster was having difficulties with the amount of memory required to populate the Excel workbook (i.e. not the Excel file itself, but the objects for representing the Workbook, Worksheet and Cell classes)
For example, if you need to populate an Excel sheet with the results from a database query (e.g. 200000 rows), then you could use the following algorithm to send this Excel file to a browser:
1) create Excel template file (without the data-rows), save to disk
2) open ZipFile, iterate over all file-entries
3) open new ZipOutputStream(servletRequest.getOutputStream())
4) copy all ZipEntries to ZipOutputStream except:
5) when the ZipEntry is for ‘xl/worksheets/sheet1.xml’, then create new XMLStreamWriter(zipOutputStream)
5a) Write Excel sheet header to XML stream
5b) for each row in the resultset, populate XML row
5c) write Excel sheet footer to XML stream
6) close xml stream, continue with copying the other ZIP entries
This way, you can stream very large Excel files to the HTTP response, or to a file, without requiring the entire Workbook to be represented in memory. It is similar to the LightCell API, but then for writing excel files, not reading.
Barry
Hi all,
Many thanks for help. Burrys advice to use OpenXML and Streaming XML Api for generating a very big sheet is really useful.Unfortunately this way we can generate books for only Office 2007 or higher. It would be great if there was possible to find solution for MS Office 2003 also.
Best regards,
Artem.
Hi,
Please try the new fix v2.4.1.7 (attached). In the new fix we have provided a new Interface(LightCellsDataProvider) for saving large dataset into the generated Excel file without building the complete data structure in the memory. The attachment JAVA file is a sample implementation of this interface based on data from database.
Thank you.
The issues you have found earlier (filed as 20137) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by aspose.notifier.
Thank you very much! We’ll try LightCells API in our project soon.
Artem.
Thank you for providing the solution but I am facing the problem for multisheet Excel file.
http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java
Above solution is working fine for Excel sheet without subsheet. Pl. provide the solution for multisheet Excel sheet
Thank you.
Hi,
Using Aspose.Cells’s LightCells API feature, you may write data to multiple worksheets in the Excel file for your needs. Please find attached the simplest example on how you may do it using Aspose.Cells LightCells APIs. The examples fills dummy data into 2 worksheets for specified rows/columns.
Thank you.
Thank you for solution but this is supporting only 65536 rows. My recquirment is about 10 Lakhs of rows which is supported by below approch
`http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java`.
But using this approch i am not able to write data into multiple subsheet of Excel sheet. So If any have some solution pl help me.
Thank you
Hi,
Since my attached demo is saving an XLS file which is Excel 97-2003 format, and this format cannot have more than 65536 rows (with 256 columns) --> This is the limitation of MS Excel’s XLS format and nothing to do with Aspose.Cells for Java component. If you need to write more than 65536 rows and more than 256 columns, please use XLSX (Excel 2007/2010 file formats) for your need.
e.g, you may change your code (after defining the rows count variable to your desired number) to:
OoxmlSaveOptions opt = new
OoxmlSaveOptions();
opt.setLightCellsDataProvider(dataProvider);
wb.save(“out_Demo1.xlsx”, opt);
Here I am trying to Zip the XML files and try to write the data into xlsx file . xlsx file is properly geneareted with two subsheet but while opening that xlsx file getting me error "Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded." Pl help me to out of this. I am attaching some code part below.
Thank you.
/**
zipfile is the xlsx file,tmpfile[] is of two XML files, entry[] is the subsheet names
/*
private static void substituteMultiple(File zipfile, File[] tmpfile, String[] entry, OutputStream out) throws IOException {
ZipFile zip =
new ZipFile(zipfile);
ZipOutputStream zos = null;
for (int i = 0; i < entry.length; i++){
zos = new ZipOutputStream(out);
@SuppressWarnings("unchecked")
Enumeration en = (Enumeration) zip.entries();
while (en.hasMoreElements()) {
ZipEntry ze = en.nextElement();
if(!ze.getName().equals(entry[i])){
zos.putNextEntry(new ZipEntry(ze.getName()));
InputStream is = zip.getInputStream(ze);
copyStream(is, zos);
zos.closeEntry();
is.close();
}
}
zos.putNextEntry(new ZipEntry(entry[i]));
InputStream is = new FileInputStream(tmpfile[i]);
copyStream(is, zos);
zos.closeEntry();
is.close();
en = null;
}zos.close();}
Hi I am trying to write lage data in Excel files and using the same step, I am generating the xlsx file with single sheet but I want to generate xlsx with two subsheet . Pl. help me to out of this , it's very urgent.
Thank you,
Hi,
Well, I am afraid, since you are using your own custom Java APIs e.g java.util.zip package and not using Aspose.Cells’s LightCells APIs, so we cannot help you. We can only recommend you to use Aspose.Cells’s LightCells APIs as demonstrated by the demo in the post:
If you find any issue regarding LightCells APIs, please give us complete sample code (similar to demo project) with template Excel file(s), we will check your issue soon.
Thank you.
Hi ,
I have saw you demo programe but I have different row count and column count in each subsheet. Also I am using xlsx format for large xlsx sheet and making changes as :
OoxmlSaveOptions opt = new OoxmlSaveOptions();
But I am getting error
"Exception in thread "main"java.lang.NoSuchMethodError:com/ctc/wstx/stax/WstxOutputFactory.configureForSpeed()V"
Pl. help me to getout of it and tell me the provision for as stated above.
Thank you.
Hi,
OoxmlSaveOptions is new API. My demo will work with new versions e.g v7.0.x.
Please download/use latest version (e.g v7.0.4) and set the main jars and other dependencies to your classpath before using the APIs into your project/program:
Thank you.