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

需求

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

方案

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

实现

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 =
				"<p style=\"text-align:left;\">\r\n" +
						"	<br />\r\n" +
						"</p>\r\n" +
						"<p style=\"text-align:left;\">\r\n" +
						"	<span style=\"color:#333333;font-size:14px;\">To  {}:</span> \r\n" +
						"</p>\r\n" +
						"<p style=\"text-align:left;\">\r\n" +
						"	<span style=\"color:#333333;font-size:14px;\">全部订单导出地址为：{}，请于两小时内下载。</span> \r\n" +
						"</p>\r\n" +
						"<p style=\"text-align:left;\">\r\n" +
						"	<span style=\"color:#333333;font-size:14px;\"> 两小时后链接将会失效哦，如失效，请再次发起导出需求。</span>\r\n" +
						"</p>\r\n" +
						"<p style=\"text-align:left;\">\r\n" +
						"	<span style=\"color:#333333;font-size:14px;\">{} </span> \r\n" +
						"<br />";
		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 ，完美优化，性能爆表！