ali easyexcel 按模版导出

/**
     * 分部业绩导出
     * @param param
     * @param response
     * @throws IOException
     */
    @PreAuthorize("@pms.hasPermission('order:order:page')")
    @GetMapping("/exportVipLevel3UserPerformance")
    public void exportVipLevel3UserPerformance(GetVipLevel3UserPerformanceParam param, HttpServletResponse response) throws IOException {
        if(StringUtil.isBlank(param.getVipLevel3User()) || param.getStartTime() == null || param.getEndTime() == null){
            throw new RuntimeException("参数不能为空");
        }

        String enableDistributionProdIds = this.getValueByConfig("enable_distribution_prod_ids");
        List<String> prodIds = Arrays.asList(StringUtils.split(enableDistributionProdIds, ","));


        //获取自己以下所有用户
        List<User>  list0=userService.getLowerLevel(param.getVipLevel3User(),new ArrayList<>());
        list0.add(userService.getById(param.getVipLevel3User()));//加上自己

        List<String> collect1 = list0.stream().map(u -> u.getUserId()).collect(Collectors.toList());

        //查询用户当前下单数量
        List<Order> list = orderService.lambdaQuery()
                .select(Order::getOrderNumber,Order::getProdName,Order::getActualTotal,Order::getActualTotal,Order::getStatus,Order::getUserId,Order::getPayType)
                .in(Order::getUserId, collect1)
                .eq(Order::getIsPayed,1)//已支付
                .between(Order::getPayTime,param.getStartTime(),param.getEndTime())
                .list();

        Map<String,Order> omap=new HashMap<>();
        for(Order order:list){
            omap.put(order.getOrderNumber(),order);
        }

        List<String> collect = list.stream().map(o -> o.getOrderNumber()).collect(Collectors.toList());

        //下单数量
        Integer orderQuantity=0;
        //退款数量
        Integer refundQuantity=0;

        //下单金额
        BigDecimal orderMoney=BigDecimal.ZERO;
        //退款金额
        BigDecimal refundMoney=BigDecimal.ZERO;

        //奖励金额
        BigDecimal rewardMoney=BigDecimal.ZERO;

        List<String> strings = Arrays.asList(UserFundDetailEnum.TYPE_0.getValue(), UserFundDetailEnum.TYPE_1.getValue(), UserFundDetailEnum.TYPE_2.getValue(), UserFundDetailEnum.TYPE_5.getValue());

        List<GetVipLevel3UserPerformanceVo> list6=new ArrayList<>();

        for(String orderNumber:collect){
            GetVipLevel3UserPerformanceVo vo=new GetVipLevel3UserPerformanceVo();
            Order order = omap.get(orderNumber);
            vo.setOrderNumber(order.getOrderNumber());
            vo.setProdName(order.getProdName());
            vo.setActualTotal(order.getActualTotal());

            vo.setPayType(order.getPayType());
            vo.setStatus(order.getStatus());

            if(order.getPayType() == 0){
                vo.setPayTypeName("手动代付");
            }else if(order.getPayType() == 1){
                vo.setPayTypeName("微信支付");
            }else if(order.getPayType() == 2){
                vo.setPayTypeName("支付宝");
            }

            if(order.getStatus() == 1){
                vo.setStatusName("待付款");
            }else if(order.getStatus() == 2){
                vo.setStatusName("待发货");
            }else if(order.getStatus() == 3){
                vo.setStatusName("待收货");
            }else if(order.getStatus() == 4){
                vo.setStatusName("待评价");
            }else if(order.getStatus() == 5){
                vo.setStatusName("成功");
            }else if(order.getStatus() == 6){
                vo.setStatusName("失败");
            }

            User byId = userService.getById(order.getUserId());
            if(byId != null){
                vo.setUserNickName(byId.getNickName());
            }

            List<OrderItem> list1 = orderItemService.lambdaQuery()
                    .select(OrderItem::getOrderItemId,OrderItem::getProdCount,OrderItem::getProductTotalAmount)
                    .eq(OrderItem::getOrderNumber, orderNumber)
                    .in(OrderItem::getProdId, prodIds).list();
            //共下单了几个
            int sum1 = list1.stream().mapToInt(i -> i.getProdCount()).sum();
            Double sum2 = list1.stream().mapToDouble(i -> i.getProductTotalAmount()).sum();
            orderMoney = orderMoney.add(new BigDecimal(sum2.toString()));
            orderQuantity = orderQuantity+sum1;

            vo.setOrderQuantity(sum1);
            vo.setOrderMoney(new BigDecimal(sum2));
            for(OrderItem item:list1){
                List<OrderRefund> list2 = orderRefundService.lambdaQuery()
                        .select(OrderRefund::getGoodsNum,OrderRefund::getRefundAmount)
                        .eq(OrderRefund::getOrderItemId, item.getOrderItemId())
                        .eq(OrderRefund::getReturnMoneySts, 1)//退款成功的
                        .list();

                //本个子订单共退款成功了几单
                int sum = list2.stream().mapToInt(r -> r.getGoodsNum()).sum();
                refundQuantity = refundQuantity + sum;

                BigDecimal reduce = list2.stream().map(r -> r.getRefundAmount()).reduce(BigDecimal.ZERO, BigDecimal::add);
                refundMoney = refundMoney.add(reduce);

                vo.setRefundQuantity(sum);
                vo.setRefundMoney(reduce);
            }


            List<UserFundDetail> list2 = userFundDetailService.lambdaQuery()
                    .select(UserFundDetail::getType,UserFundDetail::getMoney)
                    .eq(UserFundDetail::getOrderNumber, orderNumber)
                    .eq(UserFundDetail::getCategory, UserFundDetailEnum.DISTRIBUTION_BONUS.getValue())
                    .in(UserFundDetail::getType, strings).list();

            //本比订单总奖励金额
            BigDecimal reduce = list2.stream().map(f -> f.getMoney()).reduce(BigDecimal.ZERO, BigDecimal::add);
            rewardMoney = rewardMoney.add(reduce);

            //一级奖励金额
            BigDecimal type0 = list2.stream().filter(f -> StringUtil.equals(f.getType(),UserFundDetailEnum.TYPE_0.getValue())).map(f -> f.getMoney()).reduce(BigDecimal.ZERO, BigDecimal::add);

            //二级奖励金额
            BigDecimal type1 = list2.stream().filter(f -> StringUtil.equals(f.getType(),UserFundDetailEnum.TYPE_1.getValue())).map(f -> f.getMoney()).reduce(BigDecimal.ZERO, BigDecimal::add);

            //三级奖励金额
            BigDecimal type2 = list2.stream().filter(f -> StringUtil.equals(f.getType(),UserFundDetailEnum.TYPE_2.getValue())).map(f -> f.getMoney()).reduce(BigDecimal.ZERO, BigDecimal::add);

            //老上级奖励
            BigDecimal type5 = list2.stream().filter(f -> StringUtil.equals(f.getType(),UserFundDetailEnum.TYPE_5.getValue())).map(f -> f.getMoney()).reduce(BigDecimal.ZERO, BigDecimal::add);

            vo.setLevel1(type0);
            vo.setLevel2(type1);
            vo.setLevel3(type2);
            vo.setReward2YuanMoney(type5);

            list6.add(vo);
        }

        Map<String,Object> rmap=new HashMap<>();
        rmap.put("orderQuantity",orderQuantity);
        rmap.put("refundQuantity",refundQuantity);
        rmap.put("orderMoney",orderMoney);
        rmap.put("refundMoney",refundMoney);
        rmap.put("rewardMoney",rewardMoney);

        //rmap.put("orderList",list6);

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");

        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("分部业绩", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        ClassPathResource classPathResource = new ClassPathResource("excelTemplate/exportVipLevel3UserPerformance.xlsx");

        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(classPathResource.getInputStream()).build();

        WriteSheet writeSheet = EasyExcel.writerSheet().build();

        //写入list
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        excelWriter.fill(list6, fillConfig, writeSheet);
        //excelWriter.fill(data(), fillConfig, writeSheet);

        //写入map
        //map.put("text", "2019年10月9日13:28:28");
        //map.put("total", rmap);

        excelWriter.fill(rmap, writeSheet);
        //填充完成
        excelWriter.finish();
    }

const exportVipLevel3UserPerformance = () => {
  http({
    url: http.adornUrl('/order/financeStatistics/exportVipLevel3UserPerformance'),
    method: 'get',
    params: http.adornParams({
      startTime: dateRange.value === null ? null : dateRange.value[0], // 开始时间
      endTime: dateRange.value === null ? null : dateRange.value[1], // 结束时间
      vipLevel3User: queryParameter.uid
    }),
    responseType: 'blob' // 解决文件下载乱码问题
  })
    .then(({ data }) => {
      const blob = new Blob([data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8' })
      const fileName = '分部业绩.xlsx'
      const elink = document.createElement('a')
      if ('download' in elink) { // 非IE下载
        elink.download = fileName
        elink.style.display = 'none'
        elink.href = URL.createObjectURL(blob)
        document.body.appendChild(elink)
        elink.click()
        URL.revokeObjectURL(elink.href) // 释放URL 对象
        document.body.removeChild(elink)
      } else { // IE10+下载
        navigator.msSaveBlob(blob, fileName)
      }
    })
}

/**
 * get请求参数处理
 * @param {*} params 参数对象
 * @param {*} openDefultParams 是否开启默认参数?
 */
http.adornParams = (params = {}, openDefultParams = true) => {
  const defaults = {
    t: Date.now()
  }
  return openDefultParams ? merge(defaults, params) : params
}

/**
 * 请求地址处理
 * @param {*} actionName action方法名称
 */
http.adornUrl = actionName => {
  return import.meta.env.VITE_APP_BASE_API + actionName
}