【EasyExcel】第二篇：导出excel文件，导出多个sheet工作空间

        <!--操作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>

#mybatis 分页插件
pagehelper:
  helperDialect: mysql
  reasonable: true
  supportMethodsArguments: true
  params: count=countSql

 
/**
 * <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));
    }
}
 

 /***
     * 导出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("导出成功！");
    }

 /***
     * 导出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();
                }
            }
        }
    }

 /**
     * 每次向EXCEL写入的记录数(查询每页数据大小) 20W
     */
    public static final Integer PER_WRITE_ROW_COUNT = 200000;