Vfp控制Excel快速报表示例

Vfp控制Excel快速报表示例出处:网络

注：先将数据库文件导出到Excel文件，再设置Excel文件的标题、表头、纸张属性、页眉、页脚、（左、右、上、下边距）等，报表速度比用for...endf、scan...ends导出报表要快的多。

程序源码如下：

yesno=MESSAGEBOX(\将数据输出到Excel表中，是否继续？\提示信息\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(\请输入文件名,并选择回车存盘.\if not EMPTY(Exl_file)

if UPPER(RIGHT(ALLTRIM(Exl_file),4)) = \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(\WAIT CLEAR

MessageBox( \访问Excel失败！请检查你的系统是否正确安装 Excel 软件！\不正常\RETURN .f. ENDIF

lcmsg=\正在设置Excel，请稍侯…\

WAIT lcmsg WINDOW NOWAIT AT SROW()/2, (SCOLS()-LEN(lcMsg))/2

oleapp=createobject(\

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(\

oleapp.activesheet.rows(1).insert &&在第一行前插入以下一行标题 OLEI.SHEETS.ITEM(1).Range(\oleapp.SELECTION.MERGECELLS=.T. with oleapp.range(\&&设置标题及字体属性

.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())))+'日'

*以下制作双表头