本文与另一篇文章关联：
其中：
String accountDate 入参（日期）
AccountInfoEntityResp accountInfoEntityResp 导出的xml报文内容（转换成obj对象）
xml报文解析见另一篇：xml报文解析
HttpServletRequest request
HttpServletResponse response
主要核心代码如下：
/**
* 导出exc方法
*/
public void exportExcel(String accountDate, AccountInfoEntityResp accountInfoEntityResp,HttpServletRequest request,HttpServletResponse response) throws IOException {
// 声明一个工作簿
HSSFWorkbook wb = new HSSFWorkbook();
// 声明一个工作单并命名
HSSFSheet sheet = wb.createSheet("accountInfo");
// 创建标题样式
HSSFCellStyle headerStyle = (HSSFCellStyle) wb.createCellStyle();
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直对齐居中
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//创建字体
HSSFFont header_Font = (HSSFFont) wb.createFont();
header_Font.setFontName("微软雅黑");
header_Font.setFontHeightInPoints((short) 15);
headerStyle.setFont(header_Font);
// 创建单元格样式
HSSFCellStyle cell_Style = (HSSFCellStyle) wb.createCellStyle();// 设置字体样式
cell_Style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell_Style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直对齐居中
cell_Style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
cell_Style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cell_Style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cell_Style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cell_Style.setWrapText(true); // 设置为自动换行
HSSFFont cell_Font = (HSSFFont) wb.createFont();
cell_Font.setFontName("微软雅黑");
cell_Font.setFontHeightInPoints((short) 12);
cell_Style.setFont(cell_Font);
//创建自由样式
HSSFCellStyle free_Style = (HSSFCellStyle) wb.createCellStyle();// 设置字体样式
free_Style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
free_Style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont free_Font = (HSSFFont) wb.createFont();
free_Font.setFontName("微软雅黑");
free_Font.setFontHeightInPoints((short) 12);
free_Style.setFont(free_Font);
//【结算场次列表】单元格行数:SttlList->SttlInf集合size大小+1(标题行)
int sttlInfCount = accountInfoEntityResp.getSttlList().size()+1;
//【批次列表】单元格行数:SttlList->SttlInf->BatchList->BatchInf的集合大小+1(标题行)
int batchInfCount = 1;
//【分项列表】单元格行数:SttlList->SttlInf->BatchList->BatchInf->SubItemList->SubItemInf的集合大小+1(标题行)
int subItemInfCount = 0;
if(accountInfoEntityResp.getSttlList() != null){
for(int i = 0; i< accountInfoEntityResp.getSttlList().size() ;i++){
//批次列表大小
batchInfCount = batchInfCount + accountInfoEntityResp.getSttlList().get(i).getBatchList().size();
}
}
//结算场次列表+批次列表+分项列表 =明细单元格行数
int detailCount = sttlInfCount + batchInfCount + subItemInfCount ;
//1.1创建合并单元格对象
//头部"账务日期"
CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,7);//起始行,结束行,起始列,结束列
//第二行
CellRangeAddress callRangeAddress1 = new CellRangeAddress(1,1,0,1);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress1_2 = new CellRangeAddress(1,1,2,3);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress1_3 = new CellRangeAddress(1,1,4,5);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress1_4 = new CellRangeAddress(1,1,6,7);//起始行,结束行,起始列,结束列
//第三行
CellRangeAddress callRangeAddress2 = new CellRangeAddress(2,2,0,1);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress2_3 = new CellRangeAddress(2,2,2,3);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress2_4 = new CellRangeAddress(2,2,4,5);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress2_5 = new CellRangeAddress(2,2,6,7);//起始行,结束行,起始列,结束列
//明细
CellRangeAddress callRangeAddress3 = new CellRangeAddress(3,3,0,7);//起始行,结束行,起始列,结束列
//结算场次列表
CellRangeAddress callRangeAddress4 = new CellRangeAddress(4,4,0,7);//起始行,结束行,起始列,结束列
//批次列表
CellRangeAddress callRangeAddress5 = new CellRangeAddress(sttlInfCount+4+1,sttlInfCount+4+1,0,7);//起始行,结束行,起始列,结束列
//分项列表
CellRangeAddress callRangeAddress6 = new CellRangeAddress(sttlInfCount+4+1+batchInfCount+1,sttlInfCount+4+1+batchInfCount+1,0,7);//起始行,结束行,起始列,结束列
//2.1加载合并单元格对象
sheet.addMergedRegion(callRangeAddress);
sheet.addMergedRegion(callRangeAddress1);
sheet.addMergedRegion(callRangeAddress1_2);
sheet.addMergedRegion(callRangeAddress1_3);
sheet.addMergedRegion(callRangeAddress1_4);
sheet.addMergedRegion(callRangeAddress2);
sheet.addMergedRegion(callRangeAddress2_3);
sheet.addMergedRegion(callRangeAddress2_4);
sheet.addMergedRegion(callRangeAddress2_5);
sheet.addMergedRegion(callRangeAddress3);
sheet.addMergedRegion(callRangeAddress4);
sheet.addMergedRegion(callRangeAddress5);
sheet.addMergedRegion(callRangeAddress6);
//3.1创建头标题行;并且设置头标题
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
//加载单元格样式
cell.setCellStyle(headerStyle);
cell.setCellValue("财务日期:"+accountDate);
//4.1创建第二行
HSSFRow rower = sheet.createRow(1);
//结算总笔数
HSSFCell celler_1 = rower.createCell(0);
//借方金额
HSSFCell celler_2 = rower.createCell(2);
//贷方金额
HSSFCell celler_3 = rower.createCell(4);
//结算场次明细
HSSFCell celler_4 = rower.createCell(6);
//加载单元格样式
celler_1.setCellStyle(cell_Style);
celler_1.setCellValue("结算总笔数");
celler_2.setCellStyle(cell_Style);
celler_2.setCellValue("借方金额");
celler_3.setCellStyle(cell_Style);
celler_3.setCellValue("贷方金额");
celler_4.setCellStyle(cell_Style);
celler_4.setCellValue("结算场次明细");
//创建第三行
HSSFRow rowsan = sheet.createRow(2);
//结算总笔数
HSSFCell cellsan_1 = rowsan.createCell(0);
//借方金额
HSSFCell cellsan_2 = rowsan.createCell(2);
//贷方金额
HSSFCell cellsan_3 = rowsan.createCell(4);
//结算场次明细
HSSFCell cellsan_4 = rowsan.createCell(6);
//加载单元格样式
cellsan_1.setCellStyle(cell_Style);
cellsan_1.setCellValue(accountInfoEntityResp.getSttlCntNb());
cellsan_2.setCellStyle(cell_Style);
String tmpVal = String.valueOf(accountInfoEntityResp.getDebitCntAmt());
cellsan_2.setCellValue(tmpVal);
cellsan_3.setCellStyle(cell_Style);
String tmpVal2 = String.valueOf(accountInfoEntityResp.getCreditCntAmt());
cellsan_3.setCellValue(tmpVal2);
cellsan_4.setCellStyle(cell_Style);
cellsan_4.setCellValue("");
//创建明细标题、结算场次列表标题、批次列表标题、分项列表标题
//第四行：明细
HSSFRow rowsi = sheet.createRow(3);
HSSFCell cellsi = rowsi.createCell(0);
//加载单元格样式
cellsi.setCellStyle(cell_Style);
cellsi.setCellValue("明细");
//第五行：结算场次列表
HSSFRow rowwu = sheet.createRow(4);
HSSFCell cellwu = rowwu.createCell(0);
//加载单元格样式
cellwu.setCellStyle(cell_Style);
cellwu.setCellValue("结算场次列表");
//第六行：结算场次列表-标题栏
HSSFRow rowsix = sheet.createRow(5);
HSSFCell cellsix_1 = rowsix.createCell(0);
HSSFCell cellsix_2 = rowsix.createCell(1);
HSSFCell cellsix_3 = rowsix.createCell(2);
//加载单元格样式
cellsix_1.setCellStyle(cell_Style);
cellsix_1.setCellValue("报文标识号");
cellsix_2.setCellStyle(cell_Style);
cellsix_2.setCellValue("场次借贷标识");
cellsix_3.setCellStyle(cell_Style);
cellsix_3.setCellValue("场次金额");
//第7行：批次列表
HSSFRow rowseven = sheet.createRow(sttlInfCount+4+1);
HSSFCell cellseven = rowseven.createCell(0);
//加载单元格样式
cellseven.setCellStyle(cell_Style);
cellseven.setCellValue("批次列表");
//第8行：批次列表-->标题栏
HSSFRow rowsegiht = sheet.createRow(sttlInfCount+4+2);
HSSFCell cellegiht_1 = rowsegiht.createCell(0);
HSSFCell cellegiht_2 = rowsegiht.createCell(1);
HSSFCell cellegiht_3 = rowsegiht.createCell(2);
HSSFCell cellegiht_4 = rowsegiht.createCell(3);
//加载单元格样式
cellegiht_1.setCellStyle(cell_Style);
cellegiht_1.setCellValue("报文标识号");
cellegiht_2.setCellStyle(cell_Style);
cellegiht_2.setCellValue("批次号");
cellegiht_3.setCellStyle(cell_Style);
cellegiht_3.setCellValue("批次借贷标识");
cellegiht_4.setCellStyle(cell_Style);
cellegiht_4.setCellValue("批次金额");
//第9行：分项列表
HSSFRow rowba = sheet.createRow(sttlInfCount+4+1+batchInfCount+1);
HSSFCell cellba = rowba.createCell(0);
//加载单元格样式
cellba.setCellStyle(cell_Style);
cellba.setCellValue("分项列表");
//第10行：分项列表—>标题栏
HSSFRow rowsten = sheet.createRow(sttlInfCount+4+1+batchInfCount+2);
HSSFCell cellten_1 = rowsten.createCell(0);
HSSFCell cellten_2 = rowsten.createCell(1);
HSSFCell cellten_3 = rowsten.createCell(2);
HSSFCell cellten_4 = rowsten.createCell(3);
HSSFCell cellten_5 = rowsten.createCell(4);
HSSFCell cellten_6 = rowsten.createCell(5);
HSSFCell cellten_7 = rowsten.createCell(6);
HSSFCell cellten_8 = rowsten.createCell(7);
//加载单元格样式
cellten_1.setCellStyle(cell_Style);
cellten_1.setCellValue("批次号");
cellten_2.setCellStyle(cell_Style);
cellten_2.setCellValue("业务类型");
cellten_3.setCellStyle(cell_Style);
cellten_3.setCellValue("银行金额机构标识");
cellten_4.setCellStyle(cell_Style);
cellten_4.setCellValue("账户类型");
cellten_5.setCellStyle(cell_Style);
cellten_5.setCellValue("分项借方发生额");
cellten_6.setCellStyle(cell_Style);
cellten_6.setCellValue("分项借方发生笔数");
cellten_7.setCellStyle(cell_Style);
cellten_7.setCellValue("分项贷方发生额");
cellten_8.setCellStyle(cell_Style);
cellten_8.setCellValue("分项贷方发生笔数");
//结算场次列表、批次列表数据、分项列表数据
List<SttlInf> sttlList = new ArrayList<SttlInf>();
if(accountInfoEntityResp.getSttlList() != null ){
sttlList = accountInfoEntityResp.getSttlList();
}
//5.操作单元格;将列表数据写入excel
if(sttlList != null){
//结算场次列表数据
//批次列表循环计数器
int batInfCount = 0;
//分项列表循环计数器
int subItInfCount = 0;
for(int j=0; j< sttlList.size(); j++)
{
HSSFRow row3 = sheet.createRow(j+6);
HSSFCell cell0 = row3.createCell(0);
cell0.setCellStyle(cell_Style);
cell0.setCellValue(sttlList.get(j).getSttlReptFlg());
HSSFCell cell1 = row3.createCell(1);
cell1.setCellStyle(cell_Style);
cell1.setCellValue(DCFlag(sttlList.get(j).getSttlDCFlg()));
HSSFCell cell2 = row3.createCell(2);
cell2.setCellStyle(cell_Style);
cell2.setCellValue(String.valueOf(sttlList.get(j).getSttlAmt()));
List<BatchInf> BatchList = new ArrayList<BatchInf>();
if(sttlList.get(j).getBatchList() != null){
BatchList = sttlList.get(j).getBatchList();
}
if(BatchList != null){
//批次列表数据
for(int i = 0; i< BatchList.size(); i++){
batInfCount++;
HSSFRow row4 = sheet.createRow(sttlList.size()+6+1+batInfCount);
HSSFCell cell3 = row4.createCell(0);
cell3.setCellStyle(cell_Style);
cell3.setCellValue(sttlList.get(j).getSttlReptFlg());
HSSFCell cell4 = row4.createCell(1);
cell4.setCellStyle(cell_Style);
cell4.setCellValue(BatchList.get(i).getBatchId());
HSSFCell cell5 = row4.createCell(2);
cell5.setCellStyle(cell_Style);
cell5.setCellValue(DCFlag(BatchList.get(i).getBatchDCFlg()));
HSSFCell cell6 = row4.createCell(3);
cell6.setCellStyle(cell_Style);
cell6.setCellValue(String.valueOf(BatchList.get(i).getBatchNetAmt()));
List<SubItemInf> SubItemList = new ArrayList<SubItemInf>();
if(BatchList.get(i).getSubItemList() != null){
SubItemList = BatchList.get(i).getSubItemList();
}
//分项列表数据
if(SubItemList != null) {
for (int f = 0; f < SubItemList.size(); f++) {
subItInfCount++;
HSSFRow row5 = sheet.createRow((sttlInfCount+4+1+batchInfCount+2)+subItInfCount);
HSSFCell cell7 = row5.createCell(0);
cell7.setCellStyle(cell_Style);
cell7.setCellValue(BatchList.get(i).getBatchId());
String SubItemInf = SubItemList.get(f).getSubItemInf().replace("CNY","");
String[] subArray = {};
subArray = SubItemInf.split("\\|");
HSSFCell cell8 = row5.createCell(1);
cell8.setCellStyle(cell_Style);
cell8.setCellValue(busiType(subArray[0]));
HSSFCell cell9 = row5.createCell(2);
cell9.setCellStyle(cell_Style);
cell9.setCellValue(subArray[1]);
HSSFCell cell10 = row5.createCell(3);
cell10.setCellStyle(cell_Style);
cell10.setCellValue(accountType(subArray[2]));
HSSFCell cell11 = row5.createCell(4);
cell11.setCellStyle(cell_Style);
cell11.setCellValue(subArray[3]);
HSSFCell cell12 = row5.createCell(5);
cell12.setCellStyle(cell_Style);
cell12.setCellValue(subArray[4]);
HSSFCell cell13 = row5.createCell(6);
cell13.setCellStyle(cell_Style);
cell13.setCellValue(subArray[5]);
HSSFCell cell14 = row5.createCell(7);
cell14.setCellStyle(cell_Style);
cell14.setCellValue(subArray[6]);
}
}
}
}
}
}
String downFilename = "accountInfo.xls";
// 获取文件的MIME类型：
ServletContext servletContext = request.getServletContext();
String contentType = servletContext.getMimeType(downFilename);
// 将MIME类型放入响应
response.setContentType(contentType);
// 浏览器类型
String agent = request.getHeader("user-agent");
// 获取附件的名字和下载方式
String contentDisposition = "attachment;filename=" + downFilename;
// 将附件名字和下载方式放入响应头信息中
response.setHeader("Content-Disposition", contentDisposition);
// 告诉浏览器用什么软件可以打开此文件
/*response.setHeader("content-Type", "application/vnd.ms-excel");
// 下载文件的默认名称
response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(downFilename, "utf-8"));*/
ServletOutputStream out = response.getOutputStream();
wb.write(out);
out.close();
}
最终导出excel表格如下：
关注个人技术公众号：nick_coding1024
不定期分享最新前沿技术框架和bat大厂常用技术等，加群不定期分享行业内大牛直播讲课以及获得内退一线互联网公司机会。
---------------------CSDN技术博客
原文：https://blog.csdn.net/xuri24/article/details/83112954
网友评论