<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=gb2312" /> <title>Excel导入MSSQL </title> </head> <body> <% Set Conn=Server.CreateObject("ADODB.Connection") ConnStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.mappath("test.mdb")&";User ID=admin;Password=;" Conn.Open(ConnStr)set rs=server.createobject("adodb.recordset") sql="select * from test" rs.Open sql,conn,3,3 for Createtablei=0 to rs.Fields.Count-1 Createtable=Createtable&rs.fields(Createtablei).name&" text ," next Createtablesql="Create table Sheet1("&left(Createtable,len(Createtable)-1)&")" ExcelFile="Excel.xls" set fso=Server.CreateObject ("Scripting.FileSystemObject") fpath=Server.MapPath(ExcelFile) if fso.FileExists(fpath) then whichfile=Server.MapPath(ExcelFile) Set fs = CreateObject("Scripting.FileSystemObject") Set thisfile = fs.GetFile(whichfile) thisfile.delete true dim excelfile,tbname end if Dim Driver,DBPath Set conn = Server.CreateObject("ADODB.Connection") Driver = "Driver={Microsoft Excel Driver (*.xls)};Readonly=0;" DBPath = "DBQ=" & Server.MapPath(excelfile) conn.Open Driver & DBPath conn.Execute(Createtablesql) for ii=0 to rs.recordcount-1 for i=0 to rs.Fields.Count-1 Inserttablename=Inserttablename&rs.fields(i).name&"," Inserttable=Inserttable&"'"&Rs(i)&"'," Next Insertintosql="Insert into Sheet1("&left(Inserttablename,len(Inserttablename)-1)&")values("&left(Inserttable,len(Inserttable)-1)&")"conn.Execute(Insertintosql)Insertintosql ="" Inserttable="" Inserttablename="" rs.MoveNext Next Response.Redirect (ExcelFile) %></body> </html>
转成xml吧
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>Excel导入MSSQL </title>
</head>
<body>
<% Set Conn=Server.CreateObject("ADODB.Connection")
ConnStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.mappath("test.mdb")&";User ID=admin;Password=;"
Conn.Open(ConnStr)set rs=server.createobject("adodb.recordset")
sql="select * from test"
rs.Open sql,conn,3,3
for Createtablei=0 to rs.Fields.Count-1
Createtable=Createtable&rs.fields(Createtablei).name&" text ,"
next
Createtablesql="Create table Sheet1("&left(Createtable,len(Createtable)-1)&")"
ExcelFile="Excel.xls"
set fso=Server.CreateObject ("Scripting.FileSystemObject")
fpath=Server.MapPath(ExcelFile)
if fso.FileExists(fpath) then
whichfile=Server.MapPath(ExcelFile)
Set fs = CreateObject("Scripting.FileSystemObject")
Set thisfile = fs.GetFile(whichfile)
thisfile.delete true
dim excelfile,tbname
end if
Dim Driver,DBPath
Set conn = Server.CreateObject("ADODB.Connection")
Driver = "Driver={Microsoft Excel Driver (*.xls)};Readonly=0;"
DBPath = "DBQ=" & Server.MapPath(excelfile)
conn.Open Driver & DBPath
conn.Execute(Createtablesql)
for ii=0 to rs.recordcount-1
for i=0 to rs.Fields.Count-1
Inserttablename=Inserttablename&rs.fields(i).name&","
Inserttable=Inserttable&"'"&Rs(i)&"',"
Next
Insertintosql="Insert into Sheet1("&left(Inserttablename,len(Inserttablename)-1)&")values("&left(Inserttable,len(Inserttable)-1)&")"conn.Execute(Insertintosql)Insertintosql =""
Inserttable=""
Inserttablename=""
rs.MoveNext
Next
Response.Redirect (ExcelFile)
%></body>
</html>