关于导出速度慢的优化处理

优化了之前的导出收款功能。贴出代码仅供参考。有大神提出问题我会虚心接受。

import cn.hutool.core.date.DateUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.google.common.collect.Lists;
import com.google.common.util.concurrent.ThreadFactoryBuilder;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

/**
 * @author 刘能
 * @since 2021/2/2/9:17
 */
@Service
@Slf4j
public class ExportPayRecordServiceImpl implements ExportPayRecordService {

    @Resource
    private ProductCampusService productCampusService;
    @Resource
    private ExportPayRecordSearchMapper exportPayRecordSearchMapper;
    @Resource
    private ExportFinanceService exportFinanceService;
    @Resource
    private ExcelExport excelExport;

    @Resource
    private DictService dictService;
    @Resource
    private AuthorizeFeignService authorizeFeignService;
    @Resource
    private OrderRefundDetailMapper refundMapper;
    @Resource
    private BusinessOwnerMapper businessOwnerMapper;

    @Resource
    private UserInfoService userInfoService;
    @Resource
    private CommonOperateLogsMapper commonOperateLogsMapper;

    private static final Integer COUNT = 2000;
    @Value("${cis.product_app_id}")
    private String productAppId;

    private static final ThreadPoolExecutor EXECUTOR = new ThreadPoolExecutor(20, 20,
            0L, TimeUnit.MILLISECONDS,
            new LinkedBlockingQueue<>(100),
            new ThreadFactoryBuilder().setNameFormat("export-pay-record-info-%d").build(),
            new ThreadPoolExecutor.CallerRunsPolicy());

    /**
     * 新导出收款记录接口
     * @param request request
     * @param response response
     */
    @Override
    public void payRecord(HttpServletRequest request, HttpServletResponse response){
        Map<String, Object> requestParams = this.requestParams(request);
        PageReq req = new PageReq();
        req.setSearch(requestParams);
        UserContext userContext = UserThreadLocal.get();

        PageHelper.clearPage();
        PageHelper.startPage(1, 1);
        req.setPage(1);
        req.setPageNum(1);
        log.info("查询开始时间: {}", System.currentTimeMillis());
        List<ExportPayRecordVo> toExportPayRecordVoListPage1 = exportPayRecordSearchMapper.payRecords(req);

        this.logRecord(userContext, requestParams);

        PageInfo<ExportPayRecordVo> pageInfo = new PageInfo<>(toExportPayRecordVoListPage1);
        int totalCount = pageInfo.getPages();
        BigDecimal totalPageB = new BigDecimal(totalCount).divide(new BigDecimal(2000), BigDecimal.ROUND_CEILING);
        int totalPage = totalPageB.intValue();

        CountDownLatch searchCount = new CountDownLatch(totalPage);
        AtomicInteger count = new AtomicInteger(0);
        List<ExportPayRecordVo> toExportPayRecordVoList = Collections.synchronizedList(new ArrayList<>());
        for (int a = 0; a < totalPage; a ++){
            AtomicInteger page = new AtomicInteger(a + 1);
            EXECUTOR.execute(() -> {
                try {
                    PageHelper.clearPage();
                    PageHelper.startPage(page.intValue(), COUNT);
                    req.setPage(page.intValue());
                    req.setPageNum(COUNT);
                    List<ExportPayRecordVo> exportPayRecordVosPage = exportPayRecordSearchMapper.payRecords(req);
                    toExportPayRecordVoList.addAll(exportPayRecordVosPage);
                    count.addAndGet(1);
                }catch (Exception e){
                    e.printStackTrace();
                    throw new RestException(Constant.THREAD_EXCEPTION, Constant.THREAD_EXCEPTION_MESSAGE);
                }finally {
                    searchCount.countDown();
                }
            });
        }

        try {
            if (!searchCount.await(ExportConstant.SEARCH_WAIT_TIME, TimeUnit.SECONDS)){
                log.info("主线程等待子线程时间过长!");
                throw new Exception();
            }
        }catch (Exception e){
            e.printStackTrace();
            throw new RestException(Constant.EXPORT_TIME_OUT, Constant.EXPORT_TIME_OUT_MESSAGE);
        }

        if (count.get() != totalPage){
            log.error("有线程查询报错: 总页数:{} ，查询页数;{}", totalPage, count);
            throw new RestException(Constant.THREAD_EXCEPTION, Constant.THREAD_EXCEPTION_MESSAGE);
        }

        log.info("查询结束时间: {}", System.currentTimeMillis());

        List<ExportPayRecordVo> list = new ArrayList<>();
        // 只提取内部挂账跟外部挂账导出数据
        String accountAmount = request.getParameter("accountAmount");
        if(ExportConstant.ZERO.equals(accountAmount)){
            List<ExportPayRecordVo> toExportPayRecordVos = toExportPayRecordVoList.stream().filter(toExportPayRecordVo -> null != toExportPayRecordVo.getAccountAmountId()).collect(Collectors.toList());
            list.addAll(toExportPayRecordVos);
        }else{
            list.addAll(toExportPayRecordVoList);
        }

        BigDecimal totalAmount = list.stream().map(ExportPayRecordVo::getRecordPayAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        LinkedHashMap<String, String> titleMap = this.recordTitleMap();

        ExcelPageUtil excelPageUtil = new ExcelPageUtil();

        BigDecimal total = new BigDecimal(list.size());
        BigDecimal divide = total.divide(new BigDecimal(COUNT), BigDecimal.ROUND_CEILING);
        CountDownLatch begin = new CountDownLatch(divide.intValue());

        Map<String, Map<Long, Object>> feignSearchMap = this.feignSearch(userContext, req);
        log.info("处理导出数据开始时间: {}", System.currentTimeMillis());

        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);

            ThreadConfig threadConfig = new ThreadConfig();
            Executor executor = threadConfig.getAsyncExecutor();
            List<ExportPayRecordVo> resultList = Collections.synchronizedList(Lists.newArrayList());
            Map<String, String> map = exportFinanceService.listBusiness();

            AtomicInteger excelCount = new AtomicInteger(0);
            for (int num = 0; num < divide.intValue(); num ++) {
                List<ExportPayRecordVo> toExportPayRecordVos = excelPageUtil.handlerListPage(list, num + 1, COUNT);
                executor.execute(() -> {
                    try {
                        UserThreadLocal.set(userContext);
                        JSONArray ja = new JSONArray();
                        this.excelPayRecordChinese(toExportPayRecordVos, ja, feignSearchMap, map);
                        List<ExportPayRecordVo> threadList = ja.toJavaList(ExportPayRecordVo.class);
                        resultList.addAll(threadList);
                        excelCount.addAndGet(1);
                    }catch (Exception e){
                        e.printStackTrace();
                        log.error("处理数据错误信息:{}" + e.getMessage());
                        throw new RestException(Constant.THREAD_DEAL_EXCEPTION, Constant.THREAD_DEAL_EXCEPTION_MESSAGE);
                    }finally {
                        begin.countDown();
                    }
                });
            }

            if (!begin.await(ExportConstant.WAIT_TIME, TimeUnit.SECONDS)){
                log.info("主线程等待子线程时间过长!");
                throw new RestException(Constant.EXPORT_TIME_OUT, Constant.EXPORT_TIME_OUT_MESSAGE);
            }
            log.info("处理导出数据结束时间: {}", System.currentTimeMillis());

            if (excelCount.get() != divide.intValue()){
                log.error("有线程导出报错: 总页数:{} ，查询页数;{}", divide.intValue(), excelCount);
                throw new RestException(Constant.THREAD_DEAL_EXCEPTION, Constant.THREAD_DEAL_EXCEPTION_MESSAGE);
            }

            log.info("导出开始时间: {}", System.currentTimeMillis());
            excelExport.exportExcel("收款记录导出", titleMap, JSONObject.parseArray(JSONObject.toJSONString(resultList)), response, totalAmount);
            log.info("导出结束时间: {}", System.currentTimeMillis());
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();
        }catch (RestException e){
            if (Constant.EXPORT_TIME_OUT.equals(e.getCode())){
                throw new RestException(Constant.EXPORT_TIME_OUT, Constant.EXPORT_TIME_OUT_MESSAGE);
            }
            if (Constant.THREAD_DEAL_EXCEPTION.equals(e.getCode())){
                throw new RestException(Constant.THREAD_DEAL_EXCEPTION, Constant.THREAD_DEAL_EXCEPTION_MESSAGE);
            }
        }catch (Exception e){
            log.error("新导出收款记录接口报错; {}", e.getMessage());
            throw new RestException(Constant.EXPORT_PAY_FAIL, Constant.EXPORT_PAY_FAIL_MESSAGE);
        }
    }

    /**
     * 操作日志记录
     * @param userContext userContext
     * @param requestParams requestParams
     */
    private void logRecord(UserContext userContext, Map<String, Object> requestParams) {
        IdGenerator idGenerator = new IdGenerator();
        //生成流水号记录一次操作留下记录的标识
        String serialNumber = GuidGenerator.generateUUID();

        CommonOperateLogs commonOperateLogs = new CommonOperateLogs();
        commonOperateLogs.setId(idGenerator.nextId());
        commonOperateLogs.setOperateOpenId(userContext.getOpenId());
        commonOperateLogs.setOperateName(userContext.getName());
        commonOperateLogs.setOrgId(Long.parseLong(userContext.getOrgId()));
        commonOperateLogs.setLogSource("EXPORT_PAY_RECORD");
        commonOperateLogs.setLogSourceDetail("导出收款记录");
        commonOperateLogs.setLogJson("导出条件: " + JSONObject.toJSONString(requestParams));
        commonOperateLogs.setCreateTime(Integer.parseInt(String.valueOf(System.currentTimeMillis() / 1000)));
        commonOperateLogs.setSerialNumber(serialNumber);

        commonOperateLogsMapper.insert(commonOperateLogs);
    }

    /**
     * 处理请求参数
     * @param request HttpServletRequest
     * @return Map
     */
    private Map<String, Object> requestParams(HttpServletRequest request){
        Map<String, Object> map = new HashMap<>(16);
        String timeBegin = request.getParameter("timeBegin");
        String timeEnd = request.getParameter("timeEnd");
        String payType = request.getParameter("payType");
        String payStatus = request.getParameter("payStatus");
        String status = request.getParameter("status");
        String uopenId = request.getParameter("uopenId");
        String orgIds = request.getParameter("orgIds");
        String orgId = request.getParameter("orgId");

        UserContext userContext = new UserContext();

        map.put("uopenId", uopenId);
        userContext.setOpenId(uopenId);
        if (null != timeBegin && !"".equals(timeBegin)) {
            map.put("timeBegin", timeBegin);
        }
        if (null != timeEnd && !"".equals(timeEnd)) {
            map.put("timeEnd", timeEnd);
        }
        if (null != payType && !"".equals(payType)) {
            map.put("payType", payType);
        }
        if (null != payStatus && !"".equals(payStatus)) {
            map.put("payStatus", payStatus);
        }
        if (null != status && !"".equals(status)) {
            map.put("status", status);
        }
        if (null != orgId && !"".equals(orgId)) {
            map.put("mechanismId", orgId);
            userContext.setOrgId(orgId);
        }

        //查询当前人得所属机构
        ResultBean<UserNewVO> userInfo = userInfoService.getUserInfoByOpenId(productAppId, uopenId);

        List<Long> orgIdLists = new ArrayList<>();
        log.info(ExportConstant.USER_INFO, JSONObject.toJSONString(userInfo));
        if (null != userInfo){
            UserNewVO data = userInfo.getData();
            userContext.setName(data.getUserName());
            CopyUtil.copyProperties(userContext, data);
            orgIdLists = data.getOrgList().stream().map(OrgVO::getId).collect(Collectors.toList());
        }
        List<Long> orgIdList;
        if (null != orgIds && !"".equals(orgIds)) {
            orgIdList = Arrays.stream(orgIds.split(","))
                    .map(s ->Long.parseLong(s.trim())).collect(Collectors.toList());

            //求机构交集
            List<Long> orgParam = new ArrayList<>(orgIdList);
            orgIdLists.retainAll(orgParam);
        }else{
            orgIdList = new ArrayList<>(orgIdLists);
        }
        map.put("orgIds", orgIdList);

        String lineIdStr = request.getParameter("productLineIds");
        if (StringUtils.isNotBlank(lineIdStr)) {
            List<String> productLineIds = Arrays.asList(lineIdStr.split(","));
            map.put("productLineIds", productLineIds);
            List<Long> orderIdList = exportPayRecordSearchMapper.listOrderIdByProductLineIds(map);
            map.put("orderIdList", orderIdList);
        }

        ResultBean<List<Long>> campusList = productCampusService.listProductCampusByOpenIdAndOrgIdList(uopenId, orgIdList);
        log.info(ExportConstant.USER_CAMPUS_ID, JSONObject.toJSONString(campusList));
        map.put("campusIds", campusList.getData());

        UserThreadLocal.set(userContext);

        return map;
    }

    private BigDecimal keepTwoDigits(BigDecimal price) {
        if (null != price) {
            price = price.setScale(2, BigDecimal.ROUND_HALF_UP);
        } else {
            price = new BigDecimal(0);
        }

        return price;
    }

    private LinkedHashMap<String, String> recordTitleMap() {
        LinkedHashMap<String, String> headMap = new LinkedHashMap<>();
        headMap.put("orderNo", "订单编号");
        headMap.put("payId", "收款流水号");
        headMap.put("payTime", "收款时间");
        headMap.put("receiveSupplierName", "收款商户");
        headMap.put("accountType", "收款类型");
        headMap.put("payMethod", "收款方式");
        headMap.put("recordPayAmount", "收费金额");
        headMap.put("payStatus", "收款状态");
        headMap.put("invoiceNo", "收据单号");
        headMap.put("documentNo", "发票单号");
        headMap.put("accountAmount", "结算金额");
        headMap.put("customerName", "客户");
        headMap.put("commissionAmount", "佣金/代理费");
        headMap.put("earnestAmount", "预付款抵扣-实时");
        headMap.put("changeAmount", "暂存抵扣-实时");
        headMap.put("orderStatus", "订单状态");
        headMap.put("orderSource", "订单来源");
        headMap.put("gatherCompleteTime", "收款完成时间");
        headMap.put("refundCompleteTime", "退费完成时间");
        headMap.put("agreementFee", "保过预留金");
        headMap.put("businessOwnerVOrate", "业绩归属");
        headMap.put("campusName", "业绩校区");
        headMap.put("campusLeaderName", "业绩校区负责人");
        headMap.put("campusCostCenterName", "业绩校区成本中心");
        headMap.put("classCampusName", "上课校区");
        headMap.put("classCostCenterName", "上课校区成本中心");
        headMap.put("shopName", "报名点");
        headMap.put("leaderName", "报名点负责人");
        headMap.put("productLineName", "业务线");
        headMap.put("payedType", "收款类型");
        headMap.put("payWay", "支付渠道");
        headMap.put("feeNo", "收据号");
        headMap.put("approverName", "审批人");
        headMap.put("approveTime", "审批时间");
        headMap.put("status", "审核状态");
        headMap.put("approveRemark", "审批备注");
        headMap.put("arrivalAccountDateTime", "到账时间");
        headMap.put("payRecordRemark", "收款备注");
        headMap.put("productNames", "产品名称");
        headMap.put("productType", "商品类型");
        headMap.put("productCategoryName", "产品分类");
        headMap.put("name", "学员姓名");
        headMap.put("code", "学员学号");
        headMap.put("year", "考研届数");
        headMap.put("mobile", "学员手机号");
        headMap.put("idCard", "身份证号");
        headMap.put("gradeStr", "年级");
        headMap.put("qq", "QQ号码");
        headMap.put("nowSchool", "就读院校");
        headMap.put("nowMajor", "就读专业");
        headMap.put("aimMajor", "目标专业");
        headMap.put("source", "学员来源");
        headMap.put("customerRemark", "学员备注");
        headMap.put("orderOwnerName", "咨询师");
        headMap.put("remark", "订单备注");
        headMap.put("recommendPeople", "订单推荐人");
        headMap.put("orderClassification", "订单类型");
        headMap.put("guestName", "客户");
        headMap.put("approveUserName", "财务核对");
        headMap.put("approveClockTime", "核对时间");
        headMap.put("crmCreateDate", "CRM学员创建时间");
        headMap.put("agentPeople", "代理人");
        headMap.put("agentYear", "代理年份");
        headMap.put("intention", "学员意向");
        headMap.put("orderCreateTime", "订单创建时间");
        headMap.put("orgName", "机构名称");

        return headMap;
    }

    private <T> void turnObject(Map<Long, T> t, String title, Map<String, Map<Long, Object>> resultMap){
        resultMap.put(title, t.entrySet().stream().collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue)));
    }

    /**
     * 导出用的字典等集合
     * @param userContext userContext
     * @param pageReq pageReq
     * @return Map
     */
    private Map<String, Map<Long, Object>> feignSearch(UserContext userContext, PageReq pageReq){
        PageReq req = new PageReq();
        Map<String, Object> searchMap = new HashMap<>(5);
        Map<String, Map<Long, Object>> resultMap = new HashMap<>(8);

        String orgId = userContext.getOrgId();
        searchMap.put("mechanismId", orgId);
        searchMap.put("dictCode", ExportConstant.STUDENTSSOURCE);
        // 让删除的学员来源可查
        searchMap.put("dr", ExportConstant.TWO);
        req.setSearch(searchMap);

        ResultBean<List<DictsVo>> resultBean = dictService.searchDict(req);
        if (resultBean != null && resultBean.getCode() == 0 && resultBean.getData() != null && resultBean.getData().size() > 0) {
            turnObject(resultBean.getData().stream().collect(Collectors.toMap(DictsVo::getId, JSONObject::toJSONString)),
                    "dictMap", resultMap);
        }

        // 机构信息
        ResultBean<List<OrgVO>> orgResult = authorizeFeignService.getOrgInfoAll();
        if (orgResult != null && orgResult.getCode() == 0 && orgResult.getData() != null && orgResult.getData().size() > 0) {
            turnObject(orgResult.getData().stream().collect(Collectors.toMap(OrgVO::getId, JSONObject::toJSONString)),
                    "orgMap", resultMap);
        }

        // 校区报名点信息
        ResultBean<List<ProductCampusDto>> campusEnterByOrg = productCampusService.getCampusEnterByOrg(Long.parseLong(orgId));
        if (null != campusEnterByOrg && null != campusEnterByOrg.getData() && 0 < campusEnterByOrg.getData().size()){
            turnObject(campusEnterByOrg.getData().stream().collect(Collectors.toMap(ProductCampusDto::getId, JSONObject::toJSONString)),
                    "campusMap", resultMap);
        }

        // 年级
        ResultBean<List<DictsVo>> gradeResultBean = dictService.listDictByType(ExportConstant.GRADE, orgId);
        if (null != gradeResultBean && null != gradeResultBean.getData()) {
            List<DictsVo> dictList = gradeResultBean.getData();
            if (!CollectionUtils.isEmpty(dictList)) {
                turnObject(dictList.stream().collect(Collectors.toMap(DictsVo::getId, DictsVo::getDictValue)),
                        "gradeMap", resultMap);
            }
        }

        // 订单来源
        ResultBean<List<DictsVo>> orderSourceResultBean = dictService.selectIdByDictTypeCode(ExportConstant.ORDERSOURCE);
        if (null != orderSourceResultBean && null != orderSourceResultBean.getData() && 0 < orderSourceResultBean.getData().size()) {
            turnObject(orderSourceResultBean.getData().stream().collect(Collectors.toMap(DictsVo::getId, DictsVo::getDictValue)),
                    "sourceMap", resultMap);
        }

        // 咨询师
        List<BusinessOwner> businessOwnerList = businessOwnerMapper.listOwnewByOrderBuyIdsPayRecord(pageReq);
        if (businessOwnerList.size()>0){
            turnObject(businessOwnerList.stream().collect(Collectors.groupingBy(BusinessOwner::getOrderBuyId)),
                    "businessOwnerMap", resultMap);
        }

        return resultMap;
    }

    /**
     * 处理导出数据
     * @param exportPayRecordVos 结果集
     * @param ja JSONArray
     * @param feignSearchMap 导出用的字典等集合
     * @param map 调用配置中心接口返回值
     */
    private void excelPayRecordChinese(List<ExportPayRecordVo> exportPayRecordVos, JSONArray ja, Map<String, Map<Long, Object>> feignSearchMap, Map<String, String> map){
        Map<String, String> gatherCompleteTimeCollect = exportPayRecordVos.stream().collect(Collectors.toMap(ExportPayRecordVo::getOrderNo,
                ExportPayRecordVo::getUnpayTime,
                (String time1, String time2) -> {
                    if (Integer.valueOf(time1) > Integer.valueOf(time2)) {
                        return time1;
                    } else {
                        return time2;
                    }
                }));
        List<ExportPayRecordVo> collect = exportPayRecordVos.stream().filter(a -> "6".equals(a.getOrderStatus())).collect(Collectors.toList());
        List<Long> refundIds = collect.stream().map(ExportPayRecordVo::getId).collect(Collectors.toList());
        List<OrderRefundDetail> orderRefundDetails;
        Map<Long, Integer> refundTimeCollect = new HashMap<>(20);
        if (0 < refundIds.size()){
            orderRefundDetails = refundMapper.refundByBuyIds(refundIds);
            refundTimeCollect = orderRefundDetails.stream().collect(Collectors.toMap(OrderRefundDetail::getOrderBuyId,
                    orderRefundDetail -> orderRefundDetail.getRefundTime() == null ? Integer.valueOf(String.valueOf(orderRefundDetail.getCreateTime().getTime() / 1000))
            : orderRefundDetail.getRefundTime(),
                    (entity1, entity2) -> entity1));
        }

        for (ExportPayRecordVo vo : exportPayRecordVos) {
            switch (vo.getStatus() == null ? "" : vo.getStatus()) {
                case ExportConstant.ZERO:
                    vo.setStatus("支付记录已确认");
                    break;
                case ExportConstant.ONE:
                    vo.setStatus("线下支付审核中");
                    break;
                case ExportConstant.TWO:
                    vo.setStatus("支付拒绝");
                    break;
                case ExportConstant.NINE:
                    vo.setStatus("初始化");
                    break;
                default:
                    vo.setStatus("");
                    break;
            }

            switch (vo.getAccountType() == null ? "" : vo.getAccountType()){
                case ExportConstant.ZERO:
                    vo.setAccountType("内部挂账");
                    break;
                case ExportConstant.ONE:
                    vo.setAccountType("外部挂账");
                    break;
                case ExportConstant.TWO:
                    vo.setAccountType("普通收款");
                    break;
                default:
                    vo.setAccountType("");
                    break;
            }

            Long id = vo.getId();
            Map<Long, Object> businessOwnerMap = feignSearchMap.get("businessOwnerMap");
            List<BusinessOwner> businessOwners = JSONObject.parseArray(JSONObject.toJSONString(businessOwnerMap.get(id)), BusinessOwner.class);
            // 咨询师,业绩归属
            if (businessOwners!=null&&0 != businessOwners.size()){
                Map<String, Float> rates = new HashMap<>(4);
                for (BusinessOwner ownerVO : businessOwners){
                    if (0 < BigDecimal.valueOf(ownerVO.getRate()).compareTo(BigDecimal.valueOf(0))) {
                        // rate > 0
                        rates.put(ownerVO.getName(), ownerVO.getRate());
                    }
                }
                String businessOwnerVoRate = rates.toString();
                StringUtils.strip(businessOwnerVoRate, "[]");
                vo.setBusinessOwnerVOrate(businessOwnerVoRate.replace("=", ":"));
            }

            // 审批备注
            if (null == vo.getApproveRemark() || "".equals(vo.getApproveRemark())) {
                vo.setApproveRemark("");
            }

            vo.setPayMethod("");
            Map<Long, Object> orgMap = feignSearchMap.get("orgMap");
            OrgVO orgVO = JSONObject.parseObject(String.valueOf(orgMap.get(vo.getOrgId())), OrgVO.class);
            vo.setOrgName(orgVO.getName());
            if (ExportConstant.EIGHTEEN.equals(vo.getPayType())) {
                vo.setPayMethod(vo.getPayWay() == null ? "" : vo.getPayWay());
                // 机构账户id
                String supplierId = orgVO.getCode();
                // 判断校区账户
                if (orgVO.getAdminAgenciesVOList() != null && orgVO.getAdminAgenciesVOList().size() > 0) {
                    Map<Long, Object> campusMap = feignSearchMap.get("campusMap");
                    ProductCampusDto productCampusDto = JSONObject.parseObject(String.valueOf(campusMap.get(vo.getCampusId())), ProductCampusDto.class);
                    if (productCampusDto != null && productCampusDto.getAccountId() != null) {
                        Map<Long, AdminAgenciesVO> adminMap = orgVO.getAdminAgenciesVOList().stream().collect(Collectors.toMap(AdminAgenciesVO::getId, a -> a, (k1, k2) -> k1));
                        if (adminMap.get(productCampusDto.getAccountId()) != null && StringUtils.isNotBlank(adminMap.get(productCampusDto.getAccountId()).getCode())) {
                            supplierId = adminMap.get(productCampusDto.getAccountId()).getCode();
                        }
                    }
                }
                if (StringUtils.isNotBlank(map.get(supplierId))) {
                    //离线收款的商户
                    vo.setReceiveSupplierName(map.get(supplierId));
                }
            } else {
                exportFinanceService.dealPayTypeDetails(vo);
                vo.setFeeNo(vo.getReceiptStreamlineNumber() == null ? "" : vo.getReceiptStreamlineNumber());
            }

            // 商品的类型
            Set<String> names = new HashSet<>();
            Set<String> lineNames = new HashSet<>();
            BigDecimal totalMakeSurePrice = new BigDecimal("0");
            List<OrderDetail> orderDetails = vo.getOrderDetails();
            if (null != orderDetails && 0 < orderDetails.size()) {
                // 其他商品处理
                List<String> types = new ArrayList<>();
                List<String> categoryName = new ArrayList<>();
                for (OrderDetail orderDetail : orderDetails) {
                    exportFinanceService.dealProductType(orderDetail, types);
                    if (null != orderDetail.getProductCategoryName() &&
                            !"".equals(orderDetail.getProductCategoryName())) {
                        categoryName.add(orderDetail.getProductCategoryName());
                    }

                    if (null != orderDetail.getMakeSurePrice()) {
                        totalMakeSurePrice = totalMakeSurePrice.add(orderDetail.getMakeSurePrice());
                    }
                    if (null != orderDetail.getProductLineName()){
                        lineNames.add(orderDetail.getProductLineName());
                    }

                    String pn = orderDetail.getProductName();
                    String year = orderDetail.getYear();
                    names.add(year + pn);
                }
                vo.setProductType(StringUtils.strip(types.toString(), "[]"));
                // 产品分类名称
                vo.setProductCategoryName(StringUtils.strip(categoryName.toString(), "[]"));
                // 产品名称
                if (names.size() > 0) {
                    vo.setProductNames(StringUtils.strip(names.toString(), "[]"));
                }
            }
            // 保过预留金
            vo.setAgreementFee(this.keepTwoDigits(totalMakeSurePrice).toString());
            if (lineNames.size() > 0) {
                vo.setProductLineName(StringUtils.strip(String.valueOf(lineNames), "[]"));
            }

            // 支付时间
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            if (StringUtils.isNotBlank(vo.getPayTime())) {
                if (10 == vo.getPayTime().length()) {
                    Date date = new Date(Long.parseLong(vo.getPayTime()) * 1000);
                    vo.setPayTime(sdf.format(date));
                } else {
                    vo.setPayTime(DateUtil.format(DateUtil.parse(vo.getPayTime()), "yyyy-MM-dd"));
                }
            }
            
            // 核对时间
            if (null != vo.getApproveClock()) {
                Date date = new Date(vo.getApproveClock() * 1000L);
                vo.setApproveClockTime(sdf.format(date));
            }

            if (vo.getCrmCreateTime()!=null){
                Date date = new Date(vo.getCrmCreateTime() * 1000L);
                vo.setCrmCreateDate(sdf.format(date));
            }

            // 订单状态
            String orderStatus = vo.getOrderStatus();
            switch (orderStatus == null ? "" : orderStatus){
                case ExportConstant.ZERO:
                    vo.setOrderStatus("未支付");
                    break;
                case ExportConstant.ONE:
                    vo.setOrderStatus("支付完成");

                    Integer time = Integer.valueOf(gatherCompleteTimeCollect.get(vo.getOrderNo()));
                    Date date = new Date(time * 1000L);
                    vo.setGatherCompleteTime(sdf.format(date));
                    break;
                case ExportConstant.TWO:
                    vo.setOrderStatus("部分付款");
                    break;
                case ExportConstant.THREE:
                    vo.setOrderStatus("现金支付待审批");
                    break;
                case ExportConstant.FOUR:
                    vo.setOrderStatus("取消");
                    break;
                case ExportConstant.FIVE:
                    vo.setOrderStatus("现金支付驳回审批");
                    break;
                case ExportConstant.SIX:
                    vo.setOrderStatus("已退");

                    // 订单退费完成（不欠费）的时间
                    Integer refundTime = refundTimeCollect.get(vo.getId());
                    if (refundTimeCollect.containsKey(vo.getId()) && 0 != refundTime){
                        Date refundDate = new Date(refundTime * 1000L);
                        vo.setRefundCompleteTime(sdf.format(refundDate));
                    }
                    break;
                case ExportConstant.SEVEN:
                    vo.setOrderStatus("支付中");
                    break;
                case ExportConstant.EIGHT:
                    vo.setOrderStatus("已转让");
                    break;
                case ExportConstant.NINE:
                    vo.setOrderStatus("已更换");
                    break;
                default:
                    vo.setOrderStatus("");
                    break;
            }

            // 收款状态 1-成功 0-未成功 2-审核中
            String payStatus = vo.getPayStatus();
            switch (payStatus == null ? "" : payStatus){
                case ExportConstant.ZERO:
                    vo.setPayStatus("未成功");
                    break;
                case ExportConstant.ONE:
                    vo.setPayStatus("成功");
                    break;
                case ExportConstant.TWO:
                    vo.setPayStatus("审核中");
                    break;
                default:
                    vo.setPayStatus("");
                    break;
            }

            // 收费方式 payedType 0-线下支付 1-线上支付
            String payType = vo.getPayType();
            String payedType = vo.getPayedType();
            if (ExportConstant.ZERO.equals(payedType) || ExportConstant.ONE.equals(payType) || ExportConstant.TWO.equals(payType)) {
                String unPayTime = vo.getUnpayTime();
                if (null != unPayTime && !"".equals(unPayTime)) {
                    long l = Long.parseLong(unPayTime + "000");
                    Date date = new Date(l);
                    vo.setPayTime(sdf.format(date));
                }

                vo.setPayedType("离线");
                // 离线收款时间
                if (null != vo.getArrivalAccountDate()) {
                    Date date = new Date(vo.getArrivalAccountDate() * 1000L);
                    String time = sdf.format(date);
                    vo.setArrivalAccountDateTime(time);
                }
            } else if (ExportConstant.ONE.equals(payedType)) {
                vo.setPayedType("在线");
                vo.setArrivalAccountDateTime(vo.getTransferDate());
            } else if (null == payedType) {
                vo.setPayedType("");
            }

            // 身份证号处理
            String idCard = vo.getIdCard();
            if (null != idCard && !"".equals(idCard)) {
                String setIdCard = idCard.substring(0, 6) + "********" + idCard.substring(14);
                vo.setIdCard(setIdCard);
            }
            // 手机号处理
            String mobile = vo.getMobile();
            if (null != mobile && !"".equals(mobile)) {
                String setMobile = mobile.substring(0, 3) + "****" + mobile.substring(7);
                vo.setMobile(setMobile);
            }

            // 订单创建时间
            String orderCreateTime = vo.getOrderCreateTime();
            if (null != orderCreateTime && !"".equals(orderCreateTime)) {
                Date date = new Date(Long.parseLong(orderCreateTime + "000"));
                vo.setOrderCreateTime(sdf.format(date));
            }

            // 学员意向（0-高端、1-低端、2-不明确）
            switch (vo.getIntention() == null ? "" : vo.getIntention()){
                case ExportConstant.ZERO:
                    vo.setIntention("高端");
                    break;
                case ExportConstant.ONE:
                    vo.setIntention("低端");
                    break;
                case ExportConstant.TWO:
                    vo.setIntention("不明确");
                    break;
                default:
                    vo.setIntention("");
                    break;
            }

            // 学员来源
            String source = vo.getSource();
            if (!"".equals(source) && null != source) {
                Map<Long, Object> dictMap = feignSearchMap.get("dictMap");
                DictsVo dictsVo = JSONObject.parseObject(String.valueOf(dictMap.get(Long.parseLong(source))), DictsVo.class);
                if (null != dictMap.get(Long.parseLong(source))) {
                    vo.setSource(dictsVo.getDictValue());
                }
            }

            String orderClassification = vo.getOrderClassification();
            switch(orderClassification == null ? "" : orderClassification){
                case "0":
                    vo.setOrderClassification("内部赊销");
                    break;
                case "1":
                    vo.setOrderClassification("外部赊销");
                    break;
                case "2":
                    vo.setOrderClassification("现销订单");
                    break;
                default:
                    vo.setOrderClassification("");
                    break;
            }

            // 年级
            if (null != vo.getGrade()){
                Map<Long, Object> gradeMap = feignSearchMap.get("gradeMap");
                vo.setGradeStr(String.valueOf(gradeMap.get(vo.getGrade())));
            }

            // 财务审核 0-未审核 1-审核通过 2-审核驳回
            switch (vo.getFinanceCheck() == null ? "" : vo.getFinanceCheck()){
                case "0":
                    vo.setFinanceCheck("未审核");
                    break;
                case "1":
                    vo.setFinanceCheck("审核通过");
                    break;
                case "2":
                    vo.setFinanceCheck("审核驳回");
                    break;
                default:
                    vo.setFinanceCheck("");
                    break;
            }
            
            // 审核时间
            if (null != vo.getCheckTime()) {
                Date date = new Date(Long.parseLong(vo.getCheckTime() + "000"));
                vo.setCheckTimeStr(sdf.format(date));
            }

            // 订单来源
            Map<Long, Object> sourceMap = feignSearchMap.get("sourceMap");
            if (sourceMap.containsKey(vo.getOrderSourceFirstId()) && sourceMap.containsKey(vo.getOrderSourceSecondId())) {
                vo.setOrderSource(sourceMap.get(vo.getOrderSourceFirstId()) + " / " + sourceMap.get(vo.getOrderSourceSecondId()));
            }

            vo.setClassCampusName(vo.getCampusName());

            vo.setRecordPayAmount(this.keepTwoDigits(vo.getRecordPayAmount()));
            vo.setEarnestAmount(this.keepTwoDigits(vo.getEarnestAmount()));
            vo.setChangeAmount(this.keepTwoDigits(vo.getChangeAmount()));
            vo.setAccountAmount(this.keepTwoDigits(vo.getAccountAmount()));
            vo.setCommissionAmount(this.keepTwoDigits(vo.getCommissionAmount()));

            ja.add(vo);
        }
    }



}

下面是导出用的poi工具类。网上有很多

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Component;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.Map;
import java.util.concurrent.CompletableFuture;

/**
 * @author 刘能
 * @since 2021/2/3/14:05
 */
@Component
@Slf4j
public class ExcelExport {

    public static String DEFAULT_DATE_PATTERN="yyyy年MM月dd日";
    public static int DEFAULT_COLUMN_WIDTH = 17;


    public void exportExcel(String title, Map<String,String> headMap, JSONArray ja, HttpServletResponse response, BigDecimal totalAmount){
        log.info("导出数据开始****************************");
        try {

            ByteArrayOutputStream os = new ByteArrayOutputStream();
            exportExcelXPayRecord(title, headMap, ja,null,0, os, totalAmount);

            response.reset();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode("收款记录导出.xlsx","UTF-8"));

            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);

            byte[] buff = new byte[2048];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();
        }catch (Exception e) {
            e.printStackTrace();
            log.error("导出收款记录失败的错误信息: {}", e.getMessage());
            throw new RestException(Constant.EXPORT_PAY_FAIL, Constant.EXPORT_PAY_FAIL_MESSAGE);
        }
    }

    /**
     * 导出Excel 2007 OOXML (.xlsx)格式
     * @param title 标题行
     * @param headMap 属性-列头
     * @param jsonArray 数据集
     * @param datePattern 日期格式，传null值则默认 年月日
     * @param colWidth 列宽 默认 至少17个字节
     * @param out 输出流
     */
    public void exportExcelXPayRecord(String title,Map<String, String> headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out,
                                      BigDecimal totalAmount) {
        if(datePattern==null){
            datePattern = DEFAULT_DATE_PATTERN;
        }
        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
        workbook.setCompressTempFiles(true);
        //表头样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight((short) 700);
        titleStyle.setFont(titleFont);
        // 列头样式
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerStyle.setFont(headerFont);
        // 单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        Font cellFont = workbook.createFont();
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);
        // 生成一个(带标题)表格
        Sheet sheet = workbook.createSheet();
        //workbook.setSheetName(sheetNum, sheetTitle);
        //设置列宽
        int minBytes = colWidth<DEFAULT_COLUMN_WIDTH ? DEFAULT_COLUMN_WIDTH:colWidth;//至少字节数
        int[] arrColWidth = new int[headMap.size()];
        // 产生表格标题行,以及设置列宽
        String[] properties = new String[headMap.size()];
        String[] headers = new String[headMap.size()];
        int ii = 0;
        for (Iterator<String> iter = headMap.keySet().iterator(); iter
                .hasNext();) {
            String fieldName = iter.next();

            properties[ii] = fieldName;
            headers[ii] = headMap.get(fieldName);

            int bytes = fieldName.getBytes().length;
            arrColWidth[ii] =  bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii,arrColWidth[ii]*256);
            ii++;
        }
        // 遍历集合数据，产生数据行
        int rowIndex = 0;
        // 不分sheet页
        Row headerRow = sheet.createRow(0); //列头 rowIndex =1
        for(int i=0;i<headers.length;i++)
        {
            headerRow.createCell(i).setCellValue(headers[i]);
            headerRow.getCell(i).setCellStyle(headerStyle);
        }
        rowIndex = 1;//数据内容从 rowIndex=2开始
        for (Object obj : jsonArray) {
            JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
            Row dataRow = sheet.createRow(rowIndex);
            for (int i = 0; i < properties.length; i++)
            {
                Cell newCell = dataRow.createCell(i);
                Object o =  jo.get(properties[i]);
                String cellValue = "";
                if(o==null) cellValue = "";
                else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
                else if(o instanceof Float || o instanceof Double)
                    cellValue= new BigDecimal(o.toString()).setScale(2,BigDecimal.ROUND_HALF_UP).toString();
                else cellValue = o.toString();

                newCell.setCellValue(cellValue);
                newCell.setCellStyle(cellStyle);
            }
            rowIndex++;
        }
        Row dataRow = sheet.createRow(rowIndex);
        for (int i = 0; i < properties.length; i++)
        {
            Cell newCell = dataRow.createCell(i);
            String cellValue = "";

            if (0 == i){
                newCell.setCellValue("合计收款金额");
                newCell.setCellStyle(headerStyle);
            } else if (6== i){
                //
                newCell.setCellValue(String.valueOf(totalAmount));
                newCell.setCellStyle(headerStyle);
            }else {
                newCell.setCellValue(cellValue);
                newCell.setCellStyle(cellStyle);
            }
        }
        try {
            workbook.write(out);
            workbook.dispose();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

至于里面一些抛出异常之类的常量就不贴了。大概就是那么些东西。

还有一些service的方法返回类型是resultBean类型，是使用的feign微服务的调用。也不细说这里了，都是一些简单的查询。

最后感谢批评指正。