Spring Batch Tutorial: Reading Information From an Excel File

It is pretty easy to create a Spring Batch job that reads its input data from a CSV or an XML file because these file formats are supported out of the box.

However, if you want to read the input data of your batch job from a .XLS or .XLSX file that was created with Excel, you have to work a bit harder. This blog post helps you to solve that problem.

Let’s get started.

Introduction to the Example Application

During this blog post you will learn to configure an ItemReader bean that can read the input data of your batch job from an Excel file. This file contains a student list that provides the following information for your batch job:

• The name of the student.
• The email address of the student.
• The name of the purchased package.

When you read the student information from an Excel file, you have to transform that information into StudentDTO objects which are processed by your batch job. The StudentDTO class contains the information of a single student, and its source code looks as follows:

 1public class StudentDTO {
 2
 3    private String emailAddress;
 4    private String name;
 5    private String purchasedPackage;
 6
 7    public StudentDTO() {}
 8
 9    public String getEmailAddress() {
10        return emailAddress;
11    }
12
13    public String getName() {
14        return name;
15    }
16
17    public String getPurchasedPackage() {
18        return purchasedPackage;
19    }
20
21    public void setEmailAddress(String emailAddress) {
22        this.emailAddress = emailAddress;
23    }
24
25    public void setName(String name) {
26        this.name = name;
27    }
28
29    public void setPurchasedPackage(String purchasedPackage) {
30        this.purchasedPackage = purchasedPackage;
31    }
32}

Before you can configure the ItemReader that reads student information from your Excel file, you have to add a few dependencies to the classpath.

Getting the Required Dependencies

If you want to read the input data of your Spring Batch job from an Excel document, you have to add the following dependencies to the classpath:

• Spring Batch Excel is a Spring Batch extension that provides ItemReader implementations for Excel. Unfortunately at the moment the only way to get the required jar file is to build it from the source.
• Apache POI provides a Java API which helps you to read (and create) Microsoft Office documents. It’s an optional dependency of Spring Batch Excel, and you can use it for reading the input data of your batch job from .XLS and .XLSX documents.

After you have added the required dependencies to the classpath, you can finally configure the ItemReader that reads the student information from your Excel spreadsheet.

Reading Information From an Excel File

The students.xlsx file contains the student list of an online course. This file is found from the classpath and its full path is: data/students.xlsx. The content of this Excel spreadsheet looks as follows:

1NAME			|EMAIL_ADDRESS				|PURCHASED_PACKAGE
2Tony Tester		|tony.tester@gmail.com		|master
3Nick Newbie		|nick.newbie@gmail.com		|starter
4Ian Intermediate|ian.intermediate@gmail.com	|intermediate

As you already know, you can provide the input data for your Spring batch job by configuring an ItemReader bean. Because you must read the input data of your batch job from an Excel document, you can configure the ItemReader bean by following these steps:

1. Create an ExcelFileToDatabaseJobConfig class and annotate it with the @Configuration annotation. This class is the configuration class of your batch job, and it contains the beans that describe the flow of your batch job.
2. Create a method that configures your ItemReader bean and ensure that the method returns an ItemReader<StudentDTO> object.
3. Implement the created method by following these steps:
4. Create a new PoiItemReader<StudentDTO> object.
5. Ensure that the created reader ignores the header of your spreadsheet.
6. Configure the created reader to read the student information from the data/students.xlsx file that’s found from the classpath.
7. 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 the header row of your spreadsheet.
8. Return the created PoiItemReader<StudentDTO> object.

The source code of the ExcelFileToDatabaseJobConfig class looks as follows:

 1import org.springframework.batch.item.ItemReader;
 2import org.springframework.batch.item.excel.RowMapper;
 3import org.springframework.batch.item.excel.mapping.BeanWrapperRowMapper;
 4import org.springframework.batch.item.excel.poi.PoiItemReader;
 5import org.springframework.context.annotation.Bean;
 6import org.springframework.context.annotation.Configuration;
 7import org.springframework.core.env.Environment;
 8import org.springframework.core.io.ClassPathResource;
 9
10@Configuration
11public class ExcelFileToDatabaseJobConfig {
12
13    @Bean
14    ItemReader<StudentDTO> excelStudentReader() {
15        PoiItemReader<StudentDTO> reader = new PoiItemReader<>();
16        reader.setLinesToSkip(1);
17        reader.setResource(new ClassPathResource("data/students.xlsx"));
18        reader.setRowMapper(excelRowMapper());
19        return reader;
20    }
21
22    private RowMapper<StudentDTO> excelRowMapper() {
23        BeanWrapperRowMapper<StudentDTO> rowMapper = new BeanWrapperRowMapper<>();
24        rowMapper.setTargetType(StudentDTO.class);
25        return rowMapper;
26    }
27}

This approach works as long as your Excel spreadsheet has a header row and the column names of the header row can be resolved into the field names of the StudentDTO class.

However, it’s entirely possible that you have to read the input data from a spreadsheet that doesn’t have a header row. If this is the case, you have to create a custom RowMapper that transforms the rows of your spreadsheet into StudentDTO objects.

You can create a custom RowMapper by following these steps:

1. Create a StudentExcelRowMapper class.
2. Implement the RowMapper<T> interface and pass the type of created object (StudentDTO) as a type parameter.
3. Implement the T mapRow(RowSet rowSet) method of the RowMapper<T> interface by following these steps:
4. Create a new StudentDTO object.
5. Populate the field values of the created object. You can read the column values of the processed row by invoking the getColumnValue(int columnIndex) method of the RowSet interface. Also, you must remember that the index of the first column is 0.
6. Return the created StudentDTO object.

The source code of the StudentExcelRowMapper class looks as follows:

 1import org.springframework.batch.item.excel.RowMapper;
 2import org.springframework.batch.item.excel.support.rowset.RowSet;
 3
 4public class StudentExcelRowMapper implements RowMapper<StudentDTO> {
 5
 6    @Override
 7    public StudentDTO mapRow(RowSet rowSet) throws Exception {
 8        StudentDTO student = new StudentDTO();
 9
10        student.setName(rowSet.getColumnValue(0));
11        student.setEmailAddress(rowSet.getColumnValue(1));
12        student.setPurchasedPackage(rowSet.getColumnValue(2));
13
14        return student;
15    }
16}

After you have created your custom row mapper, you have to make the following changes to the configuration of your ItemReader bean:

1. Ensure that the your ItemReader doesn’t ignore the first line of the input data.
2. Replace the old excelRowMapper() method with a method that returns a new StudentExcelRowMapper object.

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

 1import org.springframework.batch.item.ItemReader;
 2import org.springframework.batch.item.excel.RowMapper;
 3import org.springframework.batch.item.excel.poi.PoiItemReader;
 4import org.springframework.context.annotation.Bean;
 5import org.springframework.context.annotation.Configuration;
 6import org.springframework.core.io.ClassPathResource;
 7
 8@Configuration
 9public class ExcelFileToDatabaseJobConfig {
10
11    @Bean
12    ItemReader<StudentDTO> excelStudentReader() {
13        PoiItemReader<StudentDTO> reader = new PoiItemReader<>();
14        reader.setResource(new ClassPathResource("data/students.xlsx"));
15        reader.setRowMapper(excelRowMapper());
16        return reader;
17    }
18
19	private RowMapper<StudentDTO> excelRowMapper() {
20       return new StudentExcelRowMapper();
21    }
22}

Let’s summarize what you learned from this blog post.

Summary

This blog post has taught you four things:

• If you want to read the input data of a Spring Batch job from an Excel spreadsheet, you have to add Spring Batch Excel and Apache POI dependencies to the classpath.
• If you want to read the input data of your batch job by using Spring Batch Excel and Apache POI, you have to use the PoiItemReader class.
• You can map the rows of your spreadsheet into T objects by using the BeanWrapperRowMapper<T> class as long as your Excel spreadsheet has a header row and the column names of the header row can be resolved into the field names of the T class.
• If your Excel spreadsheet doesn’t have a header row or the column names of the header row cannot be resolved into the field names of the T class, you have to create a custom row mapper component that implements the RowMapper<T> interface.

The next part of this tutorial describes how you can write the output data of your batch job to a CSV file.

P.S. You can get the example applications of this blog post from Github: Spring example and Spring Boot example.