Convert XLS to CSV in Java Example

In this post, we will explain how to convert a Microsoft Excel document to CSV file in Java, with a working example. We have two key parts to this conversion:

• Read input XLS file using POI Java API.
• Write extracted information into CSV using OpenCSV library.

In later tutorials, we will cover the same concept using JExcel library also. As a first step to this tutorial, let us identify a simple XLS file that needs to be converted to CSV.

Convert Excel Input Sheet

We will use a simple Excel sheet for the example. It is a two column sheet as shown below:

Excel Input Sheet

Save this sheet as excel_to_csv.xls, in a path that your Java program can access.

Read Excel using POI in Java

In this step, we will using Apache POI library to read the contents of the Excel sheet. We have covered reading excel spreadsheets in Java using POI, in detail earlier. You may want to refer to that post to get an idea of what we will be doing in this post.

Write Excel Data to CSV File using OpenCSV

In this step, we will write the excel data into CSV file using OpenCSV Java library. We earlier covered, how to create a CSV file using OpenCSV. We will extract specific code from that example, to create the CSV file.

Required JAR files for Conversion

You will need the following JAR files to work with the example provided in this article.

• Apache POI v3.8, take all JAR files to the classpath.
• opencsv-2.3.jar

XLS to CSV Conversion in Java – Full Program

The complete code that converts Excel to CSV in Java, for the example we provided is given below:

import java.io.FileInputStream;
import java.io.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.*;
import java.util.Iterator;
import java.io.FileWriter;
import au.com.bytecode.opencsv.CSVWriter;
public class excel2csv {  
        public static void main(String[] args) throws Exception{
                //First we read the Excel file in binary format into FileInputStream
                FileInputStream input_document = new FileInputStream(new File("C:\\excel_to_csv.xls"));
                // Read workbook into HSSFWorkbook
                HSSFWorkbook my_xls_workbook = new HSSFWorkbook(input_document); 
                // Read worksheet into HSSFSheet
                HSSFSheet my_worksheet = my_xls_workbook.getSheetAt(0); 
                // To iterate over the rows
                Iterator<Row> rowIterator = my_worksheet.iterator();
                // OpenCSV writer object to create CSV file
                FileWriter my_csv=new FileWriter("convertedCSVFile.csv");
                CSVWriter my_csv_output=new CSVWriter(my_csv); 
                //Loop through rows.
                while(rowIterator.hasNext()) {
                        Row row = rowIterator.next(); 
                        int i=0;//String array
                        //change this depending on the length of your sheet
                        String[] csvdata = new String[2];
                        Iterator<Cell> cellIterator = row.cellIterator();
                                while(cellIterator.hasNext()) {
                                        Cell cell = cellIterator.next(); //Fetch CELL
                                        switch(cell.getCellType()) { //Identify CELL type
                                                //you need to add more code here based on
                                                //your requirement / transformations
                                        case Cell.CELL_TYPE_STRING:
                                                csvdata[i]= cell.getStringCellValue();                                              
                                                break;
                                        }
                                        i=i+1;
                                }
                my_csv_output.writeNext(csvdata);
                }
                my_csv_output.close(); //close the CSV file
                //we created our file..!!
                input_document.close(); //close xls
        }
}

The code is fully commented and you can follow it very easily. If you have a question, you can post it in the comments section.

Example Output – excel2csv

Example output for the code we used is provided below. We got the CSV file in the format we wanted.

Output CSV Sheet