Excel upload for both .xls and .xlsx format using Oledb Connection:
For Excel 97-2003 connection string as
connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + excelFile + "; Extended Properties=" + "\"Excel 8.0;HDR=YES;+\"";
For Excel 2007 connection string as
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties=" + (char)34 + "Excel 12.0;IMEX=1;HDR=YES;" + (char)34;
Using GetOleDbSchemaTable in c#
to get All sheets in one excel file and give any name to read perfectly
dt = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" })
Or
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null)
Or
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,New Object[] { Nothing, Nothing, Nothing, "TABLE"})
Using Data Reader for Execute Query for dB (tables) And Data Adapter (ds-dataset)
oleda.Fill(ds, "TABLE");
dReader = cmd.ExecuteReader();
SqlBulkCopy gt = new SqlBulkCopy(conn);
gt.DestinationTableName = "AnbuEmp";//table name
gt.WriteToServer(dReader);
This way you upload ur excel sheets and directed to the tables using sql connection and oledb connection for excel sheets to identify the sheets and routine process.
Connection string is important one because when you upload the excel in correct format then only its open the connection and finally connection closed.