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!

Get requested page URL from request in Spring Boot

How to get the requested URL information in a Spring security project so that we can redirect the user to the secure or right page after successful login. On every request Spring security stores the following information in the session key "SPRING_SECURITY_SAVED_REQUEST". This way will help us to get the last requested URL or request from where a user started the authentication request that redirected the user to the login page.

SPRING_SECURITY_SAVED_REQUEST

Below is the code snippet to get the requested url from the "SPRING_SECURITY_SAVED_REQUEST" key.

DefaultSavedRequest savedRequest = (DefaultSavedRequest)request.getSession()

        .getAttribute("SPRING_SECURITY_SAVED_REQUEST");
    	if(savedRequest != null) {
    		String previousPageUrl = savedRequest.getRedirectUrl();
    		
    	} 

This will helps us to redirect the users to the secure page after successful login. Thank you, hope this solves your problem.

how to set environment to apply the spring application properties file

By default, application.properties file is picked by system for configurations properties. If you have different properties files available to run the application on the different environments then we need to add spring.profiles.active=<environment name> which is present in the application.property file name.


For example, if our application properties file has profile "dev", and the complete file name is application-dev.properties, then we need to set spring.profiles.active=dev in application.properties file, this way application will pic the right configuration file.

Hope this helps!