基于EasyPOI便捷高效地实现Excel导出操作

本文来自网易云社区

作者：刘哲敏

引言

在后台管理系统的开发过程中Excel文件的批量编辑操作时最常见的需求，比如Excel文件的批量导入编辑和数据记录的批量导出为Excel文件等。在实现此类需求时后端开发通常采用POI作为工具类，然而直接采用POI实现Excel文件的批量编辑功能通常需要编写较多的重复代码，开发效率较低。针对该痛点，本文将介绍如何利用EasyPOI（一个基于POI实现的工具类）高效实现导出需求。本文则通过实例的方式介绍说明如何利用EasyPOI实现Excel一对多导出以及大数据批量导出的功能。

Excel一对多导出

在商品运营中后台，由于一个商品对应于多个不同的SKU规格，且每个SKU又有对应多个不同的仓库，该特性导致商品的批量导出操作需要是一对多的关系导出。下面就以商品编辑信息导出为例说明利用EasyPOI实现一对多导出的方式。

示例代码

PO类

@Data
public class GoodsSalesInfoVO implements Serializable {
    private static final long serialVersionUID = -1443811068930105508L;

    @Excel(name = "商品ID", needMerge = true, width = 20)
    private Long goodsId;

    @Excel(name = "商品名称", needMerge = true, width = 20)
    private String goodsName;

    @Excel(name = "编辑状态", needMerge = true, dict = "goodsEditStatus", width = 20)
    private Integer editStatus;

    @Excel(name = "供应商", needMerge = true, width = 20)
    private String supplier;

    @ExcelCollection(name = "")
    private List<GoodsSalesInfoSkuVO> goodsSalesInfoSkuVOList;
}

@Data
public class GoodsSalesInfoSkuVO implements Serializable {
    private static final long serialVersionUID = 977499895912206399L;

    @Excel(name = "SkuID", needMerge = true, width = 20)
    private String skuId;

    @Excel(name = "Sku规格", needMerge = true, width = 20)
    private String skuDesc;

    @Excel(name = "成本价", needMerge = true, width = 20)
    private BigDecimal costPrice;

    @Excel(name = "供应商价", needMerge = true, width = 20)
    private BigDecimal supplierPrice;

    @Excel(name = "销售价", needMerge = true, width = 20)
    private BigDecimal salePrice;

    @Excel(name = "市场价", needMerge = true, width = 20)
    private BigDecimal marketPrice;

    @ExcelCollection(name = "")
    private List<GoodsSalesInfoStoreVO> goodsSalesInfoStoreVOList;
}

@Data
public class GoodsSalesInfoStoreVO implements Serializable {
    private static final long serialVersionUID = 6178454340122797296L;

    @Excel(name = "仓库ID", needMerge = true, width = 20)
    private Long warehouseId;

    @Excel(name = "仓库名称", needMerge = true, width = 40)
    private String warehouseName;

    @Excel(name = "已锁定库存数量", needMerge = true, width = 40)
    private Integer lockStoreNum;

    @Excel(name = "剩余可锁定锁定数量", needMerge = true, width = 40)
    private Integer lockableNum;
}

自定义单元格转换字典

public class GoodsExcelDictHandlerImpl implements IExcelDictHandler {
    @Override
    public String toName(String dict, Object obj, String name, Object value) {
        if ("goodsEditStatus".equals(dict)) {
            switch ((Integer) value) {
                case 1:
                    return "编辑中";
                case 2:
                    return "待提交";
                case 3:
                    return "待审核";
                case 4:
                    return "已上线";
                default:
                    return "";
            }
        }
        return null;
    }

    @Override
    public String toValue(String dict, Object obj, String name, Object value) {
        return null;
    }
}

单元测试代码

public class GoodsExportTest {
    @Test
    public void OneToManyTest() {
        List<GoodsSalesInfoVO> goodsList = getGoodsList();

        try {
            ExportParams params = new ExportParams("商品一对多,对多,对多导出", "测试", ExcelType.XSSF);
            // 指定单元格转换字典
            params.setDictHandler(new GoodsExcelDictHandlerImpl());
            Workbook workbook = ExcelExportUtil.exportExcel(params, GoodsSalesInfoVO.class, goodsList);
            File saveFile = new File("excel");
            if (!saveFile.exists()) {
                saveFile.mkdirs();
            }
            FileOutputStream fos = new FileOutputStream("excel/ExcelExportOneToManyGoodsTest.xlsx");
            workbook.write(fos);
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private List<GoodsSalesInfoVO> getGoodsList() {
        List<GoodsSalesInfoVO> goodsSalesInfoVOList = Lists.newArrayList();
        GoodsSalesInfoVO goods1 = new GoodsSalesInfoVO();
        goods1.setGoodsId(1001L);
        goods1.setEditStatus(1);
        goods1.setGoodsName("测试商品1");
        goods1.setSupplier("测试供应商");
        List<GoodsSalesInfoSkuVO> goods1SkuList = Lists.newArrayList();
        GoodsSalesInfoSkuVO goods1Sku1 = new GoodsSalesInfoSkuVO();
        goods1Sku1.setSkuId("1001-001");
        goods1Sku1.setSkuDesc("测试规格1");
        goods1Sku1.setCostPrice(new BigDecimal("100"));
        goods1Sku1.setMarketPrice(new BigDecimal("500"));
        goods1Sku1.setSalePrice(new BigDecimal("300"));
        goods1Sku1.setSupplierPrice(new BigDecimal("150"));
        List<GoodsSalesInfoStoreVO> sku1StoreList = Lists.newArrayList();
        GoodsSalesInfoStoreVO sku1Store1 = new GoodsSalesInfoStoreVO();
        sku1Store1.setWarehouseId(1L);
        sku1Store1.setWarehouseName("测试仓库1");
        sku1Store1.setLockStoreNum(100);
        sku1Store1.setLockableNum(200);
        sku1StoreList.add(sku1Store1);
        GoodsSalesInfoStoreVO sku1Store2 = new GoodsSalesInfoStoreVO();
        sku1Store2.setWarehouseId(2L);
        sku1Store2.setWarehouseName("测试仓库2");
        sku1Store2.setLockStoreNum(100);
        sku1Store2.setLockableNum(300);
        sku1StoreList.add(sku1Store2);
        goods1Sku1.setGoodsSalesInfoStoreVOList(sku1StoreList);
        goods1SkuList.add(goods1Sku1);

        GoodsSalesInfoSkuVO goods1Sku2 = new GoodsSalesInfoSkuVO();
        goods1Sku2.setSkuId("1001-002");
        goods1Sku2.setSkuDesc("测试规格2");
        goods1Sku2.setCostPrice(new BigDecimal("60"));
        goods1Sku2.setMarketPrice(new BigDecimal("400"));
        goods1Sku2.setSalePrice(new BigDecimal("200"));
        goods1Sku2.setSupplierPrice(new BigDecimal("100"));
        List<GoodsSalesInfoStoreVO> sku2StoreList = Lists.newArrayList();
        GoodsSalesInfoStoreVO sku2Store1 = new GoodsSalesInfoStoreVO();
        sku2Store1.setWarehouseId(1L);
        sku2Store1.setWarehouseName("测试仓库1");
        sku2Store1.setLockStoreNum(100);
        sku2Store1.setLockableNum(200);
        sku2StoreList.add(sku2Store1);
        GoodsSalesInfoStoreVO sku2Store2 = new GoodsSalesInfoStoreVO();
        sku2Store2.setWarehouseId(2L);
        sku2Store2.setWarehouseName("测试仓库2");
        sku2Store2.setLockStoreNum(100);
        sku2Store2.setLockableNum(300);
        sku2StoreList.add(sku2Store2);
        goods1Sku2.setGoodsSalesInfoStoreVOList(sku2StoreList);
        goods1SkuList.add(goods1Sku2);
        goods1.setGoodsSalesInfoSkuVOList(goods1SkuList);
        goodsSalesInfoVOList.add(goods1);
        return goodsSalesInfoVOList;
    }
}

测试导出结果

运行示例测试代码得到一对多商品信息的Excel结果如下图所示：

正如上方的测试导出结果所示，使用EasyPOI能够便捷地导出一对多关系的编辑信息列表，且输出的表单也更简洁美观。

使用小结

@Excel注解使用

这个是必须使用的注解,如果需求简单只使用这一个注解也是可以的,涵盖了常用的Excel需求。常用的属性如下表所示：

属性	类型	默认值	功能
name	String	null	列名,支持name_id
needMerge	boolean	fasle	是否需要纵向合并单元格(用于含有list中,单个的单元格,合并list创建的多个row)
orderNum	String	"0"	列的排序,支持name_id
width	double	10	列宽
suffix	String	""	文字后缀,如% 90 变成90%
isColumnHidden	boolean	false	导出隐藏列

@ExcelCollection注解使用

一对多的集合注解,用以标记集合是否被数据以及集合的整体排序

属性	类型	默认值	功能
id	String	null	定义ID
name	String	null	定义集合列名,支持name_id
orderNum	int	0	排序,支持name_id
type	Class<?>	ArrayList.class	导入时创建对象使用

自定义实现IExcelDictHandler

从3.0.4版本的EasyPOI开始支持以实现IExcelDictHandler接口的形式来实现自定转化字典操作。开发人员可以通过实现toName()方法实现从值到名称的翻译工作（导出），通过实现toValue实现从名称到值的翻译工作（导入）。

public interface IExcelDictHandler {
    /**
     * 从值翻译到名称
     *
     * @param dict  字典Key
     * @param obj   对象
     * @param name  属性名称
     * @param value 属性值
     * @return
     */
    public String toName(String dict, Object obj, String name, Object value);

    /**
     * 从名称翻译到值
     *
     * @param dict  字典Key
     * @param obj   对象
     * @param name  属性名称
     * @param value 属性值
     * @return
     */
    public String toValue(String dict, Object obj, String name, Object value);
}

Excel大批量导出

大数据导出是当我们的导出数量在几万,到上百万的数据时,一次从数据库查询这么多数据加载到内存然后写入会对我们的内存和CPU都产生压力,这个时候需要我们像分页一样处理导出分段写入Excel缓解Excel的压力。

示例代码

    @Test
    public void BigExcelExportSingleSheetTest() throws IOException {
        Workbook workbook = null;
        ExportParams params = new ExportParams("大数据测试", "测试");
        params.setDictHandler(new GoodsExcelDictHandlerImpl());
        for (int i = 0; i < 6; i++) {
            // 数据库分页查询结果
            List<GoodsSkuManageDO> goodsList = getGoodsListByPage(i, 10000);
            // 数据分页导出
            workbook = ExcelExportUtil.exportBigExcel(params, GoodsSkuManageDO.class, goodsList);
            goodsList.clear();
        }
        ExcelExportUtil.closeExportBigExcel();
        File saveFile = new File("excel/");
        if (!saveFile.exists()) {
            saveFile.mkdirs();
        }
        FileOutputStream fos = new FileOutputStream("excel/ExcelExportBigData.bigGoodsDataExport2.xlsx");
        workbook.write(fos);
        fos.close();
    }

测试导出结果

正如上方截图所展示的测试导出结果，导出10万条14列数据的处理时间为9322ms，且导出的文件前两行标题栏保持固定。

使用小结

• 大数据批量导出强制使用xssf版本的Excel

总结

EasyPOI诞生的意义就是为不太熟悉poi的，不想写太多重复太多的，只是简单的导入导出的且喜欢使用模板的开发人员提供更便捷的方式来实现基于注解的导入导出,其特点在于：

• 修改注解就可以修改Excel
• 支持常用的样式自定义
• 基于map可以灵活定义的表头字段
• 支持一对多的导出,导入
• 支持模板的导出,一些常见的标签,自定义标签
• 支持HTML/Excel转换,如果模板还不能满足用户的变态需求,请用这个功能
• 支持word的导出,支持图片,Excel

EasyPOI已经实现了一些常见Excel文件操作的基本工具类，如需了解其他本文未提及操作的实现方式可查阅官方文档：http://easypoi.mydoc.io/#text_202984

网易云免费体验馆，0成本体验20+款云产品！ 

更多网易研发、产品、运营经验分享请访问网易云社区。