基于NET的Excel导入MSSQL2008

开发机器：（需安装AccessDatabaseEngine2007x32，不兼容64bit的oledb,出现未在本地计算机上注册“microsoft.ACE.oledb.12.0”提供程序）

操作系统：Window8企业版64bit

数据库：   MSSQL2008R2_64Bit

MS-Office: Office2010_64Bit, 

服务器：

操作系统：WindowServer2003R2(32位）

数据库：   MSSQL2008(32位）

MS-Office:  Office2007(32位）

注：Access2007或2010都是提供Microsoft.ACE.OLEDB.12.0 。如果Excel2010请将“Excel 14.0”添加到 OLEDB 连接字符串的扩展属性中。 

AccessDatabaseEngine2010下载地址：http://www.microsoft.com/zh-cn/download/details.aspx?id=13255

AccessDatabaseEngine2007下载地址:http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe

数据库表结构：

Eecel测试数据：

1.基于MSSQL2008数据库管理器--新建查询

--开启导入功能
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--允许在进程中使用ACE.OLEDB.12
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
--允许动态参数
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

insert into Student select * from 
OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=YES;DATABASE=d:\test.xlsx',sheet1$)

exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 0

(备注：不同版本Excel的OleDb连接串格式）
excel 2000 ~ 2003：
Provider=Microsoft.Jet.OleDb.4.0;Data Source='excel文件路径';Extended Properties='Excel 8.0;HDR=YES'
excel 2007 ：
Provider=Microsoft.Ace.OleDb.12.0;Data Source='excel文件路径';Extended Properties='Excel 12.0;HDR=YES'

Excel2000-2003:
OleDbConnection ExcelConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + FilePath + "; Extended Properties='Excel 12.0;HDR=YES;IMEX=1'");
Excel2007:
OleDbConnection ExcelConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + FilePath + "; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'");

注:FilePath是excel文件的路径 导入Excel2007文件时机器上必须安装有Excel2007否则会报错.


结果 ：




不够智能化，列头标题也当作一行数据录入数据表，还有最后列数据混乱

二：基于Net后台编码的导入：

     //前台简单一个上传控件和按钮  
    <form id="form1" runat="server">
    <div>    
        <asp:FileUpload ID="FileUpload1" runat="server" />
        <asp:Button ID="Button1" runat="server" οnclick="Button1_Click" Text="导入" />    
    </div>
    </form> 

 
        //后台按钮事件      
        protected void Button1_Click(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile == false)
            {
                Response.Write("<script>alert('请您选择Excel文件')</script> ");
                return;//当无文件时,返回
            }
            int fileSize = 4;
            int fileLenth = FileUpload1.PostedFile.ContentLength / (1024 * 1024);
            //大小是否在限制内
            if (fileLenth > fileSize)
            {
                Response.Write("<script>alert('文件不能大于" + fileSize + "M')</script> ");
                return ;
            }
            string fileType  = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
            if (fileType == ".xls" || fileType == ".xlsx")
            {
                string fileDirectory = "Excel";
                string serverPath = Server.MapPath(fileDirectory);
                //是否存在目录，不存在先创建
                if (!System.IO.Directory.Exists(serverPath))
                {
                    System.IO.Directory.CreateDirectory(serverPath);
                }
                string newPath = serverPath + "\\" + FileUpload1.FileName;
                FileUpload1.SaveAs(newPath);
                
                DataTable dt = ExcelDataSource(newPath, "sheet1").Tables[0];
                ExcelToDB.BLL.Student_BLL bll = new Student_BLL();
                if (bll.Add2(dt))
                {
                    Response.Write("<script>alert('导入成功!')</script>");
                }
                else
                {
                    Response.Write("<script>alert('导入失败!')</script>");
                }
                
            }
            else
            {
                Response.Write("<script>alert('只可以选择Excel文件')</script>");
                return;//当选择的不是Excel文件时,返回
            }
               
        }

        /// <summary>
        /// 获取Excel数据源，填充到DataSet
        /// </summary>
        /// <param name="filepath">excel文件绝对路径</param>
        /// <param name="sheetname">excel表单名</param>
        /// <returns></returns>
        public DataSet ExcelDataSource(string filepath, string sheetname)
        {
            string strConn;
            strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "$]", strConn);
            DataSet ds = new DataSet();
            oada.Fill(ds);
            return ds;
        }

结果：