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

POI实现单元格行合并

• 实现效果
• 引入jar
• 代码实现
• • controller层
  • Service层
  • ServiceImpl层实现类

    实现效果

    

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