一、excel导入篇
二、依赖
<!--操作Excel工具依赖--> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.0.5</version> </dependency> <!--mybatis 分页插件--> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.4.5</version> </dependency> <!-- fastjson --> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.70</version> </dependency>
三、yml配置文件
#mybatis 分页插件 pagehelper: helperDialect: mysql reasonable: true supportMethodsArguments: true params: count=countSql
四、代码
1.controller层
/** * <p> * 前端控制器 * </p> * * @author 曹震 * @since 2022-10-24 */ @Api(tags = "数据源集合") @RestController @RequestMapping("/hvit/dataSet/") public class SysDataDirectoryController { @Autowired private SysDataDirectoryDataService sysDataDirectoryDataService; @ApiOperation("导出文件") @GetMapping("/exportExcel") public void exportExcel(String id, HttpServletResponse response) { ResponseEntity.ok(sysDataDirectoryDataService.exportExcel(id, response)); } }
2.service层
/*** * 导出excel结果 * 数据量适中（100W以内）： 一个SHEET分批查询导出 * @param id * @return */ public R exportExcel(String id, HttpServletResponse response) { SysDataDirectory sysDataDirectory = sysDataDirectoryService.getById(id); if (sysDataDirectory == null) { return R.error("数据源不存在！"); } String fileName = StringUtils.isNotEmpty(sysDataDirectory.getDataName()) ? sysDataDirectory.getDataName() : "新的文件"; if (sysDataDirectory.getParentId().equals(Constant.PARENT_ID)) { return R.error("请选择数据源或数据集导出！"); } List<String> list = new ArrayList<>(); List<String> ids = getTreeNodesIds(id, list); //如果是最后一级目录，则将拿到的id数据填充进集合 if (CollectionUtils.isEmpty(ids)) { ids.add(id); } QueryWrapper<SysDataDirectory> queryWrapper = new QueryWrapper<>(); queryWrapper.lambda().in(SysDataDirectory::getId, ids); queryWrapper.lambda().orderByAsc(SysDataDirectory::getSort); List<SysDataDirectory> directoryList = sysDataDirectoryService.list(queryWrapper); exportSystemExcel(fileName, directoryList, response); return R.ok("导出成功！"); }
上面这一块业务代码比较多，直接看exportSystemExcel(fileName, directoryList, response);就行。
下面代码是导出文件的代码！
/*** * 导出excel生成xlsx文件 * @param directoryList * @param response */ public void exportSystemExcel(String fileName, List<SysDataDirectory> directoryList, HttpServletResponse response) { OutputStream outputStream = null; try { long startTime = System.currentTimeMillis(); log.info("导出开始时间:{}", startTime); outputStream = response.getOutputStream(); WriteWorkbook writeWorkbook = new WriteWorkbook(); writeWorkbook.setOutputStream(outputStream); writeWorkbook.setExcelType(ExcelTypeEnum.XLSX); ExcelWriter writer = new ExcelWriter(writeWorkbook); // 设置EXCEL名称 String newFileName = new String((fileName).getBytes(), "UTF-8"); //多条数据代表有多个sheet空间 if (!CollectionUtils.isEmpty(directoryList)) { for (int i = 0; i < directoryList.size(); i++) { // 设置SHEET名称 WriteSheet sheet = new WriteSheet(); sheet.setSheetNo(i); sheet.setSheetName(directoryList.get(i).getDataName()); // 设置表头标题 WriteTable table = new WriteTable(); List<List<String>> titles = new ArrayList<>(); List<String> columns = sysDataDirectoryMapper.getColumnName(directoryList.get(i).getTableName()); if (!CollectionUtils.isEmpty(columns)) { columns.forEach(x -> { titles.add(Collections.singletonList(x)); }); } table.setHead(titles); // 查询总数并 【封装相关变量 这块直接拷贝就行 不要改动】 Integer totalRowCount = sysDataDirectoryMapper.getTableDataCount(directoryList.get(i).getTableName()); Integer pageSize = Constant.PER_WRITE_ROW_COUNT; Integer writeCount = totalRowCount % pageSize == 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize + 1); for (int k = 0; k < writeCount; k++) { List<List<String>> dataList = new ArrayList<>(); PageHelper.startPage(k + 1, pageSize); List<LinkedHashMap<String, String>> tableData = sysDataDirectoryMapper.getTableData(directoryList.get(i).getTableName()); if (!CollectionUtils.isEmpty(tableData)) { tableData.forEach(x -> { List<String> mapData = new ArrayList<>(); Set set = x.keySet(); Iterator iterator = set.iterator(); while (iterator.hasNext()) { Object next = iterator.next(); mapData.add(x.get(next)); } dataList.add(mapData); }); } writer.write(dataList, sheet, table); } } } //下载Excel response.setHeader("Content-Disposition", "attachment;filename=" + new String(newFileName.getBytes(StandardCharsets.UTF_8), "ISO8859-1") + ".xlsx"); response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); writer.finish(); outputStream.flush(); // 导出时间结束 long endTime = System.currentTimeMillis(); log.info("导出结束时间:{}", endTime + "ms"); log.info("导出所用时间:{}", (endTime - startTime) / 1000 + "秒"); } catch (Exception ex) { log.error("导出文件失败：{}", ex.getMessage()); } finally { if (outputStream != null) { try { outputStream.close(); } catch (Exception e) { e.printStackTrace(); } } } }
Integer pageSize = Constant.PER_WRITE_ROW_COUNT;这里是常量。可以自行建立 Constant类
/** * 每次向EXCEL写入的记录数(查询每页数据大小) 20W */ public static final Integer PER_WRITE_ROW_COUNT = 200000;
好了到这里基本结束了
3.导出效果
有什么问题可以留言，看到会回复！
