In the tutorial, we show you how to create a SpringBoot RestAPIs application that uses Spring Data MongoDB to get data from MongoDB collection 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 SpringData MongoRepository to interact with MongoDB
– SpringBoot – Upload/Download MultipartFile to FileSystem – Bootstrap 4 + JQuery Ajax
Technologies
– Spring Boot – 2.0.6.RELEASE
– Spring JPA
– MongoDB
– Apache POI
Practice
Project Structure ->
Create SpringBoot project
Use SpringToolSuite to create a SpringBoot project with below dependencies:
org.springframework.boot spring-boot-starter-web org.springframework.boot spring-boot-starter-data-mongodb org.springframework.boot spring-boot-starter-thymeleaf org.apache.poi poi-ooxml 3.17
Customer Model
– Customer.java
->
package com.ozenero.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.ozenero.exceldownload.repository; import org.springframework.data.repository.CrudRepository; import com.ozenero.exceldownload.model.Customer; public interface CustomerRepository extends CrudRepository{ }
Implement Excel Generator
– ExcelGenerator.java
->
package com.ozenero.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.ozenero.exceldownload.model.Customer; public class ExcelGenerator { public static ByteArrayInputStream customersToExcel(Listcustomers) 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.ozenero.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.ozenero.exceldownload.model.Customer; import com.ozenero.exceldownload.repository.CustomerRepository; import com.ozenero.exceldownload.util.ExcelGenerator; @RestController @RequestMapping("/api/customers") public class CustomerExcelDownloadRestAPI { @Autowired CustomerRepository customerRepository; @GetMapping(value = "/download/customers.xlsx") public ResponseEntityexcelCustomersReport() throws IOException { List customers = (List ) 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.ozenero.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
->
SpringBoot Excel Download Excel Customers File
Initial Customers
– In main class, we use CommandLineRunner
to init Customer’s records:
package com.ozenero.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.ozenero.exceldownload.model.Customer; import com.ozenero.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 { Listcustomers = 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.data.mongodb.database=gkzdb spring.data.mongodb.port=27017
Run & Check Results
Run the SpringBoot project,
-> MongoDB’s documents:
-> Excel Downloaded File:
Network Logs:
-> File Content: