File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes I/O and Streams and the fly likes generate large excel using zip stream Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Java » I/O and Streams
Bookmark "generate large excel using zip stream" Watch "generate large excel using zip stream" New topic
Author

generate large excel using zip stream

gendhe ranjith kumar
Greenhorn

Joined: Mar 28, 2011
Posts: 20
hi

we are using the following code to generate excel from csv.

http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java

in that they are creating a temporary xml file. later they copy data from xml file to zip file.

how to write data to zip with out storing data in temporary xml file.


Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41599
    
  55
Whatever you write to disk using some sort of OutputStream, you can also write to memory using a ByteArrayOutputStream. Same for reading data using a ByteArrayInputStream instead of some other InputStream.


Ping & DNS - my free Android networking tools app
gendhe ranjith kumar
Greenhorn

Joined: Mar 28, 2011
Posts: 20
thanks for giving response

could please give one simple example on this.

Thanks & Regards

Ranjith.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41599
    
  55
I think you'll learn more if you first try it yourself. Using streams is a crucial Java skill to have; if you're unfamiliar with them, there's a Java Tutorial chapter about that. If you get stuck, post the code that you're trying to get to work, and we'll try to help.
gendhe ranjith kumar
Greenhorn

Joined: Mar 28, 2011
Posts: 20
thanks for your response

i am working on that.


Thanks & Regards

Ranjith.
gendhe ranjith kumar
Greenhorn

Joined: Mar 28, 2011
Posts: 20
hi

i want to write the two string objects into the zip file.
i am using the following code.


this program runs

but it overrides contents of zip file and contains last String Object only .

how to append the data to the zip file?

Thanks & Regards
Ranjith.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41599
    
  55
You can't append to a ZIP file. If you want to add another file to a ZIP file, then you need to unzip it first, and then re-zip all existing files along with the new one.
gendhe ranjith kumar
Greenhorn

Joined: Mar 28, 2011
Posts: 20
hi

thanks for response

our requirement is:

we are using the following code to generate excel from csv.

http://svn.apache.org/repos/asf/poi/trunk/src/exam...odel/examples/BigGridDemo.java

in that they are creating a temporary xml file. later they copy data from xml file to zip file.

it takes more time for 30,000,000 rows and 150 columns.

we read the csv file line by line.
to avoid creating of temporary xml file.
after reading every line from csv we call the substitute(...) method
it will overrides contents of zip file.

in case of files

try
{
String filename= "C:\\book1.txt";
boolean append = true;
FileWriter fw = new FileWriter(filename,append);

fw.write("add a line\n");//appends the string to the file
fw.close();
}

catch(IOException ioe)
{
System.err.println("IOException: " + ioe.getMessage());
}

this one is working.

is there any other way to write data to zip file without losing the content.

Thanks & Regards
Ranjith.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41599
    
  55
As I said, you can't append to zip files. But if you only want a single zip file, why are you opening and closing it repeatedly to begin with? Why aren't you writing all the data that is supposed to go into it in one go? Furthermore, if you want an Excel file, why are you creating a ZIP file in between?
gendhe ranjith kumar
Greenhorn

Joined: Mar 28, 2011
Posts: 20


we need to populate an Excel-2007 sheet with the results from a csv (e.g. 40,00,000 rows),

we can open the xlsx file in zip streams(Create xlsx file and rename it to zip and open with zip tool)

then we 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

if the csv file contains large data. then we create a temporary sheet.xml in server. later we copy content to the zip file.

by doing this one we are spending extra time to copy from xml to zipfile.


Thanks & Regards
Ranjith.



Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41599
    
  55
Frankly, now I'm not following at all what you intend to do. The approach of treating an XLSX file as a ZIP file seems pointless to me, given that the POI library can handle XLSX.

Can you describe in few words what the end result of all this is supposed to be? Streaming an XLSX file to a web browser? If so, why are you trying to avoid using POI?
gendhe ranjith kumar
Greenhorn

Joined: Mar 28, 2011
Posts: 20
please check the link

http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java

there are some reasons to avoid poi.

1. for jdk 1.4 poi supports only xls.

2. poi does not support to generate pivot table.

3.some servers take more memory to generate sheets

steps:


1) create Excel template file (without the data-rows) with aspose api, 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 csv, populate XML row (using poi cellReference() class)
5c) write Excel sheet footer to XML stream
6) close xml stream, continue with copying the other ZIP entries


after read the excel file using asposeAPI

and create pivot table for sheet1.


by using this approach we can genarate xlsx file havaing 30,00,000 rows and 140 cols in less than 25 mins.

Thanks & Regards.
Ranjith.
Wim Folkerts
Greenhorn

Joined: Mar 10, 2010
Posts: 19

Hello Gendhe,

A couple of days ago I managed to get a .zip download working where .xls files are added to the .zip archive. The .xls file are not written to the filesystem which saves you a FileInputStream implementation and a ByteInputStream that needs to iterate. The logic is quite simple and might be useful for you. Btw I'm using the
to generate the Excel sheet. Below in short the idea:


Hope this helps,
Wim
 
wood burning stoves
 
subject: generate large excel using zip stream