Fetch data from Excel file using Java Spring Web application

[10585 views]

To read the input data of our Spring Batch job from an Excel document, at first we need to add the following dependency declarations into our build script:

Spring Batch Excel: This is a Spring Batch extension that provides ItemReader implementations for Excel. We can get the required jar file by building it from the source.
Apache POI: This provides a Java API for Microsoft Office documents. It is an optional dependency of Spring Batch Excel, and can be used for reading input data from .XLS and .XLSX documents.

After we have added the required dependency declarations into our build script, we can finally configure the ItemReader that can read data from our Excel spreadsheet.

Reading Information From an Excel File

As an example, we need to create a Spring Batch job that can import student information from an Excel file. This file contains information like name, email address and name of purchased subscription. When we read the student information from an Excel file, we have to transform that information into StudentDTO objects which are processed by our batch job.

public class StudentDTO { private String emailAddress; private String name; private String purchasedSubscription; public StudentDTO() {} public String getEmailAddress() { return emailAddress; } public String getName() { return name; } public String getPurchasedSubscription() { return purchasedPackage; } public void setEmailAddress(String emailAddress) { this.emailAddress = emailAddress; } public void setName(String name) { this.name = name; } public void setPurchasedSubscription(String purchasedSubscription) { this.purchasedSubscription = purchasedSubscription; } }

After this we can configure an ItemReader bean, which reads the student information from the students.xlsx file, by following these steps:

Create an ExcelFileToDatabaseJobConfig class and annotate it with the @Configuration annotation. This class is the configuration class of our batch job, and it contains the beans that will describe the flow of our batch job.
Create a method that configures our ItemReader bean and returns an ItemReader object.
We can implement the method in the following way:
1. Create a new PoiItemReader object.
2. The created reader should ignore the header of our spreadsheet.
3. Configure the created reader which will read the student information from the data/students.xlsx file that is found from the classpath.
4. Configure the reader to transform a student information row into a StudentDTO object with the BeanWrapperRowMapper class. This class populates the fields of the created StudentDTO object by using the column names given on header row of our excel sheet.
5. Return the created PoiItemReader object.

Please find below the source code of the ExcelFileToDatabaseJobConfig class:

import org.springframework.batch.item.ItemReader; import org.springframework.batch.item.excel.RowMapper; import org.springframework.batch.item.excel.mapping.BeanWrapperRowMapper; import org.springframework.batch.item.excel.poi.PoiItemReader; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.core.env.Environment; import org.springframework.core.io.ClassPathResource; @Configuration public class ExcelFileToDatabaseJobConfig { @Bean ItemReader<StudentDTO> excelStudentReader() { PoiItemReader<StudentDTO> reader = new PoiItemReader<>(); reader.setLinesToSkip(1); reader.setResource(new ClassPathResource("data/students.xlsx")); reader.setRowMapper(excelRowMapper()); return reader; } private RowMapper<StudentDTO> excelRowMapper() { BeanWrapperRowMapper<StudentDTO> rowMapper = new BeanWrapperRowMapper<>(); rowMapper.setTargetType(StudentDTO.class); return rowMapper; } }

This approach will work as long as our Excel spreadsheet has a header row and we can resolve the column names of the header row into the field names of the StudentDTO class.

However, if we need to read the input data from a spreadsheet that doesn?t have a header row, we have to create a custom RowMapper that transforms the rows of our excel into StudentDTO objects. We can create a custom RowMapper by following these steps:

Create a StudentExcelRowMapper class.
Implement an interface RowMapper and pass the type of created object (StudentDTO) as a type parameter.
Implement a method T mapRow(RowSet rowSet) of the RowMapper interface in the following way:
1. Create a new StudentDTO object.
2. Populate the field values of the object. We can read the column values of the processed row by invoking the getColumnValue(int columnIndex) method of the RowSet interface. The index of the first column is 0.
3. Return the created StudentDTO object.

Following is the source code of the StudentExcelRowMapper class:

import org.springframework.batch.item.excel.RowMapper; import org.springframework.batch.item.excel.support.rowset.RowSet; public class StudentExcelRowMapper implements RowMapper<StudentDTO> { @Override public StudentDTO mapRow(RowSet rowSet) throws Exception { StudentDTO student = new StudentDTO(); student.setName(rowSet.getColumnValue(0)); student.setEmailAddress(rowSet.getColumnValue(1)); student.setPurchasedPackage(rowSet.getColumnValue(2)); return student; } }

After creating the custom row mapper, we have to make the following changes to the configuration of our ItemReader bean:

Our ItemReader should not ignore the first line of the input data.
Replace the old excelRowMapper() method with a method which will return a new StudentExcelRowMapper object.

After we have made these changes to the ExcelFileToDatabaseJobConfig class, its source code looks as follows:

import org.springframework.batch.item.ItemReader; import org.springframework.batch.item.excel.RowMapper; import org.springframework.batch.item.excel.poi.PoiItemReader; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.core.io.ClassPathResource; @Configuration public class ExcelFileToDatabaseJobConfig { @Bean ItemReader<StudentDTO> excelStudentReader() { PoiItemReader<StudentDTO> reader = new PoiItemReader<>(); reader.setResource(new ClassPathResource("data/students.xlsx")); reader.setRowMapper(excelRowMapper()); return reader; } private RowMapper<StudentDTO> excelRowMapper() { return new StudentExcelRowMapper(); } }