导航
  • 主页
会员中心
收藏
消息
创作中心

查询生成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+

社区成员

35.7w+

社区内容

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