Core Java

Java Convert Csv to Excel File Example

Hello readers, in this tutorial, we are going to implement the Csv to Excel file conversion by using the Apache POI library. This tutorial will show developers how to write large data to an excel file using SXSSF.

1. Introduction

SXSSF (Package Name: org.apache.poi.xssf.streaming) is an API compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced, and the heap space is limited. SXSSF achieves its low memory footprint by limiting access to the rows that are within a sliding window, while XSSF gives access to all rows in the document. Older rows that are no longer in the window become inaccessible, as they are written to the disk.

In the auto-flush mode the size of the access window can be specified, to hold a certain number of rows in the memory. When that value is reached, the creation of an additional row causes the row with the lowest index to be removed from the access window and written to the disk. Do remember, the window size can be set to grow dynamically i.e. it can be trimmed periodically by an explicit call to the flushRows(int keepRows) method needed. Due to the streaming nature of the implementation, there are the following limitations when compared to the XSSF.

  • Only a limited number of rows are accessible at a point in time
  • The sheetObj.clone() method is not supported
  • Formula evaluation is not supported

Note: If developers are getting the java.lang.OutOfMemoryError exception, then the developers must use the low-memory footprint SXSSF API implementation.

Now, open up the Eclipse Ide and let’s see how to implement this conversion with the help of Apache POI library!

2. Java Convert Csv to Excel File Example

2.1 Tools Used

We are using Eclipse Kepler SR2, JDK 8 and Maven. Having said that, we have tested the code against JDK 1.7 and it works well.

2.2 Project Structure

Firstly, let’s review the final project structure, in case you are confused about where you should create the corresponding files or folder later!

Fig. 1: Csv to Excel Application Project Structure
Fig. 1: Application Project Structure

2.3 Project Creation

This section will demonstrate on how to create a Java-based Maven project with Eclipse. In Eclipse Ide, go to File -> New -> Maven Project.

Fig. 2: Create Maven Project
Fig. 2: Create Maven Project

In the New Maven Project window, it will ask you to select project location. By default, ‘Use default workspace location’ will be selected. Select the ‘Create a simple project (skip archetype selection)’ checkbox and just click on next button to proceed.

Fig. 3: Project Details
Fig. 3: Project Details

It will ask you to ‘Enter the group and the artifact id for the project’. We will input the details as shown in the below image. The version number will be by default: 0.0.1-SNAPSHOT.

Fig. 4: Archetype Parameters
Fig. 4: Archetype Parameters

Click on Finish and the creation of a maven project is completed. If you observe, it has downloaded the maven dependencies and a pom.xml file will be created. It will have the following code:

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>CsvToExcel</groupId>
	<artifactId>CsvToExcel</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>
</project>

Developers can start adding the dependencies that they want to like OpenCsv, Apache POI etc. Let’s start building the application!

3. Application Building

Below are the steps involved in developing this application.

3.1 Maven Dependencies

Here, we specify the dependencies for the OpenCsv, Apache POI, and Log4j. The rest dependencies will be automatically resolved by the Maven framework and the updated file will have the following code:

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>CsvToExcel</groupId>
	<artifactId>CsvToExcel</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<dependencies>
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.17</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.17</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/commons-lang/commons-lang -->
		<dependency>
			<groupId>commons-lang</groupId>
			<artifactId>commons-lang</artifactId>
			<version>2.6</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/log4j/log4j -->
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.17</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/com.opencsv/opencsv -->
		<dependency>
			<groupId>com.opencsv</groupId>
			<artifactId>opencsv</artifactId>
			<version>3.9</version>
		</dependency>
	</dependencies>
	<build>
		<finalName>${project.artifactId}</finalName>
	</build>
</project>

3.2 Java Class Creation

Let’s create the required Java files. Right-click on the src/main/java folder, New -> Package.

Fig. 5: Java Package Creation
Fig. 5: Java Package Creation

A new pop window will open where we will enter the package name as: com.jcg.csv2excel.

Fig. 6: Java Package Name (com.jcg.csv2excel)
Fig. 6: Java Package Name (com.jcg.csv2excel)

Once the package is created in the application, we will need to create the implementation class and the main class. Right-click on the newly created package: New -> Class.

Fig. 7: Java Class Creation
Fig. 7: Java Class Creation

A new pop window will open and enter the file name as: CsvToExcel. The utility class will be created inside the package: com.jcg.csv2excel.

Fig. 8: Java Class (CsvToExcel.java)
Fig. 8: Java Class (CsvToExcel.java)

Repeat the step (i.e. Fig. 7) and enter the filename as: AppMain. The main class will be created inside the package: com.jcg.csv2excel.

Fig. 9: Java Class (AppMain.java)
Fig. 9: Java Class (AppMain.java)

3.2.1 Implementation of Utility Class

The complete Java code to convert a Csv file to the Excel format is provided below. Let’s see the simple code snippet that follows this implementation.

CsvToExcel.java

package com.jcg.csv2excel;

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

import org.apache.commons.lang.math.NumberUtils;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.opencsv.CSVReader;

public class CsvToExcel {

	public static final char FILE_DELIMITER = ',';
	public static final String FILE_EXTN = ".xlsx";
	public static final String FILE_NAME = "EXCEL_DATA";

	private static Logger logger = Logger.getLogger(CsvToExcel.class);

	public static String convertCsvToXls(String xlsFileLocation, String csvFilePath) {
		SXSSFSheet sheet = null;
		CSVReader reader = null;
		Workbook workBook = null;
		String generatedXlsFilePath = "";
		FileOutputStream fileOutputStream = null;

		try {

			/**** Get the CSVReader Instance & Specify The Delimiter To Be Used ****/
			String[] nextLine;
			reader = new CSVReader(new FileReader(csvFilePath), FILE_DELIMITER);

			workBook = new SXSSFWorkbook();
			sheet = (SXSSFSheet) workBook.createSheet("Sheet");

			int rowNum = 0;
			logger.info("Creating New .Xls File From The Already Generated .Csv File");
			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]);
					}
				}
			}

			generatedXlsFilePath = xlsFileLocation + FILE_NAME + FILE_EXTN;
			logger.info("The File Is Generated At The Following Location?= " + generatedXlsFilePath);

			fileOutputStream = new FileOutputStream(generatedXlsFilePath.trim());
			workBook.write(fileOutputStream);
		} catch(Exception exObj) {
			logger.error("Exception In convertCsvToXls() Method?=  " + exObj);
		} finally {			
			try {

				/**** Closing The Excel Workbook Object ****/
				workBook.close();

				/**** Closing The File-Writer Object ****/
				fileOutputStream.close();

				/**** Closing The CSV File-ReaderObject ****/
				reader.close();
			} catch (IOException ioExObj) {
				logger.error("Exception While Closing I/O Objects In convertCsvToXls() Method?=  " + ioExObj);			
			}
		}

		return generatedXlsFilePath;
	}	
}

3.2.2 Implementation of Main Class

This is the main class required to execute the program and test the conversion functionality. Add the following code to it.

AppMain.java

package com.jcg.csv2excel;

import org.apache.log4j.Logger;

public class AppMain {

	private static Logger logger = Logger.getLogger(AppMain.class);

	public static void main(String[] args) {

                String xlsLoc = "config/", csvLoc = "config/sample.csv", fileLoc = "";
		fileLoc = CsvToExcel.convertCsvToXls(xlsLoc, csvLoc);
		logger.info("File Location Is?= " + fileLoc);
	}
}

4. Run the Application

To run the application, Right-click on the AppMain class -> Run As -> Java Application. Developers can debug the example and see what happens after every step!

Fig. 10: Run Application
Fig. 10: Run Application

5. Project Demo

The application shows the following as output where the Csv is successfully converted to Excel and is successfully placed in the project’s config folder.

Fig. 11: Application Output
Fig. 11: Application Output

That’s all for this post. Happy Learning!!

6. Conclusion

This tutorial used the Apache POI libraries to demonstrate a simple Csv to Excel file conversion. That’s all for this tutorial and I hope this article served you whatever you were looking for.

7. Download the Eclipse Project

This was an example of Csv to Excel file conversion for the beginners.

Download
You can download the full source code of this example here: CsvToExcel

Yatin

An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button