熱線電話：13121318867

登錄
首頁精彩閱讀（c#）Excel與SqlServer間數據相互導入
（c#）Excel與SqlServer間數據相互導入
2018-01-02
收藏

（c#）Excel與SqlServer間數據相互導入

在數據庫編程中，常會遇到要把數據庫表信息導入Excel中，  有時則是把Excel內容導入數據庫中。在這里，將介紹一種比較方便快捷的方式，也是比較普遍的。其實，這方法你并不陌生。原理很簡單，把數據庫表或Excel內容讀取到dataset類型的變量中，再逐條插入到你想要導入的地方。不管是Excel轉SqlServer，還是SqlServer轉Excel，也是適用的。

其實，Excel的也可以用sql語句來操作的。說到這里，你也大概知道怎樣做吧！接下來，通過一個實例來慢慢講解。(該實例下載地址)

首先，提供該實例的數據庫和表：

數據庫名為：MyDataBase

表名為：      CutClassTable

表創建語句如下：

[c-sharp]view plaincopy
  1. use MyDataBase  
  2. go  
  3. Create Table CutClassTable(  
  4.     StudentID varchar(20) Primary key,  
  5.     Name varchar(20) not null,  
  6.     CutClassSum int default(0),  
  7.     Cause varchar(200) default('未知')  
  8. )  

該實例運行圖：

一.Excel 導入到 SqlServer

大概步驟：

a.用sql語句中“select * from [Sheet1$]”語句獲取 Excel內容，存放到dataset類型變量中。

b.用insert 語句把dataset內容插入到SqlServer里面。

源代碼如下：

[c-sharp]view plaincopy
  1. using System.Data.SqlClient;    //用于SqlServer數據庫操作  
  2. using System.Data.OleDb;        //用于Excel數據庫操作  
  3. #region Excel導入到Sqlserver  
  4.   
  5.         //指定的Excel文件名  
  6.         private string strFileName;  
  7.         //是否已經獲取到Excel文件的路徑  
  8.         private bool hasFile;  
  9.         //是否已經把Excel讀取到Datase中  
  10.         private bool hasContent;  
  11.         //從Excel中讀取到內容  
  12.         private DataSet dsExcel;  
  13.   
  14.         public ExcelOperate()  
  15.         {  
  16.             InitializeComponent();  
  17.             //初始化為false  
  18.             hasFile = false;  
  19.             hasContent = false;  
  20.         }  
  21.   
  22.         private void Form1_Load(object sender, EventArgs e)  
  23.         {}  
  24.   
  25.   
  26.         public void ShowExcelContent()  
  27.         {   
  28.             //下面是Excel數據庫訪問操作：  
  29.                 //連接字符串  
  30.                 string strCon = @"Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + strFileName + "';Extended Properties=Excel 8.0";  
  31.                 //要執行的sql語句  
  32.                 string strSql = "select * from [Sheet1$]";  
  33.                 //創建OleDb連接對象  
  34.                 OleDbConnection oleDbCon = new OleDbConnection(strCon);  
  35.                 //創建OleDbDataAdapter  
  36.                 OleDbDataAdapter oleDbDa = new OleDbDataAdapter(strSql, oleDbCon);  
  37.                 //實例化ds  
  38.                 dsExcel = new DataSet();  
  39.                 //打開連接  
  40.                 oleDbCon.Open();  
  41.                 //從數據庫讀取內容并填充到ds中  
  42.                 oleDbDa.Fill(dsExcel, "Info");  
  43.                 //關閉連接  
  44.                 oleDbCon.Close();  
  45.   
  46.                 //綁定數據源  
  47.                 bindingSource1.DataSource = dsExcel.Tables[0];  
  48.                 //下面該句是bindingNavigator的數據綁定方法，但用該語句會提示錯誤為：bindingNavigator為只讀。所以，只能在屬性欄里的BindingSoure屬性里修改  
  49.                 //bindingNavigator1.DataBindings = bindingSource1;  
  50.                 //顯示到DataGridView  
  51.                 dataGridView1.DataSource = bindingSource1;  
  52.                 //標記ds有內容  
  53.                 hasContent = true;  
  54.         }  
  55.   
  56.         public void ToSqlServer()  
  57.         {  
  58.             //要執行的sql語句，暫時無.這里采用Stringbuilder類，因為接下來字符串連接操作比較多  
  59.             StringBuilder strbSql = new StringBuilder();  
  60.             //SqlServer連接語句，該實例數據庫為“MyDataBase”  
  61.             string strCon = @"Data Source=Localhost;Initial Catalog=MyDataBase;Integrated Security=True";  
  62.             //創建連接  
  63.             SqlConnection sqlCon = new SqlConnection(strCon);  
  64.             //創建一個空的sql執行對象  
  65.             SqlCommand sqlCom = new SqlCommand();  
  66.             //把連接對象賦予sqlCom  
  67.             sqlCom.Connection = sqlCon;  
  68.             //打開連接  
  69.             sqlCon.Open();  
  70.             //用try catch 語句，捕抓錯誤  
  71.             try  
  72.             {  
  73.                 //連續往SqlServer表里插入數據  
  74.                 for (int i = 0; i < dsExcel.Tables[0].Rows.Count; i++)  
  75.                 {  
  76.                     //要執行的insert語句：有一點要注意，在SqlServer中用  '' 標記字符串，這里記得要添加  
  77.                     strbSql.Append("insert into CutClassTable(StudentID, Name, CutClassSum, Cause) values('");  
  78.                     for (int j = 0; j < 3; j++)  
  79.                     {  
  80.                         strbSql.Append(dsExcel.Tables[0].Rows[i].ItemArray[j].ToString() + "','");  
  81.                     }  
  82.                     strbSql.Append(dsExcel.Tables[0].Rows[i].ItemArray[3].ToString() + "')");  
  83.   
  84.                     //執行sql語句  
  85.                     string strSql = strbSql.ToString();  
  86.                     sqlCom.CommandText = strSql;  
  87.                     sqlCom.ExecuteNonQuery();  
  88.                     //strbSql里面內容要清除，否則會疊加的，提示信息重復插入等信息  
  89.                     strbSql.Remove(0, strbSql.Length);  
  90.                 }  
  91.                 //插入成功提示  
  92.                 MessageBox.Show("導入SqlServer成功！請查看?。?, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  93.   
  94.             }  
  95.             catch (Exception ex)  
  96.             {  
  97.                 //失敗提示  
  98.                 MessageBox.Show("導入SqlServer過程中發生錯誤！/n錯誤提示：" + ex.Message, "警告", MessageBoxButtons.OK, MessageBoxIcon.Error);  
  99.             }  
  100.             finally  
  101.             {  
  102.                 //關閉連接  
  103.                 sqlCon.Close();  
  104.             }  
  105.         }  
  106.   
  107.         /// <summary>  
  108.         /// 選擇Excel文件  
  109.         /// </summary>  
  110.         /// <param name="sender"></param>  
  111.         /// <param name="e"></param>  
  112.         private void bntFindFile_Click(object sender, EventArgs e)  
  113.         {  
  114.             //文件選擇對話框  
  115.             OpenFileDialog FilePath = new OpenFileDialog();  
  116.             //判斷是否選擇好文件  
  117.             if (FilePath.ShowDialog() == DialogResult.OK)  
  118.             {  
  119.                 hasFile = true;  
  120.                 strFileName = FilePath.FileName;  
  121.                 tbFileName.Text = strFileName;  
  122.             }  
  123.         }  
  124.   
  125.         /// <summary>  
  126.         /// 顯示Excel內容到DatagridView  
  127.         /// </summary>  
  128.         /// <param name="sender"></param>  
  129.         /// <param name="e"></param>  
  130.         private void bntShowExcel_Click(object sender, EventArgs e)  
  131.         {  
  132.             //判斷是否已經選擇好文件  
  133.             if (hasFile)  
  134.             {  
  135.                 //顯示Excel內容到DatagridView  
  136.                 ShowExcelContent();  
  137.             }  
  138.         }  
  139.   
  140.         private void btnToSqlServer_Click(object sender, EventArgs e)  
  141.         {  
  142.             //判斷ds是否有內容  
  143.             if (hasContent)  
  144.             {     
  145.                 //導入到SqlServer  
  146.                 ToSqlServer();  
  147.             }  
  148.         }  
  149.         #endregion  

運行結果：

二.SqlServer導入Excel

步驟：（同上）

源代碼如下：

[c-sharp]view plaincopy
  1. #region  SqlServer導入到Excel  
  2.   
  3.         //新創建的Excel文件路徑  
  4.         public string strSaveFileName;  
  5.         //存放SqlServer內容  
  6.         public DataSet dsSqlServer;  
  7.   
  8.         /// <summary>  
  9.         /// 選擇Excel的保存路徑  
  10.         /// </summary>  
  11.         /// <returns>已經選擇好文件路徑則返回true， 否則false</returns>  
  12.         public bool ChoicePath()  
  13.         {  
  14.             //保存對話框  
  15.             SaveFileDialog savePath = new SaveFileDialog();  
  16.             if (savePath.ShowDialog() == DialogResult.OK)  
  17.             {  
  18.                 strSaveFileName = savePath.FileName;  
  19.                 //判斷文件是否已存在  
  20.                 if (IsExist(strSaveFileName))  
  21.                 {  
  22.                     return true;  
  23.                 }  
  24.                 else  
  25.                 {  
  26.                     return false;  
  27.                 }   
  28.             }  
  29.             else  
  30.             {  
  31.                 return false;  
  32.             }  
  33.         }  
  34.   
  35.         /// <summary>  
  36.         /// 判斷文件是否已存在，存在則詢問是否覆蓋  
  37.         /// </summary>  
  38.         /// <param name="fileName">文件路徑</param>  
  39.         /// <returns>“不存在”或“存在并允許覆蓋”返回true， 存在但不覆蓋返回false</returns>  
  40.         public bool IsExist(string fileName)  
  41.         {   
  42.             //判斷文件是否已存在     
  43.             if (System.IO.File.Exists(fileName + ".xls"))     
  44.            {   //提示是否覆蓋     
  45.                if (MessageBox.Show("該文件已經存在，是否覆蓋？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)     
  46.                {  
  47.                    return false;    
  48.                }     
  49.                else    
  50.                {   //刪除已有文件      
  51.                    System.IO.File.Delete(fileName + ".xls");  
  52.                    return true;  
  53.                }     
  54.            }  
  55.            return true;  
  56.         }  
  57.   
  58.         /// <summary>  
  59.         /// 獲取SqlServer內容，存放到dsSqlServer里面  
  60.         /// </summary>  
  61.         public void GetDs()  
  62.         {  
  63.             //要執行的sql語句  
  64.             String strSql = "select * from CutClassTable";  
  65.             //SqlServer連接語句，該實例數據庫為“MyDataBase”  
  66.             string strCon = @"Data Source=Localhost;Initial Catalog=MyDataBase;Integrated Security=True";  
  67.             //創建連接  
  68.             SqlConnection sqlCon = new SqlConnection(strCon);  
  69.             //.......  
  70.             SqlDataAdapter sqlDa = new SqlDataAdapter(strSql, strCon);  
  71.             dsSqlServer = new DataSet();  
  72.             sqlCon.Open();  
  73.             sqlDa.Fill(dsSqlServer, "Info");  
  74.             sqlCon.Close();  
  75.         }  
  76.   
  77.         /// <summary>  
  78.         /// 創建Excel文件，在我的其它文章里，有講這方面內容，不清楚的可翻看前面章節  
  79.         /// </summary>  
  80.         private void CreateExcel()  
  81.         {  
  82.             try  
  83.             {  
  84.                 //創建Excel對象  
  85.                 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();   
  86.                 //創建新的Excel工作簿  
  87.                 Microsoft.Office.Interop.Excel.Workbook wBook = excel.Application.Workbooks.Add(Missing.Value);   
  88.                 //使Excel不可視  　　　　　　　  
  89.                 excel.Visible = false;   
  90.   
  91.                 //設置禁止彈出保存和覆蓋的詢問提示框   
  92.                 excel.DisplayAlerts = false;  
  93.                 excel.AlertBeforeOverwriting = true;  
  94.   
  95.                 //保存  
  96.                 wBook.SaveAs(@strSaveFileName,  
  97.                 Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,  
  98.                 Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value,  
  99.                 Missing.Value, Missing.Value);   
  100.   
  101.                 wBook = null;  
  102.                 //必須有該語句，才能有效結束，否則每次運行會產生一個Excel  
  103.                 excel.Quit();     
  104.                 excel = null;  
  105.   
  106.             }  
  107.             catch (Exception err)   
  108.             {   
  109.                 //錯誤提示  
  110.                 MessageBox.Show("Excel操作出錯！錯誤原因：" + err.Message, "提示信息",   
  111.                     MessageBoxButtons.OK, MessageBoxIcon.Information);   
  112.                   
  113.             }   
  114.         }  
  115.   
  116.         /// <summary>  
  117.         /// 把數據導入Excel  
  118.         /// </summary>  
  119.         private void InsertToExcel()  
  120.         {  
  121.             //下面是Excel數據庫訪問操作：  
  122.             //連接字符串  
  123.             string strCon = @"Provider = Microsoft.Jet.OLEDB.4.0;Data Source = '" + strSaveFileName + "';Extended Properties='Excel 8.0;HDR=NO'";  
  124.             //要執行的sql語句，采用stringbuilder類  
  125.             StringBuilder strbSql = new StringBuilder();  
  126.             //創建OleDb連接對象.  
  127.             OleDbConnection oleDbCon = new OleDbConnection(strCon);  
  128.             //創建執行對象  
  129.             OleDbCommand oleDbCom = new OleDbCommand();  
  130.             //賦予連接對象  
  131.             oleDbCom.Connection = oleDbCon;  
  132.             //打開連接  
  133.             oleDbCon.Open();  
  134.             //下面是數據插入到Excel  
  135.             try  
  136.             {  
  137.                 //創建一張新的工作表，表名為MySheet。你或許疑惑為什么不在原有的工作區Sheet1里導入，在后面我會講到！  
  138.                 string strSql = "create table MySheet (學號 char(20), 姓名 char(20), 曠課次數 int, 原因 char(255)) ";  
  139.                 //賦予sql語句  
  140.                 oleDbCom.CommandText = strSql;  
  141.                 //執行sql語句，創建一個新表  
  142.                 oleDbCom.ExecuteNonQuery();  
  143.                 //循環插入數據  
  144.                 for (int i = 0; i < dsSqlServer.Tables[0].Rows.Count; i++)  
  145.                 {  
  146.                     //要注意 參數要用 ' '括起來的。例如： 'value'  
  147.                     strbSql.Append("insert into [MySheet$] values('");  
  148.                     for (int j = 0; j < 3; j++)  
  149.                     {  
  150.                         strbSql.Append(dsSqlServer.Tables[0].Rows[i].ItemArray[j].ToString() + "','");  
  151.                     }  
  152.                     strbSql.Append(dsSqlServer.Tables[0].Rows[i].ItemArray[3].ToString() + "')");  
  153.                     //stringbuilder轉為string類型，因為sql語句不能直接執行stringbuilder類  
  154.                     strSql = strbSql.ToString();  
  155.                     oleDbCom.CommandText = strSql;  
  156.                     oleDbCom.ExecuteNonQuery();  
  157.                     //清除strbSql過往信息  
  158.                     strbSql.Remove(0, strbSql.Length);  
  159.                 }  
  160.                 //插入成功提示  
  161.                 MessageBox.Show("導入Excel成功！請查看?。?, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  162.             }  
  163.             catch (Exception ex)  
  164.             {  
  165.                 //失敗提示  
  166.                 MessageBox.Show("導入Excel過程中發生錯誤！/n錯誤提示：" + ex.Message, "警告", MessageBoxButtons.OK, MessageBoxIcon.Error);  
  167.             }  
  168.             finally  
  169.             {  
  170.                 oleDbCon.Close();  
  171.             }  
  172.               
  173.         }  
  174.   
  175.         /// <summary>  
  176.         /// 導入數據到Excel  
  177.         /// </summary>  
  178.         /// <param name="sender"></param>  
  179.         /// <param name="e"></param>  
  180.         private void bntToExcel_Click(object sender, EventArgs e)  
  181.         {  
  182.             //判斷是否選擇好路徑  
  183.             if (ChoicePath())  
  184.             {  
  185.                 //創建一個Excel文件  
  186.                 CreateExcel();  
  187.                 //獲取SqlServer表內容，存放到dsSqlServer里面  
  188.                 GetDs();  
  189.                 //導入數據到Excel  
  190.                 InsertToExcel();  
  191.             }  
  192.         }  
  193.  
  194.         #endregion  


運行結果：

知識點講解：

a.Excel的數據庫操作命名空間為“System.Data.OleDb”；

b.Excel中的表其實是工作區，新建一個Excel，你會看到左下角會有3個工作區（分別為Sheet1， Sheet2， Sheet3）。一般數據存放在Sheet1中。

c.Excel的數據庫連接字符串為@"Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + strFileName + "';Extended Properties=Excel 8.0"；

也可以如下：@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source = '" + strSaveFileName + "';Extended Properties='Excel 8.0;HDR=NO'";

在第二個連接字符串中，新增加了字段”HDR=NO“，表示若Excel里沒有字段名，則用自動采用F1，F2。。作為字段名。（其實，我嘗試過，是不行的。）還有一點要注意：在Extended Properties=后面可以再接其他擴展屬性字段，但要用單引號' '來括起來，否則會報錯：未找到可安裝的ISAM;即語法錯誤。

d.StingBuider與String的區別和用法

          Stringbuilder其實也是字符串。只不過，String是靜態的字符串，而Stringbuilder是動態的 。為什么這么說？String 一當創建，它的大小已經是固定的了，如果在它后面再連接字符串，它會創建一個新的String實例，借于容納更多的字符。所以，說String是靜態的。相反，StringBuilder則類似于動態數組，每當連接新的字符串時，它會動態地申請空間，而不需要重新再創建一個。  其方法如下： 


最后，補充幾點：

1.在SqlServer導入Excel例子中，我是采用新建一個表的方法，再往里面插入。其實，你可以直接往Sheet1里面插入數據，這樣更直接，更方便。我要如此做是迫于無奈的，因為調用“insert into [Sheet1$](F1, F2, F3,F4) values(...)”中，總是提示無F2,F3,F4字段。無奈啊，HDR=NO/YES, 均試過，都不行。還想過，直接刪掉Sheet1，再建個，可惜，也是夭折了。這里，就不發嘮叨了。

2.導入Excel的方法還有幾種，這里大概講一下原理。其中一種，是通過創建Excel對象，采用“wSheet.Cells[ x, y] = "修改或添加數據"”的方式，詳情見我相關的文章。

3.對于Excel， Access， SqlServer三者間或其他數據庫的數據導入，這種方法也是適用的。



CDA數據分析師考試相關入口一覽（建議收藏）：

? 想報名CDA認證考試，點擊>>>   “CDA報名” 了解CDA考試詳情；

? 想學習CDA考試教材，點擊>>> “CDA教材” 了解CDA考試詳情；

? 想加入CDA考試題庫，點擊>>> “CDA題庫” 了解CDA考試詳情；

? 想了解CDA考試含金量，點擊>>> “CDA含金量” 了解CDA考試詳情；

字段

數據分析咨詢請掃描二維碼

若不方便掃碼，搜微信號：CDAshujufenxi

上一篇使用C#將Excel文件中數據導入SQL Server數據庫
下一篇SqlServer表和Excel文件數據相互復制方法
數據分析師考試動態
CDA報考指南
數據分析學習
數據分析師資訊
更多

CDA教育閉環

常見問題

關于我們

CDA數據分析師公眾號

  • CDA數據分析師公眾號

CDA考試中心小程序

  • CDA考試中心服務號

CDA數據分析師App下載

  • CDA數據分析師App下載
Copyright © 2015-2021, www.ruiqisteel.com All Rights Reserved. CDA數據分析師(北京國富如荷網絡科技有限公司) 版權所有 京ICP備11001960號-9 京公網安備 11010802034615號 經營許可證編號：京B2-20210330
聯系電話：13321103290 (微信同號)

OK
客服在線
立即咨詢
日韩人妻系列无码专区视频,先锋高清无码,无码免费视欧非,国精产品一区一区三区无码
客服在線
立即咨詢