如何在将Excel数据导入数据库时​​通过C＃过滤Excel表格 [英] How to Filter Excel Sheets through C# while Importing the excel data to database

问题描述

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

现在我可以获取属于Excel文件的所有Excel工作表。

但是怎么能我检查If and Only如果工作表的名称中有Underscore（例如student_data，teachers_data），那么只有工作表的数据将填入数据库



你可以帮我这个..

提前谢谢。

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

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

推荐答案

使用 String.Contains 方法 [ ^ ]。

这篇关于如何在将Excel数据导入数据库时​​通过C＃过滤Excel表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！