Convert Excel 2007 to CSV - Java example

In the last article, we examined how to convert XLS to CSV in Java. When it comes to converting Excel 2007, 2012 (XLSX) documents, we need to slightly take a different approach. We will explain how to convert XLSX to CSV, and in order to do this, we will use the following libraries:

• Apache POI v3.8
• OpenCSV (any latest version would do)

XLSX Example / JAR Files

The input XLSX file would be a similar one as of the last tutorial. The list of JAR files you would require for this example to work is provided below:

• dom4j-1.6.1.jar
• opencsv-2.3.jar
• poi-3.8.jar
• poi-ooxml-3.8.jar
• poi-ooxml-schemas-3.8.jar
• xmlbeans-2.3.0.jar

Excel 2007 to CSV – Full Java Example

The complete Java program that converts XLSX (Open Office Format)  to CSV format is provided below:

import java.io.FileInputStream;
import java.io.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet; 
import org.apache.poi.ss.usermodel.*;
import java.util.Iterator;
import java.io.FileWriter;
import au.com.bytecode.opencsv.CSVWriter;
public class xlsx2csv {  
        public static void main(String[] args) throws Exception{
                //Read the XLSX file into FileInputStream object
                FileInputStream input_document = new FileInputStream(new File("C:\\excel_to_csv.xlsx"));
                // Access input workbook in XSSFWorkbook object
                XSSFWorkbook my_xls_workbook = new XSSFWorkbook(input_document); 
                // Access input worksheet
                XSSFSheet  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
                        //the example input xlsx has only two columns.
                        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 CSV from XLSX!
                input_document.close(); //close xlsx file
        }
}

You must have all the JAR file mentioned above in your classpath. Otherwise, this example will not work. Also, make sure you modify the file path to point to the right Excel sheet you want to use. Depending on the number of columns in your Excel file, you will have to modify the code, marked as comments in appropriate places.

If you have a question on this tutorial, you can post it in the comments section below.