簡單的將Excel資料同步到SqlServer資料庫中

標籤：hdr   window   new   mic   fill   IV   fir   .text   private   

1.建立一個WinForm程式，添加一個Button控制項

2.Button事件

 private void button1_Click(object sender, EventArgs e)        {            System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();            if (fd.ShowDialog() == DialogResult.OK)            {                string fileName = fd.FileName;                syncExcelToDataBase(fileName, textBox1.Text.ToString());            }        }

3.從Excel讀取資料到DataTable

       private void syncExcelToDataBase(string excelfilepath, string sheetName)        {            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelfilepath + ";" + "Extended Properties=‘Excel 12.0; HDR = Yes‘";            string strExcel = string.Format("select * from [{0}$]", sheetName);            OleDbDataAdapter da = new OleDbDataAdapter(strExcel, strConn);            DataSet ds = new DataSet();            try            {                da.Fill(ds);                dt = ds.Tables[0];                if (dt != null && dt.Rows.Count > 0)                {                    foreach (DataRow dr in dt.Rows)                    {                        Update(dr);                    }                }            }            catch (Exception err)            {                MessageBox.Show("操作失敗！" + err.ToString());            }        }

4.更新到Sql server 資料庫中

        private void Update(DataRow dr)        {             string saleName = dr["TELESALES MANILA"].ToString().Trim();            string crn = dr["CRN"].ToString().Trim();            if(!string.IsNullOrEmpty(saleName) && saleName != "#N/A")            {                string[] arr = saleName.Split(‘ ‘);                if(arr.Length>0)                {                    string sql1 = "SELECT *FROM [dbo].[PUserAdmin] where FirstName=‘" + arr[0] + "‘ and LastName=‘" + arr[1] + "‘";                    DataTable dtResult = SqlHelper.ExecuteDataTable(sql1,CommandType.Text);                        string sql2 = "update [dbo].[PAgents] set BFax=‘" + dtResult.Rows[0][0] + "‘ where CRN=‘" + crn + "‘";                        SqlHelper.ExecuteNonQuery(sql2, CommandType.Text);                }            }        }

簡單的將Excel資料同步到SqlServer資料庫中