excel异步导出下载

饮风丶欤

已于 2023-11-10 14:21:01 修改

阅读量423

点赞数

文章标签： excel

于 2023-08-07 14:17:51 首次发布

本文链接：https://blog.csdn.net/weixin_42867178/article/details/132145070

版权

业务场景：业务需要生成某种复杂的excel报表，因为涉及图片和复杂的样式，导出时间长

讨论结果方案：前端触发导出功能，后端立即返回报表正在生成，请到下载列表统一等待下载

上代码：

一、统一下载列表，对应一个实体，表结构忽略

/**
 * 文件导出
 * yf
 * xxxx-xx-xx
 */
@Data
public class Export implements Reaction {
	/**
	* 主键
	*/
	private String id;

	/**
	* 文件名称
	*/
	private String fileName;

	/**
	* 文件状态1生成中2已生成3生成失败
	*/
	private Integer status;

	/**
	* 创建时间
	*/
	private Date createTime;

	/**
	* 创建人id
	*/
	private String createId;

	/**
	* 导出类型1绿化周报导出
	*/
	private Integer exportType;

	/**
	* 删除状态：0正常，1删除
	*/
	private Integer isDel;

	/**
	 * 文件下载地址
	 */
	private String url;

	/**
	 * 更新时间
	 */
	private Date updateTime;

}

导出文件辅助类

/**
 * @description: 导出文件信息
 * @author: yf
 * @create: xxxx-xx-xx
 */
@Data
@NoArgsConstructor
public class ExportFile implements Reaction {

    /**
     * 导出记录ID
     */
    private String exportId;

    /**
     * 类路径名
     * com.xx.xx.modular.export.bean.ExportFile
     */
    private String classPathName;

    /**
     * 记录名称
     */
    private String fileName;

    /**
     * 参数
     */
    private JSONObject params;

    /**
     * 数据
     */
    private Object data;

    public ExportFile(String exportId, String fileName, Object data, String classPathName){
        this.exportId = exportId;
        this.fileName = fileName;
        this.data = data;
        this.classPathName = classPathName;
    }

    public ExportFile(String exportId, String fileName, JSONObject params, String classPathName){
        this.exportId = exportId;
        this.fileName = fileName;
        this.params = params;
        this.classPathName = classPathName;
    }

}

导出文件工具类

/**
 * @description: 导出
 * @author: yf
 * @create: xxxx-xx-xx
 */
@Component
public class ExporUtil {

    /**
     * 导出Service
     */
    @Autowired
    private ExportService exportService;

    private static ExportService exportStaticService;

    @PostConstruct
    public void init() {
        exportStaticService = exportService;
    }

    /**
     * 插入导出记录
     */
    public static void addExport(ExportFile exportFile){
        //DB
        exportStaticService.addExport(exportFile.getExportId(), exportFile.getFileName());
        //redis队列
        SysRedis.setQueueMessage(RedisMessageQueue.EXPORT_FILE_QUEUE, exportFile);
    }

    /**
     * 插入导出记录
     */
    public static void addSuccessExport(Export export){
        exportStaticService.addSucessExport(export);
    }

    /**
     * 更新导出记录
     * @param id ID
     * @param url 文件url
     * @param status 状态 1生成中2已生成3生成失败
     */
    public static void updateExport(String id, String url, int status){
        Export export = new Export();
        export.setId(id);
        export.setUrl(url);
        export.setUpdateTime(new Date());
        export.setStatus(status);
        export.setUpdateTime(new Date());
        exportStaticService.updateExport(export);
    }
}

我们将下载任务放在redis中，以redis队列执行，去排队生成excel。这么做的好处，节省资源，防止cpu空转.

redis队列配置类

/**
 * @description: redis队列
 * @author: yf
 * @create: xxxx-xx-xx
 */
@Slf4j
@Service
@Conditional(IotNodeCondition.class)
public class RedisMessageQueue implements CommandLineRunner {

    /**
     * 文件生成队列
     */
    public static final String EXPORT_FILE_QUEUE = "export_file_queue";


    @Value("${gofdfs.filePrefix}")
    private String filePrefix;

    @Autowired
    private RedisTemplate<String, Object> rTemplate;
    private static RedisTemplate<String, Object> redisTemplate;

    private static Class<?> ExportFileServiceClazz = ExportFileService.class;

    @PostConstruct
    public void init() {
        redisTemplate = rTemplate;
    }

    @Override
    public void run(String... args) throws Exception {
        blockingConsume();
    }


    public void blockingConsume() {
        // 创建一个单线程的线程池
        ExecutorService single = Executors.newSingleThreadExecutor();
        single.execute(() -> {
            while (true) {
                try {
                    //使用BRPOP命令进行阻塞式读取，这里没有读到队列数据时阻塞600s，超时或者读取到数据后，再开始下一轮读取(防止cpu空转)，阻塞时间不能超过redis连接超时时间
                    ExportFile exportFile = (ExportFile) redisTemplate.opsForList().rightPop(EXPORT_FILE_QUEUE, 600, TimeUnit.SECONDS);
                    if (null == exportFile){
                        continue;
                    }
                    //通过反射调用对应模块方法执行文件写入操作
                    Class<?> clazz = Class.forName(exportFile.getClassPathName());
                    if (!ExportFileServiceClazz.isAssignableFrom(clazz)){
                        continue;
                    }
                    Object obj = clazz.newInstance();
                    Method method = clazz.getMethod("handleExport", ExportFile.class);
                    method.invoke(obj, exportFile);
                    log.info("blockingConsume-message={}" + exportFile);
                } catch (Exception e) {
                    log.info("blockingConsume-exception{}", e);
                } catch (Error e){
                    log.info("blockingConsume-error{}", e);
                }
            }
        });
    }
}

二、业务逻辑代码

/**
	 * 计划导出EXCEL
	 */
	@Override
	public int excel(JSONObject params){
		List<Plane> planeList = planeMapper.selectPlaneByList(params).getList();
		if (ListUtile.isEmpty(planeList)){
			return 0;
		}
		//插入导出记录
		String title = BUSINESS_TYPE_MAP.get(params.getInteger("businessType")) + "巡查任务清单";
		ExporUtil.addExport(new ExportFile(StrUtile.getId(), title + StrUtile.getId() + ".xlsx", params, PlanExport.class.getName()));
		return planeList.size();
	}

具体生成excel的代码

/**
 * 计划导出
 */
@Slf4j
@Component
public class PlanExport implements ExportFileService {

    @Autowired
    private PlaneMapper planeMapper;
    private static PlaneMapper planeStaticMapper;

    @PostConstruct
    public void init() {
        planeStaticMapper = planeMapper;
    }

    @Override
    public void handleExport(ExportFile exportFile) {
        try {
            Thread.sleep(10000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        JSONObject params = exportFile.getParams();
        String url = null;
        int status = 3;
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            List<Plane> planeList = planeStaticMapper.selectPlaneByList(params).getList();
            if (ListUtile.isEmpty(planeList)){
                return;
            }
            String title = BUSINESS_TYPE_MAP.get(params.getInteger("businessType")) + "巡查任务清单";
            List<List<String>> head = Arrays.asList(Arrays.asList(title,"业务类型名称"),
                    Arrays.asList(title,"任务名称"),
                    Arrays.asList(title,"所属公园"),
                    Arrays.asList(title,"巡查方"),
                    Arrays.asList(title,"巡查类型"),
                    Arrays.asList(title,"巡查频次"),
                    Arrays.asList(title,"巡查方式"),
                    Arrays.asList(title,"任务指派人"),
                    Arrays.asList(title,"任务执行人"),
                    Arrays.asList(title,"完成情况"));
            List<List<String>> dataList = new ArrayList<>();
            planeList.forEach(plane -> {
                dataList.add(Arrays.asList(6 == plane.getBusinessType() ? BUSINESS_TYPE_MAP.get(plane.getBusinessType()) + "/" + plane.getSafeManageName() : BUSINESS_TYPE_MAP.get(plane.getBusinessType()),
                        plane.getPlaneName(),
                        plane.getParkName(),
                        PATROL_UNIT_MAP.get(plane.getPatrolUnit()),
                        PATROL_TYPE_MAP.get(plane.getPlanType()),
                        plane.getPatrolFrequency() + PATROL_FREQUENCY_UNIT_MAP.get(plane.getPlanType()),
                        PATROL_MODE_MAP.get(plane.getPatrolMode()),
                        plane.getCreateUserName(),
                        plane.getExecuteUserName(),
                        plane.getYwcTaskCount() + "/" + plane.getTaskCount()));
            });
            //自动列宽
            EasyExcel.write(os)
                    .head(head)
                    .sheet(title)
                    .doWrite(dataList);
            url = SysFile.uploadFile(os.toByteArray(), exportFile.getFileName(), true);
            status = 2;
        } catch (Exception e) {
            log.info("PlanExport-exception={}", e);
        } finally {
            if (null != os) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            ExporUtil.updateExport(exportFile.getExportId(), url, status);
        }
    }
}

总结：以上一套设计，可以作为一套模板，以后所有的异步生成文件都可以套用这套模板，只用关心单独的业务和导出文件的样式