大批量excel分页导出

需要jar包：poi-4.1.2.jar

工具类：

public class ExcelUtils {

  /**
   * 解析文件.
   *
   * @param file 文件
   * @return java.util.List
   * @methodName analysisFile
   * @version 1.0
   * @date 2020/12/17  17:40
   * @author lsz
   */
  public static List<List<Object>> analysisFile(MultipartFile file) {

    if (Objects.isNull(file)) {
      throw new CareRunException("没有导入文件");
    }
    String fileName = file.getOriginalFilename();
    // 上传文件名格式不正确
    String s = ".";
    String pre = ".xlsx";
    if (fileName.lastIndexOf(s) != -1 && !pre
        .equals(fileName.substring(fileName.lastIndexOf(".")))) {
      throw new CareRunException("文件名格式不正确, 请使用后缀名为.xlsx的文件");
    }
    //读取数据
    InputStream inputStream = null;
    try {
      inputStream = file.getInputStream();
      ExcelReader excelReader = cn.hutool.poi.excel.ExcelUtil.getReader(inputStream);
      return excelReader.read(1, excelReader.getRowCount());
    } catch (IOException e) {
      e.printStackTrace();
    } finally {
      if (inputStream != null) {
        try {
          inputStream.close();
        } catch (IOException e) {
          e.printStackTrace();
        }
      }
    }
    return null;
  }

  /**
   * 导出EXCEL.
   *
   * @param response response
   * @param data     data
   */
  public static void exportExcel(HttpServletRequest request, HttpServletResponse response,
      ExcelData data) {
    try {
      // 实例化HSSFWorkbook
      HSSFWorkbook workbook = new HSSFWorkbook();
      // 创建一个Excel表单，参数为sheet的名字
      HSSFSheet sheet = workbook.createSheet("sheet");
      // 设置表头
      setTitle(workbook, sheet, data.getHead());
      // 设置单元格并赋值
      setData(sheet, data.getData());
      // 设置浏览器下载
      setBrowser(request, response, workbook, data.getFileName());
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  /**
   * 导出EXCEL，多sheet页.
   *
   * @param request  request
   * @param response response
   * @param list     list
   * @param fileName fileName
   */
  public static void exportExcelMultipleSheet(HttpServletRequest request,
      HttpServletResponse response, List<ExcelData> list, String fileName) {
    try {
      // 实例化HSSFWorkbook
      HSSFWorkbook workbook = new HSSFWorkbook();
      for (ExcelData data : list) {
        // 创建一个Excel表单，参数为sheet的名字
        HSSFSheet sheet = workbook.createSheet(data.getSheetName());
        // 设置表头
        setTitle(workbook, sheet, data.getHead());
        // 设置单元格并赋值
        setData(sheet, data.getData());
      }

      // 设置浏览器下载
      setBrowser(request, response, workbook, fileName);
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
    try {
      HSSFRow row = sheet.createRow(0);
      // 设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
      for (int i = 0; i <= str.length; i++) {
        sheet.setColumnWidth(i, 20 * 256);
      }
      // 设置为居中加粗,格式化时间格式
      HSSFCellStyle style = workbook.createCellStyle();
      HSSFFont font = workbook.createFont();
      font.setBold(true);
      style.setFont(font);
      style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
      // 创建表头名称
      HSSFCell cell;
      for (int j = 0; j < str.length; j++) {
        cell = row.createCell(j);
        cell.setCellValue(str[j]);
        cell.setCellStyle(style);
      }
    } catch (Exception e) {
      e.printStackTrace();
    }

  }

  private static void setData(HSSFSheet sheet, List<Object[]> data) {
    try {
      int rowNum = 1;
      for (int i = 0; i < data.size(); i++) {
        HSSFRow row = sheet.createRow(rowNum);
        for (int j = 0; j < data.get(i).length; j++) {
          row.createCell(j).setCellValue(String.valueOf(data.get(i)[j]));
        }
        rowNum++;
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  /**
   * 下载 .
   *
   * @param response response
   * @param workbook workbook
   * @param fileName fileName
   */
  public static void setBrowser(HttpServletRequest request, HttpServletResponse response,
      HSSFWorkbook workbook, String fileName) {
    try {
      String userAgent = request.getHeader("USER-AGENT");
      // 清空response
      response.reset();
      response.setCharacterEncoding("UTF-8");
      String newFilename = URLEncoder.encode(fileName, "UTF-8").replace("+", " ");
      String rtn = "filename=\"" + newFilename + "\"";
      if (userAgent != null) {
        userAgent = userAgent.toLowerCase();
        String edge = "edge";
        String trident = "trident";
        if (userAgent.indexOf(edge) != -1) {
          newFilename = URLEncoder.encode(fileName, "UTF-8").replace("+", " ");
          rtn = "filename=\"" + newFilename + "\"";
        } else if (userAgent.indexOf(trident) != -1) {
          rtn = "filename=\"" + newFilename + "\"";
        } else {
          rtn = "filename=\"" + new String(fileName.getBytes("UTF-8"), "ISO8859-1") + "\"";
        }
      }
      // 设置response的Header
      response.setHeader("Content-Disposition", "attachment;" + rtn);
      response.setHeader("Set-Cookie", "fileDownload=true; path=/");
      response.setContentType("application/vnd.ms-excel; charset=UTF-8");
      // 将excel写入到输出流中
      OutputStream os = new BufferedOutputStream(response.getOutputStream());
      workbook.write(os);
      os.flush();
      os.close();
    } catch (Exception e) {
      log.error("订单下载出错{}", e);
    }
  }
}

private void exportExcel(List<OrderInfo> listOrderInfo, HttpServletRequest request,
    HttpServletResponse response) {
  String[] rows = new String[]{"订单编号", "订单类型", "销售渠道", "下单时间", "支付方式", "支付时间", "流水单号", "下单人ID",
      "下单人", "收货人", "联系电话", "省", "市", "区", "详细地址", "实付金额", "订单状态"};
  Map<String, Object> retMap = new HashMap<>(16);
  if (listOrderInfo != null && CollectionUtil.isNotEmpty(listOrderInfo)) {
    SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
    String dateStr = format.format(Calendar.getInstance().getTime());
    String fileName = dateStr + ".xlsx";
    String exportUrl = exportExcelProperties.getExportUrl();
    // 先删除已经生成的文件 (此文件应该是空文件，或者有问题的)
    File fileNameFile = new File(
        exportUrl + fileName);
    if (fileNameFile.exists()) {
      fileNameFile.delete();
    }
    List<Object[]> list3 = new ArrayList<>();
    for (int i = 0; i < listOrderInfo.size(); i++) {
      OrderInfo p = listOrderInfo.get(i);
      SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
      String orderType = "普通订单";
      if (OrderCommConstant.ONE.equals(p.getOrderType())) {
        orderType = "活动订单";
      } else if (OrderCommConstant.TWO.equals(p.getOrderType())) {
        orderType = "预售订单";
      } else if (OrderCommConstant.THREE.equals(p.getOrderType())) {
        orderType = "拼团订单";
      }
      String fromType = "零售渠道";
      if (OrderCommConstant.TWO.equals(p.getFromType())) {
        fromType = "订货渠道";
      }
      String payType = "微信支付";
      if (OrderCommConstant.TWO.equals(p.getPayType())) {
        payType = "其他方式";
      }
      String orderStatus = orderStatus(p.getStatus());
      String payTimeStr = "";
      String payNo = "";
      if (null != p.getPayTime()) {
        List<OrderPayInfo> orderPayInfos = orderPayInfoMapper
            .selectList(new QueryWrapper<OrderPayInfo>().eq("order_no", p.getOrderNo()));
        if (CollectionUtil.isNotEmpty(orderPayInfos)) {
          payNo = orderPayInfos.get(0).getPayNo();
        }
        payTimeStr = dateFormat.format(p.getPayTime());
      }
      String createTimeStr = dateFormat.format(p.getCreateTime());
      Object[] o = {p.getOrderNo(), orderType, fromType, createTimeStr, payType, payTimeStr,
          payNo, p.getUserId(), p.getUserName(), p.getReceiverName(), p.getReceiverPhone(),
          p.getReceiverProvince(), p.getReceiverCity(), p.getReceiverRegion(),
          p.getReceiverAddress(), p.getPayAmount(), orderStatus};
      list3.add(o);
    }
    //导出到EXCEL
    exportToExcel(request, response, rows, fileName, list3);
  }
}

private void exportToExcel(HttpServletRequest request, HttpServletResponse response,
    String[] rows, String fileName, List<Object[]> list3) {
  ExcelData data = new ExcelData();
  data.setFileName(fileName);
  data.setHead(rows);
  data.setSheetName("sheet1");
  data.setData(list3);
  int excelMaxSheetCount = 65535;
  if (CollectionUtils.isNotEmpty(list3)) {
    if (list3.size() < excelMaxSheetCount) {
      ExcelUtils.exportExcel(request, response, data);
    } else {
      int listCount = list3.size();
      int count = listCount / excelMaxSheetCount;
      if (listCount % excelMaxSheetCount != 0) {
        count = count + 1;
      }
      List<ExcelData> list = new ArrayList<>();
      for (int i = 1; i <= count; i++) {
        ExcelData excelData = new ExcelData();
        excelData.setFileName(fileName);
        excelData.setHead(rows);
        excelData.setSheetName("订单明细第" + i + "页");
        //最后一页
        if (i == count) {
          excelData.setData(
              list3.subList((i - 1) * excelMaxSheetCount, list3.size() - 1));
        } else {
          excelData.setData(
              list3.subList((i - 1) * excelMaxSheetCount, (excelMaxSheetCount * i) - 1));
        }
        list.add(excelData);
      }
      ExcelUtils.exportExcelMultipleSheet(request, response, list, fileName);
    }
  }
}

private void setExcelRows(List<OrderInfo> listOrderInfo, List<String> rows) {
  listOrderInfo.stream().forEach(p -> {
    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String orderType = "普通订单";
    if (OrderCommConstant.ONE.equals(p.getOrderType())) {
      orderType = "活动订单";
    } else if (OrderCommConstant.TWO.equals(p.getOrderType())) {
      orderType = "预售订单";
    } else if (OrderCommConstant.THREE.equals(p.getOrderType())) {
      orderType = "拼团订单";
    }
    String fromType = "零售渠道";
    if (OrderCommConstant.TWO.equals(p.getFromType())) {
      fromType = "订货渠道";
    }
    String payType = "微信支付";
    if (OrderCommConstant.TWO.equals(p.getPayType())) {
      payType = "其他方式";
    }
    String orderStatus = orderStatus(p.getStatus());
    String payTimeStr = "";
    String payNo = "";
    if (null != p.getPayTime()) {
      List<OrderPayInfo> orderPayInfos = orderPayInfoMapper
          .selectList(new QueryWrapper<OrderPayInfo>().eq("order_no", p.getOrderNo()));
      if (CollectionUtil.isNotEmpty(orderPayInfos)) {
        payNo = orderPayInfos.get(0).getPayNo();
      }
      payTimeStr = format.format(p.getPayTime());
    }
    String createTimeStr = format.format(p.getCreateTime());
    StringBuffer sb = new StringBuffer("");
    sb.append(p.getOrderNo()).append(",")
        .append(orderType).append(",")
        .append(fromType).append(",")
        .append(createTimeStr).append(",")
        .append(payType).append(",")
        .append(payTimeStr).append(",")
        .append(payNo).append(",")
        .append(p.getUserId()).append(",")
        .append(p.getUserName()).append(",")
        .append(p.getReceiverName()).append(",")
        .append(p.getReceiverPhone()).append(",")
        .append(p.getReceiverProvince()).append(",")
        .append(p.getReceiverCity()).append(",")
        .append(p.getReceiverRegion()).append(",")
        .append(p.getReceiverAddress()).append(",")
        .append(p.getPayAmount()).append(",")
        .append(orderStatus);
    rows.add(sb.toString());
  });
}

后续还会修改。本篇文章仅做记录使用。