（c#）Excel与SqlServer间数据相互导入

          在数据库编程中，常会遇到要把数据库表信息导入Excel中，  有时则是把Excel内容导入数据库中。在这里，将介绍一种比较方便快捷的方式，也是比较普遍的。其实，这方法你并不陌生。原理很简单，把数据库表或Excel内容读取到dataset类型的变量中，再逐条插入到你想要导入的地方。不管是Excel转SqlServer，还是SqlServer转Excel，也是适用的。

          其实，Excel的也可以用sql语句来操作的。说到这里，你也大概知道怎样做吧！接下来，通过一个实例来慢慢讲解。(该实例下载地址)

          首先，提供该实例的数据库和表：

          数据库名为：MyDataBase

          表名为：      CutClassTable

          表创建语句如下：

use MyDataBase go Create Table CutClassTable( StudentID varchar(20) Primary key, Name varchar(20) not null, CutClassSum int default(0), Cause varchar(200) default('未知') )

         该实例运行图：

       

         一.Excel 导入到 SqlServer

            大概步骤：

           a.用sql语句中“select * from [Sheet1$]”语句获取 Excel内容，存放到dataset类型变量中。

           b.用insert 语句把dataset内容插入到SqlServer里面。

        源代码如下：

using System.Data.SqlClient; //用于SqlServer数据库操作 using System.Data.OleDb; //用于Excel数据库操作 #region Excel导入到Sqlserver //指定的Excel文件名 private string strFileName; //是否已经获取到Excel文件的路径 private bool hasFile; //是否已经把Excel读取到Datase中 private bool hasContent; //从Excel中读取到内容 private DataSet dsExcel; public ExcelOperate() { InitializeComponent(); //初始化为false hasFile = false; hasContent = false; } private void Form1_Load(object sender, EventArgs e) {} public void ShowExcelContent() { //下面是Excel数据库访问操作： //连接字符串 string strCon = @"Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + strFileName + "';Extended Properties=Excel 8.0"; //要执行的sql语句 string strSql = "select * from [Sheet1$]"; //创建OleDb连接对象 OleDbConnection oleDbCon = new OleDbConnection(strCon); //创建OleDbDataAdapter OleDbDataAdapter oleDbDa = new OleDbDataAdapter(strSql, oleDbCon); //实例化ds dsExcel = new DataSet(); //打开连接 oleDbCon.Open(); //从数据库读取内容并填充到ds中 oleDbDa.Fill(dsExcel, "Info"); //关闭连接 oleDbCon.Close(); //绑定数据源 bindingSource1.DataSource = dsExcel.Tables[0]; //下面该句是bindingNavigator的数据绑定方法，但用该语句会提示错误为：bindingNavigator为只读。所以，只能在属性栏里的BindingSoure属性里修改 //bindingNavigator1.DataBindings = bindingSource1; //显示到DataGridView dataGridView1.DataSource = bindingSource1; //标记ds有内容 hasContent = true; } public void ToSqlServer() { //要执行的sql语句，暂时无.这里采用Stringbuilder类，因为接下来字符串连接操作比较多 StringBuilder strbSql = new StringBuilder(); //SqlServer连接语句，该实例数据库为“MyDataBase” string strCon = @"Data Source=Localhost;Initial Catalog=MyDataBase;Integrated Security=True"; //创建连接 SqlConnection sqlCon = new SqlConnection(strCon); //创建一个空的sql执行对象 SqlCommand sqlCom = new SqlCommand(); //把连接对象赋予sqlCom sqlCom.Connection = sqlCon; //打开连接 sqlCon.Open(); //用try catch 语句，捕抓错误 try { //连续往SqlServer表里插入数据 for (int i = 0; i < dsExcel.Tables[0].Rows.Count; i++) { //要执行的insert语句：有一点要注意，在SqlServer中用 '' 标记字符串，这里记得要添加 strbSql.Append("insert into CutClassTable(StudentID, Name, CutClassSum, Cause) values('"); for (int j = 0; j < 3; j++) { strbSql.Append(dsExcel.Tables[0].Rows[i].ItemArray[j].ToString() + "','"); } strbSql.Append(dsExcel.Tables[0].Rows[i].ItemArray[3].ToString() + "')"); //执行sql语句 string strSql = strbSql.ToString(); sqlCom.CommandText = strSql; sqlCom.ExecuteNonQuery(); //strbSql里面内容要清除，否则会叠加的，提示信息重复插入等信息 strbSql.Remove(0, strbSql.Length); } //插入成功提示 MessageBox.Show("导入SqlServer成功！请查看！：", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { //失败提示 MessageBox.Show("导入SqlServer过程中发生错误！/n错误提示：" + ex.Message, "警告", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { //关闭连接 sqlCon.Close(); } } /// <summary> /// 选择Excel文件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void bntFindFile_Click(object sender, EventArgs e) { //文件选择对话框 OpenFileDialog FilePath = new OpenFileDialog(); //判断是否选择好文件 if (FilePath.ShowDialog() == DialogResult.OK) { hasFile = true; strFileName = FilePath.FileName; tbFileName.Text = strFileName; } } /// <summary> /// 显示Excel内容到DatagridView /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void bntShowExcel_Click(object sender, EventArgs e) { //判断是否已经选择好文件 if (hasFile) { //显示Excel内容到DatagridView ShowExcelContent(); } } private void btnToSqlServer_Click(object sender, EventArgs e) { //判断ds是否有内容 if (hasContent) { //导入到SqlServer ToSqlServer(); } } #endregion

           运行结果：

       

                 

           二.SqlServer导入Excel

             步骤：（同上）

            源代码如下：

#region SqlServer导入到Excel //新创建的Excel文件路径 public string strSaveFileName; //存放SqlServer内容 public DataSet dsSqlServer; /// <summary> /// 选择Excel的保存路径 /// </summary> /// <returns>已经选择好文件路径则返回true， 否则false</returns> public bool ChoicePath() { //保存对话框 SaveFileDialog savePath = new SaveFileDialog(); if (savePath.ShowDialog() == DialogResult.OK) { strSaveFileName = savePath.FileName; //判断文件是否已存在 if (IsExist(strSaveFileName)) { return true; } else { return false; } } else { return false; } } /// <summary> /// 判断文件是否已存在，存在则询问是否覆盖 /// </summary> /// <param name="fileName">文件路径</param> /// <returns>“不存在”或“存在并允许覆盖”返回true， 存在但不覆盖返回false</returns> public bool IsExist(string fileName) { //判断文件是否已存在 if (System.IO.File.Exists(fileName + ".xls")) { //提示是否覆盖 if (MessageBox.Show("该文件已经存在，是否覆盖？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No) { return false; } else { //删除已有文件 System.IO.File.Delete(fileName + ".xls"); return true; } } return true; } /// <summary> /// 获取SqlServer内容，存放到dsSqlServer里面 /// </summary> public void GetDs() { //要执行的sql语句 String strSql = "select * from CutClassTable"; //SqlServer连接语句，该实例数据库为“MyDataBase” string strCon = @"Data Source=Localhost;Initial Catalog=MyDataBase;Integrated Security=True"; //创建连接 SqlConnection sqlCon = new SqlConnection(strCon); //....... SqlDataAdapter sqlDa = new SqlDataAdapter(strSql, strCon); dsSqlServer = new DataSet(); sqlCon.Open(); sqlDa.Fill(dsSqlServer, "Info"); sqlCon.Close(); } /// <summary> /// 创建Excel文件，在我的其它文章里，有讲这方面内容，不清楚的可翻看前面章节 /// </summary> private void CreateExcel() { try { //创建Excel对象 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //创建新的Excel工作簿 Microsoft.Office.Interop.Excel.Workbook wBook = excel.Application.Workbooks.Add(Missing.Value); //使Excel不可视 　　　　　　　 excel.Visible = false; //设置禁止弹出保存和覆盖的询问提示框 excel.DisplayAlerts = false; excel.AlertBeforeOverwriting = true; //保存 wBook.SaveAs(@strSaveFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); wBook = null; //必须有该语句，才能有效结束，否则每次运行会产生一个Excel excel.Quit(); excel = null; } catch (Exception err) { //错误提示 MessageBox.Show("Excel操作出错！错误原因：" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); } } /// <summary> /// 把数据导入Excel /// </summary> private void InsertToExcel() { //下面是Excel数据库访问操作： //连接字符串 string strCon = @"Provider = Microsoft.Jet.OLEDB.4.0;Data Source = '" + strSaveFileName + "';Extended Properties='Excel 8.0;HDR=NO'"; //要执行的sql语句，采用stringbuilder类 StringBuilder strbSql = new StringBuilder(); //创建OleDb连接对象. OleDbConnection oleDbCon = new OleDbConnection(strCon); //创建执行对象 OleDbCommand oleDbCom = new OleDbCommand(); //赋予连接对象 oleDbCom.Connection = oleDbCon; //打开连接 oleDbCon.Open(); //下面是数据插入到Excel try { //创建一张新的工作表，表名为MySheet。你或许疑惑为什么不在原有的工作区Sheet1里导入，在后面我会讲到！ string strSql = "create table MySheet (学号 char(20), 姓名 char(20), 旷课次数 int, 原因 char(255)) "; //赋予sql语句 oleDbCom.CommandText = strSql; //执行sql语句，创建一个新表 oleDbCom.ExecuteNonQuery(); //循环插入数据 for (int i = 0; i < dsSqlServer.Tables[0].Rows.Count; i++) { //要注意 参数要用 ' '括起来的。例如： 'value' strbSql.Append("insert into [MySheet$] values('"); for (int j = 0; j < 3; j++) { strbSql.Append(dsSqlServer.Tables[0].Rows[i].ItemArray[j].ToString() + "','"); } strbSql.Append(dsSqlServer.Tables[0].Rows[i].ItemArray[3].ToString() + "')"); //stringbuilder转为string类型，因为sql语句不能直接执行stringbuilder类 strSql = strbSql.ToString(); oleDbCom.CommandText = strSql; oleDbCom.ExecuteNonQuery(); //清除strbSql过往信息 strbSql.Remove(0, strbSql.Length); } //插入成功提示 MessageBox.Show("导入Excel成功！请查看！：", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { //失败提示 MessageBox.Show("导入Excel过程中发生错误！/n错误提示：" + ex.Message, "警告", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { oleDbCon.Close(); } } /// <summary> /// 导入数据到Excel /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void bntToExcel_Click(object sender, EventArgs e) { //判断是否选择好路径 if (ChoicePath()) { //创建一个Excel文件 CreateExcel(); //获取SqlServer表内容，存放到dsSqlServer里面 GetDs(); //导入数据到Excel InsertToExcel(); } } #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'";

参数说明

参数一	Provider = Microsoft.Jet.OLEDB.4.0	服务提供商
参数二	Data Source = '" + strFileName + "'	Excel路径
参数三	Extended Properties=Excel 8.0"	扩展属性，这里是提供Excel8.0的oleDb数据库操作功能

           在第二个连接字符串中，新增加了字段”HDR=NO“，表示若Excel里没有字段名，则用自动采用F1，F2。。作为字段名。（其实，我尝试过，是不行的。）还有一点要注意：在Extended Properties=后面可以再接其他扩展属性字段，但要用单引号' '来括起来，否则会报错：未找到可安装的ISAM;即语法错误。

        d.StingBuider与String的区别和用法

          Stringbuilder其实也是字符串。只不过，String是静态的字符串，而Stringbuilder是动态的 。为什么这么说？String 一当创建，它的大小已经是固定的了，如果在它后面再连接字符串，它会创建一个新的String实例，借于容纳更多的字符。所以，说String是静态的。相反，StringBuilder则类似于动态数组，每当连接新的字符串时，它会动态地申请空间，而不需要重新再创建一个。  其方法如下：    

       

StringBuilder属性和方法

属性和方法名	使用
StringBuilder.Length	当期长度
StringBuilder.Capacity	当前容量，能容纳的最大字符数目
StringBuilder.Append(string strChar)	将信息追加到当前 StringBuilder 的结尾。
StringBuilder.AppendFormat(string strForm, string strMultiType )	用带格式文本替换字符串中传递的格式说明符。
StringBuilder.Insert(int index, string strInsertChar)	将字符串或对象插入到当前 StringBuilder 对象的指定索引处。
StringBuilder.Remove(int start, int end)	从当前 StringBuilder 对象中移除指定数量的字符。
StringBuilder.Replace(sting strOld, string strNew)	替换指定索引处的指定字符。

          

 详细用法，请参考MSDN：http://msdn.microsoft.com/zh-cn/library/2839d5h5(VS.80).aspx

 

        最后，补充几点：

        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三者间或其他数据库的数据导入，这种方法也是适用的。

        

           该实例下载地址：

           http://download.csdn.net/source/2237306

 

          希望对大家有用！下一篇会讲到Word文档的数据导入以及在word文档里边动态设计表格并插入数据做成报表样式。