Is there an elegant way to Generate Excel spreadsheet from List? (JAVA)

Question

In java, Is there a elegant way to Generate Excel spreadsheet from List?

Are you planning to use a library? I would suggest JExcelAPI. — KLee1, Jul 16 '10 at 17:01
CSV is indeed the way to go. You can find some code example [here](http://stackoverflow.com/questions/477886/jsp-generating-excel-spreadsheet-xls-to-download/2154226#2154226). — BalusC, Jul 16 '10 at 18:48
In spite of having the popular and accepted answer, I think that Sualeh's suggestion of using XML is a good idea too. — Carl Smotricz, Jul 16 '10 at 20:27

score 2 · Accepted Answer · answered Jul 16 '10 at 17:04

2

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.

answered Jul 16 '10 at 17:04

Carl Smotricz

66,391
18
125
167

Never thought of CSV approach.Thanks. – Mr.Burns Jul 16 '10 at 17:30
2

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 Jul 16 '10 at 18:52
@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 Jul 16 '10 at 19:59
@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 Jul 16 '10 at 20:22
@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 Jul 16 '10 at 20:25
@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 Jul 16 '10 at 20:29
@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 Jul 16 '10 at 21:12

score 1 · Answer 2 · answered Jul 16 '10 at 18:45

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.

score 0 · Answer 3 · answered Sep 06 '20 at 01:26

You can try ssio

public class Player {

    @SsColumn(index = 0, name = "Id")
    private long id;

    @SsColumn(index = 1) // the column name will be decided as "Birth Country"
    private String birthCountry;

    @SsColumn(index = 2, typeHandler = FullNameTypeHandler.class) //complex prop type
    private FullName fullName;

    @SsColumn(index = 3) //The enum's name() will be saved. Otherwise, use a typeHandler
    private SportType sportType;

    @SsColumn(index = 4, format = "yyyy/MM/dd") //date format
    private LocalDate birthDate;

    @SsColumn(index = 5, typeHandler = TimestampAsMillisHandler.class)
    //if you prefer saving timestamp as number
    private LocalDateTime createdWhen; 
...
}

  SaveParam<Player> saveParam =
            //Excel-like file. For CSV,  use "new CsvSaveParamBuilder()"
            new OfficeSaveParamBuilder<Player>()  
                    .setBeanClass(Player.class)
                    .setBeans(players)
                    .setOutputTarget(outputStream)
                    .build();
    
    SsioManager ssioManager = SsioManagerFactory.newInstance();
    SaveResult saveResult = ssioManager.save(saveParam);

Is there an elegant way to Generate Excel spreadsheet from List? (JAVA)

3 Answers3