title: Writing a large resultset to an Excel file using POI
body: <p>This is sort of inline w/ <a href="https://stackoverflow.com/questions/7195048/writing-a-large-resultset-to-a-file">Writing a large ResultSet to a File</a> but the file in question is an Excel file.</p>
  
  <p>I'm using the Apache POI library to write an Excel file with a large data set retrieved from a ResultSet object. The data could range from a few thousand records to about 1 million; not sure how this translates into file system bytes in Excel format.</p>
  
  <p>The following is a test code I wrote to check out the time taken to write such a large result set and also the performance implication w.r.t CPU &amp; Memory.</p>
  
  <pre><code>protected void writeResultsetToExcelFile(ResultSet rs, int numSheets, String fileNameAndPath) throws Exception {
  
      BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(fileNameAndPath));
      int numColumns = rs.getMetaData().getColumnCount();
  
      Workbook wb = ExcelFileUtil.createExcelWorkBook(true, numSheets);
      Row heading = wb.getSheetAt(0).createRow(1);
  
      ResultSetMetaData rsmd = rs.getMetaData();
  
      for(int x = 0; x &lt; numColumns; x++) {
          Cell cell = heading.createCell(x+1);
          cell.setCellValue(rsmd.getColumnLabel(x+1));
      }
  
      int rowNumber = 2;
      int sheetNumber = 0;
  
      while(rs.next()) {
  
          if(rowNumber == 65001) {
              log("Sheet " + sheetNumber + "written; moving onto to sheet " + (sheetNumber + 1));
              sheetNumber++;
              rowNumber = 2;
          }
  
          Row row = wb.getSheetAt(sheetNumber).createRow(rowNumber);
          for(int y = 0; y &lt; numColumns; y++) {
              row.createCell(y+1).setCellValue(rs.getString(y+1));
              wb.write(bos);
          }
  
          rowNumber++;
      }
  
      //wb.write(bos);
  
      bos.close();
  }
  </code></pre>
  
  <p>Not much luck with the above code. The file which is created seems to grow rapidly (~70Mb per sec). So I stopped the execution after about 10 minutes (killed the JVM when the file reaches 7Gb) and tried to open the file in Excel 2007. The moment I open it, the file size becomes 8k(!) and only the header and the first row are created. Not sure what I'm missing here.</p>
  
  <p>Any ideas?</p>