【项目】优化百万数据导出 excel

落日映苍穹つ 2023-10-09 19:52 38阅读 0赞

#### 优化百万数据导出 excel ####

*   *  需求
     *  方案
     *  实现
     *   *  OrderInfoController
         *  写job：OrderInfoExportTask
         *  其中uploadFile()方法：上传文件到cos, 并设置2小时后失效
         *  附录

### 需求 ###

“全部导出订单”失败，原因在于数据量太大导致接口超时。

### 方案 ###

因为是对导出时间不太敏感的业务场景，可以考虑使用该方案使用job进行大数据量的导出。具体方案如下图所示，其中job的时间为每10分钟执行一次。

![在这里插入图片描述][5e824d81ea09490dbfa24cd96c9dbfad.png]

### 实现 ###

#### OrderInfoController ####

/**
    	 * @Description:   导出订单记录列表
    	 */
    	 private final FeignSysUserService feignSysUserService;
    	private final UserExportTaskMapper userExportTaskMapper;
    	
    	@ApiOperation(value = "请求导出订单记录列表")
    	@SysLog("请求导出订单记录列表")
    	@PostMapping("/export")
    	public void export() throws Exception {
        
    		BaseUser user = SecurityUtils.getUser();
    		if (ObjectUtil.isNull(user)) {
        
    			throw new CheckedException("auto datascope, set up security details true");
    		}
    		R r = feignSysUserService.getUser(user.getId(),user.getType(),SecurityConstants.FROM_IN);
    		if (!r.isOk()) {
        
    			log.warn("全部订单下载通知的获取管理员信息远程服务查询失败！");
    			throw new RuntimeException(r.getMsg());
    		}
    		//把object转换成json字符串
    		String json = JSON.toJSONString(r.getData());
    		//把json字符串转换成UserInfo对象
    		UserVO userInfo = JSON.parseObject(json, UserVO.class);
    		if (Objects.isNull(userInfo)) {
        
    			log.warn("全部订单下载通知管理员信息不存在");
    		}
    		UserExportTask userExportTask = new UserExportTask();
    		userExportTask.setId(userInfo.getId());
    		userExportTask.setUsername(userInfo.getUsername());
    		userExportTask.setNickName(userInfo.getNickName());
    		userExportTask.setCreateTime(new Date());
    		userExportTask.setEmail(userInfo.getEmail());
    		userExportTask.setTenantId(userInfo.getTenantId());
    		userExportTaskMapper.insert(userExportTask);
    
    	}

#### 写job：OrderInfoExportTask ####

/**
 * @Description: 商城订单导出任务
 */
 @Slf4j
 @Component("orderInfoExportTask")
 @AllArgsConstructor
 public class OrderInfoExportTask {
 
 
 	private final OrderInfoService orderInfoService;
 
 	private final CosConfigProperties cosConfigProperties;
 
 	private final MailConfigProperties mailConfigProperties;
 
 
 	private final UserExportTaskService userExportTaskService;
 	/**
 	 * @Description: 商城订单导出任务
 	 */
 	@XxlJob("orderInfoExportJobHandler")
 	public ReturnT<String> orderInfoExportJob(String s) throws Exception {
 
 		UserExportTask userExportTask = new UserExportTask();
 		//查询所有的订单
 		List<UserExportTask> userExportTaskList= userExportTaskService.list(Wrappers.query(userExportTask));
 		if (userExportTaskList.size() > 0) {
 
 			for (UserExportTask userExportTask1 : userExportTaskList) {
 
 				//获取现在时间减去userExportTask1的时间
 				long diffInMilliSec = System.currentTimeMillis() - userExportTask1.getCreateTime().getTime();
 				long diffInMinutes = TimeUnit.MILLISECONDS.toMinutes(diffInMilliSec);
 				//如果时间在10分钟以内,则执行
 				if(diffInMinutes <=10){
 
 					OrderInfo orderInfo = new OrderInfo();
 					log.debug("orderInfoExportJob start...");
 					TenantContextHolder.setTenantId(CommonConstants.USER_TYPE_1);
 					List<OrderInfo> list = orderInfoService.selectOrderList(Wrappers.query(orderInfo));
 					//讲数据写入到excel,并上传到cos上,生成地址
 					String responseURL = excelLink(list,userExportTask1.getTenantId());
 					if (StringUtils.isNotEmpty(responseURL)) {
 
 						//异步邮件通知用户去下载
 						notifyUser(responseURL,userExportTask1.getEmail(),userExportTask1.getUsername());
 						log.info("orderInfoExportJob end...");
 					}
 
 				}
 
 			}
 		}
 		return SUCCESS;
 	}
 
 	/**
 	 * @Description: 讲数据写入到excel, 并上传到cos上, 生成地址
 	 */
 	private String excelLink(List<OrderInfo> list,String tenantId) throws Exception {
 
 		String sheetName = "订单记录列表";
 		FileItem fileItem;
 		// 系统管理员类型用户，写入全部数据到excel，客服写入部分数据
 		if (StrUtil.equals(CommonConstants.USER_TYPE_S, tenantId)) {
 
 			List<OrderInfoExport> exportList = OrderInfoExportUtil.convertBean(list);
 			ExcelUtil<OrderInfoExport> util = new ExcelUtil<>(OrderInfoExport.class);
 			fileItem = util.exportExcelNew(exportList, sheetName);
 		} else {
 
 			List<OrderInfoExport> exportList = OrderInfoExportUtil.convertBean(list);
 			List<OrderInfoExportCs> exportCsList = BeanUtil.copyToList(exportList, OrderInfoExportCs.class, null);
 			ExcelUtil<OrderInfoExportCs> util = new ExcelUtil<>(OrderInfoExportCs.class);
 			util.exportExcelNew(exportCsList, sheetName);
 			fileItem = util.exportExcelNew(exportCsList, sheetName);
 		}
 		//存入cos,生成链接地址,并设置2小时后失效
 		return uploadFile(fileItem, sheetName);
 	}
 
 	/**
 	 * @Description: 上传文件到cos, 并设置2小时后失效
 	 */
 	private String uploadFile(FileItem fileItem, String dir) throws Exception {
 
 		MultipartFile multipartFile = new CommonsMultipartFile(fileItem);
 		File file = FileUtils.multipartFileToFile(multipartFile);
 		SysConfigStorage sysConfigStorage = initSysConfigStorage();
 		StorageConfig storageConfig = new StorageConfig();
 		storageConfig.setAccessKeyId(sysConfigStorage.getAccessKeyId());
 		storageConfig.setAccessKeySecret(sysConfigStorage.getAccessKeySecret());
 		storageConfig.setEndpoint(sysConfigStorage.getEndpoint());
 		storageConfig.setBucket(sysConfigStorage.getBucket());
 		QcloudCosUtils qcloudCosUtils = new QcloudCosUtils(storageConfig);
 		return qcloudCosUtils.uploadFileWithDeleteTime(file, dir);
 	}
 
 	/**
 	 * @Description: 获取默认配置
 	 */
 	public SysConfigStorage initSysConfigStorage() {
 
 		SysConfigStorage sysConfigStorage = new SysConfigStorage();
 		sysConfigStorage.setStorageType(SysConfigStorage.StorageTypeEnum.QCLOUDCOS.getType());
 		sysConfigStorage.setAccessKeyId(cosConfigProperties.getSecretId());
 		sysConfigStorage.setAccessKeySecret(cosConfigProperties.getSecretKey());
 		sysConfigStorage.setEndpoint(cosConfigProperties.getRegion());
 		sysConfigStorage.setBucket(cosConfigProperties.getBucket());
 		return sysConfigStorage;
 	}
 
 	/**
 	 * @Description: 下载通知
 	 */
 	public void notifyUser(String responseURL ,String email,String realName) {
 
 		DesvMailUtil mailUtil = new DesvMailUtil(mailConfigProperties.getUsername(), mailConfigProperties.getPassword(), mailConfigProperties.getDomain(), mailConfigProperties.getUri());
 		HashMap<String, Object> params = new HashMap<String, Object>();
 		String today = DateUtil.format(new Date(), DatePattern.CHINESE_DATE_PATTERN);
 		params.put("to",email);
 		params.put("username", "");
 		params.put("title", "全部订单导出结果通知");
 		String msgBody =
 				"\r\n" +
 						"	 \r\n" +
 						"\r\n" +
 						"\r\n" +
 						"	To {}: \r\n" +
 						"\r\n" +
 						"\r\n" +
 						"	全部订单导出地址为：{}，请于两小时内下载。 \r\n" +
 						"\r\n" +
 						"\r\n" +
 						"	 两小时后链接将会失效哦，如失效，请再次发起导出需求。\r\n" +
 						"\r\n" +
 						"\r\n" +
 						"	{} \r\n" +
 						" ";
 		msgBody = StrUtil.format(msgBody, realName, responseURL, today);
 		params.put("item", msgBody);
 
 		try {
 
 			mailUtil.sendEmail(params);
 		} catch (Exception e) {
 
 			log.error("notifyUser error:{}", e.getMessage());
 		}
 		log.info("notifyUser {} sucesss",realName);
 
 	}
 
 }

#### 其中uploadFile()方法：上传文件到cos, 并设置2小时后失效 ####

/**
     * @JL 腾讯cos
     */
    @AllArgsConstructor
    @Slf4j
    public class QcloudCosUtils {
        
    	private final StorageConfig storageConfig;
    
    	/**
    	 * 上传文件(设置文件2小时后自动删除)
    	 */
    
    	public String uploadFileWithDeleteTime(File file, String dir) throws FileNotFoundException {
        
    		// 1 初始化用户身份信息（secretId, secretKey）。
    		String secretId = storageConfig.getAccessKeyId();
    		String secretKey = storageConfig.getAccessKeySecret();
    		COSCredentials cred = new BasicCOSCredentials(secretId, secretKey);
    		// 2 设置 bucket 的区域, COS 地域的简称请参照 
    		// clientConfig 中包含了设置 region, https(默认 http), 超时, 代理等 set 方法, 使用可参见源码或者常见问题 Java SDK 部分。
    		Region region = new Region(storageConfig.getEndpoint());
    		ClientConfig clientConfig = new ClientConfig(region);
    		// 3 生成 cos 客户端。
    		COSClient cosClient = new COSClient(cred, clientConfig);
    		// 指定要上传到的存储桶
    		String bucketName = storageConfig.getBucket();
    		String fileName = file.getName();
    		String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
    		String key = dir + UUID.randomUUID() + "." + suffix;// 指定目标存储路径
    		// 上传Excel文件到COS，设置过期时间为2小时
    		ObjectMetadata metadata = new ObjectMetadata();
    		Date expiration = new Date(System.currentTimeMillis() + 2 * 60 * 60 * 1000);
    		metadata.setExpirationTime(expiration);
    		InputStream inputStream = new FileInputStream(file);
    		PutObjectRequest request = new PutObjectRequest(bucketName, key, inputStream, metadata);
    		// 上传文件。
    		cosClient.putObject(request);
    		// 删除本地Excel文件
    		if (!file.delete()) {
        
    			log.error("Failed to delete file: " + file.getAbsolutePath());
    		}
    		// 关闭OSSClient。
    		cosClient.shutdown();
    		//链接两小时过期
    		URL url = cosClient.generatePresignedUrl(bucketName, key, expiration);
    		String resultStr = "https://" + url.getHost() + "/" + key;
    		return resultStr;
    
    	}
    }

#### 附录 ####

[百万数据导出 excel ，完美优化，性能爆表！][excel]

[5e824d81ea09490dbfa24cd96c9dbfad.png]: https://img-blog.csdnimg.cn/5e824d81ea09490dbfa24cd96c9dbfad.png
[excel]: https://mp.weixin.qq.com/s/H5OrXk6zV9ya007_7GzGRQ