Cpp五条

这个屌丝很懒，什么也没留下！

热门标签

EasyPoi使用记录_easypoi-base

作者：Cpp五条 | 2024-05-17 16:49:53

踩

easypoi-base

概述

先给出入门实例，然后记录遇到的问题。
maven依赖如下：

<dependency>
	<groupId>cn.afterturn</groupId>
	<artifactId>easypoi-base</artifactId>
</dependency>
<dependency>
	<groupId>cn.afterturn</groupId>
	<artifactId>easypoi-web</artifactId>
</dependency>
<dependency>
	<groupId>cn.afterturn</groupId>
	<artifactId>easypoi-annotation</artifactId>
</dependency>
1
2
3
4
5
6
7
8
9
10
11
12

easypoi-base是最核心、基础的artifactId，查看其pom文件，可知easypoi实际上是基于poi封装一层而已。对于Spring Boot应用，一个依赖即可：

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

实例

Controller：

@PostMapping("/users/export")
public void exportUser(@RequestParam Map<String, Object> params, HttpServletResponse response) throws IOException {
    List<SysUserExcel> result = appUserService.findAllUsers(params);
    // 导出
    ExcelUtil.exportExcel(result, null, "用户", SysUserExcel.class, "user", response);
}

@PostMapping(value = "/users/import")
public Result importExcl(@RequestParam("file") MultipartFile excel) throws Exception {
    if (!excel.isEmpty()) {
        List<SysUserExcel> list = ExcelUtil.importExcel(excel, 0, 1, SysUserExcel.class);
    }
    return Result.succeed("导入数据成功，共" + list.size() + "行");
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

实体类：

// 仅作为示例，省略部分字段
import cn.afterturn.easypoi.excel.annotation.Excel;
@Data
public class SysUserExcel implements Serializable {
    @Excel(name = "用户姓名", height = 20, width = 30, isImportField = "true_st" )
    private String username;

    @Excel(name = "性别", replace = {"男_0", "女_1"}, isImportField = "true_st" )
    private Integer sex;

    @Excel(name = "创建时间", format = "yyyy-MM-dd HH:mm:ss", isImportField = "true_st", width = 20)
    private Date createTime;
}
1
2
3
4
5
6
7
8
9
10
11
12
13

工具类：

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

/**
 * Excel工具类
 */
public class ExcelUtil {
    /**
     * 导出
     *
     * @param list           数据列表
     * @param title          标题
     * @param sheetName      sheet名称
     * @param pojoClass      元素类型
     * @param fileName       文件名
     * @param isCreateHeader 是否创建列头
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        exportParams.setCreateHeadRows(isCreateHeader);
        this.defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * 导出
     *
     * @param list      数据列表
     * @param title     标题
     * @param sheetName sheet名称
     * @param pojoClass 元素类型
     * @param fileName  文件名
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        this.defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
    }

    /**
     * 导出,适用于导出多个sheet的Excel,配合createOneSheet方法一起使用
     *
     * @param list     数据列表(元素是Map)
     * @param fileName 文件名
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        this.defaultExport(list, fileName, response);
    }

	/**
	 * 导入
	 */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (StringUtils.isBlank(filePath)) {
            return Collections.emptyList();
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws Exception {
        if (file == null) {
            return Collections.emptyList();
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        return ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
    }

	/**
     * 功能描述：根据接收的Excel文件来导入多个sheet,根据索引可返回一个集合
     *
     * @param file       导入文件
     * @param sheetIndex 导入sheet索引,从0开始
     * @param titleRows  表标题的行数
     * @param headerRows 表头行数
     * @param pojoClass  Excel实体类
     */
    public static <T> List<T> importExcel(MultipartFile file, int sheetIndex, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws Exception {
        if (file == null) {
            return Collections.emptyList();
        }
        // 根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页
        ImportParams params = new ImportParams();
        // 第几个sheet页
        params.setStartSheetIndex(sheetIndex);
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        return ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
    }

	/**
     * 功能描述：根据接收的Excel文件来导入多个sheet,根据索引可返回一个集合
     *
     * @param filePath   导入文件路径
     * @param sheetIndex 导入sheet索引
     * @param titleRows  表标题的行数
     * @param headerRows 表头行数
     * @param pojoClass  Excel实体类
     */
    public static <T> List<T> importExcel(String filePath, int sheetIndex, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        ImportParams params = new ImportParams();
        params.setStartSheetIndex(sheetIndex);
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
    }

    /**
     * 用于导出多sheet的Excel文件
     *
     * @param sheetName 自定义sheetName
     * @param clazz     pojo实体类
     * @param data      List<Map<String, Object>> or List<POJO>
     * @return map
     */
    public static Map<String, Object> createOneSheet(String sheetName, Class<?> clazz, List<?> data) {
        ExportParams params = new ExportParams("", sheetName, ExcelType.XSSF);
        return this.createOneSheet(params, clazz, data);
    }

    /**
     * 创建一个表格并填充内容,返回map供工作簿使用,map的key必须写死
     *
     * @param params 导出配置
     * @param clazz  带@Excel注解字段的POJO实体类
     * @param data   List<Map<String, Object>> or List<POJO>
     * @return map
     */
    private static Map<String, Object> createOneSheet(ExportParams params, Class<?> clazz, List<?> data) {
        Map<String, Object> map = new HashMap<>(8);
        map.put("title", params);
        map.put("entity", clazz);
        map.put("data", data);
        return map;
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) {
            this.downLoadExcel(fileName, response, workbook);
        }
    }

    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.XSSF);
        if (workbook != null) {
            this.downLoadExcel(fileName, response, workbook);
        }
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        workbook.write(response.getOutputStream());
    }
}
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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163

源码

注解

@Excel注解
在这里插入图片描述

问题

HttpMessageNotWritableException: No converter for [class A] with preset Content-Type ‘application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8’ java.lang.IllegalStateException: Cannot call sendError() after the response has been committed

文件下载导出功能，不能return任何数据，即方法返回数据必须定义为void。

问题背景

mybatis plus用于返回List<Map<String, Object>>的mapper.xml定义：

<select id="findList" resultType="java.util.Map">
    select id, org_abbr orgAbbr, org_full orgFull, source_type sourceType, org_url orgUrl,
    we_chat_id weChatId, we_chat_name weChatName, org_type orgType, remark, is_active isActive
    from o_org_info
    where 1 = 1
    <if test="params.orgType != null and params.orgType != ''">
        and org_type = #{params.orgType}
    </if>
    <if test="params.sourceType != null and params.sourceType != ''">
        and source_type = #{params.sourceType}
    </if>
    order by update_time desc
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13

用于返回List<PO>的mapper.xml定义：

<select id="findList" resultType="com.central.ico.model.OrgInfo">
    select * from o_org_info where 1 = 1
    <if test="params.orgType != null and params.orgType != ''">
        and org_type = #{params.orgType}
    </if>
    <if test="params.sourceType != null and params.sourceType != ''">
        and source_type = #{params.sourceType}
    </if>
    order by update_time desc
</select>
1
2
3
4
5
6
7
8
9
10

sheet1对应的实体类：

@Data
public class OrgWebExcel {
    @Excel(name = "机构简称", width = 30)
    private String orgAbbr;

    @Excel(name = "机构全称", width = 30)
    private String orgFull;

    @Excel(name = "机构网址", width = 30)
    private String orgUrl;

    @Excel(name = "机构类别", width = 30)
    private String orgType;

    @Excel(name = "备注信息", width = 30)
    private String remark;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

sheet2对应的实体类：

@Data
public class OrgChatExcel implements Serializable {
    @Excel(name = "机构简称", width = 30)
    private String orgAbbr;

    @Excel(name = "机构全称", width = 30)
    private String orgFull;

    @Excel(name = "公众号ID", width = 30)
    private String weChatId;

    @Excel(name = "公众号名称", width = 30)
    private String weChatName;

    @Excel(name = "机构类别", width = 30)
    private String orgType;

    @Excel(name = "备注信息", width = 30)
    private String remark;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

完整的数据表PO定义：

@Data
@TableName("o_org_info")
public class OrgInfo extends SuperEntity {
    private String orgAbbr;
    private String orgFull;
    private Integer sourceType;
    private String orgUrl;
    private String weChatId;
    private String weChatName;
    private String orgType;
    private String remark;
    private Boolean isActive;
    private String createBy;
    private String updateBy;
    private Date createTime;
    private Date updateTime;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

此处定义的POJO是我希望导出的Excel文件两个sheet的字段，比findList（无论是通过PO还是Map形式）返回的字段的信息少很多。

导出的Excel无数据

@Override
public void export(Map<String, Object> params, HttpServletResponse response) {
    Page<OrgInfo> page = new Page<>(MapUtils.getInteger(params, "page"), MapUtils.getInteger(params, "limit"));
    List<Map<String, Object>> list = baseMapper.findList(page, params);
    List<Map<String, Object>> webList = list.stream().filter(x -> MapUtil.getInt(x, "sourceType") == 1).collect(Collectors.toList());

    List<Map<String, Object>> lists = new ArrayList<>();
    Map<String, Object> temp1 = this.createOneSheet(SHEET_NAME1, OrgWebExcel.class, webList);
    lists.add(temp1);
    try {
        ExcelUtil.exportExcel(lists, exportName, response);
    } catch (IOException e) {
        log.error("failed" + e.getMessage());
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

使用Map形式：导出不报错，但是没有数据！！！

解决方法：在mapper.xml文件里面只select需要的数据；

java.lang.IllegalArgumentException: object is not an instance of declaring class

 @Override
public void export(Map<String, Object> params, HttpServletResponse response) {
    Page<OrgInfo> page = new Page<>(MapUtils.getInteger(params, "page"), MapUtils.getInteger(params, "limit"));
    List<OrgInfo> list = baseMapper.findList(page, params);
    List<OrgInfo> chatList = list.stream().filter(x -> x.getSourceType().equals(1)).collect(Collectors.toList());

	// 未使用的正确的list<pojo>
    List<OrgChatExcel> chatExcelList = Lists.newArrayList();
    for (OrgInfo item : chatList) {
        OrgChatExcel excel = new OrgChatExcel();
        BeanUtils.copyProperties(item, excel);
        chatExcelList.add(excel);
    }

    List<Map<String, Object>> lists = new ArrayList<>();
    // 此处第三个参数使用chatExcelList则没有问题
    Map<String, Object> temp2 = ExcelUtil.createOneSheet(SHEET_NAME2, OrgChatExcel.class, chatList);
    lists.add(temp2);
    try {
        ExcelUtil.exportExcel(lists, exportName, response);
    } catch (IOException e) {
        e.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

使用PO形式，导出报错。
解决方法，把PO List转化成期望导出数据的POJO List：

List<OrgWebExcel> webExcelList = Lists.newArrayList();
for (OrgInfo item : webList) {
	OrgWebExcel excel = new OrgWebExcel();
	BeanUtils.copyProperties(item, excel);
	webExcelList.add(excel);
}
1
2
3
4
5
6

单列数据导入导出

很简单的数据，也没有任何字段说明：
在这里插入图片描述
对于这种形式的Excel文件，在使用EasyPoi导入、导出Excel时，哪怕只有一列，也需要定义对应的POJO，不能使用String来代替 POJO：

// 导入
List<WebUrl> webList = ExcelUtil.importExcel(file, 0, 0, 1, WebUrl.class);
// 不能使用这种形式
List<String> webList = ExcelUtil.importExcel(file, 0, 0, 1, String.class);

@Data
public class WebUrl {
    @Excel(name = "网址", width = 20)
    private String url;
}
1
2
3
4
5
6
7
8
9
10

同时，Excel模版文件增加一个字段说明：网址。

Map数据映射为POJO

使用Jackson ObjectMapper：

private final ObjectMapper mapper = new ObjectMapper();
@Override
public void export(Map<String, Object> params, HttpServletResponse response) {
    List<Map<String, Object>> list = baseMapper.exportList(params);
    List<CheckResultExcel> checkList = Lists.newArrayListWithCapacity(list.size());
    for (Map<String, Object> item : list) {
        // convert map into pojo
        CheckResultExcel excel = mapper.convertValue(item, CheckResultExcel.class);
        checkList.add(excel);
    }
    List<Map<String, Object>> resultList = new ArrayList<>();
    resultList.add(ExcelUtil.createOneSheet(exportName, CheckResultExcel.class, checkList));
    ExcelUtil.exportExcel(resultList, exportName, response);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

如果报错：com.fasterxml.jackson.databind.exc.UnrecognizedPropertyException: Unrecognized field "monitor_url"
解决思路：在mybatis mapper.xml里面查询出Map<String, Object>将数据表下划线形式的字段重命名为驼峰形式。