System.OutOfMemoryException in foreach loop over worsheet rows

Question

I am trying to open an excel file and place the contents into a datatable for further use in my program. The program is receiving the error in the subject title.

private void ReadExcelToDataSet(string FileName,string Worksheet)
    {
        Microsoft.Office.Interop.Excel.Application xlApp;
        Microsoft.Office.Interop.Excel.Workbook xlWorkbook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorksheet;

        xlApp = new Microsoft.Office.Interop.Excel.Application();
        xlWorkbook = xlApp.Workbooks.Open(FileName);
        xlWorksheet = xlWorkbook.Sheets[Worksheet];

        System.Data.DataTable ReadExcel = new System.Data.DataTable();
        Range UsedRange = xlWorksheet.UsedRange;

        foreach(Range row in xlWorksheet.Rows)
        {
            ReadExcel.Rows.Add(xlWorksheet.Rows.Value);
        }

        dataGridViewTest.DataSource = ReadExcel;
    }

The program is failing on the line:

ReadExcel.Rows.Add(xlWorksheet.Rows.Value);

The worksheet isn't particularly large, and all the data is either strings or numbers (no images or charts). I'm assuming that there is something fundamentally wrong with trying to add an Excel Row to a datatable row. What would be the correct method to read Excel to a datatable using Interop, or what should I do to prevent my out of memory problem? Thank you in advance for any help.

Answer 1

var ReadExcel = new System.Data.DataTable();
var UsedRange = xlWorksheet.UsedRange;
// Add a new column to the DataTable
var column = new DataColumn();
column.ColumnName = "rowValue";
ReadExcel.Columns.Add(column);

foreach (Range row in xlWorksheet.Rows)
{               
    var newrow = ReadExcel.NewRow();
    // Add row.Value NOT xlWorksheet.Rows.Value
    newrow["rowValue"] = row.Value;
    ReadExcel.Rows.Add(newrow);
}

Basically, you've had xlWorksheet.Rows.Value instead of row.Value. But I'm guessing you'll also want to correctly use the DataTable. See here for examples: DataTable.NewRow Method . I've also used implicit typing. It's easier for the eyes.