使用SXSSFWorkbook进行分批次，多sheet页大数据量导出

@Action(value = "/testExport")
   public void testExport() throws PTPECAppException, IOException {
      long l = System.currentTimeMillis();
      HttpServletRequest request = ServletActionContext.getRequest();
      HttpServletResponse response = ServletActionContext.getResponse();
      StringBuffer sqlBuffer = new StringBuffer();

      this.pager = new Page("pagerForm", request);//分页

      //分批次查询并导出
      //测试已50000为基准
      int currentPageNum = 50000;
      int currentPage = 1;

      //设置当前页面
      pager.setCurrentPage(currentPage);
      //设置当前页码条数
      pager.setNumPerPage(currentPageNum);

      List<xxx> list = null;

      //按上述分页查询数据库，得到数据list
      list = 查询数据库的代码，根据项目情况编写，此处不写了，同理pager也是跟项目分页查询有关，可根据自己项目看情况编写
      //第一次查询后，后去查询的总条数
      int totalCount = pager.getTotalCount();

      //新建SXSSFWorkbook导出对象并设置每1000条刷新数据到硬盘，大数据导出时防止内存溢出
      SXSSFWorkbook wb = new SXSSFWorkbook(1000);


      //设置excel的标题
      String[] columnTitle = {"标题1","标题2","标题3"};

      //把第一次的查询写入到excel中      传入标题，导入的list集合，SXSSFWorkbook对象，sheet页的名称
      textExport(columnTitle,list,wb,"report");
      long l3 = System.currentTimeMillis();
      log.info("sheet耗时：" + (l3-l));



      //第一次导入后判断总条数，并用总条数除以每页条数，得到共分几页
      if(totalCount > currentPageNum){
         log.info("共多少页：" + totalCount/currentPageNum);
         //对每页的数据进行查询，并导入到excel中
         for (int i = 0; i < totalCount/currentPageNum; i++) {
            long l4 = System.currentTimeMillis();
            //设置当前页码
            pager.setCurrentPage(i + 2);
            list.clear();
            //分页查询数据库获得需要导入的list
            list = 查询数据库的代码，根据项目情况编写，此处不写了，同理pager也是跟项目分页查询有关，可根据自己项目看情况编写
            //写入到excel中
            textExport(columnTitle,list,wb,"report" + i);
            long l2 = System.currentTimeMillis();
            log.info("sheet" + (i+1) + "耗时：" + (l2-l4));
         }
      }

      //当需要导入的数据都导完后，写入到本地excel文件中
      String filePath=request.getRealPath("/")+"\\impTempExceptFiles";
      filePath = filePath.replace("\\", java.io.File.separator);
      filePath = filePath.replace("/", java.io.File.separator);

      FileOutputStream fileOutputStream = null;
      try {
         fileOutputStream = new FileOutputStream(FileUtil.createFile(filePath, "testExcept"+".xlsx"));
         wb.write(fileOutputStream);
         fileOutputStream.flush();
//       fileOutputStream.close();
      } catch (Exception e) {
         log.error("生成excel文件时出现异常", e);
         throw new RuntimeException("生成excel文件时出现异常");
      }finally {
         try {
            if (fileOutputStream != null)
               fileOutputStream.close();
            log.info("下载信息：本地文件输出流关闭");
         } catch (IOException e) {
            log.info("下载信息：本地文件输出流关闭 异常");
            e.printStackTrace();
         }
      }


      long l1 = System.currentTimeMillis();
      log.info("共计耗时：" + (l1-l));

      //上述操作把写入好的excel保存到本地文件夹中，下面的操作是浏览器访问时，把本地文件夹中的excel下载给浏览器
      OutputStream os = null;
      BufferedInputStream input = null;
      try {

         ActionContext ac = ActionContext.getContext();
         ServletContext scn = (ServletContext) ac.get(ServletActionContext.SERVLET_CONTEXT);
         String filepath = scn.getRealPath("/");
         filepath = filepath + "impTempExceptFiles\\"+"testExcept"+".xlsx";
         filepath = filepath.replace("\\", java.io.File.separator);
         filepath = filepath.replace("/", java.io.File.separator);
         log.info("下载信息：路径(" + filepath+")");
         File file = new File(filepath);
         if (file == null || !file.exists()) {
            log.info("下载信息：下载找不到文件("+filepath+")");
         }
         os = response.getOutputStream();
         // 重置响应
         response.reset();

         response.setContentType("application/x-msdownload;charset=utf-8");
         String attachFileName="testExcept"+".xlsx";
         response.setHeader("Content-Disposition", "attachment;filename="+new String(attachFileName.getBytes("GB2312"), "ISO-8859-1"));
         input = new BufferedInputStream(new FileInputStream(filepath));
         byte buffBytes[] = new byte[1024];
         int allLength = 0;

         int read = 0;
         while ((read = input.read(buffBytes)) != -1) {
            allLength += read;
            os.write(buffBytes, 0, read);
         }
         os.flush();
         log.info("下载信息：下载正常");
      } catch (IOException e) {
         log.info("下载信息：下载存在异常");
         e.printStackTrace();
      } catch (Exception e) {
         e.printStackTrace();
      }finally {
         try {
            if (os != null)
               os.close();
            if (input != null)
               input.close();
            log.info("下载信息：下载输出流关闭");
         } catch (IOException e) {
            log.info("下载信息：下载输出流关闭 异常");
            e.printStackTrace();
         }
      }
   }


   //导出excel
   public void textExport(String[] columnTitle,List<GoldTaxInvoiceInfo> list,SXSSFWorkbook wb,String sheetName) throws IOException {
      //根据sheetName,新建sheet页
      SXSSFSheet sheet = (SXSSFSheet) wb.createSheet(sheetName);
      //设置报表标题
      Cell titleCell = sheet.createRow(0).createCell((columnTitle.length / 2));
      titleCell.setCellValue("");

      //设置表头
      Row headRow = sheet.createRow(0);
      for (int i = 0; i < columnTitle.length; i++) {
         Cell cell = headRow.createCell(i);
         cell.setCellValue(columnTitle[i]);
      }

      sheet.setDefaultColumnWidth(16);
      sheet.setAutobreaks(true);

      //设置具体内容
      int rowNum = 0;
      for (int i = 0; i< list.size(); i++) {
         // 获得效验未成功数组，一行的数据形式为：[列数据$列标题#,列数据$列标题#,] 列分隔符为 #,
//       Object row1 = list.get(i);
//       Object[] row = (Object[]) row1;
         GoldTaxInvoiceInfo goldTaxInvoiceInfo = list.get(i);
         SXSSFRow dataRow = (SXSSFRow) sheet.createRow(rowNum + 1);

         //列1
         Cell cell1 = dataRow.createCell(0);
         if(StringUtils.isNotBlank(goldTaxInvoiceInfo.getBillingNo())){
            cell1.setCellValue(goldTaxInvoiceInfo.getBillingNo());
         }else{
            cell1.setCellValue("");
         }

         //列2
         Cell cell12 = dataRow.createCell(11);
         if(StringUtils.isNotBlank(goldTaxInvoiceInfo.getTaxRate())){
            cell12.setCellValue(goldTaxInvoiceInfo.getTaxRate());
         }else{
            cell12.setCellValue("");
         }
         //列3
         Cell cell13 = dataRow.createCell(12);
         if(goldTaxInvoiceInfo.getBdate() != null){
            cell13.setCellValue(goldTaxInvoiceInfo.getBdate().toString());
         }else{
            cell13.setCellValue("");
         }


         rowNum++;
         if(rowNum%1000==0){
            //每1000刷新一次到硬盘中
            sheet.flushRows();
         }
      }

   }

 

 

 

//此处是浏览器web访问后端导出excel的单机事件
function textOnclick() {

   var downloadIframe;

   if (downloadIframe) {
      downloadIframe.parentNode.removeChild(downloadIframe);
   }
   downloadIframe = document.createElement("iframe");
   
   var url = "web访问的url";
   downloadIframe.src = url;
   downloadIframe.style.display = "none";
   document.body.appendChild(downloadIframe);
   alertMsg.info("正在导出，请稍后,,,,")
   
}

经测试导出23万条数据，所用时间大概不到1分半

本文地址：https://blog.csdn.net/zuoyigehaizei/article/details/107542910