When Uploading an Excel Data File

The Excel file is a spreadsheet file format created by Microsoft for apply with Microsoft Excel. You can use the file to create, view, edit, analyse data, charts, budgets and more. In this tutorial, I will show you how to upload/import Excel file data into MySQL Database using Spring Boot & Apache POI, then export Remainder 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 Bound Boot
– Spring Kicking: Download Excel file from MySQL database tabular array

Deployment:
– Deploy Spring Boot App on AWS – Elastic Beanstalk
– Docker Compose: Jump Boot and MySQL example

Leap Boot Rest APIs for uploading Excel Files

Assume that we take an .xlsx file that contains Tutorial data every bit post-obit:

Nosotros're gonna create a Spring Kicking Application that provides APIs for:

uploading Excel File to the Bound Server & storing information in MySQL Database
getting list of items from MySQL tabular array
downloading MySQL table data equally Excel file

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

If nosotros go list of Tutorials, the Spring Rest Apis volition return:

Bound Boot Rest API returns Excel File

If yous send request to /api/excel/download, the server volition return a response with an Excel file tutorials.xlsx that contains data in MySQL table:

How to do this?
You demand to set the HTTP header:

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

You lot tin detect step by step for downloading Excel file in the tutorial:
Bound 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
Become	/api/excel/tutorials	get List of items in db table
Become	/api/excel/download	download db data equally Excel file

Technology

Coffee 8
Spring Boot two (with Jump Spider web MVC)
Maven 3.6.1
Apache POI four.ane.2

Project Construction

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

– ExcelHelper provides functions to read Excel file.

– Tutorial data 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 salvage Excel data to MySQL, load data to Excel file, or go all Tutorials from MySQL table.

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

– FileUploadExceptionAdvice handles exception when the controller processes file upload.

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

Setup Leap Kicking Excel File Upload project

Utilise Spring web tool or your development tool (Spring Tool Suite, Eclipse, Intellij) to create a Spring Boot project.

Then open pom.xml and add these dependencies:

          <dependency> 	<groupId>org.springframework.kicking</groupId> 	<artifactId>leap-boot-starter-information-jpa</artifactId> </dependency> <dependency> 	<groupId>org.springframework.boot</groupId> 	<artifactId>spring-boot-starter-spider web</artifactId> </dependency> <dependency> 	<groupId>org.apache.poi</groupId> 	<artifactId>poi-ooxml</artifactId> 	<version>4.i.2</version> </dependency> <dependency> 	<groupId>mysql</groupId> 	<artifactId>mysql-connector-java</artifactId> 	<telescopic>runtime</scope> </dependency>

Configure Spring Datasource, JPA, Hide

Nether src/principal/resources folder, open up application.properties and write these lines.

          leap.datasource.url= jdbc:mysql://localhost:3306/testdb?useSSL=imitation jump.datasource.username= root spring.datasource.password= 123456 spring.jpa.properties.hide.dialect= org.hibernate.dialect.MySQL5InnoDBDialect # Hide ddl automobile (create, create-drop, validate, update) bound.jpa.hibernate.ddl-auto= update

leap.datasource.username & spring.datasource.password backdrop are the same as your database installation.
Spring Boot uses Hibernate for JPA implementation, we configure MySQL5InnoDBDialect for MySQL database
spring.jpa.hide.ddl-motorcar is used for database initialization. We set the value to update value then that a tabular array will be created in the database automatically corresponding to defined data model. Any change to the model will too trigger an update to the table. For product, this property should exist validate.

Ascertain Information Model

Our Data model is Tutorial with four fields: id, title, clarification, published.
In model package, we define Tutorial class.

model/Tutorial.java

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

– @Entity annotation indicates that the class is a persistent Java course.
– @Tabular array annotation provides the table that maps this entity.
– @Id note is for the primary cardinal.
– @Column annotation is used to define the cavalcade in database that maps annotated field.

Create Data Repository for working with Database

Let's create a repository to collaborate 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.spring.files.excel.model.Tutorial; public interface TutorialRepository extends JpaRepository<Tutorial, Long> { }

At present we can use 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 exist big, so you may want to become only several at once past modifying this Repository to piece of work with Pagination, the didactics can be found at:
Bound Kicking Pagination & Filter example | Jump JPA, Pageable

More Derived queries at:
JPA Repository query instance in Bound Boot

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

You too observe way to write Unit Test for this JPA Repository at:
Leap Boot Unit Test for JPA Repositiory with @DataJpaTest

Implement Read/Write Excel Helper Form

We're gonna use Apache POI classes such as: Workbook, Canvass, Row, Cell.
Permit me summarize the steps for reading from Excel file:

create Workbook from InputStream
create Sheet using Workbook.getSheet() method
iterate over Rowsouthward by Iterator with Sail.iterator() and Iterator.hasNext()
from each Row, iterate over Cells
with each Jail cell, utilize getNumericCellValue(), getStringCellValue()… methods to read and parse the content

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

Under helper parcel, we create ExcepHelper grade with two methods:

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

Here is total code of helper/ExcelHelper.coffee:

          bundle com.bezkoder.leap.files.excel.helper; import coffee.io.IOException; import coffee.io.InputStream; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import org.apache.poi.ss.usermodel.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 String TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.canvas";   static String[] HEADERs = { "Id", "Title", "Description", "Published" };   static String Sheet = "Tutorials";   public static boolean hasExcelFormat(MultipartFile file) {     if (!TYPE.equals(file.getContentType())) {       return false;     }     return true;   }   public static Listing<Tutorial> excelToTutorials(InputStream is) {     try {       Workbook workbook = new XSSFWorkbook(is);       Sheet sheet = workbook.getSheet(SHEET);       Iterator<Row> rows = sheet.iterator();       List<Tutorial> tutorials = new ArrayList<Tutorial>();       int rowNumber = 0;       while (rows.hasNext()) {         Row currentRow = rows.adjacent();         // skip header         if (rowNumber == 0) {           rowNumber++;           continue;         }         Iterator<Jail cell> cellsInRow = currentRow.iterator();         Tutorial tutorial = new Tutorial();         int cellIdx = 0;         while (cellsInRow.hasNext()) {           Cell currentCell = cellsInRow.side by side();           switch (cellIdx) {           instance 0:             tutorial.setId((long) currentCell.getNumericCellValue());             pause;           example 1:             tutorial.setTitle(currentCell.getStringCellValue());             intermission;           case two:             tutorial.setDescription(currentCell.getStringCellValue());             intermission;           case 3:             tutorial.setPublished(currentCell.getBooleanCellValue());             pause;           default:             suspension;           }           cellIdx++;         }         tutorials.add together(tutorial);       }       workbook.close();       return tutorials;     } catch (IOException e) {       throw new RuntimeException("fail to parse Excel file: " + e.getMessage());     }   } }

Don't forget to change the canvas name to Tutorials (or any name you lot desire). Information technology's because we create Canvass object from that name.

          static String SHEET = "Tutorials"; ... Sheet sail = workbook.getSheet(Sheet);

Create Excel File Service

ExcelService form will exist annotated with @Service annotation, it uses ExcelHelper and TutorialRepository for 2 functions:

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

service/ExcelService.coffee

          package com.bezkoder.bound.files.excel.service; import coffee.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.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 {       Listing<Tutorial> tutorials = ExcelHelper.excelToTutorials(file.getInputStream());       repository.saveAll(tutorials);     } catch (IOException e) {       throw new RuntimeException("neglect to store excel data: " + eastward.getMessage());     }   }   public List<Tutorial> getAllTutorials() {     return repository.findAll();   } }

Define Response Message

The ResponseMessage is for message to customer that we're gonna utilize in Rest Controller and Exception Handler.

message/ResponseMessage.coffee

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

Create Controller for Upload Excel Files

In controller package, nosotros 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 Get & POST requests onto specific handler methods:

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

– Nosotros use @Autowired to inject implementation of ExcelService bean to local variable.

controller/ExcelController.java

          bundle 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.demark.notation.GetMapping; import org.springframework.web.bind.notation.PostMapping; import org.springframework.web.demark.annotation.RequestMapping; import org.springframework.web.bind.notation.RequestParam; import org.springframework.web.multipart.MultipartFile; import com.bezkoder.spring.files.excel.helper.ExcelHelper; import com.bezkoder.spring.files.excel.bulletin.ResponseMessage; import com.bezkoder.spring.files.excel.model.Tutorial; import com.bezkoder.spring.files.excel.service.ExcelService; @CrossOrigin("http://localhost:8081") @Controller @RequestMapping("/api/excel") public grade ExcelController {   @Autowired   ExcelService fileService;   @PostMapping("/upload")   public ResponseEntity<ResponseMessage> uploadFile(@RequestParam("file") MultipartFile file) {     String bulletin = "";     if (ExcelHelper.hasExcelFormat(file)) {       try {         fileService.salvage(file);         message = "Uploaded the file successfully: " + file.getOriginalFilename();         return ResponseEntity.condition(HttpStatus.OK).torso(new ResponseMessage(bulletin));       } catch (Exception east) {         message = "Could not upload the file: " + file.getOriginalFilename() + "!";         return ResponseEntity.status(HttpStatus.EXPECTATION_FAILED).body(new ResponseMessage(message));       }     }     bulletin = "Delight upload an excel file!";     return ResponseEntity.status(HttpStatus.BAD_REQUEST).body(new ResponseMessage(bulletin));   }   @GetMapping("/tutorials")   public ResponseEntity<List<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<>(zilch, HttpStatus.INTERNAL_SERVER_ERROR);     }   } }

Configure Multipart File for Servlet

Permit's define the maximum file size that can exist uploaded in awarding.properties every bit following:

          spring.servlet.multipart.max-file-size=2MB spring.servlet.multipart.max-request-size=2MB

jump.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 nosotros handle the case in that a request exceeds Max Upload Size. The system volition throw MaxUploadSizeExceededException and we're gonna use @ControllerAdvice with @ExceptionHandlerannotation for handling the exceptions.

exception/FileUploadExceptionAdvice.coffee

          package com.bezkoder.spring.files.excel.exception; import org.springframework.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.spider web.demark.annotation.ControllerAdvice; import org.springframework.web.bind.annotation.ExceptionHandler; @ControllerAdvice public form FileUploadExceptionAdvice extends ResponseEntityExceptionHandler {   @ExceptionHandler(MaxUploadSizeExceededException.class)   public ResponseEntity<ResponseMessage> handleMaxSizeException(MaxUploadSizeExceededException exc) {     render ResponseEntity.status(HttpStatus.EXPECTATION_FAILED).body(new ResponseMessage("File as well large!"));   } }

Run & Check

Run Spring Boot application with command: mvn spring-boot:run.

Allow'due south use Postman to brand some requests.

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

Conclusion

Today we've congenital a Balance Crud API using Spring Boot to upload and import data from Excel file to Mysql database tabular array.

We too meet how to utilise Apache POI to read data from Excel Sail, JpaRepository to recall items in database tabular array without demand of average code.

More Derived queries at:
JPA Repository query example in Jump Boot

Custom query with @Query notation:
Spring JPA @Query case: Custom query in Leap Boot

If you want to add Pagination to this Spring project, you can find the instruction at:
Spring Kicking Pagination & Filter example | Spring JPA, Pageable

For downloading Excel file:
Bound Boot: Download Excel file from MySQL database table

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

Or upload files to database as Blob:
Jump Boot Upload/Download File to/from Database example

Happy learning! See y'all once again.

Source Code

You can find the complete source code for this tutorial on Github.

gaskinslaught.blogspot.com

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