Shulou(Shulou.com)06/03 Report--

This article mainly introduces SpringBoot+EasyPoi how to achieve excel export function, the article introduces in great detail, has a certain reference value, interested friends must read it!

In the actual project development, the import and export of Excel is still very common, such as importing the data into the database in batches according to the template and exporting the data in the database to the form of old Excel.

Existing requirements: query the list according to the retrieval criteria and export the results to excel

Easypoi documents: https://easypoi.mydoc.io/#text_186900

The main features of EasyPoi

1. Exquisite design and easy to use

two。 Rich interfaces and simple expansion

3. Many default values, write less do more

4.spring mvc support, web export can be simple and straightforward

Implementation process 1. Create a Spring Boot project

Quickly generate links: start.spring.io

two。 Pom that introduces EasyPoi depends on cn.afterturn easypoi-base 4.3.0 cn.afterturn easypoi-web 4.3.0 cn.afterturn easypoi-annotation 4.3.0

Easypoi-base import and export toolkit, you can complete Excel export, import, Word export, Excel export function

Easypoi-web is coupled with spring-mvc based on AbstractView, greatly simplifying the export function under spring-mvc

Easypoi-annotation basic annotation package, which works on entity objects, and facilitates the dependency management of maven multi-projects after splitting.

Sax imports using the xercesImpl package (this package may cause strange problems), word exports use poi-scratchpad, all as optional packages

All dependencies in pom.xml:

Org.springframework.boot spring-boot-starter-web org.springframework.boot spring-boot-devtools runtime true org.projectlombok lombok true org.springframework.boot spring-boot-starter-test test com.baomidou Mybatis-plus-boot-starter 3.4.3 mysql mysql-connector-java runtime com.alibaba druid 1.1.20 com.alibaba druid-spring-boot-starter 1.1.20 io .springfox springfox-swagger2 2.9.2 io.swagger swagger-models io.springfox springfox-swagger-ui 2.9.2 io.swagger swagger-models 1.5.21 cn.afterturn easypoi-base 4.3.0 cn.afterturn easypoi-web 4.3.0 cn.afterturn easypoi-annotation 4.3.0 com.alibaba fastjson 1.2.71 3. Write the excel utility class package com.example.easypoiexceldemo.utils;import cn.afterturn.easypoi.excel.ExcelExportUtil;import cn.afterturn.easypoi.excel.entity.ExportParams;import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;import org.apache.commons.lang3.StringUtils;import org.apache.poi.ss.usermodel.Workbook;import org.springframework.beans.BeanUtils;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.net.URLEncoder;import java.text.SimpleDateFormat;import java.util.ArrayList Import java.util.Collection;import java.util.Date;import java.util.List / * excel utility class * @ author qzz * / public class ExcelUtils {/ * Excel export * * @ param response response * @ param fileName filename * @ param list data List * @ param pojoClass object Class * / public static void exportExcel (HttpServletResponse response, String fileName, Collection list Class pojoClass) throws IOException {if (StringUtils.isBlank (fileName)) {/ / current date SimpleDateFormat df = new SimpleDateFormat ("yyyy-MM-dd") FileName = df.format (new Date ());} Workbook workbook = ExcelExportUtil.exportExcel (new ExportParams (fileName, fileName, ExcelType.HSSF), pojoClass, list); response.setCharacterEncoding ("UTF-8"); response.setHeader ("content-Type", "application/vnd.ms-excel"); response.setHeader ("Content-Disposition", "attachment") Filename= "+ URLEncoder.encode (fileName," UTF-8 ") +" .xls "); ServletOutputStream out = response.getOutputStream (); workbook.write (out); out.flush () } / * Excel export, first sourceList to List, and then export * * @ param response response * @ param fileName file name * @ param sourceList raw data List * @ param targetClass target object Class * / public static void exportExcelToTarget (HttpServletResponse response, String fileName, Collection sourceList, Class targetClass) throws Exception {List targetList = new ArrayList (sourceList.size ()) For (Object source: sourceList) {Object target = targetClass.newInstance (); BeanUtils.copyProperties (source, target); targetList.add (target);} exportExcel (response, fileName, targetList, targetClass) } / * Excel export-set title---sheetName--- to require Collection list to be a file name of type Class pojoClass * @ param response response * @ param fileName * @ param list data List * @ param pojoClass object Class * / public static void exportExcel (HttpServletResponse response, String title, String sheetName, String fileName, Collection list Class pojoClass) throws IOException {if (StringUtils.isBlank (fileName)) {/ / current date SimpleDateFormat df = new SimpleDateFormat ("yyyy-MM-dd") FileName = df.format (new Date ());} Workbook workbook = ExcelExportUtil.exportExcel (new ExportParams (title, sheetName, ExcelType.HSSF), pojoClass, list); response.setCharacterEncoding ("UTF-8"); response.setHeader ("content-Type", "application/vnd.ms-excel"); response.setHeader ("Content-Disposition", "attachment") Filename= "+ URLEncoder.encode (fileName," UTF-8 ") +" .xls "); ServletOutputStream out = response.getOutputStream (); workbook.write (out); out.flush ();}} 4. Annotate entity classes with @ Excel

I use lombok here. Getter setter and constructors are added by annotating @ Data @ AllArgsConstructor, or manually without using lombok.

The data to be exported can be annotated * * @ Excel () above the corresponding attribute of the entity class. You can define the name and width of the exported column, as well as gender differentiation (gender 1 and 2 stored in the general database), date formatting, and so on.

Package com.example.easypoiexceldemo.excel;import cn.afterturn.easypoi.excel.annotation.Excel;import lombok.Data;import java.util.Date;/** * Commodities * @ author qzz * / @ Datapublic class ProductExcel {/ * Commodity id * / @ Excel (name= "Commodity id") private Integer product_id; / * Commodity title * / @ Excel (name= "Commodity title") private String title / * Commodity subtitle * / @ Excel (name= "Commodity subtitle") private String sub_title; / * Commodity Price * / @ Excel (name= "Commodity Price") private Double sale_price; / * founder * / @ Excel (name= "founder") private Integer create_by / * creation time * / @ Excel (name= "creation time", format = "yyyy-MM-dd") private Date create_time; / * modification time * / @ Excel (name= "modification time", format = "yyyy-MM-dd") private Date update_time / * modifier id * / @ Excel (name= "modifier id") private Integer update_by;}

@ Excel acts on filed, which is a description of the Excel column

@ Excel attribute description:

5. Controllers excel * * excel export * @ param response * / @ GetMapping ("/ excel") @ ApiOperation ("query the list according to search conditions, export excel") public void export (HttpServletResponse response) throws IOException {/ / retrieve the list according to conditions QueryWrapper queryWrapper = new QueryWrapper (); / / retrieve the list of items List list = productService.selectList (queryWrapper) / / convert the List result set to List List productList = MapToEntity.setList (list,ProductExcel.class); / / Export excel ExcelUtils.exportExcel (response,null,productList, ProductExcel.class);}

The setList method is a utility class that converts List result sets to List

MapToEntity utility class:

Package com.example.easypoiexceldemo.utils;import org.apache.commons.lang3.StringUtils;import java.lang.reflect.Field;import java.math.BigDecimal;import java.text.ParseException;import java.text.ParsePosition;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import java.util.Map / * List to List data conversion * @ author qzz * / public class MapToEntity {/ * List to List data conversion * / public static List setList (final List srcList, Class clazz) {List list = new ArrayList (); for (int iDeposit I

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.