Read and Write to Excel With Java Spring Boot

Details

Written by

Last Updated on 04 September 2020   | Print Email

Exporting data to Excel documents is a common feature of nigh whatever applications. Through this article, I'thou very glad to share with y'all guys my experience in implementing Excel export function in a Spring Boot application with the help of Apache POI Excel library.

Suppose that we have an existing Spring Kicking projection using Spring Information JPA and Hide to access data, Thymeleaf to render the view and MySQL as the database.

The code examples below demonstrate how to retrieve data near users from the database, and generate an Excel file which the users can download onto their computers.

1. Code of Entity Classes and Repositories Interfaces

We take the Userentity class that maps to the users table in the database, as shown below:

package net.codejava;  import java.util.*;  import javax.persistence.*;  @Entity @Table(proper name = "users") public grade User { 	@Id 	@GeneratedValue(strategy = GenerationType.IDENTITY) 	individual Integer id; 	 	private String e-mail; 	 	private String password; 	 	@Column(proper noun = "full_name") 	private String fullName; 		 	individual boolean enabled; 	 	@ManyToMany(cascade = CascadeType.PERSIST, fetch = FetchType.EAGER) 	@JoinTable( 			name = "users_roles", 			joinColumns = @JoinColumn(name = "user_id"), 			inverseJoinColumns = @JoinColumn(name = "role_id") 			) 	private Set<Role> roles = new HashSet<>();  	// constructors, getter and setters are not shown for brevity }

And the Officeentity form that maps to the roles table in the database:

package net.codejava;  import javax.persistence.*;  @Entity @Table(name = "roles") public course Office { 	@Id 	@GeneratedValue(strategy = GenerationType.IDENTITY) 	individual Integer id; 	 	private String name; 	 	private Cord description;  	// constructors, getter and setters are not shown for brevity	 }

The fields will be included in the generated Excel document are: User ID, Electronic mail, Full Name, Roles and Enabled.

And code of the corresponding repository interfaces looks like this:

package cyberspace.codejava;  import org.springframework.information.jpa.repository.JpaRepository;  public interface UserRepository extends JpaRepository<User, Integer> { 	 }   public interface RoleRepository extends CrudRepository<Role, Integer> { 	 }

These are simple, typical repositories as required by Bound Data JPA.

2. Declare Dependency for Excel Library

To generate Excel file, nosotros need to employ an external library and Apache POI is 1 of the most pop ones. So we need to declare the following dependency to use Apache POI for Excel in the Maven's project file:

<dependency> 	<groupId>org.apache.poi</groupId> 	<artifactId>poi-ooxml</artifactId> 	<version>four.1.0</version> </dependency>

3. Lawmaking for the Service Class

In the service layer, we may take the UserServicescourse every bit follows:

package net.codejava;  import java.util.Listing;  import javax.transaction.Transactional;  import org.springframework.beans.factory.notation.Autowired; import org.springframework.information.domain.Sort; import org.springframework.stereotype.Service;  @Service @Transactional public class UserServices { 	 	@Autowired 	private UserRepository repo; 	 	public List<User> listAll() { 		return repo.findAll(Sort.by("e-mail").ascending()); 	} 	 }

As you can come across, the listAll() method delegates the call to the findAll() method of the UserRepository interface, which is implemented by Spring Data JPA (extended from JpaRepository). The listAll() method volition be invoked to go data almost users from the database.

four. Code Excel Exporter Grade

Next, code a split up class that is responsible to generate an Excel certificate based on the input is aList collection ofUser objects, as shown below:

package net.codejava;  import java.io.IOException; import java.util.Listing;  import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse;  import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook;  public class UserExcelExporter { 	private XSSFWorkbook workbook; 	private XSSFSheet sheet; 	private Listing<User> listUsers; 	 	public UserExcelExporter(List<User> listUsers) { 		this.listUsers = listUsers; 		workbook = new XSSFWorkbook(); 	}   	private void writeHeaderLine() { 		sheet = workbook.createSheet("Users"); 		 		Row row = sail.createRow(0); 		 		CellStyle manner = workbook.createCellStyle(); 		XSSFFont font = workbook.createFont(); 		font.setBold(true); 		font.setFontHeight(xvi); 		style.setFont(font); 		 		createCell(row, 0, "User ID", style);		 		createCell(row, 1, "Eastward-postal service", fashion);		 		createCell(row, 2, "Full Name", style);		 		createCell(row, 3, "Roles", mode); 		createCell(row, 4, "Enabled", style); 		 	} 	 	private void createCell(Row row, int columnCount, Object value, CellStyle style) { 		sail.autoSizeColumn(columnCount); 		Prison cell cell = row.createCell(columnCount); 		if (value instanceof Integer) { 			cell.setCellValue((Integer) value); 		} else if (value instanceof Boolean) { 			prison cell.setCellValue((Boolean) value);  		}else { 			cell.setCellValue((String) value); 		} 		cell.setCellStyle(style); 	} 	 	private void writeDataLines() { 		int rowCount = 1;  		CellStyle style = workbook.createCellStyle(); 		XSSFFont font = workbook.createFont(); 		font.setFontHeight(xiv); 		way.setFont(font); 				 		for (User user : listUsers) { 			Row row = sheet.createRow(rowCount++); 			int columnCount = 0; 			 			createCell(row, columnCount++, user.getId(), style); 			createCell(row, columnCount++, user.getEmail(), style); 			createCell(row, columnCount++, user.getFullName(), way); 			createCell(row, columnCount++, user.getRoles().toString(), style); 			createCell(row, columnCount++, user.isEnabled(), style); 			 		} 	} 	 	public void consign(HttpServletResponse response) throws IOException { 		writeHeaderLine(); 		writeDataLines(); 		 		ServletOutputStream outputStream = response.getOutputStream(); 		workbook.write(outputStream); 		workbook.close(); 		 		outputStream.shut(); 		 	} }

This form volition create an Excel document with ane sheet containing a header row and rows for the data. The header row consists of these columns: User ID, E-mail, Total Proper noun, Roles and Enabled.

Pay attention to theexport() method that takes anHttpServletRespone as the argument, because information technology will write the content of the Excel file into the output stream of the response, and so the clients (web browsers) will exist able to download the exported Excel file.

5. Code Handler method in the Controller Class

Next, implement a handler method in a Spring MVC controller course – UserController – as follows:

packet net.codejava;  import coffee.io.IOException; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List;  import javax.servlet.http.HttpServletResponse;  import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.GetMapping;  @Controller public grade UserController {  	@Autowired 	private UserServices service; 	 	 	@GetMapping("/users/export/excel") 	public void exportToExcel(HttpServletResponse response) throws IOException { 		response.setContentType("awarding/octet-stream"); 		DateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd_HH:mm:ss"); 		String currentDateTime = dateFormatter.format(new Engagement()); 		 		String headerKey = "Content-Disposition"; 		String headerValue = "attachment; filename=users_" + currentDateTime + ".xlsx"; 		response.setHeader(headerKey, headerValue); 		 		List<User> listUsers = service.listAll(); 		 		UserExcelExporter excelExporter = new UserExcelExporter(listUsers); 		 		excelExporter.export(response);		 	}	  }

As you can see the exportToExcel() method will serve HTTP Get request with the URI /users/export/excel. It will utilize the UserServices class to get data of users from the database, and apply the UserExcelExporter class to write an Excel certificate to the response.

Likewise notice proper noun of the generated Excel file is appended with the current date time, making information technology's easier for the users to track multiple versions of files downloaded.

6. Add together Export Excel Link in the View Page

We use HTML and Thymeleaf to create a hyperlink that allows the user to click to consign data to Excel as follows:

<a thursday:href="/@{/users/consign/excel}">Export to Excel</a>

7. Test Export and Download Excel file

Click the hyperlink Export to Excel, the Spring Boot awarding will generate an Excel file and the browser volition automatically download that file. The file name is something similar this: users_2020-09-02_11-30-06.xlsx. Open this file using Microsoft Excel application, you would see the following screen:

Decision

So far you lot have learned how to lawmaking Excel export office for a Spring Kicking web application. You lot see, Spring Data JPA makes it piece of cake to get data from the database, and Apache POI makes it piece of cake to generate documents compatible with Microsoft Excel format.

For video version of this tutorial, watch the video beneath:

Related Tutorials:

• How to Write Excel Files in Java using Apache POI
• Coffee code case to export from database to Excel file
• How to Read Excel Files in Java using Apache POI
• Java lawmaking example to import data from Excel to database

Other Spring Boot Tutorials:

• Spring Boot Export Data to CSV Example
• Spring Kick Export Data to PDF Case
• Spring Boot Hi World Example
• Spring Kicking automatic restart using Jump Boot DevTools
• Spring Boot Form Handling Tutorial with Jump Grade Tags and JSP
• How to create a Bound Boot Spider web Application (Spring MVC with JSP/ThymeLeaf)
• Spring Kicking - Spring Data JPA - MySQL Example
• Leap Kicking Hello World RESTful Spider web Services Tutorial
• How to use JDBC with Spring Boot
• Spring Boot CRUD Web Application with JDBC - Thymeleaf - Oracle
• Spring Boot RESTful CRUD API Examples with MySQL database
• How to package Leap Kicking awarding to JAR and WAR
• Spring Boot Security Authentication with JPA, Hibernate and MySQL
• Spring Data JPA Paging and Sorting Examples
• Spring Kick Error Handling Guide

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Coffee 1.iv and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you lot YouTube.

Add annotate

osteenfortive.blogspot.com

Source: https://www.codejava.net/frameworks/spring-boot/export-data-to-excel-example

Share this post