How to Import Excel Data to a Database Using Spring Batch

Handling bulk data operations efficiently is a core requirement for modern enterprise applications. While Spring Batch provides fantastic, out-of-the-box support for reading flat files like CSVs and XML, it lacks a native reader for Microsoft Excel files (.xlsx).

To bridge this gap, we need to implement a custom ItemReader using the Apache POI library. In this tutorial, we will build a complete Spring Boot application that reads product data from an Excel spreadsheet and writes it directly into an Oracle database using Spring Data JPA.

📺 Watch the Live Coding!
If you prefer visual learning, watch the complete step-by-step implementation on our YouTube channel, including setting up the Apache POI dependencies and configuring the Oracle database connection.

▶ Watch the Spring Batch Excel Tutorial

1. The Source Excel Data

Before diving into the code, we must understand the structure of the data we are parsing. We have an Excel file named products.xlsx placed in our src/main/resources directory. The file contains a single sheet named Products with three columns.

Notice that Row 1 contains headers. A robust batch process must intentionally skip this header row to prevent parsing errors when converting string headers into numeric values.

Name (Column A)	Price (Column B)	Quantity (Column C)
Laptop	65000	10
Wireless Mouse	799	120
Mechanical Keyboard	2499	50

2. Application Properties & Database Setup

First, let's configure our Spring Boot environment. We are connecting to a local Oracle database instance. Add the following to your application.properties file. Ensure your pom.xml includes dependencies for Spring Batch, Spring Data JPA, the Oracle JDBC driver, and Apache POI (poi-ooxml).

spring.application.name=spring-batch-import-excel-file
spring.datasource.url=jdbc:oracle:thin:@//192.168.56.1:1521/freepdb1
spring.datasource.username=demo
spring.datasource.password=demo
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

3. Defining the Entity and Repository

Next, we define our data model. The Product class maps directly to the columns in our Excel file. We utilize an Oracle SEQUENCE strategy for primary key generation, ensuring high performance during bulk inserts.

package com.springjavalab.model;

import jakarta.persistence.*;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Entity
@Table(name = "products")
@NoArgsConstructor
@AllArgsConstructor
@Data
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "PRODUCTS_SEQ")
    @SequenceGenerator(name = "PRODUCTS_SEQ", sequenceName = "PRODUCTS_SEQ", initialValue = 1, allocationSize = 1)
    private Long id;

    private String name;
    private Double price;
    private Integer quantity;
}

We then create a standard Spring Data JPA Repository. Spring Batch's built-in writers will use this repository under the hood to persist our entities.

package com.springjavalab.repository;

import com.springjavalab.model.Product;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ProductRepository extends JpaRepository<Product, Long> {
}

4. Building the Custom Excel ItemReader

This is the core of our solution. Because Spring Batch requires readers to track their own state (to resume jobs or handle chunks), our custom reader must implement the ItemReader<T> interface and maintain a reference to the current row iterator. Let's break down exactly how this class works and why we engineered it this way:

1. The @StepScope Annotation:
This annotation is crucial for Spring Batch readers that hold state. It tells the Spring container to create a brand new instance of this reader for every single step execution. If we used a standard Singleton bean, multiple job runs would share the same rowIterator and isInitialized flag, leading to catastrophic thread-safety issues and unpredictable data processing.

2. Lazy Initialization (The initReader method):
Notice that we do not open the file or create the Workbook in the constructor. Instead, we wait until the read() method is called for the very first time. Inside initReader(), we use Apache POI's WorkbookFactory.create(inputStream) to load our Excel file, grab the "Products" sheet, and initialize our rowIterator.

3. Skipping the Header Row:
A common pitfall in batch processing is accidentally trying to parse the header text as database values. In our initReader() method, we explicitly check if (rowIterator.hasNext()) and call rowIterator.next() once before returning. This effectively consumes the header row ("Name", "Price", "Quantity") so our actual read() cycle starts purely on the data rows.

4. The read() Lifecycle & Cell Mapping:
Spring Batch operates by calling the read() method repeatedly until it returns null.

If there are rows remaining, we grab the next() row and instantiate an empty Product entity.
We extract data using zero-indexed cell positions: getCell(0) for Column A, getCell(1) for Column B, etc.
Because Apache POI treats all numeric Excel cells as double by default, we use getNumericCellValue() for the price, but we must explicitly cast the Quantity cell to an int using (int)row.getCell(2).getNumericCellValue().

5. Safe Resource Management:
Once !rowIterator.hasNext() evaluates to true, we have reached the end of the spreadsheet. Before returning null to signal completion to the Spring Batch framework, we invoke closeResources(). This safely closes the Workbook and the InputStream, which is absolutely mandatory to prevent severe memory leaks and locked files in production environments.

package com.springjavalab.batch;

import com.springjavalab.model.Product;
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.ss.usermodel.WorkbookFactory;
import org.jspecify.annotations.Nullable;
import org.springframework.batch.core.configuration.annotation.StepScope;
import org.springframework.batch.infrastructure.item.ItemReader;
import org.springframework.core.io.Resource;

import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;

@StepScope
public class ExcelItemReader implements ItemReader<Product>{

    private Iterator<Row> rowIterator;
    private Workbook workbook;
    private InputStream inputStream;
    private final Resource resource;
    private boolean isInitialized = false;

    public ExcelItemReader(Resource resource) {
        this.resource = resource;
    }

    @Override
    public @Nullable Product read() throws Exception {
        if (!isInitialized) {
            initReader();
        }

        if (!rowIterator.hasNext()) {
            closeResources();
            return null; // Signals the end of the data to Spring Batch
        }

        Row row = rowIterator.next();
        Product product = new Product();
        
        // Map zero-indexed Excel columns to our object
        product.setName(row.getCell(0).getStringCellValue());
        product.setPrice(row.getCell(1).getNumericCellValue());
        product.setQuantity((int)row.getCell(2).getNumericCellValue());

        return product;
    }

    private void initReader() throws IOException {
        this.inputStream = resource.getInputStream();
        this.workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheet("Products");
        
        if (sheet == null) {
            throw new IllegalArgumentException("Sheet 'Products' not found.");
        }
        
        this.rowIterator = sheet.iterator();
        
        // Skip the header row
        if (rowIterator.hasNext()) {
            rowIterator.next();
        }
        this.isInitialized = true;
    }

    private void closeResources() {
        try {
            if (workbook != null) {
                workbook.close();
            }
            if (inputStream != null) {
                inputStream.close();
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
}

5. Wiring the Spring Batch Job Configuration

Now we configure the batch topology. We define a Step that reads our data in chunks of 10. Chunk-oriented processing is vital for batch performance: it means Spring Batch will call our custom read() method 10 times, aggregate those 10 objects into a list, and pass that list to the writer within a single database transaction.

package com.springjavalab.config;

import com.springjavalab.batch.ExcelItemReader;
import com.springjavalab.model.Product;
import com.springjavalab.repository.ProductRepository;
import org.springframework.batch.core.job.Job;
import org.springframework.batch.core.job.builder.JobBuilder;
import org.springframework.batch.core.repository.JobRepository;
import org.springframework.batch.core.step.Step;
import org.springframework.batch.core.step.builder.StepBuilder;
import org.springframework.batch.infrastructure.item.data.RepositoryItemWriter;
import org.springframework.batch.infrastructure.item.data.builder.RepositoryItemWriterBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.transaction.PlatformTransactionManager;

@Configuration
public class BatchConfig {

    @Bean
    public ExcelItemReader excelItemReader() {
        return new ExcelItemReader(new ClassPathResource("products.xlsx"));
    }

    @Bean
    public RepositoryItemWriter<Product> productItemWriter(ProductRepository repository) {
        return new RepositoryItemWriterBuilder<Product>()
                .repository(repository)
                .methodName("save")
                .build();
    }

    @Bean
    public Step importExcelStep(JobRepository jobRepository,
                                PlatformTransactionManager transactionManager,
                                ExcelItemReader excelItemReader,
                                RepositoryItemWriter<Product> writer) {
        return new StepBuilder("importExcelStep", jobRepository)
                .<Product, Product>chunk(10)
                .transactionManager(transactionManager)
                .reader(excelItemReader)
                .writer(writer)
                .build();
    }

    @Bean
    public Job importProductJob(JobRepository jobRepository, Step importExcelStep) {
        return new JobBuilder("importProductJob", jobRepository)
                .start(importExcelStep)
                .build();
    }
}

6. Application Execution and Logs

Finally, we run our application. By default, Spring Boot triggers any configured Spring Batch jobs automatically upon startup.

package com.springjavalab;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringBatchImportExcelFileApplication {
    public static void main(String[] args) {
        SpringApplication.run(SpringBatchImportExcelFileApplication.class, args);
    }
}

When the application starts, you can monitor the HikariCP database pool initialization, the JPA entity manager startup, and ultimately the Job execution sequence in the console. Notice how the entire importExcelStep executes in just over 1 second.

INFO 22464 --- [main] .s.SpringBatchImportExcelFileApplication : Starting SpringBatchImportExcelFileApplication using Java 21.0.1
INFO 22464 --- [main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
INFO 22464 --- [main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
INFO 22464 --- [main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
INFO 22464 --- [main] .s.SpringBatchImportExcelFileApplication : Started SpringBatchImportExcelFileApplication in 7.946 seconds
INFO 22464 --- [main] o.s.b.c.l.s.TaskExecutorJobLauncher      : Job: [SimpleJob: [name=importProductJob]] launched with the following parameters: [{}]
INFO 22464 --- [main] o.s.batch.core.step.AbstractStep         : Executing step: [importExcelStep]
INFO 22464 --- [main] o.s.batch.core.step.AbstractStep         : Step: [importExcelStep] executed in 1s204ms
INFO 22464 --- [main] o.s.b.c.l.s.TaskExecutorJobLauncher      : Job: [SimpleJob: [name=importProductJob]] completed with the following parameters: [{}] and the following status: [COMPLETED] in 1s208ms
INFO 22464 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

Production Tip: Large Excel Files
In this tutorial, we used WorkbookFactory.create(). This loads the entire Excel file into memory. If you are dealing with files containing hundreds of thousands of rows, you will likely encounter OutOfMemoryError exceptions. For massive datasets, look into Apache POI's streaming API (SXSSF) or third-party libraries like FastExcel which are designed for low-memory footprint processing.

Conclusion

By combining Spring Batch's robust step-execution architecture with Apache POI's parsing capabilities, we can build highly resilient data pipelines. Designing a custom ItemReader gives us absolute control over how we handle headers, malformed cells, and resource closures, making our enterprise applications much safer for production environments.

🚀 Explore More Spring Batch Techniques

Take your data processing to the next level with these advanced backend guides.

Spring Java Lab

📂 Explore Topics

🔥 Featured Posts

How to Import Excel Data to a Database Using Spring Batch

1. The Source Excel Data

2. Application Properties & Database Setup

3. Defining the Entity and Repository

4. Building the Custom Excel ItemReader

5. Wiring the Spring Batch Job Configuration

6. Application Execution and Logs

Conclusion

🚀 Explore More Spring Batch Techniques

🧱 Spring Batch Core Components

🔄 Spring Batch ItemProcessor Example

🔁 CSV to Database with Spring Batch

🚫 Skip Policy & Error Handling

🔀 Conditional Flow in Spring Batch Jobs

🧵 Multithreaded Step in Spring Batch

Subscribe on YouTube

Search This Blog