Excel: import worksheet into DataTable
using
oExcel = new Excel.Application();
oWorkBook = oExcel.Workbooks.Open(varExcelFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oWorkSheet = (Excel.Worksheet)oWorkBook.Worksheets[1];
int r;
int c;
int intRows;
int intCols;
Excel.Range excelCell = oWorkSheet.UsedRange;
Object[,] values = (Object[,])excelCell.Value2;
intRows = values.GetLength(0);
if (intRows != 0)
{
intCols = values.GetLength(1);
if (intCols != 0)
{
for (c = 1; c <= intCols; c++)
{
dt.Columns.Add(new DataColumn((String)values[1, c]));
}
for (r = 2; r <= intRows; r++)
{
dr = dt.NewRow();
for (c = 1; c <= intCols; c++)
{
dr[(String)values[1, c]] = values[r, c];
}
dt.Rows.Add(dr);
}
}
}
dtreturn = dt;
dt = null;
oWorkSheet = null;
oWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
oWorkBook = null;
oExcel.Quit();
oExcel = null;
Excel;
System.Data.DataTable dt = new System.Data.DataTable();
DataRow dr;
System.Data.DataTable dtreturn = new System.Data.DataTable();
Excel.Application oExcel;
Excel.Workbook oWorkBook;
Excel.Worksheet oWorkSheet;