关于基于EasyExcel实现cloneSheet

[Mr-ZBH]

• 场景:
  是为了更简便的克隆sheet，翻看代码，发现以下改动，再加一个hanlder就可以支持。 com.alibaba.excel.context.WriteContextImpl#initSheet中的getWorkbook改为getCachedWorkbook即可。

• 为什么这么改？
  如果不用CachedWorkbook，在handler克隆的sheet将被忽略，无法实现克隆后，再调fill入预计的数据
  且虽然我看这两个初始化基本一致，但是使用时，必须要用CachedWorkbook，否则内部实现不支持克隆（没去细看）。

• 克隆监听器：

package com.sunsharing.social.common.excel.easyexcel.support.handler.clone;

import com.alibaba.excel.util.StringUtils;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.WorkbookWriteHandler;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.handler.context.SheetWriteHandlerContext;
import com.alibaba.excel.write.handler.context.WorkbookWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Workbook;

import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

import lombok.extern.log4j.Log4j2;

/**
 * 简单的拷贝Sheet监听器,Sheet1默认下标为0
 * @author zhangbh
 **/
@Log4j2
public class ExcelCloneSheetHandler implements WorkbookWriteHandler, SheetWriteHandler {


    /**
     * 待拷贝的sheet下标
     */
    private final int copySheetNo;
    /**
     * 拷贝次数
     */
    private final int copyTimes;
    /**
     * 创建的新sheet的名称,从0下标开始，如果要跳过，则设置空字符串
     */
    private final List<String> newSheetName;

    /**
     * 拷贝时机
     */
    private CopyWhen copyWhen = CopyWhen.afterWorkbookCreate;

    public ExcelCloneSheetHandler(int copyTimes) {
        this(0, copyTimes, null);
    }

    public ExcelCloneSheetHandler(int copyTimes, List<String> newSheetName) {
        this(0, copyTimes, newSheetName);
    }

    public ExcelCloneSheetHandler(int copySheetIdx, int copyTimes, List<String> newSheetName) {
        this.copySheetNo = copySheetIdx;
        this.copyTimes = copyTimes;
        this.newSheetName = newSheetName;
    }


    public ExcelCloneSheetHandler setCopyWhen(CopyWhen copyWhen) {
        this.copyWhen = copyWhen;
        return this;
    }

    @Override
    public void afterWorkbookCreate(WorkbookWriteHandlerContext context) {
        if (CopyWhen.afterWorkbookCreate.equals(copyWhen)) {
            cloneSheet(context, null);
        }
    }

    @Override
    public void afterWorkbookDispose(WorkbookWriteHandlerContext context) {
        if (CopyWhen.afterWorkbookDispose.equals(copyWhen)) {
            cloneSheet(context, null);
        }
    }


    @Override
    public void beforeSheetCreate(SheetWriteHandlerContext context) {
        WriteSheetHolder holder = context.getWriteSheetHolder();
        if (CopyWhen.beforeSheetCreate.equals(copyWhen) && holder.getSheetNo().equals(copySheetNo)) {
            cloneSheet(null, context);
        }
    }

    @Override
    public void afterSheetCreate(SheetWriteHandlerContext context) {
        WriteSheetHolder holder = context.getWriteSheetHolder();
        if (CopyWhen.afterSheetCreate.equals(copyWhen) && holder.getSheetNo().equals(copySheetNo)) {
            cloneSheet(null, context);
        }
    }


    private void cloneSheet(WorkbookWriteHandlerContext workbookContext, SheetWriteHandlerContext sheetContext) {
        // 需要获取CachedWorkbook否则xlsx格式时，getWorkbook会获取到SXSSFWorkbook（本身不支持克隆操作）
        Workbook workbook = null;
        if (workbookContext != null) {
            workbook = workbookContext.getWriteWorkbookHolder().getCachedWorkbook();
        } else if (sheetContext != null) {
            workbook = sheetContext.getWriteWorkbookHolder().getCachedWorkbook();
        }
        if (workbook == null) {
            log.warn("请注意此周期copyWhen={}获取不到workbook，无法复制", copyWhen);
            return;
        }
        // 原有数量
        for (int i = 0; i < copyTimes; i++) {
            workbook.cloneSheet(copySheetNo);
        }
        if (CollectionUtils.isEmpty(newSheetName)) {
            return;
        }
        AtomicInteger idx = new AtomicInteger(0);
        // 设置新名称
        Workbook finalWorkbook = workbook;
        newSheetName.forEach(name -> {
            // 为空不设置
            if (StringUtils.isBlank(name)) {
                return;
            }
            log.info("sheetNo={},设置新名称={}", idx.get(), name);
            finalWorkbook.setSheetName(idx.getAndIncrement(), name);
        });
    }

    public enum CopyWhen {
        /**
         * 拷贝时机
         */
        afterWorkbookCreate,
        afterWorkbookDispose,
        beforeSheetCreate,
        afterSheetCreate,
        ;
    }
}

[sasuke1199]

源码中创建WorkBook的时候，使用的就是XSSFWorkBook设置到cachedWorkBook中。这个的实现没有禁用clone方法。你默认的inMemory(false)实现的是SXSSFWorkBook。这个实现是禁用了clone方法。这两者其他区别还需要仔细研究一下。不过为了方便模板拷贝，使用XSSFWorkBook是比较方便的。