EasyPoi使用记录

概述

先给出入门实例，然后记录遇到的问题。
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>

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

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
</dependency>

实例

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() + "行");
}

实体类：

// 仅作为示例，省略部分字段
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;
}

工具类：

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());
    }
}

源码

注解

@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>

用于返回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>

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;
}

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;
}

完整的数据表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;
}

此处定义的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());
    }
}

使用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();
    }
}

使用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);
}

单列数据导入导出

很简单的数据，也没有任何字段说明：
在这里插入图片描述
对于这种形式的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;
}

同时，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);
}

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

参考

推荐阅读：EasyPoi教程

posted @   johnny233  阅读(88)  评论(0)  编辑  收藏  举报  
编辑推荐：
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
阅读排行：
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
点击右上角即可分享
微信分享提示