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

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

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

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

合并重复数据之后的excel

实现代码

需要用到的maven依赖

<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 = "列表导出")@ResponseBodypublic 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();}}}}