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

文章JAVA 使用POI实现单元格行合并分享给大家，欢迎收藏Java资料网，专注分享技术知识

实现效果

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

引入jar

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

代码实现

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<BrucellosisExportExcel> 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 <widthColumn.length; i++) {
            sheet.setColumnWidth((short) i, (short) widthColumn[i] * 200);
        }
        //表头 从1开始 设置第二个表头样式
        Row titleRow = sheet.createRow(1);
        //主表头
        for (int i = 0; i < masterHead.length ; i++) {
            Cell msCell = titleRow.createCell(i);
            msCell.setCellStyle(cellStyleHead);
            msCell.setCellValue(masterHead[i]);
        }
        //子表头
        for (int i = 0; i < childHead.length; i++) {
            Cell chcell = titleRow.createCell(masterHead.length+i);
            chcell.setCellStyle(cellStyleHead);
            chcell.setCellValue(childHead[i]);
        }
        //封装数据
        excelPoi(list,sheet,cellStyle,region);
        String fileName = null;
        try {
            fileName = URLEncoder.encode("患布病人员统计","UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
            response.setHeader("filename", fileName + ".xlsx");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
            OutputStream os = response.getOutputStream();
            workbook.write(os);
            os.flush();
            os.close();
            workbook.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    
    //数据封装以及塞值方法
  public void excelPoi(List<BrucellosisExportExcel> list, Sheet sheet,CellStyle cellStyle, CellRangeAddress region){

        //封装父表和子表对象
        Map<String, List<BrucellosisExportExcel>> farmerPhoneMap = list.stream().collect(Collectors.groupingBy(BrucellosisExportExcel::getFarmerPhone));
        Set<String> strings = farmerPhoneMap.keySet();
        List<String> farmerPhoneList = new ArrayList<>(strings);
        List<Map<BrucellosisExportExcel,List<BrucellosisExportExcel>>> masterList = new ArrayList<>();
        Map<BrucellosisExportExcel,List<BrucellosisExportExcel>> map = new HashMap();
        for (int i = 0; i < farmerPhoneList.size(); i++) {
            List<BrucellosisExportExcel> brucellosisExportExcels = farmerPhoneMap.get(farmerPhoneList.get(i));
            //养殖户基本信息
            BrucellosisExportExcel brucellosisExportExcel = brucellosisExportExcels.get(0);
            map.put(brucellosisExportExcel,brucellosisExportExcels);
        }
        masterList.add(map);

        //填充数据
        int lastRowIndex = 2; //记录最后行位置
        for (Map<BrucellosisExportExcel,List<BrucellosisExportExcel>> m : masterList){
            for (BrucellosisExportExcel key : m.keySet()){
                List<BrucellosisExportExcel> chlist = m.get(key);
                Row valueRow = sheet.createRow(lastRowIndex);
                Cell farmerNameCell = valueRow.createCell(0);
                farmerNameCell.setCellStyle(cellStyle);
                farmerNameCell.setCellValue(key.getFarmerName());
                if (chlist.size()>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循环里面使用循环合并的方式。

文章来源:https://blog.csdn.net/qq_43419105/article/details/137018202
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：jacktools123@163.com进行投诉反馈，一经查实，立即删除！