Open Mr-ZBH opened 2 years ago
场景: 是为了更简便的克隆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;
/**
@author zhangbh **/ @Log4j2 public class ExcelCloneSheetHandler implements WorkbookWriteHandler, SheetWriteHandler {
public ExcelCloneSheetHandler(int copyTimes) { this(0, copyTimes, null); }
public ExcelCloneSheetHandler(int copyTimes, List newSheetName) { this(0, copyTimes, newSheetName); }
public ExcelCloneSheetHandler(int copySheetIdx, int copyTimes, List 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 { /**
源码中创建WorkBook的时候,使用的就是XSSFWorkBook设置到cachedWorkBook中。这个的实现没有禁用clone方法。你默认的inMemory(false)实现的是SXSSFWorkBook。这个实现是禁用了clone方法。这两者其他区别还需要仔细研究一下。不过为了方便模板拷贝,使用XSSFWorkBook是比较方便的。
场景:
是为了更简便的克隆sheet,翻看代码,发现以下改动,再加一个hanlder就可以支持。 com.alibaba.excel.context.WriteContextImpl#initSheet中的getWorkbook改为getCachedWorkbook即可。
为什么这么改?
如果不用CachedWorkbook,在handler克隆的sheet将被忽略,无法实现克隆后,再调fill入预计的数据
且虽然我看这两个初始化基本一致,但是使用时,必须要用CachedWorkbook,否则内部实现不支持克隆(没去细看)。
克隆监听器:
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;
/**
@author zhangbh **/ @Log4j2 public class ExcelCloneSheetHandler implements WorkbookWriteHandler, SheetWriteHandler {
/**
/**
public ExcelCloneSheetHandler(int copyTimes) { this(0, copyTimes, null); }
public ExcelCloneSheetHandler(int copyTimes, List newSheetName) {
this(0, copyTimes, newSheetName);
}
public ExcelCloneSheetHandler(int copySheetIdx, int copyTimes, List 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 { /**