asked on

A servlet to output a .xls file

Dear all,

I have created a servlet that outputs an .xls spreadsheet on the fly.

When I call it on my Linux box, I have Open Office that is fired up.

But Open Office displays a filter popup window before opening the spreadsheet.

This popup window is a filter selector and asks me to choose which type of format to use.

I chose Open Office, or MS Excel, but whichever, I got the same message:

"Read error: unknown or unsupported Excel file format"

And I can view the file.

I must miss something when creating the spreadsheet.

Below is the servlet source code.

It's a demo dummy servlet.

stephaneeybert

ASKER

package com.ericsson.pii.web;

import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.Collections;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.portlet.PortletException;
import javax.portlet.RenderRequest;
import javax.portlet.RenderResponse;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.jfree.chart.JFreeChart;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.xml.XmlBeanFactory;
import org.springframework.core.io.ClassPathResource;

import com.ericsson.pii.domain.Document;
import com.ericsson.pii.domain.DocumentListResult;
import com.ericsson.pii.dto.Criteria;
import com.ericsson.pii.service.DocumentListService;
import com.ericsson.pii.service.DocumentsAndUsagesService;
import com.ericsson.pii.service.IndividualDocumentAccessListService;
import com.ericsson.pii.service.DocCategoryService;
import com.ericsson.pii.util.SessionUtil;
import com.ericsson.pii.util.SpringUtil;

public class DocumentListExcelGeneratorServlet extends HttpServlet {

      private static final long serialVersionUID = 1L;

      BeanFactory beanFactory = null;

      private DocumentListService documentListService;

      protected void processRequest(HttpServletRequest request,
                  HttpServletResponse response) throws ServletException,
                  java.io.IOException {

            request.getSession(false);

            response.setContentType("application/msexcel");

            HSSFWorkbook wb = new HSSFWorkbook();
       HSSFSheet sheet = wb.createSheet("Document List");

       // Create a row and put some cells in it. Rows are 0 based.
       HSSFRow row = sheet.createRow((short)0);
       // Create a cell and put a value in it.
       HSSFCell cell = row.createCell((short)0);
       cell.setCellValue(1);

       // Or do it on one line.
       row.createCell((short)1).setCellValue(1.2);
       row.createCell((short)2).setCellValue("This is a string");
       row.createCell((short)3).setCellValue(true);

       // Write the output to a file
       FileOutputStream fileOut = new FileOutputStream("workbook.xls");
       wb.write(fileOut);
       fileOut.close();

      }

      protected void doGet(HttpServletRequest request,
                  HttpServletResponse response) throws ServletException,
                  java.io.IOException {
            processRequest(request, response);
      }

      protected void doPost(HttpServletRequest request,
                  HttpServletResponse response) throws ServletException,
                  java.io.IOException {
            processRequest(request, response);
      }

      public String getServletInfo() {
            return "Short description";
      }

      private Object getBean(String beanName) {
            if (null == beanFactory) {
                  ClassPathResource classPathResource = new ClassPathResource("applicationContext.xml");

                  beanFactory = new XmlBeanFactory(classPathResource);
            }

            Object result = beanFactory.getBean(beanName);

            return result;
      }

}

ASKER CERTIFIED SOLUTION

mrcoffee365

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chakra-march-04

can try this.........
may be some modification reruired according to your code............
.....................

response.setContentType("application/vnd.ms-excel");
response.setHeader("Cache-Control", "public");
response.setHeader("Pragma", "public");
response.setHeader("Expires", "0");
response.setHeader("Content-disposition","attachment; filename=\""+filename+"\"");//this is optional line

HSSFWorkbook workBook = ............//your code

ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();

ServletOutputStream outStr = response.getOutputStream();
workBook.write(byteArrayOut);
byte[] bytesOut = byteArrayOut.toByteArray();
response.setContentLength(bytesOut.length);
outStr.write(bytesOut);
outStr.flush();

stephaneeybert

ASKER

Thanks for your postings!

I have found out about the problem...

I replaced the following line:
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
with
OutputStream out = response.getOutputStream();

And it worked!

I also added the mime type in the web.xml file and the applicaton/vnd-ms.excel content type, although these were not the stumbloing block, as it seemed.

Since I made use of mrcoffee365 suggestions I feel he can have the points.

Thanks to you too chakra-march-04 for your suggestion.

Cheers!

mrcoffee365

Congratulations.

Glad we could help.