优化百万数据导出 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;
}
}
文章评论