In the tutorial, we show you how to create a SpringBoot RestAPIs application that uses Spring JPA to get data from MySQL records and uses Apache POI library to write data to a Excel file.
Related posts:
– Java – How to read/write Excel file with Apache POI
– How to use Spring JPA MySQL | Spring Boot
– SpringBoot – Upload/Download MultipartFile to FileSystem – Bootstrap 4 + JQuery Ajax
Contents
Technologies
– Spring Boot – 2.0.6.RELEASE
– Spring JPA
– MySQL
– Apache POI
Practice
Project Structure ->
Create SpringBoot project
Use SpringToolSuite to create a SpringBoot project with below dependencies:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> |
Customer Model
– Customer.java ->
package com.grokonez.exceldownload.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 = "customers") 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 Customer Repository
– CustomerRepository.java ->
package com.grokonez.exceldownload.repository; import org.springframework.data.repository.CrudRepository; import com.grokonez.exceldownload.model.Customer; public interface CustomerRepository extends CrudRepository<Customer, Long>{ } |
Implement Excel Generator
– ExcelGenerator.java ->
package com.grokonez.exceldownload.util; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.IOException; 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.exceldownload.model.Customer; public class ExcelGenerator { 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()); } } } |
Implement RestAPI Controller
– CustomerExcelDownloadRestAPI.java ->
package com.grokonez.exceldownload.controller; import java.io.ByteArrayInputStream; import java.io.IOException; import java.util.List; 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.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import com.grokonez.exceldownload.model.Customer; import com.grokonez.exceldownload.repository.CustomerRepository; import com.grokonez.exceldownload.util.ExcelGenerator; @RestController @RequestMapping("/api/customers") public class CustomerExcelDownloadRestAPI { @Autowired CustomerRepository customerRepository; @GetMapping(value = "/download/customers.xlsx") public ResponseEntity<InputStreamResource> excelCustomersReport() throws IOException { List<Customer> customers = (List<Customer>) customerRepository.findAll(); ByteArrayInputStream in = ExcelGenerator.customersToExcel(customers); // return IOUtils.toByteArray(in); HttpHeaders headers = new HttpHeaders(); headers.add("Content-Disposition", "attachment; filename=customers.xlsx"); return ResponseEntity .ok() .headers(headers) .body(new InputStreamResource(in)); } } |
Implement Download View
– Create ViewController.java ->
package com.grokonez.exceldownload.controller; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; @Controller public class ViewController { @RequestMapping("/") public String home() { return "home"; } } |
– Create .html view home.html ->
<html> <head> <title>SpringBoot Excel</title> </head> <body> <h3>Download Excel Customers File</h3> <div> <a href="http://localhost:8080/api/customers/download/customers.xlsx">Customers .XLSX</a> </div> </body> </html> |
Initial Customers
– In main class, we use CommandLineRunner to init Customer’s records:
package com.grokonez.exceldownload; import java.util.Arrays; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.CommandLineRunner; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import com.grokonez.exceldownload.model.Customer; import com.grokonez.exceldownload.repository.CustomerRepository; @SpringBootApplication public class SpringJpaExcelDownloaderApplication implements CommandLineRunner { @Autowired CustomerRepository repository; public static void main(String[] args) { SpringApplication.run(SpringJpaExcelDownloaderApplication.class, args); } @Override public void run(String... args) throws Exception { List<Customer> customers = Arrays.asList( new Customer(Long.valueOf(1), "Jack Smith", "Massachusetts", 23), new Customer(Long.valueOf(2), "Adam Johnson", "New York", 27), new Customer(Long.valueOf(3), "Katherin Carter", "Washington DC", 26), new Customer(Long.valueOf(4), "Jack London", "Nevada", 33), new Customer(Long.valueOf(5), "Jason Bourne", "California", 36)); // save a list of Customers repository.saveAll(customers); } } |
Database Configuration
– application.properties ->
spring.datasource.url=jdbc:mysql://localhost:3306/testdb spring.datasource.username=root spring.datasource.password=12345 spring.jpa.generate-ddl=true spring.jpa.hibernate.ddl-auto=create-drop |
Run & Check Results
Run the SpringBoot project,
-> MySQL’s records:
-> Excel Downloaded File:
If we put Workbook inside try block parentheses it is showing error….
You should set Content-Type in order to have a valid mime Type.
For example
headers.add(“Content-Type”, “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”);
Done… Thanks a Lot………….!!!!!!!!!!
Thanks a lot for the tutorial. It has been very helpful
This work for me, thanks!
I tried this, but getting error. Actually I have configured plsql db in application.properties seems profile is not activated.
I want to pull the record from plsql and download as excel. Please help me.
INFO: No Active profile set, falling back to default profiles: default
INFO: Refreshing org.springframework.boot.context.embedded.annotationConfigEmbeddedWebapplicationcontect@66fd8833:startupdate ; root of context hierarchy
WARN: Exception encountered during context initialization – cancelling refresh attempt: org.springframework.beans.factory.BeanDefinitionStoreException:Failed to parse configuration class [com.dataextract.SpringJpaExcelDownloaderApplication]; nested exception is java.io.FileNotFoundException: Class path resource[application.properties] cannot be opened because it does not exist.
ERROR: Destroy method on bean withname ‘org.springframework.boot.autoconfigure.internalCachingMetadataReaderFactory’ threw an exception.
I am new to spring boot. i want to finish this for my organization. Please help me.
Thank you for the support.