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

JAVA 使用POI实现单元格行合并生成

news 来源：原创 2024/9/22 10:01:33

预期效果

在这里插入图片描述

maven引用

 <dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.2</version></dependency>

Java代码实现

功能实现

    private boolean callExcel(List<MColumnValueVo> mColumnValueVos,String name){SXSSFWorkbook workbook = new SXSSFWorkbook(100);Sheet sheet = workbook.createSheet("明细信息");//设置单元格居中CellStyle cellStyle = workbook.createCellStyle();//设置边框样式cellStyle.setAlignment(HorizontalAlignment.CENTER);//左右居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中String[] headers1 = {"号码","名称"};//2个String[] mxHeaders = {"行号","行性质"};//2个String[] headers2 = {"合计金额","状态"};//2个Row headerRow = sheet.createRow(0); // 创建表头行，索引为0for (int i = 0; i < headers1.length; i++) {//headers1表格合并并赋值Cell cell = headerRow.createCell(i);//创建列cell.setCellStyle(cellStyle);//设置表格样式cell.setCellValue(headers1[i]);//表格赋值内容sheet.addMergedRegion(new CellRangeAddress(0, 1, i, i));//表格的合并，先填内容在合并且合并保留内容为合并的第一行内容}int firstCol = headers1.length+mxHeaders.length; // 从第一行开始填充数据int hlt = firstCol+headers2.length;// 合计列// 自动调整列宽（可选）for (int colNum = 0; colNum < hlt; colNum++) {sheet.setColumnWidth(colNum, 256 * 25);}//发票明细表格合并并赋值Row two = sheet.getRow(1);if (two == null) {two = sheet.createRow(1);}sheet.addMergedRegion(new CellRangeAddress(0, 0, headers1.length, firstCol-1));Cell mergedCell = headerRow.createCell(headers1.length); // 合并后的单元格左上角mergedCell.setCellStyle(cellStyle);mergedCell.setCellValue("明细");for (int i = 0; i < mxHeaders.length; i++) {//明细表头赋值Cell cell = two.createCell(i + headers1.length);cell.setCellStyle(cellStyle);cell.setCellValue(mxHeaders[i]);}for (int i = 0; i < headers2.length; i++) {Cell cell = headerRow.createCell(i + firstCol);cell.setCellStyle(cellStyle);cell.setCellValue(headers2[i]);sheet.addMergedRegion(new CellRangeAddress(0, 1, firstCol+i, firstCol+i));}// 填充数据int lastRowIndex = 2; //记录最后行位置for (MColumnValueVo vo : mColumnValueVos) {// 创建行Row row = sheet.createRow(lastRowIndex);if(vo.getMxQueryOuts()!=null){//添加发票明细for (int i = 0; i < vo.getMxQueryOuts().size(); i++) {MMColumnValueVo mxVo = vo.getMxQueryOuts().get(i);Row row1 = sheet.getRow(lastRowIndex + i);if(row1 == null) {row1 = sheet.createRow(lastRowIndex + i);}for (int j = 0; j < headers2.length; j++) {Cell cell = row1.createCell(headers1.length + j);cell.setCellStyle(cellStyle);cell.setCellValue(getValueFromMMVo(mxVo, j));}}}for (int i = 0; i < headers1.length; i++) {Cell cell = row.createCell(i);cell.setCellStyle(cellStyle);cell.setCellValue(getValueFromMxVo1(vo, i));if(vo.getMxQueryOuts()!=null && vo.getMxQueryOuts().size()>1){sheet.addMergedRegion(new CellRangeAddress(lastRowIndex, lastRowIndex+vo.getMxQueryOuts().size()-1, i, i));}}for (int i = 0; i < headers2.length; i++) {Cell cell = row.createCell(i + firstCol);cell.setCellStyle(cellStyle);cell.setCellValue(getValueFromMxVo2(vo, i));if(vo.getMxQueryOuts()!=null && vo.getMxQueryOuts().size()>1){sheet.addMergedRegion(new CellRangeAddress(lastRowIndex, lastRowIndex+vo.getMxQueryOuts().size()-1, firstCol+i, firstCol+i));}}if(vo.getMxQueryOuts()==null){lastRowIndex++;}else {lastRowIndex += vo.getMxQueryOuts().size();}logger.info("lastRowIndex：{}",lastRowIndex);}// 写入到文件 name:绝对路径并带excel文件名称及后缀try (FileOutputStream out = new FileOutputStream(name)) {workbook.write(out);} catch (IOException e) {logger.error("生成明细信息excel表格异常",e);return false;} finally {// 关闭workbook，释放资源workbook.dispose();}return true;}

返回内容

private String getValueFromMxVo1(MMColumnValueVo vo, int colNumMx) {switch (colNumMx) {case 0: return vo.getLineNo() != null ? vo.getLineNo().toString() : "";case 1: return vo.getLineNature();default: return "";}
}