ASP导出EXCEL
XSL.ASP
<!--#includefile="conn.asp"-->
<%
SetxlApplication=Server.CreateObject("Excel.Application")'调用excel对象
xlApplication.Visible=False'无需打开excel
xlApplication.SheetsInNewWorkbook=1'指定excel中表的数量
xlApplication.Workbooks.Add'添加工作簿
SetxlWorksheet=xlApplication.Worksheets(1)'生成第1个工作表的子对象
xlWorksheet.name="统计"'指定工作表名称
'指定列的宽度以及对齐方式1左对齐2右对齐3居中
xlApplication.ActiveSheet.Columns(1).ColumnWidth=5
xlApplication.ActiveSheet.Columns(1).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(2).ColumnWidth=10
xlApplication.ActiveSheet.Columns(2).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(3).ColumnWidth=20
xlApplication.ActiveSheet.Columns(3).HorizontalAlignment=3
'xlApplication.ActiveSheet.Rows(i).RowHeight=30'行的高度
'指定列的高度以及特定列
xlWorksheet.Range(xlWorksheet.Cells(1,1),xlWorksheet.Cells(1,3)).MergeCells=True'合并列
xlWorksheet.Range("A1").value="2005年统计"
xlWorksheet.Range("A1").font.Size=14'字体大小
xlWorksheet.Range("A1").font.bold=true'粗体
xlWorksheet.Range("A1").HorizontalAlignment=3'水平对齐
xlWorksheet.Range("A1").VerticalAlignment=3'垂直对齐
xlWorksheet.Cells(2,1).Value="编号"
xlWorksheet.Cells(2,2).Value="姓名"
xlWorksheet.Cells(2,3).Value="单位"
'xlWorksheet.Range("A1:C1").Borders.LineStyle=1'设置行style
'--------------------------------------------------自己可做循环i=i+1（数据库数据）
i=1
strSql="select*fromexcel"
Setrs=conn.execute(strSql)
ifnotrs.eofthen
dowhilenotrs.eof
xlWorksheet.Cells(2+i,1).Value=rs(0)
xlWorksheet.Cells(2+i,2).Value=rs(1)
xlWorksheet.Cells(2+i,3).Value=rs(2)
i=i+1
rs.movenext
loop
endif
'--------------------------------------------------
Setfs=CreateObject("Scripting.FileSystemObject")
tfile=Server.MapPath("test.xls")
iffs.FileExists(tfile)then
Setf=fs.GetFile(tfile)
f.deletetrue
Setf=nothing
endif
Setfs=nothing
xlWorksheet.SaveAstfile'保存文件
xlApplication.Quit'释放对象
SetxlWorksheet=Nothing
SetxlApplication=Nothing
%>
<palign="center"><ahref="downfile.asp?fileSpec=<%=tfile%>">下载</a></p>
downfile.asp
<%
SetxlApplication=Server.CreateObject("Excel.Application")'调用excel对象
xlApplication.Visible=False'无需打开excel
xlApplication.SheetsInNewWorkbook=1'指定excel中表的数量
xlApplication.Workbooks.Add'添加工作簿
SetxlWorksheet=xlApplication.Worksheets(1)'生成第1个工作表的子对象
xlWorksheet.name="统计"'指定工作表名称
'指定列的宽度以及对齐方式1左对齐2右对齐3居中
xlApplication.ActiveSheet.Columns(1).ColumnWidth=5
xlApplication.ActiveSheet.Columns(1).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(2).ColumnWidth=10
xlApplication.ActiveSheet.Columns(2).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(3).ColumnWidth=20
xlApplication.ActiveSheet.Columns(3).HorizontalAlignment=3
'xlApplication.ActiveSheet.Rows(i).RowHeight=30'行的高度
'指定列的高度以及特定列
xlWorksheet.Range(xlWorksheet.Cells(1,1),xlWorksheet.Cells(1,3)).MergeCells=True'合并列
xlWorksheet.Range("A1").value="2005年统计"
xlWorksheet.Range("A1").font.Size=14'字体大小
xlWorksheet.Range("A1").font.bold=true'粗体
xlWorksheet.Range("A1").HorizontalAlignment=3'水平对齐
xlWorksheet.Range("A1").VerticalAlignment=3'垂直对齐
xlWorksheet.Cells(2,1).Value="编号"
xlWorksheet.Cells(2,2).Value="姓名"
xlWorksheet.Cells(2,3).Value="单位"
'xlWorksheet.Range("A1:C1").Borders.LineStyle=1'设置行style
'--------------------------------------------------自己可做循环i=i+1（数据库数据）
i=1
strSql="select*fromexcel"
Setrs=conn.execute(strSql)
ifnotrs.eofthen
dowhilenotrs.eof
xlWorksheet.Cells(2+i,1).Value=rs(0)
xlWorksheet.Cells(2+i,2).Value=rs(1)
xlWorksheet.Cells(2+i,3).Value=rs(2)
i=i+1
rs.movenext
loop
endif
'--------------------------------------------------
Setfs=CreateObject("Scripting.FileSystemObject")
tfile=Server.MapPath("test.xls")
iffs.FileExists(tfile)then
Setf=fs.GetFile(tfile)
f.deletetrue
Setf=nothing
endif
Setfs=nothing
xlWorksheet.SaveAstfile'保存文件
xlApplication.Quit'释放对象
SetxlWorksheet=Nothing
SetxlApplication=Nothing
%>
<palign="center"><ahref="downfile.asp?fileSpec=<%=tfile%>">下载</a></p>
downfile.asp
<%
FunctiondownLoadFile(FileSpec)
onerrorresumenext
ConstForReading=1
ConstTristateTrue=-1
ConstFILE_TRANSFER_SIZE=1024'16384
DimobjFileSystem,objFile,objStream
Dimchar
Dimsent
SetobjFileSystem=CreateObject("Scripting.FileSystemObject")
IfobjFileSystem.FileExists(fileSpec)=falseThen
response.write("<Script>alert(""请求文件不存在！"");history.back();</script>")
ExitFunction
EndIf
FileName=objFileSystem.GetFileName(FileSpec)
send=0
TransferFile=True
SetobjFileSystem=Server.CreateObject("Scripting.FileSystemObject")
SetobjFile=objFileSystem.GetFile(FileSpec)
SetobjStream=objFile.OpenAsTextStream(ForReading,TristateTrue)
Response.AddHeader"content-type","application/octet-stream"
Response.AddHeader"Content-Disposition","attachment;filename="&filename
Response.AddHeader"content-length",objFile.Size
DoWhileNotobjStream.AtEndOfStream
char=objStream.Read(1)
Response.BinaryWrite(char)
sent=sent+1
If(sentMODFILE_TRANSFER_SIZE)=0Then
Response.Flush
IfNotResponse.IsClientConnectedThen
TransferFile=False
ExitDo
EndIf
EndIf
Loop
Response.Flush
IfNotResponse.IsClientConnectedThenTransferFile=False
objStream.Close
SetobjStream=Nothing
SetobjFileSystem=Nothing
EndFunction
fileSpec=Lcase(Cstr(Trim(Request("fileSpec"))))
downLoadFile(fileSpec)
%>
优质内容筛选与推荐>>
1、取得grid单元格里刚输入的文本，未保存的文本
2、LEK Cluster in Docker Container
3、Problem L. Visual Cube（杭电多校2018年第三场+模拟）
4、unsigned char 与char的区别
5、Last non-zero Digit in N!(阶乘最后非0位)
FunctiondownLoadFile(FileSpec)
onerrorresumenext
ConstForReading=1
ConstTristateTrue=-1
ConstFILE_TRANSFER_SIZE=1024'16384
DimobjFileSystem,objFile,objStream
Dimchar
Dimsent
SetobjFileSystem=CreateObject("Scripting.FileSystemObject")
IfobjFileSystem.FileExists(fileSpec)=falseThen
response.write("<Script>alert(""请求文件不存在！"");history.back();</script>")
ExitFunction
EndIf
FileName=objFileSystem.GetFileName(FileSpec)
send=0
TransferFile=True
SetobjFileSystem=Server.CreateObject("Scripting.FileSystemObject")
SetobjFile=objFileSystem.GetFile(FileSpec)
SetobjStream=objFile.OpenAsTextStream(ForReading,TristateTrue)
Response.AddHeader"content-type","application/octet-stream"
Response.AddHeader"Content-Disposition","attachment;filename="&filename
Response.AddHeader"content-length",objFile.Size
DoWhileNotobjStream.AtEndOfStream
char=objStream.Read(1)
Response.BinaryWrite(char)
sent=sent+1
If(sentMODFILE_TRANSFER_SIZE)=0Then
Response.Flush
IfNotResponse.IsClientConnectedThen
TransferFile=False
ExitDo
EndIf
EndIf
Loop
Response.Flush
IfNotResponse.IsClientConnectedThenTransferFile=False
objStream.Close
SetobjStream=Nothing
SetobjFileSystem=Nothing
EndFunction
fileSpec=Lcase(Cstr(Trim(Request("fileSpec"))))
downLoadFile(fileSpec)
%>
优质内容筛选与推荐>>
1、取得grid单元格里刚输入的文本，未保存的文本
2、LEK Cluster in Docker Container
3、Problem L. Visual Cube（杭电多校2018年第三场+模拟）
4、unsigned char 与char的区别
5、Last non-zero Digit in N!(阶乘最后非0位)