热线电话：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考试详情；

▷ 想了解CDA考试含金量，点击>>> “CDA含金量” 了解CDA考试详情；

▷ 想获取CDA考试时间/费用/条件/大纲/通过率，点击 >>>“CDA考试官网” 了解CDA考试详情；

字段

数据分析咨询请扫描二维码

上一篇使用C#将Excel文件中数据导入SQL Server数据库
下一篇SqlServer表和Excel文件数据相互复制方法
CDA考试动态
CDA报考指南
热门栏目
最新资讯
更多
Copyright © 2015-present 北京国富如荷网络科技有限公司 All Rights Reserved.
京ICP备11001960号-9   京公网安备 11010802034615号   经营许可证编号：京B2-20210330
客服在线
立即咨询