Importing Excel into a DataTable Quickly
.net, c#, excel, office-interop
Solution
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 = 1; 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 + 1];
Row[cCnt.ToString()] = cellVal;
}
}
}
Problem
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); } ```