How to create an Excel file based on CSV file using Java?

Question

I have a requirement to create an XLS file on the basis of a CSV file using Java.

Please suppest to me which API is best to create excel file.

Thanks

Solution 1

I suggest you use the Apache POI framework (specifically the HSSF / XSSF API) for writing out the XLS file.

For reading a CSV file I suggest you use OpenCSV as it will take care of escaped characters etc for you.

Putting together the POI example from here and the OpenCSV example from here gives you this:

import java.io.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import au.com.bytecode.opencsv.CSVReader;

class Test {
    public static void main(String[] args) throws IOException {
        Workbook wb = new HSSFWorkbook();
        CreationHelper helper = wb.getCreationHelper();
        Sheet sheet = wb.createSheet("new sheet");

        CSVReader reader = new CSVReader(new FileReader("data.csv"));
        String[] line;
        int r = 0;
        while ((line = reader.readNext()) != null) {
            Row row = sheet.createRow((short) r++);

            for (int i = 0; i < line.length; i++)
                row.createCell(i)
                   .setCellValue(helper.createRichTextString(line[i]));
        }

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        wb.write(fileOut);
        fileOut.close();
    }
}

Solution 2

Apache POI is a library that can handle all kinds of microsoft office documents, including MS Excel. You can read the content of the csv file with simple java code and use that library to create and save a MS Excel document.

Solution 3

There are many check

http://jexcelapi.sourceforge.net/

you will get a lot of alternative. Just google.

Comments

[whd]

it will be terribly slow method when your csv has over 100k lines