Convert .csv to .xls in Java

Question

Does anyone here know of any quick, clean way to convert csv files to xls or xlsx files in java?

I have something to manage csv files already in place and I need the extra compatibility for other programs.

Sample code in addition to package names is always well appreciated.

Many thanks,

Justian

Here's my code thus far. I need to remove the returns ("\n") from the lines. Some of my cells contain multiple lines of information (a list), so I can use "\n" in csv to indicate multiple lines within a cell, but xls treats these as if I mean to put them on a new line.

The code is modified from the internet and a little messy at the moment. You might notice some deprecated methods, as it was written in 2004, and be sure to ignore the terrible return statements. I'm just using S.o.p at the moment for testing and I'll clean that up later.

package jab.jm.io;

import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class FileConverter {

    public static String ConvertCSVToXLS(String file) throws IOException {

        if (file.indexOf(".csv") < 0)
            return "Error converting file: .csv file not given.";

        String name = FileManager.getFileNameFromPath(file, false);
        ArrayList<ArrayList<String>> arList = new ArrayList<ArrayList<String>>();
        ArrayList<String> al = null;

        String thisLine;
        DataInputStream myInput = new DataInputStream(new FileInputStream(file));

        while ((thisLine = myInput.readLine()) != null) {
            al = new ArrayList<String>();
            String strar[] = thisLine.split(",");

            for (int j = 0; j < strar.length; j++) {
                // My Attempt (BELOW)
                String edit = strar[j].replace('\n', ' ');
                al.add(edit);
            }

            arList.add(al);
            System.out.println();
        }

        try {
            HSSFWorkbook hwb = new HSSFWorkbook();
            HSSFSheet sheet = hwb.createSheet("new sheet");

            for (int k = 0; k < arList.size(); k++) {
                ArrayList<String> ardata = (ArrayList<String>) arList.get(k);
                HSSFRow row = sheet.createRow((short) 0 + k);

                for (int p = 0; p < ardata.size(); p++) {
                    System.out.print(ardata.get(p));
                    HSSFCell cell = row.createCell((short) p);
                    cell.setCellValue(ardata.get(p).toString());
                }
            }

            FileOutputStream fileOut = new FileOutputStream(
                    FileManager.getCleanPath() + "/converted files/" + name
                            + ".xls");
            hwb.write(fileOut);
            fileOut.close();

            System.out.println(name + ".xls has been generated");
        } catch (Exception ex) {
        }

        return "";
    }
}

Answer 1

Don't know if you know this already, but:

• Excel (if that's your real target) is easily able to read .csv files directly, so any conversion you'd do would only be a courtesy to your less "gifted" users.
• CSV is a lowest-common-denominator format. It's unlikely for any converter to add information to that found in a .csv file that will make it more useful. In other words, CSV is a "dumb" format and converting it to .xls will (probably) increase file size but not make the format any smarter.

Curtis' suggestion of POI is the first thing that would come to my mind too.

If you're doing this conversion on a Windows machine, another alternative could be Jacob, a Java-COM bridge that would allow you to effectively remote control Excel from a Java program so as to do things like open a file and save in a different format, perhaps even applying some formatting changes or such.

Finally, I've also had some success doing SQL INSERTs (via JDBC) into an Excel worksheet accessed via the JDBC-ODBC bridge. i.e. ODBC can make an Excel file look like a database. It's not very flexible though, you can't ask the DB to create arbitrarily named .XLS files.

---

EDIT:

It looks to me like readLine() is already not giving you whole lines. How is it to know that carriage return is not a line terminator? You should be able to verify this with debug print statements right after the readLine().

If this is indeed so, it would suck because the way forward would be for you to

• either recognize incomplete lines and paste them together after the fact,
• or write your own substitute for readLine(). A simple approach would be to read character by character, replacing CRs within a CSV string and accumulating text in a StringBuilder until you feel you have a complete line.

Both alternatives are work you probably weren't looking forward to.

Answer 2

If you want to read or write XLS or XLSX files in Java, Apache POI is a good bet: http://poi.apache.org/

Answer 3

Copy paste the below program,I ran the program and it is working fine,Let me know if you have any concerns on this program.(You need Apache POI Jar to run this program)

import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;


public class CSVToExcelConverter {

    public static void main(String args[]) throws IOException
    {
        ArrayList arList=null;
        ArrayList al=null;
        String fName = "test.csv";
        String thisLine;
        int count=0;
        FileInputStream fis = new FileInputStream(fName);
        DataInputStream myInput = new DataInputStream(fis);
        int i=0;
        arList = new ArrayList();
        while ((thisLine = myInput.readLine()) != null)
        {
            al = new ArrayList();
            String strar[] = thisLine.split(",");
            for(int j=0;j<strar.length;j++)
            {
                al.add(strar[j]);
            }
            arList.add(al);
            System.out.println();
            i++;
        }

        try
        {
            HSSFWorkbook hwb = new HSSFWorkbook();
            HSSFSheet sheet = hwb.createSheet("new sheet");
            for(int k=0;k<arList.size();k++)
            {
                ArrayList ardata = (ArrayList)arList.get(k);
                HSSFRow row = sheet.createRow((short) 0+k);
                for(int p=0;p<ardata.size();p++)
                {
                    HSSFCell cell = row.createCell((short) p);
                    String data = ardata.get(p).toString();
                    if(data.startsWith("=")){
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        data=data.replaceAll("\"", "");
                        data=data.replaceAll("=", "");
                        cell.setCellValue(data);
                    }else if(data.startsWith("\"")){
                        data=data.replaceAll("\"", "");
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(data);
                    }else{
                        data=data.replaceAll("\"", "");
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(data);
                    }
                    //*/
                    // cell.setCellValue(ardata.get(p).toString());
                }
                System.out.println();
            }
            FileOutputStream fileOut = new FileOutputStream("test.xls");
            hwb.write(fileOut);
            fileOut.close();
            System.out.println("Your excel file has been generated");
        } catch ( Exception ex ) {
            ex.printStackTrace();
        } //main method ends
    }
}

Answer 4

The tools in Excel are not adequate for what the OP wants to do. He's on the right track there. Excel cannot import multiple CSV files into different worksheets in the same file, which is why you'd want to do it in code. My suggestion is to use OpenCSV to read the CSV, as it can automatically correct for newlines in data and missing columns, and it's free and open source. It's actually very, very robust and can handle all sorts of different non-standard CSV files.