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:

Convert XLS to CSV in Java - Example Input
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.


XLS to CSV - Converted Output Example
Output CSV Sheet

9 comments:

  1. Hello,

    do you know the size of file you can manage with this ? For a 1.000.000 lines file, how long does it last ?

    Regards,

    Julien.

    ReplyDelete
  2. Hi,

    Your code is working fine but if the .xlsx is having multiple sheets then it's not working.

    If you can help me multiple sheets can create multiple .csv files.

    Regards
    Biswajit

    ReplyDelete
    Replies
    1. For multiple sheet you need to do
      for (int i = 0; i < my_xls_workbook.getNumberOfSheets(); i++) {
      XSSFSheet sheet = wb.getSheetAt(i);
      }

      Delete
  3. Hi, I'm getting the below error. Can you please help me to resolve it.
    Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: Index 1 out of bounds for length 1

    ReplyDelete
  4. use this code :

    sheet.shiftRows(1, sheet.getLastRowNum(), -1);

    ReplyDelete
  5. What if we dont want 1st line of excel file,So we can remove?

    ReplyDelete
  6. HI I need to encrypt one column in excel sheet using keystore,any idea how can we do that

    ReplyDelete
  7. Hi,
    I would like to convert a 3 column file to csv and keep the format. What to do.

    For Ex

    CustomerName Account_No Balance
    Test123 003010100744444 300000.3

    For the above data in .xls , I would like to convert the same to .csv keeping the format. Please help.

    ReplyDelete
  8. hi, can u help me how to convert the file without entering the path file like FileInputStream input_document = new FileInputStream(new File("C:\\excel_to_csv.xls")); (where the user can upload their own file)

    ReplyDelete