ASP 查询sql结果导入excel表,急等

zhx730621 2009-08-02 04:59:25
我做了一个查询sql结果导入excel表,但是运行正常,可是没有excel表生成,这是怎么回事,请帮忙看看
<!--#include file="../inc/conn.asp" -->
<%
Dim jdyf
Dim mysql
jdyf=request("jdyf")
mysql = "select * from jdjzjh where jdyf='"&jdyf&"'"
server.scripttimeout=100000 '处理时间较长,设置值应大一点
On Error Resume Next
set objExcelApp = CreateObject("Excel.Application")
objExcelApp.DisplayAlerts = false
objExcelApp.Application.Visible = false
objExcelApp.WorkBooks.add
set objExcelBook = objExcelApp.ActiveWorkBook
set objExcelSheets = objExcelBook.Worksheets
set objSpreadsheet = objExcelBook.Sheets(1)
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open mysql,conn,1,3
If objRS.EOF then
response.write("Error")
respose.end
End if

Dim objField, iCol, iRow
iCol = 1 '取得列号
iRow = 1 '取得行号
objSpreadsheet.Cells(iRow, iCol).value = ""&xibua&"部的报名情况" '单元格插入数据
objSpreadsheet.Columns(iCol).ShrinkToFit=true '设定是否自动适应表格单元大小(单元格宽不变)
'设置Excel表里的字体
objSpreadsheet.Cells(iRow, iCol).Font.Bold = True '单元格字体加粗
objSpreadsheet.Cells(iRow, iCol).Font.Italic = False '单元格字体倾斜
objSpreadsheet.Cells(iRow, iCol).Font.Size = 20 '设置单元格字号
objSpreadsheet.Cells(iRow, iCol).ParagraphFormat.Alignment=1 '设置单元格对齐格式:居中
objspreadsheet.Cells(iRow,iCol).font.name="宋体" '设置单元格字体
objspreadsheet.Cells(iRow,iCol).font.ColorIndex=2 '设置单元格文字的颜色,颜色可以查询,2为白色
objSpreadsheet.Range("A1:F1").merge '合并单元格(单元区域)
objSpreadsheet.Range("A1:F1").Interior.ColorIndex = 1 '设计单元络背景色
'objSpreadsheet.Range("A2:F2").WrapText=true '设置字符回卷(自动换行)
iRow=iRow+1
For Each objField in objRS.Fields
'objSpreadsheet.Columns(iCol).ShrinkToFit=true
objSpreadsheet.Cells(iRow, iCol).value = objField.Name
'设置Excel表里的字体
objSpreadsheet.Cells(iRow, iCol).Font.Bold = True
objSpreadsheet.Cells(iRow, iCol).Font.Italic = False
objSpreadsheet.Cells(iRow, iCol).Font.Size = 20
objSpreadsheet.Cells(iRow, iCol).Halignment = 2 '居中
iCol = iCol + 1
Next 'objField
'Display all of the data
Do While Not objRS.EOF
iRow = iRow + 1
iCol = 1
For Each objField in objRS.Fields
If IsNull(objField.value) then
objSpreadsheet.Cells(iRow, iCol).value = ""
Else
objSpreadsheet.Columns(iCol).ShrinkToFit=true
objSpreadsheet.Cells(iRow, iCol).value = objField.value
objSpreadsheet.Cells(iRow, iCol).Halignment = 2
objSpreadsheet.Cells(iRow, iCol).Font.Bold = False
objSpreadsheet.Cells(iRow, iCol).Font.Italic = False
objSpreadsheet.Cells(iRow, iCol).Font.Size = 10
'objSpreadsheet.Cells(iRow, iCol).Halignment = 2
objSpreadsheet.Cells(iRow, iCol).ParagraphFormat.Alignment=1
End If
iCol = iCol + 1
Next 'objField
objRS.MoveNext
Loop

Dim SaveName
SaveName="周期计划"
Dim objExcel
Dim ExcelPath
ExcelPath = "../Excel\" & SaveName & ".xls"
objExcelBook.SaveAs Server.MapPath(ExcelPath)
Response.write("<script language='javascript'>;window.close();window.parent.location.replace('zhouqijihuadaochu.asp');alert('数据保存成功!');</script>")
objExcelApp.Quit
set objExcelApp = Nothing
%>
...全文
109 点赞 收藏 5
写回复
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
pingge520 2009-11-29
遇到了同样的问题。
回复
haiyun365 2009-08-03
On Error Resume Next
去掉这句看提示
回复
zhx730621 2009-08-03
[Quote=引用 1 楼 cpp2017 的回复:]
数据保存成功!  这句提示有么?

[/Quote]
数据保存成功这句话有,就是文件夹里没有文件
回复
poiuy343 2009-08-02
给一段ASP操作excel的,你自己对照下吧!


strAddr = Server.MapPath(".")
set objExcelApp = server.CreateObject("Excel.Application")
objExcelApp.DisplayAlerts = false
objExcelApp.Application.Visible = false
objExcelApp.WorkBooks.add
set objExcelBook = objExcelApp.ActiveWorkBook
set objExcelSheets = objExcelBook.Worksheets
set sheetActive=objExcelApp.ActiveWorkbook.ActiveSheet '实例化 sheetActive.range(strRange).value=array("ss","mm","ss")
set objExcelSheet = objExcelBook.Sheets(1)
strRange="d5:f5" '设定要填写内容的单元区域
sheetActive.range(strRange).font.size=10 '设定字体大小
sheetActive.range(strRange).WrapText=false '设定文字回卷
sheetActive.range(strRange).ShrinkToFit=true '设定是否自动适应表格单元大小


sheetActive.range(strRange).value=array("ss","mm","ss") '把数据集中的数据填写到相应的单元中




objExcelBook.SaveAs strAddr & "\xls\mm8.xls"
objExcelApp.Quit
set objExcelApp = Nothing
response.Write "<center>导出成功<br>"
response.Write "点击<a href='xls\mm8.xls' target='_blank' onclick='javascript:window.close()'>打开</a>导出的Excel文件</center>"

回复
cpp2017 2009-08-02
数据保存成功! 这句提示有么?
回复
发动态
发帖子
ASP
创建于2007-09-28

2.8w+

社区成员

ASP即Active Server Pages,是Microsoft公司开发的服务器端脚本环境。
申请成为版主
社区公告
暂无公告