generate large excel using zip stream

[gendhe ranjith kumar]

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]

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.

[gendhe ranjith kumar]

thanks for giving response

could please give one simple example on this.

Thanks & Regards

Ranjith.

[Ulf Dittmer]

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]

thanks for your response

i am working on that.


Thanks & Regards

Ranjith.

[gendhe ranjith kumar]

hi

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

public class DataToZip{
		public static void main(String[] args) {
			try {					
				String text = "In Xanadu did Kubla Khan A stately pleasure-dome decree:
				Where Alph, the sacred river, ran Through caverns measureless to man Down to a sunless sea.";
				substitute(new File("c:\\data.zip"),  "c:\\book1.txt",text);
				text = "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. ";
				substitute(new File("c:\\data.zip"),  "c:\\book1.txt",text);
						
			} catch (IOException e) {
				e.printStackTrace();
			}
		}


private static void substitute(File zipfile,  String entry ,String text) throws IOException {
    ZipFile zip = new ZipFile(zipfile);

    ZipOutputStream zos = new ZipOutputStream(new FileOutputStream("c:\\data1.zip"));

    @SuppressWarnings("unchecked")
    Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) zip.entries();
    while (en.hasMoreElements()) {
    	ZipEntry ze = en.nextElement();
        if(!ze.getName().equals(entry)){
            zos.putNextEntry(new ZipEntry(ze.getName()));
            InputStream is = zip.getInputStream(ze);
            copyStream(is, zos);
            is.close();
        }
    }
    zos.putNextEntry(new ZipEntry(entry));
    InputStream is = new ByteArrayInputStream(text.getBytes("UTF-8"));
    copyStream(is, zos);
    is.close();

    zos.close();
}

private static void copyStream(InputStream in, OutputStream out) throws IOException {
    byte[] chunk = new byte[18024];
    int count;
    while ((count = in.read(chunk)) >=0 ) {
      out.write(chunk,0,count);
    }
}
}

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]

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]

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]

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]

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]

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]

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]

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

import org.apache.poi.ss.usermodel.Workbook;

to generate the Excel sheet. Below in short the idea:

//prepare outputStreams
ServletOutputStream op = response.getOutputStream();
ZipOutputStream zipOut = new ZipOutputStream(op);

//generate tags export sheet based on a template sheet
tags.loadTemplate(appContextPath + "/importExport/");
//get the data based on a HashMap containing the parameters
tags.loadEDSData(hmParameters);
//create a workbook object
Workbook tagsWb = tags.getWorkbook();

//add tag export into .zip archive
zipOut.putNextEntry(new ZipEntry(tags.getFilename()));
tagsWb.write(zipOut);
zipOut.closeEntry();
tagsWb = null;

//you can add more entries here...

//close the streams
zipOut.finish();
zipOut.close();   
op.close();

Hope this helps,
Wim