SpringBoot集成EasyExcel实现模板写入多个sheet导出

开发环境

SpringBoot2.6+EasyExcel3.2.1

//第一种输出到指定目录
public static void main(String[] args) throws FileNotFoundException {
    InputStream inputStream = new FileInputStream(new File("模板位置"));
    InputStream inputStream1 = cloneSheet(inputStream, 0, "sheet11", "sheet12", "sheet13");
    //TODO 省略写入业务数据
    StreamUtil.inputStreamToFile(inputStream1, new File("导出数据指定输出位置"));
}

//第二种返回文件流，可用于上传文件资源服务器
public ByteArrayOutputStream export（）{
@Cleanup ByteArrayOutputStream os = new ByteArrayOutputStream();
 List<String> stringList = Lists.newArrayList();
 stringList.add("1");
 stringList.add("2");
//读取resource文件目录下的模板，打成jar包也能读取到
      InputStream resourceAsStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("模板名称.xlsx");
        InputStream inputStream = cloneSheet(resourceAsStream, 0, stringList)；
        ExcelWriter excelWriter = null;
        try{
         excelWriter = EasyExcel.write(os).withTemplate(inputStream).build();
         //遍历已经封装好要写入的模板的数据集合，一般一个对象写一个sheet
         for(int i =0;i<10;i++){
         excelWriter.fill(dtoList.get(i),writeSheet);
         }
        }finally{
         if(null != excelWriter){
                excelWriter.finish();
            }
        }
        return os;
}

/**
 * 克隆sheet
 * 注意传入的文件流在执行本方法后将关闭
 *
 * @param excelIns      excel文件流
 * @param tplSheetIndex 需要克隆的模板索引
 * @param newSheetNames 所需要生成的excel最终的sheet名
 * @return 新的InputStream
 */
@SneakyThrows
public static InputStream cloneSheet(InputStream excelIns, int tplSheetIndex, String... newSheetNames) {
//        Workbook workbook = isXlsx(excelIns) ? new XSSFWorkbook(excelIns) : new HSSFWorkbook(excelIns);
    Workbook workbook = new XSSFWorkbook(excelIns);
    Sheet tplSheet = workbook.getSheetAt(tplSheetIndex);//模板sheet
    for (int i = 0; i < newSheetNames.length; i++) {
        String sheetName = newSheetNames[i];
        if (0 == i) {//第一个,直接改名即可
            workbook.setSheetName(0, sheetName);
        } else {
            Sheet newSheet = workbook.cloneSheet(0);
            //同时复制打印设置
            PrintSetup tplPrintSetup = tplSheet.getPrintSetup();
            PrintSetup newPrintSetup = newSheet.getPrintSetup();
            newPrintSetup.setLandscape(tplPrintSetup.getLandscape());//打印方向，true：横向，false：纵向(默认)
            newPrintSetup.setPaperSize(tplPrintSetup.getPaperSize());//纸张类型

            int sheetIndex = workbook.getSheetIndex(newSheet);
            workbook.setSheetName(sheetIndex, sheetName);
        }
    }
    try (ByteArrayOutputStream bos = new ByteArrayOutputStream()) {
        workbook.write(bos);
        byte[] bytes = bos.toByteArray();
        InputStream ins = new ByteArrayInputStream(bytes);
        return ins;
    } finally {
        if (null != excelIns) excelIns.close();
    }
}

原文地址：https://blog.csdn.net/LynneZoe/article/details/140518240

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！