使用easyexcel导出带合并单元格数据

记使用easyexcel导出带合并单元格数据

最近公司需要，做了一个关于一二级分类的导出，导出过程比较有趣，记一笔！

首先导入easyexcel包，以及导出相关的包，easyexcel可以直接根据实体类导出，但是本次需求没有用到，有兴趣的小伙伴可以去看

https://blog.csdn.net/cxj443914930/article/details/123538144?ops_request_misc=&request_id=&biz_id=102&utm_term=easyexcel&utm_medium=distribute.pc_search_result.none-task-blog-2_allsobaiduweb~default-0-123538144.nonecase&spm=1018.2226.3001.4187

开始准备工作

1、导入Maven依赖

  		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.2</version>
        </dependency>
           <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.15</version>
        </dependency>

2、由于不是单一的处理导出，这里创建了TagProcessStrategyFactory 这里可以忽略，这不是导出关键，主要用于区分处理逻辑处理包装数据

package com.oppo.cc.ap.tags.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

@Service
public class TagProcessStrategyFactory {

    @Value("#{${tag.strategy}}")
    private Map<String, String> typeStrategy;

    @Autowired
    private final Map<String, TagProcessStrategy> strategies = new ConcurrentHashMap<>();

    public TagProcessStrategy getProcessStrategy(String type) {
        if (typeStrategy.containsKey(type)) {
            return strategies.get(typeStrategy.get(type));
        }
        return null;
    }
}

tag.strategy={"graphic":"graphicTagProcessor", "shortvideo":"shortVideoTagProcessor", "smallvideo":"smallVideoTagProcessor"}

@Component("graphicTagProcessor")
@Slf4j
public class GraphicTagProcessor implements TagProcessStrategy {
        @Override
    public TagsDownloadResponse buildDownLoadVo() {
          TagsDownloadResponse response = new TagsDownloadResponse();
          return response;
    }
}

3、这里才是excel的主要处理逻辑

@Slf4j
@Service
public class TagDownLoadService {

public ResponseDTO tagDownLoadExcel(TagsDownloadResponse process, String type) {
String targetFile = TagCode.getDescByTye(type).desc + ".xlsx";
ExcelWriter excelWriter = null;
ByteArrayOutputStream bos = new ByteArrayOutputStream();
try {
	//获取数据
    List<TagCategoryVo> tagCategoryVos = process.getTagCategoryVos();
    // 第一列，一级分类
    List<String> categoryList = tagCategoryVos.stream().map(TagCategoryVo::getTopCategoryTag).distinct().collect(Collectors.toList());
    // EasyExcelFactory.write(bos)可以将流返回，但是也只这一种方法，传入文件名称可以下载到本地看内容
    //EasyExcelFactory.write(targetFile)
    excelWriter = EasyExcelFactory.write(bos)
            // 设置统一的表头、表内容样式
            .registerWriteHandler(style())
            // 设置表头，表内容的行高
            .registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 50, (short) 20)).build();
    WriteSheet sheet1 = EasyExcelFactory.writerSheet(0, "标签体系一二级分类")
            // 指定这个sheet的每个列的宽度
            .registerWriteHandler(new TagCategoryVoColumnWidthStrategy()).registerWriteHandler(new ExcelFillCellMergeStrategy(0, new int[1]))
            // 自定义多级复杂表头
            .head(scoreHead(categoryList, type)).build();
    excelWriter.write(tagCategoryVos, sheet1);
    excelWriter.finish();
} catch (Exception e) {
    log.error("tag Excel download error", e);
    return ResultUtils.getFail(e.getMessage());
} finally {
    if (excelWriter != null) {
        excelWriter.finish();
    }
}
ResponseEntity<byte[]> responseEntity = downloadExcel(targetFile, bos);
return ResultUtils.getSuccess(responseEntity);



   public static HorizontalCellStyleStrategy style() {
        // 头的样式
        WriteCellStyle headStyle = new WriteCellStyle();
        headStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont headFont = new WriteFont();
        headFont.setBold(true);
        headFont.setFontHeightInPoints((short) 16);
        headStyle.setWriteFont(headFont);
        headStyle.setWrapped(true);
        // 内容的样式
        WriteCellStyle contentStyle = new WriteCellStyle();
        contentStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        contentStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentStyle.setWriteFont(contentWriteFont);
        contentStyle.setBorderTop(BorderStyle.THIN);
        contentStyle.setBorderBottom(BorderStyle.THIN);
        contentStyle.setBorderLeft(BorderStyle.THIN);
        contentStyle.setBorderRight(BorderStyle.THIN);
        contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentStyle.setWrapped(true);
        return new HorizontalCellStyleStrategy(headStyle, contentStyle);
    }

    private static List<List<String>> scoreHead(List<String> clazz, String type) {
        List<List<String>> list = Lists.newArrayList();
        List<String> head0 = Lists.newArrayList();
        List<String> head1 = Lists.newArrayList();
        String title = TagCode.getDescByTye(type).desc;
        String clazzInfo = "包含（" + Joiner.on(",").join(clazz) + "）";
        head0.add(title);
        head0.add(clazzInfo);
        head0.add("一级分类");
        head1.add(title);
        head1.add(clazzInfo);
        head1.add("二级分类");
        list.add(head0);
        list.add(head1);
        return list;
    }


    public static ResponseEntity<byte[]> downloadExcel(String fileName, ByteArrayOutputStream byteOutPutStream) {
        //下载文件
        try {
            HttpHeaders headers = new HttpHeaders();
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            // 文件名称
            headers.setContentDispositionFormData("attachment", new String(fileName.getBytes(StandardCharsets.UTF_8), "ISO8859-1"));
            return new ResponseEntity<byte[]>(byteOutPutStream.toByteArray(), headers, HttpStatus.OK);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

指定这个sheet的每个列的宽度

public class TagCategoryVoColumnWidthStrategy extends AbstractHeadColumnWidthStyleStrategy {

    @Override
    protected Integer columnWidth(Head head, Integer columnIndex) {
        switch (columnIndex) {
            case 0:
                // 一级分类
                return 20;
            case 1:
                // 二级分类
                return 10;
            default:
                return 10;
        }
    }
}

单元格合并主要逻辑

/**
 * 单元格合并
 */
@Data
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
   /**
    * 合并字段的下标
    */
   private int[] mergeColumnIndex;
   /**
    * 合并几行
    */
   private int mergeRowIndex;


   public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
      this.mergeRowIndex = mergeRowIndex;
      this.mergeColumnIndex = mergeColumnIndex;
   }

   @Override
   public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                Head head, Integer integer, Integer integer1, Boolean aBoolean) {

   }

   @Override
   public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
                               Head head, Integer integer, Boolean aBoolean) {

   }

   @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 > mergeRowIndex) {
         for (int i = 0; i < mergeColumnIndex.length; i++) {
            if (curColIndex == mergeColumnIndex[i]) {
               mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
               break;
            }
         }
      }
   }

   private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
      //获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
      Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() :
            cell.getNumericCellValue();
      Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
      Object preData = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() :
            preCell.getNumericCellValue();
      // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
      if (curData.equals(preData)) {
         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);
         }
      }
   }
}

总结：

在数据返回处理时处理的不是太好，文件名返回时会乱码，应该在返回实体类中再设置一个字段单独返回文件名称，但是前端同学做了，我就不做修改了，主要是记录一下单元格的合并，很有趣