赞
踩
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(); } }
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。