reading data through Excel

private DataTable ReadExcelData(string strFilePath, string strFileType)

{

DataTable dt = new DataTable();

try

{

if (strFileType.ToLower().Contains("xls") || strFileType.ToLower().Contains("xlsx"))

{

OleDbCommand excelCommand = new OleDbCommand();

OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter();

// for office 2003 and prior versions

//////////string excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strFilePath + "; Extended Properties =Excel 8.0;";

/////// for office 2007 onwards and prior versions

string excelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + strFilePath + "; Extended Properties =Excel 12.0;";

// Microsoft.Jet.OLEDB.4.0 driver is only for uploading Microsoft-Excel 2003 and earlier versions.

OleDbConnection excelConn = new OleDbConnection(excelConnStr);

excelConn.Open();

DataTable dtSheetName = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

if (dtSheetName != null && dtSheetName.Rows.Count > 0)

{

string strSheetName = dtSheetName.Rows[0]["TABLE_NAME"].ToString();//gives first sheetname in the excel

excelCommand = new OleDbCommand("SELECT * FROM [" + strSheetName + "]", excelConn);//excelCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", excelConn);

excelDataAdapter.SelectCommand = excelCommand;

excelDataAdapter.Fill(dt);

}

excelConn.Close();

}

}

catch (Exception objEx)

{

throw objEx;

}

finally

{

//Delete the saved file from server path after reading the data from the path.Because it is uploaded from client machine to database.We are saving to server to avoid errors.After process we are deleting here.

if (File.Exists(strFilePath))

File.Delete(strFilePath);

}

return dt;

}