Got error when write to excel in java

Question

I can't figure it out what's the problem. I tried but errors are still exist.

my program is to export to excel and i'm using apache poi api.

Below is my code. EDIT:

public void exportToExcel(ValueObjectList columnBody, String pageDef, ValueObject vo){
    try {
    HttpServletResponse response = vo.getResponse();

     SimpleDateFormat sd = new SimpleDateFormat("ddMMyy");
     Date dt = new Date();
    response.setContentType("application/vnd.ms-excel");
    if(pageDef == "promo" || pageDef.equals("promo"))
        response.setHeader("Content-Disposition", "attachment; filename=PROMO-" + sd.format(dt) + ".xls");
    else if(pageDef == "incomplete" || pageDef.equals("incomplete"))
        response.setHeader("Content-Disposition", "attachment; filename=INCOM-" + sd.format(dt) + ".xls");
    else
         response.setHeader("Content-Disposition", "attachment; filename=ST-" + sd.format(dt) + ".xls");


    // create a small spreadsheet
     HSSFWorkbook wb = new HSSFWorkbook();
     HSSFSheet sheet = wb.createSheet();

     HSSFRow row = null;
     HSSFCell cell = null;

     //set default font properties
     //font family: Arial
     //font weight: bold
     Font headerFont = wb.createFont();
     headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

     headerFont.setFontHeightInPoints((short)14);

     //Cell Style for header
     CellStyle csHeader = wb.createCellStyle();
     csHeader.setFont(headerFont);
     csHeader.setBorderBottom(csHeader.BORDER_THICK);
     csHeader.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
     csHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
     csHeader.setWrapText(true);

     //Cell Style for body
     CellStyle csBody = wb.createCellStyle();
     csBody.setWrapText(true);
     csBody.setAlignment(HSSFCellStyle.ALIGN_CENTER);


     String[] columnHeader = ((ValueObject)columnBody.get(0)).toKeyArray();
     //System.out.println("Header Length: " + columnHeader.length);

     row = sheet.createRow(0);
     cell = row.createCell(0);
     cell.setCellValue("No");
     cell.setCellStyle(csHeader);

     for(int h = 0; h < columnHeader.length; h++){
         cell = row.createCell(h+1);
         cell.setCellValue(columnHeader[h]);
         cell.setCellStyle(csHeader);
         sheet.autoSizeColumn(h+1);
         //sheet.setColumnWidth(h, 2000);
     }

     //System.out.println("header key : " + columnHeader[2]);
    //System.out.println("header value : " + testobj.get(testobj.toKeyArray()[2]));

     for(int i = 0; i < columnBody.size(); i++){
         row = sheet.createRow(i+1);
         cell = row.createCell(0);
         cell.setCellValue(i+1);
         cell.setCellStyle(csBody);
         ValueObject column = (ValueObject)columnBody.get(i);
         for(int j = 0; j < column.size(); j++){
             cell = row.createCell(j+1);
             cell.setCellValue(column.get(column.toKeyArray()[j]));
             cell.setCellStyle(csBody);
             sheet.autoSizeColumn(j+1);
         }
     }


     /*
     // write it as an excel attachment
     ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
     wb.write(outByteStream);
     byte [] outArray = outByteStream.toByteArray();
     response.setContentType("application/ms-excel");
     response.setContentLength(outArray.length);
     response.setHeader("Expires:", "0"); // eliminates browser caching
     if(pageDef == "promo" || pageDef.equals("promo"))
        response.setHeader("Content-Disposition", "attachment; filename=PROMO-" + sd.format(dt) + ".xls");
     else if(pageDef == "incomplete" || pageDef.equals("incomplete"))
        response.setHeader("Content-Disposition", "attachment; filename=INCOM-" + sd.format(dt) + ".xls");
     else
         response.setHeader("Content-Disposition", "attachment; filename=ST-" + sd.format(dt) + ".xls");

     OutputStream outStream = response.getOutputStream();
     outStream.write(outArray);
     outStream.flush();
     */


     ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(wb.getBytes().length);
     wb.write(outByteStream);


    } catch (Exception e) {
        log.error(e);
        e.printStackTrace();
    }
}

I tried to surf in google and tried to solve but not ok.

At first, i made these codes in jsp.

when i surf google, people said i've to use in servlet so I moved to servlet but still got errors.

english is not my mother-tongue. sorry if i typed wrong.

Thanks in advance.

Answer 1

This is my JSP file Code

    <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body><%@ page import="java.io.*" %>
<%@ page import="org.apache.poi.ss.usermodel.Workbook"%>
<%@ page import="org.apache.poi.ss.usermodel.Cell"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFCell"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFRow"%>
<%@ page import="org.apache.poi.ss.usermodel.Row"%>
<%@ page import="org.apache.poi.ss.usermodel.Sheet"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
<%@ page import="java.sql.*"%>

<%!int i=1; %>
<%!String Id; %>
<%  Id=(String)request.getAttribute("id"); %>
<%HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
   try {
    java.sql.Connection con;
    Class.forName("com.mysql.jdbc.Driver");
    con = DriverManager.getConnection("jdbc:mysql://localhost:3306/custinfo","root","abc");
        Statement st= con.createStatement(); 
        out.println("hello world");
    ResultSet rs=st.executeQuery("select name ,state ,balance,description from customerdata where customerid='"+Id+"'"); 

    HSSFRow row = sheet.createRow((short)0);
    row.createCell((short)0).setCellValue("NAME");
    row.createCell((short)1).setCellValue("STATE");
    row.createCell((short)2).setCellValue("BALANCE");
    row.createCell((short)3).setCellValue("DESCRIPTION");
    while(rs.next())
    {
         out.println("hello world data");       
        HSSFRow row1 = sheet.createRow((short)i);
        row1.createCell((short)0).setCellValue(rs.getString("name"));
        row1.createCell((short)1).setCellValue(rs.getString("state"));
     row1.createCell((short)2).setCellValue(rs.getString(3));
     row1.createCell((short)3).setCellValue(rs.getString(4));
     i=i+1;
    sheet.autoSizeColumn((short)1); 

    }

   }
  catch(SQLException e) {
    out.println("SQLException caught: " +e.getMessage());
  }%>
// create a small spreadsheet
<%

%>
<% 

ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
wb.write(outByteStream);
byte [] outArray = outByteStream.toByteArray();
response.setContentType("application/ms-excel");
response.setContentLength(outArray.length);
response.setHeader("Expires:", "0"); // eliminates browser caching
response.setHeader("Content-Disposition", "attachment; filename=testxls.xls");
OutputStream outStream = response.getOutputStream();
outStream.write(outArray);
outStream.flush();

%>
</body>
</html>

And in my Action class I have given this

<action name="DownloadExcel" class="bank.ReportGenerator" method="downloadExcel">
            <result name="success">/download.jsp</result>

        </action>

And in class ReportGenerator I am Giving

package bank;
import com.opensymphony.xwork2.ActionSupport;
import java.sql.*;
import java.util.Collection;
import java.util.Iterator;
import java.util.*;

public class ReportGenerator extends ActionSupport {


    private String id;

    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String pieChart() throws Exception
    {
        return SUCCESS;


    }
    public String downloadExcel() throws Exception
    {
        return SUCCESS;


    }

}

you can change the query and database in the code to make it work for you.Only JSP file is enough to generate a excel file with content.

Answer 2

java.lang.IllegalStateException: getOutputStream() has already been called for this response

is because of these code

// write it as an excel attachment
     ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
     wb.write(outByteStream); // This writes  workbook to put put stream

     // Perform these before wb.write(outByteStream);
     byte [] outArray = outByteStream.toByteArray();
     response.setContentType("application/ms-excel");
     response.setContentLength(outArray.length);
     response.setHeader("Expires:", "0"); // eliminates browser caching
     if(pageDef == "promo" || pageDef.equals("promo"))
        response.setHeader("Content-Disposition", "attachment; filename=PROMO-" + sd.format(dt) + ".xls");
     else if(pageDef == "incomplete" || pageDef.equals("incomplete"))
        response.setHeader("Content-Disposition", "attachment; filename=INCOM-" + sd.format(dt) + ".xls");
     else
         response.setHeader("Content-Disposition", "attachment; filename=ST-" + sd.format(dt) + ".xls");
     OutputStream outStream = response.getOutputStream();
     outStream.write(outArray);
     outStream.flush();
     outStream.close();

EDIT: The method write() --> write out this workbook to an Outputstream.

Check API : http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFWorkbook.html#write%28java.io.OutputStream%29

So, This has already sent output/ response and then you are trying to set response and get OutputStream from response.

There are not any references or code related Second error javax.servlet.ServletException: File "/common/err/errorPage.jsp" not found in the question.

It might be because you are trying to forward or redirect to errorPage.jsp from your Servlet which does not exist at given path.

Answer 3

I'm guessing your error is related to this bit of code (not apache poi api):

OutputStream outStream = response.getOutputStream();
outStream.write(outArray);
outStream.flush();
outStream.close();

Because you are flushing and closing the output stream in your servlet, anything that tries to write to the output stream before the response is returned to the client will cause an IllegalStateException to be thrown. Usually best to leave the flush() and close() to the servletcontainer unless you really know what you're doing.
Try removing the flush() and close(), and check if other servlets you have are doing the same thing.