帶你領略極緻簡便的報表生成工具——阿裡的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();
        }
    }