Search code examples
javaexcelexport-to-excel

What is the most _robust_ way to generate an Excel datafile from Java?

I have a situation where I have been asked to write a program that essentially does an arbitrary SQL select over JDBC, convert the ResultSet to something loadable in Excel and send it as an attachment in an email.

The question goes for what dataformat to use in order to be loadable by as many different versions of Excel as possible.

I have considered:

  • XLS - native format, the simplest way to generate seems to be with JExcel.
  • CSV - comma separated format, must use semicolons instead of commas to cope with European decimal commas, and then there is all the quotation stuff.
  • HTML - it appears that Excel knows how to read an HTML table. It should be sufficient then to set the MIME-type to be application/vnd.ms-excel

but naturally there must be other interesting ways to do it.

My major concern is incorrect interpretion of the data:

  1. Numbers with decimal commas gets misinterpreted on systems with decimal points.
  2. Character encoding issues (We cannot rely on the recipient using ISO-Latin-#).
  3. Date interpretation - we have earlier found that the YYYY-MM-DD format is pretty robust.

My major concern is robustness. I don't mind it being tedious to code, if I can count on the result being good.

Suggestions and experiences to share?

I am aware of JSP generating Excel spreadsheet (XLS) to download - that page does not discuss robustness.

Solution

  • I'd recommend Andy Khan's JExcel. It's the best library for working with Excel in Java.