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加二维数组实现
public XSSFWorkbook exportReceipt(List<SchedulingExportVo> list) {
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
String[] tableTitle = {"名称", "规模", "地区", "标题", "日期", "责任领导", "排班人员", "巡查人员", "巡查项目", "备注"};
Integer colTol = tableTitle.length;
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();
XSSFSheet sheet = wb.createSheet("excel表格的标题名字");
XSSFFont font = wb.createFont();
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);
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的业务实现应该就可以了。
最后配合前端点击导出按钮就能实现单元格合并咯。还在等什么，奥里给起来！！！
|