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.
Table of Contents
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.
- Using BufferedReader
- 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 files) and Apache POI (to write Excel files). 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 some popular websites here to do the conversion online.
Conclusion
This article explained to you the two approaches to changing CSV to excel files using the BufferedReader and Opencsv along with the Apache POI library. You have seen how 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