C# 获取excel架构并的导入sqlserver的方法

            using
             System;

            using
             System.Collections.Generic;

            using
             System.ComponentModel;

            using
             System.Data;

            using
             System.Drawing;

            using
             System.Linq;

            using
             System.Text;

            using
             System.Windows.Forms;

            using
             System.Data.OleDb;

            using
             System.Data.SqlClient;

            using
             System.IO;


            namespace
             WindowsFormsApplication1
{
    
            public
            partial
            class
             Form3 : Form
    {
        
            public
             Form3()
        {
            InitializeComponent();
        }

        
            private
            void button1_Click(object sender, EventArgs e)
        {
            string connString = "server=localhost;database=Test;uid=sa;pwd=123456";
            OpenFileDialog ofd = new OpenFileDialog();
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                TransferData(ofd.FileName, connString);
            }
        }

        publicvoid TransferData(string excelFile, string connectionString)
        {
            try
            {
                string fileType = Path.GetExtension(excelFile);
                //获取全部数据string strConn="";
                if (fileType == ".xls")
                {
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";
                }
                else
                {
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFile + ";" + ";Extended Properties=Excel 12.0";
                }
                    OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();

                DataSet ds = new DataSet();
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, newobject[] { null, null, null, "Table" });
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string sheetName = dt.Rows[i]["Table_Name"].ToString();
                    if (sheetName.Contains("$") && !sheetName.Replace("‘", "").EndsWith("$"))
                    {
                        continue;
                    }

                    string strExcel = string.Format("select * from [{0}]", sheetName);
                    OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, conn);
                    DataSet dsItem = new DataSet();
                    myCommand.Fill(dsItem, sheetName);
                    dsItem.Tables[0].TableName = sheetName.TrimEnd(‘$‘).Trim();//读取出来的表明会自动添加‘$‘符号，需要去掉。
                    ds.Tables.Add(dsItem.Tables[0].Copy());
                }

                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    string sheetName = ds.Tables[i].TableName;
                    string strSql = string.Format("if object_id(‘{0}‘) is null create table {0}(", sheetName);
                    foreach (System.Data.DataColumn c in ds.Tables[0].Columns)
                    {
                        strSql += string.Format("[{0}] varchar(255),", c.ColumnName);
                    }
                    strSql = strSql.Trim(‘,‘) + ")";

                    using (SqlConnection sqlConn = new SqlConnection(connectionString))
                    {
                        sqlConn.Open();
                        using (SqlCommand cmd = sqlConn.CreateCommand())
                        {
                            cmd.CommandText = strSql;
                            cmd.ExecuteNonQuery();
                        }
                    }

                    using (SqlBulkCopy bcp = new SqlBulkCopy(connectionString))
                    {
                        bcp.BatchSize = 100;
                        bcp.DestinationTableName = sheetName;

                        //建立excel和sqlserver表之间的映射关系
                        bcp.ColumnMappings.Add(0, 0);
                        bcp.ColumnMappings.Add(1, 1);
                        bcp.ColumnMappings.Add(2, 2);

                        bcp.WriteToServer(ds.Tables[i]);
                    }
                }
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
    }
}