VFP控制EXCEL的方法(终于找到了)

VFP控制EXCEL的常用方法oExl=CREATEOBJECT('Excel.application')oExl.Visible=.T.oExl.DefaultSaveFormat=39oExl.SheetsInNewWorkbook=1oExl.Workbooks.Open(cXLS)oExl.WindowState=-4140&&窗口最小化oExl.WindowState=-4143&&窗口正常化oExl.WindowState=-4147&&窗口最大化*------------------------------------------------------*&&页面设置oExl.ActiveSheet.PageSetup.PrintTitleRows=$1:$3&&打印标题oExl.ActiveSheet.PageSetup.Orientation=2&&页面方向:1纵向/2横向*oExl.ActiveSheet.PageSetup.PaperSize=11&&纸张尺寸:9-A4/11-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.ActiveSheet.PageSetup.CenterHorizontally=.T.&&页面居中oExl.ActiveSheet.PageSetup.HeaderMargin=0.5/0.035oExl.ActiveSheet.PageSetup.FooterMargin=0.5/0.035oExl.ActiveSheet.PageSetup.LeftHeader=&9表格”9是字号”oExl.ActiveSheet.PageSetup.CenterHeader=oExl.ActiveSheet.PageSetup.RightHeader=&9oExl.ActiveSheet.PageSetup.LeftFooter=oExl.ActiveSheet.PageSetup.CenterFooter=&9第&P页,共&N页oExl.ActiveSheet.PageSetup.RightFooter=&9制表人:'+代码+'制表时间:+TTOC(DATETIME())*------------------------------------------------------*&&整体格式设置oExl.ActiveSheet.Rows.Font.Size=9oExl.ActiveSheet.Rows.Font.Name='宋体'oExl.ActiveSheet.Rows.RowHeight=0.5/0.035oExl.ActiveSheet.Rows.NumberFormatLocal=0.00_;[红色]-0.00oExl.ActiveSheet.Rows.VerticalAlignment=2&&单元格内容垂直居中*------------------------------------------------------*&&获取最大行号和最大列号LocalnMaxRow,nMaxColnMaxRow=oExl.ActiveSheet.UsedRange.Rows.CountnMaxCol=oExl.ActiveSheet.UsedRange.Columns.Count&&表头格式设置oExl.ActiveSheet.Rows(1).Font.Size=16oExl.ActiveSheet.Rows(1).Font.Bold=.T.oExl.ActiveSheet.Rows(1).RowHeight=1/0.035oExl.ActiveSheet.Rows(1).HorizontalAlignment=3oExl.ActiveSheet.Rows(2).HorizontalAlignment=3oExl.ActiveSheet.Rows(3).HorizontalAlignment=3oExl.ActiveSheet.Rows(3).Font.Bold=.T.oExl.ActiveSheet.Range('A1:'+CHR(96+nMaxCol)+'1').MergeoExl.ActiveSheet.Range('A2:'+CHR(96+nMaxCol)+'2').Merge*------------------------------------------------------*&&条件格式隐藏0值oExl.ActiveSheet.UsedRange.FormatConditions.DeleteoExl.ActiveSheet.UsedRange.FormatConditions.Add(1,3,'0')oExl.ActiveSheet.UsedRange.FormatConditions(1).Font.ColorIndex=2&&文字白色*------------------------------------------------------*oExl.ActiveSheet.Range('A1:B1').Interior.ColorIndex=6&&单元格底纹黄色*------------------------------------------------------*oExl.ActiveSheet.Columns.AutoFit&&自动列宽oExl.ActiveSheet.Range(A3:+CHR(96+nMaxCol)+ALLTRIM(STR(nMaxRow))).Borders.Weight=2&&边框线*------------------------------------------------------*&&自动筛选IF!oExl.ActiveSheet.AutoFilterMode&&判断是否存在自动筛选oExl.ActiveSheet.Rows(3).Autofilter&&如果不存在自动筛选,则添加自动筛选ENDIF*------------------------------------------------------*&&冻结窗格oExl.ActiveSheet.Range('D4').SelectoExl.ActiveWindow.FreezePanes=.T.*------------------------------------------------------**------------------------------------------------------*&&分类汇总LocalArry(1),nMaxRow,nMaxColArry(1)=5nMaxRow=oExl.ActiveSheet.UsedRange.Rows.CountnMaxCol=oExl.ActiveSheet.UsedRange.Columns.CountoExl.ActiveSheet.Range('A3:'+chr(96+nMaxCol)+alltrim(str(nMaxRow))).Subtotal(2,-4157,@Arry,.T.,.F.,.T.)&&按第2列分类汇总数组Arry保存的列*------------------------------------------------------*1、对象的创建与关闭*******************************oExl=CREATEOBJECT('Excel.application')&&创建Excel对象oExl.SheetsInNewWorkbook=1&&新建工作簿默认包含工作表个数oExl.Workbooks.Add&&新建工作簿,工作表个数由SheetsInNewWorkBooks属性指定oExl.Workbooks.Open(cXLS,3,.T.)&&打开指定工作簿(更新链接/只读打开)oExl.Workbooks.Open(cXLS,[UpdateLinks],[ReadOnly],[Format],[Password],[WriteResPassword],[IgnoreReadOnlyRecommended],[Origin],[Delimiter],[Editable],[Notify],[Converter],[AddToMru],[Local],[CorruptLoad])oExl.Worksheets(cSheet).Activate&&激活工作表Sheet3oExl.Worksheets(3).Activate&&激活(从左到右)第3个工作表oExl.WorkSheets.Count&&工作簿中工作表数oExl.DefaultSaveFormat=39&&默认格式Excel5.0oExl.DisplayAlerts=.F.&&不显示警告信息oExl.Visible=.T.&&显示Excel窗口oExl.Visible=.F.&&不显示Excel窗口oExl.Caption=Excel标题栏&&更改Excel标题栏oExl.WorkSheet(Sheet2).Range(A1).PasteSpecial&&粘贴oExl.Quit&&退出ExceloExl.DisplayRecentFiles=.T.&&是否显示最近打开文档oExl.RecentFiles.Maximum=4&&历史最大纪录数oExl.UserName=XXXX&&用户名oExl.StandardFont=宋体&&标准字体oExl.StandardFontSize=12&&标准字体大小oExl.DefaultFilePath=D:\XXXXXX\&&默认工作目录oExl.EnableSound=False&&声音反馈oExl.RollZoom=False&&智能鼠标缩放oExl.TransitionMenuKey=/&&MicrosoftOfficeExcel菜单键oExl.ActiveWorkbook.Password=123oExl.ActiveWorkbook.WritePassword=456oExl.ActiveWorkbook.ReadOnlyRecommended=FalseoExl.ActiveWorkbook.SetPasswordEncryptionOptionsPasswordEncryptionProvider:=,_PasswordEncryptionAlgorithm:=OfficeStandard,PasswordEncryptionKeyLength:=40_,PasswordEncryptionFileProperties:=FalseIFoExl.ActiveWorkbook.FileFormat==39&&格式为Excel5.0工作簿ENDIFoExl.ActiveWorkBook.SaveAs(FileName,FileFormat,PassWord,WriteResPassWord,ReadOnlyRecommended,CreateBackup)&&另存为&&参数说明FileName字符型,指定文件名FileFormat数值型,文件格式-4143MicrosoftOfficeExcel11DBF439MicrosoftExcel5.0/9543MicrosoftExcel97-Excel2003&5.0/9544网页Html文件-4158文本文件(制表符分隔)PassWord字符型,只读密码WriteResPassWord字符型,写密码ReadOnlyRec