Friday, April 19, 2024 :: Login  

Import Excel worksheet into DataTable

 

//Add a reference to the Microsoft Excel Object Library
using appExcel = Microsoft.Office.Interop.Excel;
DataTable dt = new DataTable();
DataRow dr;
appExcel.Application oExcel = new appExcel.Application();

 

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;

 

Privacy Policy www.made4dotnet.com 2020

Map IP Address
Powered byIP2Location.com

   Terms Of Use   ::   Privacy Statement   ::   Copyright (c) 2024 www.made4dotnet.com - .NET Development