title: Reading a large Excel file by OpenXML
body: <p>I am working on a project in which i am reading few cells from an excel file using OpenXML SDK in VB.Net and storing it in DataTable.
  It works just fine for the medium sized and small files
  however when when i try to open a large file of size 107MB, i get an OutOfMemory exception after reading through few sheets. I am able to open the file by double clicking(it takes a while though)
  Below are the code that i am using. Please let me know if i can improve the process of reading by reducing the memory consumption</p>
  
  <pre><code>Dim CellRage As String() = {"AG65", "AG281", "AG335", "AG389", "AG443", "AG497", "AG551", "AG800", "AG913", "AG1081", "AG1165", "AG1305"}
  Dim CellValue(13) As String
  
  Using myWorkbook As SpreadsheetDocument = SpreadsheetDocument.Open(stream, False)
       workbookPart = myWorkbook.WorkbookPart
  
       For Each worksheetpart As WorksheetPart In workbookPart.WorksheetParts
         For count As Integer = 0 To CellRage.GetUpperBound(0) -1
               CellValue(count) = CellValue(workbookPart, sheetName, CostCellRage(count - 2)) 
         Next
         'After few sheets throws OutofMemory Exception
       Next
  End Using
  
  Private Shared Function GetCellValue(workbookPart As WorkbookPart, sheetName As String, cellAddress As String) As String
          Dim theCell As Cell
          Dim wsPart As WorksheetPart
          Dim worksheet As Sheet
          Dim value As String
          Dim stringTablePart As SharedStringTablePart = workbookPart.SharedStringTablePart
  
          worksheet = workbookPart.Workbook.Descendants(Of Sheet).Where(Function(s) s.Name = sheetName).FirstOrDefault
          wsPart = CType(workbookPart.GetPartById(worksheet.Id), WorksheetPart)
          theCell = wsPart.Worksheet.Descendants(Of Cell).Where(Function(c) c.CellReference = cellAddress).FirstOrDefault
  
          If theCell.ChildElements.Count = 0 Then
              Return ""
          End If
  
          value = theCell.CellValue.Text
  
          If (theCell.DataType IsNot Nothing) AndAlso (theCell.DataType.ToString() = "s") Then
              value = stringTablePart.SharedStringTable.ChildElements(Int32.Parse(value)).InnerText
          End If
  
          Return value
  
      End Function
  </code></pre>
  
  <p>Thanks for looking into this</p>