Excel File - Upload/Download using Apache POI + SpringBoot RestAPIs + Spring JPA + Thymeleaf to MySQL

In the post, Grokonez guides how to Upload/Download Excel Files to MySQL database using SpringBoot + Spring JPA with Apache POI library

Contents

Technologies
Goal
Practice
SourceCode

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:

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-thymeleaf</artifactId>

</dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-web</artifactId>

</dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

</dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-data-jpa</artifactId>

</dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>

Customer Model

– Create Customer.java model:

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:

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:

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:

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:

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:

<!DOCTYPE html>

<head>

<title>Upload Excel File to MySQL</title>

</head>

<body>

<h3>Upload Excel File to MySQL</h3>

<label class="control-label" for="uploadfile">Upload File:</label>

</div>

<button type="submit" class="btn btn-default" id="btnSubmit">Upload</button>

</form>

<a href="/file">Download Excel File from MySQL</a>

</div>

</body>

</html>

Application Config

– In application.properties, add configuration:

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

By grokonez | February 18, 2019.

Excel File – Upload/Download using Apache POI + SpringBoot RestAPIs + Spring JPA + Thymeleaf to MySQL

Technologies

Goal

Practice

Create SpringBoot project

Customer Model

JPA Repository

Excel Utils

RestAPIs Controller

Upload Controller

Download Controller

Viewers

Application Config

SourceCode

Post Tags

Got Something To Say: Cancel reply

Technologies

Goal

Practice

Create SpringBoot project

Customer Model

JPA Repository

Excel Utils

RestAPIs Controller

Upload Controller

Download Controller

Viewers

Application Config

SourceCode

Related Posts

Post Tags

Got Something To Say: Cancel reply