用asp从excel想sql server2000导入数据！！急~~

[iron0]

我想从excel向sql server2000导入数据，要求取excel中的一个特定标签，同时在sql数据库中生成表并添加响应的字段及数据，高手帮忙~~

[xxsoft]

这个是我考试系统里面题库导入的代码,有你需要的功能.你不动手试试,怎么知道能不能实现?

[shleo]

to 楼主
晕，我说的就是编程实现啊
难道我还让我手工打开一个excel文档，复制粘贴啊：）

[iron0]

但是你数据库里的表和字段是写好的~

并不是用程序创建的

[iron0]

在线求解，帮帮忙~~

[shleo]

？？？
你可以用代码创建表啊
为什么一定要先建好呢？

[iron0]

我的问题已经解决了，谢谢大家~
我的代码（需要修改）：
<!---#include file="conn.asp"-->
<%
Set econn = server.CreateObject("ADODB.Connection")
mystring = "driver={Microsoft Excel Driver (*.xls)};DBQ=D:\qtj\工作文件\一汽仓储\Program\仓储.xls;"
econn.Open mystring
Set rs = server.CreateObject("ADODB.Recordset")
Set rs = econn.OpenSchema(20)
Do while not rs.EOF
'所有Excel中的标签
'response.Write rs.Fields("TABLE_NAME")&"<br>"
if rs.Fields("TABLE_Name")="员工汇总2$" then
sql1="select * from ["&rs.Fields("TABLE_NAME")&"]"
set rs1=server.CreateObject("ADODB.Recordset")
rs1.Open sql1,econn,2,2
fieldType=""
For i=0 To rs1.Fields.Count-1
'字段名称
'response.Write rs1(i).Name&"<br>"
'字段长度
'response.Write rs1(i).Type&"<br>"
fname="["&rs1(i).Name&"]"
ftype=rs1(i).Type
Select Case ftype
Case 2, 18
fieldType = fieldType + fname + " [smallint] NULL ,"
Case 11
fieldType = fieldType + fname + " [bit] NULL ,"
Case 17, 16
fieldType = fieldType + fname + " [tinyint] NULL ,"
Case 3, 13, 21, 19
fieldType = fieldType + fname + " [int] NULL ,"
Case 4, 5, 139
fieldType = fieldType + fname + " [float] NULL ,"
Case 6
fieldType = fieldType + fname + " [money] NULL ,"
Case 20, 21
fieldType = fieldType + fname + " [bigint] NULL ,"
'Case 131, 14
'fieldType = fieldType + fname + " decimal(" + CStr(flenNumber) + "," + CStr(fscal) + "),"
'fieldtype(i) = 0
Case 128
fieldType = fieldType + fname + " [binary] NULL ,"
Case 129, 8
fieldType = fieldType + fname + " [char](10) NULL,"
Case 130
fieldType = fieldType + fname + " [varchar](50) NULL,"
Case 135, 133, 134, 7
fieldType = fieldType + fname + " [datetime] NULL ,"
Case 200, 202
fieldType = fieldType + fname + " [nvarchar](255) NULL,"
Case 201
fieldType = fieldType + fname + " [text] NULL ,"
Case 203
fieldType = fieldType + fname + " [ntext] NULL ,"
Case 204
fieldType = fieldType + fname + " [VarBiary] NULL ,"
Case 205
fieldType = fieldType + fname + " [image] NULL ,"
Case 72
fieldType = fieldType + fname + " [uniqueidentifier] NULL ,"
End Select
Next
response.Write "Create Table [dbo].[员工汇总_Temp]("&fieldType&")"
set rs1=nothing
'else
'response.Write"<center>您上传的Excel表格中没有[员工汇总]标签，请校验后重新上传！</center>"
end if
rs.MoveNext
Loop
rs.Close
set rs=nothing
Set rs2 = server.CreateObject("ADODB.Recordset")
Set rs2 = econn.OpenSchema(20)
Do while not rs2.EOF
if rs2.Fields("TABLE_Name")="员工汇总2$" then
sql3="select * from ["&rs2.Fields("TABLE_NAME")&"]"
set rs3=server.CreateObject("ADODB.Recordset")
rs3.Open sql3,econn,2,2
For j=0 To rs3.Fields.Count-1
'可以生成sql语句，添加到数据库
Next
set rs3=nothing
end if
rs2.MoveNext
Loop
rs2.Close
set rs2=nothing
econn.Close
Set econn = Nothing
%>
虽然大家写的都不是我想要的，但是通通有分！！！

[shleo]

和sql导到excel一样
打开excel文档，然后当作一个记录集来处理

[iron0]

我说是用编程实现~~~

[lovehwq21]

Set fso = CreateObject("Scripting.FileSystemObject")
Set of = fso.GetFile(Server.mappath(IMPORTDATAFILE & "/" & savedfilename))

Set ts = oF.OpenAsTextStream(1, -2)
do while ts.AtEndOfStream <> true
i = i + 1
strline = ts.ReadLine
arrLine = split(strline,",")
值的数组
sqlinsert = " insert into(...) values(...) "

oConn.execute(sqlinsert)
loop
---------------------------------------------------------------

excel.xsl:

字段: aa bb cc

值: a1 b1 c1

a2 b2 c2

注意，excel中字段名和字段的值都是同样作为数据输入的，只是说

你把excel表的第一行作为是字段名就是。

test.asp

<%

dim myconnection

set myconnection=server.createobject("adodb.connection")

dim myconnectionstr

myconnectionstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\test\test.x

ls;Extended Properties=Excel 8.0;"

myconnection.open myconnectionstr

dim myrecordset

set myrecordset=server.createobject("adodb.recordset")

dim mysql



mysql="select * from [sheetname$]"

myrecordset.open mysql,myconnection,1,1

do while not myrecordset.eof

response.write myrecordset("aa") & "<br>"

myrecordset.movenext

loop

set myrecordset=nothing

set myconnection=nothing

%>
打印结果:

a1

a2
然后存入数据库你应该会了吧

[hxx986]

先设置用ASP打开excel,再提取字段写入SQLServer的表中,不就行了吗???

[lovehwq21]

方法精解
---------------------------------------------------------------

Set econn = Server.CreateObject("ADODB.Connection")
Driver = "Driver={Microsoft Excel Driver (*.xls)};"
DBPath = "DBQ=" & Server.MapPath(xlspath)
econn.Open Driver & DBPath
Set ers = Server.CreateObject("ADODB.Recordset")
sql="Select * From [sheet1$]"
ers.Open sql,econn,0,1
可以用上面的方式打开excel并从里面读取数据
操作跟access一样

详细：
---------------------------------------------------------------

set conn=server.createobject("adodb.connection")
DBPath=server.mappath("aaa.mdb")
conn.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & DBPath
set rs=server.createobject("adodb.recordset")
sql="select * from "+request("tablename")
rs.open sql,conn,1,3

Set xlApp = server.CreateObject("Excel.Application")

strsource = server.mappath(request("filename"))

Set xlbook = xlApp.Workbooks.Open(strsource)
Set xlsheet = xlbook.Worksheets(1)
i=2
while xlsheet.cells(i,1)<>""
'response.write xlsheet.cells(i,3) '+xlsheet.cells(i,2)+xlsheet.cells(i,3) & xlsheet.cells(i,4) & xlsheet.cells(i,5) & xlsheet.cells(i,6)
'response.end
rs.addnew
' rs("c_name")=xlsheet.cells(i,1)
rs("c_Devic")=xlsheet.cells(i,1)
rs("c_Description")=xlsheet.cells(i,2)
rs("c_DBP")=trim(xlsheet.cells(i,3))
rs("c_SPQ")=xlsheet.cells(i,4)
rs("c_MOQ")=xlsheet.cells(i,5)
rs("c_Pin")=xlsheet.cells(i,6)
rs("c_Act")=xlsheet.cells(i,7)
rs("c_St")=xlsheet.cells(i,8)
rs("c_Pgm")=xlsheet.cells(i,9)
rs("c_LBE")=xlsheet.cells(i,10)

rs.update
i=i+1
wend

set xlsheet=nothing
xlbook.close
set xlbook=nothing
xlApp.quit
rs.close
set rs =nothing
conn.close
set conn=nothing
response.write "上传成功，数据存入成功!"

[iron0]

我需要读取excel的时候同时在sql server中创建table并添加字段~~然后才添加记录

[xxsoft]

<!--#include file="conn.asp"-->

<%
action=request("action")
error=request("error")

dim upload,file,formName,formPath,iCount,newname
set upload=new upload_5xSoft ''建立上传对象



if action="save" then

filename=upload.form("filename")
sheet=upload.form("sheet")
subjectview=upload.form("subjectview")

formPath= "excel/"
for each formName in upload.file ''列出所有上传了的文件
set file=upload.file(formName) ''生成一个文件对象
if file.FileSize>0 then ''如果 FileSize > 0 说明有文件数据

newname=year(date)&month(date)&day(date)&hour(time)&minute(time)&second(time)&file.FileName
file.SaveAs Server.mappath(formPath&newname) ''保存文件


end if
next
set file=nothing

set upload=nothing ''删除此对象






Set Conn1 = Server.CreateObject("ADODB.Connection")
Driver1 = "Driver={Microsoft Excel Driver (*.xls)};"
DBPath1 = "DBQ=" & Server.MapPath("excel\"&newname)
'调用Open 方法打开数据库
Conn1.Open Driver1 & DBPath1





sql="SELECT * FROM ["&sheet&"$] order by chapter"
set rs=server.createobject("adodb.recordset")
rs.open sql,conn1,3,2


if rs.eof then
response.redirect "adminexcel.asp?error=EXCEL 没有数据"
end if

do while not rs.eof

sql1="SELECT * FROM tbtk "
set rs1=server.createobject("adodb.recordset")
rs1.open sql1,conn,3,2

rs1.addnew
rs1("title")=rs("title")
rs1("a")=rs("a")
rs1("b")=rs("b")
rs1("c")=rs("c")
rs1("d")=rs("d")
rs1("e")=rs("e")
rs1("f")=rs("f")
rs1("ans")=rs("ans")
rs1("chapter")=rs("chapter")
rs1("category")=rs("category")
rs1("kcid")=subjectview
rs1("level")=rs("level")
rs1("kao")=rs("kao")
rs1("photo")=rs("photo")
rs1.update


rs.movenext
loop

response.redirect "adminexcel.asp?error=EXCEL 导入成功"

end if
%>

<html>

<head>
<meta http-equiv="Content-Language" content="zh-cn">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>系统管理 | EXCEL 导入</title>
<link rel="stylesheet" type="text/css" href="css.css">
<script language="JavaScript">





function FanAll(form){
for (var i=0;i<form.elements.length;i++){
var e = form.elements[i];
if (e.checked == false){ e.checked = true; }
else { e.checked = false;}
}}

</script>
</head>

<body>

<table border="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber2" height="25">
<tr>
<td width="100%">当前位置： <a href="adminsomething.asp">综合管理</a>
<span lang="en-us">></span> EXCEL 导入<span lang="en-us">                                          </span></td>
</tr>
</table>
<center>
<form name="viewdatabase" method="POST" action="?action=save" enctype="multipart/form-data"><font color="#FF0000"><b><%=error%></b></font>
<table border="1" cellspacing="1" style="border-collapse: collapse" bordercolor="#799AE1" width="374" id="AutoNumber1" height="368">

<tr class="trh">
<td align="center" height="22" width="361" colspan="2">
<font color="#FFFFFF">EXCEL 导入</font></td>
</tr>

<tr>
<td align="center" height="31" width="105" bgcolor="#FFFDE8">EXCEL表格：</td>
<td align="center" height="31" width="256" bgcolor="#FFFDE8">
<input type="file" name="filename" size="20" class="inputnormalleft" ></td>
</tr>
<tr>
<td align="center" height="31" width="105" bgcolor="#FFFDE8">课程名称：</td>

[xxsoft]

<td align="center" height="31" width="256" bgcolor="#FFFDE8"><select size="1" name="subjectview">
<%
'-----------------权限设定
power=session("b2bsoft_power")

dim arr,id1
if instr(power,",")>0 then
Arr=split(power)
for i = 0 to ubound(arr)
id1=clng(arr(i))

sql3="select * from tbkc where kcid="&id1
set rs3=server.createobject("adodb.recordset")
rs3.open sql3,conn,3,2

if not rs3.eof then
%>
<option value="<%=rs3("kcid")%>" <%if cint(rs3("kcid"))=cint(subjectview) then %>selected <%end if %>><%=rs3("kcm")%></option>
<%
end if
next
else

if power<=0 and power<>"-2" then

sql3="select * from tbkc"
set rs3=server.createobject("adodb.recordset")
rs3.open sql3,conn,3,2

do while not rs3.eof %>
<option value="<%=rs3("kcid")%>" <%if cint(rs3("kcid"))=cint(subjectview) then %>selected <%end if %>><%=rs3("kcm")%></option>
<% rs3.movenext
loop

else
sql3="select * from tbkc where kcid="&power
set rs3=server.createobject("adodb.recordset")
rs3.open sql3,conn,3,2

if not rs3.eof then
%>
<option value="<%=rs3("kcid")%>" <%if cint(rs3("kcid"))=cint(subjectview) then %>selected <%end if %>><%=rs3("kcm")%></option>
<% end if
end if

end if

rs3.movefirst

%></select></td>
</tr>
<tr>
<td align="center" height="31" width="105" bgcolor="#FFFDE8">工作表名：</td>
<td align="center" height="31" width="256" bgcolor="#FFFDE8">
<input type="text" name="sheet" size="20" class="inputnormal" value="sheet1"></td>
</tr>

<tr>
<td align="center" height="35" width="521" colspan="2" bgcolor="#FFFDE8">
<input class="s02" type="submit" value="导 入" name="B1" style="float: middle">     
<INPUT onclick="window.location='adminsomething.asp'" type=button value="返 回" name=close class="s02"></td>
</tr>

<tr>
<td align="center" height="324" width="521" colspan="2" bgcolor="#FFFDE8">
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="96%" id="AutoNumber3" height="369">
<tr>
<td width="100%" colspan="2" height="31">
<p align="center">导入说明</td>
</tr>
<tr>
<td width="100%" height="50" colspan="2"><font color="#FF0000">
注：导出的EXCEL格式和导入的模板格式不同，不能直接导入。如果要导入，可以把数据复制到模板，再导入模板。</font></td>
</tr>
<tr>
<td width="100%" height="23" colspan="2">EXCEL模板的格式要按以下说明排列</td>
</tr>
<tr>
<td width="23%" height="23" align="center">title</td>
<td width="77%" height="23">  试题内容</td>
</tr>
<tr>
<td width="23%" height="23" align="center">a</td>
<td width="77%" height="23">  A 选项</td>
</tr>
<tr>
<td width="23%" height="23" align="center">b</td>
<td width="77%" height="23">  B 选项</td>
</tr>
<tr>
<td width="23%" height="23" align="center">c</td>
<td width="77%" height="23">  C 选项</td>
</tr>
<tr>
<td width="23%" height="23" align="center">d</td>
<td width="77%" height="23">  D 选项</td>
</tr>
<tr>
<td width="23%" height="23" align="center">e</td>
<td width="77%" height="23">  E 选项</td>
</tr>
<tr>
<td width="23%" height="23" align="center">f</td>
<td width="77%" height="23">  F 选项</td>
</tr>
<tr>
<td width="23%" height="23" align="center">ans</td>
<td width="77%" height="23">  试题答案</td>
</tr>
<tr>
<td width="23%" height="23" align="center">chapter</td>
<td width="77%" height="23">  试题章节(数字类型)</td>
</tr>
<tr>
<td width="23%" height="62" align="center">category</td>
<td width="77%" height="62"><br>
  试题类型：<br>
1-名词解释  2-填空题  3-判断题  4-单项选择题  5-多项选择题  6-简答题 
7-论述题  8-阅读理解<br>
<font color="#FF0000">因为阅读理解的试题类型很特别，所以只能导入题干，不能导入题肢</font><br>
　</td>
</tr>
<tr>
<td width="23%" height="24" align="center">level</td>
<td width="77%" height="24"> 试题难度：1-容易  2-中等  3-较难 
4-最难</td>
</tr>
<tr>
<td width="23%" height="24" align="center">kao</td>
<td width="77%" height="24">  试题类别：1-考试题   0-练习题  2-通用题</td>
</tr>
<tr>
<td width="23%" height="36" align="center">photo</td>
<td width="77%" height="36">图片名称，图片先保存到 “\examimage”目录下</td>
</tr>
<tr>
<td width="100%" height="33" align="center" colspan="2"> <font color="#FF0000">EXCEL文件夹内有导入模板<b>in_tbtk.xls</b>，请使用前先浏览一下</font></td>
</tr>
</table>
</td>
</tr>

<caption></caption>
</table>
</form></center>

</body>

</html>

[xxsoft]

无错的代码儿...自己调试...导入EXCEL的同时在数据库里更新相应的纪录..

[iron0]

我想要的是数据库中没有表~导入excel的时候创建表并添加字段

[wsmall]

呵呵，何必要用asp呢？
直接在sql server中利用它的导入数据的功能/
可以满足你的在导入的时候创建表，而且可以定时执行。
这样多么简单

[iron0]

我也知道那样方便~可这是需要~

你总不能让没一个用户都去操作数据库吧