Reading excel data in Java Sprint Boot

In this article, we will walk through the steps and program which we need to develop to read the excel data in Java program or spring boot application.

1. Maven dependency 

There is an Apache POI library that we need to add to our project, that will help us to easily perform read and write with excel. 

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

2. Apache POI

A major use of the Apache POI api is for Text Extraction applications such as web spiders, index builders, and content management systems.

The Apache POI Project's mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft's OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is your Java Excel solution (for Excel 97-2008). We have a complete API for porting other OOXML and OLE2 formats and welcome others to participate.

There are four types of API available in Apache POI, that we can use to extract the text from different types of files.

1. POIFS: POIFS APIs are used if you had a document written in OLE 2 Compound Document Format, probably written using MFC, that you needed to read in Java.
2. HSSF: HSSF APIs are used if you needed to read or write an Excel file using Java (XLS).
3. XSSF: XSSF APIs are used if you need to read or write an OOXML Excel file using Java (XLSX).
4. SXSSF: SXSSF APIs can be used for large excel files, that allows you to write very large Excel (XLSX) files in a memory optimized way.

The above summary has been written from Apache POI documentation. To read and explore more about POI you may visit the official Apache POI site. https://poi.apache.org/ 

3. Reading from an excel file

After importing the required dependency let's see a sample program that will help us to read the text from an excel sheet.

An excel sheet name "role_mapping_sheet.xlsx" having the following data. This excel sheet you need to keep in the project and it path we have to define in the program. Below is the snapshot of excel data.

Now, we need to create a model class that maps the value of these two columns into two class variables.

RoleTableModel.java

/**
 * 
 */
package serialization;

/**
 * @author rashid
 *
 */
public class RoleTableModel {
	
	public RoleTableModel(String title) {
		super();
		this.title = title;
	}

	public RoleTableModel() {
	}

	private String title;

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	@Override
	public String toString() {
		return "RoleTableModel [title=" + title + "]";
	}
}

Now, see the actual implementation class where we are performing the extraction. Below is the class InsertRoleData.java 

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.thymeleaf.util.ClassLoaderUtils;

import org.apache.poi.ss.usermodel.Cell;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import serialization.RoleTableModel;


public class InsertRoleData {
	Workbook workbook;
	private static final Logger LOGGER = LoggerFactory.getLogger(InsertRoleData.class);
	public List<RoleTableModel> getExcelDataAsList() {
		
		List<String> list = new ArrayList<String>();
		int noOfColumns = 0;
		try {
			FileInputStream file = new FileInputStream(new File("src\\main\\resources\\static\\role_mapping_sheet.xlsx"));
			workbook = new XSSFWorkbook(file);
			Sheet sheet = workbook.getSheetAt(0);
			noOfColumns = sheet.getRow(0).getLastCellNum();

			for (Row row : sheet) {
				for (Cell cell : row) {
					String cellValue = cell.toString();
					list.add(cellValue);
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		List<RoleTableModel> allRoles = createList(list, noOfColumns);
		LOGGER.info("Number of columns: "+noOfColumns);
		return allRoles;
	}
	
	private List<RoleTableModel> createList(List<String> excelData, int noOfColumns) {

		ArrayList<RoleTableModel> invList = new ArrayList<RoleTableModel>();

		int i = noOfColumns;
		do {
			RoleTableModel inv = new RoleTableModel();
			inv.setTitle(excelData.get(i));
			LOGGER.info("role model "+inv.toString());
			invList.add(inv);
			i = i + (noOfColumns);
			
		} while (i < excelData.size());
		LOGGER.info("role model list "+invList.toString());
		return invList;
	}
	
}

You may now simply run your spring boot or java application and read the text from excel sheet cell by cell. Hope this helps you in implementation. If you face any issue while implementation of Apache POI, feels free to ask your question in the comment section. Thank You!