《VFP控制EXCEL的方法》由会员分享，可在线阅读，更多相关《VFP控制EXCEL的方法（15页珍藏版）》请在金锄头文库上搜索。
1、VFP控制EXCEL的常用方法oExl=CREATEOBJECT(Excel.application)oExl.Visible=.T.oExl.DefaultSaveFormat=39 oExl.SheetsInNewWorkbook=1 oExl.Workbooks.Open(cXLS)oExl.WindowState=-4140&窗口最小化oExl.WindowState=-4143&窗口正常化oExl.WindowState=-4147&窗口最大化*&页面设置 oExl.ActiveSheet.PageSetup.PrintTitleRows=$1:$3 &打印标题 oExl.Activ
2、eSheet.PageSetup.Orientation=2 &页面方向:1纵向/2横向*oExl.Ac tiveShee t.PageSe tup.PaperSize=ll&纸尺寸：9-A4/ll-A5oExl.ActiveSheet.PageSetup.TopMargin=0.8/0.035&顶边距oExl.ActiveSheet.PageSetup.BottomMargin=0.8/0.035 &底边距 oExl.ActiveSheet.PageSetup.LeftMargin=0&左边距oExl.ActiveSheet.PageSetup.RightMargin=0&右边距oExl.A
3、ctiveSheet.PageSetup.CenterHorizontally=.T. &页面居中 oExl.ActiveSheet.PageSetup.HeaderMargin=0.5/0.035 oExl.ActiveSheet.PageSetup.FooterMargin=0.5/0.035oExl.ActiveSheet.PageSetup.LeftHeader=&9表格”9 是字号” oExl.ActiveSheet.PageSetup.CenterHeader= oExl.ActiveSheet.PageSetup.RightHeader=&9 oExl.ActiveSheet.P
4、ageSetup.LeftFooter=oExl.ActiveSheet.PageSetup.CenterFooter=&9第&P 页,共&N 页 oExl.ActiveSheet.PageSetup.RightFooter=&9 制 表 人 ：+ 代 码 + 制 表 时 间：+TTOC(DATETIME()*&整体格式设置oExl.ActiveSheet.Rows.Font.Size=9oExl.Ac ti veShee t.Rows.F ont. Name=宋体 oExl.ActiveSheet.Rows.RowHeight=0.5/0.035 oExl.ActiveSheet.Rows.
5、NumberFormatLocal=0.00_ ;红色-0.00oExl.ActiveSheet.Rows.VerticalAlignment=2&单元格容垂直居中*&获取最大行号和最大列号Local nMaxRow,nMaxCol nMaxRow=oExl.ActiveSheet.UsedRange.Rows.Count nMaxCol=oExl.ActiveSheet.UsedRange.Columns.Count &表头格式设置oExl.ActiveSheet.Rows(1).Font.Size=16 oExl.ActiveSheet.Rows(1).Font.Bold=.T. oExl
6、.ActiveSheet.Rows(1).RowHeight=1/0.035 oExl.ActiveSheet.Rows(1).HorizontalAlignment=3 oExl.ActiveSheet.Rows(2).HorizontalAlignment=3 oExl.ActiveSheet.Rows(3).HorizontalAlignment=3 oExl.ActiveSheet.Rows(3).Font.Bold=.T. oExl.ActiveSheet.Range(A1:+CHR(96+nMaxCol)+1).Merge oExl.ActiveSheet.Range(A2:+CH
7、R(96+nMaxCol)+2).Merge *&条件格式隐藏0值 oExl.ActiveSheet.UsedRange.FormatConditions.Delete oExl.ActiveSheet.UsedRange.FormatConditions.Add(1,3,0)oExl.ActiveSheet. UsedRange.FormatConditions(l). Font.Colorlndex=2&文字白色*oExl.ActiveSheet.Range(A1:B1).Interior.ColorIndex=6&单元格底纹黄色*oExl.ActiveSheet.Columns.Auto
8、Fit &自动列宽oExl.ActiveSheet.Range(A3:+CHR(96+nMaxCol)+ALLTRIM(STR(nMaxRow).Borders.Wei ght=2 &边框线*&自动筛选IF !oExl.ActiveSheet.AutoFilterMode &判断是否存在自动筛选 oExl.ActiveSheet.Rows(3).Autofilter &如果不存在自动筛选,则添加自动筛选ENDIF*&冻结窗格oExl.ActiveSheet.Range(D4).Select oExl.ActiveWindow.FreezePanes = .T.*&分类汇总Local Arry(
9、1),nMaxRow,nMaxColArry(1)=5 nMaxRow=oExl.ActiveSheet.UsedRange.Rows.Count nMaxCol=oExl.ActiveSheet.UsedRange.Columns.Count oExl.ActiveSheet.Range(A3:+chr(96+nMaxCol)+alltrim(str(nMaxRow).Subtotal(2, -4157,Arry,.T.,.F.,.T.) &按第2列分类汇总数组Arry保存的列* 1、对象的创建与关闭xlz xlz xlz xlz xlz xlz xlz xlz xlz xlz xlz xl
10、z xlz xlz xlz xlz xlz xlz xlz xlz xlz xlz xlz xlz xlz xfz xfz xfz xfz xfz xfz不不不不不不不不不不不不不不不不不不不不不不不不不不不不不不不oExl=CREATEOBJECT(Excel.application)&创建 Excel 对象oExl.SheetsInNewWorkbook=1 &新建工作簿默认包含工作表个数 oExl.Workbooks.Add &新建工作簿,工作表 个数由SheetsInNewWorkBooks属性指定oExl.Workbooks.Open(cXLS,3,.T.)&打开指定工作簿(更新/只
11、读打开)oExl.Workbooks.Open(cXLS, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)oExl.Worksheets(cSheet).Activate oExl.Worksheets(3).Activate oExl.WorkSheets.Count oExl.DefaultSaveFormat=3
12、9 oExl.DisplayAlerts=.F.oExl.Visible=.T. oExl.Visible=.F.oExl.Caption=Excel 标题栏 oExl.WorkSheet(Sheet2).Range(A1 oExl.Quit oExl.DisplayRecentFiles=.T. oExl.RecentFiles.Maximum=4 oExl.UserName=XXXXoExl.S tandardFon t=宋体 oExl.StandardFontSize=12 oExl.DefaultFilePath=D:XXXXXX oExl.EnableSound=False oExl
13、.RollZoom=False oExl.TransitionMenuKey=/ oExl.ActiveWorkbook.Password=123 oExl.ActiveWorkbook.WritePassword=&激活工作表Sheet3 &激活（从左到右）第3个工作表 &工作簿中工作表数 &默认格式Excel 5.0 &不显示警告信息&显示Excel窗口&不显示Excel窗口&更改Excel标题栏 .PasteSpecial&粘贴&退出 Excel &是否显示最近打开文档 &历史最大纪录数 &用户名&标准字体 &标准字体大小&默认工作目录&声音反馈&智能鼠标缩放&Microsoft Off
14、ice Excel 菜单键oExl.ActiveWorkbook.ReadOnlyRecommended=False oExl.ActiveWorkbook.SetPasswordEncryptionOptionsPasswordEncryptionProvider:=, _ PasswordEncryptionAlgorithm:=OfficeStandard,PasswordEncryptionKeyLength:=40 _, PasswordEncryptionFileProperties:=FalseIF oExl.ActiveWorkbook.FileFormat=39&格式为 Ex
15、cel 5.0 工作簿ENDIF oExl.ActiveWorkBook.SaveAs(FileName,FileFormat,PassWord,WriteResPassWord,ReadOn lyRecommended,CreateBackup)&另存为&参数说明FileName 字符型,指定文件名FileFormat 数值型,文件格式-4143 Microsoft Office Excel11 DBF439 Microsoft Excel 5.0/9543 Microsoft Excel97-Excel2003 & 5.0/9544 网页Html文件-4158 文本文件(制表符分隔) PassWord 字符型,只读密码 WriteResPassWord 字符型,写密码ReadOnlyRecommended逻辑型，建议只读CreateBackup 逻辑型,自动备份例如：oExl.Ac ti veWorkBook.SaveAs(d:l.xls,39)&放弃存盘
