spring boot 中easypoi导出导入excel_easypoi导出excel

1、安装依赖

<dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.4.0</version>
        </dependency>
1
2
3
4
5

2、实体类

package com.iot.iotdb.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
import lombok.EqualsAndHashCode;

@Data
@EqualsAndHashCode(callSuper = false)
public class Member {

    @Excel(name = "变量名称", width = 20)
    private String variableName;

    @Excel(name = "变量地址", width = 40)
    private String variableAddress;

    @Excel(name = "数据类型", width = 30)
    private String dataType;

    @Excel(name = "变量值", width = 25)
    private String variableValue;

    @Excel(name = "备注", width =50)
    private String remark;
}

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

在此我们就可以看到EasyPoi的核心注解@Excel，通过在对象上添加@Excel注解，可以将对象信息直接导出到Excel中去，下面对注解中的属性做个介绍；

name：Excel中的列名；
width：指定列的宽度；
needMerge：是否需要纵向合并单元格；
format：当属性为时间类型时，设置时间的导出导出格式；
desensitizationRule：数据脱敏处理，3_4表示只显示字符串的前3位和后4位，其他为*号；
replace：对属性进行替换；
suffix：对数据添加后缀。
3、导入

@PostMapping("/import")
    public List<Member> importExcel(MultipartFile file){
        ImportParams params = new ImportParams();
        //params.setTitleRows(1);//标题
        //params.setHeadRows(1);//表头
        try {
            List<Member> list = ExcelImportUtil.importExcel(
                    file.getInputStream(),
                    Member.class, params);
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

4、导出

@PostMapping("/export")
    public void exportExcel(HttpServletResponse response, @RequestBody List<Member> list){
        ExportParams params = new ExportParams(null, "测试", ExcelType.XSSF);
        String fileName="test.xlsx";
        Workbook workbook = ExcelExportUtil.exportExcel(params, Member.class, list);
        this.downLoadExcel(fileName,response,workbook);
    }

    public void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
            response.getOutputStream().close();
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage());
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

5、参考网站：https://www.cnblogs.com/livedian/p/15469742.html
6、注意：如果凶指定行开始并忽略红杭数据的话，则

ImportParams params = new ImportParams();
//        params.setTitleRows(4);//标题
        params.setHeadRows(4);//表头
        params.setNeedSave(true);
        params.setStartSheetIndex(0);
        params.setSheetNum(1);
        params.setKeyIndex(0);
1
2
3
4
5
6
7

8、对于日期类型的，则

@Excel(name="计划结束",importFormat = "yyyy-MM-dd")
1