Import excel file data into MySQL with Spring Boot and Apache POI

In this tutorial, we will practice how to read excel file and import the excel data into MySQL database with Spring Boot and Apache POI.

Table of Contents

  • Setup a Spring Boot Service Application with Spring Initializer.
  • Connect to MySQL database.
  • Read data from excel.
  • Upload excel and import data with REST API.

Setup a Spring Boot Service Application

Use start.spring.io to create a “web” project. In the “Dependencies” dialog search for and add the “web” dependency as shown in the screenshot. Hit the “Generate” button, download the zip, and unpack it into a folder on your computer.

Projects created by start.spring.io contain Spring Boot, a framework that makes Spring ready to work inside your app, but without much code or configuration required. Spring Boot is the quickest and most popular way to start Spring projects.

Unzip and open the project with IntelliJ IDEA.

Connect to MySQL database

In following steps we will create a MySQL database and db tables, and also add a REST API which return the data query from MySQL database.

Step 1 – Create database and db tables

Use following db scripts to create database, db table and also insert mock data into the db table.

create database import_excel_demo;

use import_excel_demo;

create table student(
	id int auto_increment,
	student_no varchar(50),
	first_name varchar(100),
	last_name varchar(100),
	age int,
	address text,
    primary key(id)
);

insert into student(student_no, first_name, last_name, age, address)
values('2021001', 'frank', 'test', 30, 'mocked address');

Step 2 – Add MySQL Connector Dependency

Add mysql-connector-java dependency into pom.xml file:

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

Step 3 – Add MySQL Connection String Settings

Add following settings into file application.properties:

# MySQL db
spring.datasource.url=jdbc:mysql://localhost:3306/import_excel_demo?useUnicode\=true&characterEncoding\=utf-8&useSSL\=false&serverTimezone\=GMT%2B8
spring.datasource.username=importdemo
spring.datasource.password=abc@123
spring.jpa.show-sql=true
  • import_excel_demo : it is the database name
  • importdemo: it is the MySQL user account
  • abc@123: it is the user account’s password

Note: this settings is only for demo purpose, please do not use above settings in your real project !

Step 4 – Add Test REST API for Querying DB Data

Create entity model for student db table:

@Entity
@Table(name = "student")
public class StudentEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public int id;
    public String student_no;
    public String first_name;
    public String last_name;
    public int age;
    public String address;
}

Create student business model:

public class Student {
    public int id;
    public String studentNo;
    public String firstName;
    public String lastName;
    public int age;
    public String address;
}

Create StudentRepository:

public interface StudentRepository extends CrudRepository<StudentEntity, Integer> {
    @Override
    List<StudentEntity> findAll();
}

Create TestController and add a REST API to query all students data:

@RestController
@RequestMapping("/api/v1/student")
public class TestController {

    @Autowired
    StudentRepository studentRepository;

    @GetMapping("/get-students")
    public List<Student> GetSudents(){
        List<Student> result = new ArrayList<>();

        List<StudentEntity> entities = studentRepository.findAll();

        if (entities != null && entities.size() > 0){
            entities.forEach(x->{
                Student item = new Student();
                item.id = x.id;
                item.studentNo = x.student_no;
                item.firstName = x.first_name;
                item.lastName = x.last_name;
                item.age = x.age;
                item.address = x.address;

                result.add(item);
            });
        }

        return result;
    }
}

Full code structure:

Step 5 – Build, Run and Test REST API

Use following commands to build and run the service:

$ mvn package
$ java -jar ./target/ImportExcelDemo-0.0.1-SNAPSHOT.jar

Note that you can comment the Unit Test code if there are compile error, unit test is not covered in this tutorial.

The default service port is 8080.

Open the url http://localhost:8080/api/v1/student/get-students directly, then will get the json response.

You can also test the REST API with other popular tools like Postman, Rest Client Plugin in VSCode and so on.

Read Data from Excel

We have created a REST API and return the data query from the MySQL db data. Following are the steps for how to import excel file data into MySQL db table.

Step 1 – Add Apache POI Dependency

Add org.apache.poi dependency in pom.xml file:

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>4.1.2</version>
		</dependency>

Step 2 – Add Swagger or Open API Support

Swagger or OpenAPI user interface allows you to view REST services and execute GET, POST, PUT, DELETE HTTP endpoints. This is helpful since you do not need to use Postman or some other tool to test REST Apis. Especially for upload excel file test.

Firstly, add dependencies in pom.xml:

		<dependency>
			<groupId>io.springfox</groupId>
			<artifactId>springfox-swagger2</artifactId>
			<version>2.9.2</version>
		</dependency>
		<dependency>
			<groupId>io.springfox</groupId>
			<artifactId>springfox-swagger-ui</artifactId>
			<version>2.9.2</version>
		</dependency>

Secondly, add property settings in application.properties:

# Swagger
swagger.enable=true

At last, add @EnableSwagger2 attribute for application class, in our case it is ImportExcelDemoApplication.

@SpringBootApplication
@EnableSwagger2
public class ImportExcelDemoApplication {

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

}

Step 3 – Add Import Excel Rest API

Following are the import api, and following with two private methods which are using for format the cell data.

@PostMapping("/import-order-excel")
    public List<Student> importExcelFile(@RequestParam("file") MultipartFile files)throws IOException {

        List<Student> students = new ArrayList<>();

        XSSFWorkbook workbook = new XSSFWorkbook(files.getInputStream());

        // Read student data form excel file sheet1.
        XSSFSheet worksheet = workbook.getSheetAt(0);
        for (int index = 0; index < worksheet.getPhysicalNumberOfRows(); index++) {
            if (index > 0) {
                XSSFRow row = worksheet.getRow(index);
                Student student = new Student();

                student.studentNo = getCellValue(row, 0);
                student.firstName = getCellValue(row, 1);
                student.lastName = getCellValue(row, 2);
                student.age = convertStringToInt(getCellValue(row, 3));
                student.address = getCellValue(row, 4);

                students.add(student);
            }
        }

        // Save to db.
        List<StudentEntity> entities = new ArrayList<>();
        if (students.size() > 0) {
            students.forEach(x->{
                StudentEntity entity = new StudentEntity();
                entity.student_no = x.studentNo;
                entity.first_name = x.firstName;
                entity.last_name = x.lastName;
                entity.age = x.age;
                entity.address = x.address;

                entities.add(entity);
            });

            studentRepository.saveAll(entities);
        }

        return students;
    }

    private int convertStringToInt(String str) {
        int result = 0;

        if (str == null || str.isEmpty() || str.trim().isEmpty()) {
            return result;
        }

        result = Integer.parseInt(str);

        return result;
    }

    private String getCellValue(Row row, int cellNo) {
        DataFormatter formatter = new DataFormatter();

        Cell cell = row.getCell(cellNo);

        return formatter.formatCellValue(cell);
    }

Note:

  • Read all excel cell data as string value at first. it is converted by method String getCellValue(Row row, int cellNo).
  • For age which is Integer type, we use method int convertStringToInt(String str) to convert string to int value.
  • At last, we save all read data into MySQL db table.

Step 4 – Run and Test Swagger UI

Use following command to do package and start service:

$ mvn package
$ java -jar ./target/ImportExcelDemo-0.0.1-SNAPSHOT.jar

Open URL http://localhost:8080/swagger-ui.html#/ in browser and will see the swagger UI like:

Step 5 – Import Excel File with Swagger UI

Prepare a students data excel as following:

Choose the excel file and click execute to import with swagger UI as following:

You can call get-students api to check the new students result after executing the import action.

Summary

In this tutorial, we have created a Spring Boot API service from scratch. We also done following things in this API services:

  • Connect to MySQL database and return the DB data in the rest API.
  • Added Swagger or OpenAPI support.
  • Upload excel file with Swagger UI and read excel data with Apache POI.
  • Save data in excel file to MySQL DB table.

The full code example is available in GitHub here.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.