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

背景

本文章可以将数据库中多个表数据导出到一个excel文件中，每个数据库表数据为一个sheet工作空间页，为了防止oom问题，文章导出采用了分页形式导出数据。

如需转载，请附上本文章的链接，谢谢！

作者：曹震

目录

一、excel导入篇

二、依赖

三、yml配置文件

四、代码

1.controller层

2.service层

3.导出效果

一、excel导入篇

【EasyExcel】第一篇：动态导入excel，生成对应数据库表_郭老师的小迷弟雅思莫了的博客-CSDN博客https://caozhen.blog.csdn.net/article/details/127785588?spm=1001.2014.3001.5502

二、依赖

        
        <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>easyexcelartifactId>
            <version>3.0.5version>
        dependency>
        
     
        <dependency>
            <groupId>com.github.pagehelpergroupId>
            <artifactId>pagehelper-spring-boot-starterartifactId>
            <version>1.4.5version>
        dependency>
 
        
        <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>fastjsonartifactId>
            <version>1.2.70version>
        dependency>

三、yml配置文件

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

四、代码

1.controller层

 
/**
 * 
 * 前端控制器
 * 
 *
 * @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 list = new ArrayList<>();
        List ids = getTreeNodesIds(id, list);
        //如果是最后一级目录，则将拿到的id数据填充进集合
        if (CollectionUtils.isEmpty(ids)) {
            ids.add(id);
        }
        QueryWrapper queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda().in(SysDataDirectory::getId, ids);
        queryWrapper.lambda().orderByAsc(SysDataDirectory::getSort);
        List 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 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> titles = new ArrayList<>();
                    List 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> dataList = new ArrayList<>();
                        PageHelper.startPage(k + 1, pageSize);
                        List> tableData = sysDataDirectoryMapper.getTableData(directoryList.get(i).getTableName());
                        if (!CollectionUtils.isEmpty(tableData)) {
                            tableData.forEach(x -> {
                                List 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.导出效果

 有什么问题可以留言，看到会回复！