How to Filter Excel Sheets through C# while Importing the excel data to database

[sasen903]

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;
             }
          }

Now I can fatch all the excel sheet belongs to the Excel File.
But How can I check "If and Only If the sheets are havin Underscore in their name(eg. student_data,te achers_data) then only the data of the sheets will populate in tha data base"

Can you help me on this..
Thanks in advance.

[Rabbit]

Use the index method of the string class to look for an underscore character in the name of the sheet.

[Landy67]

Hello,

Here is my solution to export the second worksheet to datatable.

            Workbook workbook = new Workbook();
            workbook.LoadFromFile("sample.xlsx);

            //Initialize worksheet
            Worksheet sheet = Worksheet sheet = workbook.Worksheets["student_data"];

            //Export Data
            DataTable DT = sheet.ExportDataTable();

In order to achieve quickly, I use a commercial .NET Excel component, Spire.XLS for .NET in this solution.