【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参数空
最后直接把这个【标题结果集】丢进去，就实现了页面的那种效果
