In this article lets discuss about returning an excel file as part of Http Response.
Consider the following table. Lets return an excel sheet containing the following details as an Http response.
| EMP_ID | EMP_NAME | AGE | SALARY | DEPARTMENT |
|---|---|---|---|---|
| 1001 | George | 35 | 50000 | IT |
| 1002 | Patrick | 41 | 60000 | FINANCE |
| 1003 | Haggai | 32 | 45000 | HR |
| 1004 | Jones | 29 | 40000 | MARKETING |
| 1005 | Miller | 38 | 54000 | SALES |
Have a look at the servlet class below. In the doGet() method it sets the response content type as "application/xls" and also sets attachment headers with excel sheet file name. It the returns the response to a welcome.jsp file.
ExcelResponseServlet.java
* THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS''
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
* TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
* PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
* BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*/
package in.techdive.servlet;
import in.techdive.java.Employee;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class ExcelResponseServlet extends HttpServlet
{
/*
* (non-Javadoc)
* @see javax.servlet.http.HttpServlet#doGet(javax.servlet.http.HttpServletRequest,
* javax.servlet.http.HttpServletResponse)
*/
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException
{
List<Employee> lstEmp = new ArrayList<Employee>();
List<String> headerLst = new ArrayList<String>();
Employee emp1 = new Employee(1001, "George", 35, 50000, "IT");
Employee emp2 = new Employee(1002, "Patrick", 41, 60000, "FINANCE");
Employee emp3 = new Employee(1003, "Haggai", 32, 45000, "HR");
Employee emp4 = new Employee(1004, "Jones", 29, 40000, "MARKETING");
Employee emp5 = new Employee(1005, "Miller", 38, 54000, "SALES");
lstEmp.add(emp1);
lstEmp.add(emp2);
lstEmp.add(emp3);
lstEmp.add(emp4);
lstEmp.add(emp5);
headerLst.add("EMP_ID");
headerLst.add("EMP_NAME");
headerLst.add("AGE");
headerLst.add("SALARY");
headerLst.add("DEPARTMENT");
req.setAttribute("emplst", lstEmp);
req.setAttribute("headerLst", headerLst);
resp.setContentType("application/xls");
resp.setHeader("Content-Disposition", "attachment; filename=" + "new.xls");
getServletConfig().getServletContext().getRequestDispatcher("/welcome.jsp").forward(req, resp);
}
/*
* (non-Javadoc)
* @see javax.servlet.http.HttpServlet#service(javax.servlet.http.HttpServletRequest,
* javax.servlet.http.HttpServletResponse)
*/
@Override
protected void service(HttpServletRequest arg0, HttpServletResponse arg1) throws ServletException, IOException
{
super.service(arg0, arg1);
}
}
Take a look at the jsp file below.
excel.jsp
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Excel Response</title>
</head>
<body>
<TABLE border="1">
<tr>
<c:forEach var="headerItems" items="${requestScope.headerLst}">
<th>
<b><c:out value="${headerItems}" /> </b>
</th>
</c:forEach>
</tr>
<c:forEach var="item" items="${requestScope.emplst}">
<tr>
<td><c:out value="${item.empId}" /></td>
<td><c:out value="${item.empName}" /></td>
<td><c:out value="${item.age}" /></td>
<td><c:out value="${item.salary}" /></td>
<td><c:out value="${item.department}" /></td>
</tr>
</c:forEach>
</TABLE>
</body>
</html>
It uses jstl core tags to iterate the 'emplst' and display each employee object attributes. These things will be in turn printed in the excel sheet which returned as a part of response.
Here is the web.xml file for your reference.
web.xml
<servlet-name>excel</servlet-name>
<servlet-class>
in.techdive.servlet.ExcelResponseServlet
</servlet-class>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>excel</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>