Java实现导出excel对重复数据进行单元格合并

Java 导出excel重复数据动态合并单元

前言：记录下第一次做动态导出excel表格，重复数据合并行。这个难就难在不知道从数据库查出来的数据那些是重复的哪些是不重复的，全部都是动态合并，并不能进行表格写死合并，要根据实际数据来合并。我这个项目是利用poi实现的。注意：我的表格导出功能是有前端配合的，没用swagger和postman导出过。以下就分享纯后端的导出合并单元格逻辑处理，前端自理。

没有合并重复数据之前的excel

合并重复数据之后的excel

实现代码

需要用到的maven依赖

		<!-- excel工具 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
        </dependency>

在实体类需要导出的字段上加上poi里的@Excel，此处我只是挑了两个字段举例

	@ApiModelProperty(value = "规模")
    @Excel(name = "规模")
    private String reservoirScale;

    @ApiModelProperty(value = "地区")
    @Excel(name = "地区")
    private String reservoirRegion;

步骤一：service层的数据处理实现业务，先查询出来需要导出的数据，最后的数据导出来也就是图一的样子，这一步很简单就是controller调service调dao调mapper查出数据库的数据然后返回一个List<对象>集合

//先查询出来需要导出的数据
public List<SchedulingExportVo> exportScheduling(SelectSchedulingBo schedulingBo){
        //校验
        if (schedulingBo != null) {
            //获取需要被导出的数据
            List<SchedulingExportVo> schedulingExportVos = inspectionSchedulingMapper.exportScheduling(schedulingBo);
            //获取字典表数据
            List<SysDictData> data = dictTypeService.selectDictDataByType("reservoir_scale");
            //键值对存入
            Map<String, String> map = data.stream().collect(Collectors.toMap(SysDictData::getDictValue, SysDictData::getDictLabel));
            //遍历
            for (int i = 0; i < schedulingExportVos.size(); i++) {
                schedulingExportVos.get(i).setReservoirScale(map.get(schedulingExportVos.get(i).getReservoirScale()));
            }
            return schedulingExportVos;
        }
        return new ArrayList<>();
    }

步骤二：service层的数据处理实现业务。另外在写一个单元格合并的方法，对查询出来的数据进行和合并单元格操作，注意：exportReceipt方法里面的list，就是步骤一查出来的list集合数据，利用poi加二维数组实现

//把需要导出的数据进行合并单元格的处理  List<SchedulingExportVo> list：步骤一查出来的数据
public XSSFWorkbook exportReceipt(List<SchedulingExportVo> list) {
        // 定义时间格式
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        // 标题
        String[] tableTitle = {"名称", "规模", "地区", "标题", "日期", "责任领导", "排班人员", "巡查人员", "巡查项目", "备注"};
        // 定义表格列数
        Integer colTol = tableTitle.length;
        // 定义行数 数据条数 + 主题(1) + 标题(1) = 2 + 数据条数
        Integer rowTol = (list != null ? list.size() : 0) + 1;
        // 定义 容器装载数据  二维数组
        String[][] receiptTwoArr = new String[rowTol][colTol];
        // 循环行
        for (int i = 0; i < receiptTwoArr.length; i++) {
            // 循环列
            for (int y = 0; y < receiptTwoArr[i].length; y++) {
                // 标题
                if (i == 0) {
                    receiptTwoArr[i][y] = tableTitle[y];
                    continue;
                }
                SchedulingExportVo schedulingExportVo = list.get(i - 1);
                switch (y) {
                    case 0:
                        // 名称
                        receiptTwoArr[i][y] = StrUtil.isNotBlank(schedulingExportVo.getReservoirName()) ? schedulingExportVo.getReservoirName() : "";
                        break;
                    case 1:
                        // 规模
                        receiptTwoArr[i][y] = StrUtil.isNotBlank(schedulingExportVo.getReservoirScale()) ? schedulingExportVo.getReservoirScale() : "";
                        break;
                    case 2:
                        // 地区
                        receiptTwoArr[i][y] = StrUtil.isNotBlank(schedulingExportVo.getReservoirRegion()) ? schedulingExportVo.getReservoirRegion() : "";
                        break;
                    case 3:
                        // 标题
                        receiptTwoArr[i][y] = StrUtil.isNotBlank(schedulingExportVo.getTitle()) ? schedulingExportVo.getTitle() : "";
                        break;
                    case 4:
                        // 开始日期
                        if (DateUtil.format(schedulingExportVo.getStartDate(), "yyyy-MM-dd").equals(DateUtil.format(schedulingExportVo.getEndDate(), "yyyy-MM-dd"))) {
                            receiptTwoArr[i][y] = simpleDateFormat.format(schedulingExportVo.getStartDate());
                        } else {
                            receiptTwoArr[i][y] = simpleDateFormat.format(schedulingExportVo.getStartDate()) + " ~ " + simpleDateFormat.format(schedulingExportVo.getEndDate());
                        }
                        break;
                    case 5:
                        // 领导
                        receiptTwoArr[i][y] = schedulingExportVo.getPersonLiable() != null ? schedulingExportVo.getPersonLiable() : "";
                        break;
                    case 6:
                        // 排班人员
                        receiptTwoArr[i][y] = schedulingExportVo.getSchedulingPerson() != null ? schedulingExportVo.getSchedulingPerson() : "";
                        break;
                    case 7:
                        // 巡查人员
                        receiptTwoArr[i][y] = StrUtil.isNotBlank(schedulingExportVo.getNickName()) ? schedulingExportVo.getNickName() : "";
                        break;
                    case 8:
                        // 巡查项目
                        receiptTwoArr[i][y] = StrUtil.isNotBlank(schedulingExportVo.getInspectPositionNames()) ? schedulingExportVo.getInspectPositionNames() : "";
                        break;
                    case 9:
                        // 备注
                        receiptTwoArr[i][y] = StrUtil.isNotBlank(schedulingExportVo.getRemark()) ? schedulingExportVo.getRemark() : "";
                        break;
                }
            }
        }

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFCellStyle style = wb.createCellStyle();
        XSSFCellStyle style2 = wb.createCellStyle();
        //创建HSSFSheet对象
        XSSFSheet sheet = wb.createSheet("excel表格的标题名字");
        XSSFFont font = wb.createFont();

        //================ 设置表格里面的主题样式 start =============
        //HSSFColor.VIOLET.index //字体颜色
        //font.setColor(new HSSFColor().getIndex());
        //字体大小
        font.setFontHeightInPoints((short) 12);
        //把字体应用到当前的样式
        style.setFont(font);
        style2.setFont(font);
        // 垂直
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style2.setVerticalAlignment(VerticalAlignment.CENTER);
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style2.setAlignment(HorizontalAlignment.LEFT);
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        cellStyle.setFont(headerFont);
        //================ 设置主题样式 end =============

        //创建HSSFRow对象
        XSSFRow xssfRow = null;
        XSSFCell xssfCell = null;
        // 循环行
        for (int i = 0; i < receiptTwoArr.length; i++) {
            xssfRow = sheet.createRow(i);
            // 设置主题
            if (i == 0) {
                //高度
                xssfRow.setHeight((short) 700);
            }
            // 标题
            if (i == 1) {
                //高度
                xssfRow.setHeight((short) 600);
            }
            //高度
            xssfRow.setHeight((short) 500);
            // 循环列
            for (int y = 0; y < receiptTwoArr[i].length; y++) {
                xssfCell = xssfRow.createCell(y);
                xssfCell.setCellValue(receiptTwoArr[i][y]);
                if (i == 0) {
                    if (Arrays.asList(new Integer[]{2, 7, 8}).contains(y)) {
                        sheet.setColumnWidth(y, 34 * 256);
                    } else if (Arrays.asList(new Integer[]{3, 4, 9}).contains(y)) {
                        sheet.setColumnWidth(y, 24 * 256);
                    } else {
                        sheet.setColumnWidth(y, 17 * 256);
                    }
                    xssfCell.setCellStyle(cellStyle);
                    continue;
                }
                if (Arrays.asList(new Integer[]{2, 3, 7, 8, 9}).contains(y)) {
                    // 自适应宽度
                    xssfCell.setCellStyle(style2);
                    continue;
                }
                xssfCell.setCellStyle(style);
            }
        }
        //开始数据合并   合并行
        List<Long> collect = list.stream().map(o -> o.getId()).distinct().collect(Collectors.toList());
        List<Integer> collect1 = collect.stream().map(o -> list.stream().filter(q -> q.getId().longValue() == o.longValue()).collect(Collectors.toList()).size()).collect(Collectors.toList());
        for (int index = 0; index < collect1.size(); index++) {
            int sum = collect1.stream().limit(index).mapToInt(o -> (int) o).sum();
            int count = collect1.get(index) > 1 ? collect1.get(index) : 0;
            if (count == 0) {
                continue;
            }
            sheet.addMergedRegion(new CellRangeAddress(sum + 1, sum + count, 0, 0));
            sheet.addMergedRegion(new CellRangeAddress(sum + 1, sum + count, 1, 1));
            sheet.addMergedRegion(new CellRangeAddress(sum + 1, sum + count, 2, 2));
            sheet.addMergedRegion(new CellRangeAddress(sum + 1, sum + count, 3, 3));
            sheet.addMergedRegion(new CellRangeAddress(sum + 1, sum + count, 4, 4));
            sheet.addMergedRegion(new CellRangeAddress(sum + 1, sum + count, 5, 5));
            sheet.addMergedRegion(new CellRangeAddress(sum + 1, sum + count, 6, 6));
            sheet.addMergedRegion(new CellRangeAddress(sum + 1, sum + count, 9, 9));
        }
        return wb;
    }

步骤三：controller层调用service

	@GetMapping("/export")
    @ApiOperation(value = "列表导出")
    @ResponseBody
    public AjaxResult exportScheduling(SelectSchedulingBo schedulingBo) {
        if (schedulingBo.getDeptId() == null) {
            schedulingBo.setDeptId(SecurityUtils.getLoginUser().getUser().getDeptId());
        }
        OutputStream out = null;
        XSSFWorkbook wb = null;
        try {
            List<SchedulingExportVo> list = inspectionSchedulingExportService.exportScheduling(schedulingBo);
            wb = inspectionSchedulingExportService.exportReceipt(list);
            ExcelUtil<SchedulingExportVo> util = new ExcelUtil<SchedulingExportVo>(SchedulingExportVo.class);
            String filename = util.encodingFilename("巡检排班");
            out = new FileOutputStream(util.getAbsoluteFile(filename));
            wb.write(out);
            return AjaxResult.success(filename);
        } catch (Exception e) {
            e.printStackTrace();
            throw new CustomException("导出Excel失败，请联系网站管理员！");
        } finally {
            if (wb != null) {
                try {
                    wb.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                    }
            }
        }
    }

至于service调dao调mapper去数据库查数据我就不放出来了，这个我想各位既然是都能被安排写导出合并单元的程序员了，只要看上面service的业务实现应该就可以了。

最后配合前端点击导出按钮就能实现单元格合并咯。还在等什么，奥里给起来！！！