What's it all about, Alfie?

I had a user that liked the pages I'd created in my web app, but they really needed to be able to export the data to Excel so they didn't have to transcribe it. Makes sense, and given that I know Excel takes CSV data, and the DataTable I wrote in Java has an option to output CSV, it should be easy.

Well... that was a good idea... but it really wasn't too hard once you get a few things cleared up. First, the CSV format. I had my CSV output method, toCSV(), formatting the numbers which was a terrible mistake. I needed to stop that. Then I needed to escape the CSV data according to these basic CSV rules:

• if the data has a comma in it - surround it with double-quotes
• if the data has a double-quote in it, double it, and then surround it with double-quotes

The first one is obvious. If I have data like this:

then the CSV needs to look like:

  32.5,Reggie,"Indianapolis, IN",5

and if I had data like this:

then the CSV needs to look like:

  32.5,"Reggie ""Dog"" Johnson","Indianapolis, IN",5

OK, these were new little wrinkles for me, but it didn't take too much time to make these fixes to my DataTable Java class. Now I have the right format, but how do I set up the rest of the response so I have Excel automatically handle it without a lot of grief?

Well... it turns out it's the header and content type. Each of my servlet requests has a parameter associated with them called out. If it's json, then the output of the request is JSON. If it's html, then it's HTML. This really helps in the debugging of the servlets as I can add &out=html... to the URL and see the results in the browser. Nice.

Well... let's make another 'type' of out - excel. Then, in my servlet I can do the following:

  String   out = request.getParameter("out");
  if (out == null) {
    out = "html";
  }
 
  if (out.equalsIgnoreCase("json") || out.equalsIgnoreCase("html")) {
    response.setContentType("text/html");
  } else if (out.equalsIgnoreCase("csv")) {
    response.setContentType("text/csv");
  } else if (out.equalsIgnoreCase("excel")) {
    response.setHeader("Content-disposition", "attachment;filename=\"myDump.csv\"");
    response.setContentType("application/vnd.ms-excel");
  }

I was lucky to get the header and content type from a few google searches, and then it was just a matter of putting it all together. Once I get my DataTable I can then ship it back to the caller with the code:

  if (out.equalsIgnoreCase("json")) {
    response.print(ans.toJSON());
  } else if (out.equalsIgnoreCase("csv") || out.equalsIgnoreCase("excel")) {
    response.print(ans.toCSV());
  } else {
    response.print("<center>" + ans.toHTML() + "</center>");
  }

When I put this all together, the client doesn't need to know a thing. The request comes in for the URL, and the data comes back, intercepted by Excel, and it fills in the cells. Pretty neat.

This entry was posted on Wednesday, February 10th, 2010 at 3:41 pm and is filed under Coding, Cube Life. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.