public DataTable GetExcelData(string _sfile)
{
EventLog.WriteEntry("GETEXCEL DATA", "GET EXCELDATE STARTED");
OleDbConnection xlconn = new OleDbConnection();
xlconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + _sfile + "';Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");
xlconn.Open();
DataTable dtExcel = new DataTable();
dtExcel = xlconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
OleDbDataAdapter xlda = new OleDbDataAdapter();
DataTable xldt = new DataTable();
if (dtExcel != null)
{
String[] excelSheetNames = new String[dtExcel.Rows.Count];
int i = 0;
EventLog.WriteEntry("Loop", (dtExcel.Rows.Count).ToString());
foreach (DataRow row in dtExcel.Rows)
{
EventLog.WriteEntry("Excel FOR Loop", dtExcel.Rows.Count.ToString());
excelSheetNames[i] = row["TABLE_NAME"].ToString();
EventLog.WriteEntry("ExcelSheet Name", excelSheetNames[i].ToString());
string lastChars = excelSheetNames[i].Substring(excelSheetNames[i].Length - 1);
EventLog.WriteEntry(" lastChar", lastChars);
if (lastChars == "_")
{
excelSheetNames[i] = excelSheetNames[i].Remove(excelSheetNames[i].Length - 1);
EventLog.WriteEntry("After Remove" ,excelSheetNames[i]);
}
string strQ = "select * from [" + excelSheetNames[i] + "]";
xlda = new OleDbDataAdapter(strQ, xlconn);
EventLog.WriteEntry("ExcelSheet Name After", excelSheetNames[i].ToString());
xlda.Fill(xldt);
i++;
}
}
xlconn.Close();
return xldt;
}