JSP generating Excel spreadsheet (XLS) to download

Question

Hey,

I have this application i'm developing in JSP and i wish to export a couple of data from the database into XLS (MS Excel format).

Is it possible under tomcat to just write a file as if it was a normal java aplication, and then generate a link to this file? Or do i need to use a specific API for it?

Will i have permition problems when doing this?

Thank you all, fmsf

Answer 1

You will probably need a library to manipulate Excel files, like JExcelAPI ("jxl") or POI. I'm more familiar with jxl and it can certainly write files. You can generate them and store them by serving a URL to them but I wouldn't. Generated files are a pain. They add complication in the form on concurrency, clean-up processes, etc.

If you can generate the file on the fly and stream it to the client through the standard servlet mechanisms.

If it's generated many, may times or the generation is expensive then you can cache the result somehow but I'd be more inclined to keep it in memory than as a file. I'd certainly avoid, if you can, linking directly to the generated file by URL. If you go via a servlet it'll allow you to change your impleemntation later. It's the same encapsualtion concept as in OO dsign.

Answer 2

maybe you should consider using some reporting tool with an option of exporting files into XLS format. my suggestion is JasperReports

Answer 3

While you can use a full fledged library like JExcelAPI, Excel will also read CSV and plain HTML tables provided you set the response MIME Type to something like "application/vnd.ms-excel".

Depending on how complex the spreadsheet needs to be, CSV or HTML can do the job for you without a 3rd party library.

Answer 4

POI is an excellent tool for this task. But you will likely want to write a servlet in Java and call that servlet in your HTML. A full article about how to do this can be found at DevX.

Answer 5

SmartXLS for java is a good java excel component which can generate the excel report for you.

Answer 6

Don't use plain HTML tables with an application/vnd.ms-excel content type. You're then basically fooling Excel with a wrong content type which would cause failure and/or warnings in the latest Excel versions. It will also messup the original HTML source when you edit and save it in Excel. Just don't do that.

CSV in turn is a standard format which enjoys default support from Excel without any problems and is in fact easy and memory-efficient to generate. Although there are libraries out, you can in fact also easily write one in less than 20 lines (funny for ones who can't resist). You just have to adhere the RFC 4180 spec which basically contains only 3 rules:

1. Fields are separated by a comma.
2. If a comma occurs within a field, then the field has to be surrounded by double quotes.
3. If a double quote occurs within a field, then the field has to be surrounded by double quotes and the double quote within the field has to be escaped by another double quote.

Here's a kickoff example:

public static <T> void writeCsv (List<List<T>> csv, char separator, OutputStream output)
    throws IOException
{
    BufferedWriter writer = null;
    try {
        writer = new BufferedWriter(new OutputStreamWriter(output, "UTF-8"));
        for (List<T> row : csv) {
            StringBuilder line = new StringBuilder();
            for (Iterator<T> iter = row.iterator(); iter.hasNext();) {
                String field = String.valueOf(iter.next()).replace("\"", "\"\"");
                if (field.indexOf(separator) > -1 || field.indexOf('"') > -1) {
                    field = '"' + field + '"';
                }
                line.append(field);
                if (iter.hasNext()) {
                    line.append(separator);
                }
            }
            writer.write(line.toString());
            writer.newLine();
        }
    } finally {
        if (writer != null) try { writer.close(); } catch (IOException ignore) {}
    }
}

Here's an example how you could use it:

public static void main(String[] args) throws IOException {
    List<List<String>> csv = new ArrayList<List<String>>();
    csv.add(Arrays.asList("field1", "field2", "field3"));
    csv.add(Arrays.asList("field1,", "field2", "fie\"ld3"));
    csv.add(Arrays.asList("\"field1\"", ",field2,", ",\",\",\""));
    writeCsv(csv, ',', System.out);
}

And inside a Servlet (yes, Servlet, don't use JSP for this!) you can basically do:

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    String filename = request.getPathInfo();
    List<List<Object>> csv = someDAO().list();
    response.setHeader("content-type", "application/csv");
    response.setHeader("content-disposition", "attachment;filename=\"" + filename + "\"");
    writeCsv(csv, ';', response.getOutputStream());
}

Map this servlet on something like /csv/* and invoke it as something like http://example.com/context/csv/filename.csv. That's all.

Note that I added the possiblity to specify the separator character separately, because it may depend on the locale used whether Excel would accept a comma , or semicolon ; as CSV field separator. Note that I also added the filename to the URL pathinfo, because a certain webbrowser developed by a team in Redmond otherwise wouldn't save the download with the proper filename.