一般情况：
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参数空
最后直接把这个【标题结果集】丢进去，就实现了页面的那种效果
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
