Excelfile Data Upload Into Mysql Table + Spring4

The Excel file is a spreadsheet file format created by Microsoft for use with Microsoft Excel. You can use the file to create, view, edit, analyse data, charts, budgets and more. In this tutorial, I will bear witness you how to upload/import Excel file data into MySQL Database using Spring Kick & Apache POI, then export Residuum API to return Excel file from database table.

Related Posts:
– Spring Boot Multipart File upload example
– How to upload multiple files in Java Jump Boot
– Upload/Import CSV file to MySQL Database in Leap Boot
– Jump Kicking: Download Excel file from MySQL database tabular array

Deployment:
– Deploy Spring Boot App on AWS – Elastic Beanstalk
– Docker Etch: Spring Kicking and MySQL example

Spring Boot Rest APIs for uploading Excel Files

Assume that nosotros take an .xlsx file that contains Tutorial information as post-obit:

We're gonna create a Spring Boot Application that provides APIs for:

uploading Excel File to the Bound Server & storing data in MySQL Database
getting listing of items from MySQL table
downloading MySQL table data as Excel file

After the Excel file is uploaded successfully, tutorials table in MySQL database will look similar this:

If we become list of Tutorials, the Spring Rest Apis will return:

Spring Kick Rest API returns Excel File

If you lot send asking to /api/excel/download, the server will return a response with an Excel file tutorials.xlsx that contains data in MySQL table:

How to practise this?
Yous need to set the HTTP header:

          "Content-disposition" : "attachment; filename=[yourFileName]"  "Content-Type" : "application/vnd.ms-excel"

You can find step by step for downloading Excel file in the tutorial:
Spring Boot: Download Excel file from MySQL database table

These are APIs to be exported:

Methods	Urls	Actions
POST	/api/excel/upload	upload an Excel File
GET	/api/excel/tutorials	get Listing of items in db table
GET	/api/excel/download	download db data as Excel file

Engineering science

Java 8
Spring Boot 2 (with Bound Spider web MVC)
Maven iii.6.one
Apache POI four.ane.2

Project Structure

This is the project directory that we're gonna build:

– ExcelHelper provides functions to read Excel file.

– Tutorial information model class corresponds to entity and table tutorials.
– TutorialRepository is an interface that extends JpaRepository for persisting data.

– ExcelService uses ExcelHelper and TutorialRepository methods to save Excel data to MySQL, load data to Excel file, or get all Tutorials from MySQL table.

– ExcelController calls ExcelService methods and consign Rest APIs: upload Excel file, get data from MySQL database.

– FileUploadExceptionAdvice handles exception when the controller processes file upload.

– application.backdrop contains configuration for Bound Data and Servlet Multipart file.
– pom.xml for Spring Boot, MySQL connector, Apache POI dependencies.

Setup Jump Boot Excel File Upload project

Use Jump web tool or your development tool (Leap Tool Suite, Eclipse, Intellij) to create a Spring Kicking project.

Then open pom.xml and add together these dependencies:

          <dependency> 	<groupId>org.springframework.boot</groupId> 	<artifactId>spring-kick-starter-information-jpa</artifactId> </dependency> <dependency> 	<groupId>org.springframework.boot</groupId> 	<artifactId>bound-boot-starter-web</artifactId> </dependency> <dependency> 	<groupId>org.apache.poi</groupId> 	<artifactId>poi-ooxml</artifactId> 	<version>four.1.2</version> </dependency> <dependency> 	<groupId>mysql</groupId> 	<artifactId>mysql-connector-java</artifactId> 	<scope>runtime</scope> </dependency>

Configure Spring Datasource, JPA, Hide

Under src/main/resources folder, open up awarding.backdrop and write these lines.

          spring.datasource.url= jdbc:mysql://localhost:3306/testdb?useSSL=imitation leap.datasource.username= root spring.datasource.password= 123456 bound.jpa.backdrop.hibernate.dialect= org.hibernate.dialect.MySQL5InnoDBDialect # Hibernate ddl auto (create, create-drib, validate, update) spring.jpa.hibernate.ddl-motorcar= update

spring.datasource.username & bound.datasource.countersign properties are the same as your database installation.
Spring Boot uses Hibernate for JPA implementation, we configure MySQL5InnoDBDialect for MySQL database
spring.jpa.hibernate.ddl-auto is used for database initialization. We gear up the value to update value so that a table will be created in the database automatically corresponding to defined data model. Any change to the model volition also trigger an update to the table. For production, this property should be validate.

Define Data Model

Our Information model is Tutorial with iv fields: id, title, description, published.
In model parcel, we define Tutorial form.

model/Tutorial.java

          package com.bezkoder.spring.files.excel.model; import javax.persistence.Cavalcade; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.Table; @Entity @Tabular array(name = "tutorials") public class Tutorial {   @Id   @Column(name = "id")   private long id;   @Column(proper name = "championship")   private String title;   @Column(proper name = "description")   private String clarification;   @Column(proper name = "published")   private boolean published;   public Tutorial() {   }   public Tutorial(String championship, String description, boolean published) {     this.title = title;     this.description = description;     this.published = published;   }   public long getId() {     render id;   }   public void setId(long id) {     this.id = id;   }   public Cord getTitle() {     render championship;   }   public void setTitle(String title) {     this.title = title;   }   public String getDescription() {     return clarification;   }   public void setDescription(Cord description) {     this.clarification = description;   }   public boolean isPublished() {     render published;   }   public void setPublished(boolean isPublished) {     this.published = isPublished;   }   @Override   public Cord toString() {     return "Tutorial [id=" + id + ", title=" + title + ", desc=" + clarification + ", published=" + published + "]";   } }

– @Entity note indicates that the class is a persistent Java class.
– @Table annotation provides the tabular array that maps this entity.
– @Id note is for the principal primal.
– @Column note is used to define the column in database that maps annotated field.

Create Data Repository for working with Database

Let's create a repository to interact with Tutorials from the database.
In repository package, create TutorialRepository interface that extends JpaRepository.

repository/TutorialRepository.java

          package com.bezkoder.spring.files.excel.repository; import org.springframework.data.jpa.repository.JpaRepository; import com.bezkoder.leap.files.excel.model.Tutorial; public interface TutorialRepository extends JpaRepository<Tutorial, Long> { }

Now we can apply JpaRepository's methods: save(), findOne(), findById(), findAll(), count(), delete(), deleteById()… without implementing these methods.

The quantity of rows in Excel file (also tutorials table) could be big, and so yous may want to get merely several at once by modifying this Repository to work with Pagination, the education can be plant at:
Spring Boot Pagination & Filter example | Spring JPA, Pageable

More than Derived queries at:
JPA Repository query example in Spring Kicking

Custom query with @Query annotation:
Leap JPA @Query example: Custom query in Spring Boot

You also find way to write Unit of measurement Test for this JPA Repository at:
Spring Boot Unit Test for JPA Repositiory with @DataJpaTest

Implement Read/Write Excel Helper Form

We're gonna utilize Apache POI classes such every bit: Workbook, Sail, Row, Cell.
Allow me summarize the steps for reading from Excel file:

create Workbook from InputStream
create Canvass using Workbook.getSheet() method
iterate over Rows by Iterator with Sheet.iterator() and Iterator.hasNext()
from each Row, iterate over Cells
with each Cell, use getNumericCellValue(), getStringCellValue()… methods to read and parse the content

          Workbook workbook = new XSSFWorkbook(inputStream); Sheet sheet = workbook.getSheet(SHEET); Iterator<Row> rows = canvass.iterator(); while (rows.hasNext()) {   Row currentRow = rows.adjacent();   Iterator<Prison cell> cellsInRow = currentRow.iterator();   while (cellsInRow.hasNext()) {      Cell currentCell = cellsInRow.adjacent();      // each cell instance      id = (long) currentCell.getNumericCellValue();      title = currentCell.getStringCellValue();      published = currentCell.getBooleanCellValue();   }       workbook.close();

Under helper package, we create ExcepHelper class with two methods:

hasExcelFormat(): check if a file has Excel format or not
excelToTutorials(): read InputStream of a file, return a listing of Tutorials

Hither is full code of helper/ExcelHelper.coffee:

          package com.bezkoder.jump.files.excel.helper; import java.io.IOException; import java.io.InputStream; import coffee.util.ArrayList; import coffee.util.Iterator; import coffee.util.Listing; import org.apache.poi.ss.usermodel.Prison cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Canvass; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.web.multipart.MultipartFile; import com.bezkoder.spring.files.excel.model.Tutorial; public class ExcelHelper {   public static Cord TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.canvas";   static Cord[] HEADERs = { "Id", "Championship", "Description", "Published" };   static String SHEET = "Tutorials";   public static boolean hasExcelFormat(MultipartFile file) {     if (!TYPE.equals(file.getContentType())) {       return false;     }     render truthful;   }   public static List<Tutorial> excelToTutorials(InputStream is) {     try {       Workbook workbook = new XSSFWorkbook(is);       Canvass canvas = workbook.getSheet(SHEET);       Iterator<Row> rows = canvass.iterator();       List<Tutorial> tutorials = new ArrayList<Tutorial>();       int rowNumber = 0;       while (rows.hasNext()) {         Row currentRow = rows.next();         // skip header         if (rowNumber == 0) {           rowNumber++;           keep;         }         Iterator<Cell> cellsInRow = currentRow.iterator();         Tutorial tutorial = new Tutorial();         int cellIdx = 0;         while (cellsInRow.hasNext()) {           Jail cell currentCell = cellsInRow.next();           switch (cellIdx) {           case 0:             tutorial.setId((long) currentCell.getNumericCellValue());             intermission;           instance i:             tutorial.setTitle(currentCell.getStringCellValue());             interruption;           instance 2:             tutorial.setDescription(currentCell.getStringCellValue());             pause;           example 3:             tutorial.setPublished(currentCell.getBooleanCellValue());             break;           default:             break;           }           cellIdx++;         }         tutorials.add together(tutorial);       }       workbook.close();       render tutorials;     } grab (IOException e) {       throw new RuntimeException("fail to parse Excel file: " + e.getMessage());     }   } }

Don't forget to change the sheet name to Tutorials (or any name y'all want). Information technology'southward because we create Sheet object from that proper name.

          static String SHEET = "Tutorials"; ... Canvas sheet = workbook.getSheet(SHEET);

Create Excel File Service

ExcelService grade will be annotated with @Service annotation, information technology uses ExcelHelper and TutorialRepository for 2 functions:

relieve(MultipartFile file): store Excel data to database
getAllTutorials(): read data from database and return Listing<Tutorial>

service/ExcelService.java

          package com.bezkoder.spring.files.excel.service; import java.io.IOException; import coffee.util.List; import org.springframework.beans.manufactory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile; import com.bezkoder.leap.files.excel.helper.ExcelHelper; import com.bezkoder.spring.files.excel.model.Tutorial; import com.bezkoder.spring.files.excel.repository.TutorialRepository; @Service public class ExcelService {   @Autowired   TutorialRepository repository;   public void save(MultipartFile file) {     try {       List<Tutorial> tutorials = ExcelHelper.excelToTutorials(file.getInputStream());       repository.saveAll(tutorials);     } grab (IOException due east) {       throw new RuntimeException("fail to shop excel information: " + e.getMessage());     }   }   public List<Tutorial> getAllTutorials() {     return repository.findAll();   } }

Define Response Bulletin

The ResponseMessage is for bulletin to customer that nosotros're gonna utilize in Remainder Controller and Exception Handler.

message/ResponseMessage.java

          package com.bezkoder.spring.files.excel.message; public class ResponseMessage {   private String bulletin;   public ResponseMessage(String message) {     this.message = message;   }   public String getMessage() {     render message;   }   public void setMessage(String message) {     this.message = bulletin;   } }

Create Controller for Upload Excel Files

In controller package, we create ExcelController class for Rest Apis.
– @CrossOrigin is for configuring allowed origins.
– @Controller annotation indicates that this is a controller.
– @GetMapping and @PostMapping annotation is for mapping HTTP Become & Mail requests onto specific handler methods:

POST /upload: uploadFile()
GET /tutorials: getAllTutorials()

– We utilise @Autowired to inject implementation of ExcelService bean to local variable.

controller/ExcelController.java

          package com.bezkoder.spring.files.excel.controller; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.http.HttpStatus; import org.springframework.http.ResponseEntity; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.notation.RequestMapping; import org.springframework.web.demark.note.RequestParam; import org.springframework.web.multipart.MultipartFile; import com.bezkoder.jump.files.excel.helper.ExcelHelper; import com.bezkoder.spring.files.excel.message.ResponseMessage; import com.bezkoder.leap.files.excel.model.Tutorial; import com.bezkoder.spring.files.excel.service.ExcelService; @CrossOrigin("http://localhost:8081") @Controller @RequestMapping("/api/excel") public form ExcelController {   @Autowired   ExcelService fileService;   @PostMapping("/upload")   public ResponseEntity<ResponseMessage> uploadFile(@RequestParam("file") MultipartFile file) {     String message = "";     if (ExcelHelper.hasExcelFormat(file)) {       try {         fileService.save(file);         bulletin = "Uploaded the file successfully: " + file.getOriginalFilename();         render ResponseEntity.status(HttpStatus.OK).body(new ResponseMessage(message));       } catch (Exception east) {         message = "Could not upload the file: " + file.getOriginalFilename() + "!";         return ResponseEntity.status(HttpStatus.EXPECTATION_FAILED).torso(new ResponseMessage(message));       }     }     message = "Please upload an excel file!";     render ResponseEntity.status(HttpStatus.BAD_REQUEST).body(new ResponseMessage(message));   }   @GetMapping("/tutorials")   public ResponseEntity<Listing<Tutorial>> getAllTutorials() {     try {       List<Tutorial> tutorials = fileService.getAllTutorials();       if (tutorials.isEmpty()) {         return new ResponseEntity<>(HttpStatus.NO_CONTENT);       }       return new ResponseEntity<>(tutorials, HttpStatus.OK);     } catch (Exception e) {       return new ResponseEntity<>(aught, HttpStatus.INTERNAL_SERVER_ERROR);     }   } }

Configure Multipart File for Servlet

Allow'southward define the maximum file size that can be uploaded in awarding.backdrop every bit following:

          spring.servlet.multipart.max-file-size=2MB bound.servlet.multipart.max-asking-size=2MB

spring.servlet.multipart.max-file-size: max file size for each request.
spring.servlet.multipart.max-request-size: max request size for a multipart/form-data.

Handle File Upload Exception

This is where we handle the case in that a request exceeds Max Upload Size. The system will throw MaxUploadSizeExceededException and we're gonna use @ControllerAdvice with @ExceptionHandlerannotation for handling the exceptions.

exception/FileUploadExceptionAdvice.java

          package com.bezkoder.spring.files.excel.exception; import org.springframework.spider web.multipart.MaxUploadSizeExceededException; import org.springframework.spider web.servlet.mvc.method.annotation.ResponseEntityExceptionHandler; import com.bezkoder.spring.files.excel.bulletin.ResponseMessage; import org.springframework.http.HttpStatus; import org.springframework.http.ResponseEntity; import org.springframework.web.bind.annotation.ControllerAdvice; import org.springframework.web.bind.note.ExceptionHandler; @ControllerAdvice public form FileUploadExceptionAdvice extends ResponseEntityExceptionHandler {   @ExceptionHandler(MaxUploadSizeExceededException.class)   public ResponseEntity<ResponseMessage> handleMaxSizeException(MaxUploadSizeExceededException exc) {     return ResponseEntity.status(HttpStatus.EXPECTATION_FAILED).torso(new ResponseMessage("File also large!"));   } }

Run & Cheque

Run Spring Boot awarding with command: mvn bound-boot:run.

Let's apply Postman to brand some requests.

If you upload a file with size larger than max file size (2MB):

Conclusion

Today we've built a Residuum Grime API using Spring Boot to upload and import data from Excel file to Mysql database table.

Nosotros likewise come across how to use Apache POI to read information from Excel Canvass, JpaRepository to remember items in database tabular array without demand of average code.

More Derived queries at:
JPA Repository query example in Spring Kicking

Custom query with @Query annotation:
Spring JPA @Query instance: Custom query in Spring Kicking

If yous want to add Pagination to this Jump project, you tin can find the education at:
Spring Boot Pagination & Filter case | Leap JPA, Pageable

For downloading Excel file:
Spring Kicking: Download Excel file from MySQL database table

Upload CSV File instead:
Leap Boot: Upload CSV file Data into MySQL Database

Or upload files to database as BLOB:
Spring Kicking Upload/Download File to/from Database example

Happy learning! See yous once more.

Farther Reading

Spring Information JPA Reference Documentation
https://github.com/apache/poi

Deployment:
– Deploy Leap Kicking App on AWS – Elastic Beanstalk
– Docker Compose: Spring Kick and MySQL example

Source Code

Y'all tin can find the complete source lawmaking for this tutorial on Github.

smithinart1960.blogspot.com

Source: https://www.bezkoder.com/spring-boot-upload-excel-file-database/