需求：第一个sheet是总的列表，其他sheet以英文名称命名是第一个sheet的具体信息，并且点击第一个sheet英文名称，跳转到对应的sheet。
步骤：
（1）首先定义模板（模板放在springboot的resources下的template文件夹中），第一个sheet定义好标题和样式，列表内容填写对应字段，第二个sheet按照格式内容定义好样式和显示字段；
（2）根据id查出所有的列表信息，然后将英文名称取出来，处理一下长度和大小写（excel的sheet命名最长为31，大小写不敏感）；
（3）使用流方式加载模板，根据第一个sheet的列表长度分别按照第二个sheet克隆同等数量的sheet，并根据英文名称命名；
（4）使用easyExcel写入第一个sheet内容，定义拦截器设置英文名称的超链接；
（5）查询出所有表字段的详细信息列表，转换成map（主键为key，value为list），然后循环写入其他sheet中。
引入依赖：
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.1.0</version> </dependency>
代码示例：
@RequestMapping(value = "/exportFillExcel", method = RequestMethod.POST) public void exportFillExcel(HttpServletResponse response,@RequestBody TyTableInfoMVO tyTableInfoMVO)
throws SysException, IOException, AppException { String[] tableIds = tyTableInfoMVO.getAllTableIds().split(","); tyTableInfoMVO.setTableIds(tableIds); List<TyTableInfoExcel> tableList = tyTableInfoService.exportExcel(tyTableInfoMVO); List<String> linkNames = tableList.stream() .map(table -> table.getTableName().length()>28?table.getTableName().substring(0,28):table.getTableName()) .map(String::toLowerCase) .collect(Collectors.toList());
//File file = ResourceUtils.getFile("classpath:template/tyTemplate.xlsx"); //使用这种方式打包找不到文件 ClassPathResource classPathResource = new ClassPathResource("template/tyTemplate.xlsx"); InputStream inputStream = classPathResource.getInputStream(); response.setContentType("application/vnd.ms-excel;charset=utf-8"); response.setCharacterEncoding("utf-8"); String fileName = URLEncoder.encode("数据字典", "UTF-8"); response.setHeader("Content-disposition", "attachment;filename="+ fileName + ".xlsx"); ExcelWriter excelWriter = null; try(ByteArrayOutputStream bos = new ByteArrayOutputStream()){ //原模板只有一个sheet，通过poi复制出需要的sheet个数的模板 XSSFWorkbook workbook = new XSSFWorkbook(inputStream); //设置模板的第一个sheet的名称 workbook.setSheetName(0, "目录"); workbook.setSheetName(1, linkNames.get(0)); //循环表名 Map<String,Integer> map = new HashMap<>(); map.put(linkNames.get(0),1); for (int i = 1; i < linkNames.size(); i++) { if(map.containsKey(linkNames.get(i))){ map.put(linkNames.get(i),map.get(linkNames.get(i))+1); workbook.cloneSheet(1, linkNames.get(i)+"_"+map.get(linkNames.get(i))); }else{ map.put(linkNames.get(i),1); workbook.cloneSheet(1, linkNames.get(i)); } } //写到流里 workbook.write(bos); byte[] bArray = bos.toByteArray(); InputStream is = new ByteArrayInputStream(bArray); excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(is).build(); Map<String,Integer> linkMap = new HashMap<>(); WriteSheet writeSheet = EasyExcel.writerSheet("目录") .registerWriteHandler(new LinkStyleHandler(linkMap)) .build(); excelWriter.fill(tableList,writeSheet); //查询所有表 List<TableFieldInfoExcel> fieldList = new ArrayList<TableFieldInfoExcel>(); TyTableInfoMVO tyTable = new TyTableInfoMVO(); tyTable.setTableIds(tableIds); fieldList = tyFieldInfoService.exportFieldList(tyTable); Map<String, List<TableFieldInfoExcel>> collect = fieldList.stream()
.collect(Collectors.groupingBy(TableFieldInfoExcel::getTyTableInfoId)); //设置表 for(int i=0;i<tableList.size();i++){ WriteSheet fieldSheet = EasyExcel.writerSheet(i+1).build(); List<TableFieldInfoExcel> field = collect.get(tableList.get(i).getTyTableInfoId()); //自定义表头 List<Map<String,String>> headList = new ArrayList<>(); Map<String,String> headMap = new HashMap<>(); headMap.put("descContent",tableList.get(i).getContentDesc()); headMap.put("nameContent",tableList.get(i).getTableDesc()); headMap.put("enNameContent",tableList.get(i).getTableName()); headMap.put("keyContent",tableList.get(i).getUniqueindex()); headMap.put("uniqueContent",tableList.get(i).getUniqueindex()); headMap.put("catalogContent",tableList.get(i).getTableCatalog()); headList.add(headMap); excelWriter.fill(new FillWrapper("headList",headList), fieldSheet); excelWriter.fill(new FillWrapper("fieldList",field), fieldSheet); } }catch (Exception e){ e.printStackTrace(); }finally { // 关闭流 if(excelWriter!=null){ excelWriter.close(); } if(inputStream != null){ inputStream.close(); } } }
拦截器：
public class LinkStyleHandler implements CellWriteHandler { private Map<String,Integer> map; public LinkStyleHandler(Map<String, Integer> map) { this.map = map; } @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) { int col = cell.getColumnIndex(); if(col == 1){ Workbook workbook = writeSheetHolder.getSheet().getWorkbook(); String linkName = (cell.getStringCellValue().length()>28?cell.getStringCellValue().substring(0,28):cell.getStringCellValue())
.toLowerCase(); CreationHelper helper = workbook.getCreationHelper(); Hyperlink hyperlink = helper.createHyperlink(HyperlinkType.DOCUMENT); if(map.containsKey(linkName)){ map.put(linkName,map.get(linkName)+1); hyperlink.setAddress("#"+linkName+"_"+map.get(linkName)+"!A1"); }else{ map.put(linkName,1); hyperlink.setAddress("#"+linkName+"!A1"); } cell.setHyperlink(hyperlink); } } }
前台vue请求导出代码：
<el-button type="primary" size="small" class="exportBtn" @click="exportData" v-loading.fullscreen.lock="exportLoading" element-loading-text="拼命下载中" element-loading-background="rgba(0, 0, 0, 0.8)" >导出</el-button> exportData(){ this.allTableIds = '' let checkedNodes = this.$refs.tree.getCheckedNodes() for(let i=0;i<checkedNodes.length;i++){ if(checkedNodes[i].type=='table'){ if(i<checkedNodes.length-1){ this.allTableIds += checkedNodes[i].tyTableInfoId+',' }else{ this.allTableIds += checkedNodes[i].tyTableInfoId } } } if(this.allTableIds.length == 0){ this.$alert('请先选择您要导出的表！', { confirmButtonText:'确定', type:'warning', title:'提示' }); }else{ let tdata = {} tdata.allTableIds = this.allTableIds this.exportLoading = true; axios({ method: "post", baseURL: process.env.VUE_APP_BASE_API, // url = base url + request url url: "/tyTableInfoApi/exportFillExcel", data: tdata, headers: { Authorization: getToken() }, responseType: "blob", }).then(response=>{ this.exportLoading = false const filename = response.headers["content-disposition"].replace( /\w+;filename=(.*)/, "$1" ); const type = response.headers["content-type"]; const blob = new Blob([response.data], { type: "blob" }); if (typeof window.navigator.msSaveBlob !== "undefined") { // 兼容IE，window.navigator.msSaveBlob：以本地方式保存文件 window.navigator.msSaveBlob(blob, decodeURI(filename)); } else { // 创建新的URL并指向File对象或者Blob对象的地址 const blobURL = window.URL.createObjectURL(blob); // 创建a标签，用于跳转至下载链接 const tempLink = document.createElement("a"); tempLink.style.display = "none"; tempLink.href = blobURL; tempLink.setAttribute("download", decodeURI(filename)); // 兼容：某些浏览器不支持HTML5的download属性 if (typeof tempLink.download === "undefined") { tempLink.setAttribute("target", "_blank"); } // 挂载a标签 document.body.appendChild(tempLink); tempLink.click(); document.body.removeChild(tempLink); // 释放blob URL地址 window.URL.revokeObjectURL(blobURL); } }); } },
模板样式：
最终效果图：
前台页面
总结：最开始使用拦截器的方式设置每个sheet的样式，由于每个单元格都要遍历一遍，所以导出的速度很慢，后来使用模板填充方式在模板中定义好样式和字体，代码中只需要查询要显示的数据，速度提升了很多；另外之前是表详细信息是遍历一次查询一次，后来改成一次性查出所有的数据并转换成map，遍历的时候只需要判断是否包含就可以导出，大大提升了性能。
文档：https://easyexcel.opensource.alibaba.com/docs/current/
