订餐系统总结（二）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