查询生成execl??

zjb_619 2010-08-12 05:44:26

Set xlApplication = Server.CreateObject("Excel.Application") '调用excel对象
xlApplication.Visible = False '无需打开excel
xlApplication.SheetsInNewWorkbook=4 '指定excel中表的数量
xlApplication.Workbooks.Add '添加工作簿
Set xlWorksheet1 = xlApplication.Worksheets(1) '生成第1个工作表的子对象
xlWorksheet1.name="第一季度" '指定工作表名称
'指定列的宽度以及对齐方式 1左对齐 2右对齐 3居中
xlApplication.ActiveSheet.Columns(1).ColumnWidth=8
xlApplication.ActiveSheet.Columns(1).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(2).ColumnWidth=45
xlApplication.ActiveSheet.Columns(2).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(3).ColumnWidth=12
xlApplication.ActiveSheet.Columns(3).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(4).ColumnWidth=12
xlApplication.ActiveSheet.Columns(4).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(5).ColumnWidth=12
xlApplication.ActiveSheet.Columns(5).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(6).ColumnWidth=8
xlApplication.ActiveSheet.Columns(6).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(7).ColumnWidth=12
xlApplication.ActiveSheet.Columns(7).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(8).ColumnWidth=12
xlApplication.ActiveSheet.Columns(8).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(9).ColumnWidth=15
xlApplication.ActiveSheet.Columns(9).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(10).ColumnWidth=8
xlApplication.ActiveSheet.Columns(10).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(11).ColumnWidth=12
xlApplication.ActiveSheet.Columns(11).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(12).ColumnWidth=8
xlApplication.ActiveSheet.Columns(12).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(13).ColumnWidth=8
xlApplication.ActiveSheet.Columns(13).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(14).ColumnWidth=8
xlApplication.ActiveSheet.Columns(14).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(15).ColumnWidth=8
xlApplication.ActiveSheet.Columns(15).HorizontalAlignment=3

'xlApplication.ActiveSheet.Rows(i).RowHeight = 30'行的高度
'指定列的高度以及特定列
'xlWorksheet11.Range(xlWorksheet11.Cells(1,1), xlWorksheet11.Cells(1,3)).MergeCells =True '合并列
'xlWorksheet11.Range("A1").value="2005年统计"
'xlWorksheet11.Range("A1").font.Size=14'字体大小
'xlWorksheet11.Range("A1").font.bold=true'粗体
'xlWorksheet11.Range("A1").HorizontalAlignment=3'水平对齐
'xlWorksheet11.Range("A1").VerticalAlignment=3'垂直对齐

xlWorksheet1.Cells(1,1).Value = "日期"
xlWorksheet1.Cells(1,2).Value = "供方名称"
xlWorksheet1.Cells(1,3).Value = "供货数量"
xlWorksheet1.Cells(1,4).Value = "不合格数量"
xlWorksheet1.Cells(1,5).Value = "不合格率"
xlWorksheet1.Cells(1,6).Value = "Q1"
xlWorksheet1.Cells(1,7).Value = "供货批次"
xlWorksheet1.Cells(1,8).Value = "不合格批次"
xlWorksheet1.Cells(1,9).Value = "不合格批次率"
xlWorksheet1.Cells(1,10).Value = "Q2"
xlWorksheet1.Cells(1,11).Value = "合同正点率"
xlWorksheet1.Cells(1,12).Value = "D"
xlWorksheet1.Cells(1,13).Value = "P/S"
xlWorksheet1.Cells(1,14).Value = "N"
xlWorksheet1.Cells(1,15).Value = "结果"
'xlWorksheet11.Range("A1:C1").Borders.LineStyle=1 '设置行style
i=1
strSql = "select * from ven_quarter_tj where left(stst_quarter,4)=year(getdate()) and right(stst_quarter,1)=1"
Set rs=Server.CreateObject("ADODB.RecordSet")
rs.Open strSql,Conn,1,1
if not rs.eof then
do while not rs.eof
xlWorksheet1.Cells(2+i,1).Value = left(rs("stst_quarter"),4)
xlWorksheet1.Cells(2+i,2).Value = rs("ven_name")
xlWorksheet1.Cells(2+i,3).Value = rs("par_in_num")
xlWorksheet1.Cells(2+i,4).Value = rs("par_bad_num")
xlWorksheet1.Cells(2+i,5).Value = rs("I")
xlWorksheet1.Cells(2+i,6).Value = rs("Q1")
xlWorksheet1.Cells(2+i,7).Value = rs("lot_in_num")
xlWorksheet1.Cells(2+i,8).Value = rs("lot_bad_num")
xlWorksheet1.Cells(2+i,9).Value = rs("M")
xlWorksheet1.Cells(2+i,10).Value = rs("Q2")
xlWorksheet1.Cells(2+i,11).Value = rs("con_rate")
xlWorksheet1.Cells(2+i,12).Value = rs("D")
xlWorksheet1.Cells(2+i,13).Value = rs("p_s")
xlWorksheet1.Cells(2+i,14).Value = rs("N")
xlWorksheet1.Cells(2+i,15).Value = rs("result")
i=i+1
rs.movenext
loop
rs.close
set rs = nothing
end if

Set fs = CreateObject("Scripting.FileSystemObject")
tfile=Server.MapPath("业绩统计.xls")
if fs.FileExists(tfile) then
Set f = fs.GetFile(tfile)
f.delete true
Set f = nothing
end if
Set fs = nothing
xlWorksheet1.SaveAs tfile '保存文件
'xlWorksheet2.SaveAs tfile '保存文件
'xlWorksheet3.SaveAs tfile '保存文件
'xlWorksheet4.SaveAs tfile '保存文件
xlApplication.Quit '释放对象
Set xlWorksheet1 = Nothing
'Set xlWorksheet2 = Nothing
'Set xlWorksheet3 = Nothing
'Set xlWorksheet4 = Nothing
Set xlApplication = Nothing
<a href="downfile.asp?fileSpec=<%=tfile%>" class="STYLE1">下载</a>

我用这段代码来实现生成execl,但是打开速度非常慢,是我写的代码有问题么??
...全文
37 点赞 收藏 7
写回复
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
zjb_619 2010-08-13
我怎么打开是全是乱码呢??
回复
孟子E章 2010-08-13
可以的。
你在Excel里面创建3个Sheet,然后另存为表格xml,你记事本打开xml,看到里面的格式,研究下,你用程序生成这个文本文件,扩展名命名为xls,打开的时候就是3个了
回复
zjb_619 2010-08-13
我想导出的一个excel里有多个字表,除了这样,还有没有别的方法???
回复
孟子E章 2010-08-13
慢是正常的,这个相对于你打开Excel,然后进行操作的过程相同。如果对格式没有太多的要求,你可以导出成xml或者html兼容的格式
回复
zjb_619 2010-08-13
还是没有人回答么?
回复
zjb_619 2010-08-12
没有人看么?
回复
zjb_619 2010-08-12
要是生成的一个execl要有多个子表,是不是向下面这样写,基本上打不开???

Set xlApplication = Server.CreateObject("Excel.Application") '调用excel对象
xlApplication.Visible = False '无需打开excel
xlApplication.SheetsInNewWorkbook=4 '指定excel中表的数量
xlApplication.Workbooks.Add '添加工作簿
Set xlWorksheet1 = xlApplication.Worksheets(1) '生成第1个工作表的子对象
xlWorksheet1.name="第一季度" '指定工作表名称
'指定列的宽度以及对齐方式 1左对齐 2右对齐 3居中
xlApplication.ActiveSheet.Columns(1).ColumnWidth=8
xlApplication.ActiveSheet.Columns(1).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(2).ColumnWidth=45
xlApplication.ActiveSheet.Columns(2).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(3).ColumnWidth=12
xlApplication.ActiveSheet.Columns(3).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(4).ColumnWidth=12
xlApplication.ActiveSheet.Columns(4).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(5).ColumnWidth=12
xlApplication.ActiveSheet.Columns(5).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(6).ColumnWidth=8
xlApplication.ActiveSheet.Columns(6).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(7).ColumnWidth=12
xlApplication.ActiveSheet.Columns(7).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(8).ColumnWidth=12
xlApplication.ActiveSheet.Columns(8).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(9).ColumnWidth=15
xlApplication.ActiveSheet.Columns(9).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(10).ColumnWidth=8
xlApplication.ActiveSheet.Columns(10).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(11).ColumnWidth=12
xlApplication.ActiveSheet.Columns(11).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(12).ColumnWidth=8
xlApplication.ActiveSheet.Columns(12).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(13).ColumnWidth=8
xlApplication.ActiveSheet.Columns(13).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(14).ColumnWidth=8
xlApplication.ActiveSheet.Columns(14).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(15).ColumnWidth=8
xlApplication.ActiveSheet.Columns(15).HorizontalAlignment=3

'xlApplication.ActiveSheet.Rows(i).RowHeight = 30'行的高度
'指定列的高度以及特定列
'xlWorksheet11.Range(xlWorksheet11.Cells(1,1), xlWorksheet11.Cells(1,3)).MergeCells =True '合并列
'xlWorksheet11.Range("A1").value="2005年统计"
'xlWorksheet11.Range("A1").font.Size=14'字体大小
'xlWorksheet11.Range("A1").font.bold=true'粗体
'xlWorksheet11.Range("A1").HorizontalAlignment=3'水平对齐
'xlWorksheet11.Range("A1").VerticalAlignment=3'垂直对齐

xlWorksheet1.Cells(1,1).Value = "日期"
xlWorksheet1.Cells(1,2).Value = "供方名称"
xlWorksheet1.Cells(1,3).Value = "供货数量"
xlWorksheet1.Cells(1,4).Value = "不合格数量"
xlWorksheet1.Cells(1,5).Value = "不合格率"
xlWorksheet1.Cells(1,6).Value = "Q1"
xlWorksheet1.Cells(1,7).Value = "供货批次"
xlWorksheet1.Cells(1,8).Value = "不合格批次"
xlWorksheet1.Cells(1,9).Value = "不合格批次率"
xlWorksheet1.Cells(1,10).Value = "Q2"
xlWorksheet1.Cells(1,11).Value = "合同正点率"
xlWorksheet1.Cells(1,12).Value = "D"
xlWorksheet1.Cells(1,13).Value = "P/S"
xlWorksheet1.Cells(1,14).Value = "N"
xlWorksheet1.Cells(1,15).Value = "结果"
'xlWorksheet11.Range("A1:C1").Borders.LineStyle=1 '设置行style
i=1
strSql = "select * from ven_quarter_tj where left(stst_quarter,4)=year(getdate()) and right(stst_quarter,1)=1"
Set rs=Server.CreateObject("ADODB.RecordSet")
rs.Open strSql,Conn,1,1
if not rs.eof then
do while not rs.eof
xlWorksheet1.Cells(2+i,1).Value = left(rs("stst_quarter"),4)
xlWorksheet1.Cells(2+i,2).Value = rs("ven_name")
xlWorksheet1.Cells(2+i,3).Value = rs("par_in_num")
xlWorksheet1.Cells(2+i,4).Value = rs("par_bad_num")
xlWorksheet1.Cells(2+i,5).Value = rs("I")
xlWorksheet1.Cells(2+i,6).Value = rs("Q1")
xlWorksheet1.Cells(2+i,7).Value = rs("lot_in_num")
xlWorksheet1.Cells(2+i,8).Value = rs("lot_bad_num")
xlWorksheet1.Cells(2+i,9).Value = rs("M")
xlWorksheet1.Cells(2+i,10).Value = rs("Q2")
xlWorksheet1.Cells(2+i,11).Value = rs("con_rate")
xlWorksheet1.Cells(2+i,12).Value = rs("D")
xlWorksheet1.Cells(2+i,13).Value = rs("p_s")
xlWorksheet1.Cells(2+i,14).Value = rs("N")
xlWorksheet1.Cells(2+i,15).Value = rs("result")
i=i+1
rs.movenext
loop
rs.close
set rs = nothing
end if

set xlWorksheet2 = xlApplication.Worksheets(2)'生成第2个工作表的子对象
xlWorksheet2.name="第二季度" '指定工作表名称
'指定列的宽度以及对齐方式 1左对齐 2右对齐 3居中
xlApplication.ActiveSheet.Columns(1).ColumnWidth=8
xlApplication.ActiveSheet.Columns(1).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(2).ColumnWidth=45
xlApplication.ActiveSheet.Columns(2).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(3).ColumnWidth=12
xlApplication.ActiveSheet.Columns(3).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(4).ColumnWidth=12
xlApplication.ActiveSheet.Columns(4).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(5).ColumnWidth=12
xlApplication.ActiveSheet.Columns(5).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(6).ColumnWidth=8
xlApplication.ActiveSheet.Columns(6).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(7).ColumnWidth=12
xlApplication.ActiveSheet.Columns(7).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(8).ColumnWidth=12
xlApplication.ActiveSheet.Columns(8).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(9).ColumnWidth=15
xlApplication.ActiveSheet.Columns(9).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(10).ColumnWidth=8
xlApplication.ActiveSheet.Columns(10).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(11).ColumnWidth=12
xlApplication.ActiveSheet.Columns(11).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(12).ColumnWidth=8
xlApplication.ActiveSheet.Columns(12).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(13).ColumnWidth=8
xlApplication.ActiveSheet.Columns(13).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(14).ColumnWidth=8
xlApplication.ActiveSheet.Columns(14).HorizontalAlignment=3
xlApplication.ActiveSheet.Columns(15).ColumnWidth=8
xlApplication.ActiveSheet.Columns(15).HorizontalAlignment=3

'xlApplication.ActiveSheet.Rows(i).RowHeight = 30'行的高度
'指定列的高度以及特定列
'xlWorksheet11.Range(xlWorksheet11.Cells(1,1), xlWorksheet11.Cells(1,3)).MergeCells =True '合并列
'xlWorksheet11.Range("A1").value="2005年统计"
'xlWorksheet11.Range("A1").font.Size=14'字体大小
'xlWorksheet11.Range("A1").font.bold=true'粗体
'xlWorksheet11.Range("A1").HorizontalAlignment=3'水平对齐
'xlWorksheet11.Range("A1").VerticalAlignment=3'垂直对齐

xlWorksheet2.Cells(1,1).Value = "日期"
xlWorksheet2.Cells(1,2).Value = "供方名称"
xlWorksheet2.Cells(1,3).Value = "供货数量"
xlWorksheet2.Cells(1,4).Value = "不合格数量"
xlWorksheet2.Cells(1,5).Value = "不合格率"
xlWorksheet2.Cells(1,6).Value = "Q1"
xlWorksheet2.Cells(1,7).Value = "供货批次"
xlWorksheet2.Cells(1,8).Value = "不合格批次"
xlWorksheet2.Cells(1,9).Value = "不合格批次率"
xlWorksheet2.Cells(1,10).Value = "Q2"
xlWorksheet2.Cells(1,11).Value = "合同正点率"
xlWorksheet2.Cells(1,12).Value = "D"
xlWorksheet2.Cells(1,13).Value = "P/S"
xlWorksheet2.Cells(1,14).Value = "N"
xlWorksheet2.Cells(1,15).Value = "结果"
'xlWorksheet11.Range("A1:C1").Borders.LineStyle=1 '设置行style
i=1
strSql = "select * from ven_quarter_tj where left(stst_quarter,4)=year(getdate()) and right(stst_quarter,1)=2"
Set rs=Server.CreateObject("ADODB.RecordSet")
rs.Open strSql,Conn,1,1
if not rs.eof then
do while not rs.eof
xlWorksheet2.Cells(1+i,1).Value = left(rs("stst_quarter"),4)
xlWorksheet2.Cells(2+i,2).Value = rs("ven_name")
xlWorksheet2.Cells(2+i,3).Value = rs("par_in_num")
xlWorksheet2.Cells(2+i,4).Value = rs("par_bad_num")
xlWorksheet2.Cells(2+i,5).Value = rs("I")
xlWorksheet2.Cells(2+i,6).Value = rs("Q1")
xlWorksheet2.Cells(2+i,7).Value = rs("lot_in_num")
xlWorksheet2.Cells(2+i,8).Value = rs("lot_bad_num")
xlWorksheet2.Cells(2+i,9).Value = rs("M")
xlWorksheet2.Cells(2+i,10).Value = rs("Q2")
xlWorksheet2.Cells(2+i,11).Value = rs("con_rate")
xlWorksheet2.Cells(2+i,12).Value = rs("D")
xlWorksheet2.Cells(2+i,13).Value = rs("p_s")
xlWorksheet2.Cells(2+i,14).Value = rs("N")
xlWorksheet2.Cells(2+i,15).Value = rs("result")
i=i+1
rs.movenext
loop
rs.close
set rs = nothing
end if


Set fs = CreateObject("Scripting.FileSystemObject")
tfile=Server.MapPath("业绩统计.xls")
if fs.FileExists(tfile) then
Set f = fs.GetFile(tfile)
f.delete true
Set f = nothing
end if
Set fs = nothing
xlWorksheet1.SaveAs tfile '保存文件
xlWorksheet2.SaveAs tfile '保存文件
'xlWorksheet3.SaveAs tfile '保存文件
'xlWorksheet4.SaveAs tfile '保存文件
xlApplication.Quit '释放对象
Set xlWorksheet1 = Nothing
Set xlWorksheet2 = Nothing
'Set xlWorksheet3 = Nothing
'Set xlWorksheet4 = Nothing
Set xlApplication = Nothing
回复
发动态
发帖子
ASP
创建于2007-09-28

2.8w+

社区成员

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