0

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.

Community
  • 1
  • 1
Thorbjørn Ravn Andersen
  • 73,784
  • 33
  • 194
  • 347
  • I have considerable experience with the HTML method, and it has worked well in every case I needed it. It's nice that you can even use the HTML code to set style items or colspans in the excel sheet. – Erick Robertson Nov 17 '10 at 16:16

3 Answers3

1

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

duffymo
  • 305,152
  • 44
  • 369
  • 561
  • 1
    Any insight into comparing it against Apache's? Just curious. – Chris Nov 17 '10 at 15:41
  • Personal, subjective experience - I like JExcel a lot more. Another data point comes from Spring: The have a JExcelView, but no PoiView. It must be good if the Spring guys prefer it. – duffymo Nov 17 '10 at 16:13
  • "Personal, subjective experience" well yeah but why is it better ? I've never used it btw, but might change in the future if there is good reason to. – NimChimpsky Nov 17 '10 at 16:20
  • Because I've used both and found JExcel to be easier to code. I don't have any statistics to back this up. Accept it or not - I don't care. You asked for opinions and experience - I gave you both. I even offered Spring as an additional data point. That's more than anyone else here has done. It's hardly fair to downvote just mine. – duffymo Nov 17 '10 at 16:44
  • @duffymo chillax! I didn't downvote, just wondered what the specific benefits were. – NimChimpsky Nov 17 '10 at 16:49
  • Thought you did - my mistake. – duffymo Nov 17 '10 at 17:11
  • @duffymo, I have now done some initial investigation of the Write demo in JExcelAPI, and it looks like that this format contains localization information so the sample in the US locale loads properly in my Danish Excel locale. – Thorbjørn Ravn Andersen Nov 17 '10 at 17:16
  • @duffymo, it appears that that JExecelAPI does not allow for writing workbooks with more than 256 columns, and this limitation was lifted with Office 2007. Do you know if it is possible to make JExeclAPI generate with these limitation? – Thorbjørn Ravn Andersen Nov 18 '10 at 10:48
  • No, I don't. I don't usually create spreadsheets that exceed the column limit. JExcel doesn't support the new XML standard type (.xslx) as far as I know. Does POI improve on either of these? – duffymo Nov 18 '10 at 12:53
  • Ok, I have for now just let the rows spread over several sheets. – Thorbjørn Ravn Andersen Nov 18 '10 at 13:26
1

Apache hssf

This has always been the chosen method where I've worked in Java development.

It's an acronym for Horrible SpreadSheet Format

NimChimpsky
  • 46,453
  • 60
  • 198
  • 311
  • @NinChimpsky, got actual experiences to share? – Thorbjørn Ravn Andersen Nov 17 '10 at 16:10
  • It works, it is well documented and easy to use. You seem to emphasize robustness, well that depends on the code you write, the apache library won't let you down. – NimChimpsky Nov 17 '10 at 16:14
  • How is your answer less subjective and personal than mine? "...won't let you down..." - where's your data? Equally subjective and personal. – duffymo Nov 17 '10 at 16:48
  • Who said it was less subjective, I certainly didn't, why are you getting your knickers in twist ? I was actually interested in what the benefits of jExcel were compared to hssf. I can give you data if you like, but whats the point you've used both and prefer jExcel. – NimChimpsky Nov 17 '10 at 16:54
1

The quick way to generate Excel files to to write out tab delineated text and name it <name>.xls. Excel will open any text file ending in .xls as a single worksheet.

Michael Shopsin
  • 2,055
  • 2
  • 24
  • 43
  • @Michael, interesting approach, but I am more interested in robust than quick. Any experiences to share? – Thorbjørn Ravn Andersen Nov 17 '10 at 16:10
  • Tab delineated text for Excel import is robust because tabs rarely occur in the data and can easily be filtered out. Excel will treat the text as tab delineated in a text file even if there are commas so you can put long and complex data into the cells. – Michael Shopsin Nov 17 '10 at 16:22
  • @Michael, have you experiences with importing data generated in a different locale? How are dates and numbers to represented to be globally usable? – Thorbjørn Ravn Andersen May 18 '11 at 13:21
  • If you know the local that data came from you can set the encodings in Java. Otherwise text files from unknown locations have to guessed at based on what encodings would make the characters readable. – Michael Shopsin May 18 '11 at 13:34
  • @Michael, sounds rather fragile. The JExcel solution proved to be very robust. – Thorbjørn Ravn Andersen May 18 '11 at 14:08
  • I usually produce the spreadsheets for internal user so the overhead of a library isn't worth the effort. – Michael Shopsin May 18 '11 at 14:44