Convert CSV File to Excel 2007 Format Java Example

In the last example, we examined how to convert a CSV file to XLS in Java using POI and OpenCSV. This example, however was successful only in converting the output to Office 97 - 2003 Excel binary format file. Office 2007 openXML standard excel file conversion was not possible through that code. In this tutorial, we will see how to convert CSV file to Excel 2007 format file. 

The example follows the previous tutorial and hence there will not be much deviation to the explanation. We will still use CSVReader object to read input CSV file, however, to write the output we will use XSSFWorkbook and XSSFSheet classes. Step by Step tutorial to convert CSV to XLSX is provided below:

1) Input CSV for XLSX conversion

We will use the CSV file data below for this example:

1,Finance
2,Marketing
3,IT

2) Required JAR Files for CSV to XLSX conversion

You will need the following JAR files to convert CSV files to XLSX.

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

You can search for these JAR files in the internet and download a copy of them before you try the example code below.

3) Java Example Code - CSV to XLSX conversion

Fully working code example to convert CSV file to XLSX format in Java is provided below:

import java.io.FileOutputStream;
import java.io.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook; //Write in Office 2007, 2012 format
import org.apache.poi.xssf.usermodel.XSSFSheet; //Write in Office 2007, 2012 format
import org.apache.poi.ss.usermodel.*;
import au.com.bytecode.opencsv.CSVReader;
import java.io.FileReader;
import java.util.*;
public class CSV2XLSX {  
        public static void main(String[] args) throws Exception{
                
                /* Step -1 : Read input CSV file in Java */
                String inputCSVFile = "csv_2_xlsx.csv";
                CSVReader reader = new CSVReader(new FileReader(inputCSVFile));
                /* Variables to loop through the CSV File */
                String [] nextLine; /* for every line in the file */            
                int lnNum = 0; /* line number */
                /* Step -2 : Define POI Spreadsheet objects */          
                XSSFWorkbook  new_workbook = new XSSFWorkbook (); //create a blank workbook object
                XSSFSheet sheet = new_workbook.createSheet("CSV_to_XLSX");  //create a worksheet with caption score_details
                /* Step -3: Define logical Map to consume CSV file data into excel */
                Map<String, Object[]> excel_data = new HashMap<String, Object[]>(); //create a map and define data
                /* Step -4: Populate data into logical Map */
                while ((nextLine = reader.readNext()) != null) {
                        lnNum++;                        
                        excel_data.put(Integer.toString(lnNum), new Object[] {nextLine[0],nextLine[1]});                        
                }
                /* Step -5: Create Excel Data from the map using POI */
                Set<String> keyset = excel_data.keySet();
                int rownum = 0;
                for (String key : keyset) { //loop through the data and add them to the cell
                        Row row = sheet.createRow(rownum++);
                        Object [] objArr = excel_data.get(key);
                        int cellnum = 0;
                        for (Object obj : objArr) {
                                Cell cell = row.createCell(cellnum++);
                                if(obj instanceof Double)
                                        cell.setCellValue((Double)obj);
                                else
                                        cell.setCellValue((String)obj);
                                }
                }
                /* Write XLS converted CSV file to the output file */
                FileOutputStream output_file = new FileOutputStream(new File("CSV_2_XLSX.xlsx")); //create XLSX file
                new_workbook.write(output_file);//write converted XLSX file to output stream
                output_file.close(); //close the file
        }
}

This code produces the same output file as in the previous tutorial, however the format of the file would be office 2007 excel file format. Simple, you just used different class files available in POI to create the excel in 2007 + format.

This Tutorial: How to Convert CSV file to .XLSX format Excel file in Java?
Keywords: CSV to XLSX in Java, Java CSV conversion Example, POI, OpenCSV, Convert CSV to XLSX