Forum:

Servlets

Zip and excel generated on the fly

Greenhorn

Posts: 6

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I am generating excel on the fly using a sample snippet given below. for 1 case its size is becoming more than 50mb and is not opening. Download box dissappears while downloading. So i m trying to zip that excel.

// code to export to excel.

String excelMainRowData = null;
ExportToExcelVO vo = new ExportToExcelVO();
ExportToExcelBean expExclBean = new ExportToExcelBean();
ArrayList<ExportToExcelVO> listVO = new ArrayList<ExportToExcelVO>();

ArrayList<String> listRows = new ArrayList<String>();

String fileName = "Promotion Eligibility Report.xls";
String fileHeader = "" + "Promotion Eligibility Report as on Date : ";
vo.setNoOfRowsBetweenRows(0);
for(int j = 0, size = alldata.size(); j < size; j++){ // alldata contains all data from db
listRows.add(excelMainRowData); // excelMainRowData contains data for each row
}

vo.setListRowsToExcel(listRows);
listVO.add(vo);
FacesContext fc = FacesContext.getCurrentInstance();
HttpServletResponse response = (HttpServletResponse) fc
.getExternalContext().getResponse();

ExportToExcelVO vo;
boolean result = false;
String contentType = "application/vnd.ms-excel";
response.setHeader("Content-disposition", "attachment; filename="
+ fileName);
response.setContentType(contentType);
try {
PrintWriter out = response.getWriter();
out.println("<html><body> ");
if (!(fileHeading.equalsIgnoreCase(""))) {
out.println(fileHeading + " ");
}
for (int k = 0; k < listVO.size(); k++) {
vo=listVO.get(k);
if (!(vo.getStrHeading().equalsIgnoreCase(""))) {
out.println(vo.getStrHeading()+" ");
}
if (!(vo.getStrTableHeading().equalsIgnoreCase(""))) {
out.println(vo.getStrTableHeading());
}
out.println("<hr> <table border='1' cellpadding='1' cellspacing='1'>");
for (int i = 0; i < vo.getListRowsToExcel().size(); i++) {
out.println("<tr>"+vo.getListRowsToExcel().get(i)+"</tr>");
for (int j = 0; j < vo.getNoOfRowsBetweenRows(); j++) {
out.println(" ");
}
out.println("</tr>");
}
out.println("</table>");
out.println(" ");
}

out.println("</html></body>");
out.close();
fc.responseComplete();

// Now i want to zip this file on runtime. I am not able to use FileInputStream fis = new FileInputStream(filename);
because file doesnot exist on the machine. Its generated on runtime. Please suggest how to zip on the fly

Ulf Dittmer

Rancher

Posts: 43081

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Just to nitpick: You're not creating an Excel file. You're creating an HTML file that happens to be readable by Excel - a big difference.

Instead of println to the response writer you should append into a StringBuilder. Then you can zip it along these lines, and stream the bytes to the client using the response output stream.

ank Gupta

Greenhorn

Posts: 6

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Thanks a top for your reply. But if i want to generate html i need to write response to print writer. How will Stringbuilder help me? and how do we convert an printwriter response into bytes?

Ulf Dittmer

Rancher

Posts: 43081

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

But if i want to generate html i need to write response to print writer.

No. HTML is just string data - you can create it any way you want. But you need to decide first if you want to emit HTML for a browser to display, or if you want zipped-up HTML for Excel to open. Your previous post said you wanted the latter; is that still the case?

You can get bytes from a StringBuilder by calling its toString method, and then calling getBytes on the resulting String. Be sure to specify the correct encoding, so that Excel is able to read it.

ank Gupta

Greenhorn

Posts: 6

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

yes actually i want zipped-up HTML for Excel to open. So basically you suggest to use string builder, convert to bytes, do the compression and then render output using response output stream ?

Ulf Dittmer

Rancher

Posts: 43081

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Yes.

ank Gupta

Greenhorn

Posts: 6

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I used following snippet as suggested. For small data i did not got data in excel and for large data (71000 records) i got java.lang.OutOfMemoryError: Java heap space error. Please check the code and suggest.

try {
StringBuilder sb = new StringBuilder();
sb.append("<html><body> ");
if (!(fileHeading.equalsIgnoreCase(""))) {
sb.append(fileHeading + " ");
}
for (int k = 0; k < listVO.size(); k++) {
vo=listVO.get(k);
if (!(vo.getStrHeading().equalsIgnoreCase(""))) {
sb.append(vo.getStrHeading()+" ");
}
if (!(vo.getStrTableHeading().equalsIgnoreCase(""))) {
sb.append(vo.getStrTableHeading());
}
sb.append("<hr> <table border='1' cellpadding='1' cellspacing='1'>");
for (int i = 0; i < vo.getListRowsToExcel().size(); i++) {
sb.append("<tr>"+vo.getListRowsToExcel().get(i)+"</tr>");
for (int j = 0; j < vo.getNoOfRowsBetweenRows(); j++) {
sb.append(" ");
}
sb.append("</tr>");
}
sb.append("</table>");
sb.append(" ");
}
sb.append("</html></body>");
byte[] input = sb.toString().getBytes();
// Create the compressor with highest level of compression
Deflater compressor = new Deflater();
compressor.setLevel(Deflater.BEST_COMPRESSION);

// Give the compressor the data to compress
System.out.println("b4compression");
compressor.setInput(input);
compressor.finish();

// Create an expandable byte array to hold the compressed data.
// You cannot use an array that's the same size as the orginal because
// there is no guarantee that the compressed data will be smaller than
// the uncompressed data.
ByteArrayOutputStream bos = new ByteArrayOutputStream(input.length);

// Compress the data
byte[] buf = new byte[1024];
while (!compressor.finished()) {
int count = compressor.deflate(buf);
bos.write(buf, 0, count);
}
System.out.println("after compression");
try {
bos.close();
} catch (IOException e) {
}

// Get the compressed data
byte[] compressedData = bos.toByteArray();

ServletOutputStream out = response.getOutputStream();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment; filename="
+ fileName);
out.println(compressedData.toString());
out.close();
} catch (IOException e) {
logger.error("IO EXCEPTION in Export To Excel Bean");
e.printStackTrace();
result = false;
}

Ulf Dittmer

Rancher

Posts: 43081

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

You need to get in the habit to UseCodeTags. The code is unnecessarily hard to read as it is, reducing the likelihood that people will try to read and make sense of it.

If you don't have enough memory you may have to do this on disk. Write the HTML to a file, and then compress that. That way you can control how much memory is used (by adjusting the buffer size in your code).

By the way, this won't work for two reasons:

Firstly, calling toString on an array does not what you think it does. Print it out to see what it's actually doing.
Secondly, you can't treat binary data like text. You can't use a Writer for sending it to the client; you need to use the response stream, as I said in my previous post.
[ August 20, 2008: Message edited by: Ulf Dittmer ]

ank Gupta

Greenhorn

Posts: 6

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Now i am totally confused

I am using response output stream (ServletOutputStream out = response.getOutputStream()

. Are you talking about using BufferedInputStream and BufferedOutputStream. Actually i havent worked much on these streaming classes so a bit clueless. How do i write HTML to a file and how do i adjust buffer size? If i write html to file then will i give URL of file in input stream using getInputStream. Your help is highly appreciated.

Ulf Dittmer

Rancher

Posts: 43081

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Are you talking about using BufferedInputStream and BufferedOutputStream.

No, I'm talking about the code using "println". You need to use "write(byte[])".

How do i write HTML to a file

Basic code to write to a file. Note that web apps have no concept of a default directory (as this code uses) - you need to use an absolute path.

and how do i adjust buffer size?

After you have zipped the file on disk, you need to read it for streaming to the browser. During read you need a byte[] for temporarily holding the data in memory; the size of this buffer can be as large

If i write html to file then will i give URL of file in input stream using getInputStream.

No. You'll read the file contents into memory, an dthen stream that to the browser. Here's an example of how to read a file. Note that you do NOT want to read all of the file into memory -because that might cause memory problems, as you have found out- but send it to the servlet output stream block by block.

Taking a step back, it seems to me that it may be simpler to investigate why the original approach of streaming the HTML to the browser didn't work. There is no inherent limit on how big a page can be (provided the browser doesn't time out).
You may also be able to configure your web server/servlet container to use GZIP compression on the fly; Tomcat is one that can do this.

Don't get me started about those stupid light bulbs.