EasyExcel 动态列，横向扩展，横向循环

Excel 模板版

1 模板 指令介绍

模板是处理复杂Excel的简单方法，复杂的Excel样式，可以用Excel直接编辑，完美的避开了代码编写样式的雷区，同时指令的支持，也提了模板的有效性 下面列举下EasyPoi支持的指令以及作用，最主要的就是各种fe的用法

• 空格分割
• 三目运算 {{test ? obj:obj2}}
• n: 表示 这个cell是数值类型 {{n:}}
• le: 代表长度{{le:()}} 在if/else 运用{{le:() > 8 ? obj1 : obj2}}
• fd: 格式化时间 {{fd:(obj;yyyy-MM-dd)}}
• fn: 格式化数字 {{fn:(obj;###.00)}}
• fe: 遍历数据,创建row
• !fe: 遍历数据不创建row
• $fe: 下移插入,把当前行,下面的行全部下移.size()行,然后插入
• #fe: 横向遍历
• v_fe: 横向遍历值
• !if: 删除当前列 {{!if:(test)}}
• 单引号表示常量值 '' 比如'1' 那么输出的就是 1
• &NULL& 空格
• ]] 换行符 多行遍历导出
• sum： 统计数据

2.下载模板

关键字段1、【fe: 】代表向下循环 

关键字段2、【v_fe:】代表向右循环 

 

实现效果

 3.实现代码：

 3.1此处用的layui

form.on('submit(dowAll)', function (data) {
        layer.load(2, {time: 2000});
        var param = data.field;
        param.langCode = langCode;

        //拼接查询参数
        var params = '';
        $.each(param, function (k, v) {
            params += '&' + k + '=' + v;
        });

        location.href=layui.setter.reqHost + 'afterCtrl/dowAll?langCode='+langCode+params;
        return false;
    });

3.2 后端 Controller

@GetMapping("/dowAll")
    public void dowAll(
            String client, String contractNo, String plateNo, String purchaseDate, Integer status, String prodCode, String saleman, Integer branch, Integer area, String depositDate, String dueDate, String paymentDate, String langCode, Integer currentPage, Integer pageSize,
            HttpServletResponse response
    ) {
        try{
            AcctPageParam param = new AcctPageParam(client, contractNo, plateNo, purchaseDate, status, prodCode, saleman, branch, area, depositDate, dueDate, paymentDate, currentPage, pageSize, langCode);
            log.info("下载还款计划【afterCtrl/dowAll】  start = {}", JSONObject.toJSONString(param));

            Map<String,Object> list = accountService.dowAll(param);
            //TODO Easypoi 根据模板下载
            TemplateExportParams params = new TemplateExportParams("template/Sale_temp.xlsx");
            params.setColForEach(true);
            Workbook workbook = ExcelExportUtil.exportExcel(params, list);
            OutputStream output = null;
            response.setContentType("application/force-download");// 应用程序强制下载
            //导出文件名：Claim+客诉种类+ERP CODE+导出日期，sheet名用客诉编号
            String fileName = "ACCT_" + "_" +DateUtils.formatDate2String(new Date(), DateUtils.SUP_SHORT_PATTERN2);
            try {
                response.setHeader("Content-Disposition", "attachment;filename="+fileName+".xls");
                output = response.getOutputStream();
                workbook.write(output);
                output.flush();
            } catch (Exception e) {
                e.printStackTrace();
                log.error("文件流关闭失败", e);
            } finally {
                try {
                    if (output != null) {
                        output.close();
                    }
                } catch (Exception e) {
                    log.error("文件流关闭失败", e);
                }
            }
            log.info("下载还款计划 end");
        }catch (Exception e){
            e.printStackTrace();
            log.error("loan_acct_details  err----",e);
        }
    }

3.3 ServiceImpl实现 【关键代码】

/**
     * 集合对象转为 temp 可识别的 Map
     * @param biList
     * @return
     * @throws Exception
     */
    public Map<String,Object> acctToExcelMap(List<AcctExcel> biList) throws Exception {
        Map<String,Object> data = new HashMap<>();
        List<Map<String, Object>> titles = new ArrayList<Map<String, Object>>();
        List<Map<String, Object>> rowList = new ArrayList<Map<String, Object>>();
        for (int i = 0; i < biList.size(); i++) {
            AcctExcel excel = biList.get(i);
            Map<String, Object> row = EmptyUtil.objectToMapExclude(excel,"collList");
            Map<String, Object> title;

            int idx = 0;
            for (ScheduleExcel item : excel.getCollList()) {
                if(i == 0){
                    title = new HashMap<String, Object>();
                    title.put("title_1", "PAYMENT");
                    title.put("title_2", item.getRepayDueDate() + "DATE");
                    title.put("title_3", "BALANCE");
                    title.put(REPAY_DUE_DATE, "t.col_" + (idx + 1));
                    title.put("paidTotalAmt", "t.col_" + (idx + 2));
                    title.put("totalBal", "t.col_" + (idx + 3));
                    titles.add(title);
                }
                row.put("col_" + (idx + 1), item.getTermNo());
                row.put("col_" + (idx + 2), item.getLoanAccountNo());
                row.put("col_" + (idx + 3), item.getRepayDueDate());
                idx += 3;
            }
            rowList.add(row);
        }
        data.put("titles", titles);
        data.put("rowList", rowList);
        System.out.println(JSON.toJSON(data));
        return data;
    }

下载对象

/**
 * 不变行对象
 *
 */
@Data
public class AcctExcel {

    @Excel(name = "SN")         //行号
    private Integer index;
    @Excel(name = "DATE")       //日期
    private String purchaseDate;
    @Excel(name = "BRANCH")     //品牌
    private String branchText;
    @Excel(name = "AREA")       //区域
    private String areaText;
    @Excel(name = "CLIENT")     //客户端
    private String client;
    @Excel(name = "COLOUR")     //颜色
    private String colorText;
    @Excel(name = "PLATE N0")   //板N0
    private String plateNo;
    @Excel(name = "QTY")        //数量
    private String qty;
    @Excel(name = "UNIT PRICE") //单价
    private String unitPrice;

    List<ScheduleExcel> collList = new ArrayList<>();
}

/**
 * 重复向右扩展对象
 *
 */
@Data
public class ScheduleExcel {
    @Excel(name = "DATE")           //还款日期
    private String repayDueDate;
    @Excel(name = "PAYMENT")        //付款金额
    private String paidTotalAmt;
    @Excel(name = "BALANCE")        //余额
    private String totalBal;
    private BigDecimal remainPrinAmt;

    private String loanAccountNo;
    private Integer termNo;
}