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

引言

 

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

 

Excel一对多导出

 

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

 

示例代码

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

import java.io.Serializable;
import java.util.List;
@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(id = "a_id",name = "")
    private List<GoodsSalesInfoSkuVO> goodsSalesInfoSkuVOList;
}

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.List;

@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(id = "b_id",name="")
    private List<GoodsSalesInfoStoreVO> goodsSalesInfoStoreVOList;
}

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

import java.io.Serializable;

@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能够便捷地导出一对多关系的编辑信息列表，且输出的表单也更简洁美观。

 

 

总结

 

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

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

注意：EasyPoi版本最好是3.3.0以上