[Java code] Convert CSV to Excel file using Apache POI

This article shows you how to convert a CSV file to an Excel file using Apache POI. You will see a converter program that converts the CSV file into both Excel file formats XLS and XLSX.

Convert csv to excel file in java

Maven Dependencies

We required the following maven dependencies to develop this application.

  • poi 4.0.1
  • poi-ooxml 4.0.1
  • opencsv 3.9
  • commons-lang 2.6

Include the below maven dependencies to your pom.xml file. If you are new to the Maven application development in Eclipse IDE for Apache POI, don’t worry you can see the complete setup of Maven and Eclipse here: Apache POI – Getting Started.

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.0.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.0.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.opencsv/opencsv -->
<dependency>
	<groupId>com.opencsv</groupId>
	<artifactId>opencsv</artifactId>
	<version>3.9</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-lang/commons-lang -->
<dependency>
	<groupId>commons-lang</groupId>
	<artifactId>commons-lang</artifactId>
	<version>2.6</version>
</dependency>

How to convert csv to excel?

There are two major steps involved in CSV to Excel file conversion. The first step is reading the given CSV file and the other one is creating/writing the CSV file data in an Excel file. To create or write the Excel file we will use the Apache POI library but to read the CSV file data we will use two approaches here.

  1. Using BufferedReader
  2. Using Opencsv

Convert CSV to Excel using BufferedReader and POI

We can write a simple CSV to Excel converter program using BufferedReader (to read CSV file) and Apache POI (to write Excel file). Let’s see the below java code for this:

CsvToExcel.java

package com.javacodepoint.excel;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;

import org.apache.commons.lang.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 
 * @author javacodepoint.com
 *
 */

public class CsvToExcel {

	public static final char CSV_FILE_DELIMITER = ',';

	public void convertCsvToExcel(String strSource, String strDestination, String extension)
			throws IllegalArgumentException, IOException {

		Workbook workBook = null;
		FileOutputStream fos = null;

		// Check that the source file exists.
		File sourceFile = new File(strSource);
		if (!sourceFile.exists()) {
			throw new IllegalArgumentException("The source CSV file cannot be found at " + sourceFile);
		}

		// Check that the destination folder exists to save the Excel file.
		File destination = new File(strDestination);
		if (!destination.exists()) {
			throw new IllegalArgumentException(
					"The destination directory " + destination + " for the converted Excel file does not exist.");
		}
		if (!destination.isDirectory()) {
			throw new IllegalArgumentException(
					"The destination " + destination + " for the Excel file is not a directory/folder.");
		}

		// Getting BufferedReader object
		BufferedReader br = new BufferedReader(new FileReader(sourceFile));

		// Getting XSSFWorkbook or HSSFWorkbook object based on excel file format
		if (extension.equals(".xlsx")) {
			workBook = new XSSFWorkbook();
		} else {
			workBook = new HSSFWorkbook();
		}

		Sheet sheet = workBook.createSheet("Sheet");

		String nextLine;
		int rowNum = 0;
		while ((nextLine = br.readLine()) != null) {
			Row currentRow = sheet.createRow(rowNum++);
			String rowData[] = nextLine.split(String.valueOf(CSV_FILE_DELIMITER));
			for (int i = 0; i < rowData.length; i++) {
				if (NumberUtils.isDigits(rowData[i])) {
					currentRow.createCell(i).setCellValue(Integer.parseInt(rowData[i]));
				} else if (NumberUtils.isNumber(rowData[i])) {
					currentRow.createCell(i).setCellValue(Double.parseDouble(rowData[i]));
				} else {
					currentRow.createCell(i).setCellValue(rowData[i]);
				}
			}
		}
		String filename = sourceFile.getName();
		filename = filename.substring(0, filename.lastIndexOf('.'));
		File generatedExcel = new File(strDestination, filename + extension);
		fos = new FileOutputStream(generatedExcel);
		workBook.write(fos);

		try {
			// Closing workbook, fos, and br object
			workBook.close();
			fos.close();
			br.close();

		} catch (IOException e) {
			System.out.println("Exception While Closing I/O Objects");
			e.printStackTrace();
		}

	}

	/*
	 * Testing the CSV to Excel converter program using the main method
	 */
	public static void main(String[] args) {
		long startTime = System.currentTimeMillis();
		boolean converted = true;
		try {
			CsvToExcel converter = new CsvToExcel();
			String strSource = "E:/Excel/employee.csv";
			String strDestination = "E:/Excel/";
			converter.convertCsvToExcel(strSource, strDestination, ".xlsx");
		} catch (Exception e) {
			System.out.println("Unexpected exception");
			e.printStackTrace();
			converted = false;
		}
		if (converted) {
			System.out.println("Conversion took " + ((System.currentTimeMillis() - startTime) / 1000) + " seconds");
		}
	}
}

Convert CSV to Excel using Opencsv and POI

The Opencsv is a third-party java library that we will use here to read the CSV file data and by using POI, write the data to an Excel file. Let’s see the below code for this:

CsvToExcelConverter.java

package com.javacodepoint.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;

import org.apache.commons.lang.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.opencsv.CSVReader;

/**
 * 
 * @author javacodepoint.com
 *
 */

public class CsvToExcelConverter {

	public static final char CSV_FILE_DELIMITER = ',';

	public void convertCsvToExcel(String strSource, String strDestination, String extension)
			throws IllegalArgumentException, IOException {

		Workbook workBook = null;
		FileOutputStream fos = null;

		// Check that the source file exists.
		File sourceFile = new File(strSource);
		if (!sourceFile.exists()) {
			throw new IllegalArgumentException("The source CSV file cannot be found at " + sourceFile);
		}

		// Check that the destination folder exists to save the Excel file.
		File destination = new File(strDestination);
		if (!destination.exists()) {
			throw new IllegalArgumentException(
					"The destination directory " + destination + " for the converted Excel file does not exist.");
		}
		if (!destination.isDirectory()) {
			throw new IllegalArgumentException(
					"The destination " + destination + " for the Excel file is not a directory/folder.");
		}

		// Getting CSVReader object by passing specified Delimiter
		CSVReader reader = new CSVReader(new FileReader(sourceFile), CSV_FILE_DELIMITER);

		// Getting XSSFWorkbook or HSSFWorkbook object based on excel file format
		if (extension.equals(".xlsx")) {
			workBook = new XSSFWorkbook();
		} else {
			workBook = new HSSFWorkbook();
		}

		Sheet sheet = workBook.createSheet("Sheet");

		String[] nextLine;
		int rowNum = 0;
		while ((nextLine = reader.readNext()) != null) {
			Row currentRow = sheet.createRow(rowNum++);
			for (int i = 0; i < nextLine.length; i++) {
				if (NumberUtils.isDigits(nextLine[i])) {
					currentRow.createCell(i).setCellValue(Integer.parseInt(nextLine[i]));
				} else if (NumberUtils.isNumber(nextLine[i])) {
					currentRow.createCell(i).setCellValue(Double.parseDouble(nextLine[i]));
				} else {
					currentRow.createCell(i).setCellValue(nextLine[i]);
				}
			}
		}
		String filename = sourceFile.getName();
		filename = filename.substring(0, filename.lastIndexOf('.'));
		File generatedExcel = new File(strDestination, filename + extension);
		fos = new FileOutputStream(generatedExcel);
		workBook.write(fos);

		try {
			// Closing workbook, fos, and reader object
			workBook.close();
			fos.close();
			reader.close();

		} catch (IOException e) {
			System.out.println("Exception While Closing I/O Objects");
			e.printStackTrace();
		}

	}

	/*
	 * Testing the CSV to Excel converter program using the main method
	 */
	public static void main(String[] args) {
		long startTime = System.currentTimeMillis();
		boolean converted = true;
		try {
			CsvToExcel converter = new CsvToExcel();
			String strSource = "E:/Excel/employee.csv";
			String strDestination = "E:/Excel/";
			converter.convertCsvToExcel(strSource, strDestination, ".xlsx");
		} catch (Exception e) {
			System.out.println("Unexpected exception");
			e.printStackTrace();
			converted = false;
		}
		if (converted) {
			System.out.println("Conversion took " + ((System.currentTimeMillis() - startTime) / 1000) + " seconds");
		}
	}
}

Convert multiple csv to excel

In some use-cases, you may have the requirements like converting multiple CSV files into a single Excel file. Let’s see how you can do it easily with some modifications in the above examples.

You just assume all CSV files are available in a single directory or folder. Now see the following java code to read all the CSV files:

// Check to see if the sourceFolder variable holds a reference to
// a folder of csv files.
final File[] filesList;
if(source.isDirectory()) {
    // Get a list of all of the CSV files in
    // the source folder/directory
    filesList = source.listFiles(new ExcelFilenameFilter());
}
 
for(File excelFile : filesList) {
   //open the CSV file
   //read csv data 
   //save the data to excel file
  //...
}
 
 
/**
 * An instance of this class can be used to control the files returned
 * be a call to the listFiles() method when made on an instance of the
 * File class and that object refers to a folder/directory
 */
class ExcelFilenameFilter implements FilenameFilter {
   @Override
    public boolean accept(File file, String name) {
        return(name.endsWith(".csv"));
    }
}

Convert csv to excel online

There are multiple websites available to Convert CSV to excel online for free. We are listing out some popular websites here to do the conversion online.

Conclusion

This article explained to you the two approaches to change CSV to excel files using the BufferedReader and Opencsv along with Apache POI library. You have seen how do you work with Multiple CSV files for converting them into a single Excel file. You have also seen some popular websites for CSV to excel converters online.

You can download the complete eclipse maven project (Csv To Excel) source code from the below download button.

Related Articles:

[Java code] Convert Excel file to CSV with Apache POI
How to create password-protected Excel in java?
How to Read password-protected Excel in java?
How to write data to an existing Excel file in java?

You may also like:

How to create a Stopwatch in JavaScript?
File upload validations in javascript
Preview an image before uploading using Javascript
Preview an image before uploading using jQuery
File Upload in Java Servlet Example
Multiple file upload in Java with Progress bar – Ajax

Share with friends

Leave a Comment

Your email address will not be published.