当前位置：首页 > news >正文

excel 动态列导出

news 来源：原创 2024/5/19 19:11:00

excel动态列，只好用poi来写了，也并不复杂，一样就这个件事情抽像为几步，就是套路了，开发效率就上去了。
1 准备空模板
导出操作与excel模板的导出一样，可以参考excel导出标准化

2 自定义SheetWriteHandler
要通过pos自己创建每一样，像模板一样创建即可.

WriteSheet sheet0 = EasyExcel.writerSheet(0)//标题.registerWriteHandler(new GoodsInvRdSumWriteHandler(goodsInvRdSumListDto.getHeader())).build();

主要重写afterSheetCreate,也就是一行行的创建excel模板

 @Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {Workbook workbook = writeWorkbookHolder.getWorkbook();this.centerCellStyle = createCellContentStyle(workbook,HorizontalAlignment.CENTER,BorderStyle.THIN);this.leftCellStyle = createCellContentStyle(workbook,HorizontalAlignment.LEFT,BorderStyle.THIN);this.rightCellStyle = createCellContentStyle(workbook,HorizontalAlignment.RIGHT,BorderStyle.THIN);Sheet sheet = workbook.getSheetAt(0);row1(sheet,workbook);row2(sheet,workbook);row34(sheet);row5(sheet);}

第一行

  /*** 第一行是标题* @param sheet*/private void row1(Sheet sheet,Workbook workbook){Row row = sheet.createRow(0);row.setHeight((short) (50 * 20));Cell cell = row.createCell(0);cell.setCellValue("商品收发汇总表");cell.setCellStyle(getHeadCellStyle(workbook, this.centerCellStyle));CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 9+this.dynamicHeader.size()*2-1);sheet.addMergedRegionUnsafe(cellRangeAddress);setMergedRegionStyleNoBorder(sheet, cellRangeAddress);}

第二行

/*** 第二行 公司名称、日期* @param sheet*/private void row2(Sheet sheet,Workbook workbook){Row row = sheet.createRow(1);CellStyle subHeaderStyle = createCellContentStyle(workbook, HorizontalAlignment.LEFT,BorderStyle.NONE);// 公司名称Cell cell = row.createCell(0);cell.setCellStyle(subHeaderStyle);cell.setCellValue("公司：{companyName}　　　　　　　　                       日期：{startBillDate}至{endBillDate}");sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 0, 9+this.dynamicHeader.size()*2-1));}

第三行,第四行涉及到动态列的创建和合并表头

 private void row34(Sheet sheet){Row row3 = sheet.createRow(2);Row row4 = sheet.createRow(3);// 商品编码Cell cell = row3.createCell(0);cell.setCellValue("商品编码");cell.setCellStyle(this.centerCellStyle);CellRangeAddress cellRangeAddress = new CellRangeAddress(2, 3, 0, 0);sheet.addMergedRegionUnsafe(cellRangeAddress);setMergedRegionStyle(sheet, cellRangeAddress);// 商品名称cell = row3.createCell(1);cell.setCellValue("商品名称");cell.setCellStyle(this.centerCellStyle);cellRangeAddress = new CellRangeAddress(2, 3, 1, 1);sheet.addMergedRegionUnsafe(cellRangeAddress);setMergedRegionStyle(sheet, cellRangeAddress);// 商品规格cell = row3.createCell(2);cell.setCellValue("商品规格");cell.setCellStyle(this.centerCellStyle);cellRangeAddress = new CellRangeAddress(2, 3, 2, 2);sheet.addMergedRegionUnsafe(cellRangeAddress);setMergedRegionStyle(sheet, cellRangeAddress);//动态列int dySize = this.dynamicHeader.size();if (dySize>0){for (int i=0; i<dySize; i++){Map<String,Object> colMap = this.dynamicHeader.get(i);String busiType = String.valueOf(colMap.get("prop")).replace("busi_", BaseConstant.Separate.NONE);BusinessTypeEnum businessTypeEnum = BusinessTypeEnum.getInvBusinessTypeEnum(busiType);// 第3行——合并表头cell = row3.createCell(3+i*2);cell.setCellValue(businessTypeEnum.display());cell.setCellStyle(this.centerCellStyle);cellRangeAddress = new CellRangeAddress(2, 2, 3+i*2, 4+i*2);sheet.addMergedRegionUnsafe(cellRangeAddress);setMergedRegionStyle(sheet, cellRangeAddress);// 第4行——成本cell = row4.createCell(3+i*2);cell.setCellStyle(this.centerCellStyle);cell.setCellValue("数量");// 第4行——数量cell = row4.createCell(4+i*2);cell.setCellStyle(this.centerCellStyle);cell.setCellValue("成本");}}// 入库合计cell = row3.createCell(3+dySize*2);cell.setCellValue("入库合计");cell.setCellStyle(this.centerCellStyle);cellRangeAddress = new CellRangeAddress(2, 2, 3+dySize*2, 4+dySize*2);sheet.addMergedRegionUnsafe(cellRangeAddress);setMergedRegionStyle(sheet, cellRangeAddress);// 入库合计——成本cell = row4.createCell(3+dySize*2);cell.setCellStyle(this.centerCellStyle);cell.setCellValue("数量");// 入库合计——数量cell = row4.createCell(4+dySize*2);cell.setCellStyle(this.centerCellStyle);cell.setCellValue("成本");// 出库合计cell = row3.createCell(5+dySize*2);cell.setCellValue("出库合计");cell.setCellStyle(this.centerCellStyle);cellRangeAddress = new CellRangeAddress(2, 2, 5+dySize*2, 6+dySize*2);sheet.addMergedRegionUnsafe(cellRangeAddress);setMergedRegionStyle(sheet, cellRangeAddress);// 出库合计——成本cell = row4.createCell(5+dySize*2);cell.setCellStyle(this.centerCellStyle);cell.setCellValue("数量");// 出库合计——数量cell = row4.createCell(6+dySize*2);cell.setCellStyle(this.centerCellStyle);cell.setCellValue("成本");// 结余cell = row3.createCell(7+dySize*2);cell.setCellValue("结余");cell.setCellStyle(this.centerCellStyle);cellRangeAddress = new CellRangeAddress(2, 2, 7+dySize*2, 8+dySize*2);sheet.addMergedRegionUnsafe(cellRangeAddress);setMergedRegionStyle(sheet, cellRangeAddress);// 结余——成本cell = row4.createCell(7+dySize*2);cell.setCellStyle(this.centerCellStyle);cell.setCellValue("数量");// 结余——数量cell = row4.createCell(8+dySize*2);cell.setCellStyle(this.centerCellStyle);cell.setCellValue("成本");}

第五行是数据域

/*** 第五行：数据域* @param sheet*/private void row5(Sheet sheet){Row row = sheet.createRow(4);// 商品编码Cell cell = row.createCell(0);cell.setCellStyle(this.leftCellStyle);cell.setCellValue("{.stockCode}");// 商品名称cell = row.createCell(1);cell.setCellStyle(this.leftCellStyle);cell.setCellValue("{.stockName}");// 商品规格cell = row.createCell(2);cell.setCellStyle(this.leftCellStyle);cell.setCellValue("{.stockModel}");// 动态列int dySize = this.dynamicHeader.size();if (!CheckEmptyUtil.isEmpty(this.dynamicHeader)){for (int i=0; i<dySize; i++){Map<String,Object> colMap = this.dynamicHeader.get(i);List<Map<String,String>> chidren = (List<Map<String,String>>)colMap.get("children");// 数量Map<String,String> countMap = chidren.get(0);cell = row.createCell(3+i*2);cell.setCellStyle(this.rightCellStyle);cell.setCellValue(String.format("{.%s}", countMap.get("prop")));// 成本Map<String,String> costMap = chidren.get(1);cell = row.createCell(4+i*2);cell.setCellStyle(this.rightCellStyle);cell.setCellValue(String.format("{.%s}", costMap.get("prop")));}}// 入库合计cell = row.createCell(3+dySize*2);cell.setCellStyle(this.rightCellStyle);cell.setCellValue("{.count_total_in}");cell = row.createCell(4+dySize*2);cell.setCellStyle(this.rightCellStyle);cell.setCellValue("{.cost_total_in}");// 出库合计cell = row.createCell(5+dySize*2);cell.setCellStyle(this.rightCellStyle);cell.setCellValue("{.count_total_out}");cell = row.createCell(6+dySize*2);cell.setCellStyle(this.rightCellStyle);cell.setCellValue("{.cost_total_out}");// 结余cell = row.createCell(7+dySize*2);cell.setCellStyle(this.rightCellStyle);cell.setCellValue("{.final_count}");cell = row.createCell(8+dySize*2);cell.setCellStyle(this.rightCellStyle);cell.setCellValue("{.final_cost}");}

表格样式这里只写一个,其他的参考pos文档即可,不要每一个单元都重新创建单元格样式,那样非常消耗性能.

 private CellStyle createCellContentStyle(Workbook workbook, HorizontalAlignment align,BorderStyle borderStyle) {CellStyle style = workbook.createCellStyle();// 设置对齐样式style.setAlignment(align);//背景为白色style.setFillForegroundColor(IndexedColors.WHITE.getIndex());// 设置边框样式// 下边框style.setBorderBottom(borderStyle);// 左边框style.setBorderLeft(borderStyle);// 上边框style.setBorderTop(borderStyle);// 右边框style.setBorderRight(borderStyle);// 生成字体Font font = workbook.createFont();font.setFontName("宋体");// 设置字体大小font.setFontHeightInPoints((short) 10);// 粗体显示font.setBold(false);// 选择创建的字体格式style.setFont(font);return style;}