目录
说明
本文章基于上个案例来说,链接在上面。
如果改动了这里可以发现,其实对sheet的名字并不影响,所以这是个伪代码,那么怎么去动态设置sheet的名称呢,肯定会有这种需求,网上案例也有,那我就基于我上一篇案例来继续说明,如何动态设置sheet名称。
主要类:
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
/**
* 自定义模板导出sheet拦截器
*/
public class CustomTemplateSheetStrategy implements SheetWriteHandler {
private Integer sheetNo;
private String sheetName;
public CustomTemplateSheetStrategy(String sheetName) {
this.sheetName = sheetName;
}
public CustomTemplateSheetStrategy(Integer sheetNo, String sheetName) {
this.sheetNo = sheetNo;
this.sheetName = sheetName;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
/**
* 功能:动态修改模板中sheet的名称
* sheet创建完成后调用
*
* @param writeWorkbookHolder
* @param writeSheetHolder
*/
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
if (sheetName == null) {
return;
}
if (sheetNo == null) {
sheetNo = 0;
}
//往哪个sheet设置名称
writeWorkbookHolder.getCachedWorkbook().setSheetName(sheetNo, sheetName);
}
}
controller:
@GetMapping("/upload-xls")
public void uploadXls() {
//初始化excel的数据体
List<Map<String, String>> maps = new LinkedList<>();
for (int i = 6; i < 13; i++) {
HashMap<String, String> map = new HashMap<>();
map.put("name", "小" + i);
map.put("age", "" + i);
maps.add(map);
}
ExcelWriter excelWriter;
try {
// 文件名(这里URLEncoder.encode可以防止中文乱码)
String fileName = URLEncoder.encode("四(二)班-数据导出", "UTF-8");
// 设置返回文件信息
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
} catch (Exception e) {
log.info(e.getMessage(), e);
}
//获取模板文件,模板文件需放在 resource下
String templateExcelFileName = "upload.xlsx";
InputStream templateExcelInputStream = this.getClass().getClassLoader().getResourceAsStream(templateExcelFileName);
if (null == templateExcelInputStream) {
log.error("模板文件不存在!");
}
ByteArrayOutputStream outputStream = null;
ByteArrayOutputStream bos = cloneInputStream(templateExcelInputStream);
//动态sheet1 Excel表头
List<String> heads = Arrays.asList("name", "age");
List<List<String>> excelHeaders = heads.stream().map(Collections::singletonList).collect(Collectors.toList());
//动态sheet1 Excel数据
List<List<String>> excelDatas = excelDatas(heads, maps);
try {
InputStream copyInputStream = new ByteArrayInputStream(bos.toByteArray());
outputStream = new ByteArrayOutputStream();
excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(copyInputStream).excelType(ExcelTypeEnum.XLSX).build();
WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
Map<String, Object> map = new HashMap<String, Object>();
map.put("schoolName", "新民小学");
map.put("className", "四(二)班");
map.put("count", maps.size());
map.put("uploadDate", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(System.currentTimeMillis()));
excelWriter.fill(map, writeSheet);
//动态填充表头和内容
WriteSheet attrSheet = EasyExcel.
writerSheet(1)
.registerWriteHandler(new CustomTemplateSheetStrategy(1, "动态命名的学生sheet"))
.build();
attrSheet.setHead(excelHeaders);
excelWriter.write(excelDatas, attrSheet);
WriteSheet attrSheet1 = EasyExcel
.writerSheet(2)
.registerWriteHandler(new CustomTemplateSheetStrategy(2, "大年"))
.build();
attrSheet1.setHead(excelHeaders);
excelWriter.write(excelDatas, attrSheet1);
excelWriter.finish();
outputStream.flush();
outputStream.close();
copyInputStream.close();
} catch (Exception e) {
log.error(e.getMessage(), e);
} finally {
if (null != outputStream) {
try {
outputStream.flush();
outputStream.close();
} catch (IOException e) {
log.error("导出 excel 时关闭 outputStream 出现异常", e);
}
}
}
}
这里可以覆盖之前模板设置的sheet名,也可以动态生成新的sheet
就这样 end
就先说到这
\color{#008B8B}{ 就先说到这}
就先说到这
在下
A
p
o
l
l
o
\color{#008B8B}{在下Apollo}
在下Apollo
一个爱分享
J
a
v
a
、生活的小人物,
\color{#008B8B}{一个爱分享Java、生活的小人物,}
一个爱分享Java、生活的小人物,
咱们来日方长,有缘江湖再见,告辞!
\color{#008B8B}{咱们来日方长,有缘江湖再见,告辞!}
咱们来日方长,有缘江湖再见,告辞!