Convert CSV File to Excel in Java Example

In this tutorial, we will explain how to convert a CSV file to XLS format in Java. We will be using OpenCSV [opencsv-2.3.jar] and Apache POI [v3.8] to convert a CSV file to XLS format. OpenCSV is one of the best libraries to read a CSV file and Apache POI is equivalently best in the aspect of creating XLS document compatible with Microsoft Office packages. Often, you may find yourself in a need to read a CSV file and convert it into XLS for readability reasons. This article aims to make a novel attempt to achieve this conversion, purely in Java. This tutorial is broken down into simpler steps for easy understanding:

1. Define Input CSV File 
2. JAR files required to convert CSV to XLS format 
3. Read CSV file in Java using Open CSV 
4. Add CSV file Contents to Apache POI XLS Objects 
5. Create XLS file output 
6. Test Conversion status

1) Input CSV File – To Read and Convert to XLS

The input CSV file for this exercise if a very simple file with two columns as shown below:

1,Finance
2,Marketing
3,IT

We will name this file csv_2_xls.csv. In the next step, we will show how to read a CSV file in Java using OpenCSV library.

2) Required JAR Files for CSV XLS Conversion

In order to run the example provided in this tutorial, you will need the following JAR files:

opencsv-2.3.jar; poi-3.8.jar

3) Read CSV File using OpenCSV in Java

In this step, you will be using the CSVReader class [ defined in au.com.bytecode.opencsv.CSVReader ] to read the CSV file in Java. The constructor in CSVReader supports creating an object by directly pointing to the CSV file using the FileReader class. [ defined in java.io.FileReader ]. When you look at the complete code example, you will be able to understand how this works.

4) Add CSV File Contents to Map

You use the readNext method, available in CSRReader class, to read every line in the CSV file , and populate individual columns to a Hashmap in Java. This step is repeated until all the lines are read to the hashmap. Note that this is purely optional. You can directly write the content to the logical worksheet object directly if you want to, and skip this step.

5) Create XLS file output

In this step, you will use Apache POI Java library and convert the data in the HashMap (i.e. CSV File) into Excel document. The Excel file will also be written to a file in this step.

6) CSV to XLS in Java – Complete Code Example

The complete Java code example to convert a CSV file to XLS format is provided below:

import java.io.FileOutputStream;
import java.io.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.*;
import au.com.bytecode.opencsv.CSVReader;
import java.io.FileReader;
import java.util.*;
public class Convert_CSV_XLS {  
        public static void main(String[] args) throws Exception{
                
                /* Step -1 : Read input CSV file in Java */
                String inputCSVFile = "csv_2_xls.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 */          
                HSSFWorkbook new_workbook = new HSSFWorkbook(); //create a blank workbook object
                HSSFSheet sheet = new_workbook.createSheet("CSV2XLS");  //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("CSV2XLS.xls")); //create XLS file
                new_workbook.write(output_file);//write converted XLS file to output stream
                output_file.close(); //close the file
        }
}

When you run this code, it produces the required Excel output from CSV file, which can be seen from the screenshot below:

Convert CSV to XLS in Java Program Output

That completes a very quick tutorial on converting CSV file to XLS in Java using Apache POI and OpenCSV library. There is lot of scope to tweak the code to support to your requirement. Hope you liked this tutorial. If you have any questions that you want us to answer, post it in the comments section of this blog post.

This Tutorial: How to convert CSV File to Excel Format in Java?
Keywords: Convert CSV to Excel in Java, Apache POI, Java, CSV to Excel, OpenCSV, CSVReader, Example
Next Tutorial: How to convert CSV File to XLSX format in Java, using POI?