作者:乔山办公网日期:
返回目录:excel表格制作
只能先导出,然后复制到其他表
asp导出到excel
在开头加上这一句
Response.ContentType = "application/vnd.ms-excel"
如果导7a64e78988e69d83363出结果为空白的,那就去掉它
一下是导出Excel代码
--------------------------------------------------------------------------------
<%
set rs=server.createobject("adodb.recordset")
sql="select * from provinceinfo where 1=1"
rs.open sql,objconn,1,1
Set ExcelApp =CreateObject("Excel.Application")
ExcelApp.Application.Visible = True
Set ExcelBook = ExcelApp.Workbooks.Add
ExcelBook.WorkSheets(1).cells(1,1).value ="用户表"
ExcelBook.WorkSheets(1).cells(2,1).value = "用户编号"
ExcelBook.WorkSheets(1).cells(2,2).value = "登陆名"
ExcelBook.WorkSheets(1).cells(2,3).value = "真实姓名"
ExcelBook.WorkSheets(1).cells(2,4).value = "密码"
cnt =3
do while not rs.eof
ExcelBook.WorkSheets(1).cells(cnt,1).value = rs("provinceid")
ExcelBook.WorkSheets(1).cells(cnt,2).value = rs("province")
ExcelBook.WorkSheets(1).cells(cnt,3).value = rs("flag")
ExcelBook.WorkSheets(1).cells(cnt,4).value = rs("id")
rs.movenext
cnt = cint(cnt) + 1
loop
Excelbook.SaveAs "d:\yourfile.xls" '这个是数据导出完毕以后在D盘存成文件
ExcelApp.Application.Quit '导出以后退出Excel
Set ExcelApp = Nothing '注销Excel对象
%>
因你数据量大 另提示EXCEL相关信息
Excel 07-2003一个工作表最多可有65536行,行用数字1—65536表示;最多可有256列,列用英文字母A—Z,AA—AZ,BA—BZ,……,IA—IV表示;一个工作簿中最多含有255个工作表,默认情况下是三个工作表;
Excel 2007及以后版本,一个工作表最多可有1048576行,16384列;
所以建议用2007以上版本的excel
<%
dim excelstring
dim excelstr
dim excelstr2
dim head
excelstring = ""
head = "Sn" & "," & "Reason" & "," & "Empid" & "," & "Empname" & "," & "Ext" & "," & "Depname" excel="creat"
If excel<>""
Then
Set rs2 = Server.CreateObject ("ADODB.Recordset")
sql2 = "Select * from value_table "
rs2.open sql2,conn,1,3 do while not rs2.eof excelstr =" " & rs2(0) & """,""" & rs2(1) & """,""" & rs2(2) & """,""" & rs2(3) & """,""" & rs2(4) & """,""" & rs2(5) & """" excelstr2 = excelstr2 & Chr(13) & excelstr rs2.movenext loop excelstring = head &Chr(13)& excelstr2 'response.Write(excelstring) 'rs2.close
Set rs2=Nothing
set fs = CreateObject("scripting.FileSystemObject")
set exc = fs.OpenTextFile(server.MapPath("excel.csv"),2,True) exc.write(excelstring) if fs.FileExists(Server.MapPath("excel.csv"))
then Response.Write("<a href=excel.csv>报表已经生成,点击查看e68a84e8a2ad7a686964616f362</a>") 'Response.write(excelstring) else Response.Write("生成报表失败!") end if End if%>
给你段代码:<%
strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("数据库.mdb")
set conn = server.createobject("adodb.connection")
conn.open strconn
%>
<HTML>
<HEAD>
<meta content="text/html; charset=gb2312" http-equiv="Content-Type">
<TITLE>生成EXCEL文件</TITLE>
</HEAD>
<body>
<a href="?act=make">在线生成EXCEL</a>
<a href="record.xls" target="_blank">下载e799bee5baa6e4b893e5b19e362</a>
<hr size=1 align=left width=300px>
<%
if Request("act") = "" then
Response.Write "生成EXCEL文件"
elsedim rs,sql,filename,fs,myfile,x
Set fs = server.CreateObject("scripting.filesystemobject")
filename = Server.MapPath("record.xls")
if fs.FileExists(filename) then
fs.DeleteFile(filename)
end ifset myfile = fs.CreateTextFile(filename,true)Set rs = Server.CreateObject("ADODB.Recordset")
sql = "select * from record worder by id desc"
rs.Open sql,conn,1,1
if rs.EOF and rs.BOF thenelse
dim strLine,responsestr
strLine=""
For each x in rs.fields
strLine = strLine & x.name & chr(9)
Next
myfile.writeline strLine
Do while Not rs.EOF
strLine=""
for each x in rs.Fields
strLine = strLine & x.value & chr(9)
next
myfile.writeline strLine
rs.MoveNext
loop
end if
rs.Close
set rs = nothing
End If
%>