In java, Is there a elegant way to Generate Excel spreadsheet from List?
views:
83answers:
2
+2
A:
There are two possible and radically different approaches:
Write a CSV file. That's comma-separated, you just write out your fields, separated by commas, into a file with a .csv extension. Excel can read that just fine and it's dramatically simple.
Use Apache/Jakarta POI, a library, to write perfectly formatted, Office-compatible Excel files (Excel 95, 2003, ... various standards). This takes a bit more work.
Carl Smotricz
2010-07-16 17:04:19
Never thought of CSV approach.Thanks.
Mr.Burns
2010-07-16 17:30:52
Just to note, be careful if the data you're writing to CSV can contain commas. CSV will read every comma in your file as a column delimiter.
Jon Quarfoth
2010-07-16 18:52:19
@Jon, commas in the data shouldn't be problem if the string values are properly quoted... 1,2.34,"5,678.9","ABC DEF"
Mark Baker
2010-07-16 19:59:35
@Mark: You're correct, but that way begins a trail of sorrow. One can't be sure there are no quotes in the data too, so the quotes need to be escaped... not a difficult problem, but an annoyance. [This document](http://creativyst.com/Doc/Articles/CSV/CSV01.htm) looks like a pretty thorough description of the Excel dialect of CSV, except they don't mention that some regions use semicola instead of commas.
Carl Smotricz
2010-07-16 20:22:46
@Carl A trail of sorrow it might appear, and yes, you do need to escape your quotes in a CSV, but that's the standard (insofar as there is a standard) that's respected by most CSV libraries...
Mark Baker
2010-07-16 20:25:13
@Mark: Certainly, if it's worth doing it's worth doing correctly. I just feel annoyed at having to write a method of maybe 30 lines to accommodate the quirks of this format. Actually, it's Mr. Burns that has to, not me, so I'm fine with that ;)
Carl Smotricz
2010-07-16 20:29:49
@Carl - Couldn't name any off the top of my head, but I'm sure Java has some library that reads/writes CSV files with all the quotes and escapes, sames as most languages have recognised libraries or even built-in functions for the task - opencsv? -= if you need Excel cell formatting, or multiple worksheets, then Apache POI or JExcelAPI are better choices, otherwise CSV is a good solution
Mark Baker
2010-07-16 21:12:21
+1
A:
As a previous answer suggests, CSV is an easy way to do this, but Excel has a habit of inferring data types - for example, if a string looks like a number, it will be formatted as a number, even if you have double-quoted it. If you want more control, you can try generating Excel XML, which in your case may be using a template, and generating a table that looks a little bit like an HTML table. See an example of a simple Excel XML document.
Sualeh Fatehi
2010-07-16 18:45:14