How to use JSP/JSTL to create a CSV file

Question

Hi, I have created a jsp file, with a simple table on it.

I would like to create another jsp file that users can open in Excel or save as an xls.

This is my entire jsp file, this creates a csv file which opens in Excel when a link is clicked:

<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt"uri="http://java.sun.com/jsp/jstl/fmt" %>
<% response.setContentType("application/vnd.ms-excel");
   response.setHeader("Content-Disposition","attachment; filename=" + "online-dashboard.csv" ); %>

<jsp:useBean id="ReportInfo" class="com.reports.ReportLister"  scope="request" />
${ReportInfo.reportType},M,W,Other
<c:forEach var="rrow" items="${ReportInfo.list}"  varStatus="rowCounter">
    ${rrow.subjectCode},${rrow.MCount},${rrow.WCount},${rrow.OCount}
</c:forEach>
Totals,${ReportInfo.totalMSections},${ReportInfo.totalWSections},${ReportInfo.totalOSections}

When I open it in Excel, each row is separated by 2 lines.

Is there an easy way to create an excel file this way?

Is there an easy way to add some formatting ( like bold text for the column headers )?

Answer 1

A better way would be Spring and its JExcelView.

Answer 2

In order to create a .xls file, you'll need something heavier-duty than JSTL. Apache POI and JExcelApi are two open-source libraries for generating native Excel format files. POI can generate both .xls and .xlsx; I don't have experience with JExcelApi, but it appears to only support .xls.

As duffymo alluded to, Spring has an AbstractExcelView which you can extend and use with either library. If you're not using Spring, though, you can still use one of the libraries to generate a Workbook object and write its contents to the OutputStream of a ServletResponse. They will also let you format your data in various ways (including bold text) and even create comments and other Excel elements.

For a .xls file you'll want to set the content-type to application/vnd.ms-excel, and for .xlsx application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.

Answer 3

The reason you're getting empty lines is that JSP is rendering empty lines inside your loop. You could eliminate them by packing your loop into one line:

<c:forEach var="rrow" items="${ReportInfo.list}"  varStatus="rowCounter">${rrow.subjectCode},${rrow.MCount},${rrow.WCount},${rrow.OCount}</c:forEach>

Or you could add a servlet filter that would strip empty lines from the response.

However, if you want to add special formatting, I believe that goes beyond the comma-separated values format, and you'd need to generate excel native files as suggested by others.

EDIT: Instead of packing your loop into one line, try adding the following directive to your page:

<%@page trimDirectiveWhitespaces="true"%>

Answer 4

An easy way is to use the fact that Excel can understand HTML. So simply format your data as an HTML Table and send it as an XLS file. Something like

<table>
<c:forEach var="rrow" items="${ReportInfo.list}"  varStatus="rowCounter">
<tr><td><b>${rrow.subjectCode}</b></td>
    <td>${rrow.MCount}</td>
    <td>${rrow.WCount}</td>
    <td>${rrow.OCount}</td></tr>
</c:forEach>
</table>

Answer 5

JSP is not entirely the right tool for this. Use a servlet class. To save some bytes in Stackoverflow's database, here's just a link to an answer I posted before on the subject: Export to excel using JSP.

---

That said, I have a few comments on your code and the other answers:

1. Do not use XLS content type when returning CSV. This will lead to warnings during opening the file. Use the CSV content type: text/csv. Excel perfectly understands this.

2. Do not render HTML along a wrong content type (the one indicating a XLS(X) file). You're then basically fooling Excel. Since Excel version 2007, it will display warnings about that.