大数据量导出excel

前面我们介绍了如何在前端、后端导出数据量较小的excel表格。那么当数据量较大时导出应该如何做呢？

当需要导出数据量较大的 Excel 表格时，由于一次性将数据全部加载到内存中再进行导出可能会导致内存溢出的问题，因此需要采取分批量导出的方式。

下面介绍一种异步生成分批量的excel压缩文件的方法。前端主要工作是发起生成请求，轮询生成进度并下载，下载后发起删除请求。后端负责接收生成请求，分批查询数据，生成excel，打包为zip，上传到ftp，下载后ftp服务的删除压缩包。需要使用redis缓存生成进度，多线程分批查询并生成excel可以加快生成速度。

前端代码

前端发起请求后会接收到一个后端生成的zipFileID,然后根据这个zipFileID完成下载及删除。

 exportDetail() {
            this.zipFileID = '',
            this.downloadUrl = '',
            this.exportVisible = false;
            this.isExportButtonDisabled = true;
            this.descriptionVisible = false;

            let _query = Object.assign({}, this.query2);
            exportProductDetailSerialNumber(_query).then(res => {
                if (res.code == 200) {
                    this.zipFileID = res.data;
                    this.$notify({
                        title: '提示',
                        message: '正在生成Excel的压缩文件,请留意进度...',
                        type: 'success',
                        duration: 5000
                    })
                    // 轮询下载结果
                    let self = this;
                    download.downloadExcel(this.zipFileID, self)
                } else {
                    this.isExportButtonDisabled = false;
                    this.$message({
                        message: '生成失败',
                        type: 'error'
                    });
                }
            }).catch(err => {
                this.isExportButtonDisabled = false;
                this.$message({
                    message: '生成失败!!!!!',
                    type: 'error'
                });
            })
        },

关于轮询下载结果，我封装成了一个工具类，在需要使用的地方引入就可以使用。

引入

import download from '@/utils/exportExcelUtils';

exportExcelUtils.js工具类代码,工具类需要放入前端项目中一般放于utils文件夹下

import { checkTaskStatus, deleteTask } from '@/api/axiosapi';
const download = {
    // 轮询下载结果
    downloadExcel(zipFileID,self,saleNo) {
        self.progress = 0
        let downloadInterval = setInterval(() => {
            checkTaskStatus(zipFileID).then(res => {
                // 处理返回的结果
                if (res.data === 'completed' || res.data === 'generate_fail') {
                    clearInterval(downloadInterval)
                    self.isExportButtonDisabled = false;
                    self.$notify({
                        title: '提示',
                        message: '生成失败，请重试',
                        duration: 0,
                        type: 'error',

                    });

                    throw new Error('生成失败，请重试')
                } else if (res.data.status === 'processing') {
                    self.progress = res.data.progress;

                    //如果进度为100提示正在打包
                    if (self.progress !== '100.00' && self.progress !== 'null') {
                        self.$notify({
                            title: '提示',
                            message: `正在生成Excel文件,请稍后--->${self.progress}%`,
                            duration: 4900
                        });
                    } else if (!self.isPackagingNotified) {

                        self.isPackagingNotified = true // 标记已经显示过通知
                        self.$notify({
                            title: '提示',
                            message: '正在打包文件,请稍后...',
                            duration: 16000
                        })
                    }
                } else {
                    clearInterval(downloadInterval)
                    self.downloadUrl = res.data
                    self.isPackagingNotified = false // 重置标记
                    // 下载
                    const xhr = new XMLHttpRequest();
                    xhr.open('GET', self.downloadUrl);
                    xhr.responseType = 'blob';
        
                    xhr.addEventListener('loadstart', () => {
                        self.$notify({
                            title: '提示',
                            message: '正在下载已生成的文件...',
        
                        });
                    });
        
                    xhr.addEventListener('load', () => {
                        const blob = xhr.response;
                        const link = document.createElement('a');
                        link.href = window.URL.createObjectURL(blob);
                        if(saleNo !== undefined){
                            link.download = `${saleNo}-excel.xlsx`;
                        }else{
                            link.download = `${zipFileID}-excel.zip`;
                        }
                        
        
                        document.body.appendChild(link);
                        link.click();
                        document.body.removeChild(link);
                        if(saleNo !== undefined){
                            self.$notify({
                                title: '提示',
                                message: `${saleNo}生产工单已完成下载，请查看浏览器下载区域。`,
                                duration: 3000,
                                type: 'success',
            
                            });}else{
                                self.$notify({
                                    title: '提示',
                                    message: '压缩文件已完成下载，请查看浏览器下载区域。',
                                    duration: 0,
                                    type: 'success',
                
                                });
                            }
                        
                    });
                    xhr.onloadend = function() {
                        self.isExportButtonDisabled = false;
                        // 下载完成后删除文件
                        deleteTask(self.zipFileID).then(
                            res => {
                                if (res.code !== 200) {
                                    // 删除失败
                                } else {
                                    
                                }
                            },
                            error => {
                    
                            }
                        );};
        
                    xhr.send();
                }
            }).catch(error => {
                // 请求出错时，停止轮询并抛出异常
                clearInterval(downloadInterval)
                self.isExportButtonDisabled = false;
                self.$notify({
                    title: '提示',
                    message: '请求出错，请重试',
                    duration: 0,
                    type: 'error',

                });
                throw new Error('生成失败，请重试')
            })
        }, 5000)

    },
  };
  
  
  export default download;

后端代码

控制层

这个接口采用异步的方式进行导出操作，当用户发起请求时，先返回一个 zipFileID 给客户端，表示文件正在导出中，完成后可以通过该 ID 获取到下载地址。同时开启一个新的线程执行导出操作，避免占用主线程资源，从而提高接口的性能和并发度。

需要注意的是，在导出操作结束后，需要将导出状态更新到 Redis 中，以便客户端可以通过该状态获取到下载地址。如果导出失败则需要在 Redis中删除该 zipFileID 以及相关状态信息。

这个接口的实现思路比较清晰，采用了异步导出的方式，并选择了 Redis 作为状态存储的工具，可以有效地保证接口的并发度和性能，并且可以很好地应对导出异常情况的处理

@Slf4j
@RestController
@RequestMapping("/excel")
public class ExcelController {
    @Autowired
    private ExcelService excelService;

    @Autowired
    private RedisTemplate redisTemplate;

    @Autowired
    private FtpInfoFeignApi ftpInfoFeignApi;

    

    /**
     * 导出用户Excel表
     *
     * @return
     */
    @ApiOperation("导出用户Excel表")
    @PostMapping("/exportAllUser")
    public Result exportAllUser() {
        String zipFileID = UUID.randomUUID().toString();
        HashOperations hashOperations = redisTemplate.opsForHash();
        String key = "zipFileID:"+zipFileID;
        Map<String, String> map = new HashMap<>();
        map.put("status", "processing");
        map.put("url", "null");
        hashOperations.putAll(key, map);
        new Thread(() -> {
            boolean flag = excelService.exportUserExcelVo(zipFileID);
            if (!flag) {
                log.error("导出用户excel表失败,zipFileID{}", zipFileID);
                redisTemplate.delete(key);
            }
        }).start();

        return ResultWrapper.ok(zipFileID);
    }
    

    /**
     * 查询生成文件的任务状态通用接口
     * @param zipFileID
     * @return
     */
    @ApiOperation("查询生成文件的任务状态")
    @PostMapping("/checkTaskStatus/{zipFileID}")
    public Result checkTaskStatus(@PathVariable(name = "zipFileID") String zipFileID) {
        HashOperations hashOperations = redisTemplate.opsForHash();
        String key = "zipFileID:"+zipFileID;
        String status = (String) hashOperations.get(key, "status");

        if (status == null) {
            // 如果没有该任务的记录，则说明任务已经完成或出错
            ResultWrapper.error( "completed");
        } else if (status.equals("generate_fail")) {
            // 如果任务生成失败，则返回失败信息
            return ResultWrapper.error("generate_fail");
        } else if (status.equals("generate_success")) {
            // 如果任务成功生成 zip 文件，则返回下载链接
            String url = (String)hashOperations.get(key, "url");
            return ResultWrapper.ok(url);
        } else {
            // 如果任务仍在进行中，则返回进度
            String progress = (String) hashOperations.get(key, "progress");
            HashMap<String, String> map = new HashMap<>();
            map.put("status", status);
            map.put("progress", progress);
            return ResultWrapper.ok(map);
        }
        return ResultWrapper.ok();
    }
    /**
     * 下载后删除压缩任务文件通用接口
     */
    @ApiOperation("下载后删除压缩任务文件")
    @GetMapping("/deleteTaskStatus/{zipFileID}")
    public Result deleteTaskStatus(@PathVariable(name = "zipFileID") String zipFileID) {
        FtpInfoDto ftpInfo = ftpInfoFeignApi.getFtpInfoDto();
        boolean flag = ExcelUtil.deleteFile(zipFileID,ftpInfo);
        String key = "zipFileID:"+zipFileID;
        redisTemplate.delete(key);
        if (!flag) {
            return ResultWrapper.error();
        }
        return ResultWrapper.ok();
    }
}

服务层

public interface ExcelService {
    

    /**
     * 导出用户压缩包
     *
     * @return boolean
     */
    boolean exportUserExcelVo(String zipFileID);
}

@Service
@Slf4j
public class ExcelServiceImpl implements ExcelService {
     @Autowired
    private UserMapper userMapper;


    @Autowired
    private FtpInfoFeignApi ftpInfoFeignApi;

    @Autowired
    private RedisTemplate redisTemplate;

    @Autowired
    private ThreadPoolExecutor excelExportThreadPool;

   /**
     * 导出用户压缩包
     *
     * @param zipFileID
     * @return boolean
     */
    @Override
    public boolean exportUserExcelVo(String zipFileID) {
        HashOperations hashOperations = redisTemplate.opsForHash();
        String key = "zipFileID:" + zipFileID;
        //获取查询总条数
        int count = userMapper.selectCount(null);
        try {
            BiFunction<Integer, Integer, List<?>> selectFunction = (startRow, searchTotal) -> userMapper.selectAllUserExcelVo(startRow, searchTotal);
            //生成excel的方法
            List<File> files = ExcelUtil.generateExcel(excelExportThreadPool, hashOperations, key,
                    UserExcelVo.class, selectFunction, count);
            //获取FTP服务信息的方法，这个你们需要根据自己公司的情况获取登陆地址密码等，这个接口不是通用的
            FtpInfoDto ftpInfo = ftpInfoFeignApi.getFtpInfoDto();
            if (ftpInfo == null) {
                throw new BusinessException("无法获取ftp连接信息");
            }
            //生成zip并上传ftp服务的方法
            ExcelUtil.generateZipAndUpload(hashOperations, zipFileID, files, ftpInfo);

        } catch (Exception e) {
            hashOperations.put(key, "status", "generate_fail");
            log.info("导出用户表异常: {}", e.getMessage(), e);
            return false;
        }

        return true;
    }
}

关于生成excel和生成zip并上传到ftp服务以及删除，我同样封装成了一个工具类，值得注意的是ftp连接的方式并不是通用的，你们需要修改成自己的ftp服务信息接口。

@Slf4j
public class ExcelUtil {

    /**
     * 查询全部数据并生成返回execl文件列表
     *
     * @param excelExportThreadPool 线程池
     * @param clazzResultType       查询结果对象类型
     * @param selectFunction        查询方法
     * @param count                 查询结果数量
     * @return 查询结果对象列表
     * @throws InterruptedException
     */
    public static <T> List<File> generateExcel(ThreadPoolExecutor excelExportThreadPool, HashOperations hashOperations, String key, Class<?> clazzResultType,
                                               BiFunction<Integer, Integer, List<?>> selectFunction, int count) throws InterruptedException {
        // 限制最大导出数量100万条
        if (count > 1000000) count = 1000000;
        // 批量读取数据的行数
        int batchSize = 100000;
        int totalPages = (count + batchSize - 1) / batchSize;
        //线程计数器,用于等待所有线程执行完毕
        CountDownLatch countDownLatch = new CountDownLatch(totalPages);
        // 创建一个临时目录用于保存 Excel 文件
        String tempDir = System.getProperty("java.io.tmpdir");
        File excelDir = new File(tempDir);
        if (!excelDir.exists()) {
            excelDir.mkdirs();
        }
        // 用于保存每个Excel文件的临时文件
        List<File> tempFiles = new ArrayList<>();
        final int[] f = {1};
        //启动线程查询和写入Excel数据
        for (int i = 0; i < totalPages; i++) {
            final int startRow = i * batchSize;
            final int searchTotal = (i == totalPages - 1) ? (count - i * batchSize) : batchSize;
            System.out.println("startRow:" + startRow + ",searchTotal:" + searchTotal);
            final float[] zipProgress = {0};
            //使用线程池执行任务
            excelExportThreadPool.submit(() -> {
                try {
                    zipProgress[0] = (((float) f[0] / totalPages) * 100);
                    String progress = String.format("%.2f", zipProgress[0]);
                    //如果是最后一个线程,则将进度设置为100
                    if (f[0] == totalPages) {
                        progress = "100.00";
                    }
                    hashOperations.put(key, "progress", progress);
                    int fileCount = f[0]++;
                    String fileName = "excel表" + fileCount + "-" + UUID.randomUUID().toString() + ".xlsx";
                    // 定义临时文件路径
                    String filePath = excelDir.getPath() + File.separator + fileName;
                    List<?> dataList = selectFunction.apply(startRow, searchTotal);
                    EasyExcel.write(filePath, clazzResultType)
                            .sheet("Sheet1")
                            .doWrite(dataList);
                    tempFiles.add(new File(filePath));

                } finally {
                    countDownLatch.countDown();
                }
            });
        }
        countDownLatch.await();
        return tempFiles;
    }


    /**
     * 生成zip并上传ftp通用方法
     *
     * @param hashOperations redis操作对象
     * @param zipFileID      zip文件ID
     * @param tempFiles      临时文件列表
     * @param ftpInfo        ftp信息
     * @return
     */
    public static void generateZipAndUpload(HashOperations hashOperations, String zipFileID, List<File> tempFiles, FtpInfoDto ftpInfo) {
        if (tempFiles == null || tempFiles.size() == 0) {
            throw new BusinessException("没有可压缩的文件");
        }
        // 创建一个临时目录用于保存 zip 文件
        String tempDir = System.getProperty("java.io.tmpdir");
        File zipDir = new File(tempDir);
        if (!zipDir.exists()) {
            zipDir.mkdirs();
        }
        String zipFileName = zipFileID + "-excel.zip";
        String zipFilePath = zipDir.getPath() + File.separator + zipFileName;
        // 将多个临时文件打包成 zip 文件
        try (FileOutputStream fileOutputStream = new FileOutputStream(zipFilePath);
             ZipOutputStream zipOutputStream = new ZipOutputStream(fileOutputStream)) {
            for (File file : tempFiles) {
                ZipEntry zipEntry = new ZipEntry(file.getName());
                zipOutputStream.putNextEntry(zipEntry); // 添加到 ZIP 压缩包中
                FileInputStream fileInputStream = new FileInputStream(file);
                byte[] buffer = new byte[1024];
                int length;
                while ((length = fileInputStream.read(buffer)) > 0) {
                    zipOutputStream.write(buffer, 0, length); // 压缩文件
                }
                fileInputStream.close();
                zipOutputStream.closeEntry();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 删除临时文件,这里是excel文件的临时文件
            for (File file : tempFiles) {
                file.delete();
            }
        }
        if (ftpInfo == null) {
            throw new BusinessException("无法获取ftp连接信息");
        } else {
            String filePath = ftpInfo.getBasePath() + "/存储路径";
            try (Ftp ftp = new Ftp(ftpInfo.getAddress(), Integer.parseInt(ftpInfo.getPort()), ftpInfo.getUsername(), ftpInfo.getPassword(), Charsets.UTF_8)) {
                ftp.setMode(FtpMode.Passive);
                ftp.upload(filePath, new File(zipFilePath));
                String zipUrl = ftpInfo.getImageBasePath() + "存储路径/" + zipFileName;
                //记录为已经生成状态
                String key = "zipFileID:" + zipFileID;
                hashOperations.put(key, "status", "generate_success");
                hashOperations.put(key, "url", zipUrl);

            } catch (Exception e) {
                log.error("上传文件到ftp失败: {}", e.getMessage(), e);
            } finally {
                File zipFile = new File(zipFilePath);
                zipFile.delete();
            }
        }
    }

    /**
     * 删除ftp压缩文件的通用方法
     *
     * @param zipFileID zip文件ID
     * @param ftpInfo   ftp信息
     * @return 删除成功返回true, 否则返回false
     */

    public static boolean deleteFile(String zipFileID, FtpInfoDto ftpInfo) {
        if (ftpInfo == null) {
            throw new BusinessException("无法获取ftp连接信息");
        } else {
            String filePath = ftpInfo.getBasePath() + "/存储路径/";
            try (Ftp ftp = new Ftp(ftpInfo.getAddress(), Integer.parseInt(ftpInfo.getPort()), ftpInfo.getUsername(), ftpInfo.getPassword(), Charsets.UTF_8)) {
                ftp.setMode(FtpMode.Passive);
                boolean b = ftp.delFile(filePath + zipFileID + "-excel.zip");
//                if (!b) {
//                    throw new BusinessException("删除文件失败");
//                }
//                log.info("删除文件成功, zipUrl: {}", filePath + zipFileID + "-excel.zip");

            } catch (Exception e) {
                log.error("删除文件失败: {}", e.getMessage(), e);
            }
        }
        return true;
    }
}

线程池

@Configuration
public class ThreadPoolConfig {

    @Bean("excelExportThreadPool")
    public ThreadPoolExecutor excelExportThreadPool(){
       return new ThreadPoolExecutor(4, 8, 60, TimeUnit.SECONDS, new ArrayBlockingQueue<>(20), new ThreadFactory() {
            private AtomicInteger threadNumber = new AtomicInteger(1);

            @Override
            public Thread newThread(Runnable r) {
                Thread thread = new Thread(r);
                thread.setName("excel-export-thread-pool" + threadNumber.getAndIncrement());
                return thread;
            }
        },new ThreadPoolExecutor.AbortPolicy());
    }
}

表格写入对象

@Data
@ToString
@NoArgsConstructor
@ExcelIgnoreUnannotated
public class UserExcelVo {

    @ColumnWidth(21)
    @ExcelProperty("用户名")
    private String username;

    @ColumnWidth(15)
    @ExcelProperty("昵称")
    private String nickname;

    @ColumnWidth(15)
    @ExcelProperty("性别")
    private String gender;

    @ColumnWidth(25)
    @ExcelProperty("电子邮箱")
    private String email;

    @ColumnWidth(25)
    @ExcelProperty("登录时间")
    private String loginTime;

    @ColumnWidth(20)
    @ExcelProperty("登录IP")
    private String loginIp;

    @ColumnWidth(25)
    @ExcelProperty("创建时间")
    private String createTime;

    @ColumnWidth(25)
    @ExcelProperty("更新时间")
    private String updateTime;

}

mapper

    /**
     * 查询所有用户ExcelVo
     *
     * @param startRow
     * @param searchTotal
     * @return
     */
    List<UserExcelVo> selectAllUserExcelVo(@Param("startRow") Integer startRow, @Param("searchTotal") Integer searchTotal);

xml

 <select id="selectAllUserExcelVo" resultType="com.quxin.chip.vo.UserExcelVo">
        SELECT
            u.username AS username,
            u.nickname AS nickname,
            CASE  u.gender
               WHEN 0 THEN '男'
               WHEN 1 THEN '女'
               ELSE '未知'
               END  as gender,
            u.email as email,
            u.login_time AS loginTime,
            u.login_ip AS loginIp,
            u.create_time AS createTime,
            u.update_time AS updateTime
        FROM
            t_user u
        ORDER BY u.create_time DESC
            LIMIT #{startRow}, #{searchTotal}
    </select>