title: Processing excel files with data reader: ExecuteReader() buffers entire file
body: <p>I'm running into a peculiar issue when trying to process large excel files (300mb+) using a data reader.
  The following code illustrates the way I open the excel file and iterate over the rows in sheet 'largesheet$':</p>
  
  <pre><code>const string inputFilePath = @"C:\largefile.xlsx";
  const string connectionString =
      "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=\"Excel 12.0;IMEX=1;HDR=YES;\";Data Source=" +
      inputFilePath;
  
  // Initialize connection
  using (var connection = new OleDbConnection(connectionString))
  {
      // Open connection
      connection.Open();
  
      // Configure command
      var command = new OleDbCommand("largesheet$", connection) {CommandType = CommandType.TableDirect};
  
      // Execute reader
      var reader = command.ExecuteReader(); // &lt;-- Completely loads file/sheet into memory
  
      // Iterate results
      while (reader.HasRows)
      {
          // Read single row
          reader.Read();
  
          // ...
      }
  
      // Close connection
      connection.Close();
  }
  </code></pre>
  
  <p>In my understanding this should open the excel file and load each row when needed by using the <em>reader.Read()</em> statement.<br>
  However, it appears that the <em>ExecuteReader()</em> statement does more than returning an OleDbDataReader instance. Using breakpoints I noticed that that one statement takes 30s+, and the Windows Resource Monitor indicates a steady increase of allocated memory during the execution of that statement.<br>
  Specifying the <em>CommandBehavior</em> parameter (e.g. <em>SequentialAccess</em>) of the ExecuteReader() method has no effect.</p>
  
  <p>What am I doing wrong here? Are there alternative ways of processing large (excel) files?</p>
  
  <p><strong>Note</strong>: the IMEX &amp; HDR extended properties of the connection string are intentional.</p>
  
  <p><strong>Edit</strong>: After some rational thinking I assume it is not possible to process an excel file without buffering it one way or another. Since excel files are basically a glorified collection of compressed XML files it is not possible to process a worksheet without decompressing it (and keeping it in ram or temporarily saving to disk).<br>
  The only alternative I can think of is using <a href="http://msdn.microsoft.com/en-us/library/office/microsoft.office.interop.excel" rel="nofollow">Microsoft.Office.Interop.Excel</a>. Not sure how <a href="http://openxmldeveloper.org/" rel="nofollow">OpenXML</a> handles it though.</p>