easyExcel大数据量导出

/**
* 导出系统日志
*
* @param type    类型
* @param psw     psw
* @param request 请求
* @return {@link AjaxResult}
* @throws Exception 异常
*/
public AjaxResult downLoadLogFile(@PathVariable("type") Integer type, String psw, HttpServletRequest request) throws Exception
{
    boolean flag = TokenTools.checkTokenIsEqual(request, "UserToken", "user_SToken");
    if (!flag)
    {
        throw new RRException("token失效");
    }
    List<SysLogModel> date;
    if (ObjectUtil.isNotNull(SystemLoggerTimeCache.getSystemLoggerList(SystemLoggerTimeCache.KEY)))
    {
        // 缓存有则从缓存取数据
        date = SystemLoggerTimeCache.getSystemLoggerList(SystemLoggerTimeCache.KEY);
    }else
    {
        // 缓存没有则进行流式查询
        date = logService.getAllLoggerList(type);
    }
    String fileName = IdUtil.simpleUUID()+"审计日志.xlsx";
    String filePath = properties.getServerResultPath()+fileName;
    BigExcelFileInit.bigExcelFileInit(date,psw,fileName,filePath,SysLogModel.class);
    return AjaxResult.success("日志文件生成完成!",fileName);
    }

/**
 * @version 1.0.0
 * @className: BigExcelFileInit
 * @description: excel大数据量导出工具类
 * @author: LiJunYi
 * @create: 2022/7/6 8:45
 */
public class BigExcelFileInit
{
    /**
     * xlsx excel 每个 sheet 写入的数据
     */
    private static final int NUM_PER_SHEET = 300000;

    /**
     * xlsx excel 每次向 sheet 中写入的数据（分页写入）
     */
    private static final int NUM_BY_TIMES = 50000;

    /**
     * excel通用大数据量分sheet分次写入
     *
     * @param passWord    密码
     * @param fileName    文件名字
     * @param data        数据
     * @param filePath    文件路径
     * @param elementType 元素类型
     * @throws Exception 异常
     */
    public static void bigExcelFileInit(List<?> data, String passWord, String fileName, String filePath, Class<?> elementType) throws Exception
    {
        // 获取 sheet 的个数
        int sheetNum = data.size() % NUM_PER_SHEET == 0 ? data.size() / NUM_PER_SHEET : data.size() / NUM_PER_SHEET + 1;
        // 获取每个sheet 写入的次数
        int writeNumPerSheet = NUM_PER_SHEET / NUM_BY_TIMES;
        // 最后一个 sheet 写入的数量
        int writeNumLastSheet = data.size() - (sheetNum - 1) * NUM_PER_SHEET;
        // 最后一个 sheet 写入的次数
        int writeNumPerLastSheet = writeNumLastSheet % NUM_BY_TIMES == 0 ? writeNumLastSheet / NUM_BY_TIMES : writeNumLastSheet / NUM_BY_TIMES + 1;
        // 指定写入的文件
        try(ExcelWriter excelWriter = EasyExcel.write(filePath, elementType).build())
        {
            for (int i = 0; i < sheetNum; i++)
            {
                String sheetName = "sheet" + i;
                WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName).build();
                // 每个sheet 写入的次数
                int writeNum = i == sheetNum - 1 ? writeNumPerLastSheet : writeNumPerSheet;
                // 每个sheet 最后一次写入的最后行数
                int endEndNum = i == sheetNum - 1 ? data.size() : (i + 1) * NUM_PER_SHEET;
                for (int j = 0; j < writeNum; j++)
                {
                    int startNum = i * NUM_PER_SHEET + j * NUM_BY_TIMES;
                    int endNum = j == writeNum - 1 ? endEndNum : i * NUM_PER_SHEET + (j + 1) * NUM_BY_TIMES;
                    excelWriter.write(data.subList(startNum, endNum), writeSheet);
                }
            }
        }
        if (StrUtil.isNotEmpty(passWord))
        {
            // 设置excel打开密码
            FileReadonlyProtectionUtil.enforceEncryptProtectionExcel(filePath, passWord);
        }
    }
}

/**
 * @version 1.0.0
 * @className: SystemBigLoggerHandle
 * @description: 系统大数据日志处理类
 * @author: LiJunYi
 * @create: 2022/7/6 9:53
 */
@Component
@Slf4j
public class SystemBigLoggerHandle
{
    private static SysLogDao logDao;

    @Autowired
    public void setLogDao(SysLogDao logDao)
    {
        SystemBigLoggerHandle.logDao = logDao;
    }

    /**
     * 通过线程池+分段Mysql获取所有日志记录
     *
     * @param limit 限制
     * @return {@link List}<{@link SysLogModel}>
     */
    public static List<SysLogModel> getAllLoggerList(int limit,Integer type)
    {
        // 获取对应日志类型的总个数
        Integer count = logDao.getCountByType(type);
        // 线程池
        ThreadPoolTaskExecutor poolTaskExecutor = SpringUtil.getBean("commonTaskSchedulingThreadPool");
        List<FutureTask<List<SysLogModel>>> resultList = new ArrayList<>();
        //分段次数
        long cycles = count / limit;
        for (long i = 0; i < cycles; i++)
        {
            //每一段的起始坐标
            long offset = i * limit;
            log.info("异步查询系统日志，偏移量offset: {}", offset);
            //具体的查询任务
            FutureTask<List<SysLogModel>> futureTask = new FutureTask<>(() -> logDao.queryAllByLimit(offset,limit,type));
            poolTaskExecutor.execute(futureTask);
            resultList.add(futureTask);
        }
        List<SysLogModel> result = new ArrayList<>();
        Iterator<FutureTask<List<SysLogModel>>> iterator = resultList.iterator();
        while (iterator.hasNext()) {
            try {
                result.addAll(iterator.next().get());
            } catch (InterruptedException | ExecutionException e)
            {
                log.error("【getAllLoggerList#SysLogServiceImpl()】多线程查询出现异常：{}" ,e.getMessage());
                throw new RuntimeException(e);
            }
            //获取一个就删除一个任务
            iterator.remove();
        }
        //最后一次数据可能不为整，需要额外操作
        if (result.size() != count)
        {
            result.addAll(logDao.queryAllByLimit(result.size() ,Math.toIntExact(count),type));
        }
        return result;
    }

    /**
     * 获取所有日志记录通过mybatis光标
     * 这里还可以参考上面方法修改为分段查询
     * @param type     类型
     * @param count    总数
     * @param pageNum  页面顺序值
     * @param pageSize 页面大小
     * @return {@link List}<{@link SysLogModel}>
     */
    public static List<SysLogModel> getAllLoggerListUseMybatisCursor(int type,Integer count,Integer pageNum,Integer pageSize)
    {
        //分页偏移量
        Integer offset = (pageNum - 1) * pageSize;
        List<SysLogModel> resultList = new ArrayList<>(count);
        // 流式查询
        try(Cursor<SysLogModel> cursor = logDao.queryByCursor(type,offset,pageSize))
        {
            cursor.forEach(resultList::add);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        // 将结果存入缓存
        SystemLoggerTimeCache.putSystemLoggerList(SystemLoggerTimeCache.KEY,resultList);
        return resultList;
    }
}

public interface SysLogDao extends BaseMapper<SysLogModel>
{
    /**
     * 通过流式方法查询所有数据
     *
     * @param type     类型
     * @param offset   分页偏移量
     * @param pageSize 页面大小
     * @return {@link Cursor}<{@link SysLogModel}>
     */
    Cursor<SysLogModel> queryByCursor(@Param("type") Integer type, @Param("offset") Integer offset, @Param("pageSize") Integer pageSize);
}