private String[] GetExcelSheetNames(string excelFile) { OleDbConnection objConn = null; SqlConnection objSqlConn = null; System.Data.DataTable dt = null; try { // Connection String. Change the excel file to the file you // will search. string strpath = txtFilePath.Text; string excelConnectionString = ""; // Create connection object by using the preceding connection string. if (strpath.Trim().EndsWith(".xlsx")) { excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", strpath); } else if (strpath.Trim().EndsWith(".xls")) { excelConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", strpath); } objConn = new OleDbConnection(excelConnectionString); // Open connection with the database. objConn.Open(); // Get the data table containg the schema guid. dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return null; } String[] strExcelSheets = new String[dt.Rows.Count]; int i = 0; // Add the sheet name to the string array. foreach (DataRow row in dt.Rows) { strExcelSheets[i] = row["TABLE_NAME"].ToString(); i++; } // Loop through all of the sheets if you want too... for (int j = 0; j < strExcelSheets.Length; j++) { // Query each excel sheet. OleDbConnection connection = new OleDbConnection(excelConnectionString); connection.Open(); OleDbCommand cmd = new OleDbCommand("Select * FROM [" + strExcelSheets[j].Trim() + "]", connection); using (OleDbDataReader dr = cmd.ExecuteReader()) { // SQL Server Connection String string sqlConnectionString = @"Data Source=D-113059445;Initial Catalog=Sample;Integrated Security=True"; objSqlConn = new SqlConnection(sqlConnectionString); objSqlConn.Open(); // Bulk Copy to SQL Server using (SqlBulkCopy objbulkCopy = new SqlBulkCopy(sqlConnectionString)) { objbulkCopy.DestinationTableName = "[Sample].[dbo].[Employee]"; objbulkCopy.WriteToServer(dr); lblMsg.Visible = true; lblMsg.Text = ("Data Exoprted To Sql Server Succefully"); } } } return strExcelSheets; } catch (SqlException ex) { lblMsg.Visible=true; lblMsg.Text=(ex.ToString()); return null; } }
String.Contains
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)