1,需求:
因为项目需要所以要按要求合并excel表数据
2,实现方法,java的easyExcel
因为实现合并需继承AbstractMergeStrategy类重写merge方法,在merger中按自己的需要去合并数据
package com.ironge.yangtse.server.handler;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.merge.AbstractMergeStrategy;import com.ironge.yangtse.server.modules.dto.resp.ExportExamPlanResp;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import java.util.ArrayList;import java.util.List;import java.util.Map;public class ExamPlanExportMergeHandler extends AbstractMergeStrategy {//分组 用于记录多少组合并private List<Integer> exportFieldGroupCountList = new ArrayList<>();public ExamPlanExportMergeHandler(List<ExportExamPlanResp> list, Map<Integer,List<ExportExamPlanResp>> groupByResult){Integer firstCol = 1;//如果只合并一行/一列会报错,所以合并数据只有一的时候跳过,不合并if(list != null && list.size() > 0) {for(Integer planIds : groupByResult.keySet()) {//计算每次合并几列exportFieldGroupCountList.add(groupByResult.get(planIds).size());}}else{exportFieldGroupCountList = new ArrayList<Integer>();}}@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {Integer lastRow = 1;for (Integer indexCount : exportFieldGroupCountList) {//计算每次合并几列// int firstRow, int lastRow, int firstCol, int lastCol//CellRangeAddress cellRangeAddress = new CellRangeAddress(0,0,1,lastCol);if(indexCount > 1) {// int firstRow, int lastRow, int firstCol, int lastColCellRangeAddress cellRangeAddress = new CellRangeAddress(lastRow, lastRow + indexCount - 1, 0, 0);CellRangeAddress cellRangeAddress1 = new CellRangeAddress(lastRow, lastRow+indexCount-1, 1, 1);CellRangeAddress cellRangeAddress2 = new CellRangeAddress(lastRow, lastRow+indexCount-1, 10, 10);CellRangeAddress cellRangeAddress3 = new CellRangeAddress(lastRow, lastRow+indexCount-1, 11, 11);CellRangeAddress cellRangeAddress4 = new CellRangeAddress(lastRow, lastRow+indexCount-1, 12, 12);sheet.addMergedRegionUnsafe(cellRangeAddress);sheet.addMergedRegionUnsafe(cellRangeAddress1);sheet.addMergedRegionUnsafe(cellRangeAddress2);sheet.addMergedRegionUnsafe(cellRangeAddress3);sheet.addMergedRegionUnsafe(cellRangeAddress4);lastRow += indexCount;}}}}
@PostMapping("/exportGetList")@ApiOperation("导出计划")public void exportGetList(@RequestBody GetExamPlanReq req,HttpServletResponse response) {req.setCurrent(0);req.setSize(1000000);PageDto<ExamPlanResp> dto = examPlanService.getList(req);List<ExportExamPlanResp> list = dto.getList().stream().map(e->{return examPlanConvert.toExport(e);}).collect(Collectors.toList());Map<Integer,List<ExportExamPlanResp>> groupByResult = list.stream().collect(Collectors.groupingBy(ExportExamPlanResp::getId));String fileName = "考试计划";try (OutputStream out = response.getOutputStream()) {response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName +".xlsx");//这里是重点,这里要实例化上面的处理类,并传递参数,list为要写入的集合,groupBuresult为分组后的写入数据,用于区分多少行合并EasyExcel.write(out, ExportExamPlanResp.class).registerWriteHandler(new ExamPlanExportMergeHandler(list,groupByResult)).sheet("sheet").doWrite(list);} catch (IOException e) {log.error("文件导出异常", e);throw new BusinessException(YangErrorCodeEnum.FILE_DOWNLOAD_FAILED);}}