如何将网页上的数据导入到EXCEL表格中

easyfly 2003-11-20 08:06:51

如何用ASP将数据导入到EXCEL表格中？能否提供例子？？？

...全文

3159 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

easyfly 2003-11-22

打赏
举报

谢谢大家捧场，问题解决了，：）。

foxlb 2003-11-21

打赏
举报

http://www.pconline.com.cn/pcedu/empolder/wz/asp/10111/13297.html

tigerwen01 2003-11-21

打赏
举报

给你一个例子：
<%@ LANGUAGE="VBSCRIPT" %>
<%option explicit%>
<HTML>
<HEAD>
<meta content="text/html; charset=gb2312" http-equiv="Content-Type">
<TITLE>生成EXCEL文件</TITLE>
</HEAD>
<body>
<a href="dbtoexcel.asp?act=make">生成EXCEL文件</a>
<hr size=1 align=left width=300px>
<%
if Request("act") = "" then
Response.Write "生成EXCEL文件"
else

dim conn,strconn
strconn="driver={SQL Server};server=xxx;uid=sa;pwd=;database=DB_Test"
set conn=server.CreateObject("adodb.connection")
conn.Open strconn

dim rs,sql,filename,fs,myfile,x

Set fs = server.CreateObject("scripting.filesystemobject")
'--假设你想让生成的EXCEL文件做如下的存放
filename = Server.MapPath("online.xls")
'--如果原来的EXCEL文件存在的话删除它
if fs.FileExists(filename) then
fs.DeleteFile(filename)
end if
'--创建EXCEL文件
set myfile = fs.CreateTextFile(filename,true)

Set rs = Server.CreateObject("ADODB.Recordset")
'--从数据库中把你想放到EXCEL中的数据查出来
sql = "select * from Tb_Execl order by sort desc"
rs.Open sql,conn
if rs.EOF and rs.BOF then
Response.Write "库里暂时没有数据！"
else
dim strLine,responsestr
strLine=""
For each x in rs.fields
strLine= strLine & x.name & chr(9)
Next

'--将表的列名先写入EXCEL
myfile.writeline strLine

Do while Not rs.EOF
strLine=""

for each x in rs.Fields
strLine= strLine & x.value & chr(9)
next
'--将表的数据写入EXCEL
myfile.writeline strLine

rs.MoveNext
loop
end if
rs.Close
set rs = nothing
conn.close
set conn = nothing
set myfile = nothing
Set fs=Nothing
end if
%>
</BODY>
</HTML>

blueice2002 2003-11-21

打赏
举报

如何把数据从SQL Server导出到Access或Excel中去？
我有大量的重要数据要从SQL Server导出到Access或Excel文件中去，手工做太麻烦，还有其它的好办法吗？

有，我们在 SQL Server 连接中嵌套一个 Access 连接，再逐条写入，就可以了。见下列代码：
<%set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "driver={SQL server};server=mamaco;uid=linner;pwd=123;dat
abase=linner"
sql="select * from chunfeng"
set rs=conn.execute (sql)%>
<%set Conn1 = Server.CreateObject("ADODB.Connection")
param = "driver={Microsoft Access Driver (*.mdb)}"
conn.Open param & ";dbq=" & Server.MapPath("chunfeng.mdb")
conn1.execute("delete from chunfeng")%>
<%do while not rs.eof%>
<%dim gssyh,gsfwh,gslyb,gswxt,gswjh,gssyt,gsqj
gssyh=rs("gssyh")
gsfwh=rs("gsfwh")
gslyb=rs("gslyb")
gswxt=rs("gswxt")
gswjh=rs("gswjh")
gssyt=rs("gssyt")
gsqj=rs("gsqj")
%>
<%sql1="insert into chunfeng(gssyh,gsfwh,gslyb,gswxt,gswjh,gssyt,gsqj)
values

('"& gssyh &"','" & gsfwh & "'," & gslyb & ",'"&gswxt&"','"&gswjh&"',"& gssyt &"," & gsqj &")"
conn1.execute (sql1)%>
<%rs.movenext
loop
conn1.close
conn.close%>
<%response.redirect "/download/chunfeng.mdb"%>

xieyj 2003-11-21

打赏
举报

我给你一个函数，系统会自动判断如果安装OFFICE就按OFFICE导出，如果没有就导出成CSV文件。函数如下：
Function IntStr(sStr, iInt)
Dim i
For i = 1 To iInt-Len(sStr)
IntStr = IntStr &"0"
Next
IntStr = IntStr & sStr
End Function
Rem 参数是 rs.recordset
Sub Export(objRdc)
On Error Resume Next
Set objExcel = CreateObject("excel.application")
Dim i, j

If Err.Number <> 0 Then
Dim sPath, MyCsv, MyTxt, MyStr
MyStr = ""
sPath = "c:\rptCtime_"& IntStr(Month(Date()),2) & IntStr(Day(Date()),2) & IntStr(Hour(Time()),2) & IntStr(Minute(Time()),2) &".csv"
Set MyCsv = CreateObject("Scripting.FileSystemObject")
Set MyTxt = MyCsv.CreateTextFile(sPath,True) '覆盖原来存在的文件(一般不会存在)
With objRdc
For i = 0 To .Fields.Count
MyStr = MyStr & .Fields(i).name & ","
Next
MyTxt.WriteLine left(MyStr, len(MyStr)-1)
Do While Not .EOF
MyStr = ""
For i = 0 To .Fields.Count
MyStr = MyStr &""""& .Fields(i).value & ""","
Next
MyTxt.WriteLine left(MyStr, len(MyStr)-1)
.MoveNext
Loop
End With
Set MyCsv = Nothing
Set MyTxt = Nothing
Msgbox "Because your machine not install MS-Excel, So, System export export"&chr(13)&"CSV file only. the file's Address as below, please check."&chr(13)&chr(13)&sPath, vbinformation, "PM"
Else
' have install ms-excel
With objexcel
.Visible = True
.workbooks.Add
.sheets("sheet1").Select
For i = 0 To objRdc.Fields.Count
.range(chr(i+65)&"1").Select
.activecell.Value = objRdc.Fields(i).Name
Next
If instr(1,navigator.userAgent,"Windows NT 5.0") > 0 Then
.ActiveSheet.Range("a2").copyfromrecordset objRdc
Else
For i = 1 To objRdc.RecordCount
For j = 0 To objRdc.Fields.Count
.range(chr(j+65)&i+1).Select
.activecell.Value = objRdc.Fields(j).Value
Next
objRdc.MoveNext
Next
End If

.range("A1").Select
End With
End If

Set objExcel = Nothing
End Sub

julong88 2003-11-21

打赏
举报

else
dim i,j,k
On Error Resume Next
set objExcelApp = CreateObject("Excel.Application")
objExcelApp.DisplayAlerts = false
objExcelApp.Application.Visible = false
objExcelApp.Workbooks.Open("e:\web\jianzhu\ruku.xls") '打开Excel模板
set objExcelBook = objExcelApp.ActiveWorkBook
set objExcelSheets = objExcelBook.Worksheets
set objExcelSheet = objExcelBook.Sheets(3)
i=1
for each x in rs.fields
objExcelSheet.cells(5,i).value= x.name //输入名字
i=i+1
next

k=1
j=5
while not rs.eof //输入值
for each x in rs.fields
objExcelSheet.cells(j+1,k).value=x.value
k=k+1
next
k=1
rs.movenext
j=j+1
wend
objExcelBook.Save //保存、退出
objExcelApp.Quit
set objExceApp=Nothing
set rs=nothing
rs.Close
**************************************
else前面的内容为判断纪录集是否为空
objExcelBook.Sheets(3) 为选择在那一页输入

else之前的是判断记录即是否为空