excel导入sql数据库方法.doc

excel导入sql数据库方法 在网上看到很多朋友在咨询excel导入sql数据库方法，前段时间做项目时客户有这个功能需求，所以今天给大家分享一下实现的方法。先介绍一个思路：将excel文件上传到服务器，然后预览数据，可以对数据进行筛选，点击“导入”后将选择的数据导入到sql数据库中，最后把上传的文件从服务器上删除，释放空间。好了，接下来我们就按步骤来讲解excel导入sql数据库方法。 1、excel文件上传到服务器 我们采用函数的实现，代码如下： call UploadFile(fName,FilePath) Function UploadFile(fName,FilePath)?????? ???? UpLoadFileType= x l s | ?? //设置允许上传的文件类型???? FileTypeErr=True??? set upload=new upload_file?for each formName in upload.File??? set file=upload.File(formName)????? ?fName=file.FileName?FileExt=file.FileExt???if file.filesize100 then?????Response.Write script?? ???Response.Write alert(文件地址不能为空);???Response.Write location.href= Request.asp; ?????Response.Write /script???Response.end()? ????end if????? UpLoadFile_ID=split(UpLoadFileType,|)??for i=0 to ubound(UpLoadFile_ID)??????? ??if UpLoadFile_ID(i)=FileExt then??? FileTypeErr=False??? exit for??end if?????? ??next?? ??if FileTypeErr=True then???Response.Write script?? ???Response.Write alert(文件类型错误);???Response.Write location.href= Request.asp; ?????Response.Write /script???Response.end()??end if????mapurl=trim(fName)??file.SaveToFile Server.mappath(mapurl) //保存文件?set file=nothing??? next ??? set upload=nothing??? FilePath=Server.mappath(mapurl)? //将上传的文件路径赋值给FilePathEnd Function 2、预览数据，选择数据导入到sql数据库中 我们采用函数的实现，代码如下： call ExcelToSql(fName,FilePath) Function ExcelToSql(ExName,FilePath,Mainbody)??? ?Dim conn_xls?Dim StrConn_xls?Dim Rs?Dim Sql?Dim i????ExTName = Sheet1?On Error Resume Next??Set conn_xls =Server.CreateObject(ADODB.Connection)?StrConn_xls=Driver={Microsoft Excel Driver (*.xls)};DBQ= Server.MapPath(ExName)?conn_xls.Open StrConn_xls???Set rs_xls = Server.CreateObject(ADODB.Recordset) ?Sql=select * from [ExTName$]??rs_xls.Open Sql,conn_xls ,3,3 ???If Err Then????err.Clear??Set conn_xls = Nothing??set rs_xls=nothing??Delete_UpLoadFile(FilePath)????Response.Write script?? ??Response.Write alert(文件格式有误);??Response.Write location.href= Request.