EasyExcel 动态设置多sheet名称

说明

单个excel多sheet下载案例

本文章基于上个案例来说，链接在上面。

如果改动了这里可以发现，其实对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

$就先说到这$
$在下Apollo$
$一个爱分享Java、生活的小人物，$
$咱们来日方长，有缘江湖再见，告辞！$