Here I am going to show you how to download excel file from MySQL using Spring framework. Generating excel report from MySQL database has few advantages over other file formats. Most of the people you are working with in your organization are familiar with excel file format, hence it needs a short learning curve. You may also like to read how to download json file in Spring Boot.
The excel file has other benefits as given below:
- It allows you to use templates and formulas to aggregate data.
- It has very easy-to-use charting functionality compared to other software, and several helpful built-in features.
- It is also great because you have the ability to cut and paste into reports. Most people write reports in Word, so it’s easy to cut and paste data or charts from Excel to Word.
Here I will use Apache POI library to write data to excel format but I am not going to write data to any excel file. I am going to apply some basic styles to the excel data, such as, bold fonts to the header columns of the row, border around every row.
Prerequisites
Java at least 8, Maven 3.6.3, Gradle 6.5.1, Spring Boot 2.3.3, MySQL 8.0.17, Apache POI 4.1.2
Project Setup
You can create gradle or maven based project in your favorite IDE or tool. According to your gradle or maven based project you can use one of the following build file.
You need to add additional jaxb runtime API if you are using MySQL 8 and Java higher than version 8.
build.gradle
buildscript {
ext {
springBootVersion = '2.3.3.RELEASE'
}
repositories {
mavenCentral()
}
dependencies {
classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
}
}
plugins {
id 'java-library'
id 'org.springframework.boot' version "${springBootVersion}"
}
sourceCompatibility = 12
targetCompatibility = 12
repositories {
mavenCentral()
}
dependencies {
implementation("org.springframework.boot:spring-boot-starter-web:${springBootVersion}")
implementation("org.springframework.boot:spring-boot-starter-data-jpa:${springBootVersion}")
implementation 'mysql:mysql-connector-java:8.0.21'
implementation 'org.apache.poi:poi-ooxml:4.1.2'
//required for JDK 9 or above
implementation 'javax.xml.bind:jaxb-api:2.4.0-b180830.0359'
}pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.roytuts</groupId>
<artifactId>spring-excel-report-generation</artifactId>
<version>0.0.1-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.3.RELEASE</version>
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<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.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
<!--required only if jdk 9 or higher version is used-->
<dependency>
<groupId>javax.xml.bind</groupId>
<artifactId>jaxb-api</artifactId>
<version>2.4.0-b180830.0359</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>at least 8</source>
<target>at least 8</target>
</configuration>
</plugin>
</plugins>
</build>
</project>MySQL Data
I have some sample data in MySQL table product under database roytuts as shown below.
In MySQL version 8 you do not need to specify the size of the int data type. If you do so then MySQL server will give you warning.
CREATE TABLE `product` (
`id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`price` double COLLATE utf8mb4_unicode_ci NOT NULL,
`sale_price` double COLLATE utf8mb4_unicode_ci NOT NULL,
`sales_count` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL,
`sale_date` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
insert into product(id,name,price,sale_price,sales_count,sale_date) values(1, 'Desktop','30000','35000','55','02-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(2, 'Desktop','30300','37030','43','03-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(3, 'Tablet','39010','48700','145','04-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(4, 'Phone','15000','17505','251','05-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(5, 'Phone','18000','22080','178','05-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(6, 'Tablet','30500','34040','58','05-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(7, 'Adapter','2000','2500','68','06-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(8, 'TV','45871','55894','165','07-04-2018');DataSource Configuration
To establish connection to database and to fetch data or write data from/to database table you need to configure datasource in Spring framework. I am using annotation based or Java based datasource configuration.
First step is to put the database settings into application.properties file under classpath directory src/main/resources.
#datasource
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost/roytuts
spring.datasource.username=root
spring.datasource.password=root
#disable schema generation from Hibernate
spring.jpa.hibernate.ddl-auto=noneThe Java based datasource configuration is given below. The application.properties file is automatically read by Spring Boot framework as it is under the classpath directory with standard naming convention.
Spring framework provides Environment to retrieve your configuration properties easily. The class is annotation with @Configuration to indicate it is used for configuration purpose.
The datasource requires entity manager factory and if you do not create it then Spring Boot will create one for you.
You need to let Spring know which package contains entity classes and which package contains Spring Data JPA repository interfaces. Spring Data JPA Repository is built-in API that provides functions for performing basic CRUD (Create, Read, Update and Delete) operations out of the box.
package com.roytuts.excel.report.generation.config;
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
@Configuration
@EnableJpaRepositories(basePackages = "com.roytuts.excel.report.generation.repository")
public class Config {
@Autowired
private Environment environment;
@Bean
public DataSource dataSource() {
DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName(environment.getRequiredProperty("spring.datasource.driverClassName"));
ds.setUrl(environment.getRequiredProperty("spring.datasource.url"));
ds.setUsername(environment.getRequiredProperty("spring.datasource.username"));
ds.setPassword(environment.getRequiredProperty("spring.datasource.password"));
return ds;
}
@Bean
public EntityManagerFactory entityManagerFactory(DataSource dataSource) {
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
vendorAdapter.setDatabase(Database.MYSQL);
LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
factory.setJpaVendorAdapter(vendorAdapter);
factory.setPackagesToScan("com.roytuts.excel.report.generation.entity");
factory.setDataSource(dataSource);
factory.afterPropertiesSet();
return factory.getObject();
}
}
Entity Class
Your entity class maps the database table columns with your class attributes for the given table name in annotation @Table on the entity class.
The @Column annotation is used to map your Java field or attribute with corresponding table column name. If your java attribute and table column name are same then you don’t need to specify the name using @Column annotation.
To map primary key column you need to put @Id annotation. I am using generation type identity because I have the auto increment primary key for the table.
package com.roytuts.excel.report.generation.entity;
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 = "product")
public class Product {
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
@Column(name = "name")
private String name;
@Column(name = "price")
private Double price;
@Column(name = "sale_price")
private Double salePrice;
@Column(name = "sales_count")
private Integer salesCount;
@Column(name = "sale_date")
private String saleDate;
// getters and setters
}
Repository
Here is the repository that extends JpaRepository interface to get the maximum benefits from Spring framework.
You need to pass entity class type and primary key type when you are extending the JpaRepository. The type is here Product and the primary key type is Integer in the Product entity class.
I have not declared any class in this interface because I don’t need to. I am just going to fetch all records for generating excel file or report for the table data.
package com.roytuts.excel.report.generation.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import com.roytuts.excel.report.generation.entity.Product;
public interface ProductRepository extends JpaRepository<Product, Integer> {
}
REST Controller
I am creating Spring REST controller class wherein I am going to implement the excel file download functionality.
I have auto-wired the repository in this class which is not a good practice but for the sake of simplicity for this example. You can read best practices in Spring application.
I have created an endpoint /report/product/ with GET http request for downloading the excel report from MySQL.
I have fetched all data from MySQL table using repository to write to ByteArrayOutputStream using Apache POI’s WorkBook instance.
I have written header for each column that indicates about the data of the particular column. I have applied border to each row/column. I have also set the fonts bold for header row. I have two different border thickness for header and rest of the rows.
In the response I have set few headers, such as, media type for file, file name and content disposition (as attachment) to forcefully download the file instead of rendering the data onto browser.
package com.roytuts.excel.report.generation.rest.controller;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
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 org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.InputStreamResource;
import org.springframework.core.io.Resource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import com.roytuts.excel.report.generation.entity.Product;
import com.roytuts.excel.report.generation.repository.ProductRepository;
@RestController
public class ProductRestController {
@Autowired
private ProductRepository repository;
@GetMapping("/report/product/")
public ResponseEntity<Resource> generateExcelReport() throws IOException {
List<Product> products = repository.findAll();
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet();
int rowCount = 0;
Row row = sheet.createRow(rowCount++);
Font font = wb.createFont();
font.setBold(true);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setBorderTop(BorderStyle.THICK);
cellStyle.setBorderBottom(BorderStyle.THICK);
cellStyle.setBorderLeft(BorderStyle.THICK);
cellStyle.setBorderRight(BorderStyle.THICK);
cellStyle.setFont(font);
Cell cell = row.createCell(0);
cell.setCellValue("Id");
cell.setCellStyle(cellStyle);
cell = row.createCell(1);
cell.setCellValue("Name");
cell.setCellStyle(cellStyle);
cell = row.createCell(2);
cell.setCellValue("Price");
cell.setCellStyle(cellStyle);
cell = row.createCell(3);
cell.setCellValue("Sale Price");
cell.setCellStyle(cellStyle);
cell = row.createCell(4);
cell.setCellValue("Sales Count");
cell.setCellStyle(cellStyle);
cell = row.createCell(5);
cell.setCellValue("Sale Date");
cell.setCellStyle(cellStyle);
cellStyle = wb.createCellStyle();
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
for (Product product : products) {
row = sheet.createRow(rowCount++);
int columnCount = 0;
cell = row.createCell(columnCount++);
cell.setCellValue(product.getId());
cell.setCellStyle(cellStyle);
cell = row.createCell(columnCount++);
cell.setCellValue(product.getName());
cell.setCellStyle(cellStyle);
cell = row.createCell(columnCount++);
cell.setCellValue(product.getPrice());
cell.setCellStyle(cellStyle);
cell = row.createCell(columnCount++);
cell.setCellValue(product.getSalePrice());
cell.setCellStyle(cellStyle);
cell = row.createCell(columnCount++);
cell.setCellValue(product.getSalesCount());
cell.setCellStyle(cellStyle);
cell = row.createCell(columnCount++);
cell.setCellValue(product.getSaleDate());
cell.setCellStyle(cellStyle);
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
wb.close();
ByteArrayInputStream is = new ByteArrayInputStream(os.toByteArray());
HttpHeaders headers = new HttpHeaders();
headers.setContentType(
MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
headers.setCacheControl("must-revalidate, post-check=0, pre-check=0");
headers.set(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=ProductExcelReport.xlsx");
ResponseEntity<Resource> response = new ResponseEntity<Resource>(new InputStreamResource(is), headers,
HttpStatus.OK);
return response;
}
}
Main Class
The purpose of having a class with main method and @SpringBootAppliation is to deploy the app into embedded Tomcat server which is by default shipped with Spring Boot framework.
package com.roytuts.excel.report.generation;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class SpringExcelReportApp {
public static void main(String[] args) {
SpringApplication.run(SpringExcelReportApp.class, args);
}
}
Testing the Application
Once you run the above main class your application will start up on default port 8080. If you cannot use default port for some reasons then you can specify the one you want in application.properties file using the key/value pair server.port=<port number>.
As it is GET request so you would be able to call the REST API endpoint (http://localhost:8080/report/product/) directly from the browser.
Once you call the URL in the browser you will see an excel file named ProductExcelReport.xlsx is ready for the save. You can save it anywhere on your system and open it to see the output as shown in the following image:
Hope you got an idea how to generate excel report from MySQL using Spring framework. You can find the above output excel file in the source code.
Source Code
Thanks for reading.