Easy Excel 导出数据合并列

使用步骤：

• 导入maven依赖

  	<!-- easy excel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>${easy-excel.version}</version>
        </dependency>
        我这使用的是3.1.5版本
  

• 对应实体bean对象

  @Data
  @ContentRowHeight(18)
  @HeadRowHeight(25)
  @ColumnWidth(20)
  public class InspectExport implements Serializable {
  
      private static final long serialVersionUID = -3032057591998811851L;
  
      @ExcelProperty(index = 0, value = "xx单号")
      private String inspectNo;
      @ExcelProperty(index = 1, value = "状态")
      private String inspectStatusValue;
      @ExcelProperty(index = 2, value = "xx品名")
      private String inspectProduceKind;
      @ExcelProperty(index = 3, value = "xx号")
      private String inspectZjEquipmentNo;
      @ExcelProperty(index = 4, value = "xx米数")
      private BigDecimal outMeters;
      @ExcelProperty(index = 5, value = "xx米数")
      private BigDecimal actualMeters;
      @ExcelProperty(index = 6, value = "xx机号")
      private String inspectEquipmentNo;
      @ExcelProperty(index = 7, value = "xx")
      private String inspectUserName;
      @ExcelProperty(index = 8, value = "xx工")
      private String userName;
      @ExcelProperty(index = 9, value = "时间")
      @DateTimeFormat(value = "yyyy-MM-dd HH:mm:ss")
      @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
      private Date inspectEndTime;
      @ExcelProperty(index = 10, value = "xx")
      private String inspectWeftDensity;
      @ExcelProperty(index = 11, value = "xx")
      private String inspectWeftArrangement;
      @ExcelProperty(index = 12, value = "xx")
      private String inspectHorseLength;
      @ExcelProperty(index = 13, value = "xx")
      private String inspectFlawTotalCount;
      @ExcelProperty(index = 14, value = "xx")
      private String inspectStart;
      @ExcelProperty(index = 15, value = "xx")
      private String inspectDown;
  

• 编写controller和相关service 方法

  /**
   * 导出检验列表
   */
  @ApiOperation(value = "导出检验列表")
  @GetMapping("/export")
  public APIResult<AjaxResult> export(Inspect inspect) {
      return success(inspectService.exportInspect(inspect));
  }
  

    /**
   * 导出
   *
   * @param inspect  xxx对象
   */
  @Override
  public AjaxResult exportInspect(Inspect inspect) {
      inspect.setInspectOrgId(SecurityUtils.getLoginUserOrgId());
      List<InspectExport> inspectList = baseMapper.selectHllInspectListForExport(inspect);
  
      WriteCellStyle headWriteCellStyle = new WriteCellStyle();
      // 设置背景颜色
      headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
      // 设置头字体
      WriteFont headWriteFont = new WriteFont();
      headWriteFont.setFontHeightInPoints((short) 13);
      headWriteFont.setBold(true);
      headWriteCellStyle.setWriteFont(headWriteFont);
      // 设置头居中
      headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
      // 内容策略
      WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
      // 设置 水平居中
      contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
      // 设置 垂直居中
      contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
  
      HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
  
      try {
          // 设置文件名
          String fileName = UUID.randomUUID().toString() + "_" + "xxx记录" + ".xlsx";
          String downloadPath = HuawenConfig.getDownloadPath() + fileName;
          File desc = new File(downloadPath);
          if (!desc.getParentFile().exists()) {
              desc.getParentFile().mkdirs();
          }
          // 获取写出流
          ExcelWriter excelWriter = EasyExcel.write(new FileOutputStream(downloadPath), InspectExport.class).build();
  
          ExcelWriterSheetBuilder builder = EasyExcel.writerSheet("xxx记录")
                  .registerWriteHandler(horizontalCellStyleStrategy)
                  // 自定义合并列的策略
                  .registerWriteHandler(new ExcelFillCellMergeStrategyHandler(1, new int[]{0, 1, 2, 3, 6, 7, 9, 10, 11, 12, 13, 14, 15}));
  
          // 创建sheet
          WriteSheet writeSheet = builder.build();
  
          // 写出去
          excelWriter.write(inspectList, writeSheet);
          excelWriter.finish();
  
          return AjaxResult.success(fileName);
      } catch (Exception e) {
          log.error("inspect export error--->{}", e.getMessage());
          return AjaxResult.error("xxx记录导出出错:" + e.getMessage());
      }
  
  }
  

• 自定义列处理的hanlder 该方法循环太多，数据渲染较慢

  @Data
  public class ExcelFillCellMergeStrategyHandler implements CellWriteHandler {
      /**
       * 存放这一行需要合并哪些列
       * example: [0,1,2]
       * 那就是合并 0,1,2列
       */
      private int[] needMergeColumnIndex;
  
      /**
       * 需要向上合并的列
       */
      private int needMergeRowIndex;
  
      /**
       * 忽略合并的列
       */
      private int ignoreMergeRowIndex;
  
  
      public ExcelFillCellMergeStrategyHandler() {
      }
  
      public ExcelFillCellMergeStrategyHandler(int needMergeRowIndex, int[] needMergeColumnIndex) {
          this.needMergeRowIndex = needMergeRowIndex;
          this.needMergeColumnIndex = needMergeColumnIndex;
      }
  
      public ExcelFillCellMergeStrategyHandler(int needMergeRowIndex, int[] needMergeColumnIndex, int ignoreMergeRowIndex) {
          this.needMergeRowIndex = needMergeRowIndex;
          this.needMergeColumnIndex = needMergeColumnIndex;
          this.ignoreMergeRowIndex = ignoreMergeRowIndex;
      }
  
  
      @Override
      public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
          int curRowIndex = cell.getRowIndex();
          int curColIndex = cell.getColumnIndex();
          if (curRowIndex > needMergeRowIndex) {
              for (int columnIndex : needMergeColumnIndex) {
                  if (curColIndex == columnIndex) {
                      mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                      break;
                  }
              }
          }
      }
  
      /**
       * 当前单元格向上合并
       *
       * @param writeSheetHolder writeSheetHolder
       * @param cell             当前单元格
       * @param curRowIndex      当前行
       * @param curColIndex      当前列
       */
      private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
          Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
          Row preRow = cell.getSheet().getRow(curRowIndex - 1);
          if (preRow == null) {
              // 当获取不到上一行数据时，使用缓存sheet中数据
              preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);
          }
          Cell preCell = preRow.getCell(curColIndex);
          Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
          // 不需要合并的列直接跳出
          if (ignoreMergeRowIndex == curRowIndex - 1) {
              return;
          }
          // 将当前单元格数据与上一个单元格数据比较
          boolean dataBool = preData.equals(curData);
  
          // 此处需要注意：这里是获取每一行第一列数据和上一行第一列数据进行比较，如果相等合并，所以第一列必须放可以用来合并的关键字段
          boolean equals = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue());
          if (dataBool && equals) {
              Sheet sheet = writeSheetHolder.getSheet();
              List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
              boolean isMerged = false;
              for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                  CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                  // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                  if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                      sheet.removeMergedRegion(i);
                      cellRangeAddr.setLastRow(curRowIndex);
                      sheet.addMergedRegion(cellRangeAddr);
                      isMerged = true;
                  }
              }
              // 若上一个单元格未被合并，则新增合并单元
              if (!isMerged) {
                  CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                  sheet.addMergedRegion(cellRangeAddress);
              }
          }
      }
  
  }
  
  

• 自定义处理列合并的handler 推荐使用该方法，主要是重写AbstractMergeStrategy的merge方法

  
  package com.huawen.inspect.handler;
  
  import cn.hutool.core.collection.CollUtil;
  import com.alibaba.excel.metadata.Head;
  import com.alibaba.excel.write.merge.AbstractMergeStrategy;
  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;
  
  /**
   * @author:xjl
   * @date:2023/3/24 8:39
   * @Description: 列合并策略处理 【速度快一点 主要是重写AbstractMergeStrategy的merge方法】
   **/
  public class ColumnMergeStrategyHandler extends AbstractMergeStrategy {
  
      /**
       * 需要合并的数量集合
       */
      private final List<Integer> needMergeCountList;
  
      /**
       * 需要合并的列的下标集合
       */
      private final List<Integer> needMergeColumnIndexList;
  
      /**
       * 需要开始合并单元格的首行index
       */
      private Integer rowIndex;
  
      /**
       * 构参
       *
       * @param dataList                 用来区分是否要合并的数据集合
       * @param needMergeColumnIndexList 哪些格子要合并
       */
      public ColumnMergeStrategyHandler(List<String> dataList, List<Integer> needMergeColumnIndexList) {
          this.needMergeCountList = getNeedMergeCountList(dataList);
          this.needMergeColumnIndexList = needMergeColumnIndexList;
      }
  
      /**
       * 重写merge方法
       *
       * @param sheet            工作簿对象
       * @param cell             cell对象
       * @param head             head对象
       * @param relativeRowIndex 原始的行下标
       */
      @Override
      protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
          if (null == rowIndex) {
              rowIndex = cell.getRowIndex();
          }
          needMergeColumnIndexList.forEach(targetColumnIndex -> {
              // 仅从首行以及目标列的单元格开始合并，忽略其他
              if (cell.getRowIndex() == rowIndex && cell.getColumnIndex() == targetColumnIndex) {
                  mergeColumn(sheet, targetColumnIndex);
              }
          });
      }
  
      /**
       * 合并列
       *
       * @param sheet             sheet对象
       * @param targetColumnIndex 需要合并的格子
       */
      private void mergeColumn(Sheet sheet, Integer targetColumnIndex) {
          int rowCount = rowIndex;
  
          for (Integer count : needMergeCountList) {
              if (count == 1) {
                  rowCount += count;
                  continue;
              }
              // 合并单元格 使用poi的API
              CellRangeAddress cellRangeAddress = new CellRangeAddress(rowCount, rowCount + count - 1, targetColumnIndex, targetColumnIndex);
              sheet.addMergedRegionUnsafe(cellRangeAddress);
              rowCount += count;
          }
  
      }
  
      /**
       * 该方法将目标列根据值是否相同连续可合并，存储可合并的行数
       *
       * @param dataList 源数据
       * @return 需要合并的行数
       */
      private List<Integer> getNeedMergeCountList(List<String> dataList) {
          if (CollUtil.isEmpty(dataList)) {
              return new ArrayList<>();
          }
  
          // 存储需要合并的行数的集合
          List<Integer> needMergeCountList = new ArrayList<>();
          int count = 1;
  
          for (int i = 1; i < dataList.size(); i++) {
              if (dataList.get(i).equals(dataList.get(i - 1))) {
                  count++;
              } else {
                  needMergeCountList.add(count);
                  count = 1;
              }
          }
          // 加入list
          needMergeCountList.add(count);
  
          return needMergeCountList;
      }
  
  }