title: ColdFusion 8 Reading large Excel files
body: <p>I have been able to create my application so it can take Excel files with multiple sheets and put them into the database. However I'm not sure how I do this with large files 30mb+ I tried looking into <code>bufferedReader</code> but I'm not sure this is what I want since it reads a portion at a time and I am using Apache POI to read in multiple Excel sheets.</p>
  
  <p>Currently if I send in a file to big I'm getting Java heap space errors, which is why I'm feeling the file is to large. During testing it worked fine, and then I used the 32mb file, and it failed.</p>
  
  <p>Below is the code, I appreciate any help in advance. 
  also myfile is an argument</p>
  
  <pre><code>ci_model = createObject("component", "com.data_feeds.models.ci.model_item_setup");
  FileIn = createObject("java","java.io.FileInputStream").init(javacast("string", myfile));
  wb = createObject("java","org.apache.poi.xssf.usermodel.XSSFWorkbook").init(FileIn);
  workbook = createObject("java","org.apache.poi.xssf.streaming.SXSSFWorkbook").init(wb);
  sheetCount = workbook.getNumberOfSheets();
  
  for(sheetIndex = 0; sheetIndex LT sheetCount; sheetIndex = sheetIndex + 1)
  {
      recordIndex = 1;
      sheet = workbook.getSheetAt(sheetIndex);
  
      rowHeader = sheet.getFirstRowNum();
      rowCount = sheet.getLastRowNum();
  
      for (rowIndex = 1; rowIndex LTE rowCount; rowIndex = rowIndex + 1)
      {
          row = sheet.getRow(rowIndex);
          columnCount = row.getLastCellNum();
  
          for(columnIndex = 0; columnIndex LT columnCount; columnIndex = columnIndex +1)
          {
              record[recordIndex][columnIndex+1] = row.getCell(columnIndex);
              if((columnIndex+1)==4)
              {
                  record[recordIndex][columnIndex+1] = ci_model.boolean_number(row.getCell(columnIndex).toString());
              }
          }
          recordIndex = recordIndex + 1;
          if(recordIndex == 500)
          {
              this.insert_record(record);
              arrayClear(record);
              request.help.debug.collect_garbage();
              recordIndex = 1;
          }
      }
      this.insert_record(record);
      arrayClear(record);
  }
  FileIn.close();
  </code></pre>
  
  <p>I am using ColdFusion 8.</p>