JSP page without HTML code for exporting data to Excel Sheet

Question

Hi friends, I am facing a problem in exporting my data to excel sheet, this is because of some code which other developers in my team made. So the main problem is to export the data to Excel or .cvs using JSP page but without using any HTML code. Any suggestion would also help me to explore in my developing arena. Thanks for your efforts.

Answer 1

Better use a Servlet for this. Raw Java code doesn't belong in a JSP file, that's simply recipe for maintenance trouble.

To start, create a simple Java utility class which takes for example a List<List<T>> or a List<Data> (wherein Data represents one row) representing the CSV contents and an OutputStream as method arguments and write logic which does the data copying task.

Once you get that to work, create a Servlet class which takes some CSV file identifier as request parameter or pathinfo (I recommend using pathinfo as a certain webbrowser developed by a team in Redmond would fail on detection of filename/mimetype otherwise), uses the identifier to get the List<List<T>> or List<Data> from somewhere and writes it to the OutputStream of the HttpServletResponse along a set of correct response headers.

Here's a basic kickoff example:

public static <T> void writeCsv (List<List<T>> csv, char separator, OutputStream output) throws IOException {
    BufferedWriter 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();
    }
}

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 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", "text/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 basically all. The filename in the pathinfo is important because a certain webbrowser developed by a team in Redmond ignores the filename part of the Content-Disposition header and uses the last path part of the URL instead.