带你领略极致简便的报表生成工具——阿里的easyExcel

一、步骤

1、导入依赖

     <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.1</version>
        </dependency>

2、创建工具

/**
 * @author wyy
 * @version 1.0
 * @date 2019/7/30 16:27
 * @description
 **/
public class ExcelUtil {
    /**
     * 导出 Excel ：一个 sheet，带表头.
     *
     * @param response  HttpServletResponse
     * @param list      数据 list，每个元素为一个 BaseRowModel
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的 sheet 名
     * @param model     映射实体类，Excel 模型
     * @throws Exception 异常
     */
    public static void writeExcel(
            HttpServletResponse response, List<? extends BaseRowModel> list,
            String fileName, String sheetName, BaseRowModel model) throws Exception {
        ExcelWriter writer =
                new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1, 0, model.getClass());
        sheet.setSheetName(sheetName);
        writer.write(list, sheet);
        writer.finish();
    }

    /**
     * 导出文件时为Writer生成OutputStream.
     *
     * @param fileName 文件名
     * @param response response
     * @return ""
     */
    private static OutputStream getOutputStream(String fileName,
                                                HttpServletResponse response) throws Exception {
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();
        } catch (IOException e) {
            throw new Exception("导出excel表格失败!", e);
        }
    }
}

3、创建表模型

/**
 * @author wyy
 * @version 1.0
 * @date 2019/8/6 8:47
 * @description   
 **/
@Data（记得导入 import lombok.Data;）
public class ExcelProperty extends BaseRowModel {
    /**
     * value: 表头名称
     * index: 列的号, 0表示第一列
     */
    /**
     * 供应商
     */
    @ExcelProperty(value = "供应商", index = 1)
    private String supplierName;
     /**
     * 商品
     */
    @ExcelProperty(value = "商品名", index = 1)
    private String productName;
    /**
     * 入库件数
     */
    @ExcelProperty(value = "入库件数", index = 4)
    private Integer inNum;
    /**
     * 入库重量
     */
    @ExcelProperty(value = "入库重量(Kg)", index = 5)
    private BigDecimal inWeight;
    /**
     * 入库长度
     */
    @ExcelProperty(value = "入库长度(m)", index = 6)
    private BigDecimal inLength;
    /**
     * 入库面积
     */
    @ExcelProperty(value = "入库面积(m²)", index = 7)
    private BigDecimal inArea;
    /**
     * 出库件数
     */
    @ExcelProperty(value = "出库件数", index = 8)
    private Integer outNum;
    /**
     * 出库重量
     */
    @ExcelProperty(value = "出库重量(Kg)", index = 9)
    private BigDecimal outWeight;
    /**
     * 出库长度
     */
    @ExcelProperty(value = "出库长度(m)", index = 10)
    private BigDecimal outLength;
    /**
     * 出库面积
     */
    @ExcelProperty(value = "出库面积(m²)", index = 11)
    private BigDecimal outArea;


}

4、导入数据

 public void printSummary(Query query, HttpServletResponse response) {
       ExcelProperty property = new ExcelProperty();
       //这里持久实体和报表实体不建议用同一个 所以分开并转化
        List<ModelPO> list = reportDao.listallSummary(query);
        List<ExcelProperty> data = null;
        if (!CollectionUtils.isEmpty(list)) {
            data = list.stream()
                    .map(x -> new ExcelProperty (x.getSupplierName(), x.productName(), x.getInNum(), x.getInWeight(),
                            x.getInLength(), x.getInArea(), x.getOutNum(), x.getOutWeight(), x.getOutLength(), x.getOutArea()))
                    .collect(Collectors.toList());
        }
        try {
            ExcelUtil.writeExcel(response, data, "summaryReport", "汇总表", property );
        } catch (Exception e) {
            e.printStackTrace();
        }
    }