📄 用asp把excel数据导入sqlserver数据库中.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<!-- saved from url=(0055)http://www.51base.com/article/view_article.asp?id=72470 -->
<HTML lang=zh-cn><HEAD><TITLE>NB联盟</TITLE>
<META content="text/html; charset=gb2312" http-equiv=Content-Type><LINK
href="NB联盟30.files/style.css" rel=stylesheet>
<META content="MSHTML 5.00.2614.3500" name=GENERATOR></HEAD>
<BODY><!-- start page body -->
<TABLE align=center border=0 cellPadding=0 cellSpacing=0 class=td id=position
width=773>
<TBODY>
<TR>
<TD class=shadow colSpan=2 height=20 width=262> <A
href="http://www.51base.com/"><IMG border=0
src="NB联盟30.files/logo1.gif"></A> </TD>
<TD align=right width=505>
<P align=left></P></TD></TR></TBODY></TABLE>
<TABLE align=center border=0 cellPadding=0 cellSpacing=0 id=main width=770>
<TBODY>
<TR>
<TD class=tdlbr vAlign=top>
<TABLE align=center border=0 cellPadding=0 cellSpacing=0 id=welcome
style="TABLE-LAYOUT: fixed" width="98%">
<TBODY>
<TR>
<TD style="TABLE-LAYOUT: fixed; WORD-BREAK: break-all"
vAlign=top><FONT color=red><B>文章标题</B></FONT><FONT color=red><B>:
用ASP把EXCEL数据导入SQLSERVER数据库中,哪位知道,请接分 </B></FONT>
<HR noShade SIZE=1>
<BR>哪位知道在ASP把EXCEL数据导入SQLSERVER数据库中,请给点思路或代码.谢谢啦.
<BR>---------------------------------------------------------------
<BR> <BR>没有原代码，给你点提示，你可把EXCEL表当成一个数据库，从数据库里取数。再提交到SQL到保存。如果从EXCEL取数。论坛和技术文档里都有这方面的资料。
<BR>---------------------------------------------------------------
<BR> <BR>Set fso =
CreateObject("Scripting.FileSystemObject") <BR>Set
of = fso.GetFile(Server.mappath(IMPORTDATAFILE
& "/" & savedfilename))
<BR> <BR>Set ts =
oF.OpenAsTextStream(1, -2) <BR>do while
ts.AtEndOfStream <> true <BR>i
= i + 1 <BR>
strline = ts.ReadLine
<BR> arrLine
= split(strline,",") <BR>值的数组
<BR>
sqlinsert = " insert into(...)
values(...) " <BR> <BR>
oConn.execute(sqlinsert)
<BR>loop
<BR>---------------------------------------------------------------
<BR> <BR>excel.xsl: <BR> <BR>字段:
aa bb
cc <BR> <BR>值: a1
b1 c1
<BR> <BR>
a2 b2
c2 <BR> <BR>注意，excel中字段名和字段的值都是同样作为数据输入的，只是说
<BR> <BR>你把excel表的第一行作为是字段名就是。
<BR> <BR> test.asp
<BR> <BR><% <BR> <BR>dim
myconnection <BR> <BR>set
myconnection=server.createobject("adodb.connection")
<BR> <BR>dim myconnectionstr
<BR> <BR>myconnectionstr="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=e:\test\test.x <BR> <BR>ls;Extended
Properties=Excel 8.0;"
<BR> <BR>myconnection.open myconnectionstr
<BR> <BR>dim myrecordset
<BR> <BR>set
myrecordset=server.createobject("adodb.recordset")
<BR> <BR>dim mysql
<BR> <BR> <BR> <BR>mysql="select *
from [sheetname$]"
<BR> <BR>myrecordset.open mysql,myconnection,1,1
<BR> <BR>do while not
myrecordset.eof <BR> <BR>response.write
myrecordset("aa") & "<br>"
<BR> <BR>myrecordset.movenext
<BR> <BR>loop <BR> <BR>set
myrecordset=nothing <BR> <BR>set
myconnection=nothing <BR> <BR>%>
<BR>打印结果: <BR> <BR>a1
<BR> <BR>a2 <BR>然后存入数据库你应该会了吧
<BR>---------------------------------------------------------------
<BR> <BR>EXCEL可以当数据库用的
<BR> <BR> <BR> Function CreateExcelRecordset(
XLS文件名,Sheet名 ) <BR> <BR> Dim
conn.Driver,DBPath <BR> <BR> ‘
建立Connection对象 <BR> Set conn =
Server.CreateObject("ADODB.Connection") <BR> Driver
= "Driver={ Microsoft Excel Driver
(*.xls)};" <BR> DBPath = "DBQ=" &
Server.MapPath( "XLS文件名" ) <BR> <BR> ‘
调用Open 方法打开数据库 <BR> conn.Open Driver
& DBPath <BR> Set CreateExcelRecordset
= Server.CreateObject("ADODB.Recordset") <BR> ‘
打开Sheet，参数二为Connection对象,因为Excel ODBC驱动程序无法直接用
<BR>‘sheet名来打开sheet,所以请注意以下的select语句
<BR> CreateExcelRecordset.Open "Select *
From ["&sheet&”$]”, conn, 2, 2
<BR> <BR> End Function
<BR>---------------------------------------------------------------
<BR> <BR>excel可以把他看作一个数据库，用asp当然只能操作服务器端的数据。
<BR>对于客户端是没有办法操作了，当然如果你的客户信任你，你可以做成activex控件来操作客户端，这又是其他的问题了。<BR></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE><BR>
<TABLE align=center border=0 cellPadding=2 cellSpacing=0 id=footer width=770>
<TBODY>
<TR>
<TD align=middle class=tdt>
<P align=center></P></TD></TR></TBODY></TABLE><BR></BODY></HTML>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -