JAVA 使用POI实现单元格行合并 java 利用poi实现excel行合并

实现效果

如果最后面的三行数据大于1时 那么前面十二行就需要行合并

引入jar

 org.apache.poi
 poi-ooxml
 5.2.2
 

代码实现

controller层

@PostMapping(value = "getExcel")
 public void getExcel(@RequestBody BrucellosisListDTO brucellosisListDTO, HttpServletRequest request, HttpServletResponse response){
 businessTaskBrucellosisService.getExcel(brucellosisListDTO,request,response);
 }

Service层

void getExcel(BrucellosisListDTO brucellosisListDTO, HttpServletRequest request, HttpServletResponse response);

ServiceImpl层实现类

@Override
 public void getExcel(BrucellosisListDTO brucellosisListDTO, HttpServletRequest request, HttpServletResponse response) {
 //数据来源
 List list = queryExcelList(brucellosisListDTO);
 //数据来源 通过参数传入
 String[] masterHead = {"姓名","养殖户类型","手机号码","人口数","所在区域(省)","所在区域(市)","所在区域(区/县)","所在区域(乡镇)","所在区域(乡村)","防疫负责人","养殖总数","布病人数"};
 String[] childHead = {"布病人员","布病人手机号码","布病人身份证号码"};
 int[] widthColumn = new int[]{20,20,25,15,30,30,30,20,30,30,20,15,25,50,50,50};
 //创建Excel工作薄对象
 XSSFWorkbook workbook=new XSSFWorkbook();
 //创建Excel工作表对象
 Sheet sheet = workbook.createSheet("布病人员统计");
 //设置单元格居中
 CellStyle cellStyle = workbook.createCellStyle();
 //设置边框样式
 cellStyle.setAlignment(HorizontalAlignment.CENTER);
 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
 //创建行的单元格，从0开始
 Row row = sheet.createRow(0);
 //创建统计单元格
 Cell masterCell=row.createCell(0);
 //设置第一个表头样式
 CellStyle cellStyleHead = workbook.createCellStyle();
 cellStyleHead.setAlignment(HorizontalAlignment.CENTER);
 cellStyleHead.setVerticalAlignment(VerticalAlignment.CENTER);
 cellStyleHead.setBorderBottom(BorderStyle.THIN);
 cellStyleHead.setBorderLeft(BorderStyle.THIN);
 cellStyleHead.setBorderRight(BorderStyle.THIN);
 cellStyleHead.setBorderTop(BorderStyle.THIN);
 cellStyleHead.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
 cellStyleHead.setFillPattern(FillPatternType.SOLID_FOREGROUND);
 //赋值
 masterCell.setCellValue("养殖户布病人员基本信息统计");
 masterCell.setCellStyle(cellStyleHead);
 //合并列
 CellRangeAddress region = new CellRangeAddress(0, 0, 0, (masterHead.length+childHead.length-1));
 sheet.addMergedRegion(region);
 row.setHeight((short) (3*200));
 // 设置列宽
 for (int i = 0; i 1){ //子表数量大于1才进行 行合并
 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 0, 0);
 sheet.addMergedRegion(region);
 }
 Cell farmerTypeNameCell = valueRow.createCell(1);
 farmerTypeNameCell.setCellStyle(cellStyle);
 farmerTypeNameCell.setCellValue(key.getFarmerTypeName());
 if (chlist.size()>1){ //子表数量大于1才进行 行合并
 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 1, 1);
 sheet.addMergedRegion(region);
 }
 Cell farmerPhoneCell = valueRow.createCell(2);
 farmerPhoneCell.setCellStyle(cellStyle);
 farmerPhoneCell.setCellValue(key.getFarmerPhone());
 if (chlist.size()>1){ //子表数量大于1才进行 行合并
 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 2, 2);
 sheet.addMergedRegion(region);
 }
 Cell populationCell = valueRow.createCell(3);
 populationCell.setCellStyle(cellStyle);
 populationCell.setCellValue(key.getPopulation());
 if (chlist.size()>1){ //子表数量大于1才进行 行合并
 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 3, 3);
 sheet.addMergedRegion(region);
 }
 Cell provinceNameCell = valueRow.createCell(4);
 provinceNameCell.setCellStyle(cellStyle);
 provinceNameCell.setCellValue(key.getProvinceName());
 if (chlist.size()>1){ //子表数量大于1才进行 行合并
 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 4, 4);
 sheet.addMergedRegion(region);
 }
 Cell cityNameCell = valueRow.createCell(5);
 cityNameCell.setCellStyle(cellStyle);
 cityNameCell.setCellValue(key.getCityName());
 if (chlist.size()>1){ //子表数量大于1才进行 行合并
 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 5, 5);
 sheet.addMergedRegion(region);
 }
 Cell areaNameCell = valueRow.createCell(6);
 areaNameCell.setCellStyle(cellStyle);
 areaNameCell.setCellValue(key.getAreaName());
 if (chlist.size()>1){ //子表数量大于1才进行 行合并
 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 6, 6);
 sheet.addMergedRegion(region);
 }
 Cell townNameCell = valueRow.createCell(7);
 townNameCell.setCellStyle(cellStyle);
 townNameCell.setCellValue(key.getTownshipName());
 if (chlist.size()>1){ //子表数量大于1才进行 行合并
 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 7, 7);
 sheet.addMergedRegion(region);
 }
 Cell streetNameCell = valueRow.createCell(8);
 streetNameCell.setCellStyle(cellStyle);
 streetNameCell.setCellValue(key.getStreetName());
 if (chlist.size()>1){ //子表数量大于1才进行 行合并
 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 8, 8);
 sheet.addMergedRegion(region);
 }
 Cell personInChargeNameCell = valueRow.createCell(9);
 personInChargeNameCell.setCellStyle(cellStyle);
 personInChargeNameCell.setCellValue(key.getPersonInChargeName());
 if (chlist.size()>1){ //子表数量大于1才进行 行合并
 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 9, 9);
 sheet.addMergedRegion(region);
 }
 Cell animalNumCell = valueRow.createCell(10);
 animalNumCell.setCellStyle(cellStyle);
 animalNumCell.setCellValue(key.getAnimalNum());
 if (chlist.size()>1){ //子表数量大于1才进行 行合并
 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 10, 10);
 sheet.addMergedRegion(region);
 }
 Cell bruNumCell = valueRow.createCell(11);
 bruNumCell.setCellStyle(cellStyle);
 bruNumCell.setCellValue(key.getBruNum());
 if (chlist.size()>1){ //子表数量大于1才进行 行合并
 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 11, 11);
 sheet.addMergedRegion(region);
 }
 for (int i = 0; i < chlist.size(); i++) {
 BrucellosisExportExcel brucellosisExportExcel = chlist.get(i);
 Row chRow;
 if (i == 0){ //避免重复创建 覆盖主表数据
 chRow = valueRow;
 }else {
 chRow = sheet.createRow(lastRowIndex);
 }
 lastRowIndex++;
 Cell userNameCell = chRow.createCell(12);
 userNameCell.setCellStyle(cellStyle);
 userNameCell.setCellValue(brucellosisExportExcel.getUserName());
 Cell usePhoneCell = chRow.createCell(13);
 usePhoneCell.setCellStyle(cellStyle);
 usePhoneCell.setCellValue(brucellosisExportExcel.getUserPhone());
 Cell idCardCell = chRow.createCell(14);
 idCardCell.setCellStyle(cellStyle);
 idCardCell.setCellValue(brucellosisExportExcel.getIdCard());
 }
 }
 }
 }

备注： 由于我这里要合并的列比较多而且牵扯到每一列都不同，所以使用了一行一行去校验和合并的方法；当然 如果需要合并的就只有一行 可以在 excelPoi的第二个for循环里面使用循环合并的方式。