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!
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.
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 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.
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 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!
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.
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.
You can download the full source code of this example here: CsvToExcel
