【Java】POI Excel导出 动态行合并

一般情况：

Excel导出一般都是一行一行的记录输出

、

这是Controller代码：

标题行的设置：

标题行会设置获取的结果集的字段名，数据会自动根据设置的名称匹配装填

特殊的需求:

如页面的效果，附加的三个字段存在多个记录，需要合并和之前的主记录拼接处理：

 右侧的数据是根据这个接口提供的：

页面有用循环回调来实现

但是导出功能，开发干脆就没写这部分了？？？

所以BA要我把这个部分给弄出来

合并原理：

关于POI框架和用友提供的封装方法都没有这样关于记录行合并的操作：

现有的方式都是对标题行进行合并设置的

这是一段原框架中的Main方法Demo案例：

    public static void main(String[] args) {
        NormalExcelExport excel = new NormalExcelExport();
        SXSSFWorkbook wb = excel.createExcelWork();
        List<List<ExcelCol>> colColList = new LinkedList();
        List<ExcelCol> exColList = new LinkedList();
        exColList.add(new ExcelCol("TEST1", "测试1", 2, 1));
        exColList.add(new ExcelCol("TEST2", "测试2", 2, 1));
        exColList.add(new ExcelCol("TEST3", "测试3", 1, 3));
        colColList.add(exColList);
        List<ExcelCol> exColList1 = new LinkedList();
        exColList1.add(new ExcelCol("TEST1", "测试1"));
        exColList1.add(new ExcelCol("TEST2", "测试2"));
        exColList1.add(new ExcelCol("TEST3", "测试3"));
        colColList.add(exColList1);
        List<Map> datas = new LinkedList();
        Map map1 = new HashMap();
        map1.put("TEST1", "1");
        map1.put("TEST2", "2");
        map1.put("TEST3", "3");
        datas.add(map1);
        String[] fieldNames = new String[]{"TEST1", "TEST2", "TEST3"};
        excel.addSheetMerge(wb, colColList, fieldNames, datas, "第1页");

        try {
            excel.saveLocal(wb, "F:/", "test1.xlsx");
        } catch (Exception var10) {
            var10.printStackTrace();
        }

    }

文件效果：

每一次添加之后该方法会将迭代器拨动至下一个基本单元行

 colColList.add(exColList);

方法执行之后，迭代器换行至这个位置：

这时就是程序理解的第二行：

添加完第二行记录之后，迭代器切换至第三行：

所以到这里我们就明白合并操作的原理了

这是我写的一个简单的案例：

    private static void demo2() {
        NormalExcelExport excel = new NormalExcelExport();
        SXSSFWorkbook wb = excel.createExcelWork();

        // 总表格
        List<List<ExcelCol>> colColList = new LinkedList();

        // 标题行
        List<ExcelCol> exColList = new LinkedList();
        exColList.add(new ExcelCol("TEST1", "标题1"));
        exColList.add(new ExcelCol("TEST2", "标题2"));
        exColList.add(new ExcelCol("TEST3", "标题3"));
        // 要合并的行标题
        exColList.add(new ExcelCol("TEST4", "标题4"));
        exColList.add(new ExcelCol("TEST5", "标题5"));
        exColList.add(new ExcelCol("TEST6", "标题6"));

        colColList.add(exColList);

        // 单次合并实现
        List<ExcelCol> leftSideRow = new LinkedList();

        // 左侧单元格 实现行合并
        leftSideRow.add(new ExcelCol("TEST1", "AAA", 2, 1));
        leftSideRow.add(new ExcelCol("TEST2", "BBB", 2, 1));
        leftSideRow.add(new ExcelCol("TEST3", "CCC", 2, 1));
        leftSideRow.add(new ExcelCol("TEST4", "41"));
        leftSideRow.add(new ExcelCol("TEST5", "42"));
        leftSideRow.add(new ExcelCol("TEST6", "43"));
        colColList.add(leftSideRow);
        leftSideRow = new LinkedList<>();
        leftSideRow.add(new ExcelCol("TEST4", "51"));
        leftSideRow.add(new ExcelCol("TEST5", "52"));
        leftSideRow.add(new ExcelCol("TEST6", "53"));
        colColList.add(leftSideRow);

        List<ExcelCol> smapleRow = new LinkedList();
        smapleRow.add(new ExcelCol("TEST1", "AAA3", 3, 1));
        smapleRow.add(new ExcelCol("TEST2", "AAA4", 3, 1));
        smapleRow.add(new ExcelCol("TEST3", "AAA5", 3, 1));
        smapleRow.add(new ExcelCol("TEST4", "AAA6"));
        smapleRow.add(new ExcelCol("TEST5", "AAA7"));
        smapleRow.add(new ExcelCol("TEST6", "AAA8"));

        colColList.add(smapleRow); // 换行时注意 前3个合并了3行，下一行会是 AA5开始
        smapleRow = new LinkedList<>();
        smapleRow.add(new ExcelCol("TEST4", "AAA26"));
        smapleRow.add(new ExcelCol("TEST5", "AAA27"));
        smapleRow.add(new ExcelCol("TEST6", "AAA28"));
        colColList.add(smapleRow);
        smapleRow = new LinkedList<>();
        smapleRow.add(new ExcelCol("TEST4", "AAA26"));
        smapleRow.add(new ExcelCol("TEST5", "AAA27"));
        smapleRow.add(new ExcelCol("TEST6", "AAA28"));
        colColList.add(smapleRow);


        excel.addSheetMerge(wb, colColList, null, null, "第1页");

        try {
            excel.saveLocal(wb, "D:/", "test1.xlsx");
        } catch (Exception var10) {
            var10.printStackTrace();
        }
    }

回到业务需求：

    /**
     *
     * @param queryParam
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/exportData2", method = RequestMethod.GET)
    @ResponseBody
    public Map<String, Object> SSPInvoiceSumReportExport2(@RequestParam Map<String, String> queryParam)
            throws Exception {
        ElemBean condition = new ElemBean(queryParam);
        List<List<ExcelCol>> colColList = new LinkedList();
        // 设置标题行
        List<ExcelCol> titleColList = new LinkedList<ExcelCol>();
        titleColList.add(new ExcelCol("asc_code","维修站代码"));
        titleColList.add(new ExcelCol("SAP_CODE","SAP码"));
        titleColList.add(new ExcelCol("asc_name","维修站名称"));
        titleColList.add(new ExcelCol("invoice_no","发票号"));
        titleColList.add(new ExcelCol("no_tax_amount","不含税金额"));
        titleColList.add(new ExcelCol("invoice_amount","含税金额"));
        titleColList.add(new ExcelCol("tax_amount","税费"));
        titleColList.add(new ExcelCol("audit_status","状态", ExcelDataType.DICT));
        titleColList.add(new ExcelCol("invoice_date","发票日期",ExcelDataType.DATEYYYYMMDD));
        titleColList.add(new ExcelCol("created_at","提报日期",ExcelDataType.DATEYYYYMMDD));
        titleColList.add(new ExcelCol("sap_code1","冲收入代码"));
        titleColList.add(new ExcelCol("product_price","合同金额"));
        titleColList.add(new ExcelCol("no_tax_income_amount","收入金额(不含税)"));
        colColList.add(titleColList);

        // 需要getInvoiceQuery的查询条件 设置 limit 9999999
//        PageInfoDto pageInfoDto = service.getInvoiceQuery(new ElemBean(queryParam));

        List<Object> params = new LinkedList<Object>();
        String sql = service.getSspInvoiceSumReportExpSql(condition, params);
        List<Map> pageInfoDto = DcsDaoUtil.findAll(sql, params);  // 左侧的主结果集

        Map<String,String> distMap = new LinkedHashMap<>();
        distMap.put("59701001","已提交");
        distMap.put("59701002","已审核");
        distMap.put("59701003","已驳回");
        distMap.put("59701004","已核销");

        for (int i = 0; i < pageInfoDto.size(); i++) {
            // 当前行
            Map currentRow = pageInfoDto.get(i);

            String  invoice_no =  StringUtils.isNullOrEmpty(currentRow.get("invoice_no")) ? "" :  currentRow.get("invoice_no").toString(); // 发票单号为空的情况
            List<Map> invoiceDetail = new LinkedList<>();
            // 右侧的
            if(!"".equals(invoice_no) && invoice_no != null){
                invoiceDetail = service.getInvoiceDetail(invoice_no, null);  // 右侧结果集
            }
            titleColList = new LinkedList<>();
            titleColList.add(new ExcelCol("asc_code", StringUtils.isNullOrEmpty(currentRow.get("asc_code")) ? "" : currentRow.get("asc_code").toString(),invoiceDetail.size() == 0 ? 1 : invoiceDetail.size() , 1));
            titleColList.add(new ExcelCol("SAP_CODE", StringUtils.isNullOrEmpty(currentRow.get("SAP_CODE")) ? "" :  currentRow.get("SAP_CODE").toString(),invoiceDetail.size() == 0 ? 1 : invoiceDetail.size() , 1));
            titleColList.add(new ExcelCol("asc_name", StringUtils.isNullOrEmpty(currentRow.get("asc_name")) ? "" :  currentRow.get("asc_name").toString(),invoiceDetail.size() == 0 ? 1 : invoiceDetail.size() , 1));
            titleColList.add(new ExcelCol("invoice_no", StringUtils.isNullOrEmpty(currentRow.get("invoice_no")) ? "" :  currentRow.get("invoice_no").toString(),invoiceDetail.size() == 0 ? 1 : invoiceDetail.size() , 1));
            titleColList.add(new ExcelCol("no_tax_amount", StringUtils.isNullOrEmpty(currentRow.get("no_tax_amount")) ? "" :  currentRow.get("no_tax_amount").toString(),invoiceDetail.size() == 0 ? 1 : invoiceDetail.size() , 1));
            titleColList.add(new ExcelCol("invoice_amount", StringUtils.isNullOrEmpty(currentRow.get("invoice_amount")) ? "" :  currentRow.get("invoice_amount").toString(),invoiceDetail.size() == 0 ? 1 : invoiceDetail.size() , 1));
            titleColList.add(new ExcelCol("tax_amount", StringUtils.isNullOrEmpty(currentRow.get("tax_amount")) ? "" :  currentRow.get("tax_amount").toString(),invoiceDetail.size() == 0 ? 1 : invoiceDetail.size() , 1));
            titleColList.add(new ExcelCol("audit_status", StringUtils.isNullOrEmpty(currentRow.get("audit_status")) ? "" :  distMap.get( currentRow.get("audit_status").toString()),invoiceDetail.size() == 0 ? 1 : invoiceDetail.size() , 1));
            titleColList.add(new ExcelCol("invoice_date", StringUtils.isNullOrEmpty(currentRow.get("invoice_date")) ? "" :  currentRow.get("invoice_date").toString().substring(0,10),invoiceDetail.size() == 0 ? 1 : invoiceDetail.size() , 1));
            titleColList.add(new ExcelCol("created_at", StringUtils.isNullOrEmpty(currentRow.get("created_at")) ? "" :  currentRow.get("created_at").toString().substring(0,10),invoiceDetail.size() == 0 ? 1 : invoiceDetail.size() , 1));
            for(int j = 0 ; j < invoiceDetail.size() ; j ++){
                if(j == 0){ // 第一行的的时候就需要和前面的内容 并列为一行
                    titleColList.add(new ExcelCol("sap_code1",StringUtils.isNullOrEmpty(invoiceDetail.get(j).get("sap_code1")) ? "" : invoiceDetail.get(j).get("sap_code1").toString()));
                    titleColList.add(new ExcelCol("product_price",StringUtils.isNullOrEmpty(invoiceDetail.get(j).get("product_price")) ? "" : invoiceDetail.get(j).get("product_price").toString()));
                    titleColList.add(new ExcelCol("no_tax_income_amount",StringUtils.isNullOrEmpty(invoiceDetail.get(j).get("no_tax_income_amount")) ? "" : invoiceDetail.get(j).get("no_tax_income_amount").toString()));
                    colColList.add(titleColList);
                }else{ // 后面下推的行记录就是新的一行了
                    titleColList = new LinkedList<>();
                    titleColList.add(new ExcelCol("sap_code1",StringUtils.isNullOrEmpty(invoiceDetail.get(j).get("sap_code1")) ? "" : invoiceDetail.get(j).get("sap_code1").toString()));
                    titleColList.add(new ExcelCol("product_price",StringUtils.isNullOrEmpty(invoiceDetail.get(j).get("product_price")) ? "" : invoiceDetail.get(j).get("product_price").toString()));
                    titleColList.add(new ExcelCol("no_tax_income_amount",StringUtils.isNullOrEmpty(invoiceDetail.get(j).get("no_tax_income_amount")) ? "" : invoiceDetail.get(j).get("no_tax_income_amount").toString()));
                    colColList.add(titleColList);
                }

            }

            if(invoiceDetail.size() == 0){ // 还存在右边结果集查不到结果的情况，这一行也要保留为空记录
                titleColList.add(new ExcelCol("sap_code1",""));
                titleColList.add(new ExcelCol("product_price",""));
                titleColList.add(new ExcelCol("no_tax_income_amount",""));
                colColList.add(titleColList);
            }
        }


        // for local tested , write by dzz 2021年5月20日18:28:41
//        NormalExcelExport excel = new NormalExcelExport();
//
//        SXSSFWorkbook wb = excel.createExcelWork();
//
//        excel.addSheetMerge(wb, colColList, null, null, "第1页");
//        excel.saveLocal(wb, "D:/", "test1.xlsx");



        Map<String, Object> maps = jmcExc.addSheetMerge(colColList, null, " select * from (select 1 + 1 ) a where 1 = 2 ", null, "SSP发票汇总报表.xlsx", "SSP发票汇总报表", null);
        return maps;
    }

最后的调用方法：

 Map<String, Object> maps = jmcExc.addSheetMerge(colColList, null, " select * from (select 1 + 1 ) a where 1 = 2 ", null, "SSP发票汇总报表.xlsx", "SSP发票汇总报表", null);

SQL参数时方法中有一个数据装填操作，如果SQL没有记录就不会装数据执行：

无记录的SQL：

" select * from (select 1 + 1 ) a where 1 = 2 "

要求的字段名为空，SQL参数空

最后直接把这个【标题结果集】丢进去，就实现了页面的那种效果