<html><head><metacontent=”text/html;charset=BIG5″http-equiv=”Content-Type”><title>clientuserdsproduceexcelreport</title></head><bodybgColor=”skyblue”topMargin=0leftMargin=”20″oncontextmenu=”returnfalse”rightMargin=”0″bottomMargin=”0″><formaction=”test_print_report.asp”method=”post”name=”myform”><divalign=”center”><center><tableborder=”5″bgcolor=”#ffe4b5″><tr><tdalign=”middle”bgcolor=”#ffffff”bordercolor=”#000080″><fontcolor=”#000080″size=”3″>clientuserdsproduceexcelreport</font></td></tr></table></div><divalign=”left”><inputtype=”button”value=”QueryData”name=”query”language=”vbscript”onclick=”fun_query()”><inputtype=”button”value=”ClearData”name=”Clear”language=”vbscript”onclick=”fun_clear()”><inputtype=”button”value=”ExcelReport”name=”report”language=”vbscript”onclick=”fun_excel()”></div><divid=”adddata”></div></form></center></body></html><scriptlanguage=”vbscript”>dimrds,rs,dfdimstrSQL,StrRs,strCn,RowCntdimxlApp,xlBook,xlSheet1,xlmodule,XlPageSetupdimHeadRowCnt,TitleRowCnt,ContentRowCnt,FootRowCntdimPageRowCnt,PageNo,TotalPageCnt,ContentRowNowCntdimColumnAllWidth,ColumnAWidth,ColumnBWidth,ColumnCWidth,ColumnDWidthsubfun_query()setrds=CreateObject(“RDS.DataSpace”)Setdf=rds.CreateObject(“RDSServer.DataFactory”,”[url]http://iscs00074[/url]”)strCn=”DRIVER={SQLServer};SERVER=iscs00074;UID=sa;APP=MicrosoftDevelopmentEnvironment;DATABASE=pubs;UserId=sa;PASSWORD=;”strSQL=”Select*fromjobs”Setrs=df.Query(strCn,strSQL)ifnotrs.eofthenStrRs=”<tableborder=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=StrRsStrRs=””elsemsgbox”Nodatainthetable!”endifendsubsubfun_clear()StrRs=””adddata.innerHTML=StrRsendsubsubfun_excel()setrds=CreateObject(“RDS.DataSpace”)Setdf=rds.CreateObject(“RDSServer.DataFactory”,”[url]http://iscs00074[/url]”)strCn=”DRIVER={SQLServer};SERVER=iscs00074;UID=sa;APP=MicrosoftDevelopmentEnvironment;DATABASE=pubs;UserId=sa;PASSWORD=;”strSQL=”Selectcount(*)asrecordcntfromjobs”Setrs=df.Query(strCn,strSQL)TotalPageCnt=rs(“recordcnt”)rs.closesetrs=nothingstrSQL=”Select*fromjobs”Setrs=df.Query(strCn,strSQL)SetxlApp=CreateObject(“EXCEL.APPLICATION”)SetxlBook=xlApp.Workbooks.AddSetxlSheet1=xlBook.ActiveSheetSetxlmodule=xlbook.VBProject.VBComponents.Add(1)xlSheet1.Application.Visible=TruexlSheet1.Application.UserControl=Truei=0RowCnt=1PageNo=1HeadRowCnt=4‘Theheadernumbertoprintinonepage!TitleRowCnt=3‘Thetitlenumbertoprintinonepage!ContentRowCnt=6‘Therecordnumbertoprintinonepage!FootRowCnt=1‘Thefooternumbertoprintinonepage!PageRowCnt=HeadRowCnt+TitleRowCnt+ContentRowCnt+FootRowCntTotalPageCnt=int((TotalPageCnt+ContentRowCnt-1)/ContentRowCnt)ColumnAWidth=5‘TheColumnAWidth!ColumnBWidth=30‘TheColumnBWidth!ColumnCWidth=5‘TheColumnCWidth!ColumnDWidth=5‘TheColumnDWidth!‘AddtheHeadandTitlecallhead_title‘AddtheDatadowhilenotrs.eofWithxlSheet1.cells(RowCnt,1).value=rs(0).cells(RowCnt,2).value=rs(1).cells(RowCnt,3).value=rs(2).cells(RowCnt,4).value=rs(3)endwithrs.movenextContentRowNowCnt=ContentRowNowCnt+1ifnotrs.eofthenifContentRowNowCntmod(ContentRowCnt)=0thenContentRowNowCnt=0RowCnt=cint(RowCnt)+1‘AddtheFootcallfoot_title‘AddtheHeadandTitlecallhead_titleelseRowCnt=cint(RowCnt)+1endifelseRowCnt=cint(RowCnt)+1callfoot_titleendifloop‘FormattheGridandFontcallformat_grid‘ReleaseReferences‘XLSheet1.PrintOut‘xlBook.Saved=TrueSetxlmodule=NothingSetxlSheet1=NothingSetxlBook=NothingxlApp.QuitSetxlApp=Nothingrs.closesetrs=nothingendsubsubhead_title()dimHeadRowHeadRow=1dowhileHeadRow<=HeadRowCntWithxlSheet1.range(“C”+trim(RowCnt)+”:”+”D”+trim(RowCnt)).mergeendwithRowCnt=RowCnt+1HeadRow=HeadRow+1loop‘Formattheheadnameofcells(Thenewpageofrow=5,6,7)WithxlSheet1.Cells(RowCnt-3,2).Value=”THEJOBINFORMATIONTABLE”.Cells(RowCnt-3,3).Value=date().Cells(RowCnt-4,3).Value=”The”+trim(PageNo)+”/”+trim(TotalPageCnt)+”Pages”endwith‘FormatthetitlefieldnameofcellsWithxlSheet1.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=”Thejob”.Cells(RowCnt+1,1).Value=”job_id”.Cells(RowCnt+1,2).Value=”job_desc”.Cells(RowCnt,3).Value=”Level”.Cells(RowCnt+1,3).Value=”Maxlevel”.Cells(RowCnt+1,4).Value=”Minlevel”EndWithRowCnt=int(RowCnt)+3PageNo=PageNo+1endsubsubfoot_title()dimFootRowFootRow=1dowhileFootRow<=FootRowCntWithxlSheet1.range(“C”+trim(RowCnt)+”:”+”D”+trim(RowCnt)).mergeendwithRowCnt=RowCnt+1FootRow=FootRow+1loopWithxlSheet1.Cells(RowCnt-1,1).Value=”A:”.Cells(RowCnt-1,2).Value=”B:”.Cells(RowCnt-1,3).Value=”C:”endwithendsubsubformat_grid()dimstrCodedimMyMacrostrCode=_”subMyMacro()”&vbCr&_”dimHeadRowCnt”&vbCr&_”dimTitleRowCnt”&vbCr&_”dimContentRowCnt”&vbCr&_”dimFootRowCnt”&vbCr&_”dimPageRowCnt”&vbCr&_”dimBgnCnt”&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&_”Withsheet1″&vbCr&_”.Range(“”A1″”).ColumnWidth=”&ColumnAWidth&””&vbCr&_”.Range(“”B1″”).ColumnWidth=”&ColumnBWidth&””&vbCr&_”.Range(“”C1″”).ColumnWidth=”&ColumnCWidth&””&vbCr&_”.Range(“”D1″”).ColumnWidth=”&ColumnDWidth&””&vbCr&_”EndWith”&vbCr&_”dowhilePageNo<=”&TotalPageCnt&””&vbCr&_”ifPageNo=”&TotalPageCnt&”then”&vbCr&_”ContentRowCnt=”&ContentRowNowCnt&””&vbCr&_”PageRowCnt=HeadRowCnt+TitleRowCnt+ContentRowCnt+FootRowCnt”&vbCr&_”endif”&vbCr&_”Range(“”A””+trim(BgnCnt)+””:D””+trim(BgnCnt+PageRowCnt-1)).Select”&vbCr&_”WithRange(“”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&_”EndWith”&vbCr&_”WithRange(“”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&_”EndWith”&vbCr&_”WithRange(“”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&_”endWith”&vbCr&_”WithRange(“”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&_”endWith”&vbCr&_”PageNo=PageNo+1″&vbCr&_”BgnCnt=BgnCnt+PageRowCnt”&vbCr&_”loop”&vbCr&_”WithSheet1.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&_”EndWith”&vbCr&_”Range(“”A1″”).Select”&vbCr&_”endsub”xlmodule.CodeModule.AddFromString(strCode)xlApp.Run”MyMacro”endsub</script> 生活比你想象的要容易得多，只要学会接受那些不可接受的，
