user1334858 user1334858 - 3 months ago 32
C# Question

Importing Excel into a DataTable Quickly

I am trying to read an Excel file into a list of Data.DataTable, although with my current method it can take a very long time. I essentually go Worksheet by Worksheet, cell by cell, and it tends to take a very long time. Is there a quicker way of doing this? Here is my code:

List<DataTable> List = new List<DataTable>();

// Counting sheets
for (int count = 1; count < WB.Worksheets.Count; ++count)
{
// Create a new DataTable for every Worksheet
DATA.DataTable DT = new DataTable();

WS = (EXCEL.Worksheet)WB.Worksheets.get_Item(count);

textBox1.Text = count.ToString();

// Get range of the worksheet
Range = WS.UsedRange;


// Create new Column in DataTable
for (cCnt = 1; cCnt <= Range.Columns.Count; cCnt++)
{
textBox3.Text = cCnt.ToString();


Column = new DataColumn();
Column.DataType = System.Type.GetType("System.String");
Column.ColumnName = cCnt.ToString();
DT.Columns.Add(Column);

// Create row for Data Table
for (rCnt = 0; rCnt <= Range.Rows.Count; rCnt++)
{
textBox2.Text = rCnt.ToString();

try
{
cellVal = (string)(Range.Cells[rCnt, cCnt] as EXCEL.Range).Value2;
}
catch (Microsoft.CSharp.RuntimeBinder.RuntimeBinderException)
{
ConvertVal = (double)(Range.Cells[rCnt, cCnt] as EXCEL.Range).Value2;
cellVal = ConvertVal.ToString();
}

// Add to the DataTable
if (cCnt == 1)
{

Row = DT.NewRow();
Row[cCnt.ToString()] = cellVal;
DT.Rows.Add(Row);
}
else
{

Row = DT.Rows[rCnt];
Row[cCnt.ToString()] = cellVal;

}
}
}
// Add DT to the list. Then go to the next sheet in the Excel Workbook
List.Add(DT);
}

Answer

Caling .Value2 is an expensive operation because it's a COM-interop call. I would instead read the entire range into an array and then loop through the array:

object[,] data = Range.Value2;

// Create new Column in DataTable
for (int cCnt = 1; cCnt <= Range.Columns.Count; cCnt++)
{
    textBox3.Text = cCnt.ToString();

    var Column = new DataColumn();
    Column.DataType = System.Type.GetType("System.String");
    Column.ColumnName = cCnt.ToString();
    DT.Columns.Add(Column);

    // Create row for Data Table
    for (int rCnt = 0; rCnt <= Range.Rows.Count; rCnt++)
    {
        textBox2.Text = rCnt.ToString();

        string CellVal = String.Empty;
        try
        {
            cellVal = (string)(data[rCnt, cCnt]);
        }
        catch (Microsoft.CSharp.RuntimeBinder.RuntimeBinderException)
        {
            ConvertVal = (double)(data[rCnt, cCnt]);
            cellVal = ConvertVal.ToString();
        }

        DataRow Row;

        // Add to the DataTable
        if (cCnt == 1)
        {

            Row = DT.NewRow();
            Row[cCnt.ToString()] = cellVal;
            DT.Rows.Add(Row);
        }
        else
        {

            Row = DT.Rows[rCnt];
            Row[cCnt.ToString()] = cellVal;

        }
    }
}