订餐系统总结（二）sqlserver 与excel

  想通过这篇文章描述一下从sqlserver导入数据到excel和从excel导出数据到sqlserver的几种方法。

一、通过SQL语句直接导入到数据库

       数据的导入与导出是每个系统基本都具有的功能，在开始的时候，自己想的是把用户的信息从数据库中直接导入到sqlserver数据库，于是，沿着这个思路也就有了第一种方法：

首先要执行语句：

exec sp_configure 'show advanced options',1 --1打开 0关闭 reconfigure go exec sp_configure 'ad hoc distributed queries',1 --1打开 0关闭 reconfigure go如果不执行上述语句，会报错，自己可以试下！（在sql2005的外围配置管理器里也可以设置）

然后执行如下语句(excel为excel97-03)便可以查询出excel中的数据，我查的是excel工作薄中的teacherInfo工作表

SELECT * FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source="C:\test.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...[TeacherInfo$]
显示结果如下：

说明：1、提供程序版本：必须使用 Jet 4.0 提供程序；Jet 3.51 提供程序不支持 Jet ISAM 驱动程序

                       2、Excel 版本：对于 Excel 95 工作簿（Excel 版本 7.0），应指定 Excel 5.0；对于 Excel 97、Excel 2000 或 Excel 2002 (XP) 工作簿（Excel 版本 8.0、9.0 和 10.0），应指定 Excel 8.0 版本。

         

          二、其实我原来是这样想的，后来通过和艳梅师姐讨论，说这是不可以的。对信息的导入不能通过直接对数据库进行操作，因为如果系统交付用户使用后，用户是不会操作数据库的，所以应该在系统的后台通过代码来实现用户信息的导入。

系统采用了三层架构，代码实现如下：

1、界面层，导入按钮：

protected void btnBatch_Click(object sender, EventArgs e) { m_BatchAddTeacher BatchAddTeacher=new m_BatchAddTeacher(); if (file.PostedFile.FileName=="") { Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('请选择要导入的文件！');</script>"); return; } DataTable dt = BatchAddTeacher.GetDataTable(file.PostedFile.FileName); //B层的批量导入的方法，获取数据。 try { if (BatchAddTeacher.WriteToDB("TeacherInfo",dt)) { Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('导入成功！');</script>"); } } catch (Exception) { Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('导入失败，请确认是否修改年级名称为对应的序号！');</script>"); } }
2、B层的主要方法

/// <summary>返回通过这个excel导入的dataTable /// /// </summary> /// <param name="fileName"></param> /// <returns></returns> public DataTable GetDataTable(string fileName) { return IBatchAddTeacher.GetDataTable(fileName); } /// <summary>写入数据库 /// /// </summary> /// <param name="tableName"></param> /// <param name="dt"></param> /// <returns></returns> public bool WriteToDB(string tableName, DataTable dt) { return IBatchAddTeacher.WriteToDB(tableName, dt); }
   3、在D层采用了抽象工厂加反射，所以直接给出DAL层的接口实现：

OleDbConnection oledbConn = new OleDbConnection(); /// <summary>将excel中的数据加载到datatable /// /// </summary> /// <param name="fileName"></param> /// <returns></returns> public System.Data.DataTable GetDataTable(string fileName) { string strConn = "Provider=Microsoft.Jet.Oledb.4.0;" + "data source=" + fileName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"; oledbConn = new OleDbConnection(strConn); DataSet ds = new DataSet(); OleDbDataAdapter oledbda = new OleDbDataAdapter("select * from[Sheet1$]", GetCon()); oledbda.Fill(ds, fileName); //获取数据集中的第一个表 return ds.Tables[0]; } /// <summary> /// 连接Excel表的方法 /// </summary> /// <returns></returns> public OleDbConnection GetCon() { if (oledbConn.State == ConnectionState.Closed) { oledbConn.Open(); } return oledbConn; } /// <summary> /// 插入数据到数据库 /// </summary> /// <param name="tableName"></param> /// <param name="dt"></param> /// <returns></returns> public bool WriteToDB(string tableName, DataTable dt) { return new SqlHelper().BatchInsertData(tableName, dt); }下面是SQLHelper中的对应的方法：

说明：列标题：默认情况下，系统认为 Excel 数据源的第一行包含可用作字段名的列标题。如果不是这种情况，则必须将该设置关闭，否则，第一行数据将会“消失”，而被用作字段名称。这可通过向连接字符串的扩展属性添加可选的 HDR= 设置来完成。默认情况下（无需指定）是 HDR=Yes。如果没有列标题，则需要指定 HDR=No；提供程序将字段命名为 F1、F2 等等

          2、关于IMEX的值，可以参考：http://support.microsoft.com/kb/194124

http://www.cnblogs.com/xingyukun/archive/2007/04/30/733461.html

/// <summary> /// 批量插入数据 /// </summary> /// <param name="tableName">数据表名称</param> /// <param name="dt">要插入的数据</param> /// <returns></returns> public bool BatchInsertData(string tableName, DataTable dt) { //数据批量导入sqlserver,创建实例 System.Data.SqlClient.SqlBulkCopy sqlbulk = new System.Data.SqlClient.SqlBulkCopy(strCon); //目标数据库表名 sqlbulk.DestinationTableName = tableName; //数据集字段索引与数据库字段索引映射 //for (int i = 0; i < dt.Columns.Count; i++) //{ // sqlbulk.ColumnMappings.Add(i, i); //} //导入 sqlbulk.WriteToServer(dt); sqlbulk.Close(); return true; }

这样就实现excel的完美导入！

三、sqlserver导出到excel

可以直接通过DTS(data transformation service)导出和导入excel数据到sqlserver。因为都是图形化的界面，在此不再赘述了。

四、在系统里面有一个需求就是要把数据，导出到excel，现在给出从dataTable把数据导出到excel的实现：

/// <summary>把dataTable中的数据导出到excel /// /// </summary> /// <param name="table"></param> private void TableToExcel(DataTable table) { StringBuilder sbText = new StringBuilder(); try { sbText.AppendLine("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=gb2312\">"); sbText.AppendLine("<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">"); //导出列名 sbText.AppendLine("<tr style=\"font-weight:bold;white-space:nowrap;\">"); for (int i = 0; i < table.Columns.Count; i++) { sbText.AppendFormat("<td>{0}</td>", table.Columns[i].ColumnName); } sbText.AppendLine("</tr>"); //导出数据 foreach (DataRow row in table.Rows) { sbText.Append("<tr>"); for (int i = 0; i < table.Columns.Count; i++) { if (i==0) { //这个看情况吧，如果是以0开头的话，就加上这句话。 sbText.AppendFormat("<td>{0}</td>", "'"+row[i].ToString()); } else { sbText.AppendFormat("<td>{0}</td>", row[i]); } } sbText.AppendLine("</tr>"); } sbText.AppendLine("</table>"); Response.Clear(); Response.Buffer = true; Response.Charset = "GB2312"; Response.AppendHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"); //设置输出流编码格式 Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); //设置输出文件类型为excel文件。 Response.ContentType = "application/ms-excel"; EnableViewState = false; } catch (Exception ex) { //错误处理代码 Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('错误信息：！"+ex.Message.ToString()+"');</script>"); } Response.Write(sbText.ToString()); Response.End(); }

说明：对于有的字符串是0开头的话，excel会自动把前面的0去掉，于是在导出的时候加了一个方法，就是在前面加一个单引号。（我的表第一列就是这种情况）。

到此，关于sqlserver与excel的几种导入与导出关系都介绍完毕了，其实还有好东西要了解和掌握的，如果想深刻地理解，最好还是参考MSDN的帮助。

猛击此处：http://support.microsoft.com/kb/257819