|
Vfp控制Excel快速报表示例 出处:网络 注：先将数据库文件导出到Excel文件，再设置Excel文件的标题、表头、纸张属性、页眉、页脚、（左、右、上、下边距）等，报表速度比用for...endf、scan...ends导出报表要快的多。 程序源码如下： yesno=MESSAGEBOX("将数据输出到Excel表中，是否继续？",32+0+1,"提示信息") if yesno=2 return endif *产生要保存的文件名 wjm='产销存表'+ALLTRIM(STR(YEAR(DATE())))+IIF(LEN(ALLTRIM(STR(month(date()-28))))=1,'0'+ALLTRIM(STR(month(date()-28))),ALLTRIM(STR(month(date()-28)))) Exl_file = PUTFILE("请输入文件名,并选择回车存盘.","&wjm","XLS") if not EMPTY(Exl_file) if UPPER(RIGHT(ALLTRIM(Exl_file),4)) = ".XLS" OLDAREA = SELECT() PRIVATE OLDREC lcmsg="正在整理数据库数据，请稍侯…" WAIT lcmsg WINDOW NOWAIT AT SROW()/2, (SCOLS()-LEN(lcMsg))/2 *整理数据 SET ENGINEBEHAVIOR 80 &&或sys(3099,80) SELECT cxchz.dm,mc,cxchz.qcsl,cxchz.qcje,cxchz.byrksl,; cxchz.byrkje,cxchz.jqdj,cxchz.byxssl,cxchz.byxscb,cxchz.qmsl,cxchz.qmje ; from cxchz into dbf 产销存表.dbf SORT on dm to ls USE ls EXCLUSIVE DELETE FOR (qcsl=0.and.byrksl=0.and.byxssl=0.and.qmsl=0).or.empty(dm).or.subs(ALLTRIM(dm),1,4)='1108'.or.subs(ALLTRIM(dm),1,4)='1109'.or.subs(ALLTRIM(dm),1,2)='12' pack COPY TO 产销存表.dbf USE 产销存表 EXCLUSIVE *加入汇总项 sum all qcsl,qcje,byrksl,byrkje,byxssl,byxscb,qmsl,qmje to s1,j1,s3,j3,s4,j4,s5,j5 APPEND BLANK REPLACE qcsl with s1,qcje with j1,byrksl with s3,byrkje with j3,byxssl with s4,byxscb with j4,qmsl with s5,qmje with j5 nCount=AFIELDS(aPrtArray) &&总字段数 hen=RECCOUNT() &&总记录数 GO top COPY TO '&Exl_file' TYPE XLS &&生成excel文件 SELECT (OLDAREA) endif ENDIF CLOSE DATABASES DELETE FILE '产销存表.dbf' DELETE FILE 'ls.dbf' ********************************以下为调用EXCEL处理文件格式 lcmsg="正在访问 Excel 软件……" WAIT lcmsg WINDOW NOWAIT AT SROW()/2, (SCOLS()-LEN(lcMsg))/2 ExcelSheet = GetObject('','Excel.Sheet') If Type("ExcelSheet")#'O' WAIT CLEAR MessageBox( "访问Excel失败！请检查你的系统是否正确安装 Excel 软件！",48,"Excel不正常") RETURN .f. ENDIF lcmsg="正在设置Excel，请稍侯…" WAIT lcmsg WINDOW NOWAIT AT SROW()/2, (SCOLS()-LEN(lcMsg))/2 oleapp=createobject("excel.application") oleapp.workbooks.Open('&Exl_file') && 打开文件 oleapp.visible=.t. olei=oleapp.workbooks.item(1) olei.sheets.item(1).Name = allt(str(year(date())))+'年'+allt(str(month(DATE()-28)))+'月份自产产成品产、销、存明细表' *oleapp.activesheet.rows(1).delete oleapp.range("A1:K1").value='' oleapp.activesheet.rows(1).insert &&在第一行前插入以下一行标题 OLEI.SHEETS.ITEM(1).Range("A1:K1").select oleapp.SELECTION.MERGECELLS=.T. with oleapp.range("A1") &&设置标题及字体属性 .value=allt(str(year(date())))+'年'+allt(str(month(DATE()-28)))+'月份自产产成品产、销、存明细表' .Font.Name="黑体" .Font.size=18 .HorizontalAlignment=3 &&设置单元格A1水平居中 endwith oleapp.activesheet.rows(2).insert oleapp.activesheet.rows(3).insert olei.sheets.item(1).cells(2,9).value='报表日期:'+ALLTRIM(STR(YEAR(DATE())))+'年'+ALLTRIM(STR(MONTH(DATE())))+'月'+ALLTRIM(STR(DAY(DATE())))+'日' *以下制作双表头 OLEI.SHEETS.ITEM(1).Range("A3:A4").select oleapp.SELECTION.MERGECELLS=.T. olei.activesheet.range("A3:A3").value='产品编号' OLEI.SHEETS.ITEM(1).Range("B3:B4").select oleapp.SELECTION.MERGECELLS=.T. olei.activesheet.range("B3:B3").value='期末结存产品名称规格' OLEI.SHEETS.ITEM(1).Range("C3:D3").select oleapp.SELECTION.MERGECELLS=.T. olei.activesheet.range("C3:C3").value='期初结存' olei.activesheet.range("C4:C4").value='数量' olei.activesheet.range("D4:D4").value='金额' OLEI.SHEETS.ITEM(1).Range("E3:F3").select oleapp.SELECTION.MERGECELLS=.T. olei.activesheet.range("E3:E3").value='本期产品入库' olei.activesheet.range("E4:E4").value='数量' olei.activesheet.range("F4:F4").value='金额' OLEI.SHEETS.ITEM(1).Range("G3:G4").select oleapp.SELECTION.MERGECELLS=.T. olei.activesheet.range("G3:G3").value='加权平均单价' olei.activesheet.range("G3:G3").WrapText =.T. &&自动换行 OLEI.SHEETS.ITEM(1).Range("H3:I3").select oleapp.SELECTION.MERGECELLS=.T. olei.activesheet.range("H3:H3").value='本期销售' olei.activesheet.range("H4:H4").value='数量' olei.activesheet.range("I4:I4").value='销售成本' OLEI.SHEETS.ITEM(1).Range("J3:J4").select oleapp.SELECTION.MERGECELLS=.T. olei.activesheet.range("J3:J3").value='期末数量' OLEI.SHEETS.ITEM(1).Range("K3:K4").select oleapp.SELECTION.MERGECELLS=.T. olei.activesheet.range("K3:K3").value='期末结存' OLEI.SHEETS.ITEM(1).Range("A2:A2").select WITH oleapp.range("A3:K4") &&表头格式控制 .font.name='黑体' .font.size=12 .HorizontalAlignment=3 .VerticalAlignment=2 endwith HEN=HEN+4 olei.sheets.item(1).range(olei.sheets.item(1).cells(3,1), olei.sheets.item(1).cells(hen,ncount)).borders(1).linestyle = 1 olei.sheets.item(1).range(olei.sheets.item(1).cells(3,1), olei.sheets.item(1).cells(hen,ncount)).borders(2).linestyle = 1 olei.sheets.item(1).range(olei.sheets.item(1).cells(3,1), olei.sheets.item(1).cells(hen,ncount)).borders(3).linestyle = 1 olei.sheets.item(1).range(olei.sheets.item(1).cells(3,1), olei.sheets.item(1).cells(hen,ncount)).borders(4).linestyle = 1 zls='K'-ALLTRIM(STR(hen,8)) WITH oleapp.range("A1:&zls") .VerticalAlignment = 2 &&设置整张表打印区域文字垂直居中 ENDWITH CSL='A'-ALLTRIM(STR(HEN,8)) JSL='B'-ALLTRIM(STR(HEN,8)) WITH oleapp.range("&csl:&jsl") .value='' .mergecells=.t. .value='合 计' .HorizontalAlignment=3 endwith oleapp.ActiveSheet.Columns(1).ColumnWidth=11 &&设置第1列列宽 oleapp.ActiveSheet.Columns(2).ColumnWidth=29 WITH oleapp.sheets.item(1).pagesetup &&页面设置 .CenterFooter="第"+"&P"+"页"+"/共"+"&N"+"页" &&页脚中间 .rightfooter="制表:松涛" &&页脚右边 .TopMargin=0.6/0.035 &&顶边距 .BottomMargin=1.5/0.035 &&底边距 .LeftMargin=0.8/0.035 &&左边距 .RightMargin=0.5/0.035 &&右边距 .FooterMargin=0.8/0.035 .PrintTitleRows = "$1:$4" &&设置打印的顶端标题行1-4行 .Orientation = 2 &&设置纸张为横向 * .PrintQuality = 180 &&设置打印精度 ENDWITH oleapp.sheets.item(1).pagesetup.CenterHorizontally=.t.&&页面水平居中 *oleapp.sheets.item(1).pagesetup.CenterVertically=.t. &&页面垂直居中 olei.sheets.item(1).printpreview &&打印预览 * =MESSAGEBOX(ALLTRIM(STR(YEAR(DATE())))+"年"+ALLTRIM(STR(MONTH(date()-28)))+"月份产销存表已生成,请检查!",0+0+0,"") oleapp.Workbooks(1).Save() rele oleapp 本文来自编程入门网：http://www./VFPwz/report/report4.htm
|
|
|
