java导出2007excel--防止内存溢出

最新推荐文章于 2023-03-06 16:03:53 发布

wangxiao71892

最新推荐文章于 2023-03-06 16:03:53 发布

阅读量928

点赞数

文章标签：导出excel 防内存溢出

本文介绍了一种使用Apache POI SXSSFWorkbook进行大容量数据导出的方法，解决了因内存限制导致的大数据量Excel导出问题，并提供了核心代码实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

近期项目要求需导出4w+ 数据到excel，之前版本导出存在tomcat内存不够的情况。解决后的核心代码调整如下。

   public voidgetExcle(HttpServletRequest request, HttpServletResponseresponse,List list) {


       Workbookworkbook = new SXSSFWorkbook(1000);
       Sheet sheet= workbook.createSheet("文件名");

       String[]dataHeader = { "序号", "时间", "ID号", "长(mm)", "宽(mm)", "高(mm)","重量(Kg)" };
       //workbook.setSheetName(0, );
       Row headrow = sheet.createRow(0);
       for (int col = 0; col < dataHeader.length; col++) {
           Cell cell = headrow.createCell(col);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellValue(dataHeader[col]);
       }
       int r=0;
       Cell cell=null;
       Row row=null;
       for(ExpressDataEntity o : list) {
          row=sheet.createRow(r+1);
           cell =row.createCell(0);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellValue(StringUtil.defaultIfBlank(o.getDevice_id()));
           cell = row.createCell(1);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellValue(DateUtil.getNewFormatDateString(o.getScanDate()));
           cell = row.createCell(2);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellValue(StringUtil.defaultIfBlank(o.getBarCodes()));
           if(o.getLength() != null){
            row.createCell(3).setCellValue(o.getLength());
          }
         if(o.getWidth() != null){
            row.createCell(4).setCellValue(o.getWidth());
          }
         if(o.getHeight() != null){
            row.createCell(5).setCellValue(o.getHeight());
          }
         if(o.getWeight() != null){
            row.createCell(6).setCellValue(String.format("%.3f",o.getWeight()/1000.0));
          }
           r++;
       };
       //通过Response把数据以Excel格式保存
       //application/vnd.openxmlformats-officedocument.spreadsheetml.sheet 2007
       response.reset();
       response.setContentType("application/vnd.ms-excel;charset=UTF-8");
       response.setCharacterEncoding("UTF-8");
       //System.out.println(excleName);
       try {
           response.addHeader("Content-Disposition","attachment;filename=\""
                   + new String(("test" + ".xlsx").getBytes("GBk"),
                           "ISO8859_1") + "\"");
           OutputStream out = response.getOutputStream();
           workbook.write(out);
           out.flush();
           out.close();
       } catch (Exception e) {
           e.printStackTrace();
       }
    }
总结：用的是poi版本的jar可以支持excel2007写临时文件。现在导出大都转向csv格式了，支持导出百万条。有兴趣的同学可以研究下。
附：jar下载链接http://download.youkuaiyun.com/download/wangxiao71892/9952323