EasyExcel使用记录：

官方地址：https://www.yuque.com/easyexcel/doc/easyexcel

github:https://github.com/alibaba/easyexcel

介绍：EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。64M内存1分钟内读取75M(46W行25列)的Excel

对比POI：POI在数据量的大的情况下很容易内存溢出，而EasyExcel在大数据量的情况下对内存专门做了处理，祥见：关于EasyExcel

具体代码如下（根据具体业务进行修改）：

前提：引入maven依赖

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.7</version>
</dependency>

1.Controller:

/**
 * 导出Excel -easyExcel
 * @param orderQuery
 * @return R
 */
@Inner(value = false)
@ApiOperation(value = "导出Excel", notes = "导出Excel")
@SysLog("服务统计导出")
@GetMapping("/exportBizOrder")
public void exportBizOrder2(HttpServletResponse response, BizOrderQuery orderQuery) throws IOException {
   bizOrderService.exportBizOrder2(response, orderQuery);
}

2.service层具体业务代码：

/**
 * 导出查询结果-easyExcel
 *
 * @param response
 * @param orderQuery
 */
void exportBizOrder2(HttpServletResponse response, BizOrderQuery orderQuery) throws IOException;

impl:

/**
    * easyExcel
    * @param response
    * @param orderQuery
    * @throws IOException
    */
   @Override
   public void exportBizOrder2(HttpServletResponse response, BizOrderQuery orderQuery) throws IOException {
      // 查询导出集合方法(Mapper)
      List<BizOrderViewDTO> orders = selOrder(orderQuery);
      if (orders != null && orders.size()>0) {
         for (BizOrderViewDTO data : orders) {
            // 服务状态
            if (data.getOrderStatus() != null) {
               String orderStatus = "";
               if ("1".equals(data.getOrderStatus())) {
                  orderStatus = "待服务";
               }
               if ("2".equals(data.getOrderStatus())) {
                  orderStatus = "服务中";
               }
               if ("3".equals(data.getOrderStatus())) {
                  orderStatus = "服务完成";
               }
               if ("4".equals(data.getOrderStatus())) {
                  orderStatus = "服务取消";
               }
               data.setOrderStatus(orderStatus);
            }
            // 是否需要e代驾
            String userEDJ = "否";
            if ((data.getUserEDJ() != null && Integer.parseInt(data.getUserEDJ()) > 0) || (data.getCpNumber() != null && data.getCpNumber().length() > 0)) {
               userEDJ = "是";
            }
            data.setUserEDJ(userEDJ);
         }
         try {
            // EasyExcel核心代码
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding(StringPool.UTF_8);
            String name = StrBuilder.create()
   //             .append(BizConstants.EXPORT_TEMPLATE_NAME) // 暂时注释，测试环境中文乱码
                  .append(DatePattern.PURE_DATETIME_MS_FORMAT.format(new Date()))// 导出的excel文件名
                  .append(BizConstants.EXCEL_XLSX).toString();// BizConstants.EXCEL_XLSX = ".xlsx"
            // 这里URLEncoder.encode可以防止中文乱码
            String fileName = URLEncoder.encode(name, "UTF-8");
//
            response.setHeader("fileName",fileName);
            EasyExcel.write(response.getOutputStream(), BizOrderViewDTO.class)
                  .autoCloseStream(Boolean.FALSE)
                  .sheet(BizConstants.EXPORT_BIZORDER_NAME)// excel中sheet名称
                  .doWrite(orders);
         } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding(StringPool.UTF_8);
            Map<String, String> map = new HashMap<String, String>(2);
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
         }
      }
   }

3.查询方法selOrder

/**
 * 条件查询
 * @param orderQuery
 * @return
 */
private List<BizOrderViewDTO> selOrder(BizOrderQuery orderQuery) {
   logger.debug("BizOrderQuery:{}", orderQuery);
   QueryWrapper<BizOrder> queryWrapper = new QueryWrapper<>();
   if (orderQuery != null) {
      //订单编号
      String orderNumber = orderQuery.getOrderNumber();
      if (orderNumber != null && orderNumber.length() > 0) {
         queryWrapper.eq("order_number", orderNumber);
      }
      //订单状态
      Integer orderStatus = orderQuery.getOrderStatus();
      if (orderStatus != null && orderStatus > 0) {
         queryWrapper.eq("order_status", orderStatus);
      }
      //下单人手机号
      String customerPhone = orderQuery.getCustomerPhone();
      if (customerPhone != null && customerPhone.length() > 0) {
         queryWrapper.eq("order_phone", customerPhone);
      }
      //下单人姓名
      String customerName = orderQuery.getCustomerName();
      if (customerName != null && customerName.length() > 0) {
         queryWrapper.like("order_name", customerName);
      }
      //商业保单号
      String policyNo = orderQuery.getPolicyNo();
      if (policyNo != null && policyNo.length() > 0) {
         queryWrapper.eq("policy_number", policyNo);
      }
      Integer edjChoice = orderQuery.getEdjChoice();
      if (edjChoice != null) {
         if (edjChoice == 1) {
            queryWrapper.gt("goods_id", 0);
            queryWrapper.isNotNull("cp_number");
         }
         if (edjChoice == 0) {
            queryWrapper.and(Wrapper -> Wrapper.isNull("goods_id").or().lt("goods_id", 1));
            queryWrapper.and(Wrapper -> Wrapper.isNull("cp_number").or().eq("cp_number", ""));
         }
      }
      Integer payType = orderQuery.getPayType();
      if (payType != null && payType > 0) {
         queryWrapper.eq("pay_type", payType);
      }
      //订单金额检索下限
      String orderAmountIndexFrom = orderQuery.getOrderAmountIndexFrom();
      if (orderAmountIndexFrom != null && orderAmountIndexFrom.length() > 0) {
         queryWrapper.ge("order_amount", BigDecimal.valueOf(Double.parseDouble(orderAmountIndexFrom)));
      }
      //订单金额检索上限
      String orderAmountIndexTo = orderQuery.getOrderAmountIndexTo();
      if (orderAmountIndexTo != null && orderAmountIndexTo.length() > 0) {
         queryWrapper.le("order_amount", BigDecimal.valueOf(Double.parseDouble(orderAmountIndexTo)));
      }
      //实付金额检索下限
      String payAmountIndexFrom = orderQuery.getPayAmountIndexFrom();
      if (payAmountIndexFrom != null && payAmountIndexFrom.length() > 0) {
         queryWrapper.ge("pay_amount", BigDecimal.valueOf(Double.parseDouble(payAmountIndexFrom)));
      }
      //实付金额检索上限
      String payAmountIndexTo = orderQuery.getPayAmountIndexTo();
      if (payAmountIndexTo != null && payAmountIndexTo.length() > 0) {
         queryWrapper.le("pay_amount", BigDecimal.valueOf(Double.parseDouble(payAmountIndexTo)));
      }
      //下单时间检索下限
      String orderTimeIndexFrom = orderQuery.getOrderTimeIndexFrom();
      if (orderTimeIndexFrom != null && orderTimeIndexFrom.length() > 0) {
         queryWrapper.ge("order_time", orderTimeIndexFrom);
      }
      //下单时间检索上限
      String orderTimeIndexTo = orderQuery.getOrderTimeIndexTo();
      if (orderTimeIndexTo != null && orderTimeIndexTo.length() > 0) {
         queryWrapper.le("order_time", orderTimeIndexTo);
      }
      String orderEndTimeIndexFrom = orderQuery.getEndTimeIndexFrom();
      if (orderEndTimeIndexFrom != null && orderEndTimeIndexFrom.length() > 0) {
         queryWrapper.ge("end_time", orderEndTimeIndexFrom);
      }
      String orderEndTimeIndexTo = orderQuery.getEndTimeIndexTo();
      if (orderEndTimeIndexTo != null && orderEndTimeIndexTo.length() > 0) {
         queryWrapper.le("end_time", orderEndTimeIndexTo);
      }
      Integer evaluateScoreIndexFrom = orderQuery.getEvaluateScoreIndexFrom();
      Integer evaluateScoreIndexTo = orderQuery.getEvaluateScoreIndexTo();
      if (evaluateScoreIndexFrom != null && evaluateScoreIndexFrom > 0) {
         queryWrapper.ge("evaluate_score", evaluateScoreIndexFrom);
      }
      if (evaluateScoreIndexTo != null && evaluateScoreIndexTo > 0) {
         queryWrapper.le("evaluate_score", evaluateScoreIndexTo);
      }
      if (StrUtil.isNotBlank(orderQuery.getDeptIds())) {
         queryWrapper.in("o.dept_id", Arrays.asList(orderQuery.getDeptIds().split(",")));
      }
   }
   queryWrapper.orderByDesc("id");
   List<BizOrderViewDTO> orderData = bizOrderMapper.queryBizOrderForExport2(queryWrapper);
   return Optional.ofNullable(orderData).orElse(new ArrayList<BizOrderViewDTO>());
}

4.返回给前端DTO类BizOrderViewDTO

@Data
public class BizOrderViewDTO {
   /**
    * 订单编号
    */
   @ColumnWidth(30)
   @ExcelProperty(value = "订单编号", index = 0)
   private String orderNumber;
   /**
    * 服务状态
    */
   @ColumnWidth(30)
   @ExcelProperty(value = "服务状态", index = 1)
   private String orderStatus;
   /**
    * 手机号
    */
   @ColumnWidth(30)
   @ExcelProperty(value = "手机号", index = 2)
   private String orderPhone;
   /**
    * 用户姓名
    */
   @ColumnWidth(30)
   @ExcelProperty(value = "用户姓名", index = 3)
   private String orderName;
   /**
    * 保单号
    */
   @ColumnWidth(30)
   @ExcelProperty(value = "保单号", index = 4)
   private String policyNumber;
   /**
    * 所属机构
    */
   @ColumnWidth(50)
   @ExcelProperty(value = "所属机构", index = 5)
   private String deptName;
   /**
    * 下单时间
    */
   @ColumnWidth(30)
   @ExcelProperty(value = "下单时间", index = 6)
   private String orderTime;
   /**
    * 结束时间
    */
   @ColumnWidth(30)
   @ExcelProperty(value = "结束时间", index = 7)
   private String endTime;
   /**
    * 订单金额
    */
   @ColumnWidth(15)
   @ExcelProperty(value = "订单金额", index = 8)
   private String orderAmount;
   /**
    * 是否使用e代驾
    */
   @ColumnWidth(30)
   @ExcelProperty(value = "是否使用e代驾", index = 9)
   private String userEDJ;
   /**
    * 人民币面值
    */
   @ColumnWidth(30)
   @ExcelProperty(value = "人民币面值", index = 10)
   private String rmbFaceValueObj;
   @ExcelIgnore
   private String cpNumber;
}

更多API及相关注解使用方法！