欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

C#實現(xiàn)Excel數(shù)據(jù)導(dǎo)入到SQL server數(shù)據(jù)庫

 更新時間:2024年03月25日 10:32:00   作者:susan花雨  
這篇文章主要為大家詳細(xì)介紹了在C#中如何實現(xiàn)Excel數(shù)據(jù)導(dǎo)入到SQL server數(shù)據(jù)庫中,文中的示例代碼簡潔易懂,希望對大家有一定的幫助

將Excel數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫,用winfrom系統(tǒng),如圖:

該系統(tǒng)可以選擇導(dǎo)入的Excel文件,設(shè)置要導(dǎo)入的數(shù)據(jù)庫的基本設(shè)置。

代碼:

winfrom窗體:

 public partial class ExceldaoSql : Form
    {
        Sqlconnn sqlcon = new Sqlconnn();
        public ExceldaoSql()
        {
            InitializeComponent();
        }
        string str_Excel_Path;
        private void ExceldaoSql_Load(object sender, EventArgs e)
        {
            txt_Server.Text = "(local)";
            //cbox_Server.Text = "machine";
            cbox_Server.DropDownStyle = ComboBoxStyle.DropDownList;
            DataTable dt = sqlcon.f1();
            if (dt != null)
            {
                if (dt.Rows.Count != 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        cbox_Server.Items.Add(dt.Rows[i][0].ToString().Trim());//向控件中添加數(shù)據(jù)              
                    }
                }
            }
            ckbox_Windows.Checked = true;          
            txt_Name.Enabled = false;//禁用按鈕
            txt_Pwd.Enabled = false;        
        }
        //選擇多個Excel文件
        private void button1_Click(object sender, EventArgs e)
        {
            //只能打開一個文件------------------------------------------
            openFileDialog1.Filter = "Excel文件|*.xlsx";//設(shè)置打開文件篩選器           
            openFileDialog1.Title = "打開Excel文件";//設(shè)置打開文件標(biāo)題
            openFileDialog1.Multiselect = true;//允許選中多個文件
            if (openFileDialog1.ShowDialog() == DialogResult.OK)//判斷是否選擇了文件
            {
                str_Excel_Path = openFileDialog1.FileName.ToString();//獲取選擇的文件地址
                txt_Path.Text = str_Excel_Path;//在textBox1中顯示選擇的文件地址                  
            } 
            
        }
        //獲取SQL Server服務(wù)器上的所有數(shù)據(jù)庫信息    
        private void button2_Click(object sender, EventArgs e)
        {
            cbox_Server.Items.Clear();   
            DataTable dt = sqlcon.f1();
            if (dt != null)
            {
                if (dt.Rows.Count != 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        cbox_Server.Items.Add(dt.Rows[i][0].ToString().Trim());//向控件中添加數(shù)據(jù)              
                    }
                }
            }
        }

        //將選擇的Excel表導(dǎo)入到SQL Server數(shù)據(jù)庫中
        private void button3_Click(object sender, EventArgs e)
        {
            if (txt_Path.Text != "")
            {
                string[] P_str_Names = txt_Path.Text.Split(',');//存儲所有選擇的Excel文件名
                string P_str_Name = "";//儲存遍歷到的Excel文件名                     
                List<string> P_list_SheetNames = new List<string>();//創(chuàng)建泛型集合對象,用來存儲工作表名稱      
                for (int i = 0; i < P_str_Names.Length; i++)//遍歷所有選擇的Excel文件名
                {
                    P_str_Name = P_str_Names[i];//記錄遍歷到的Excel文件名 

                    P_list_SheetNames = GetSheetName(P_str_Name);//獲取Excel文件中的所有工作表名

                    for (int j = 0; j < P_list_SheetNames.Count; j++)//遍歷所有工作表
                    {
                        if (ckbox_Windows.Checked)//用Windows身份驗證登錄SQL Server                   
                        //將工作表內(nèi)容導(dǎo)出到SQL Server
                        {
                            ImportDataToSql(P_str_Name, P_list_SheetNames[j], "Data Source='" + txt_Server.Text + "';Initial Catalog='" + cbox_Server.Text + "';Integrated Security=True;");
                        }
                        else if (ckbox_SQL.Checked)//用SQL Server身份驗證登錄SQL Server                 
                        {
                            ImportDataToSql(P_str_Name, P_list_SheetNames[j], "Data Source='" + txt_Server.Text + "'Database='" + cbox_Server.Text + "';Uid='" + txt_Name.Text + "';Pwd='" + txt_Pwd.Text + "';");
                        }
                    }
                }
                MessageBox.Show("已經(jīng)將所有選擇的Excel工作表導(dǎo)入到了SQL Server數(shù)據(jù)庫中!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);                                
            }
            else
            {
                MessageBox.Show("請選擇需要導(dǎo)入數(shù)據(jù)庫的文件!");
            }         
        }
     
        //獲取Excel文件中的所有工作表名稱
        private List<string> GetSheetName(string P_str_Name)
        {
           List<string > P_list_SheetName=new List<string> ();//創(chuàng)建泛型集合對象
            //連接Excel數(shù)據(jù)庫
           //OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + P_str_Name + ";Extended Properties=Excel 8.0;");
           OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + P_str_Name + ";Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"");      
           olecon.Open();//打開數(shù)據(jù)庫連接
           System.Data.DataTable DTable = olecon.GetSchema("Tables");//創(chuàng)建表對象
           DataTableReader DTReader = new DataTableReader(DTable);//創(chuàng)建表讀取對象
           while (DTReader.Read())
           {
               string p_str_sName=DTReader ["Table_Name"].ToString ().Replace ('$',' ').Trim ();//記錄工作表名稱
               if (!P_list_SheetName.Contains(p_str_sName))//判斷泛型集合是否已經(jīng)存在該工作表名稱
                   P_list_SheetName.Add(p_str_sName);//將工作表加入到泛集合中
           }
            DTable =null;//清空表對象
            DTReader =null ;//清空表讀取對象
            olecon .Close ();//關(guān)閉數(shù)據(jù)庫連接
            return P_list_SheetName ;
        }

       
        /*將Excel中指定工作表內(nèi)容導(dǎo)入SQL Server數(shù)據(jù)庫中*/
        public void ImportDataToSql(string p_str_Excel,string  p_str_SheetName,string p_str_SqlCon)
        {
            DataSet myds = new DataSet();//創(chuàng)建數(shù)據(jù)集對象
            try
            {
                //獲得全部數(shù)據(jù)
                //string P_str_OledbCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + p_str_Excel + ";Extended Properties=Excel 8.0;";
                string P_str_OledbCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + p_str_Excel + ";Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"";
                OleDbConnection oledbcon = new OleDbConnection(P_str_OledbCon);//創(chuàng)建Oledb數(shù)據(jù)庫連接對象
                string p_str_ExcelSql = "";//記錄要執(zhí)行的Excel查詢語句
                OleDbDataAdapter oledbda = null;//創(chuàng)建Oledb數(shù)據(jù)橋接器對象
                p_str_ExcelSql = string.Format("select * from [{0}$]",p_str_SheetName);//記錄要執(zhí)行的Excel查詢語句
                oledbda = new OleDbDataAdapter(p_str_ExcelSql, P_str_OledbCon);//使用數(shù)據(jù)橋接器執(zhí)行Excel查詢
                oledbda.Fill(myds, p_str_SheetName);//填充數(shù)據(jù)
                //定義變量,用來記錄創(chuàng)建表的SQL語句
                string P_str_CreateSql = string.Format("create table {0}(", p_str_SheetName);
                foreach (DataColumn c in myds .Tables [0].Columns )//遍歷數(shù)據(jù)集中的所有行
                {
                    P_str_CreateSql += string.Format("[{0}]text,", c.ColumnName);//在表中創(chuàng)建字段
                }
                P_str_CreateSql = P_str_CreateSql + ")";//完善創(chuàng)建表的SQL語句
                //創(chuàng)建SQL數(shù)據(jù)庫連接對象
                using (SqlConnection sqlcon=new SqlConnection (p_str_SqlCon ))
                {
                    sqlcon.Open();//打開數(shù)據(jù)庫連接
                    SqlCommand sqlcmd = sqlcon.CreateCommand();//創(chuàng)建執(zhí)行命令對象
                    sqlcmd.CommandText = P_str_CreateSql;//指定要執(zhí)行的SQL數(shù)據(jù)
                    sqlcmd.ExecuteNonQuery();//執(zhí)行操作
                    sqlcon.Close();//關(guān)閉數(shù)據(jù)庫連接
                }
                using (SqlBulkCopy bcp = new SqlBulkCopy(p_str_SqlCon))//用bcp導(dǎo)入數(shù)據(jù)
                {
                    bcp.BatchSize = 100;//每次傳輸?shù)男袛?shù)
                    bcp.DestinationTableName = p_str_SheetName;//定義目標(biāo)表
                    bcp.WriteToServer(myds.Tables[0]);//將數(shù)據(jù)寫入SQL server數(shù)據(jù)表
                }
            }
            catch
            {
                MessageBox.Show("SQL Server 數(shù)據(jù)庫中已經(jīng)存在" + p_str_SheetName + "表!", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        private void ckbox_Windows_CheckedChanged(object sender, EventArgs e)
        {

            if (ckbox_Windows.CheckState == CheckState.Checked)//當(dāng)選擇Windows身份驗證
            {
                ckbox_SQL.Checked = false ;//SQL Server身份驗證不能選中
                //txt_Name.ReadOnly = true;//設(shè)為只讀
                //txt_Pwd.ReadOnly = true;
                txt_Name.Enabled = false;//禁用按鈕
                txt_Pwd.Enabled = false;
                txt_Name.Text = "";
                txt_Path.Text = "";
                txt_Path.Text = str_Excel_Path;//在textBox1中顯示選擇的文件地址 
            }
            else
            {
                ckbox_SQL.Checked = true ;
                //txt_Name.ReadOnly = false ;//設(shè)為只讀
                //txt_Pwd.ReadOnly = false;
                txt_Name.Enabled = true;//啟用按鈕
                txt_Pwd.Enabled = true;
                txt_Name.Text  = "sa";
                txt_Path.Text = "";
                txt_Path.Text = str_Excel_Path;//在textBox1中顯示選擇的文件地址 
            }
        }

        private void ckbox_SQL_CheckedChanged(object sender, EventArgs e)
        {
            if (ckbox_SQL.CheckState == CheckState.Checked)
            {
                ckbox_Windows.Checked = false;
                //txt_Name.ReadOnly = false;//設(shè)為只讀
                //txt_Pwd.ReadOnly = false;
                txt_Name.Enabled = true ;//啟用按鈕
                txt_Pwd.Enabled = true;
                txt_Name.Text = "sa";
                txt_Path.Text = "";
                txt_Path.Text = str_Excel_Path;//在textBox1中顯示選擇的文件地址 
            }
            else
            {
                ckbox_Windows.Checked = true ;
                //txt_Name.ReadOnly = true;//設(shè)為只讀
                //txt_Pwd.ReadOnly = true;
                txt_Name.Enabled = false;//禁用按鈕
                txt_Pwd.Enabled = false;
                txt_Name.Text = "";
                txt_Path.Text = "";
                txt_Path.Text = str_Excel_Path;//在textBox1中顯示選擇的文件地址 
            }
        }
       
    }

連接數(shù)據(jù)庫的類:Sqlconnn

class Sqlconnn
    {
        private static string constr = "server=(local);Initial Catalog=D_total;Integrated Security=True";
       // private static string constr = "Data Source =192.168.1.201;Initial Catalog=D_total23 ;User Id=sa;Password=123";
        public DataTable f1()
        {
            string A = "select name from master..sysdatabases";//查詢本數(shù)據(jù)庫信息
            return Only_Table1(A);
        }
        public DataTable Only_Table1(string exec)
        {
            System.Data.DataTable dt_jdl = new DataTable();
            try
            {
                using (SqlConnection con = new SqlConnection(constr))
                {
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    } if (con.State == ConnectionState.Open || con.State == ConnectionState.Connecting)
                    {
                        SqlDataAdapter sda2 = new SqlDataAdapter(exec, con);//全部通過寫存儲過程即可
                        DataSet ds2 = new DataSet();
                        sda2.Fill(ds2, "cxq");
                        dt_jdl = ds2.Tables["cxq"];
                        sda2.Dispose();
                        ds2.Dispose();
                    }
                    con.Close();
                }
                return dt_jdl;
            }
            catch (Exception EX)
            {
                return null;
            }
        }
    }

系統(tǒng)優(yōu)點:可以導(dǎo)入Excel的多個工作表的數(shù)據(jù)

系統(tǒng)有一個缺陷:無法將相同的表導(dǎo)入數(shù)據(jù)庫多次,也就是說只能導(dǎo)入數(shù)據(jù)庫一次,無法覆蓋和添加。

到此這篇關(guān)于C#實現(xiàn)Excel數(shù)據(jù)導(dǎo)入到SQL server數(shù)據(jù)庫的文章就介紹到這了,更多相關(guān)C# Excel數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!

相關(guān)文章

最新評論