2

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 )?

Improve this question

5 Answers 5

Reset to default
2

A better way would be Spring and its JExcelView.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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>
Improve this answer

1 Comment

Thank you, that is just the simple trick that I was looking for. I set the header to use a filename ending in xsl, and it worked perfectly.
1

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"%>
Improve this answer

Comments

0

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.

Improve this answer

3 Comments

I recommend JExcelAPI over POI (in my not-terribly-recent experience it uses far less memory), though they're both pretty solid.
According to the Spring docs, "We've found that the JExcelApi is somewhat more intuitive and furthermore, JExcelApi has a bit better image-handling capabilities. There have been memory problems with large Excel file when using JExcelApi however." I found this in the spring 2.0 documentation, which may be more or less current than your experience.
Andy Khan's JExcel is indeed far superior to POI. That's what Spring is using for its ExcelView.
0

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.

Improve this answer

1 Comment

I wouldn't quite say that Excel "perfectly understands" CSV. It seems to behave well enough with ASCII CSV, but it barfs on non-ASCII UTF-8. It will accept tab-delimited (not comma-delimited) UTF-16 with BOM. It's a bit of a mess, and LibreOffice is considerably better.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.