完整代码如下
<%
On Error Resume Next
导入Excel电子表格数据到SQL Sever数据库 By Dicky ::
Function Open_Conn(SqlDatabaseNameSqlPasswordSqlUsernameSqlLocalNameSqlConn)
创建数据库连接对象并打开数据库连接
Dim ConnStr
SQL Server数据库连接参数数据库名用户密码用户名连接名（本地用local外地用IP）
ConnStr = Provider=Sqloledb; User ID= & SqlUsername & ; Password= & SqlPassword & ; Initial Catalog = & SqlDatabaseName & ; Data Source= & SqlLocalName & ;
Set SqlConn = ServerCreateObject(ADODBConnection)
SqlConnOpen ConnStr
If Err Then
ErrClear
Set SqlConn = Nothing
ResponseWrite 数据连接错误!
ResponseEnd
End If
End Function
Function Close_Conn(SqlConn)
关闭数据库连接并清除数据库连接对象
If IsObject(SqlConn) Then
SqlConnClose
Set SqlConn = Nothing
End If
End Function
Call Open_Conn(Shat_EDGsa(local)SqlConn) 打开本地SQL Server数据库连接
Call Open_Conn(Shat_EDGsasshSqlConn) 打开远程SQL Server数据库连接
Function Get_EMP_CnName(NTACCNT)
根据用户NT帐号得到用户中文名
Dim SqlRs
Sql = Select EMP_CNAME From RF_EMPLOYEE Where EMP_NTACCNT=&NTACCNT&
Set Rs = ServerCreateObject(AdodbRecordSet)
RsOpen SqlSqlConn
If RsEof Then
Get_EMP_CnName =
Else
Get_EMP_CnName = Rs(EMP_CNAME)
End If
RsClose
Set Rs = Nothing
End Function %>
<html>
<head>
<title>导入Excel电子表格数据到SQL Sever数据库</title>
<body bgcolor=#ACDAF>
<center><b>导入Excel电子表格数据到SQL Sever数据库</b></center>
<FORM METHOD=POST name=form>
请选择数据源（本地库）:
<SELECT NAME=Table title=请选择需要导入数据的表>
<option></option>
<% Dim RsSqlDatabaseTable
Set RsSqlDatabaseTable = SqlConnOpenSchema()
Do While Not RsSqlDatabaseTableEof %>
<option<%If Trim(Request(Table))=RsSqlDatabaseTable() Then ResponseWrite selected%>><%=RsSqlDatabaseTable()%></option>
<% RsSqlDatabaseTableMoveNext:Loop
Set RsSqlDatabaseTable = Nothing %>
</SELECT><input type=submit name=submit value=开始导出>
<br>请选择目标表（远程库）:
<SELECT NAME=Table title=请选择需要导入数据的表>
<option></option>
<% Dim RsSqlDatabaseTable
Set RsSqlDatabaseTable = SqlConnOpenSchema()
Do While Not RsSqlDatabaseTableEof %>
<option<%If Trim(Request(Table))=RsSqlDatabaseTable() Then ResponseWrite selected%>><%=RsSqlDatabaseTable()%></option>
<% RsSqlDatabaseTableMoveNext:Loop
Set RsSqlDatabaseTable = Nothing %>
</SELECT>
</FORM>
<font color=blue>导出过程中请不要刷新页面！</font><br>
<%
If Trim(Request(Table)) <> Then
Dim SqlRs
Sql = Select * From Sheet$Sheet$是我们实现用SQL Server自身数据转换功能得到的表名
Set Rs = SqlConnExecute(Sql)
If RsEof And RsBof Then
Responsewrite 没有找到您需要的数据!!<br>
Else
Do While Not RsEof
SqlInsert = Insert Into &Trim(Request(Table))& (EDG_Project_NameEDG_Project_NoEDG_Project_VMEDG_Project_VM_CnNameEDG_Project_MEDG_Project_M_CnNameEDG_Project_DirectorEDG_Project_Director_CnName) Values (&Trim(Rs(工程名称))&&Trim(Rs(工程编号))&&Trim(Rs(项目副理))&&Trim(Rs(项目副理))&(&Get_EMP_CnName(Trim(Rs(项目副理)))&)&&Trim(Rs(项目经理))&&Trim(Rs(项目经理))&(&Get_EMP_CnName(Trim(Rs(项目经理)))&)&&Trim(Rs(项目总监))&&Trim(Rs(项目总监))&(&Get_EMP_CnName(Trim(Rs(项目总监)))&)&)
ResponseWrite SqlInsert
此处插入的值根据实际数据库结构进行调整
SqlConnExecute(SqlInsert)插入Excel表格裏所有东东
RsMoveNext
Loop
ResponseWrite <font color=red>恭喜成功导出数据！^_^</font><br>
End If
RsClose
Set Rs = Nothing
End If
Call Close_Conn(SqlConn) 关闭Excel数据库连接
Call Close_Conn(SqlConn) 关闭SQL Server数据库连接 %>
</body>
</html>
