ASP实现 将Excel表格数据批量导入到SQLServer数据库
'定义打开Excel表格的函数Function OpenExcel(path) dim excel,rs,strsql On Error Resume Next Set rs = Server.CreateObject("ADODB.RecordSet") Set excel = Server.CreateObject("ADODB.Connection") excel.Open "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & path If Err.number<> 0 Then Response.Write "请检查上传的Excel文件内部格式，文件无法打开，导入失败！" Response.End End If strsql = "SELECT * FROM [Sheet1$]" '在这里指定工作薄名称,默认是Sheet1$ Set rs = excel.Execute(strsql) Set OpenExcel = rsEnd Function
?
'读取文件中的内容Dim rsInfoSet rsInfo = Server.CreateObject("ADODB.RecordSet")Set rsInfo = OpenExcel("E:/a.xls") '这里的文件路径请用Server.Path来获取'检查读取结果If rsInfo.State<> 1 Then Response.Write "请检查Excel文件中的工作表命名是否为Sheet1，导入失败！" Response.EndEnd IfIf rsInfo.EOF And rsInfo.BOF Then Response.Write "没有找到Excel表中的数据，导入失败！" Response.EndEnd IfIf IsNull(rsInfo.Fields(0)) or Trim(rsInfo.Fields(0))="" Then Response.Write "没有找到Excel表中的数据，导入失败！" Response.EndEnd If'这里指定导入数据的列数,列数少了退出If rsInfo.Fields.Count< 7 Then Response.Write "Excel表中的数据列数不正确，导入失败！" Response.EndEnd If'创建数据库连接dim dbrs,conn,sqlSet conn = Server.CreateObject("ADODB.Connection")Set dbrs = Server.CreateObject("ADODB.Recordset")'注: G_DB_ConnectString是连接数据库的字符串,自己定义conn.ConnectionString = G_DB_ConnectStringconn.Open '打开数据库连接'创建临时表sql = "IF EXISTS (SELECT * FROM sysobjects WHERE xtype='U' and name='tmp_PartRes') "sql = sql & "BEGIN Drop table tmp_PartRes END "sql = sql & "Create table tmp_PartRes([ID] int identity(1,1),"sql = sql & "PartID varchar(100),Brand varchar(100),[Package] varchar(100),"sql = sql & "BatchNo varchar(100),[Price] varchar(100),[Stock] varchar(100) default('0'),"sql = sql & "Brief varchar(100),StockFlag int default(1),"sql = sql & "SuperFlag int default(1),SaleFlag int default(1))"conn.execute sql'取表结构 注意: 只取表的结构, 不要数据, 因为我这个是刚创建的临时表, 没有数据,'如果表中存在数据, 要注意加上条件句, 防止取到数据 如: where ID = -1sql = "SELECT * FROM tmp_PartRes"dbrs.CursorLocation = 3 '这一定要设置为3dbrs.Open sql,conn, 3, 4 '这里的参数必须是3和4'取到表结构后, 必须要把活动连接及数据库连接关闭,这个很重要, 否则导入速度特慢.Set dbrs.ActiveConnection = Nothingconn.close'提取Excel中的数据, 将excel中的数据放入到数据库表中.While Not rsInfo.EOF If Trim(rsInfo.Fields(0))<> "" Then dbrs.AddNew dbrs("PartID") = Ucase(Trim(rsInfo.Fields(0))) dbrs("Brand") = Trim(rsInfo.Fields(1)) dbrs("Package") = Trim(rsInfo.Fields(2)) dbrs("BatchNo") = Trim(rsInfo.Fields(3)) dbrs("Price") = Trim(rsInfo.Fields(4)) If Trim(rsInfo.Fields(5))<>"" Then dbrs("Stock") = Trim(rsInfo.Fields(5)) Else dbrs("Stock") = "0" End If dbrs("Brief") = Trim(rsInfo.Fields(6)) End If rsInfo.MoveNextWend'更新记录集到数据库临时表conn.Open '打开连接dbrs.ActiveConnection = conndbrs.UpdateBatch '批量更新函数'更新完成后, 关闭连接dbrs.CloseSet dbrs = NothingrsInfo.CloseSet rsInfo = Nothing?
注: 原文出处: http://freshflower.iteye.com/blog/1774733