Click here to Skip to main content
15,662,426 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
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 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.
Posted
Updated 1-Mar-13 3:26am
v2

1 solution

 
Share this answer
 
Comments
sasen903 1-Mar-13 10:30am    
Actually I have never used it. Can you please guide me how I can use it in my program.
Richard MacCutchan 1-Mar-13 10:37am    
You start by reading the documentation.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900