Import Data from Excel to Mysql using Spring Boot

Tutorial: “Import Data from Excel to Mysql using Spring Boot – SpringBoot Upload/Import and Download/Export Excel files to MySQL”

Creating SpringBoot RestAPIs to upload/import and export/download Excel files to databases: PostgreSQL or MySQL is one of the most common question in the development world. Going through the tutorial post, I explain details “How to read data from Excel Sheet and insert into Database table in Spring Boot RestAPIs with Upload/Download Excel functions” by step to step with coding and give you 100% running source code. What we will do?

– I draw full diagram architecture of SpringBoot Upload Download Excel files service.
– I implements how to Excel File service using poi-ooxml.
– I implement SpringBoot Upload Download files RestApis.
– I use Spring JPA to interact with database MySQL.
– I implement a global exception handling to deal with a big uploading file
– I use Ajax and Bootstrap to develop frontend for uploading and downloading excel files.

To do the tutorial, we need prepare Java >= 8, Eclipse with SpringToolSuite, PostgreSQL and pgAdmin or MySQL, a RestClient (I use Advanced Rest Client).

Now let’s go!

Youtube Video Guide – Import Data from Excel to Mysql using Spring Boot

Overview – Import Data from Excel to Mysql using Spring Boot

Here is an overview about workflow data of Upload/Download Excel Files project with SpringBoot.

– We implement an Ajax or use a Rest client to upload/download Excel files to/from SpringBoot application.
– For manipulating Excel files, we develop an Excel Utils class to write and read data from them.
– We implement a File Service to do CRUD operations with PostgreSQL/MySQL that supported by SpringJPA Repository.

Here is an overview of SpringBoot project that we will implement in the tutorial:

• controller package implements Controller RestAPIs classes to upload/download Excel files from Ajax or a Rest-Client
• repository package defines a CRUD JPA Repository: CustomerRepository
• services package implements a class ExcelFileServices with functions to store and retrieve Excel File’s data
• utils package implements a class ExcelUtils with common functions for reading or writing data to/from Excel files
• model package defines a class Customer for mapping data between each Excel’s row with each database’s record
• errorhandler package implements a class RestExceptionHandler to handle an exception: file size exceeded

Create SpringBoot – Import Data from Excel to Mysql using Spring Boot

We use Eclipse to create a SpringBoot project with a set of below dependencies:

• spring-boot-starter-data-jpa dependency is used to interact with PostgreSQL/MySQL database
• postgresql or mysql-connector-java drivers are used to connect with PostgreSQL/MySQL database
• spring-boot-starter-web dependency is used to implement Spring RestAPIs Controller

For writting and reading Excel files, we use a dependency poi-ooxml of org.apache.poi.

In the tutorial, we mainly do a demo with PostgreSQL database, so here is the details of necessary dependencies in pom.xml file:

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.1.2</version>
</dependency>
<dependency>
	<groupId>org.postgresql</groupId>
	<artifactId>postgresql</artifactId>
	<scope>runtime</scope>
</dependency>

SpringBoot Read data from Excel file – Import Data from Excel to Mysql using Spring Boot

We define a function parseExcelFile to read an Excel file and return a list data Customer, here is a signature of the util function:

public static List parseExcelFile(InputStream is) {
    ...
}

We use a high level representation of an Excel workbook org.apache.poi.ss.usermodel.Workbook to do the task.

From the Workbook, we get an Excel Sheet, and reading all data rows but skip the header row. With a Excel row, we fill each cell data to a corresponding field of a Customer entity. After all, result is a list of Customer entities be returned.

Here is details of the util function:

public static List parseExcelFile(InputStream is) {
	try {
		Workbook workbook = new XSSFWorkbook(is);

		Sheet sheet = workbook.getSheet("Customers");
		Iterator rows = sheet.iterator();

		List lstCustomers = new ArrayList();

		int rowNumber = 0;
		while (rows.hasNext()) {
			Row currentRow = rows.next();

			// skip header
			if (rowNumber == 0) {
				rowNumber++;
				continue;
			}

			Iterator cellsInRow = currentRow.iterator();

			Customer cust = new Customer();

			int cellIndex = 0;
			while (cellsInRow.hasNext()) {
				Cell currentCell = cellsInRow.next();

				if (cellIndex == 0) { // ID
					cust.setId((long) currentCell.getNumericCellValue());
				} else if (cellIndex == 1) { // Name
					cust.setName(currentCell.getStringCellValue());
				} else if (cellIndex == 2) { // Address
					cust.setAddress(currentCell.getStringCellValue());
				} else if (cellIndex == 3) { // Age
					cust.setAge((int) currentCell.getNumericCellValue());
				}

				cellIndex++;
			}

			lstCustomers.add(cust);
		}

		// Close WorkBook
		workbook.close();

		return lstCustomers;
	} catch (IOException e) {
		throw new RuntimeException("FAIL! -> message = " + e.getMessage());
	}
}

SpringBoot Write data to Excel file – Import Data from Excel to Mysql using Spring Boot

For writting data to Excel file, we define a customersToExcel utility function that get a list of Customer entities and return a ByteArrayInputStream object as below signature:

public static ByteArrayInputStream customersToExcel(List customers) throws IOException {
    ...
}

We continue to use a high level representation of a Excel worksheet org.apache.poi.ss.usermodel.Sheet to create a header and data rows for Excel file. We go through each customer entity and fill each property of the customer to a corresponding cell of a Excel row.

Here is coding details of the function:

public static ByteArrayInputStream customersToExcel(List customers) throws IOException {
	String[] COLUMNs = { "Id", "Name", "Address", "Age" };
	try (Workbook workbook = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream();) {
		CreationHelper createHelper = workbook.getCreationHelper();

		Sheet sheet = workbook.createSheet("Customers");

		Font headerFont = workbook.createFont();
		headerFont.setBold(true);
		headerFont.setColor(IndexedColors.BLUE.getIndex());

		CellStyle headerCellStyle = workbook.createCellStyle();
		headerCellStyle.setFont(headerFont);

		// Row for Header
		Row headerRow = sheet.createRow(0);

		// Header
		for (int col = 0; col < COLUMNs.length; col++) {
			Cell cell = headerRow.createCell(col);
			cell.setCellValue(COLUMNs[col]);
			cell.setCellStyle(headerCellStyle);
		}

		// CellStyle for Age
		CellStyle ageCellStyle = workbook.createCellStyle();
		ageCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));

		int rowIdx = 1;
		for (Customer customer : customers) {
			Row row = sheet.createRow(rowIdx++);

			row.createCell(0).setCellValue(customer.getId());
			row.createCell(1).setCellValue(customer.getName());
			row.createCell(2).setCellValue(customer.getAddress());

			Cell ageCell = row.createCell(3);
			ageCell.setCellValue(customer.getAge());
			ageCell.setCellStyle(ageCellStyle);
		}

		workbook.write(out);
		return new ByteArrayInputStream(out.toByteArray());
	}
}

SpringBoot Checking a MultipartFile file is an Excel file?

We just define a simple utility function isExcelFile as below code:

public static String EXCELTYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

public static boolean isExcelFile(MultipartFile file) {
	
	if(!EXCELTYPE.equals(file.getContentType())) {
		return false;
	}
	
	return true;
}

Define Spring Boot JPA Repository - Import Data from Excel to Mysql using Spring Boot

To do CRUD operations with PostgreSQL/MySQL database, we define a simple CustomerRepository interface that extends the org.springframework.data.repository.CrudRepository,

package com.loizenai.excelfile.repository;

import org.springframework.data.repository.CrudRepository;

import com.loizenai.excelfile.model.Customer;

public interface CustomerRepository extends CrudRepository{
}

See related article: How to integrate SpringBoot 2.x with PostgreSQL database using Spring JPA

Implement a SpringBoot Excel File Service

We create a Excel File Service ExcelFileServices to do 2 tasks:

• Store data to database with supported API: store(MultipartFile file)
• Load data from database to a Excel file with supported API ByteArrayInputStream loadFile()

ExcelFileServices class uses the Spring JPA repository CustomerRepository to interact with database and use the utility functions of ExcelUtils class to manipulate data with Excel files.

Here is coding details:

package com.loizenai.excelfile.services;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.loizenai.excelfile.model.Customer;
import com.loizenai.excelfile.repository.CustomerRepository;
import com.loizenai.excelfile.utils.ExcelUtils;

@Service
public class ExcelFileServices {

	@Autowired
	CustomerRepository customerRepository;

	// Store File Data to Database
	public void store(MultipartFile file) {
		try {
			List lstCustomers = ExcelUtils.parseExcelFile(file.getInputStream());
			// Save Customers to DataBase
			customerRepository.saveAll(lstCustomers);
		} catch (IOException e) {
			throw new RuntimeException("FAIL! -> message = " + e.getMessage());
		}
	}

	// Load Data to Excel File
	public ByteArrayInputStream loadFile() {
		List customers = (List) customerRepository.findAll();

		try {
			ByteArrayInputStream in = ExcelUtils.customersToExcel(customers);
			return in;
		} catch (IOException e) {}

		return null;
	}
}

* Note: org.springframework.stereotype.Service indicates that an annotated class is a "Service"

Implement SpringBoot Upload Excel files RestApi

We create a posting API /api/uploadfiles for uploading multiple Excel files, and here is a signature detail:

@PostMapping("/api/uploadfiles")
public Message uploadFileMulti(
        @RequestParam("uploadfiles") MultipartFile[] uploadfiles) {

The function uploadFileMulti does the 2 main taks:

1. Validated task: check names and type of all requested files before processing them
2. Store Excel files' data to database

Now we create a rest controller UploadFileRestAPIs that implements the above API:

package com.loizenai.excelfile.controller;

import java.util.Arrays;
import java.util.stream.Collectors;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import com.loizenai.excelfile.message.Message;
import com.loizenai.excelfile.services.ExcelFileServices;
import com.loizenai.excelfile.utils.ExcelUtils;

@RestController
public class UploadFileRestAPIs {

	@Autowired
	ExcelFileServices fileServices;
	
	@PostMapping("/api/uploadfiles")
	public Message uploadFileMulti(
	        @RequestParam("uploadfiles") MultipartFile[] uploadfiles) {

        // Get file name
        String uploadedFileName = Arrays.stream(uploadfiles).map(x -> x.getOriginalFilename())
                .filter(x -> !StringUtils.isEmpty(x)).collect(Collectors.joining(" , "));

        if (StringUtils.isEmpty(uploadedFileName)) {
            return new Message(uploadedFileName, "please select a file!", "fail");
        }
        
        String notExcelFiles = Arrays.stream(uploadfiles).filter(x -> !ExcelUtils.isExcelFile(x))
        							.map(x -> x.getOriginalFilename())
        							.collect(Collectors.joining(" , "));
        
        if(!StringUtils.isEmpty(notExcelFiles)) {
        	return new Message(notExcelFiles, "Not Excel Files", "fail");
        }
        
        try {
        	
        	for(MultipartFile file: uploadfiles) {
        		fileServices.store(file);
        	}
            return new Message(uploadedFileName, "Upload Successfully", "ok");

        } catch (Exception e) {
        	return new Message(uploadedFileName, e.getMessage(), "fail");
        }
    }
}

The UploadFileRestAPIs uses the ExcelFileServices service to store Excel files' data to database.

@RestController is a convenience annotation that is itself annotated with @Controller and @ResponseBody.

The returned class Message contains a set of necessary information to return back to requested client, includes 3 attributes: filename, message, status.

Here is coding details of Message class:

package com.loizenai.excelfile.message;

public class Message {
	private String filename;
	private String message;
	private String status;
	
	public Message(String filename, String message, String status) {
		this.filename = filename;
		this.message = message;
		this.status = status;
	}
	
        // getters and setters
        // ...

Implement SpringBoot Download Excel file RestApi

We implement a rest controller DownloadFileRestAPIs having a downloadFile() API with getting URL /api/file to load all entities from database and serve them as a excel file back to requested client.

Here is coding details:

package com.loizenai.excelfile.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.InputStreamResource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import com.loizenai.excelfile.services.ExcelFileServices;

@RestController
public class DownloadFileRestAPIs {

	@Autowired
	ExcelFileServices fileServices;

	/*
	 * Download Files
	 */
	@GetMapping("/api/file")
	public ResponseEntity downloadFile() {

		HttpHeaders headers = new HttpHeaders();
		headers.add("Content-Disposition", "attachment; filename=customers.xlsx");
		
		return ResponseEntity.ok().headers(headers)
									.contentType(MediaType.parseMediaType("application/vnd.ms-excel"))
									.body(new InputStreamResource(fileServices.loadFile()));
	}
}

org.springframework.http.ResponseEntity is an extension of HttpEntity that adds a HttpStatus status code.

With above coding, the API returns a file that has name is customers.xlsx, type is application/vnd.ms-excel (Excel file type) and status code is 200 (.ok()) for successfully processing.

Configure SpringBoot PostgreSQL/MySQL database connection

In the tutorial "Import Data from Excel to Mysql using Spring Boot", We add database configuration in application.properties file.

- For PostgreSQL connection, you follow below configuration formatting:

## PostgreSQL
spring.datasource.url=jdbc:postgresql://localhost:5432/loizenaidb
spring.datasource.username=postgres
spring.datasource.password=123
#drop & create table again, good for testing, comment this in production
spring.jpa.hibernate.ddl-auto=create

- For MySQL connection, you follow below configuration formatting:

spring.datasource.url=jdbc:mysql://localhost:3306/loizenaidb
spring.datasource.username=root
spring.datasource.password=12345
#drop & create table again, good for testing, comment this in production
spring.jpa.hibernate.ddl-auto=create

Test the implemented RestAPIs with Rest-Client

In the tutorial "Import Data from Excel to Mysql using Spring Boot", I design a set of testcase for the tutorial: "SpringBoot & Excel file Upload/Download - Import Data from Excel to MySQL using Spring Boot RestApis".

We create a POST request to URL http://localhost:8080/api/uploadfiles that has a body with multipart/form-data content type and includes 2 Excel files as below:

Run the SpringBoot project then send the POST request, we get a response successfully as below details:

Checking database, we get a customer table with 10 records as below:

- Do a GET request to get a Excel file from URL http://localhost:8080/api/file:

Test SpringBoot Upload a big Excel file

We upload a very big Excel file or just comment the Excel checking type segment code in UploadFileRestAPIs class to test a big file uploading.

Here is the Excel checking type segment code:

...
if(!StringUtils.isEmpty(notExcelFiles)) {
	return new Message(notExcelFiles, "Not Excel Files", "fail");
}
...

- We do a big file uploading to SpringBoot RestApi,

We get a 500 error response with an exception message throwed from SpringBoot server:

org.apache.tomcat.util.http.fileupload.impl.SizeLimitExceededException: the request was rejected because its size (45422988) exceeds the configured maximum (10485760)
	at org.apache.tomcat.util.http.fileupload.impl.FileItemIteratorImpl.init(FileItemIteratorImpl.java:150) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.util.http.fileupload.impl.FileItemIteratorImpl.getMultiPartStream(FileItemIteratorImpl.java:194) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.util.http.fileupload.impl.FileItemIteratorImpl.findNextItem(FileItemIteratorImpl.java:213) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.util.http.fileupload.impl.FileItemIteratorImpl.(FileItemIteratorImpl.java:131) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.util.http.fileupload.FileUploadBase.getItemIterator(FileUploadBase.java:255) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.tomcat.util.http.fileupload.FileUploadBase.parseRequest(FileUploadBase.java:279) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.connector.Request.parseParts(Request.java:2870) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.connector.Request.getParts(Request.java:2772) ~[tomcat-embed-core-9.0.33.jar:9.0.33]
	at org.apache.catalina.connector.RequestFacade.getParts(RequestFacade.java:1098) ~[tomcat-embed-core-9.0.33.jar:9.0.33]

This is a "Maximum upload size exceeded" exception. How to catch and handle the exception? We go to the next session for a right solution.

Implement Springboot Uploading Rest Exception Handler

For handling the "Maximum upload size exceeded" exception, we create a RestExceptionHandler class that extends the ResponseEntityExceptionHandler class.

package com.loizenai.excelfile.errorhandler;

import javax.servlet.http.HttpServletRequest;

import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.ControllerAdvice;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartException;
import org.springframework.web.servlet.mvc.method.annotation.ResponseEntityExceptionHandler;

@ControllerAdvice
public class RestExceptionHandler extends ResponseEntityExceptionHandler {

    // Catch file size exceeded exception!
    @ExceptionHandler(MultipartException.class)
    @ResponseBody
    ResponseEntity handleControllerException(HttpServletRequest request, Throwable ex) {
        HttpStatus status = getStatus(request);
        return new ResponseEntity(new Error("0x123", ex.getMessage()), status);
    }

    private HttpStatus getStatus(HttpServletRequest request) {
        Integer statusCode = (Integer) request.getAttribute("javax.servlet.error.status_code");
        if (statusCode == null) {
            return HttpStatus.INTERNAL_SERVER_ERROR;
        }
        return HttpStatus.valueOf(statusCode);
    }
}

org.springframework.web.bind.annotation.ControllerAdvice is a specialization of @Component for classes that declare @ExceptionHandler, @InitBinder, or @ModelAttribute methods to be shared across multiple @Controller classes.

org.springframework.web.servlet.mvc.method.annotation.ResponseEntityExceptionHandler is a convenient base class for @ControllerAdvice classes that wish to provide centralized exception handling across all @RequestMapping methods through @ExceptionHandler methods. This base class provides an @ExceptionHandler method for handling internal Spring MVC exceptions. This method returns a ResponseEntity for writing to the response with a message converter. (refer Spring docs)

If having any multipartfile exception, SpringBoot returns back to rest client a ResponseEntity object with body is a user-defined Error entity:

package com.loizenai.excelfile.errorhandler;

public class Error {
	private String errCode;
	private String errDesc;
	
	public Error(String errCode, String errDesc) {
		this.errCode = errCode;
		this.errDesc = errDesc;
	}
	
        // ...

Now we do a testcase with a big file uploading again:

SpringBoot Tuning File Upload Limits

We can tune Spring Boot's auto-configured MultipartConfigElement with property settings. We add the following properties to your existing properties settings (in application.properties file):

spring.servlet.multipart.max-file-size=4096KB
spring.servlet.multipart.max-request-size=4096KB

The multipart settings are constrained as follows:

• spring.http.multipart.max-file-size is set to 4096KB, meaning total file size cannot exceed 4096KB.
• spring.http.multipart.max-request-size is set to 4096KB, meaning total request size for a multipart/form-data cannot exceed 4096KB.

Implement Ajax Client for frontend

In the tutorial "Import Data from Excel to Mysql using Spring Boot", we create a html file having a data form with 2 file inputs and use Ajax to upload the form to defined SpringBoot RestAPIs. We use bootstrap to create a html upload page as below:

<!DOCTYPE html>
<html lang="en">
<head>
<title>Upload Download Excel File Examples to PostgreSQL/MySQL</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js"></script>
<script type="text/javascript" src="/js/excelfiles.js"></script>
</head>
<body>
	<div class="container">
		<div class="row h-100 justify-content-center align-items-center">
			<div class="col-sm-6">
				<h3>Upload Excel Files to PostgreSQL</h3>
				<form id="fileUploadForm">
					<div class="form-group">
						<label class="control-label" for="uploadfile">Choose Excel Files:</label>
						<input type="file" class="form-control" placeholder=".xlxs file" name="uploadfiles"></input>
						<input type="file" class="form-control" placeholder=".xlxs file" name="uploadfiles"></input>
					</div>
					<button type="submit" class="btn btn-danger" id="btnSubmit">Upload</button>
				</form>
				<div id="uploadedfile" style="display:none">
				</div>
			</div>
		</div>
	</div>
</body>
</html>

We save the .html file in static folder under src/main/resources folder of SpringBoot project as index.html name.

We continue to implement an Ajax script to upload the data-form:

$(document).ready(function() {
	
	function checkExtension(){
		var selection = document.getElementsByName('uploadfiles');
		for (var i=0; icustomers.xlsx");
						$("#uploadedfile").css("display", "block");
						alert("Upload Successfully!");
					}else{
						$("#uploadedfile").css("display", "none");
						alert("Fail!");
					}
				},
				error: function(e){
					alert("Fail! " + e);
				}
			});
		}
		
		return false;
	});
	
})

With above script, the first step, we check the extension of uploading files that is .xlsx or NOT (Excel files or not). Then we use $.ajax script to process the uploading files. If successfully uploading files, we add a link to html page. Otherwise, we create an alert to notify for users with a Fail message.

Test Import Data from Excel to Mysql using Spring Boot with Ajax Client

- Upload Multi Excel files:

- Download Multi Excel files:

Sourcecode - Import Data from Excel to Mysql using Spring Boot

Below is the detail sourcecode for the tutorial: "SpringBoot RestAPI Upload Excel file to MySQL - SpringBoot Upload/Import and Download/Export Excel files to MySQL"

DownloadUploadExcelFile

- Github sourcecode:

SpringBoot Upload/Import Excel files to MySQL

Happy Learning! See you later!

Further Reading