问题描述:
通过EasyExcel读取模板生成多sheet页Excel的过程中出现报错
Sheet index (2) is out of range (0..1)
EasyExcel依赖版本:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>
在git issue上看到其他低版本依然存在该问题。
使用模板填充的方式进行多sheet填错,报错out of range · Issue #2731 · alibaba/easyexcel · GitHub
解决方案:通过POI动态复制sheet
代码如下:
一、EasyExcel读取模板下载的主要逻辑:
/**
* 模板下载
*
* @param listId 查询业务数据id
*/
public void download(String listId) {
String fileName = "模板.xlsx";
String downloadPath = EasyExcelUtil.getExcelWritePath(UUID.randomUUID().toString());
String writePath = downloadPath + fileName;
//这一步是查询业务数据
List<CensusListForm> listFormList = censusListFormService.queryByListId(listId);
if (CollectionUtils.isEmpty(listFormList)) {
throw new BusinessException("业务数据为空,无法下载");
}
//将业务数据封装成自己定义的MultiSheet类
List<MultiSheet> sheetList = this.handleSheet(listFormList);
if (CollectionUtils.isNotEmpty(sheetList)) {
ExcelWriter excelWriter = null;
try {
InputStream stream = new ClassPathResource(missionFillTemplate).getInputStream();
int capacity = sheetList.size() - EasyExcelUtil.DEFAULT_SHEET_NUM;
if (capacity > 0) {
//扩容
stream = EasyExcelUtil.templateSheetResize(stream, capacity);
}
excelWriter = EasyExcelFactory.write(writePath)
.withTemplate(stream)
.excelType(ExcelTypeEnum.XLSX)
.registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
.build();
ExcelWriter finalExcelWriter = excelWriter;
//如果模板中只有一个sheet页,这里循环就会报错
sheetList.forEach(e -> {
WriteSheet writeSheet = EasyExcelFactory.writerSheet(e.getOrder())
//动态sheet页名覆盖
.registerWriteHandler(new CustomSheetNameHandler(e.getOrder(), e.getSheetName()))
.head(e.getHead())
//设置导出文档样式
.registerWriteHandler(EasyExcelUtil.getStyleStrategy())
.build();
finalExcelWriter.write(e.getData(), writeSheet);
});
} catch (IOException e) {
log.error("读取模板流报错:{}", e.toString());
throw new BusinessException("读取模板流报错:", e);
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
}
}
EasyExcelUtil.download(writePath, fileName);
}
二、MultiSheet类的元素
/**
* sheet页名
*/
private String sheetName;
/**
* sheet页排序 从0开始
*/
private Integer order;
/**
* excel表头
*/
private List<List<String>> head;
/**
* 数据集合
*/
private List<List<String>> data;
三、通过POI复制sheet页扩容模板
/**
* 通过POI方式复制sheet页。
* EasyExel(截止到3.3.2版本)通过模板动态添加sheet页时存在bug:
* 例如:模板中只有1个sheet页,那么动态添加第2个sheet页或报错Sheet index (1) is out of range (0..0)
* 【注】默认所有的模板sheet页有3页。只有超过3个sheet才用该方法
*
* @param stream excel模板流
* @param capacity 额外扩容的大小
* @return 扩容后的模板流
*/
public static InputStream templateSheetResize(InputStream stream, Integer capacity) {
if (capacity <= 0) {
return stream;
}
try {
ByteArrayOutputStream bos = new ByteArrayOutputStream();
//通过poi复制出需要的sheet个数的模板
XSSFWorkbook workbook = new XSSFWorkbook(stream);
//设置模板的第一个sheet的名称
for (int i = 1; i <= capacity; i++) {
//复制模板,得到第3+capacity个sheet
//定义的模板DEFAULT_SHEET_NUM = 3
int num = DEFAULT_SHEET_NUM + i;
workbook.cloneSheet(DEFAULT_SHEET_NUM - 1, "Sheet" + num);
}
//写到流里
workbook.write(bos);
byte[] bArray = bos.toByteArray();
return new ByteArrayInputStream(bArray);
} catch (Exception e) {
log.error("模板扩充sheet页失败:{}", e.getMessage());
e.printStackTrace();
}
return stream;
}