适配任何数据结构的异步Excel生成(企业级开发)

最新推荐文章于 2024-04-29 20:19:35 发布

置顶一个爱运动的程序员

最新推荐文章于 2024-04-29 20:19:35 发布

阅读量880

点赞数 3

分类专栏：项目操作后端 Spring Boot 文章标签：数据结构 java 数据库 spring boot excel

本文链接：https://blog.csdn.net/weixin_45537947/article/details/125964325

版权

后端同时被 3 个专栏收录

17 篇文章 9 订阅

订阅专栏

项目操作

10 篇文章 0 订阅

订阅专栏

Spring Boot

8 篇文章 0 订阅

订阅专栏

前言

背景： 由于公司的excel生成过于缓慢，有时生成一个excel文件需要等待几十秒甚至几分钟，在等待的时候用户不能跳转其他页面，用户体验不好，可以先看一下，公司excel的下载流程图
在这里插入图片描述
可以发现问题所在，在查询数据的时候可能会有比较长的时间等待以及excel的生成都需要一定的时间，且数据结构单一，对不同的表单和报表等都需要独立出代码，代码的复用不高，所以进行如下优化。
先看优化后的流程图：
在这里插入图片描述

需求：用户选择需要下载的excel数据，会把选择的数据id发送给后台，后台有个解析数据的服务会把请求下载的数据，转化为sql，发送给异步下载excel，excel接收到sql并加入到线程池，返回通知给前端，正在下载的信息，用户便可在下载任务查看下载情况，在重新点击下载。下面便会分享的是异步下载excel的具体过程，至于服务转发就不在这里阐述了

源码会放在最后

一、Java操作Excel的基础知识

可以看这篇文章：https://blog.csdn.net/weixin_45537947/article/details/122521270

二、测试准备

模拟测试的数据：

CREATE TABLE `user`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `age` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `creat_time` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

创建下载任务表：

CREATE TABLE `record`  (
  `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `account` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `status` int(0) NULL DEFAULT NULL COMMENT '0下载中，1下载完成',
  `url` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `creation_time` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `file_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

三、实现源码

AsyncExcelMapper.java:

/**
 * 异步下载excel业务
 *
 * @author : 一个爱运动的程序员
 */
@Mapper
@Repository
public interface AsyncExcelMapper extends BaseMapper<Record> {

    @Select(" ${querySql} ")
    List<Map<String, Object>> queryList(@Param("querySql") String querySQL);
}

IAsyncExcelService.java:

/**
 * 异步下载excel业务
 *
 * @author : 一个爱运动的程序员
 */
public interface IAsyncExcelService  extends IService<Record> {


    /**
     * 查询下载成功的任务
     * @param account
     * @return
     */
    List<Record> findDownloadTask(String account);

    /**
     * 通过文件码下载文件
     * @param response
     * @param id
     */
    void streamDownloadFile(HttpServletResponse response, String id);

    /**
     * 添加下载任务-SQL
     * @param username
     * @param fileName
     * @param querySQL
     */
    String addSQLDownloadTask(String username, String fileName, String querySQL);
}

AsyncExcelServiceImpl.java:

/**
 * 异步下载excel业务
 *
 * @author : 一个爱运动的程序员
 */
@Service
public class AsyncExcelServiceImpl extends ServiceImpl<AsyncExcelMapper, Record> implements IAsyncExcelService {

    private final static Logger logger = LoggerFactory.getLogger(AsyncExcelServiceImpl.class);

    @Autowired
    AsyncExcelMapper asyncExcelMapper;


    /**
     * 创建等待队列
     */
    BlockingQueue<Runnable> bq = new ArrayBlockingQueue<Runnable>(20);

    /**
     * 创建线程池，池中保存的线程数为3，允许的最大线程数为5
     * keepAliveTime：当线程数大于核心数时，该参数为所有的任务终止前，多余的空闲线程等待新任务的最长时间
     * unit：等待时间的单位
     * workQueue：任务执行前保存任务的队列，仅保存由execute方法提交的Runnable任务
     */
    ThreadPoolExecutor pool = new ThreadPoolExecutor(3, 5, 50, TimeUnit.MILLISECONDS, bq);


    /**
     * 查询下载成功的任务
     *
     * @param account
     * @return
     */
    @Override
    public List<Record> findDownloadTask(String account) {
        return asyncExcelMapper.selectList(new QueryWrapper<Record>().eq("status", 1).eq("account", account).orderByDesc("creation_time"));
    }

    /**
     * 通过文件码下载文件
     *
     * @param response
     * @param id
     */
    @Override
    public void streamDownloadFile(HttpServletResponse response, String id) {
        try {
            Record record = asyncExcelMapper.selectById(id);
            File file = new File(record.getUrl());
            String filename = file.getName();
            // 以流的形式下载文件。
            InputStream fis = new BufferedInputStream(new FileInputStream(record.getUrl()));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            // 清空response
            response.reset();
            response.setContentType("application/octet-stream;charset=UTF-8");
            String fileName = new String(filename.getBytes("gb2312"), "iso8859-1");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            OutputStream ouputStream = response.getOutputStream();
            ouputStream.write(buffer);
            ouputStream.flush();
            ouputStream.close();
        } catch (Exception e) {
            throw new RuntimeException(String.format("文件下载出现异常: %s", e.getMessage()));
        }
    }

    /**
     * 添加下载任务-SQL
     *
     * @param username
     * @param fileName
     * @param querySQL
     */
    @Override
    public String addSQLDownloadTask(String username, String fileName, String querySQL) {
        // 在任务表中新建下载任务
        Record record = new Record();
        String taskId = username + System.currentTimeMillis();
        record.setId(taskId);
        record.setAccount(username);
        record.setStatus(0);
        record.setCreationTime(String.valueOf(new Date()));
        record.setFileName(fileName);
        asyncExcelMapper.insert(record);
        try {
            pool.execute(() -> {
                try {
                    Thread.sleep(5000);
                    List<Map<String, Object>> mapList = asyncExcelMapper.queryList(querySQL);
                    // 未查询到数据，删除任务表中正在下载的任务记录
                    if (mapList.isEmpty() || mapList == null) {
                        asyncExcelMapper.deleteById(taskId);
                        logger.info(taskId + " 未查询到数据，无法下载");
                    } else {    // 查询到数据生成Excel表
                        String property = System.getProperty("user.dir") + "\\src\\main\\resources\\excel\\";
                        String fileUrl = property + taskId + fileName + ".xlsx";
                        List<List<Object>> lists = new ArrayList<List<Object>>();
                        for (Map<String, Object> m : mapList) {
                            List<Object> data = new ArrayList<Object>();
                            for (Map.Entry<String, Object> entry : m.entrySet()) {
                                data.add(entry.getValue());
                            }
                            lists.add(data);
                        }
                        EasyExcel.write(fileUrl)
                                // 这里放入动态头
                                .head(head(mapList)).sheet(fileName)
                                // 当然这里数据也可以用 List<List<String>> 去传入
                                .doWrite(lists);
                        Record r = new Record();
                        r.setId(taskId);
                        r.setStatus(1);
                        r.setUrl(fileUrl);
                        asyncExcelMapper.updateById(r);
                        logger.info(taskId + fileName + "下载完成");
                    }
                } catch (Exception e) {
                    asyncExcelMapper.deleteById(taskId);
                    throw new RuntimeException(String.format("线程池下载任务出现异常: %s", e.getMessage()));
                }
            });
        } catch (Exception e) {
            asyncExcelMapper.deleteById(taskId);
            throw new RuntimeException(String.format("线程池等待队列已满，无法添加新的下载任务: %s", e.getMessage()));
        }
        return "已加入下载任务";
    }

    /**
     * 获取行信息
     * @param list
     * @return
     */
    private List<List<String>> head(List<Map<String, Object>> list) {
        List<List<String>> lists = new ArrayList<List<String>>();
        for (Map<String, Object> m : list) {
            for (Map.Entry<String, Object> entry : m.entrySet()) {
                List<String> l = new ArrayList<>();
                String mapKey = entry.getKey();
                l.add(mapKey);
                lists.add(l);
            }
            break;
        }
        return lists;
    }
}

因为需要被全局调用，所以调用采用了单例的设计模式：AsyncExcelSingleton.java:

/**
 * 异步下载excel的全局单例调用类
 *
 * @author : 一个爱运动的程序员
 */
@Slf4j
public class AsyncExcelSingleton {

    private static volatile AsyncExcelSingleton INSTANCE;

    /**
     * 加上 ForTool 后缀来和之前两种方式创建的对象作区分。
     */
    private IAsyncExcelService iAsyncExcelService;

    private AsyncExcelSingleton() {
        iAsyncExcelService = SpringContextUtils.getBean(IAsyncExcelService.class);
    }


    public static AsyncExcelSingleton getInstance() {
        if (null == INSTANCE) {
            synchronized (AsyncExcelSingleton.class) {
                if (null == INSTANCE) {
                    INSTANCE = new AsyncExcelSingleton();
                }
            }
        }
        return INSTANCE;
    }

    /**
     * 使用 SpringContextUtils 获取的 UserService 对象，并从 UserDao 中获取数据
     */
    /**
     * 查询下载成功的任务
     * @param account
     * @return
     */
    public List<Record> findDownloadTask(String account) {
        if (null == iAsyncExcelService) {
            log.debug("AsyncExcelSingleton iAsyncExcelService findDownloadTask is null");
            throw new RuntimeException(String.format("AsyncExcelSingleton iAsyncExcelService findDownloadTask is null"));
        }
        return iAsyncExcelService.findDownloadTask(account);
    }

    /**
     * 通过文件码下载文件
     * @param response
     * @param id
     */
    public void streamDownloadFile(HttpServletResponse response, String id) {
        if (null == iAsyncExcelService) {
            log.debug("AsyncExcelSingleton iAsyncExcelService streamDownloadFile is null");
            throw new RuntimeException(String.format("AsyncExcelSingleton iAsyncExcelService streamDownloadFile is null"));
        }
        iAsyncExcelService.streamDownloadFile(response, id);
    }

    /**
     * 添加下载任务-SQL
     *
     * @param username
     * @param fileName
     * @param querySQL
     */
    public String addSQLDownloadTask(String username, String fileName, String querySQL) {
        if (null == iAsyncExcelService) {
            log.debug("AsyncExcelSingleton iAsyncExcelService addSQLDownloadTask is null");
            throw new RuntimeException(String.format("AsyncExcelSingleton iAsyncExcelService addSQLDownloadTask is null"));
        }
        return iAsyncExcelService.addSQLDownloadTask(username, fileName, querySQL);
    }
}

AsyncExcelController.java:
注意： 由于我是模拟测试，一般除登录外需要传输用户名外，其他时候我们一般不会去传输用户的信息，要获取用户信息直接cookit、session、token中获取就可以了

/**
 * 异步下载excel的控制层
 *
 * @author : 一个爱运动的程序员
 */

@RestController
@RequestMapping("/async/excel")
public class AsyncExcelController {

    @GetMapping("/findDownloadTask")
    public R<List<Record>> findDownloadTask(String username) {
        List<Record> list = AsyncExcelSingleton.getInstance().findDownloadTask(username);
        return R.ok(list);
    }

    @GetMapping("/streamDownloadFile")
    public void streamDownloadFile(HttpServletResponse response, String id) {
        AsyncExcelSingleton.getInstance().streamDownloadFile(response, id);
    }

    @GetMapping("/addSQLDownloadTask")
    public R<String> addSQLDownloadTask(String username, String fileName, String querySQL) {
        String s = AsyncExcelSingleton.getInstance().addSQLDownloadTask(username, fileName, querySQL);
        return R.ok(s);
    }
}

四、功能测试

1、添加下载任务
在这里插入图片描述

2、查询下载任务

3、下载到本地

总结

以上的内容便是此处分享的内容，希望对你有帮助，当然如果你有更好的实现点子或者我的实现有不好的地方欢迎评论区指出，谢谢*✧⁺˚⁺ପ(๑･ω･)੭ु⁾⁾ 好好学习天天向上

源码

GitHub: https://github.com/XIN007-C/async-excel

一个爱运动的程序员

关注

3
点赞
踩
1

收藏

觉得还不错? 一键收藏
4
评论
适配任何数据结构的异步Excel生成(企业级开发)

由于公司的excel生成过于缓慢，有时生成一个excel文件需要等待几十秒甚至几分钟，在等待的时候用户不能跳转其他页面，用户体验不好，可以先看一下，公司excel的下载流程图可以发现问题所在，在查询数据的时候可能会有比较长的时间等待以及excel的生成都需要一定的时间，且数据结构单一，对不同的表单和报表等都需要独立出代码，代码的复用不高，所以进行如下优化。需求源码会放在最后。...
复制链接

扫一扫