点击刷新
上周五来了新的需求，基本上我写的还款那一系列流程不要了（我好悲伤，当时写了很久的，逻辑复杂的写的我很骄傲），新的变成如上所示（仅仅一部分），勾选几笔后生成一个excel表格，不同的融资编号所引发的那堆数据生成的表格放在不同的sheet页。模板如下：
哎呦，这个表格不太规则哟~之前写的比较简单，中规中矩，一行字段标题对应几行数据，连表头都没有，但这个是双重表格，而且有16个以上的合并单元格，表格带边框，左对齐，右对齐，居中的数据都有，还有加粗的，大小不一的数据，一看写出代码就少不了。于是最开始的想法是偷个懒，坐等同事的在上一步业务中对每笔单号生成各自的excel，我在这一步拿着所有的融资编号去找到他们在上一步保存的excel，然后写套代码做一个excel的数据合并，将不同的excel合到一个excel中，放入不同sheet页（来夸夸我，我是不是很机智），结果我成功了，但我又失败了。。。我在网上找了套代码进行合并的，然后无脑的修改后还是运行，生成本地成功了，但是放在swagger上测试下载时，一直说文件打不开。。整到下班也一直说格式问题文件打不开，网上各种方式都试了，，果然，别人的代码不要随意套用，还是自己写一套比较实用。
周末的日子比较无聊，望着空荡荡的房子，安静的过分，无聊的我决定找点事情做，打开电脑打开IDEA，将周五决定偷懒的工作整一下，于是各种通过代码调整合并单元格，样式诸类。。。基本上把整个思路全写完了，大面积代码，剩下的就是那些数据在哪取我不清楚，只能先造点假数据了。。周一来了后，问了下同事数据取的表在哪，然后把代码补充下，再根据生成的excel样式对比模板表，调了调，大功告成。因为第一次处理生成这种样式的excel，所以纪念一下，有需要的朋友可以当个参考，注释我都写的很明确了，因为我在main方法里面加个测试数据，所以代码粘下来可以直接运行看效果。
代码如下：
package cn.exrick.xboot.common.utils; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import javax.servlet.http.HttpServletResponse; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.text.SimpleDateFormat; import java.util.*; /** * @author zae */ public class DownLoadPayMsgUtil { public static void main(String[] args) { //示例数据： //申请信息数据 String[] apllyTitlesKey = new String[]{"txn_no","deliver_contact","order_id","order_date","deliver_address","account_period","commodity_name","trade_amount"}; String[] applyTitle = new String[]{"序号","产融平台融资编号","发货人","订单编号","订单日期","工厂地址","收货地址","商品名称","运费金额"}; Map<String, Object> randomMapOne = getRandomMap(apllyTitlesKey); Map<String, Object> randomMapTwo = getRandomMap(apllyTitlesKey); Map<String, Object> randomMapThree = getRandomMap(apllyTitlesKey); Map<String, Object> randomMapFour = getRandomMap(apllyTitlesKey); List<Map<String,Object>> applyContantList = Arrays.asList(randomMapOne,randomMapTwo,randomMapThree,randomMapFour);//实际工作中在数据库中获取 String[][] applyContent = DownLoadPayMsgUtil.convertListToArray(apllyTitlesKey, applyContantList);//list转换二维数组 //支付信息数据 String[] payTitlesKey = new String[]{"carry_date","carry_no","carry_contact_user","carry_plate","carry_amount","account_bank","account_no"}; String[] payTitle = new String[]{"承运时间","提单号","承运人","承运车号","运费金额","开户行","账号"}; Map<String, Object> mapOne = getRandomMap(payTitlesKey); Map<String, Object> mapTwo = getRandomMap(payTitlesKey); List<Map<String,Object>> payContantList = Arrays.asList(mapOne,mapTwo);//实际工作中在数据库中获取 String[][] payContent = DownLoadPayMsgUtil.convertListToArray(payTitlesKey, payContantList);//list转换二维数组 //前置内容数据 List<String> beforeTitle = Arrays.asList("申请人:","开户行:","名称:","账号:"); List<String> beforeContent = Arrays.asList("zae","工商银行","zae的账户","274630575");//实际工作在数据库中取得 Map<String,Object> dataMap = new HashMap<>(); dataMap.put("applyTitle",applyTitle);//申请单表字段组 dataMap.put("applyContent",applyContent);//申请单表内容数据 dataMap.put("payTitle",payTitle);//支付信息表字段组 dataMap.put("payContent",payContent);//支付信息表内容数据 dataMap.put("beforeTitle",beforeTitle);//前置内容字段组 dataMap.put("beforeContent",beforeContent);//前置内容数据 dataMap.put("applyAmount","2000");//申请金额总和 dataMap.put("payAmount","3000");//运费金额总和 List<Map<String,Object>> dataList = new ArrayList<>(); Map<String,Object> dataMap2 = new HashMap<>(dataMap); dataList.add(dataMap);//多个map情况下会生成多个sheet页 dataList.add(dataMap2); //根据传入的数据，生成一个excel对象 HSSFWorkbook wb = DownLoadPayMsgUtil.getHSSFWorkbook(dataList); //导出下载 //DownLoadPayMsgUtil.exportExcel(wb,"提款申请.xls",null); //保存在本地 exportExcelToLocally(wb,"D:\\提款申请.xls"); } /** * 用传入的sheet名称，标题，内容生成HSSFWorkbook对象。 * @param dataList * @return HSSFWorkbook */ public static HSSFWorkbook getHSSFWorkbook(List<Map<String,Object>> dataList){ // 第一步，创建一个HSSFWorkbook，对应一个Excel文件 HSSFWorkbook wb = new HSSFWorkbook(); //第二步，创建确定位置的的合并单元格对象 CellRangeAddress callRangeAddress1 = new CellRangeAddress(2,2,0,8);//起始行,结束行,起始列,结束列 CellRangeAddress callRangeAddress2 = new CellRangeAddress(3,3,0,1);//起始行,结束行,起始列,结束列 CellRangeAddress callRangeAddress3 = new CellRangeAddress(3,3,2,5);//起始行,结束行,起始列,结束列 CellRangeAddress callRangeAddress4 = new CellRangeAddress(3,3,7,8);//起始行,结束行,起始列,结束列 CellRangeAddress callRangeAddress5 = new CellRangeAddress(4,4,0,1);//起始行,结束行,起始列,结束列 CellRangeAddress callRangeAddress6 = new CellRangeAddress(4,4,2,7);//起始行,结束行,起始列,结束列 CellRangeAddress callRangeAddress7 = new CellRangeAddress(5,5,0,1);//起始行,结束行,起始列,结束列 CellRangeAddress callRangeAddress8 = new CellRangeAddress(5,5,2,7);//起始行,结束行,起始列,结束列 CellRangeAddress callRangeAddress9 = new CellRangeAddress(6,6,0,1);//起始行,结束行,起始列,结束列 CellRangeAddress callRangeAddress10 = new CellRangeAddress(6,6,2,7);//起始行,结束行,起始列,结束列 CellRangeAddress callRangeAddress11 = new CellRangeAddress(8,8,0,8);//起始行,结束行,起始列,结束列 // 第三步，创建单元格样式（字体大小，是否加粗，是否加边框,对齐方式） HSSFCellStyle styleTitle = createCellStyle(wb,(short)11,true,false,"C");//标题头 HSSFCellStyle styleBeforeTitle = createCellStyle(wb,(short)9,false,false,"R");//前置内容字段 HSSFCellStyle styleBeforeData = createCellStyle(wb,(short)9,false,false,"F");//前置内容数据 HSSFCellStyle styleTableTitle = createCellStyle(wb,(short)9,true,true,"C");//表头 HSSFCellStyle styleTableContent = createCellStyle(wb,(short)9,false,true,"C");//表内容 for(int k=1;k<=dataList.size();k++){ Map<String,Object> dataMap = dataList.get(k-1); String []applyTitle = (String[]) dataMap.get("applyTitle");//申请信息表的字段组 String [][]applyContent = (String[][]) dataMap.get("applyContent");//申请信息表的内容组 String []payTitle = (String[]) dataMap.get("payTitle");//支付信息表的字段组 String [][]payContent = (String[][]) dataMap.get("payContent");//支付信息表的内容组 //第四步：创建未确定位置的合并单元格对象 CellRangeAddress callRangeAddress12 = new CellRangeAddress(10+applyContent.length,10+applyContent.length,0,4);//起始行,结束行,起始列,结束列 CellRangeAddress callRangeAddress13 = new CellRangeAddress(10+applyContent.length,10+applyContent.length,6,7);//起始行,结束行,起始列,结束列 CellRangeAddress callRangeAddress14 = new CellRangeAddress(11+applyContent.length,11+applyContent.length,0,8);//起始行,结束行,起始列,结束列 CellRangeAddress callRangeAddress15 = new CellRangeAddress(12+applyContent.length,12+applyContent.length,6,8);//起始行,结束行,起始列,结束列 // 第五步，在workbook中添加一个sheet,对应Excel文件中的sheet HSSFSheet sheet = wb.createSheet("Sheet"+k); //设置列宽 sheet.setColumnWidth(0, 256*15); sheet.setColumnWidth(1, 256*20); sheet.setColumnWidth(2, 256*25); sheet.setColumnWidth(5, 256*30); //第六步，加载合并单元格对象 sheet.addMergedRegion(callRangeAddress1); sheet.addMergedRegion(callRangeAddress2); sheet.addMergedRegion(callRangeAddress3); sheet.addMergedRegion(callRangeAddress4); sheet.addMergedRegion(callRangeAddress5); sheet.addMergedRegion(callRangeAddress6); sheet.addMergedRegion(callRangeAddress7); sheet.addMergedRegion(callRangeAddress8); sheet.addMergedRegion(callRangeAddress9); sheet.addMergedRegion(callRangeAddress10); sheet.addMergedRegion(callRangeAddress11); sheet.addMergedRegion(callRangeAddress12); sheet.addMergedRegion(callRangeAddress13); sheet.addMergedRegion(callRangeAddress14); sheet.addMergedRegion(callRangeAddress15); for(int i = 1;i<=payContent.length+1;i++){//支付信息账号部分单元格合并的创建以及加载 CellRangeAddress callRangeAddress16 = new CellRangeAddress(12+applyContent.length+i,12+applyContent.length+i,6,8);//起始行,结束行,起始列,结束列 sheet.addMergedRegion(callRangeAddress16); } // 第七步，根据模板往sheet中添加数据 //7.1 设置标题头和前置内容 List<String> beforeTitle = (List<String>) dataMap.get("beforeTitle"); List<String> beforeContent = (List<String>) dataMap.get("beforeContent"); //设置总标题 HSSFRow rowTitle = sheet.createRow(2); HSSFCell cellTitle = rowTitle.createCell(0); cellTitle.setCellStyle(styleTitle); cellTitle.setCellValue("提款信息表"); //设置前置内容数据 for(int i = 3;i<=6;i++){ HSSFRow rowBefore = sheet.createRow(i); for(int j = 0;j<=2;j++){//循环赋值前置信息数据（排除申请日期） if(j == 1){ continue; } HSSFCell cellBefore = rowBefore.createCell(j); if(j == 0){ cellBefore.setCellStyle(styleBeforeTitle); cellBefore.setCellValue(beforeTitle.get(i-3)); }else{ cellBefore.setCellStyle(styleBeforeData); cellBefore.setCellValue(beforeContent.get(i-3)); } } if(i == 3){//申请日期 HSSFCell cellBeforeDate = rowBefore.createCell(7); cellBeforeDate.setCellStyle(styleBeforeData); cellBeforeDate.setCellValue("申请日期:"+new SimpleDateFormat("yyyy-MM-dd").format(new Date())); } } //7.2申请信息 //设置申请信息表的表头 HSSFRow rowApplyTitle = sheet.createRow(8); for(int i = 0;i<applyTitle.length;i++){ HSSFCell cellApplyTitle = rowApplyTitle.createCell(i); cellApplyTitle.setCellStyle(styleTableTitle); cellApplyTitle.setCellValue("申请信息"); } //设置申请信息表的内容 for(int row = 9;row <= 9+applyContent.length;row++){ HSSFRow rowApplyContent = sheet.createRow(row); for(int cell = 0;cell<applyTitle.length;cell++){ HSSFCell cellApplyContent = rowApplyContent.createCell(cell); cellApplyContent.setCellStyle(styleTableContent); if(row == 9){//表的字段名 cellApplyContent.setCellValue(applyTitle[cell]); }else if(row != 9 && cell == 0){//表的序号列 cellApplyContent.setCellValue(row-9); }else{//表的内容 cellApplyContent.setCellValue(applyContent[row-10][cell-1]); } } } //设置申请信息表的底部 HSSFRow rowApplyBottom = sheet.createRow(9+applyContent.length+1); for(int i = 0;i<=8;i++){ if(i==0 || i==5 || i==6 || i==8){ HSSFCell cellApplyBottom = rowApplyBottom.createCell(i); cellApplyBottom.setCellStyle(styleTableContent); if(i == 8){ cellApplyBottom.setCellValue(dataMap.get("applyAmount").toString()); } } } //7.3支付信息 //设置支付信息表的表头 HSSFRow rowPayTitle = sheet.createRow(11+applyContent.length); for(int i = 0;i<applyTitle.length;i++){ HSSFCell cellPayTitle = rowPayTitle.createCell(i); cellPayTitle.setCellStyle(styleTableTitle); cellPayTitle.setCellValue("支付信息"); } //设置支付信息表的内容 for(int row = 12+applyContent.length;row<=12+applyContent.length+payContent.length+1;row++){ HSSFRow rowPayContent = sheet.createRow(row); for(int cell = 0;cell<payTitle.length-1;cell++){ HSSFCell cellPayContent = rowPayContent.createCell(cell); cellPayContent.setCellStyle(styleTableContent); if(row == 12+applyContent.length){//支付信息表的字段名 cellPayContent.setCellValue(payTitle[cell]); }else if(row == 12+applyContent.length+payContent.length+1){//支付信息表的最后一行 if(cell == 4){//运费金额总和 cellPayContent.setCellValue(dataMap.get("payAmount").toString()); }else{//空白 cellPayContent.setCellValue(""); } }else{//支付信息表的内容 cellPayContent.setCellValue(payContent[row-13-applyContent.length][cell]); } } for(int cell = payTitle.length-1;cell<9;cell++){//关于该表中账号字段的特殊处理 HSSFCell cellPayContent = rowPayContent.createCell(cell); cellPayContent.setCellStyle(styleTableContent); if(row == 12+applyContent.length){ cellPayContent.setCellValue(payTitle[payTitle.length-1]); }else if(row == 12+applyContent.length+payContent.length+1){ cellPayContent.setCellValue(""); }else{ cellPayContent.setCellValue(payContent[row-13-applyContent.length][payTitle.length-1]); } } } } return wb; } //把list转换为String[][] public static String[][] convertListToArray(String[] titles , List<Map<String,Object>> list){ //excel的主体内容 String[][] content = new String[list.size()][]; for (int i = 0; i < list.size(); i++) { Map<String,Object> map = list.get(i); content[i] = new String[titles.length]; for (int j = 0; j < titles.length; j++) { Object obj = map.get(titles[j]); if(obj == null){ obj = ""; } content[i][j] = obj.toString(); } } return content; } /** *字体大小，是否加粗，是否水平居中，是否加边框,左对齐"L",右对齐"R" * @param workbook * @param fontsize * @return 单元格样式 */ private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize,boolean flag,boolean flag2,String alignType) { HSSFCellStyle style = workbook.createCellStyle(); //左右对齐 if(alignType!=null){ if("L".equalsIgnoreCase(alignType)){ style.setAlignment(HSSFCellStyle.ALIGN_LEFT);//左对齐 }else if("R".equalsIgnoreCase(alignType)){ style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//右对齐 }else if("C".equalsIgnoreCase(alignType)){ style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中 } } //是否加边框 if(flag2){ style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 } style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中 //创建字体 HSSFFont font = workbook.createFont(); //是否加粗字体 if(flag){ font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); } font.setFontHeightInPoints(fontsize); //加载字体 style.setFont(font); return style; } /* 把HSSFWorkbook对象写入到浏览器输出流，完成下载功能 */ public static void exportExcel(HSSFWorkbook hssfWorkbook, String fileName, HttpServletResponse response){ try { //设置响应头 DownLoadPayMsgUtil.setResponseHeader(response, fileName); //获取输出流 OutputStream os = response.getOutputStream(); //写入 hssfWorkbook.write(os); os.flush(); os.close(); } catch (Exception e) { e.printStackTrace(); } } /* 设置浏览器下载响应头 */ private static void setResponseHeader(HttpServletResponse response, String fileName) { try { try { fileName = new String(fileName.getBytes(),"ISO8859-1"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } response.setContentType("application/octet-stream;charset=UTF-8"); response.setHeader("Content-Disposition", "attachment;filename="+ fileName); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); } catch (Exception ex) { ex.printStackTrace(); } } /** * 将生成的excel生成在本地 * @param hssfWorkbook * @param path */ private static void exportExcelToLocally(HSSFWorkbook hssfWorkbook,String path){ try { FileOutputStream fileOut = new FileOutputStream(path); hssfWorkbook.write(fileOut); fileOut.flush(); fileOut.close(); System.out.println("生成成功"); } catch (IOException e) { e.printStackTrace(); } } /** * 随机生成map数据，测试专用 * @param titleArr * @return */ private static Map<String,Object> getRandomMap(String [] titleArr){ Map<String,Object> rtnMap = new HashMap<>(); for(int i = 0;i<titleArr.length;i++){ rtnMap.put(titleArr[i],"测试数据"+i); } return rtnMap; } }
生成的效果如下：
生成了两个sheet页，当然如果勾选更多笔融资编号，就会生成更多的sheet页。
注释很清楚，不多解释了，嘻嘻。
© 著作权归作者所有
举报
相关热门文章
发表评论
0/200
