IDC资讯中心
test_print_report.asp
<html>
<head>
<meta content="text/html; charset=big5" http-equiv="content-type">
<title>client use rds produce excel report</title>
</head>
<body bgcolor="skyblue" topmargin=0 leftmargin="20" oncontextmenu="return false" rightmargin="0" bottommargin="0">
<form action="test_print_report.asp" method="post" name="myform">
<div align="center"><center>
<table border="5" bgcolor="#ffe4b5" style="height: 1px; top: 0px" bordercolor="#0000ff">
<tr>
<td align="middle" bgcolor="#ffffff" bordercolor="#000080">
<font color="#000080" size="3">
client use rds produce excel report
</font>
</td>
</tr>
</table>
</div>
<div align="left">
<input type="button" value="query data" name="query" language="vbscript" onclick="fun_query()" style="height: 32px; width: 90px">
<input type="button" value="clear data" name="clear" language="vbscript" onclick="fun_clear()" style="height: 32px; width: 90px">
<input type="button" value="excel report" name="report" language="vbscript" onclick="fun_excel()" style="height: 32px; width: 90px">
</div>
<div id="adddata"></div>
</form></center>
</body>
</html>
<script language="vbscript">
dim rds,rs,df
dim strsql,strrs,strcn,rowcnt
dim xlapp, xlbook, xlsheet1,xlmodule,xlpagesetup
dim headrowcnt,titlerowcnt,contentrowcnt,footrowcnt
dim pagerowcnt,pageno,totalpagecnt,contentrownowcnt
dim columnallwidth,columnawidth,columnbwidth,columncwidth,columndwidth
sub fun_query()
set rds = createobject("rds.dataspace")
set df = rds.createobject("rdsserver.datafactory","http://iscs00074")
strcn="driver={sql server};server=iscs00074;uid=sa;app=microsoft development environment;database=pubs;user id=sa;password=;"
strsql = "select * from jobs"
set rs = df.query(strcn, strsql)
if not rs.eof then
strrs="<table border=1><tr><td>job_id</td><td>job_desc</td><td>max_lvl</td><td>min_lvl</td></tr><tr><td>"+ rs.getstring(,,"</td><td>","</td></tr><tr><td>"," ") +"</td></tr></table>"
adddata.innerhtml=strrs
strrs=""
else
msgbox "no data in the table!"
end if
end sub
sub fun_clear()
strrs=""
adddata.innerhtml=strrs
end sub
sub fun_excel()
set rds = createobject("rds.dataspace")
set df = rds.createobject("rdsserver.datafactory","http://iscs00074")
strcn="driver={sql server};server=iscs00074;uid=sa;app=microsoft development environment;database=pubs;user id=sa;password=;"
strsql = "select count(*) as recordcnt from jobs"
set rs = df.query(strcn, strsql)
totalpagecnt=rs("recordcnt")
rs.close
set rs=nothing
strsql = "select * from jobs"
set rs = df.query(strcn, strsql)
set xlapp = createobject("excel.application")
set xlbook = xlapp.workbooks.add
set xlsheet1 = xlbook.activesheet
set xlmodule = xlbook.vbproject.vbcomponents.add(1)
xlsheet1.application.visible = true
xlsheet1.application.usercontrol = true
i=0
rowcnt=1
pageno=1
headrowcnt=4 the header number to print in one page!
titlerowcnt=3 the title number to print in one page!
contentrowcnt=6 the record number to print in one page!
footrowcnt=1 the footer number to print in one page!
pagerowcnt=headrowcnt+titlerowcnt+contentrowcnt+footrowcnt
totalpagecnt=int((totalpagecnt+contentrowcnt-1)/contentrowcnt)
columnawidth=5 the columna width!
columnbwidth=30 the columnb width!
columncwidth=5 the columnc width!
columndwidth=5 the columnd width!
add the head and title
call head_title
add the data
do while not rs.eof
with xlsheet1
.cells(rowcnt,1).value = rs(0)
.cells(rowcnt,2).value = rs(1)
.cells(rowcnt,3).value = rs(2)
.cells(rowcnt,4).value = rs(3)
end with
rs.movenext
contentrownowcnt=contentrownowcnt+1
if not rs.eof then
if contentrownowcnt mod (contentrowcnt) =0 then
contentrownowcnt=0
rowcnt = cint(rowcnt) + 1
add the foot
call foot_title
add the head and title
call head_title
else
rowcnt = cint(rowcnt) + 1
end if
else
rowcnt = cint(rowcnt) + 1
call foot_title
end if
loop
format the grid and font
call format_grid
release references
xlsheet1.printout
xlbook.saved = true
set xlmodule = nothing
set xlsheet1 = nothing
set xlbook = nothing
xlapp.quit
set xlapp = nothing
rs.close
set rs=nothing
end sub
sub head_title()
dim headrow
headrow=1
do while headrow<= headrowcnt
with xlsheet1
.range("c"+trim(rowcnt)+":"+"d"+trim(rowcnt)).merge
end with
rowcnt=rowcnt+1
headrow=headrow+1
loop
format the head name of cells (the new page of row=5,6,7)
with xlsheet1
.cells(rowcnt-3, 2).value = "the job information table"
.cells(rowcnt-3, 3).value = date()
.cells(rowcnt-4, 3).value = "the "+trim(pageno)+"/"+trim(totalpagecnt) +" pages"
end with
format the title field name of cells
with xlsheet1
.range("a"+trim(rowcnt) +":b"+trim(rowcnt)).merge
.range("a"+trim(rowcnt+1) +":a"+trim(rowcnt+2)).merge
.range("b"+trim(rowcnt+1) +":b"+trim(rowcnt+2)).merge
.range("c"+trim(rowcnt) +":d"+trim(rowcnt)).merge
.range("c"+trim(rowcnt+1) +":c"+trim(rowcnt+2)).merge
.range("d"+trim(rowcnt+1) +":d"+trim(rowcnt+2)).merge
.cells(rowcnt, 1).value = "the job"
.cells(rowcnt+1,1).value = "job_id"
.cells(rowcnt+1,2).value = "job_desc"
.cells(rowcnt, 3).value = "level"
.cells(rowcnt+1,3).value = "max level"
.cells(rowcnt+1,4).value = "min level"
end with
rowcnt=int(rowcnt)+3
pageno=pageno+1
end sub
sub foot_title()
dim footrow
footrow=1
do while footrow<= footrowcnt
with xlsheet1
.range("c"+trim(rowcnt)+":"+"d"+trim(rowcnt)).merge
end with
rowcnt=rowcnt+1
footrow=footrow+1
loop
with xlsheet1
.cells(rowcnt-1, 1).value = "a:"
.cells(rowcnt-1, 2).value = "b:"
.cells(rowcnt-1, 3).value = "c:"
end with
end sub
sub format_grid()
dim strcode
dim mymacro
strcode = _
"sub mymacro() " & vbcr & _
"dim headrowcnt" & vbcr & _
"dim titlerowcnt" & vbcr & _
"dim contentrowcnt" & vbcr & _
"dim footrowcnt" & vbcr & _
"dim pagerowcnt" & vbcr & _
"dim bgncnt" & vbcr & _
"headrowcnt="& headrowcnt &"" & vbcr & _
"titlerowcnt="& titlerowcnt &"" & vbcr & _
"contentrowcnt="& contentrowcnt &"" & vbcr & _
"footrowcnt="& footrowcnt &"" & vbcr & _
"pagerowcnt=headrowcnt+titlerowcnt+contentrowcnt+footrowcnt" & vbcr & _
"bgncnt=1" & vbcr & _
"pageno=1" & vbcr & _
"range(""a""+trim(bgncnt)+"":d""+trim(bgncnt)).select" & vbcr & _
"with sheet1" & vbcr & _
" .range(""a1"").columnwidth = "& columnawidth&"" & vbcr & _
" .range(""b1"").columnwidth = "& columnbwidth&"" & vbcr & _
" .range(""c1"").columnwidth = "& columncwidth&"" & vbcr & _
" .range(""d1"").columnwidth = "& columndwidth&"" & vbcr & _
"end with" & vbcr & _
"do while pageno<= "& totalpagecnt&"" & vbcr & _
"if pageno= "& totalpagecnt& " then" & vbcr & _
" contentrowcnt="& contentrownowcnt &"" & vbcr & _
" pagerowcnt=headrowcnt+titlerowcnt+contentrowcnt+footrowcnt" & vbcr & _
"end if" & vbcr & _
"range(""a""+trim(bgncnt)+"":d""+trim(bgncnt+pagerowcnt-1)).select" & vbcr & _
"with range(""a""+trim(bgncnt)+"":d""+trim(bgncnt+pagerowcnt-1))" & vbcr & _
" .borders.linestyle = xlcontnuous" & vbcr & _
" .borders.weight = xlthin" & vbcr & _
" .borders.colorindex = 10" & vbcr & _
" .rowheight = 15" & vbcr & _
" .verticalalignment = xlcenter" & vbcr & _
" .horizontalalignment = xlleft" & vbcr & _
" .font.size = 9" & vbcr & _
"end with" & vbcr & _
"with range(""a""+trim(bgncnt)+"":d""+trim(bgncnt+headrowcnt-1))" & vbcr & _
" .font.size = 11" & vbcr & _
" .font.bold = true" & vbcr & _
" .borders.linestyle = xllinestylenone" & vbcr & _
" .verticalalignment = xlcenter" & vbcr & _
" .horizontalalignment = xlcenter" & vbcr & _
" .orientation = xlhorizontal" & vbcr & _
"end with" & vbcr & _
"with range(""a""+trim(bgncnt+headrowcnt)+"":d""+trim(bgncnt+headrowcnt+titlerowcnt-1))" & vbcr & _
" .wraptext = true" & vbcr & _
" .font.size = 9" & vbcr & _
" .font.bold = true" & vbcr & _
" .verticalalignment = xlcenter" & vbcr & _
" .horizontalalignment = xlcenter" & vbcr & _
" .orientation = xlhorizontal" & vbcr & _
"end with" & vbcr & _
"with range(""a""+trim(bgncnt+headrowcnt+titlerowcnt+contentrowcnt)+"":d""+trim(bgncnt+headrowcnt+titlerowcnt+contentrowcnt+footrowcnt-1))" & vbcr & _
" .font.size = 9" & vbcr & _
" .font.bold = true" & vbcr & _
" .borders.linestyle = xllinestylenone" & vbcr & _
" .verticalalignment = xlcenter" & vbcr & _
" .horizontalalignment = xlleft" & vbcr & _
" .orientation = xlhorizontal" & vbcr & _
"end with" & vbcr & _
"pageno=pageno+1" & vbcr & _
"bgncnt=bgncnt+pagerowcnt" & vbcr & _
"loop" & vbcr & _
"with sheet1.pagesetup" & vbcr & _
" .headermargin = application.centimeterstopoints(0)" & vbcr & _
" .leftmargin = application.centimeterstopoints(2)" & vbcr & _
" .rightmargin =application.centimeterstopoints(2)" & vbcr & _
" .topmargin = application.centimeterstopoints(1)" & vbcr & _
" .bottommargin = application.centimeterstopoints(1)" & vbcr & _
" .footermargin = application.centimeterstopoints(0)" & vbcr & _
" .orientation = xllandscape" & vbcr & _
" .orientation = xlportrait" & vbcr & _
" .centerhorizontally = true" & vbcr & _
" .centervertically = false" & vbcr & _
" .papersize = xlpapera4" & vbcr & _
"end with" & vbcr & _
"range(""a1"").select" & vbcr & _
"end sub"
xlmodule.codemodule.addfromstring (strcode)
xlapp.run "mymacro"
end sub
</script>
