How to Process Excel Files with Spring Batch in Spring Boot 3

1. Introduction

For a comprehensive guide on Spring Batch basics, see the two referenced articles. This article shows how to read Excel files (.xls or .xlsx) using PoiItemReader, convert rows to User objects, and write or process the data.

2. Practical Example

2.1 Prepare Environment

Add the required dependencies:

<properties>
  <poi.version>5.4.1</poi.version>
</properties>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-batch</artifactId>
</dependency>
<dependency>
  <groupId>org.springframework.batch.extensions</groupId>
  <artifactId>spring-batch-excel</artifactId>
  <version>0.1.1</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>${poi.version}</version>
  <optional>true</optional>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>${poi.version}</version>
  <optional>true</optional>
</dependency>

2.2 Prepare Excel Data

Upload an Excel file containing 200 rows of user data (example image omitted).

2.3 Define Data Model

public class User {
  private Long id;
  private String name;
  private Integer age;
  private Integer sexCode;
  private String email;
  private String createTime;
}

2.4 Define Reader

@Bean
ItemReader<User> userReader(@Value("classpath:datas/users.xlsx") Resource data) {
  PoiItemReader<User> reader = new PoiItemReader<>();
  reader.setLinesToSkip(1);
  reader.setResource(data);
  reader.setRowMapper(new UserRowMapper());
  return reader;
}

2.5 Define RowMapper

public class UserRowMapper implements RowMapper<User> {
  @Override
  public User mapRow(RowSet rs) throws Exception {
    User user = new User();
    Properties props = rs.getProperties();
    user.setId(Long.valueOf(props.getProperty("编号")));
    user.setName(props.getProperty("姓名"));
    user.setAge(Integer.valueOf(props.getProperty("年龄")));
    user.setSexCode(props.getProperty("性别").equals("男") ? 0 : 1);
    user.setEmail(props.getProperty("邮箱"));
    user.setCreateTime(props.getProperty("创建事件"));
    return user;
  }
}

2.6 Define Processor

public class UserProcessor implements ItemProcessor<User, User> {
  private static final Logger logger = LoggerFactory.getLogger(UserProcessor.class);
  @Override
  public User process(User item) throws Exception {
    logger.info("Processing User data: {}", item);
    return item;
  }
}

2.7 Define Writer

public class UserWriter implements ItemWriter<User> {
  private static final Logger logger = LoggerFactory.getLogger(UserWriter.class);
  @Override
  public void write(Chunk<? extends User> chunk) throws Exception {
    logger.info("Writing {} records", chunk.size());
    // TODO: implement actual write logic
  }
}

2.8 Bean Configuration (Step and Job)

@Configuration
public class BatchConfig {
  @Bean
  ItemWriter<User> userWriter() { return new UserWriter(); }
  @Bean
  UserProcessor userProcessor() { return new UserProcessor(); }
  @Bean
  Step step1(ItemReader<User> userReader, JobRepository jobRepository, PlatformTransactionManager transactionManager) {
    return new StepBuilder("excelfile-to-step1", jobRepository)
      .<User, User>chunk(10, transactionManager)
      .reader(userReader)
      .processor(userProcessor())
      .writer(userWriter())
      .build();
  }
  @Bean
  Job excelFileToDatabaseJob(Step step1, JobRepository jobRepository) {
    return new JobBuilder("excelfile-to-db", jobRepository)
      .start(step1)
      .build();
  }
}

2.9 Test with CommandLineRunner

@Component
public class JobRunner implements CommandLineRunner {
  private final Job job;
  private final JobLauncher jobLauncher;
  public JobRunner(Job job, JobLauncher jobLauncher) {
    this.job = job;
    this.jobLauncher = jobLauncher;
  }
  @Override
  public void run(String... args) throws Exception {
    JobParameters params = new JobParametersBuilder()
      .addJobParameter("currentTimestamp", new JobParameter(LocalDateTime.now(), LocalDateTime.class))
      .toJobParameters();
    jobLauncher.run(job, params);
  }
}

2.10 Optimization for Large Files

While PoiItemReader is feature‑rich, it consumes significant memory for large Excel files. Replace it with StreamingXlsxItemReader, which keeps only the current row in memory.

@Bean
ItemReader<User> userReader(@Value("classpath:datas/users.xlsx") Resource data) {
  StreamingXlsxItemReader<User> reader = new StreamingXlsxItemReader<>();
  // configure reader as needed
  return reader;
}

Update the UserRowMapper to use RowSet.getCurrentRow():

public class UserRowMapper implements RowMapper<User> {
  @Override
  public User mapRow(RowSet rs) throws Exception {
    User user = new User();
    String[] currentRow = rs.getCurrentRow();
    user.setId(Long.valueOf(currentRow[0]));
    user.setName(currentRow[1]);
    user.setAge(Integer.valueOf(currentRow[2]));
    user.setSexCode(currentRow[3].equals("男") ? 0 : 1);
    user.setEmail(currentRow[4]);
    user.setCreateTime(currentRow[5]);
    return user;
  }
}

Using the streaming reader dramatically reduces memory consumption, as shown in the benchmark images.