开发环境
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();//遍历已经封装好要写入的模板的数据集合，一般一个对象写一个sheetfor(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);//模板sheetfor (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();}
}
)
