In the post, Grokonez guides how to Upload/Download Excel Files to MySQL database using SpringBoot + Spring JPA with Apache POI library
Contents
Technologies
– Spring Boot – 2.0.6.RELEASE
– Spring JPA
– Thymeleaf
– MySQL
– Apache POI
Goal
We create a SpringBoot project as below:
-> Excel File:
-> Results:
-> MySQL:
Practice
Create SpringBoot project
We create a SpringBoot project with below dependencies:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</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>3.17</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> |
Customer Model
– Create Customer.java model:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 | package com.grokonez.excelfile.model; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.Table; @Entity @Table(name = "customer") public class Customer { @Id @GeneratedValue(strategy = GenerationType.AUTO) private long id; @Column(name = "name") private String name; @Column(name = "address") private String address; @Column(name = "age") private int age; public Customer() { } public Customer(long id, String name, String address, int age) { this.id = id; this.name = name; this.address = address; this.age = age; } public long getId() { return id; } public void setId(long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } @Override public String toString() { return "Customer [id=" + id + ", name=" + name + ", address=" + address + ", age=" + age + "]"; } } |
JPA Repository
– Create CustomerRepository.java:
1 2 3 4 5 6 7 8 | package com.grokonez.excelfile.repository; import org.springframework.data.repository.CrudRepository; import com.grokonez.excelfile.model.Customer; public interface CustomerRepository extends CrudRepository<Customer, Long>{ } |
Excel Utils
– Implement Excel Utils:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 | package com.grokonez.excelfile.util; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.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.CellStyle; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; 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.xssf.usermodel.XSSFWorkbook; import com.grokonez.excelfile.model.Customer; public class ExcelUtils { public static ByteArrayInputStream customersToExcel(List<Customer> 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()); } } public static List<Customer> parseExcelFile(InputStream is) { try { Workbook workbook = new XSSFWorkbook(is); Sheet sheet = workbook.getSheet("Customers"); Iterator<Row> rows = sheet.iterator(); List<Customer> lstCustomers = new ArrayList<Customer>(); int rowNumber = 0; while (rows.hasNext()) { Row currentRow = rows.next(); // skip header if(rowNumber == 0) { rowNumber++; continue; } Iterator<Cell> 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()); } } } |
RestAPIs Controller
Upload Controller
– Implement UploadFileController.java:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | package com.grokonez.excelfile.controller; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.multipart.MultipartFile; import com.grokonez.excelfile.fileservice.FileServices; @Controller public class UploadFileController { @Autowired FileServices fileServices; @GetMapping("/") public String index() { return "multipartfile/uploadform.html"; } @PostMapping("/") public String uploadMultipartFile(@RequestParam("uploadfile") MultipartFile file, Model model) { try { fileServices.store(file); model.addAttribute("message", "File uploaded successfully!"); } catch (Exception e) { model.addAttribute("message", "Fail! -> uploaded filename: " + file.getOriginalFilename()); } return "multipartfile/uploadform.html"; } } |
Download Controller
– Implement DownloadFileController.java file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | package com.grokonez.excelfile.controller; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.core.io.InputStreamResource; import org.springframework.http.HttpHeaders; import org.springframework.http.ResponseEntity; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.GetMapping; import com.grokonez.excelfile.fileservice.FileServices; @Controller public class DownloadFileController { @Autowired FileServices fileServices; /* * Download Files */ @GetMapping("/file") public ResponseEntity<InputStreamResource> downloadFile() { HttpHeaders headers = new HttpHeaders(); headers.add("Content-Disposition", "attachment; filename=customers.xlsx"); return ResponseEntity .ok() .headers(headers) .body(new InputStreamResource(fileServices.loadFile())); } } |
Viewers
– Implement upload/download viewers:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | <!DOCTYPE html> <html lang="en"> <head> <title>Upload Excel File to 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.1.0/css/bootstrap.min.css"> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.0/umd/popper.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.1.0/js/bootstrap.min.js"></script> </head> <body> <div class="container h-100"> <div class="h-100"> <div class="row h-100 justify-content-center align-items-center"> <div class="col-sm-5"> <h3>Upload Excel File to MySQL</h3> <form method="POST" enctype="multipart/form-data" id="fileUploadForm"> <div class="form-group"> <label class="control-label" for="uploadfile">Upload File:</label> <input type="file" class="form-control" id="uploadfile" placeholder="Upload File" name="uploadfile"></input> </div> <button type="submit" class="btn btn-default" id="btnSubmit">Upload</button> </form> <div th:if="${message}"> <span th:text="${message}"/> <a href="/file">Download Excel File from MySQL</a> </div> </div> </div> </div> </div> </body> </html> |
Application Config
– In application.properties, add configuration:
1 2 3 4 | spring.datasource.url=jdbc:mysql://localhost:3306/testdb spring.datasource.username=root spring.datasource.password=12345 spring.jpa.generate-ddl=true |
SourceCode
– Excel File: customers.xlsx
– SourceCode: SpringBootUploadDownloadExcelFile