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

1. Introduction

SXSSF (Package Name:org.apache.poi.xssf.streaming) is an API compatible streaming extension ofXSSF 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, whileXSSF 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 theflushRows(int keepRows) method needed. Due to the streaming nature of the implementation, there are the following limitations when compared to theXSSF.

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

Note: If developers are getting thejava.lang.OutOfMemoryError exception, then the developers must use the low-memory footprintSXSSF 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!

2.3 Project Creation

This section will demonstrate on how to create a Java-based Maven project with Eclipse. In Eclipse Ide, go toFile -> New -> 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.

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.

Click on Finish and the creation of a maven project is completed. If you observe, it has downloaded the maven dependencies and apom.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 theupdated 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 thesrc/main/java folder,New -> Package.

A new pop window will open where we will enter the package name as: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.

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.

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.

3.2.1 Implementation of Utility Class

The complete Java code to convert aCsv file to theExcel 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 theAppMain class-> Run As -> Java Application. Developers can debug the example and see what happens after every step!

5. Project Demo

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

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.

Do you want to know how to develop your skillset to become aJava Rockstar?

Subscribe to our newsletter to start Rockingright now!

To get you started we give you our best selling eBooks forFREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to theTerms andPrivacy Policy

Sign up

Thank you!

We will contact you soon.