JAVA （ EasyExcel 通过模板导入、导出、下载模板）——亲测有用_easyexcel模板导出

作者：Cpp五条 | 2024-03-01 02:19:19

踩

easyexcel模板导出

本文参考：https://liuyanzhao.com/10060.html
先说两个实体类对象。
我这里举例 Student 和 StudentExcel
Student 就是你数据库表的对象
StudentExcel 是你导入进来的Excel对应的字段，为啥写他，用来校验数据格式，判空处理，我这里正则验证几乎把常用到的都写到了（身份证、银行卡、性别、手机号等等）如果你用不到把正则的验证的注解删了就可以啦。
你要把你导入进来的对象 StudentExcel 转换成你业务对象 Student ，转换方式我下面有工具类，大家可以参考去改一下。

如果你导入的参数有校验，或者不能为null，可以加注解来校验，日期校验注解是我自己写的注解，我一会下面会放大家直接copy就可以用。（常用的身份证、手机号、性别、日期、银行卡号都有做校验，参考我的 StudentExcel 实体类，日期是我单独写的一个注解类，放下面了，可以直接copy使用）

（写作不易，各位大佬觉得有用给个赞支持一下~）

1.模板copy到项目里

这个模板是你自己定义的写好的直接扔到项目里就行，不要直接扔在resources目录下，创一个文件夹（template）放进去。看图：
在这里插入图片描述
pom.xml添加依赖

	<!--easyexcel 依赖-->
	<dependency>
	   <groupId>com.alibaba</groupId>
	   <artifactId>easyexcel</artifactId>
	   <version>3.0.5</version>
	</dependency>

   <!--hibernate-validator-->
     <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-validation</artifactId>
         <version>2.4.3</version>
     </dependency>

	<!--lombok 依赖-->
	<dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.22</version>
    </dependency>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

**StudentExcel Excel对象

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Pattern;
import java.io.Serializable;

/**
 * @author ext.libo15
 * @title: 导入员工花名册
 * @date 2022/3/2 13:49
 */
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@HeadRowHeight(value = 40)
public class StudentExcel implements Serializable {

    private static final long serialVersionUID = -5144055068797033748L;

    /**
     * 姓名
     */
    @ExcelProperty(value = "姓名", index = 0)
    @ColumnWidth(value = 20)
    @NotBlank(message="姓名不能为空")
    private String name;

    /**
     * 性别 1：男 ：2女
     */
    @ExcelProperty(value = "性别\r1：男 ：2女", index = 1)
    @ColumnWidth(value = 20)
    @NotBlank(message="性别不能为空")
    private String gender;

    /**
     * 出生日期
     */
    @ExcelProperty(value = "出生日期", index = 2)
    @ColumnWidth(value = 20)
    @NotBlank(message="出生日期不能为空")
    @DateTimeStr(format ="yyyy-MM-dd", message = "出生日期格式错误，正确格式为：yyyy-MM-dd")
    private String birthday;

    /**
     * 年龄
     */
    @ExcelProperty(value = "年龄", index = 3)
    @ColumnWidth(value = 20)
    @NotBlank(message="年龄不能为空")
    private String age;


    /**
     * 身份证号码
     */
    @ExcelProperty(value = "身份证号码", index = 4)
    @ColumnWidth(value = 20)
    @NotBlank(message="身份证号码不能为空")
    @Pattern(regexp = "^\\d{15}$|^\\d{17}[0-9Xx]$",message = "身份证格式不正确!")//长度18，0-9
    private String cardNo;

    /**
     * 联系电话
     */
    @ExcelProperty(value = "联系电话", index = 5)
    @ColumnWidth(value = 20)
    @NotBlank(message="联系电话不能为空")
    @Pattern(regexp = "^[1][3,4,5,7,8][0-9]{9}$",message = "联系电话格式不正确!")//长度11
    private String telephone;

    /**
     * 是否已婚 1：是  2：否
     */
    @ExcelProperty(value = "是否已婚", index = 6)
    @ColumnWidth(value = 20)
    @NotBlank(message="是否已婚不能为空")
    private String marryStatus;


    /**
     * 银行卡号
     */
    @ExcelProperty(value = "银行卡号", index = 8)
    @ColumnWidth(value = 20)
    @NotBlank(message="银行卡号不能为空")
    @Pattern(regexp = "^([1-9]{1})(\\d{14}|\\d{18})$",message = "银行卡号格式不正确!")//长度11
    private String bankCardNo;


    

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

Student 对象（你的业务对象，就是数据库对应的对象）

public class Student implements Serializable {

    private static final long serialVersionUID = -5144055068797033748L;

    /**
     * 姓名
     */
    private String name;

    /**
     * 性别 1：男 ：2女
     */
    private int gender;

    /**
     * 出生日期
     */
    private Date birthday;

    /**
     * 年龄
     */
    private int age;


    /**
     * 身份证号码
     */
    private String cardNo;

    /**
     * 联系电话
     */
    private String telephone;

    /**
     * 是否已婚 1：是  2：否
     */
    private int marryStatus;

    /**
     * 银行卡号
     */
    private String bankCardNo;

    /**
     * 时间
     */
    private Date dimissionDate;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

Controller 代码：

    /**
     　　* 导入
     　　*/
    @RequestMapping(value="/import")
    public void importExcel(HttpServletRequest request, HttpServletResponse response,@RequestBody MultipartFile file) throws Exception {
        staffManageService.importExcel(file);
    }


    /**
     　　* 导出
     　　*/
    @RequestMapping(value="/export",method = RequestMethod.POST)
    public void batchExport(HttpServletResponse response) throws ParseException {
        staffManageService.export(response,staffRosterVo);
    }
    /**
     　　* 下载模板
     　　*
     　　* @param
     　　* @return
     　　* @throws Exception
     　　* @author ext.libo15
     　　* @date 2022/2/28 17:23
     　　*/
    @GetMapping(value="/download")
    public void download(HttpServletRequest request, HttpServletResponse response) throws FileNotFoundException {
        FileUtil.downloadExcel(request,response);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

2.下载模板

这里其实 Controller + FileUtil 工具类，下载模板的操作已经做好了。工具类我放最下面了

3.导入

    /**
     * 导入
     * @param file
     */
    @Override
    public void importExcel(MultipartFile file) throws Exception {
            List<Student> ExcelList = null;
            String validate = "";
            //校验文件大小 100M
            boolean size = FileUtil.checkFileSize(file.getSize(), 100, "M");
            if(size == false){
                //文件过大啦，只能100M
            }
            //excel读取数据
            staffExcelList = EasyExcel.read(new BufferedInputStream(file.getInputStream())).head(StaffDataExcel.class).sheet().doReadSync();
            if (ExcelList.size() > 1000) {
                //最多导入1000条哦
            }
            // excel数据校验
            validate = ValidatorUtils.beanValidate(ExcelList);
            if(!validate.equals("success")){
                //参数异常提示  如：姓名不能为空哦~
            }
            //把Excel数据转换成你对应的对象集合List
            List<Student> studentList = ExcelUtil.excelListConvertList(ExcelList);
            //拿到数据后就可以 入库操作啦 -----我自己的入库就不给你们写了
            StudentService.importExcel(studentList );
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

4.导出

    /**
     * 导出
     * @param response
     * @param staffRosterVo
     * @return
     * @throws ParseException
     */
    @Override
    public void export(HttpServletResponse response) throws ParseException {
        String fileName = "导出的文件名称";
        String sheetName = "导出的 sheetName ";
       
        List<Student> list = studentService.findList();
        List<StaffDataExportExcel> studentExcelList = ExcelUtil.listConvertexcelList(list );
        try {
           ExcelUtil.writeExcel(response, studentExcelList , fileName, sheetName, StaffDataExportExcel.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

5.工具类 FileUtil ：

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;

public class FileUtil {

    /**
     * 判断文件大小
     *
     * @param len
     *            文件长度
     * @param size
     *            限制大小
     * @param unit
     *            限制单位（B,K,M,G）
     * @return
     */
    public static boolean checkFileSize(Long len, int size, String unit) {
//        long len = file.length();
        double fileSize = 0;
        if ("B".equals(unit.toUpperCase())) {
            fileSize = (double) len;
        } else if ("K".equals(unit.toUpperCase())) {
            fileSize = (double) len / 1024;
        } else if ("M".equals(unit.toUpperCase())) {
            fileSize = (double) len / 1048576;
        } else if ("G".equals(unit.toUpperCase())) {
            fileSize = (double) len / 1073741824;
        }
        if (fileSize > size) {
            return false;
        }
        return true;
    }
	
	
    public static void downLoadFile(String filePath, String fileName, HttpServletResponse response) {
        try (InputStream inStream = new FileInputStream(filePath);
             OutputStream os = response.getOutputStream()) {
            response.setHeader("Content-Disposition", "attachment; fileName=" + URLEncoder.encode(fileName, "UTF-8"));
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            byte[] buff = new byte[100];
            int len;
            while ((len = inStream.read(buff)) > 0) {
                os.write(buff, 0, len);
            }
            os.flush();
        } catch (Exception e) {
            e.Exception ();
        }
    }

	//下载模板
    public static void downloadExcel(HttpServletRequest request, HttpServletResponse response){
        //方法一：直接下载路径下的文件模板（这种方式貌似在SpringCloud和Springboot中，打包成JAR包时，无法读取到指定路径下面的文件，不知道记错没，你们可以自己尝试下！！！）
        try {
        	//文件名称
            String fileName = "importStaffRoster.xlsx";
            //设置要下载的文件的名称
            response.setHeader("Content-disposition", "attachment;fileName=" + fileName);
            //通知客服文件的MIME类型
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            //获取文件的路径
            String filePath = FileUtil.class.getResource("/template/" + fileName).getPath();
            FileInputStream input = new FileInputStream(filePath);
            OutputStream out = response.getOutputStream();
            byte[] b = new byte[2048];
            int len;
            while ((len = input.read(b)) != -1) {
                out.write(b, 0, len);
            }
            //修正 Excel在“xxx.xlsx”中发现不可读取的内容。是否恢复此工作薄的内容？如果信任此工作簿的来源，请点击"是"
            response.setHeader("Content-Length", String.valueOf(input.getChannel().size()));
            input.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }

    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

6.工具类 ValidatorUtils （导入的时候参数校验）

import cn.hutool.core.lang.Assert;
import org.apache.commons.lang3.ObjectUtils;

import javax.validation.ConstraintViolation;
import javax.validation.Valid;
import javax.validation.Validation;
import javax.validation.Validator;
import java.util.*;

public class ValidatorUtils {
    private static final Validator validator = Validation.buildDefaultValidatorFactory()
            .getValidator();

    public static String beanValidate(@Valid Object obj) throws IllegalArgumentException {
        if (obj instanceof Collection) {
            ValidCollection<Object> validCollection = new ValidCollection<>();
            validCollection.setList((Collection) obj);
            return validate(validCollection);
        } else {
            return validate(obj);
        }
    }

    public static String validate(@Valid Object obj) {
        try {
            Map<String, String> validatedMsg = new HashMap<>();
            Set<ConstraintViolation<Object>> constraintViolations = validator.validate(obj);
            String validate = "";
            String message = "";
            for (ConstraintViolation<Object> c : constraintViolations) {
                validate = c.getPropertyPath().toString();
                validate = validate.substring(5,6);
                int unm = Integer.valueOf(validate) +1;
                validatedMsg.put( "表格第"+unm+"条 ", c.getMessage());
            }
            Assert.isTrue(ObjectUtils.isEmpty(constraintViolations), validatedMsg.toString());
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        return "success";
    }


    private static class ValidCollection<T> {
        @Valid
        private Collection<T> list;

        public Collection<T> getList() {
            return list;
        }

        public void setList(Collection<T> list) {
            this.list = list;
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtil {
    /**
     * 导出
     * @param response
     * @param data
     * @param fileName
     * @param sheetName
     * @param clazz
     * @throws Exception
     */
    public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz) throws Exception {
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);
    }
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        return response.getOutputStream();
    }

	/*
	* Excel转换实体类对象
	*/
    public static List<Student> excelListConvertList(List<StudentExcel> studentExcelList) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        List<Student> list = new ArrayList<>();
        for (StudentExcel s : studentExcelList) {
            Student student = Student.builder()
                    .name(staffDataExcel.getname())
                    .age(staffDataExcel.age())
                    .city(staffDataExcel.getCity())
                    .sex(staffDataExcel.getsex().equals("男")?1:2)
                    .birthday(sdf.parse(staffDataExcel.getBirthday()))
                    .build();
            list.add(student);
        }
        return list ;
    }

	/*
	*  导出的List集合 转换 Excel
	*/
    public static List<StudentExportExcel> listConvertexcelList(List<Student> student) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        List<StudentExcel> list= new ArrayList<>();
        for (Student s : student) {
            StudentExcel excel = StudentExcel.builder()
                    .name(staffDataExcel.getname())
                    .age(staffDataExcel.age())
                    .city(staffDataExcel.getCity())
                    .sex(staffDataExcel.getsex().equals("男")?1:2)
                    .birthday(sdf.parse(staffDataExcel.getBirthday()))
                    .build();
                    .build();
            list.add(excel);
        }
        return list;
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

7.校验日期注解


import javax.validation.Constraint;
import javax.validation.Payload;
import java.lang.annotation.*;

@Target({ElementType.FIELD, ElementType.PARAMETER})
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Constraint(validatedBy = CheckDateTimeCaseValidator.class)
public @interface DateTimeStr {

    String message() default "{javax.validation.constraints.DateTimeStr.message}";


    String format() default "yyyy-MM-dd";

    Class<?>[] groups() default {};

    Class<? extends Payload>[] payload() default {};
}




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Cpp五条/article/detail/170286

JAVA （ EasyExcel 通过模板 导入、导出、下载模板）——亲测有用_easyexcel模板导出

目录