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.

2 thoughts on “Excel File – Upload/Download using Apache POI + SpringBoot RestAPIs + Spring JPA + Thymeleaf to MySQL”

sarath says:
October 14, 2019 at 6:28 pm
When a file upload error accuried
sarath says:
October 14, 2019 at 6:28 pm
When a file upload error accuried java.lang.nullpoiter Exception.

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

2 thoughts on “Excel File – Upload/Download using Apache POI + SpringBoot RestAPIs + Spring JPA + Thymeleaf to MySQL”

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

2 thoughts on “Excel File – Upload/Download using Apache POI + SpringBoot RestAPIs + Spring JPA + Thymeleaf to MySQL”

Got Something To Say: Cancel reply