SpringBoot系列一：整合EasyExcel实现文件上传下载

一、下载导入模板

1.1、下载简单模板

1.1.1、EmployeeController

 1 package com.moon.controller;
 2 
 3 import com.moon.service.UserService;
 4 import org.springframework.web.bind.annotation.GetMapping;
 5 import org.springframework.web.bind.annotation.RestController;
 6 
 7 import javax.annotation.Resource;
 8 import javax.servlet.http.HttpServletResponse;
 9 
10 @RestController
11 public class EmployeeController {
12 
13     @Resource
14     private UserService userService;
15 
16     /**
17      * 下载简单模板
18      * @param response
19      * @return
20      */
21     @GetMapping("/simpleTemplateDownload")
22     public void simpleTemplateDownload(HttpServletResponse response) {
23         userService.simpleTemplateDownload(response);
24     }
25 
26 
27 }

1.1.2、EmployeeService和EmployeeServiceImpl

1 package com.moon.service;
2 
3 import com.moon.result.Result;
4 
5 import javax.servlet.http.HttpServletResponse;
6 
7 public interface EmployeeService {
8     Result simpleTemplateDownload(HttpServletResponse response);
9 }

 1 package com.moon.service;
 2 
 3 import com.alibaba.excel.EasyExcel;
 4 import com.alibaba.excel.write.builder.ExcelWriterBuilder;
 5 import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
 6 import com.moon.result.Result;
 7 import com.moon.result.Results;
 8 import com.moon.template.EmployeeSimpleImportTemplate;
 9 import org.springframework.stereotype.Service;
10 
11 import javax.servlet.ServletOutputStream;
12 import javax.servlet.http.HttpServletResponse;
13 import java.net.URLEncoder;
14 import java.text.SimpleDateFormat;
15 import java.util.ArrayList;
16 import java.util.Date;
17 import java.util.List;
18 
19 @Service
20 public class EmployeeServiceImpl implements EmployeeService {
21     @Override
22     public Result simpleTemplateDownload(HttpServletResponse response) {
23         try {
24             response.setContentType("application/vnd.ms-excel");
25             response.setCharacterEncoding("UTF-8");
26             SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
27             String fileName = URLEncoder.encode(sdf.format(new Date()) + "人员信息", "UTF-8");
28             response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
29 
30             ServletOutputStream outputStream = response.getOutputStream();
31             ExcelWriterBuilder workBook = EasyExcel.write(outputStream, EmployeeSimpleImportTemplate.class);
32 
33             ExcelWriterSheetBuilder sheet = workBook.sheet("人员信息");
34 
35             List<EmployeeSimpleImportTemplate> employees = initData();
36 
37             sheet.doWrite(employees);
38 
39             return Results.success();
40         } catch (Exception e) {
41             e.printStackTrace();
42             return Results.error("1", "失败！", null);
43         }
44     }
45 
46     private List<EmployeeSimpleImportTemplate> initData() {
47         List<EmployeeSimpleImportTemplate> employees = new ArrayList<>();
48 
49         EmployeeSimpleImportTemplate employee = new EmployeeSimpleImportTemplate("zhangsan","zhangsan",18,"男","liubei@qq.com","18812349876","1998-08-08","2021-03-02","美团","北京","世界那么大,我想去看看","C开发工程师","钓鱼,爬山");
50         employees.add(employee);
51 
52         return employees;
53     }
54 }

1.1.3、EmployeeSimpleImportTemplate

 1 package com.moon.template;
 2 
 3 import cn.afterturn.easypoi.excel.annotation.Excel;
 4 import com.alibaba.excel.annotation.ExcelProperty;
 5 import com.alibaba.excel.annotation.format.DateTimeFormat;
 6 import com.alibaba.excel.annotation.write.style.ColumnWidth;
 7 import com.alibaba.excel.annotation.write.style.ContentStyle;
 8 import com.alibaba.excel.annotation.write.style.HeadStyle;
 9 import lombok.AllArgsConstructor;
10 import lombok.Data;
11 import lombok.NoArgsConstructor;
12 import org.apache.poi.ss.usermodel.BorderStyle;
13 import org.apache.poi.ss.usermodel.HorizontalAlignment;
14 
15 import java.io.Serializable;
16 
17 /**
18  * 下载简单模板
19  */
20 @Data
21 @AllArgsConstructor
22 @NoArgsConstructor
23 @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,
24         borderLeft = BorderStyle.THIN,
25         borderTop = BorderStyle.THIN,
26         borderRight = BorderStyle.THIN,
27         borderBottom = BorderStyle.THIN)
28 @HeadStyle
29 public class EmployeeSimpleImportTemplate implements Serializable {
30     @ExcelProperty(value = "OA账号",index = 0)
31     @Excel(name = "OA账号",fixedIndex = 0)
32     @ColumnWidth(20)
33     private String oaAccountNo;
34 
35     @ExcelProperty(value = "姓名",index = 1)
36     @Excel(name = "姓名",fixedIndex = 1)
37     @ColumnWidth(20)
38     private String username;
39 
40     @ExcelProperty(value = "年龄",index = 2)
41     @Excel(name = "年龄",fixedIndex = 2)
42     @ColumnWidth(20)
43     private Integer age;
44 
45     @ExcelProperty(value = "性别",index = 3)
46     @Excel(name = "性别",fixedIndex = 3)
47     @ColumnWidth(20)
48     private String sex;
49 
50     @ExcelProperty(value = "邮箱",index = 4)
51     @Excel(name = "邮箱",fixedIndex = 4)
52     @ColumnWidth(20)
53     private String email;
54 
55     @ExcelProperty(value = "手机号码",index = 5)
56     @Excel(name = "手机号码",fixedIndex = 5)
57     @ColumnWidth(20)
58     private String mobile;
59 
60     @ExcelProperty(value = "出生日期",index = 6)
61     @Excel(name = "出生日期",fixedIndex = 6,importFormat = "yyyy-MM-dd")
62     @ColumnWidth(20)
63     @DateTimeFormat(value = "yyyy-MM-dd")
64     private String birthday;
65 
66     @ExcelProperty(value = "入职日期",index = 7)
67     @Excel(name = "入职日期",fixedIndex = 7,importFormat = "yyyy-MM-dd")
68     @ColumnWidth(20)
69     @DateTimeFormat(value = "yyyy-MM-dd")
70     private String entryDate;
71 
72     @ExcelProperty(value = "公司名称",index = 8)
73     @Excel(name = "公司名称",fixedIndex = 8)
74     @ColumnWidth(20)
75     private String companyName;
76 
77     @ExcelProperty(value = "公司所在城市",index = 9)
78     @Excel(name = "公司所在城市",fixedIndex = 9)
79     @ColumnWidth(20)
80     private String companyLocation;
81 
82     @ExcelProperty(value = "离职原因",index = 10)
83     @Excel(name = "离职原因",fixedIndex = 10)
84     @ColumnWidth(20)
85     private String reasonForLeaving;
86 
87     @ExcelProperty(value = "岗位名称",index = 11)
88     @Excel(name = "岗位名称",fixedIndex = 11)
89     @ColumnWidth(20)
90     private String positionName;
91 
92     @ExcelProperty(value = "爱好",index = 12)
93     @Excel(name = "爱好",fixedIndex = 12)
94     @ColumnWidth(20)
95     private String hobby;
96 
97 }

1.1.4、pom.xml

  1 <?xml version="1.0" encoding="UTF-8"?>
  2 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  3          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  4     <modelVersion>4.0.0</modelVersion>
  5     <parent>
  6         <groupId>org.springframework.boot</groupId>
  7         <artifactId>spring-boot-starter-parent</artifactId>
  8         <version>2.6.2</version>
  9         <relativePath/> <!-- lookup parent from repository -->
 10     </parent>
 11     <groupId>com.moon</groupId>
 12     <artifactId>springboot-easyexcel</artifactId>
 13     <version>0.0.1-SNAPSHOT</version>
 14     <name>springboot-easyexcel</name>
 15     <description>springboot-easyexcel实现文件上传下载</description>
 16     <properties>
 17         <java.version>1.8</java.version>
 18     </properties>
 19     <dependencies>
 20         <!--EasyExcel begin-->
 21         <dependency>
 22             <groupId>cn.afterturn</groupId>
 23             <artifactId>easypoi-base</artifactId>
 24             <version>4.1.0</version>
 25         </dependency>
 26         <dependency>
 27             <groupId>cn.afterturn</groupId>
 28             <artifactId>easypoi-web</artifactId>
 29             <version>4.1.0</version>
 30         </dependency>
 31         <dependency>
 32             <groupId>cn.afterturn</groupId>
 33             <artifactId>easypoi-annotation</artifactId>
 34             <version>4.1.0</version>
 35         </dependency>
 36         <dependency>
 37             <groupId>com.alibaba</groupId>
 38             <artifactId>easyexcel</artifactId>
 39             <version>2.2.10</version>
 40         </dependency>
 41         <!--EasyExcel end-->
 42 
 43         <dependency>
 44             <groupId>org.mybatis.spring.boot</groupId>
 45             <artifactId>mybatis-spring-boot-starter</artifactId>
 46             <version>2.2.0</version>
 47         </dependency>
 48         <dependency>
 49             <groupId>com.alibaba</groupId>
 50             <artifactId>druid-spring-boot-starter</artifactId>
 51             <version>1.1.10</version>
 52         </dependency>
 53         <dependency>
 54             <groupId>mysql</groupId>
 55             <artifactId>mysql-connector-java</artifactId>
 56             <version>8.0.20</version>
 57         </dependency>
 58 
 59         <dependency>
 60             <groupId>org.springframework.boot</groupId>
 61             <artifactId>spring-boot-starter-web</artifactId>
 62         </dependency>
 63 
 64         <dependency>
 65             <groupId>org.projectlombok</groupId>
 66             <artifactId>lombok</artifactId>
 67             <optional>true</optional>
 68         </dependency>
 69         <dependency>
 70             <groupId>org.springframework.boot</groupId>
 71             <artifactId>spring-boot-starter-test</artifactId>
 72             <scope>test</scope>
 73         </dependency>
 74     </dependencies>
 75 
 76     <build>
 77         <resources>
 78             <resource>
 79                 <directory>src/main/java</directory>
 80                 <includes>
 81                     <include>**/*.xml</include>
 82                 </includes>
 83             </resource>
 84             <resource>
 85                 <directory>src/main/resources</directory>
 86             </resource>
 87         </resources>
 88         <plugins>
 89             <plugin>
 90                 <groupId>org.springframework.boot</groupId>
 91                 <artifactId>spring-boot-maven-plugin</artifactId>
 92                 <configuration>
 93                     <excludes>
 94                         <exclude>
 95                             <groupId>org.projectlombok</groupId>
 96                             <artifactId>lombok</artifactId>
 97                         </exclude>
 98                     </excludes>
 99                 </configuration>
100             </plugin>
101 
102             <plugin>
103                 <groupId>org.mybatis.generator</groupId>
104                 <artifactId>mybatis-generator-maven-plugin</artifactId>
105                 <version>1.3.2</version>
106                 <dependencies>
107                     <dependency>
108                         <groupId>mysql</groupId>
109                         <artifactId>mysql-connector-java</artifactId>
110                         <version>8.0.26</version>
111                     </dependency>
112                 </dependencies>
113                 <configuration>
114                     <configurationFile>src/main/resources/generatorConfig.xml</configurationFile>
115                     <verbose>true</verbose>
116                     <overwrite>true</overwrite>
117                 </configuration>
118             </plugin>
119         </plugins>
120     </build>
121 
122 </project>

1.1.5、Result封装类相关

  1 package com.moon.result;
  2 
  3 import javax.validation.Valid;
  4 import java.io.Serializable;
  5 import java.util.ArrayList;
  6 import java.util.List;
  7 
  8 public class DefaultResult<T> implements Result<T>, Serializable {
  9     private static final long serialVersionUID = -4408341719434417427L;
 10     private static final String SUCCESS_CODE = "0";
 11     private static final String UNKNOWN_ERROR = "1";
 12     private static final String ERROR_PREFIX = "SYS_";
 13     private String code;
 14     private String message;
 15     @Valid
 16     private T data;
 17     private String errorClass;
 18     private String errorStack;
 19     private List<ViolationItem> violationItems;
 20 
 21     public DefaultResult() {
 22     }
 23 
 24     public String getCode() {
 25         return this.code;
 26     }
 27 
 28     public String getMessage() {
 29         return this.message;
 30     }
 31 
 32     public T getData() {
 33         return this.data;
 34     }
 35 
 36     public List<ViolationItem> getViolationItems() {
 37         return this.violationItems;
 38     }
 39 
 40     public String getErrorClass() {
 41         return this.errorClass;
 42     }
 43 
 44     public String getErrorStack() {
 45         return this.errorStack;
 46     }
 47 
 48     public DefaultResult<T> setCode(String code) {
 49         this.code = code;
 50         return this;
 51     }
 52 
 53     public DefaultResult<T> setMessage(String message) {
 54         this.message = message;
 55         return this;
 56     }
 57 
 58     public DefaultResult<T> setData(T data) {
 59         this.data = data;
 60         return this;
 61     }
 62 
 63     public DefaultResult<T> setViolationItems(List<ViolationItem> violationItems) {
 64         this.violationItems = violationItems;
 65         return this;
 66     }
 67 
 68     public DefaultResult<T> setErrorClass(String errorClass) {
 69         this.errorClass = errorClass;
 70         return this;
 71     }
 72 
 73     public DefaultResult<T> setErrorStack(String errorStack) {
 74         this.errorStack = errorStack;
 75         return this;
 76     }
 77 
 78     public DefaultResult<T> addViolationItem(String field, String message) {
 79         if (this.violationItems == null) {
 80             this.violationItems = new ArrayList();
 81         }
 82 
 83         this.violationItems.add(new DefaultVioationItem(field, message));
 84         return this;
 85     }
 86 
 87     public boolean isSuccess() {
 88         return "0".equals(this.code);
 89     }
 90 
 91     public boolean isError() {
 92         return "1".equals(this.code) || this.code != null && this.code.startsWith("SYS_");
 93     }
 94 
 95     public boolean isFailure() {
 96         return !this.isSuccess() && !this.isError();
 97     }
 98 
 99     public String toString() {
100         return "Result{code='" + this.code + '\'' + ", message='" + this.message + '\'' + ", data=" + this.data + ", errorClass='" + this.errorClass + '\'' + ", errorStack='" + this.errorStack + '\'' + ", violationItems=" + this.violationItems + '}';
101     }
102 
103     public static class DefaultVioationItem implements ViolationItem, Serializable {
104         private static final long serialVersionUID = 2803300694383082237L;
105         private String field;
106         private String message;
107 
108         public DefaultVioationItem() {
109         }
110 
111         public DefaultVioationItem(String field, String message) {
112             this.field = field;
113             this.message = message;
114         }
115 
116         public String getField() {
117             return this.field;
118         }
119 
120         public void setField(String field) {
121             this.field = field;
122         }
123 
124         public String getMessage() {
125             return this.message;
126         }
127 
128         public void setMessage(String message) {
129             this.message = message;
130         }
131 
132         public boolean equals(Object o) {
133             if (this == o) {
134                 return true;
135             } else if (o != null && this.getClass() == o.getClass()) {
136                 DefaultVioationItem that = (DefaultVioationItem)o;
137                 boolean fieldFlag = this.field != null ? this.field.equals(that.field) : that.field == null;
138                 boolean messageFlag = this.message != null ? this.message.equals(that.message) : that.message == null;
139                 return messageFlag && fieldFlag;
140             } else {
141                 return false;
142             }
143         }
144 
145         public int hashCode() {
146             return this.field != null ? this.field.hashCode() : 0;
147         }
148 
149         public String toString() {
150             return "{field='" + this.field + '\'' + ", message='" + this.message + '\'' + '}';
151         }
152     }
153 }

 1 package com.moon.result;
 2 
 3 import java.io.Serializable;
 4 import java.util.List;
 5 
 6 public interface Result<T> extends Serializable {
 7     String getCode();
 8 
 9     String getMessage();
10 
11     T getData();
12 
13     List<ViolationItem> getViolationItems();
14 
15     String getErrorClass();
16 
17     String getErrorStack();
18 
19     Result<T> setCode(String code);
20 
21     Result<T> setMessage(String message);
22 
23     Result<T> setData(T data);
24 
25     Result<T> setViolationItems(List<ViolationItem> violationItems);
26 
27     Result<T> setErrorClass(String errorClass);
28 
29     Result<T> setErrorStack(String errorStack);
30 
31     Result<T> addViolationItem(String field, String message);
32 
33     boolean isSuccess();
34 
35     boolean isError();
36 
37     boolean isFailure();
38 
39     public interface ViolationItem extends Serializable {
40         String getField();
41 
42         void setField(String field);
43 
44         String getMessage();
45 
46         void setMessage(String message);
47     }
48 }

 1 package com.moon.result;
 2 
 3 public interface ResultCode {
 4     String SYS = "SYS";
 5     String COMMON = "C";
 6     String AUTH = "AUTH";
 7 
 8     String code();
 9 
10     String message();
11 }

 1 package com.moon.result;
 2 
 3 import java.util.function.Function;
 4 
 5 public final class Results {
 6     public Results() {
 7     }
 8 
 9     public static Result<Void> success() {
10         return (new DefaultResult()).setCode(SuccessCode.SUCCESS.code()).setMessage(SuccessCode.SUCCESS.message());
11     }
12 
13     public static <T> Result<T> success(T data) {
14         return (new DefaultResult()).setCode(SuccessCode.SUCCESS.code()).setMessage(SuccessCode.SUCCESS.message()).setData(data);
15     }
16 
17     public static <T, R> Result<R> success(T data, Function<T, R> transTFunction) {
18         return (new DefaultResult()).setCode(SuccessCode.SUCCESS.code()).setMessage(SuccessCode.SUCCESS.message()).setData(transTFunction.apply(data));
19     }
20 
21     public static <T> Result<T> error(String code, String message, String errorClass) {
22         return (new DefaultResult()).setCode(code).setMessage(message).setErrorClass(errorClass);
23     }
24 }

package com.moon.result;

public enum SuccessCode implements ResultCode {
    SUCCESS("0", "成功!");

    private final String code;
    private final String message;

    private SuccessCode(String code, String message) {
        this.code = code;
        this.message = message;
    }

    public String code() {
        return this.code;
    }

    public String message() {
        return this.message;
    }
}

1.1.6、启动服务测试，下载简单模板

http://localhost:8888/simpleTemplateDownload

1.2、下载复杂模板

1.2.1、EmployeeController.java中增加如下代码

1 /**
2  * 下载复杂模板
3  * @param response
4  * @return
5  */
6 @GetMapping("/complexTemplateDownload")
7 public void complexTemplateDownload(HttpServletResponse response) {
8     userService.complexTemplateDownload(response);
9 }

1.2.2、EmployeeService.java和EmployeeServiceImpl.java中增加如下代码

1 Result complexTemplateDownload(HttpServletResponse response);

 1 @Override
 2     public Result complexTemplateDownload(HttpServletResponse response) {
 3         try {
 4             response.setContentType("application/vnd.ms-excel");
 5             response.setCharacterEncoding("UTF-8");
 6             SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
 7             String fileName = URLEncoder.encode(sdf.format(new Date()) + "人员信息(复杂模板)", "UTF-8");
 8             response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
 9 
10             ServletOutputStream outputStream = response.getOutputStream();
11 
12             ExcelWriterBuilder workBook = EasyExcel.write(outputStream, EmployeeComplexImportTemplate.class);
13 
14             ExcelWriterSheetBuilder sheet = workBook.sheet("人员信息(复杂模板)");
15 
16             List<EmployeeComplexImportTemplate> budgetUnits = initData2();
17 
18             sheet.doWrite(budgetUnits);
19 
20             return Results.success();
21         } catch (Exception e) {
22             e.printStackTrace();
23             return Results.error("1", "失败", null);
24         }
25     }
26 
27     private List<EmployeeComplexImportTemplate> initData2() {
28         List<EmployeeComplexImportTemplate> employees = new ArrayList<>();
29 
30         EmployeeComplexImportTemplate employee = new EmployeeComplexImportTemplate("zhangsan", "zhangsan", 18, "男", "liubei@qq.com", "18812349876", "1998-08-08", "2021-03-02", "美团", "北京", "世界那么大,我想去看看", "C开发工程师", "钓鱼,爬山");
31         employees.add(employee);
32 
33         return employees;
34     }

1.2.3、启动服务下载复杂模板

http://localhost:8888/complexTemplateDownload

posted @ 2022-01-06 16:24 谁怕？一蓑烟雨任平生阅读(684) 评论(0) 收藏举报

刷新页面返回顶部

谁怕？一蓑烟雨任平生