SpringBoot 大数据量导出多Excel文件后压缩

SpringBoot 大数据量导出多Excel文件后压缩

目录

1. 导入依赖

2. ExcelUtils 工具类

3. 大数据量生成Excel文件案列，生成多个Excel文件压缩 案列

---

1. 导入依赖

        
            com.alibaba
            easyexcel
            2.2.8
        

        
            com.google.guava
            guava
            28.2-jre
        

2. ExcelUtils 工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.google.common.collect.Lists;
import com.jenkins.dto.User;
import com.jenkins.utils.ZipUtil;
import com.jenkins.utils.excel.dto.WorkbookDto;

import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicReference;
import java.util.function.Supplier;


public class ExcelUtils {

    
    private static final String pathConfig = "C:/tmp";

    
    private static final int EXCEL_LIMIT_NUM = 500000;

    
    public static File createMultiFileExcelZipFile(List> suppliers) {
        File zipFile = null;
        // 临时目录
        File localDir = ExcelUtils.createDir(pathConfig);

        int sheetNumber = 0;
        for (Supplier supplier : suppliers) {
            WorkbookDto workbookDto = supplier.get();
            File file = new File(localDir, workbookDto.getFileName() + "_" + workbookDto.getFileSuffix() + ".xlsx");
            ExcelWriter excelWriter = EasyExcel.write(file.getAbsolutePath()).excelType(ExcelTypeEnum.XLSX).build();
            if (workbookDto.getDataList().size() > EXCEL_LIMIT_NUM) {
                List> parts = Lists.partition(workbookDto.getDataList(), EXCEL_LIMIT_NUM);
                for (int i = 1; i <= parts.size(); i++) {
                    List list = parts.get(i - 1);
                    writeExcel(excelWriter, list, workbookDto.getExcelClass(), sheetNumber, "sheet" + i);
                    sheetNumber++;
                }
            } else {
                writeExcel(excelWriter, workbookDto.getDataList(), workbookDto.getExcelClass(), sheetNumber, "sheet1");
                sheetNumber++;
            }
            excelWriter.finish();
        }
        String srcFilePath = localDir.getPath();
        String destFilePath = srcFilePath;
        zipFile = ZipUtil.compress(srcFilePath, destFilePath);
        if (null != zipFile) {
            deleteFileDir(localDir);
        }
        return zipFile;
    }

    public static void writeExcel(ExcelWriter excelWriter, List dataList, Class excelClass, Integer sheetNumber,
                                  String sheetName) {
        Class cls = null;
        if (dataList == null || dataList.isEmpty()) {
            cls = excelClass;
        } else {
            cls = dataList.get(0).getClass();
        }
        WriteSheet sheet = EasyExcel.writerSheet(sheetNumber, sheetName).head(cls).build();
        excelWriter.write(dataList, sheet);
    }

    
    private static boolean deleteFileDir(File dirFile) {
        if (!dirFile.exists()) {
            return false;
        }
        if (dirFile.isFile()) {
            return dirFile.delete();
        } else {
            for (File file : dirFile.listFiles()) {
                deleteFileDir(file);
            }
        }
        return dirFile.delete();
    }

    
    private static File createDir(String localDirPath) {
        File localDir = new File(localDirPath);
        if (!localDir.exists()) {
            localDir.mkdirs();
        }
        return localDir;
    }
}

3. 大数据量生成Excel文件案列，生成多个Excel文件压缩 案列

// 大数据量生成Excel文件案列，生成多个Excel文件压缩后
    public static void main(String[] args) {
        // 单个Excel文件 数据行数， 建议定义为常量
        int limit = 1000;
        // 查询需要导出 数据总量
        int total = userMapper.findTotal();
        // 计算页总数
        int pageTotal = total % limit > 0 ? (total / limit + 1) : (total / limit);

        List> dataList = new ArrayList<>();
        AtomicReference startUserId = new AtomicReference<>(null);
        for (int i = 0; i < pageTotal; i++) {
            int pageNumber = i;
            Supplier supplier = () -> {
                // ...（此处查询数据库数据，当执行 supplier.get() 方法时，才会执行该代码块）
                List users = userMapper.findListByParams(startUserId.get(), pageNumber * limit, limit);

                // 记录查询结果末尾最后一条数据的userId, 查询出的数据需按照 userId排序 大数据量查询数据库优化 *** 作
                if (users.size() > 0) {
                    startUserId.set((users.get(users.size() - 1)).getUserId());
                }

                return new WorkbookDto(users, User.class, "用户", String.valueOf(pageNumber));
            };
            dataList.add(supplier);
        }
        File zipFile = ExcelUtils.createMultiFileExcelZipFile(dataList);
        // ... （可对文件做上传或直接响应给前端等 *** 作）
    }