Excel百万数据导出功能Java代码

用异步的方式实现Excel导出功能
Controller层

@RestController
@RequestMapping("/coupon/export/")
public class CouponExportController {

	@Autowired
	private CouponExportService couponExportService;
	@Autowired
	private StringRedisTemplate redisService;
	
	@RequestMapping("export.do")
	public JSONResult export(HttpServletRequest request, CouponAccount couponAcc) {
		String userId = ((WebUserDetails) SecurityContextHolder.getContext().getAuthentication().getPrincipal())
				.getUserInfo().getId();
		long time = DateUtils.getDayEndTime(new Date()).getTime() - new Date().getTime();
		redisService.opsForValue().set(String.format(RedisKeys.EXCEL_EXPORT_STATUS, userId), "1");
		//将导出状态存储在redis中,值为开始生成
		//异步执行导出和上传到OSS的逻辑
		//异步的结尾,将上传到OSS后得到的URL存到redis,并且将导出状态置为生成完毕
		couponExportService.export(userId, couponAcc);
		return JSONResult.successInstance(null);
	}
}

***service 层 ***

public interface CouponExportService {

	void export(String userId, CouponAccount couponAcc);
}

实现类层代码

@Service
public class CouponExportServiceImpl implements CouponExportService{

	@Autowired
	private StringRedisTemplate redisService;
	@Autowired
	private MongoTemplate mongoTemplate;
	/**
	*CouponAccount查询时传入的参数(命名不规范)
	*CouponListQueryEntity传入参数实体(进行转化)
	*/
	@Async
	@Override
	public void export(String userId, CouponAccount couponAcc) {
        CouponListQueryEntity couponListQueryEntity = new CouponListQueryEntity();
        couponListQueryEntity.setCouponAccount(couponAcc);
        File file = new File("优惠劵核销表.xlsx");
		FileOutputStream fileOutputStream = null;
		try {
			fileOutputStream = new FileOutputStream(file);
			ExcelBoot.ExportBuilder(fileOutputStream,"优惠劵核销表", CouponListExportEntity.class)
			        .generateMultiSheetStream(couponListQueryEntity, new ExportFunction<CouponListQueryEntity, CouponListExportEntity>(){
			            @Override
			            public List<CouponListExportEntity> pageQuery(CouponListQueryEntity queryQaram, int pageNum, int pageSize){
			                List<CouponListExportEntity> result = getResult(queryQaram,pageNum,pageSize,userId);
			                return result;
			            }
			            @Override
			            public CouponListExportEntity convert(CouponListExportEntity entity) {
			                return entity;
			            }
			        });
			String fileUpload = OssConstant.BUCKET_PRE_URL_CARPLUSGO_OSS + OSSUpload.fileUpload(file, "优惠劵核销表.xlsx");
			long time = DateUtils.getDayEndTime(new Date()).getTime() - new Date().getTime();
			redisService.opsForValue().set(String.format(RedisKeys.EXCEL_EXPORT_URL, userId), fileUpload, time, TimeUnit.MILLISECONDS);
		} catch (Exception e) {
			
		} finally {
			if(Objects.nonNull(fileOutputStream)) {
				try {
					fileOutputStream.close();
				} catch (IOException e) {
				}
			}
			redisService.opsForValue().set(String.format(RedisKeys.EXCEL_EXPORT_STATUS, userId), "0");
		}
	}
//查询数据的业务逻辑
	private List<CouponListExportEntity> getResult(CouponListQueryEntity queryQaram, int pageNum, int pageSize,String userId) {
        Query query = new Query();
        //默认排序:有效的优先显示
        query.with(new Sort(new Sort.Order(Sort.Direction.DESC, "createTime")));
        query.skip((pageNum - 1) * pageSize).limit(pageSize);
        List<CouponListExportEntity> orderList = new ArrayList<>();
        CouponAccount couponAcc = queryQaram.getCouponAccount();
        if (!StringUtils.isEmpty(couponAcc.getCreateTime())) {
            Date now = new Date();
            Date newDate = DateUtils.addDays(now, -180);
            query.addCriteria(Criteria.where("createTime").lte(newDate));
        }
        //乘客手机号
        if (!StringUtils.isEmpty(couponAcc.getUserPhone())) {
            query.addCriteria(Criteria.where("userPhone").is(couponAcc.getUserPhone()));
        }
        ///筛选开始时间-结束时间
        if (Objects.nonNull(couponAcc.getGrantStartTime()) || Objects.nonNull(couponAcc.getGrantEndTime())) {
            if(Objects.isNull(couponAcc.getCreateTime())&& org.apache.commons.lang3.StringUtils.isEmpty(couponAcc.getCouponId())){
                Criteria grantTimeQuery = new Criteria().where("createTime");
                if (Objects.nonNull(couponAcc.getGrantStartTime())) {
                    Calendar calendar = Calendar.getInstance();
                    calendar.setTime(couponAcc.getGrantStartTime());
                    grantTimeQuery.gte(DataUtils.getStartTime(calendar));
                }
                //筛选开始时间-结束时间
                if (Objects.nonNull(couponAcc.getGrantEndTime())) {
                    Calendar calendar = Calendar.getInstance();
                    calendar.setTime(couponAcc.getGrantEndTime());
                    grantTimeQuery.lte(DataUtils.getEndTime(calendar));
                }
                query.addCriteria(grantTimeQuery);
            }
        }
        //使用开始时间-使用结束时间
        if (Objects.nonNull(couponAcc.getUseStartTime()) || Objects.nonNull(couponAcc.getUseEndTime())){
            Criteria useTimeQuery = new Criteria().where("payTime");
            if (Objects.nonNull(couponAcc.getUseStartTime())) {
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(couponAcc.getUseStartTime());
                useTimeQuery.gte(DataUtils.getStartTime(calendar));
            }
            if (Objects.nonNull(couponAcc.getUseEndTime())) {
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(couponAcc.getUseEndTime());
                useTimeQuery.lte(DataUtils.getEndTime(calendar));
            }
            query.addCriteria(useTimeQuery);
        }
        List<CouponAccount> findCouAcc = mongoTemplate.find(query, CouponAccount.class);
        //查询支付记录
        List<String> IdList = findCouAcc.stream().filter(t -> !StringUtils.isEmpty(t.getId())).map(t -> t.getId()).collect(Collectors.toList());
        Query querytp = new Query();
        querytp.addCriteria(Criteria.where("couponId").in(IdList));
        List<TsharePay> tsharePayList = mongoTemplate.find(querytp, TsharePay.class);
        //key;couponId  , value:TsharePay
        Map<String, TsharePay> couponIdPayMap = tsharePayList.stream().filter(t -> Objects.nonNull(t)).collect(Collectors.toMap(t -> t.getCouponId(), Function.identity(), (a, b) -> a));

        //查询订单
        List<String> orderIdList = tsharePayList.stream().filter(t -> !StringUtils.isEmpty(t.getOrderid())).map(t -> t.getOrderid()).collect(Collectors.toList());
        Query queryPay = new Query();
        queryPay.addCriteria(Criteria.where("id").in(orderIdList));
        List<TabOrder> tabOrders = mongoTemplate.find(queryPay, TabOrder.class);
        //key:orderId, value:TabOrder
        Map<String, TabOrder> orderIdOfNumMap = tabOrders.stream().filter(t -> Objects.nonNull(t)).collect(Collectors.toMap(t -> t.getId(), Function.identity(), (a, b) -> a));


        //查询优惠券模板表
        List<String> couponIdList = findCouAcc.stream().filter(t -> !StringUtils.isEmpty(t.getId())).map(t -> t.getCouponId()).collect(Collectors.toList());
        Query queryCouponId = new Query();
        queryCouponId.addCriteria(Criteria.where("id").in(couponIdList));
        List<CouponTemplates> couponTemplatesList = mongoTemplate.find(queryCouponId, CouponTemplates.class);
        //key:coupon , value: CouponTemplates
        Map<String, CouponTemplates> couponIdTemMap = couponTemplatesList.stream().filter(t -> Objects.nonNull(t)).collect(Collectors.toMap(t -> t.getId(), Function.identity(), (a, b) -> a));

        findCouAcc.stream().forEach(couponAccount->{
        	CouponListExportEntity entity = new CouponListExportEntity();
            entity.setCouponId(couponAccount.getCouponId());//优惠卷id
            entity.setGrantTime(couponAccount.getCreateTime());//优惠劵创建时间
            entity.setEndTime(couponAccount.getEndTime());//优惠劵到期时间
            entity.setUserPhone(couponAccount.getUserPhone());//用户手机号
            if (couponAccount.getEndTime().getTime() < System.currentTimeMillis()){
                entity.setStatusName("已过期");
            }
            if (couponAccount.getStatus() == CouponEnums.CouponAccountStatus.PASS_AUDIT.getCode() && couponAccount.getEndTime().getTime() > System.currentTimeMillis()) {
                entity.setStatusName("未过期");
            }

            if (couponAccount.getStatus() == CouponEnums.CouponAccountStatus.USED.getCode()) {
                entity.setStatusName(CouponEnums.CouponAccountStatus.USED.getDesc());
                if (couponIdPayMap.containsKey(couponAccount.getId())){
                    TsharePay tsharePay = couponIdPayMap.get(couponAccount.getId());
                    entity.setActualPayment(tsharePay.getPayAmount());//实际支付金额
                    entity.setPayTime(tsharePay.getPayTime());//支付结束时间
                    if (orderIdOfNumMap.containsKey(tsharePay.getOrderid())){
                        TabOrder tabOrder = orderIdOfNumMap.get(tsharePay.getOrderid());
                        entity.setOrderNum(tabOrder.getOrderNum());//订单编号
                        entity.setOrderType(OrderUtil.getOrderType(tabOrder));
                    }
                }
            }
            if (couponIdTemMap.containsKey(couponAccount.getCouponId())){
                CouponTemplates queryCoupon = couponIdTemMap.get(couponAccount.getCouponId());
                if (!StringUtils.isEmpty(queryCoupon)){
                    entity.setCouponName(queryCoupon.getCouponName());
                    entity.setCouponMoney(queryCoupon.getDiscount());
                }
            }
            orderList.add(entity);
        });
        return orderList;
    }
}

时间工具类

public class DataUtils {
    /*
     * 获取当前天的起始时间
     */
    public static Date getStartTime(Calendar day) {
        day.set(Calendar.HOUR_OF_DAY, 0);
        day.set(Calendar.MINUTE, 0);
        day.set(Calendar.SECOND, 0);
        return day.getTime();
    }
    /*
     * 获取当前天的结束时间
     */
    public static Date getEndTime(Calendar day) {
        day.set(Calendar.HOUR_OF_DAY, 23);
        day.set(Calendar.MINUTE, 59);
        day.set(Calendar.SECOND, 59);
        return day.getTime();
    }
}

要导出的字段实体

@Data
public class CouponListExportEntity {

    @ExportField(columnName = "发放日期")
    private Date grantTime;

    @ExportField(columnName = "使用日期")
    private Date payTime;

    @ExportField(columnName = "到期时间")
    private Date endTime;

    @ExportField(columnName = "优惠券面额")
    private Double couponMoney;

    @ExportField(columnName = "使用订单类型")
    private String orderType;

    @ExportField(columnName = "使用订单号")
    private String orderNum;

    @ExportField(columnName = "订单实付金额")
    private Double actualPayment;

    @ExportField(columnName = "乘客手机号")
    private String userPhone;

    @ExportField(columnName = "劵ID")
    private String couponId;

    @ExportField(columnName = "卷模板名称")
    private String couponName;

    @ExportField(columnName = "状态")
    private String statusName;
}

用的开源框架
GitHub:https://github.com/programmeres/excel-boot

码云:https://gitee.com/nw1992/easy-poi
可以实现自动分sheet导入导出,适用于大量数据导入导出
@Async注解只有在不同的类中才生效,在同一个类中不生效
查询数据转换实体写入数据都要异步进行,否则会存在数据还没有写入直接就返回下载文件的URL,文件导出数据为空

发布了4 篇原创文章 · 获赞 0 · 访问量 72
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 数字20 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览