vfp直接读取EXCEL文件示例
vfp调用wordORexcel（一）
vfp调用wordORexcel（一）vfp调用word OR excel（一）1.VFP全面控制EXCLVFP和Excel都可以用来进行处理数据库表格，如果巧妙地将二者的优点结合起来，将会大大方便我们的工作。
比如我们可以利用VFP 进行处理数据，而利用Excel的预览打印功能进行报表打印。
这就需要我们在VFP中直接来控制Excel。
下面就在开发VFP应用项目时对Excel的控制作一下介绍：1．创建Excel对象eole=CREATEOBJECT(′Excel.application′)2．添加新工作簿eole.Workbooks.add3．设置第3个工作表为激活工作表eole.Worksheets(″sheet3″).Activate4．打开指定工作簿eole.Workbooks.Open(″c:\temp\ll.xls″)5．显示Excel窗口eole.visible=.t.6．更改Excel标题栏eole.Caption=″VFP应用程序调用Microsoft Excel″7．给单元格赋值eole.cells(1,4).value=XM(XM为数据库字段名)8．设置指定列的宽度(单位：字符个数)eole.ActiveSheet.Columns(1).ColumnWidth=59．设置指定行的高度(单位：磅)eole.ActiveSheet.Rows(1).RowHeight=1/0.035(设定行高为1厘米，1磅=0.035厘米)10．在第18行之前插入分页符eole.Worksheets(″Sheet1″).Rows(18).PageBreak=111．在第4列之前删除分页符eole.ActiveSheet.Columns(4).PageBreak=012．指定边框线宽度(Borders参数如下)ole.ActiveSheet.Range(″b3:d3″).B orders(2).Weight=313．设置四个边框线条的类型eole.ActiveSheet.Range(″b3:d3″).Borders(2).LineStyle=1(其中Borders参数：1－左、2－右、3－顶、4－底、5－斜、6－斜/；LineStyle值：1与7－细实、2－细虚、4－点虚、9－双细实线)14．设置页眉eole.ActiveSheet.PageSetup.CenterHeader=″报表1″15．设置页脚eole.ActiveSheet.PageSetup.Cent erFooter=″第＆P页″16．设置页眉到顶端边距为2厘米eole.ActiveSheet.PageSetup.HeaderMargin=2/0.03517．设置页脚到底边距为3厘米eole.ActiveSheet.PageSetup.FooterMargin=3/0.03518．设置顶边距为2厘米eole.ActiveSheet.PageSetup.T opMargin=2/0.03519．设置底边距为4厘米eole.ActiveSheet.PageSetup.BottomMargin=4/0.03520．设置左边距为2厘米veole.ActiveSheet.PageSetup.LeftMargin=2/0.03521．设置右边距为2厘米eole.ActiveSheet.PageSetup.RightMargin=2/0.03522．设置页面水平居中eole.ActiveSheet.PageSetup.CenterHorizontally=.t.23．设置页面垂直居中eole.ActiveSheet.PageSetup.CenterVertically=.t. 24．设置页面纸张大小(1－窄行8511 39－宽行1411) eole.ActiveSheet.PageSetup.PaperSize=125．打印单元格网线eole.ActiveSheet.PageSetup.PrintGridlines=.t. 26．拷贝整个工作表edRange.Copy27．拷贝指定区域eole.ActiveSheet.Range(″A1:E2″).Copy28．粘贴eole.WorkSheet(″Sheet2″).Range(″A1″).PasteSpecial 29．在第2行之前插入一行eole.ActiveSheet.Rows(2).Insert30．在第2列之前插入一列eole.ActiveSheet.Columns(2).Insert31．设置字体eole.ActiveSheet.Cells(2,1)=″黑体″32．设置字体大小eole.ActiveSheet.Cells(1,1).Font.Size=2533．设置字体为斜体eole.ActiveSheet.Cells(1,1).Font.Italic=.t.34．设置整列字体为粗体eole.ActiveSheet.Columns(1).Font.Bold=.t.35．清除单元格公式eole.ActiveSheet.Cells(1,4).ClearContents36．打印预览工作表eole.ActiveSheet.PrintPreview37．打印输出工作表eole.ActiveSheet.PrintOut38．工作表另为eole.A ctiveWorkbook.SaveAs(″c:\temp\22.xls″)39．放弃存盘eole.ActiveWorkbook.saved=.t.40．关闭工作簿eole.Workbooks.close41．退出Exceleole.quit2.VFP数据导出至EXCEL问题这样设计：oexl=createobject("excel.application")oexl.workbooks.addoexl.visible=.t.oexl.workbooks(1).activateoexl.cells(1,1).value="名称1"oexl.cells(1,2).value="名称2"oexl.cells(1,3).value="名称3"3.如何在VFP5.0中利用EXCEL的功能做报表在VFP中调用Excel要利用Automation。
VFP全面控制EXCEL
最近正好有空，看到不少人对VFP控制EXCEL有兴趣，现将上次的“精华贴"重新整理一下,供各位爱好者参考!oExcel。
Selection。
NumberFormatLocal = "@" ＆＆把被选定的单元格设为文本格式1、对象的创建与关闭＊＊*＊＊＊＊**＊**＊**＊*＊*＊*＊***＊****＊eole=CREATEOBJECT('Excel.application'） &＆创建Excel对象eole.Workbooks。
add && 添加新工作簿eole。
Worksheets（”sheet3"）.Activate ＆＆设置第3个工作表为激活工作表eole。
Workbooks。
Open(”D：\ABC\11.xls”) ＆＆打开指定工作簿eole。
visible=。
t. ＆& 显示Excel窗口eole。
visible=。
F。
&& 不显示Excel窗口eole。
Caption="VFP应用程序调用Microsoft Excel”＆＆更改Excel标题栏eole。
ActiveWorkbook.SaveAs(”D:\ABC\22。
xls") &＆工作表另为eole。
ActiveWorkbook。
saved=。
t. ＆& 放弃存盘eole。
edRange。
Copy && 拷贝整个工作表eole。
ActiveSheet。
Range("A1：E2”）。
Copy &＆拷贝指定区域eole.WorkSheet（"Sheet2”)。
Range(”A1").PasteSpecial &＆粘贴eole。
ActiveWorkbook.Save && 存盘（自动存盘不提问）eole。
Workbooks.close ＆＆关闭工作簿eole。
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="&9"oExl.ActiveSheet.PageSetup.LeftFooter=""oExl.ActiveSheet.PageSetup.CenterFooter="&9第&P页,共&N页"oExl.ActiveSheet.PageSetup.RightFooter="&9制表人:'+代码+' 制表时间:"+TTOC(DATETIME()) *------------------------------------------------------*&&整体格式设置oExl.ActiveSheet.Rows.Font.Size=9='宋体'oExl.ActiveSheet.Rows.RowHeight=0.5/0.035oExl.ActiveSheet.Rows.NumberFormatLocal="0.00_ ;[红色]-0.00"oExl.ActiveSheet.Rows.VerticalAlignment=2 &&单元格内容垂直居中*------------------------------------------------------*&&获取最大行号和最大列号Local nMaxRow,nMaxColnMaxRow=edRange.Rows.CountnMaxCol=edRange.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值edRange.FormatConditions.DeleteedRange.FormatConditions.Add(1,3,'0')edRange.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.*------------------------------------------------------**------------------------------------------------------*&&分类汇总Local Arry(1),nMaxRow,nMaxColArry(1)=5nMaxRow=edRange.Rows.CountnMaxCol=edRange.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&&激活工作表Sheet3 oExl.Worksheets(3).Activate &&激活(从左到右)第3个工作表oExl.WorkSheets.Count &&工作簿中工作表数oExl.DefaultSaveFormat=39 &&默认格式Excel 5.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 &&历史最大纪录数erName="XXXX" &&用户名oExl.StandardFont="宋体" &&标准字体oExl.StandardFontSize="12" &&标准字体大小oExl.DefaultFilePath="D:\XXXXXX\" &&默认工作目录oExl.EnableSound=False &&声音反馈oExl.RollZoom=False &&智能鼠标缩放oExl.TransitionMenuKey="/" &&Microsoft Office Excel菜单键oExl.ActiveWorkbook.Password="123"oExl.ActiveWorkbook.WritePassword="456"oExl.ActiveWorkbook.ReadOnlyRecommended=FalseoExl.ActiveWorkbook.SetPasswordEncryptionOptions PasswordEncryptionProvider:="", _PasswordEncryptionAlgorithm:="OfficeStandard", PasswordEncryptionKeyLength:=40 _, PasswordEncryptionFileProperties:=FalseIF oExl.ActiveWorkbook.FileFormat==39 &&格式为Excel 5.0工作簿ENDIFoExl.ActiveWorkBook.SaveAs(FileName,FileFormat,PassWord,WriteResPassWord,ReadOnlyRecom mended,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.ActiveWorkBook.SaveAs("d:\1.xls",39)oExl.ActiveWorkbook.saved=.T.&&放弃存盘oExl.ActiveWorkbook.Save&&存盘（自动存盘不提问）oExl.Workbooks.close&&关闭工作簿3、单元格的设置***********************************&&冻结窗格oExl.ActiveSheet.Range('D4').SelectoExl.ActiveWindow.FreezePanes = .T.&&条件格式设定oExl.ActiveSheet.Range('A1:H18').FormatConditions.Add(Type,Operator,Formula1,Formula2) Type 类型(1单元格数值,2公式)Operator 操作符(1介于,2不介于,3等于,4不等于,5大于,6小于,7大于等于,8小于等于) Formula1 表达式1Formula2 表达式2,操作符为1介于或2不介于才有效&&条件格式例句:数值为0者,文字颜色白色edRange.FormatConditions.DeleteedRange.FormatConditions.Add(1,3,'0')edRange.FormatConditions(1).Font.ColorIndex=2&&文字白色&&编辑edRange.Copy&&拷贝整个工作表oExl.ActiveSheet.Range("A1:E2").Copy&&拷贝指定区域oExl.ActiveSheet.Columns("D:D").Delete&&删除列oExl.ActiveSheet.Columns("A:B").Delete&&删除列oExl.ActiveSheet.Rows(1).Insert &&在第1行之前插入一行oExl.ActiveSheet.Columns(2).Insert&&在第2列之前插入一列oExl.ActiveSheet.Cells(1,2).Value="ABTMC"&&给单元格赋值oExl.ActiveSheet.Cells(2,2).Value="&BBBB"&&给单元格赋值oExl.ActiveSheet.Cells(1,4).ClearContents&&清除单元格公式oExl.ActiveSheet.Rows(18).PageBreak=1&&在第18行之前插入分页符oExl.ActiveSheet.Columns(4).PageBreak=0&&在第4列之前删除分页符oExl.ActiveSheet.Rows(3).AutoFilter &&自动筛选oExl.ActiveSheet.Cells(1,1).Delete(-4159) &&删除单元格-4159 右边单元格左移-4162 下边单元格上移oExl.Selection.EntireRow.Delete &&所在行整行删除oExl.Selection.EntireColumn.Delete &&所在列整列删除oExl.ActiveSheet.Cells(1,1).Insert(-4142) &&插入单元格-4142 活动单元格下移-4161 活动单元格右移oExl.ActiveSheet.Selection.EntireRow.Insert &&插入行oExl.ActiveSheet.Selection.EntireColumn.Insert &&插入列&&选择性粘贴oExl.ActiveSheet.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=FalsePaste 粘贴数据:8列宽,11公式和数值格式,12数值与数值格式,-4104全部,-4123公式,-4163数值,-4122格式,Operation 运算符:-4142无,2加,3减,4乘,5除SkipBlanks 跳过空白单元:逻辑型Transpose 转置:逻辑型--------------------------------------------------&&排序成功例句:oExl.ActiveSheet.Range('A1:'+CHR(96+nMaxCol)+ALLTRIM(STR(nMaxRow))).Sort(oExl.ActiveSheet .Range(CHR(96+ASCAN(Arry,'原币金额'))+'2'),2,,,,,,1)&&按"原币金额"所在列降序排列,有标题oExl.ActiveSheet.Rows('1:28').SortKey1:=Range("A2"), Order1:=xlDescending,Key2:=Range("B2"), Order2:=xlAscending,Key3:=Range("C2"), Order3:=xlDescending,Header:=xlYes,OrderCustom:=6,MatchCase:=True,Orientation:=xlTopToBottom,SortMethod:=xlStroke,DataOption1:=xlSortNormal,DataOption2:=xlSortNormal,DataOption3:=xlSortNormal参数说明:2 xlDescending降序1 xlAscenging升序标题行:0 xlGuess1 xlYes,2 xlNo1 xlTopToBottom排序方法:1 xlPinYin,2 xlStroke0 xlSortNormalKey1 排序字段:取值类型是范围oExl.ActiveSheet.Range('B2')Order1 升序降序:xlAscending=1升序,xlDescending=2降序Header 有无标题:xlGuess=0自动判断,xlYes=1有,xlNo=2无orderCustom 排序顺序:1普通,6日一二三MatchCase 是否区分大小写:T区分,F不区分Orientation 1从头到底,2从左到右SortMethod 排序方法:xlPinYin=1拼音,xlStroke=2字母DataOption1 xlSortNormal=0正常DataOption2DateOption3&&分类汇总LOCAL Array(1)Array(1)=5oExl.ActiveSheet.Range('A1:G28').Subtotal(2,-4157,@Array,.T.,.F.,.T.)参数列表:GroupBy:=2, Function:=xlSum, TotalList:=Array(3), Replace:=True, PageBreaks:=False, SummaryBelowData:=True参数说明:GroupBy 分组字段序号Function 汇总方式:-4157求和,-4112计数TotalList 汇总数据项:保存汇总列序号的数组引用Replace 替换当前分类汇总:逻辑型,默认TPageBreaks 每组数据分页:逻辑型,默认FSummaryBelowData 汇总结果显示在数据下方:逻辑型,默认T&&汇总结果表间切换oExl.ActiveSheet.Outline.ShowLevels RowLevels:=2oExl.ActiveSheet.Outline.ShowLevels RowLevels:=1oExl.ActiveSheet.Outline.ShowLevels RowLevels:=3&&数值格式设置oExl.ActiveSheet.Range("A").NumberFormatLocal="0.00"&&指定区域内的数字显示格式&&对齐设置oExl.ActiveSheet.Range('A1:B1').HorizontalAlignment=3&&水平方向2左对齐，3居中，4右对齐edRange.VerticalAlignment=2&&垂直方向1靠上，2居中，3靠下oExl.ActiveSheet.Range('A1:B1').WrapText=.T. &&自动换行oExl.ActiveSheet.Range('A1:B1').Orientation = -4166 &&文字方向-4166 垂直向下oExl.ActiveSheet.Range('A1:B1').AddIndent = False &&缩进oExl.ActiveSheet.Range('A1:B1').IndentLevel = 2 &&缩进2字符oExl.ActiveSheet.Range('A1:B1').ShrinkToFit = .T. &&缩小以填充oExl.ActiveSheet.Range('A1:B1').ReadingOrder = -5002 &&文字阅读方向-5002 根据内容-5003 从左到右oExl.selection.MergeCells=.T.&&合并单元oExl.selection.HorizontalAlignment=2&&水平方向2左对齐，3居中，4右对齐oExl.selection.VerticalAlignment=2&&垂直方向1靠上，2居中，3靠下&&字体设置oExl.ActiveSheet.ROWS(1)="黑体"&&设置字体oExl.ActiveSheet.ROWS(1).Font.FontStyle="常规"oExl.ActiveSheet.ROWS(1).Font.Size=14&&设置字体大小oExl.ActiveSheet.ROWS(1).Font.Bold=.T.&&设置整行字体为粗体oExl.ActiveSheet.ROWS(1).Font.StrikeThrough=.T. &&删除线oExl.ActiveSheet.Cells(1,1).Font.Italic=.T.&&设置字体为斜体oExl.ActiveSheet.ROWS(1).Font.Superscript = .T. &&上标oExl.ActiveSheet.ROWS(1).Font.Subscript = .T. &&下标oExl.ActiveSheet.ROWS(1).Font.OutlineFont = FalseoExl.ActiveSheet.ROWS(1).Font.Shadow = FalseoExl.ActiveSheet.ROWS(1).Font.Underline = -4142 &&下划线oExl.ActiveSheet.ROWS(1).Font.ColorIndex = -4105 &&字体颜色自动设置oExl.ActiveSheet.ROWS(1).Font.Underline = 2 &&下划线类型下划线类型2 单下划线&&边框设置oExl.ActiveSheet.Range("b3:d3").Borders.Weight=2&&指定边框线宽度其中Borders参数：1－左、2－右、3－顶、4－底、5－斜\、6－斜/；不带参数表示默认上下左右四个边框LineStyle值：1与7－细实、2－细虚、4－点虚、9－双细实线)oExl.ActiveSheet.Range("A1:BE29").Borders(1).LineStyle=1 &&设置四个边框线条的类型oExl.ActiveSheet.Range("A1:BE29").Borders(2).LineStyle=1 &&设置四个边框线条的类型oExl.ActiveSheet.Range("A1:BE29").Borders(3).LineStyle=1 &&设置四个边框线条的类型oExl.ActiveSheet.Range("A1:BE29").Borders(4).LineStyle=1 &&设置四个边框线条的类型&&1 xlContinuous&&2 xlThin&&5 xlDiagonalDown&&6 xlDiagonalUp&&10 xlEdgeRight&&12 xlInsideHorizontal&&行列设置oExl.ActiveSheet.Columns(1).ColumnWidth=20&&列的宽度(单位：字符个数)oExl.ActiveSheet.Columns(2).ColumnWidth=30oExl.ActiveSheet.Columns("C:BE").ColumnWidth=9.5oExl.ActiveSheet.Columns.AutoFit&&最适合的列宽oExl.ActiveSheet.Rows(1).RowHeight=1/0.035&&设置指定行的高度(单位：磅)(行高为1厘米，1磅=0.035厘米)oExl.ActiveSheet.Range('A1:B1').Interior.ColorIndex=nColorIndex &&设置底纹常用颜色值ColorIndex1 黑色2 白色3 红色4 鲜绿5 蓝色6 黄色7 粉红8 青绿9 深红10 绿色13 紫罗兰15 灰-25%34 浅青绿36 浅黄38 玫瑰红39 淡紫40 茶色42 水绿46 橙色53 褐色54 梅兰oExl.ActiveSheet.Range('P3:P4').Orientation=-4166 &&文字方向垂直&&同一工作簿中复制工作表副本oExl.WorkSheets(1).Copy(oExl.ActiveWorkBook.Sheets(1))&&不同工作簿oExl.Sheets(1).copy(oExl.Workbooks("BOOK1.XLS").Sheets(1))3、页面设置***********************************************oExl.ActiveSheet.PageSetup.TopMargin=2/0.035&&设置顶边距oExl.ActiveSheet.PageSetup.BottomMargin=1.5/0.035&&设置底边距oExl.ActiveSheet.PageSetup.LeftMargin=0&&设置左边距oExl.ActiveSheet.PageSetup.RightMargin=0&&设置右边距&&页眉页脚oExl.ActiveSheet.PageSetup.HeaderMargin=1.5/0.035&&设置页眉边距oExl.ActiveSheet.PageSetup.FooterMargin=0.8/0.035&&设置页脚边距oExl.ActiveSheet.PageSetup.LeftHeader="&9XXXXXX"oExl.ActiveSheet.PageSetup.CenterHeader=""oExl.ActiveSheet.PageSetup.RightHeader="&9 "oExl.ActiveSheet.PageSetup.LeftFooter=""oExl.ActiveSheet.PageSetup.CenterFooter="&9第&P页,共&N页"oExl.ActiveSheet.PageSetup.RightFooter="&9制表人:xxx "&P页序号&N页总数&D日期,&T时间&S删除线&Y下标&Z路径&F文件&A标签&G图片&X上标&""隶属,倾斜""&9九号字体&U下划线&B粗体&&左页眉图片设定oExl.ActiveSheet.PageSetup.LeftHeaderPicture.Filename="D:\2.jpg"oExl.ActiveSheet.PageSetup.LeftHeaderPicture.Height = 45oExl.ActiveSheet.PageSetup.LeftHeaderPicture.Width = 11.25oExl.ActiveSheet.PageSetup.LeftHeaderPicture.Brightness = 0.51oExl.ActiveSheet.PageSetup.LeftHeaderPicture.ColorType = msoPictureGrayscale oExl.ActiveSheet.PageSetup.LeftHeaderPicture.CropBottom = 28.35oExl.ActiveSheet.PageSetup.LeftHeaderPicture.CropLeft = 28.35oExl.ActiveSheet.PageSetup.LeftHeaderPicture.CropRight = 28.35oExl.ActiveSheet.PageSetup.LeftHeaderPicture.CropTop = 28.35oExl.ActiveSheet.PageSetup.CenterHorizontally=.T.&&设置页面水平居中oExl.ActiveSheet.PageSetup.CenterVertically=.T.&&设置页面垂直居中&&设置页面纸张大小oExl.ActiveSheet.PageSetup.Orientation=2&&页面方向2表示横向oExl.ActiveSheet.PageSetup.PaperSize=11纸张大小:9 A411 A570 A6oExl.ActiveSheet.PageSetup.Draft=.T.oExl.ActiveSheet.PageSetup.FirstPageNumber=1oExl.ActiveSheet.PageSetup.Order=1&&单色打印oExl.ActiveSheet.PageSetup.BlackAndWhite=.T.oExl.ActiveSheet.PageSetup.PrintComments=-4142oExl.ActiveSheet.PageSetup.Zoom=100 &&缩放100%oExl.ActiveSheet.PageSetup.Zoom=.F. &&禁用缩放oExl.ActiveSheet.PageSetup.FitToPagesWide=1 &&调整为一页宽oExl.ActiveSheet.PageSetup.FitToPagesTall=1 &&调整为一页高oExl.ActiveSheet.PageSetup.PrintHeadings=.T.oExl.ActiveSheet.PageSetup.PrintGridLines=.T.oExl.ActiveSheet.PageSetup.PrintErrors=oExl.ActiveSheet.PageSetup.PrintQuality=600 &&打印质量oExl.ActiveSheet.PageSetup.PrintTitleRows="$1:$3"&&固定标题行oExl.ActiveSheet.PageSetup.PrintTitleColumns="$A:$C"&&固定标题列oExl.ActiveSheet.PageSetup.PrintGridlines=.T.&&打印单元格网线*********************************************************&&打印oExl.ActiveSheet.PrintPreview&&打印预览工作表oExl.ActiveSheet.PageSetup.PrintArea="$A$1:$E$30" &&设置打印区域ActiveSheet.PageSetup.PrintArea = "" &&取消打印区域ActiveWorkbook.WebPagePreview &&网页预览oExl.ActiveSheet.PrintOut&&打印输出工作表5、VFP下直接调用EXCEL、WORD文件DECLARE INTEGER ShellExecute IN shell32.DLL INTEGER HWND,STRING,STRING lpszFile,STRING, STRING,INTEGERoperate="open"ShellExecute(0,operate,"C:\111.XLS",0,0,1)&&EXCEL文件ShellExecute(0,operate,"C:\111.DOC",0,0,1)&&WORD文件oExcel=Createobject("Excel.application")&&创建Excel对象**对象属性1、添加Sheets.Add before:=Sheets(1) '在第1工作表前添加新工作表Sheets.Add after:=Sheets(Sheets.Count) '在最后工作表后添加新工作表2、移动ActiveSheet.Move before:=Sheets(2) '将当前工作表移动至第2工作表之前3、命名="工作表名" '将当前工作表命名为"工作表名"4、删除可以用以下语句删除当前工作表。
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="&9"oExl.ActiveSheet.PageSetup.LeftFooter=""oExl.ActiveSheet.PageSetup.CenterFooter="&9第&P页,共&N页"oExl.ActiveSheet.PageSetup.RightFooter="&9制表人:'+代码+' 制表时间:"+TTOC(DATETIME())*------------------------------------------------------*&&整体格式设置oExl.ActiveSheet.Rows.Font.Size=9='宋体'oExl.ActiveSheet.Rows.RowHeight=0.5/0.035oExl.ActiveSheet.Rows.NumberFormatLocal="0.00_ ;[红色]-0.00"oExl.ActiveSheet.Rows.VerticalAlignment=2 &&单元格容垂直居中*------------------------------------------------------*&&获取最大行号和最大列号Local nMaxRow,nMaxColnMaxRow=edRange.Rows.CountnMaxCol=edRange.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值edRange.FormatConditions.DeleteedRange.FormatConditions.Add(1,3,'0')edRange.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.Wei ght=2 &&边框线*------------------------------------------------------*&&自动筛选IF !oExl.ActiveSheet.AutoFilterMode &&判断是否存在自动筛选oExl.ActiveSheet.Rows(3).Autofilter &&如果不存在自动筛选,则添加自动筛选ENDIF*------------------------------------------------------*&&冻结窗格oExl.ActiveSheet.Range('D4').SelectoExl.ActiveWindow.FreezePanes = .T.*------------------------------------------------------**------------------------------------------------------*&&分类汇总Local Arry(1),nMaxRow,nMaxColArry(1)=5nMaxRow=edRange.Rows.CountnMaxCol=edRange.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 &&激活工作表Sheet3 oExl.Worksheets(3).Activate &&激活(从左到右)第3个工作表oExl.WorkSheets.Count &&工作簿中工作表数oExl.DefaultSaveFormat=39 &&默认格式Excel 5.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 &&历史最大纪录数erName="XXXX" &&用户名oExl.StandardFont="宋体" &&标准字体oExl.StandardFontSize="12" &&标准字体大小oExl.DefaultFilePath="D:\XXXXXX\" &&默认工作目录oExl.EnableSound=False &&声音反馈oExl.RollZoom=False &&智能鼠标缩放oExl.TransitionMenuKey="/" &&Microsoft Office Excel菜单键oExl.ActiveWorkbook.Password="123"oExl.ActiveWorkbook.WritePassword="456"oExl.ActiveWorkbook.ReadOnlyRecommended=FalseoExl.ActiveWorkbook.SetPasswordEncryptionOptionsPasswordEncryptionProvider:="", _PasswordEncryptionAlgorithm:="OfficeStandard", PasswordEncryptionKeyLength:=40 _, PasswordEncryptionFileProperties:=FalseIF oExl.ActiveWorkbook.FileFormat==39 &&格式为Excel 5.0工作簿ENDIFoExl.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.ActiveWorkBook.SaveAs("d:\1.xls",39)oExl.ActiveWorkbook.saved=.T. &&放弃存盘oExl.ActiveWorkbook.Save &&存盘（自动存盘不提问）oExl.Workbooks.close &&关闭工作簿3、单元格的设置***********************************&&冻结窗格oExl.ActiveSheet.Range('D4').SelectoExl.ActiveWindow.FreezePanes = .T.&&条件格式设定oExl.ActiveSheet.Range('A1:H18').FormatConditions.Add(Type,Operator,Formula1,Fo rmula2)Type 类型(1单元格数值,2公式)Operator 操作符(1介于,2不介于,3等于,4不等于,5大于,6小于,7大于等于,8小于等于)Formula1 表达式1Formula2 表达式2,操作符为1介于或2不介于才有效&&条件格式例句:数值为0者,文字颜色白色edRange.FormatConditions.DeleteedRange.FormatConditions.Add(1,3,'0')edRange.FormatConditions(1).Font.ColorIndex=2&&文字白色&&编辑edRange.Copy &&拷贝整个工作表oExl.ActiveSheet.Range("A1:E2").Copy &&拷贝指定区域oExl.ActiveSheet.Columns("D:D").Delete &&删除列oExl.ActiveSheet.Columns("A:B").Delete &&删除列oExl.ActiveSheet.Rows(1).Insert &&在第1行之前插入一行oExl.ActiveSheet.Columns(2).Insert &&在第2列之前插入一列oExl.ActiveSheet.Cells(1,2).Value="ABTMC" &&给单元格赋值oExl.ActiveSheet.Cells(2,2).Value="&BBBB" &&给单元格赋值oExl.ActiveSheet.Cells(1,4).ClearContents &&清除单元格公式oExl.ActiveSheet.Rows(18).PageBreak=1 &&在第18行之前插入分页符oExl.ActiveSheet.Columns(4).PageBreak=0 &&在第4列之前删除分页符oExl.ActiveSheet.Rows(3).AutoFilter &&自动筛选oExl.ActiveSheet.Cells(1,1).Delete(-4159) &&删除单元格-4159 右边单元格左移-4162 下边单元格上移oExl.Selection.EntireRow.Delete &&所在行整行删除oExl.Selection.EntireColumn.Delete &&所在列整列删除oExl.ActiveSheet.Cells(1,1).Insert(-4142) &&插入单元格-4142 活动单元格下移-4161 活动单元格右移oExl.ActiveSheet.Selection.EntireRow.Insert &&插入行oExl.ActiveSheet.Selection.EntireColumn.Insert &&插入列&&选择性粘贴oExl.ActiveSheet.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=FalsePaste 粘贴数据:8列宽,11公式和数值格式,12数值与数值格式,-4104全部,-4123公式,-4163数值,-4122格式,Operation 运算符:-4142无,2加,3减,4乘,5除SkipBlanks 跳过空白单元:逻辑型Transpose 转置:逻辑型--------------------------------------------------&&排序成功例句:oExl.ActiveSheet.Range('A1:'+CHR(96+nMaxCol)+ALLTRIM(STR(nMaxRow))).Sort(oExl.A ctiveSheet.Range(CHR(96+ASCAN(Arry,'原币金额'))+'2'),2,,,,,,1)&&按"原币金额"所在列降序排列,有标题oExl.ActiveSheet.Rows('1:28').SortKey1:=Range("A2"), Order1:=xlDescending,Key2:=Range("B2"), Order2:=xlAscending,Key3:=Range("C2"), Order3:=xlDescending,Header:=xlYes,OrderCustom:=6,MatchCase:=True,Orientation:=xlTopToBottom,SortMethod:=xlStroke,DataOption1:=xlSortNormal,DataOption2:=xlSortNormal,DataOption3:=xlSortNormal参数说明:2 xlDescending降序1 xlAscenging升序标题行:0 xlGuess1 xlYes,2 xlNo1 xlTopToBottom排序方法:1 xlPinYin,2 xlStroke0 xlSortNormalKey1 排序字段:取值类型是围oExl.ActiveSheet.Range('B2')Order1 升序降序:xlAscending=1升序,xlDescending=2降序Header 有无标题:xlGuess=0自动判断,xlYes=1有,xlNo=2无orderCustom 排序顺序:1普通,6日一二三MatchCase 是否区分大小写:T区分,F不区分Orientation 1从头到底,2从左到右SortMethod 排序方法:xlPinYin=1拼音,xlStroke=2字母DataOption1 xlSortNormal=0正常DataOption2DateOption3&&分类汇总LOCAL Array(1)Array(1)=5oExl.ActiveSheet.Range('A1:G28').Subtotal(2,-4157,Array,.T.,.F.,.T.)参数列表:GroupBy:=2, Function:=xlSum, TotalList:=Array(3), Replace:=True, PageBreaks:=False, SummaryBelowData:=True参数说明:GroupBy 分组字段序号Function 汇总方式:-4157求和,-4112计数TotalList 汇总数据项:保存汇总列序号的数组引用Replace 替换当前分类汇总:逻辑型,默认TPageBreaks 每组数据分页:逻辑型,默认FSummaryBelowData 汇总结果显示在数据下方:逻辑型,默认T&&汇总结果表间切换oExl.ActiveSheet.Outline.ShowLevels RowLevels:=2oExl.ActiveSheet.Outline.ShowLevels RowLevels:=1oExl.ActiveSheet.Outline.ShowLevels RowLevels:=3&&数值格式设置oExl.ActiveSheet.Range("A").NumberFormatLocal="0.00" &&指定区域的数字显示格式&&对齐设置oExl.ActiveSheet.Range('A1:B1').HorizontalAlignment=3 &&水平方向2左对齐，3居中，4右对齐edRange.VerticalAlignment=2 &&垂直方向1靠上，2居中，3靠下oExl.ActiveSheet.Range('A1:B1').WrapText=.T. &&自动换行oExl.ActiveSheet.Range('A1:B1').Orientation = -4166 &&文字方向-4166 垂直向下oExl.ActiveSheet.Range('A1:B1').AddIndent = False &&缩进oExl.ActiveSheet.Range('A1:B1').IndentLevel = 2 &&缩进2字符oExl.ActiveSheet.Range('A1:B1').ShrinkToFit = .T. &&缩小以填充oExl.ActiveSheet.Range('A1:B1').ReadingOrder = -5002 &&文字阅读方向-5002 根据容-5003 从左到右oExl.selection.MergeCells=.T. &&合并单元oExl.selection.HorizontalAlignment=2 &&水平方向2左对齐，3居中，4右对齐oExl.selection.VerticalAlignment=2 &&垂直方向1靠上，2居中，3靠下&&字体设置oExl.ActiveSheet.ROWS(1)="黑体" &&设置字体oExl.ActiveSheet.ROWS(1).Font.FontStyle="常规"oExl.ActiveSheet.ROWS(1).Font.Size=14 &&设置字体大小oExl.ActiveSheet.ROWS(1).Font.Bold=.T. &&设置整行字体为粗体oExl.ActiveSheet.ROWS(1).Font.StrikeThrough=.T. &&删除线oExl.ActiveSheet.Cells(1,1).Font.Italic=.T. &&设置字体为斜体oExl.ActiveSheet.ROWS(1).Font.Superscript = .T. &&上标oExl.ActiveSheet.ROWS(1).Font.Subscript = .T. &&下标oExl.ActiveSheet.ROWS(1).Font.OutlineFont = FalseoExl.ActiveSheet.ROWS(1).Font.Shadow = FalseoExl.ActiveSheet.ROWS(1).Font.Underline = -4142 &&下划线oExl.ActiveSheet.ROWS(1).Font.ColorIndex = -4105 &&字体颜色自动设置oExl.ActiveSheet.ROWS(1).Font.Underline = 2 &&下划线类型下划线类型2 单下划线&&边框设置oExl.ActiveSheet.Range("b3:d3").Borders.Weight=2 &&指定边框线宽度其中Borders参数：1－左、2－右、3－顶、4－底、5－斜\、6－斜/；不带参数表示默认上下左右四个边框LineStyle值：1与7－细实、2－细虚、4－点虚、9－双细实线)oExl.ActiveSheet.Range("A1:BE29").Borders(1).LineStyle=1 &&设置四个边框线条的类型oExl.ActiveSheet.Range("A1:BE29").Borders(2).LineStyle=1 &&设置四个边框线条的类型oExl.ActiveSheet.Range("A1:BE29").Borders(3).LineStyle=1 &&设置四个边框线条的类型oExl.ActiveSheet.Range("A1:BE29").Borders(4).LineStyle=1 &&设置四个边框线条的类型&&1 xlContinuous&&2 xlThin&&5 xlDiagonalDown&&6 xlDiagonalUp&&10 xlEdgeRight&&12 xlInsideHorizontal&&行列设置oExl.ActiveSheet.Columns(1).ColumnWidth=20 &&列的宽度(单位：字符个数)oExl.ActiveSheet.Columns(2).ColumnWidth=30oExl.ActiveSheet.Columns("C:BE").ColumnWidth=9.5oExl.ActiveSheet.Columns.AutoFit &&最适合的列宽oExl.ActiveSheet.Rows(1).RowHeight=1/0.035 &&设置指定行的高度(单位：磅)(行高为1厘米，1磅=0.035厘米)oExl.ActiveSheet.Range('A1:B1').Interior.ColorIndex=nColorIndex &&设置底纹常用颜色值ColorIndex1 黑色2 白色3 红色4 鲜绿5 蓝色6 黄色7 粉红8 青绿9 深红10 绿色13 紫罗兰15 灰-25%34 浅青绿36 浅黄38 玫瑰红39 淡紫40 茶色42 水绿46 橙色53 褐色54 梅兰oExl.ActiveSheet.Range('P3:P4').Orientation=-4166 &&文字方向垂直&&同一工作簿中复制工作表副本oExl.WorkSheets(1).Copy(oExl.ActiveWorkBook.Sheets(1))&&不同工作簿oExl.Sheets(1).copy(oExl.Workbooks("BOOK1.XLS").Sheets(1))3、页面设置***********************************************oExl.ActiveSheet.PageSetup.TopMargin=2/0.035 &&设置顶边距oExl.ActiveSheet.PageSetup.BottomMargin=1.5/0.035 &&设置底边距oExl.ActiveSheet.PageSetup.LeftMargin=0 &&设置左边距oExl.ActiveSheet.PageSetup.RightMargin=0 &&设置右边距&&页眉页脚oExl.ActiveSheet.PageSetup.HeaderMargin=1.5/0.035 &&设置页眉边距oExl.ActiveSheet.PageSetup.FooterMargin=0.8/0.035 &&设置页脚边距oExl.ActiveSheet.PageSetup.LeftHeader="&9XXXXXX"oExl.ActiveSheet.PageSetup.CenterHeader=""oExl.ActiveSheet.PageSetup.RightHeader="&9 "oExl.ActiveSheet.PageSetup.LeftFooter=""oExl.ActiveSheet.PageSetup.CenterFooter="&9第&P页,共&N页"oExl.ActiveSheet.PageSetup.RightFooter="&9制表人:xxx "&P页序号&N页总数&D日期,&T时间&S删除线&Y下标&Z路径&F文件&A标签&G图片&X上标&""隶属,倾斜""&9九号字体&U下划线&B粗体&&左页眉图片设定oExl.ActiveSheet.PageSetup.LeftHeaderPicture.Filename="D:\2.jpg"oExl.ActiveSheet.PageSetup.LeftHeaderPicture.Height = 45oExl.ActiveSheet.PageSetup.LeftHeaderPicture.Width = 11.25oExl.ActiveSheet.PageSetup.LeftHeaderPicture.Brightness = 0.51oExl.ActiveSheet.PageSetup.LeftHeaderPicture.ColorType = msoPictureGrayscale oExl.ActiveSheet.PageSetup.LeftHeaderPicture.CropBottom = 28.35oExl.ActiveSheet.PageSetup.LeftHeaderPicture.CropLeft = 28.35oExl.ActiveSheet.PageSetup.LeftHeaderPicture.CropRight = 28.35oExl.ActiveSheet.PageSetup.LeftHeaderPicture.CropTop = 28.35oExl.ActiveSheet.PageSetup.CenterHorizontally=.T. &&设置页面水平居中oExl.ActiveSheet.PageSetup.CenterVertically=.T. &&设置页面垂直居中&&设置页面纸大小oExl.ActiveSheet.PageSetup.Orientation=2 &&页面方向2表示横向oExl.ActiveSheet.PageSetup.PaperSize=11纸大小:9 A411 A570 A6oExl.ActiveSheet.PageSetup.Draft=.T.oExl.ActiveSheet.PageSetup.FirstPageNumber=1oExl.ActiveSheet.PageSetup.Order=1&&单色打印oExl.ActiveSheet.PageSetup.BlackAndWhite=.T.oExl.ActiveSheet.PageSetup.PrintComments=-4142oExl.ActiveSheet.PageSetup.Zoom=100 &&缩放100%oExl.ActiveSheet.PageSetup.Zoom=.F. &&禁用缩放oExl.ActiveSheet.PageSetup.FitToPagesWide=1 &&调整为一页宽oExl.ActiveSheet.PageSetup.FitToPagesTall=1 &&调整为一页高oExl.ActiveSheet.PageSetup.PrintHeadings=.T.oExl.ActiveSheet.PageSetup.PrintGridLines=.T.oExl.ActiveSheet.PageSetup.PrintErrors=oExl.ActiveSheet.PageSetup.PrintQuality=600 &&打印质量oExl.ActiveSheet.PageSetup.PrintTitleRows="$1:$3" &&固定标题行oExl.ActiveSheet.PageSetup.PrintTitleColumns="$A:$C" &&固定标题列oExl.ActiveSheet.PageSetup.PrintGridlines=.T. &&打印单元格网线*********************************************************&&打印oExl.ActiveSheet.PrintPreview &&打印预览工作表oExl.ActiveSheet.PageSetup.PrintArea="$A$1:$E$30" &&设置打印区域ActiveSheet.PageSetup.PrintArea = "" &&取消打印区域ActiveWorkbook.WebPagePreview &&网页预览oExl.ActiveSheet.PrintOut &&打印输出工作表5、VFP下直接调用EXCEL、WORD文件DECLARE INTEGER ShellExecute IN shell32.DLL INTEGER HWND,STRING,STRING lpszFile,STRING, STRING,INTEGERoperate="open"ShellExecute(0,operate,"C:\111.XLS",0,0,1) &&EXCEL文件ShellExecute(0,operate,"C:\111.DOC",0,0,1) &&WORD文件oExcel=Createobject("Excel.application")&&创建Excel对象**对象属性1、添加Sheets.Add before:=Sheets(1) '在第1工作表前添加新工作表Sheets.Add after:=Sheets(Sheets.Count) '在最后工作表后添加新工作表2、移动ActiveSheet.Move before:=Sheets(2) '将当前工作表移动至第2工作表之前3、命名="工作表名" '将当前工作表命名为"工作表名"4、删除可以用以下语句删除当前工作表。
vfp与excel的结合
set safe offif used('qxjl')sele qxjlelsesele 0use qxjlendifzapxh=3tmpsheet = GetObject('','excel.sheet')XLApp = tmpsheet.applicationXLBOOK=XLApp.WorkBooks.open('c:\zlgl\qxjl.xls')XLApp.Worksheets('缺陷明细').ActivateXLSheet = XLApp.ActiveSheetxlapp.visible=.t.XH=1*!* DO WHILE .T.*!* DDA=XLSheet.Cells(XH,1).Value*!* IF LEN(ALLTRIM(DDA))>0*!* DDA='A4'+':G'+ALLTRIM(STR(XH))*!* dda1=XLSheet.range("&DDA").BORDERS(1).LINESTYLE*!* XLSheet.range("&DDA").BORDERS(1).LINESTYLE=1*!* XLSheet.range("&DDA").BORDERS(2).LINESTYLE=1*!* XLSheet.range("&DDA").BORDERS(3).LINESTYLE=1*!* XLSheet.range("&DDA").BORDERS(4).LINESTYLE=1*!* XH=XH+1*!* ELSE*!* EXIT*!* ENDIF*!* ENDDOdateyear1=left(alltrim(XLSheet.Cells(1,1).Value),4)datemonth1=substr(alltrim(XLSheet.Cells(1,1).Value),7,2)dda='C:\ZLGL\history\TJ'+DA TEYEAR1+DA TEMONTH1+'.XLS'if file("&dda")ts=messagebox(dateyear1+'年'+datemonth1+'月已经统计过了，还要重新统计吗？',256+64+4,'*****************友情提示************************')if ts=6delete file &ddasele lstjlocate for dateyear=dateyear1.and.datemonth=datemonth1deletepackendifelsereturnendifendifif type('XLSheet.Cells(2,5).Value')!='N'.or.XLSheet.Cells(2,5).Value=0messagebox('E2栏中没有填写罗莎线的生产量',256,'***********友情提示****************')returnendifif type('XLSheet.Cells(2,7).Value')!='N'.or.XLSheet.Cells(2,7).Value=0messagebox('G2栏中没有填写6790线的生产量',256,'***********友情提示****************')returnendifoldcaption=mand4.captionmand4.forecolor=16711680mand4.caption='正在处理，请稍候…………！'zsl=XLSheet.Cells(2,2).Value&&本月底盘数lssl=XLSheet.Cells(2,5).Value&&本月罗莎底盘数zksl=XLSheet.Cells(2,7).Value&&本月6790底盘数do while .t.dda=XLSheet.Cells(xh,1).Valueif len(alltrim(dda))=0exitendifxh=xh+1enddofor i1=4 to xh-1sele qxjlappen blankrepla qxjl with alltrim(XLSheet.Cells(i1,1).Value)repla zrr with alltrim(XLSheet.Cells(i1,2).Value)repla tsg with alltrim(XLSheet.Cells(i1,3).Value)repla ch with alltrim(XLSheet.Cells(i1,4).Value)repla jb with alltrim(XLSheet.Cells(i1,5).Value)repla zrlb with alltrim(XLSheet.Cells(i1,6).Value)repla zrrbz with alltrim(XLSheet.Cells(i1,8).Value)repla tsgbz with alltrim(XLSheet.Cells(i1,9).Value)sele qxjlcount to hfjl for alltrim(upper(jb))='AA'.or.alltrim(upper(jb))='AB'.or.alltrim(upper(jb))='BA'.or.alltrim(upper(jb)) ='BB'.or.alltrim(upper(jb))='CA'.or.alltrim(upper(jb))='AC'.or.alltrim(upper(jb))='BC'.or.alltrim(up per(jb))='CB'.or.alltrim(upper(jb))='CC'.or.len(alltrim(upper(jb)))=0COUNT TO BIG2 FOR LEN(ALLTRIM(JB))>2if hfjl<reccount().OR.BIG2>0messagebox('明细中存在不明缺陷类别，请更正后重新运行！',256,'**************友情提示****************')sele qxjlzapmand4.forecolor=0returnendifset safe offuse zrrbbzapsele 0use gwbbzapsele 0use zrrfszapsele 0use zrrcszapsele 0use gwfszapsele 0use gwcszapsele 0use ryb order zrrsele 0use qxdzb order jbsele 0use qxjlset relation to jb into qxdzbset relation to zrr into ryb addiset order to xwgwcount to a for len(alltrim(zrr))=0repla all zrr with tsg for len(alltrim(zrr))=0.and.len(alltrim(tsg))>0repla all gw with ryb.gw,xw with ryb.xw,i with 1,fs with qxdzb.fsset relation tocopy to bbfs for (upper(jb)='BB'.or.upper(jb)='CA'.or.upper(jb)='AA'.or.upper(jb)='AB'.or.upper(jb)='BA').and.at(' Z',upper(zrlb))>0sele rybset order to xwzrrcopy to xwzrrset order to xwgwcopy to xwgwsele 0use xwgwgo topscanxwmem=alltrim(xw)gwmem=alltrim(gw)sele qxjlcount to a for xw=xwmem.and.gw=gwmemsum fs to b for xw=xwmem.and.gw=gwmemcount to c for xw=xwmem.and.gw=gwmem.and.fs>2*按工位统计sele gwcsappen blankrepla i with a,xw with xwmem,gw with gwmemsele gwbbappen blankrepla i with c,xw with xwmem,gw with gwmemsele gwfsappen blankrepla i with b,xw with xwmem,gw with gwmemsele xwgwendscan*按责任人统计sele 0use xwzrrgo topscanxwmem=alltrim(xw)gwmem=alltrim(gw)zrrmem=alltrim(zrr)sele qxjlcount to a for xw=xwmem.and.zrr=zrrmem*sum fs to b for xw=xwmem.and.zrr=zrrmem.and.at('H',ch)=0.and.at('N',ch)=0.and.at('W',zrlb)=0.and.at('C',zrlb)= 0sum fs to b for xw=xwmem.and.zrr=zrrmem.and.at('Z',upper(zrlb))>0&&.and.at('H',upper(ch))=0.and.at('N',uppe r(ch))=0count to c for xw=xwmem.and.zrr=zrrmem.and.fs>2sele zrrcsappen blankrepla i with a,xw with xwmem,gw with gwmem,zrr with zrrmemsele zrrbbappen blankrepla i with c,xw with xwmem,gw with gwmem,zrr with zrrmemsele zrrfsappen blankrepla i with b,xw with xwmem,gw with gwmem,zrr with zrrmemsele xwzrrendscansele qxjlcount to asum fs to bcount to c for fs>2XLApp.Worksheets('月产量').ActivateXLSheet = XLApp.ActiveSheetXLSheet.Cells(1,1).Value=dateyear1+'年'+datemonth1+'月'+'公司质量统计'dda=val(datemonth1)+1XLSheet.Cells(3,dda).Value=zslXLSheet.Cells(4,dda).Value=aXLSheet.Cells(5,dda).Value=bXLSheet.Cells(6,dda).Value=cif dda<12for xh=dda+1 to 12XLSheet.Cells(3,xh).V alue=0XLSheet.Cells(4,xh).V alue=0XLSheet.Cells(5,xh).V alue=0XLSheet.Cells(6,xh).V alue=0endforendifxlSheet.ChartObjects("图表2").Activatexlchart=xlapp.activechartxlchart.SeriesCollection(1).Points(dda-1).border.linestyle=1xlchart.SeriesCollection(1).border.colorindex=1xlchart.SeriesCollection(1).Points(dda).border.linestyle=0xlchart.SeriesCollection(1).Points(dda-1).markersize=5xlSheet.ChartObjects("图表11").Activatexlchart=xlapp.activechartxlchart.SeriesCollection(1).Points(dda-1).border.linestyle=1xlchart.SeriesCollection(1).Points(dda).border.linestyle=0xlchart.SeriesCollection(1).Points(dda-1).markersize=5xlSheet.ChartObjects("图表8").Activatexlchart=xlapp.activechartxlchart.SeriesCollection(1).Points(dda-1).border.linestyle=1xlchart.SeriesCollection(1).Points(dda).border.linestyle=0xlchart.SeriesCollection(1).Points(dda-1).markersize=5xlSheet.ChartObjects("图表10").Activatexlchart=xlapp.activechartxlchart.SeriesCollection(1).Points(dda-1).border.linestyle=1xlchart.SeriesCollection(1).Points(dda).border.linestyle=0xlchart.SeriesCollection(1).Points(dda-1).markersize=5XLAPP.ACTIVEWORKBOOK.SA VED=.T.xwmc='6790线'XLApp.Worksheets('6790缺陷明细').ActivateXLSheet = XLApp.ActiveSheetxlsheet.range('a3:g300').deleteXLSheet.Cells(1,1).Value=dateyear1+'年'+datemonth1+'月'+'6790缺陷明细' if used('qxjlls')sele qxjllsuseendifif used('qxjl')sele qxjlelsesele 0use qxjlendifset order to xwgwzrrcopy to qxjlls for xw='6790线'use qxjllsgo topstartrec1=0startrec2=0ddadda1=''ddadda2=''scanxh1=recno()+2if XLSheet.Cells(xh1,1).mergecellsXLSheet.Cells(xh1,1).Horizontalalignment =2XLSheet.Cells(xh1,1).mergecells=.f.XLSheet.Cells(xh1,1).Horizontalalignment =4XLSheet.Cells(xh1,5).Horizontalalignment =4endifif ddadda1!=alltrim(gw).or.mod((xh1-3),64)=0XLSheet.Cells(xh1,1).Value=alltrim(gw)ddadda1=alltrim(gw)if startrec1>0DDA='A'+ALLTRIM(STR(STARTREC1))+':A'+ALLTRIM(STR(xh1-1)) XLSheet.RANGE("&DDA").mergeXLSheet.Cells(startrec1,1).Horizontalalignment =3XLSheet.Cells(startrec1,1).verticalalignment =2endifstartrec1=xh1endifif ddadda2!=alltrim(zrr).or.mod((xh1-3),64)=0XLSheet.Cells(xh1,3).Value=alltrim(zrr)ddadda2=alltrim(zrr)if startrec2>0DDA='C'+ALLTRIM(STR(STARTREC2))+':C'+ALLTRIM(STR(xh1-1)) XLSheet.RANGE("&DDA").mergeXLSheet.Cells(startrec2,3).Horizontalalignment =3XLSheet.Cells(startrec2,3).verticalalignment =2endifstartrec2=xh1endifXLSheet.Cells(xh1,2).Value=alltrim(qxjl)XLSheet.Cells(xh1,4).Value=alltrim(tsg)XLSheet.Cells(xh1,5).Value=alltrim(ch)XLSheet.Cells(xh1,6).Value=alltrim(jb)XLSheet.Cells(xh1,7).Value=alltrim(zrlb)DDA='A'+ALLTRIM(STR(recno()+2))+':g'+ALLTRIM(STR(recno()+2)) XLSheet.RANGE("&DDA").BORDERS(1).LINESTYLE=1XLSheet.range("&DDA").BORDERS(2).LINESTYLE=1XLSheet.RANGE("&DDA").BORDERS(3).LINESTYLE=1XLSheet.range("&DDA").BORDERS(4).LINESTYLE=1endscanif startrec1<xh1DDA='A'+ALLTRIM(STR(STARTREC1))+':A'+ALLTRIM(STR(xh1)) XLSheet.RANGE("&DDA").mergeendifXLSheet.Cells(startrec1,1).Horizontalalignment =3XLSheet.Cells(startrec1,1).verticalalignment =2if startrec2<xh1DDA='C'+ALLTRIM(STR(STARTREC2))+':C'+ALLTRIM(STR(xh1))XLSheet.RANGE("&DDA").mergeendifXLSheet.Cells(startrec2,3).Horizontalalignment =3XLSheet.Cells(startrec2,3).verticalalignment =2DDA='A'+ALLTRIM(STR(xh1+1))+':G'+ALLTRIM(STR(xh1+1))xlsheet.range("&dda").mergexlsheet.range("&dda").BORDERS(1).LINESTYLE=1xlsheet.range("&dda").BORDERS(2).LINESTYLE=1xlsheet.range("&dda").BORDERS(3).LINESTYLE=1xlsheet.range("&dda").BORDERS(4).LINESTYLE=1if used('qxjl')sele qxjlelsesele 0use qxjlendifset order to xwgwcount to a for alltrim(xw)=xwmc.and.fs>2b=round(((zksl-a)*100/zksl),2)xlsheet.cells(xh1+1,1).value=xwmc+'本月一次交检合格率：'+alltrim(str(b,6,2))+'%' XLSheet.Cells(xh1+1,1).Horizontalalignment =3XLSheet.Cells(xh1+1,1).verticalalignment =2*!* sum i to ddadda*!* DDA='A'+ALLTRIM(STR(reccount()+3))+':G'+ALLTRIM(STR(reccount()+3)) *!* XLSheet.RANGE("&DDA").BORDERS(1).LINESTYLE=1*!* XLSheet.RANGE("&DDA").BORDERS(2).LINESTYLE=1*!* XLSheet.RANGE("&DDA").BORDERS(3).LINESTYLE=1*!* XLSheet.RANGE("&DDA").BORDERS(4).LINESTYLE=1*!* XLSheet.RANGE("&DDA").merge*!* XLSheet.Cells(xh1,1).Horizontalalignment =3xwmc='罗莎线'XLApp.Worksheets('罗莎缺陷明细').ActivateXLSheet = XLApp.ActiveSheetxlsheet.range('a3:g300').deleteXLSheet.Cells(1,1).Value=dateyear1+'年'+datemonth1+'月'+'罗莎缺陷明细' if used('qxjlls')sele qxjllsuseendifif used('qxjl')sele qxjlelsesele 0use qxjlendifset order to xwgwzrrcopy to qxjlls for xw='罗莎线'use qxjllsgo topstartrec1=0startrec2=0ddadda1=''ddadda2=''scanxh1=recno()+2if XLSheet.Cells(xh1,1).mergecellsXLSheet.Cells(xh1,1).Horizontalalignment =2XLSheet.Cells(xh1,1).mergecells=.f.XLSheet.Cells(xh1,1).Horizontalalignment =4XLSheet.Cells(xh1,5).Horizontalalignment =4endifif ddadda1!=alltrim(gw).or.mod((xh1-3),64)=0XLSheet.Cells(xh1,1).Value=alltrim(gw)ddadda1=alltrim(gw)if startrec1>0DDA='A'+ALLTRIM(STR(STARTREC1))+':A'+ALLTRIM(STR(xh1-1)) XLSheet.RANGE("&DDA").mergeXLSheet.Cells(startrec1,1).Horizontalalignment =3XLSheet.Cells(startrec1,1).verticalalignment =2endifstartrec1=xh1endifif ddadda2!=alltrim(zrr).or.mod((xh1-3),64)=0XLSheet.Cells(xh1,3).Value=alltrim(zrr)ddadda2=alltrim(zrr)if startrec2>0DDA='C'+ALLTRIM(STR(STARTREC2))+':C'+ALLTRIM(STR(xh1-1)) XLSheet.RANGE("&DDA").mergeXLSheet.Cells(startrec2,3).Horizontalalignment =3XLSheet.Cells(startrec2,3).verticalalignment =2endifstartrec2=xh1endifXLSheet.Cells(xh1,2).Value=alltrim(qxjl)XLSheet.Cells(xh1,4).Value=alltrim(tsg)XLSheet.Cells(xh1,5).Value=alltrim(ch)XLSheet.Cells(xh1,6).Value=alltrim(jb)XLSheet.Cells(xh1,7).Value=alltrim(zrlb)DDA='A'+ALLTRIM(STR(recno()+2))+':g'+ALLTRIM(STR(recno()+2)) XLSheet.RANGE("&DDA").BORDERS(1).LINESTYLE=1 XLSheet.range("&DDA").BORDERS(2).LINESTYLE=1XLSheet.RANGE("&DDA").BORDERS(3).LINESTYLE=1 XLSheet.range("&DDA").BORDERS(4).LINESTYLE=1endscanif startrec1<xh1DDA='A'+ALLTRIM(STR(STARTREC1))+':A'+ALLTRIM(STR(xh1)) XLSheet.RANGE("&DDA").mergeendifXLSheet.Cells(startrec1,1).Horizontalalignment =3XLSheet.Cells(startrec1,1).verticalalignment =2if startrec2<xh1DDA='C'+ALLTRIM(STR(STARTREC2))+':C'+ALLTRIM(STR(xh1)) XLSheet.RANGE("&DDA").mergeendifXLSheet.Cells(startrec2,3).Horizontalalignment =3XLSheet.Cells(startrec2,3).verticalalignment =2dda='A'+ALLTRIM(STR(xh1+1))+':G'+ALLTRIM(STR(xh1+1))xlsheet.range("&dda").mergexlsheet.range("&dda").BORDERS(1).LINESTYLE=1xlsheet.range("&dda").BORDERS(2).LINESTYLE=1xlsheet.range("&dda").BORDERS(3).LINESTYLE=1xlsheet.range("&dda").BORDERS(4).LINESTYLE=1if used('qxjl')sele qxjlelsesele 0use qxjlendifset order to xwgwcount to a for alltrim(xw)=xwmc.and.fs>2b=round(((lssl-a)*100/lssl),2)xlsheet.cells(xh1+1,1).value=xwmc+'本月一次交检合格率：'+alltrim(str(b,6,2))+'%' XLSheet.Cells(xh1+1,1).Horizontalalignment =3XLSheet.Cells(xh1+1,1).verticalalignment =2*!* sum i to ddadda*!* DDA='A'+ALLTRIM(STR(reccount()+3))+':G'+ALLTRIM(STR(reccount()+3)) *!* XLSheet.RANGE("&DDA").BORDERS(1).LINESTYLE=1*!* XLSheet.RANGE("&DDA").BORDERS(2).LINESTYLE=1*!* XLSheet.RANGE("&DDA").BORDERS(3).LINESTYLE=1*!* XLSheet.RANGE("&DDA").BORDERS(4).LINESTYLE=1*!* XLSheet.RANGE("&DDA").merge*!* XLSheet.Cells(xh1,1).Horizontalalignment =3xwmc='综合类'XLApp.Worksheets('综合类缺陷明细').ActivateXLSheet = XLApp.ActiveSheetxlsheet.range('a3:g500').deleteXLSheet.Cells(1,1).Value=dateyear1+'年'+datemonth1+'月'+'综合类缺陷明细'if used('qxjlls')sele qxjllsuseendifif used('qxjl')sele qxjlelsesele 0use qxjlendifset order to xwgwzrrcopy to qxjlls for xw='综合类'use qxjllsgo topstartrec1=0startrec2=0ddadda1=''ddadda2=''scanxh1=recno()+2if XLSheet.Cells(xh1,1).mergecellsXLSheet.Cells(xh1,1).Horizontalalignment =2XLSheet.Cells(xh1,1).mergecells=.f.XLSheet.Cells(xh1,1).Horizontalalignment =4XLSheet.Cells(xh1,5).Horizontalalignment =4endifif ddadda1!=alltrim(gw).or.mod((xh1-3),64)=0XLSheet.Cells(xh1,1).Value=alltrim(gw)ddadda1=alltrim(gw)if startrec1>0DDA='A'+ALLTRIM(STR(STARTREC1))+':A'+ALLTRIM(STR(xh1-1)) XLSheet.RANGE("&DDA").mergeXLSheet.Cells(startrec1,1).Horizontalalignment =3XLSheet.Cells(startrec1,1).verticalalignment =2endifstartrec1=xh1endifif ddadda2!=alltrim(zrr).or.mod((xh1-3),64)=0XLSheet.Cells(xh1,3).Value=alltrim(zrr)ddadda2=alltrim(zrr)if startrec2>0DDA='C'+ALLTRIM(STR(STARTREC2))+':C'+ALLTRIM(STR(xh1-1)) XLSheet.RANGE("&DDA").mergeXLSheet.Cells(startrec2,3).Horizontalalignment =3XLSheet.Cells(startrec2,3).verticalalignment =2endifstartrec2=xh1endifXLSheet.Cells(xh1,2).Value=alltrim(qxjl)XLSheet.Cells(xh1,4).Value=alltrim(tsg)XLSheet.Cells(xh1,5).Value=alltrim(ch)XLSheet.Cells(xh1,6).Value=alltrim(jb)XLSheet.Cells(xh1,7).Value=alltrim(zrlb)DDA='A'+ALLTRIM(STR(recno()+2))+':g'+ALLTRIM(STR(recno()+2)) XLSheet.RANGE("&DDA").BORDERS(1).LINESTYLE=1XLSheet.range("&DDA").BORDERS(2).LINESTYLE=1XLSheet.RANGE("&DDA").BORDERS(3).LINESTYLE=1XLSheet.range("&DDA").BORDERS(4).LINESTYLE=1endscanif startrec1<xh1DDA='A'+ALLTRIM(STR(STARTREC1))+':A'+ALLTRIM(STR(xh1)) XLSheet.RANGE("&DDA").mergeendifXLSheet.Cells(startrec1,1).Horizontalalignment =3XLSheet.Cells(startrec1,1).verticalalignment =2if startrec2<xh1DDA='C'+ALLTRIM(STR(STARTREC2))+':C'+ALLTRIM(STR(xh1))XLSheet.RANGE("&DDA").mergeendifXLSheet.Cells(startrec2,3).Horizontalalignment =3XLSheet.Cells(startrec2,3).verticalalignment =2dda='A'+ALLTRIM(STR(xh1+1))+':G'+ALLTRIM(STR(xh1+1))xlsheet.range("&dda").mergexlsheet.range("&dda").BORDERS(1).LINESTYLE=1xlsheet.range("&dda").BORDERS(2).LINESTYLE=1xlsheet.range("&dda").BORDERS(3).LINESTYLE=1xlsheet.range("&dda").BORDERS(4).LINESTYLE=1if used('qxjl')sele qxjlelsesele 0use qxjlendifset order to xwgwcount to a for alltrim(xw)=xwmc.and.fs>2b=round(((zsl-a)*100/zsl),2)xlsheet.cells(xh1+1,1).value=xwmc+'本月一次交检合格率：'+alltrim(str(b,6,2))+'%' XLSheet.Cells(xh1+1,1).Horizontalalignment =3XLSheet.Cells(xh1+1,1).verticalalignment =2*!* sum i to ddadda*!* DDA='A'+ALLTRIM(STR(reccount()+3))+':G'+ALLTRIM(STR(reccount()+3))*!* XLSheet.RANGE("&DDA").BORDERS(1).LINESTYLE=1*!* XLSheet.RANGE("&DDA").BORDERS(2).LINESTYLE=1*!* XLSheet.RANGE("&DDA").BORDERS(3).LINESTYLE=1*!* XLSheet.RANGE("&DDA").BORDERS(4).LINESTYLE=1*!* XLSheet.RANGE("&DDA").merge*!* XLSheet.Cells(xh1,1).Horizontalalignment =3******************************************以上为公共数据处理模块**********************************************************************************6790数据处理模块****************************************xwmc='6790线'sele gwcscount to reccounts for alltrim(xw)=xwmcpublic sjcs(reccounts)public cs(4),fs(4),bl(4)XLApp.Worksheets('6790质量分析').ActivateXLSheet = XLApp.ActiveSheetIF USED('QXJL')SELE QXJLELSESELE 0USE QXJLENDIFsele qxjlcount to bbx for fs>2.and.alltrim(xw)=xwmccount to cs(1) for alltrim(xw)=xwmccount to cs(2) for at('Z',UPPER(zrlb))>0.and.alltrim(xw)=xwmccount to cs(3) for at('W',UPPER(zrlb))>0.and.alltrim(xw)=xwmccount to cs(4) for at('C',UPPER(zrlb))>0.and.alltrim(xw)=xwmcSUM FS to fs(1) for alltrim(xw)=xwmcSUM FS to fs(2) for at('Z',UPPER(zrlb))>0.and.alltrim(xw)=xwmcSUM FS to fs(3) for at('W',UPPER(zrlb))>0.and.alltrim(xw)=xwmcSUM FS to fs(4) for at('C',UPPER(zrlb))>0.and.alltrim(xw)=xwmcdda1=left(alltrim(XLSheet.Cells(1,1).Value),4)dda2=substr(alltrim(XLSheet.Cells(1,1).Value),7,2)XLSheet.Cells(1,1).Value=dateyear1+'年'+datemonth1+'月'+xwmc+'底盘质量缺陷分析'if dda1=dateyear1.and.dda2=datemonth1for xh1=0 to 3dda1=4*xh1+2dda2=xh1+1XLSheet.Cells(4,dda1).Value=cs(dda2)XLSheet.Cells(5,dda1).Value=fs(dda2)endforoutput4='('+ALLTRIM(STR(zksl))+')台'XLSheet.Cells(9,2).Value='本月份'+output4xh=11do while .t.dda=alltrim(XLSheet.Cells(xh,1).Value)if len(dda)=0exitendifsele gwcslocate for xw=xwmc.and.gw=ddaif !found()XLSheet.Cells(xh,2).V alue='没有找到此工位！'loopendifXLSheet.Cells(xh,2).V alue=round(i/zksl,4)xh=xh+1enddoelsefor xh1=0 to 3dda1=4*xh1+2dda2=xh1+1for xh=dda1+3 to dda1+1 step -1XLSheet.Cells(4,xh).V alue=XLSheet.Cells(4,xh-1).Value XLSheet.Cells(5,xh).V alue=XLSheet.Cells(5,xh-1).Value endforXLSheet.Cells(4,dda1).Value=cs(dda2)XLSheet.Cells(5,dda1).Value=fs(dda2)endforsta=at('(',alltrim(XLSheet.Cells(9,10).Value))enda=len(alltrim(XLSheet.Cells(9,10).Value))sta=enda-staoutput1=right(alltrim(XLSheet.Cells(9,10).Value),sta+1) sta=at('(',alltrim(XLSheet.Cells(9,6).Value))enda=len(alltrim(XLSheet.Cells(9,6).Value))sta=enda-staoutput2=right(alltrim(XLSheet.Cells(9,6).Value),sta+1)sta=at('(',alltrim(XLSheet.Cells(9,2).Value))enda=len(alltrim(XLSheet.Cells(9,2).Value))sta=enda-staoutput3=right(alltrim(XLSheet.Cells(9,2).Value),sta+1) output4='('+ALLTRIM(STR(zksl))+')台'XLSheet.Cells(9,14).V alue='前三月'+output1XLSheet.Cells(9,10).V alue='前二月'+output2XLSheet.Cells(9,6).Value='前一月'+output3XLSheet.Cells(9,2).Value='本月份'+output4xh=11do while .t.dda=alltrim(XLSheet.Cells(xh,1).Value)if len(dda)=0exitendifsele gwcslocate for xw=xwmc.and.gw=ddaif !found()XLSheet.Cells(xh,1).V alue='无此工位！'xh=xh+1loopendifdda=XLSheet.Cells(xh,14).V alue-XLSheet.Cells(xh,10).Value if type('dda')='C'dda=0endifdo casecase dda=0XLSheet.Cells(xh,16).Value='---'case dda<0XLSheet.Cells(xh,16).Value='↑'case dda>0XLSheet.Cells(xh,16).Value=' ↓'endcaseXLSheet.Cells(xh,14).Value=XLSheet.Cells(xh,10).Value XLSheet.Cells(xh,10).Value=XLSheet.Cells(xh,6).Value XLSheet.Cells(xh,6).V alue=XLSheet.Cells(xh,2).V alue XLSheet.Cells(xh,2).Value=round(i/zksl,4)xh=xh+1enddoendifxh=21do while .t.dda=alltrim(XLSheet.Cells(xh,1).Value)if len(dda)=0exitendifsele gwbbset order to xwgwsele gwfsset order to xwgwsele gwcsset relation to xw+gw into gwfsset relation to xw+gw into gwbb addirepla all gwcs.i1 with gwfs.i,gwcs.i2 with gwbb.i set relation tolocate for xw=xwmc.and.gw=ddaif !found()XLSheet.Cells(xh,1).V alue='无此工位！'xh=xh+1loopendifXLSheet.Cells(xh,2).V alue=iXLSheet.Cells(xh,3).V alue=i1XLSheet.Cells(xh,4).V alue=i2xh=xh+1enddoXLApp.Worksheets('6790按责任人统计').Activate XLSheet = XLApp.ActiveSheetFOR XH=1 TO 50XLSheet.ROWS(4).DELETEENDFORsele zrrcsset order to icopy to zrrcsls for xw=xwmcsele zrrfsset order to icopy to zrrfsls for xw=xwmcsele zrrbbset order to icopy to zrrbbls for xw=xwmcsele 0use zrrcslsxh1=1go topscanif recno()>1.and.XLSheet.Cells(recno()+2,5).Value!=i xh1=xh1+1endifXLSheet.Cells(recno()+3,1).Value=recno() XLSheet.Cells(recno()+3,2).Value=xh1XLSheet.Cells(recno()+3,3).Value=alltrim(zrr) XLSheet.Cells(recno()+3,4).Value=gwXLSheet.Cells(recno()+3,5).Value=iendscansele 0use zrrfslsxh1=1go topscanif recno()>1.and.XLSheet.Cells(recno()+2,11).Value!=i xh1=xh1+1endifXLSheet.Cells(recno()+3,7).Value=recno() XLSheet.Cells(recno()+3,8).Value=xh1XLSheet.Cells(recno()+3,9).Value=alltrim(zrr) XLSheet.Cells(recno()+3,10).Value=gwXLSheet.Cells(recno()+3,11).Value=iendscansele 0use zrrbblsxh1=1go topscanif recno()>1.and.XLSheet.Cells(recno()+2,17).Value!=i xh1=xh1+1endifXLSheet.Cells(recno()+3,13).Value=recno()XLSheet.Cells(recno()+3,14).Value=xh1XLSheet.Cells(recno()+3,15).Value=alltrim(zrr)XLSheet.Cells(recno()+3,16).Value=gwXLSheet.Cells(recno()+3,17).Value=iendscanDDA='A4'+':Q'+ALLTRIM(STR(reccount()+3))XLSheet.range("&DDA").BORDERS(1).LINESTYLE=1XLSheet.range("&DDA").BORDERS(2).LINESTYLE=1XLSheet.range("&DDA").BORDERS(3).LINESTYLE=1XLSheet.range("&DDA").BORDERS(4).LINESTYLE=1DDA='F2'+':F'+ALLTRIM(STR(reccount()+3))XLSheet.range("&DDA").MERGEDDA='L2'+':L'+ALLTRIM(STR(reccount()+3))XLSheet.range("&DDA").MERGE*!* XLSheet.ROWS(8).ROWHEIGHT=13*!* XLSheet.COLUMNS(5).COLUMNWIDTH=13*************罗莎线********************xwmc='罗莎线'sele gwcscount to reccounts for alltrim(xw)=xwmcpublic sjcs(reccounts)public cs(4),fs(4),bl(4)XLApp.Worksheets('罗莎质量分析').ActivateXLSheet = XLApp.ActiveSheetsele qxjlcount to bbx for fs>2.and.alltrim(xw)=xwmccount to cs(1) for alltrim(xw)=xwmccount to cs(2) for at('Z',UPPER(zrlb))>0.and.alltrim(xw)=xwmccount to cs(3) for at('W',UPPER(zrlb))>0.and.alltrim(xw)=xwmccount to cs(4) for at('C',UPPER(zrlb))>0.and.alltrim(xw)=xwmcSUM FS to fs(1) for alltrim(xw)=xwmcSUM FS to fs(2) for at('Z',UPPER(zrlb))>0.and.alltrim(xw)=xwmcSUM FS to fs(3) for at('W',UPPER(zrlb))>0.and.alltrim(xw)=xwmcSUM FS to fs(4) for at('C',UPPER(zrlb))>0.and.alltrim(xw)=xwmcdda1=left(alltrim(XLSheet.Cells(1,1).Value),4)dda2=substr(alltrim(XLSheet.Cells(1,1).Value),7,2)XLSheet.Cells(1,1).Value=dateyear1+'年'+datemonth1+'月'+xwmc+'底盘质量缺陷分析' if dda1=dateyear1.and.dda2=datemonth1for xh1=0 to 3dda1=4*xh1+2dda2=xh1+1XLSheet.Cells(4,dda1).Value=cs(dda2)XLSheet.Cells(5,dda1).Value=fs(dda2)endforoutput4='('+ALLTRIM(STR(lssl))+')台'XLSheet.Cells(9,2).Value='本月份'+output4xh=11do while .t.dda=alltrim(XLSheet.Cells(xh,1).Value)if len(dda)=0exitendifsele gwcslocate for xw=xwmc.and.gw=ddaif !found()XLSheet.Cells(xh,2).V alue='没有找到此工位！'loopendifXLSheet.Cells(xh,2).V alue=round(i/lssl,4)xh=xh+1enddoelsefor xh1=0 to 3dda1=4*xh1+2dda2=xh1+1for xh=dda1+3 to dda1+1 step -1XLSheet.Cells(4,xh).V alue=XLSheet.Cells(4,xh-1).Value XLSheet.Cells(5,xh).V alue=XLSheet.Cells(5,xh-1).Value endforXLSheet.Cells(4,dda1).Value=cs(dda2)XLSheet.Cells(5,dda1).Value=fs(dda2)endforsta=at('(',alltrim(XLSheet.Cells(9,10).Value))enda=len(alltrim(XLSheet.Cells(9,10).Value))sta=enda-staoutput1=right(alltrim(XLSheet.Cells(9,10).Value),sta+1) sta=at('(',alltrim(XLSheet.Cells(9,6).Value))enda=len(alltrim(XLSheet.Cells(9,6).Value))sta=enda-staoutput2=right(alltrim(XLSheet.Cells(9,6).Value),sta+1)sta=at('(',alltrim(XLSheet.Cells(9,2).Value))enda=len(alltrim(XLSheet.Cells(9,2).Value))sta=enda-staoutput3=right(alltrim(XLSheet.Cells(9,2).Value),sta+1) output4='('+ALLTRIM(STR(lssl))+')台'XLSheet.Cells(9,14).V alue='前三月'+output1XLSheet.Cells(9,10).V alue='前二月'+output2XLSheet.Cells(9,6).Value='前一月'+output3XLSheet.Cells(9,2).Value='本月份'+output4xh=11do while .t.dda=alltrim(XLSheet.Cells(xh,1).Value)if len(dda)=0exitendifsele gwcslocate for xw=xwmc.and.gw=ddaif !found()XLSheet.Cells(xh,1).V alue='无此工位！'xh=xh+1loopendifdda=XLSheet.Cells(xh,14).V alue-XLSheet.Cells(xh,10).Value if type('dda')='C'dda=0endifdo casecase dda=0XLSheet.Cells(xh,16).Value='---'case dda<0XLSheet.Cells(xh,16).Value='↑'case dda>0XLSheet.Cells(xh,16).Value=' ↓'endcaseXLSheet.Cells(xh,14).Value=XLSheet.Cells(xh,10).Value XLSheet.Cells(xh,10).Value=XLSheet.Cells(xh,6).Value XLSheet.Cells(xh,6).V alue=XLSheet.Cells(xh,2).V alue XLSheet.Cells(xh,2).Value=round(i/lssl,4)xh=xh+1enddoendif。
如何在vfp中调用excel实现图、表的制作（之二）
如何在vfp中调用excel实现图、表的制作（之二）言归正传。
接下来谈谈vfp调用excel生成统计图形的问题。
初学者可能以为这会是件很难的事情，其实还是很简单的，不必担心。
你最好用一点儿数据自己先在excel中试试如何生成一个统计图：打开Excel输入一组数据，例如从A1－A5，从上往下输入100,200,150,223,152这五个数据，然后用鼠标点击A1不放拖至A5，这样就把A1－A5选中了。
接着点击’图表向导’快捷键，在’图表类型’中选择’柱形图’，’子图表类型’就选择第一个，按’下一步’，在下一窗口中的’系列产生在：’选择’列’，因为我们的数据是从上往下按列摆放的。
接着按’下一步’，进入新的窗口，在’图表标题’栏输入:各生产车间产量柱形图；在分类(X)轴栏输入:车间；在’数值(Y)轴’输入:产量。
点击’下一步’，在’图表位置’窗口选中’将图表作为其中的对象插入’，点击’完成’按钮即可看见生成的柱形图形。
我们从vfp调用excel生成图表的角度来简单了解一下excel中的一些基本概念：一．对象层次：（由高到低）Application对象：它在excel对象结构中处于顶层。
形象地说，双击Excel图表就相当于创建了一个Application对象。
↓Workbooks对象：即工作簿对象(集)，启动Excel后点击’新建’或’打开’快捷键就相当于创建或打开了一个Workbook。
↓Worksheets对象：即工作表(集)。
启动Excel后工作表集会自动默认含有sheet1、sheet2和sheet3这三个工作表。
Charts对象：图表(集)，与Worksheets对象同级↓Cells对象：表格(集)，即我们看到的一个个格子。
Range对象：是格子的一个范围，如上面的A1－A5，与Cells对象同级二．VBA(Visual Basic Application)：它是一种面向对象的程序语言，内嵌于Excel，Excel的各项功能都可以直接使用VBA进行操作。
如何在vfp中调用excel实现图、表的制作（之八）
如何在vfp中调用excel实现图、表的制作（之八）如何在vfp中调用excel实现图、表的制作（之八）本讲纯属凑数，将搜集的一些可能用到的VFP调用Excel指令语句罗列出来供各位在编程时参考。
所幸没有稿酬可赚，也就没有骗稿费之嫌，各位看官也就不会责怪我。
呵呵。
1. 创建Excel对象eole=CreateObject(“Excel.application”)2. 添加新的工作簿eole.Workbooks.Add3. 打开指定工作簿eole.Workbooks.Open(“C:\demo.xls”)4. 设置第三个工作表为激活(作用)工作表eole.WorkSheets(“Sheet3”).Activate5. 是否显示Excel窗口eole.Visible=.T. (.T. 显示；.F.不显示)6.关闭工作簿eole.Workbooks.Close7.退出Exceleole.quit8.保存当前工作簿eole.ActiveWorkbook.Save9.当前工作簿另存为eole.ActiveWorkbook.SaveAs(“e:\temp\my1.xls”)10.退出时是否存盘eole.ActiveWorkbook.Saved=.F. (.F. 放弃存盘；.T.不放弃存盘)11.是否防止警告框的出现eole.DisplayAlerts=.F. (.F. 不出现；.T.出现)12.运行Excel宏eole.Application.Run(“宏1”)13.当前屏不显示零值eole.ActiveWindow.DisplayZeros=.F.14.更改Excel标题栏内容eole.Caption=”趋势分析表”15. 给单元格赋值eole.Cells(1,4).Value=3.141616.选定区域eole.ActiveSheet.Range(“A1:G10”).Select17.清除区域eole. ActiveSheet.Range(“A1:G10”).Clear18.合并单元格eole.ActiveSheet.Range(“A1:B2”).Merge19. 设置字体eole.ActiveSheet.Cells(2,1)=”隶书”20. 设置字号eole.ActiveSheet.Cells(2,1).font.size=1621.设置字体颜色eole.ActiveSheet.Cells(1,1).font.ColorIndex=322.字体设为斜体eole.ActiveSheet.Cells(1,1).font.Italic=.T.23.文字带下划线eole. ActiveSheet.Range(“A2:A2”).font.underline=.T.24.整列字体为粗体eole.ActiveSheet.Columns(2).font.bold=.T.25.设置单元格背景色eole. ActiveSheet.Cells(3,4).Interior.ColorIndex = 46 26.调整列宽eole.Range(“A1:H1”).ColumnWidth=12.527.调整指定列宽度eole.ActiveSheet.Column(2).ColumnWidth=2028.单元格文字居中eole.Cells(1,8).HorizontalAlignment=329.插行eole.ActiveSheet.Rows(1).Insert30.删行eole.ActiveSheet.Rows[8].Delete31.插列eole.ActiveSheet.Columns(2).Insert32.删列eole.Columns("D:D").Selecteole.Selection.Delete(-4159)33.删除指定工作表指定列eole.WorkSheets(“Sheet1”).Row(3).Delete34.调整行高eole.ActiveSheet.Rows(5).RowHeight=4035.块拷贝eole.Range(“A1:J3”).Selecteole.Selection.Copy或：eole.ActiveSheet.Range(“A1:E2”).Copy36.块粘贴eole.Range(“A1:A1”).Selecteole.ActiveSheet.Paste37.拷贝整个工作表ed.Range.Copy / eole.Cells.Copy 38.在指定工作表指定单元格粘贴eole.WorkSheets(“Sheet2”).Range(“A1”).PasteSpecial 39.单元格设置公式eole.Range(“A1”).Fornula=”=10*Rand()”40.清除单元格公式eole.ActiveSheet.Cells(1,4).ClearContents41.插入新工作表eole.Sheets.Add42.删除某个工作表eole.Sheets(“Sheet2”).Selecteole.ActiveWindow.SelectSheets.Delete43.返回当前工作簿工作表的总数eole.WorkSheets.Count44.返回Sheet1工作表的表名eole.WorkSheets(1).Name45.工作表更名eole.Sheets(2).Name=”损益表”46. 设置表格四个边框线条的类型eole.ActiveSheet.Range(“B3:D3”).Borders(2).Linestyl e=1 其中：borders参数：1－左 2－右 3－顶 4－底 5－斜/ 6－反斜\Linestyle参数：1和7－细实线；2－细虚线；4－点虚线；9：双细实线47. 设置表格边框线粗细eole.ActiveSheet.Range(“B3:D3”).Borders(2).Weight=348.在指定行插入分页符eole.WorkSheets(“Sheet1”).Rows(10).PageBreak=149.在指定列之前删除分页符eole.ActiveSheet.Columns(4).PageBreak=050.打印预览工作表eole.ActiveSheet.PrintPreview51.打印输出eole.ActiveSheet.Printout52.报表打印方向eole.ActiveSheet.PageSetup.Orientation=1 (1:垂直打印2:水平打印)53.设置页面纸张尺寸eole.ActiveSheet.PageSetup.PaperSize=954.打印单元格网线eole.ActiveSheet.PageSetup.PrintGridLines=.T.55.报表水平居中eole.ActiveSheet.PageSetup.CenterHorizontally=.T.56.报表垂直居中eole.ActiveSheet.PageSetup. CenterVertically=.T.57.设置报表页眉eole.ActiveSheet.PageSetup.CenterHeader=”报表1”58.设置报表页脚eole.ActiveSheet.PageSetup.CenterFoo ter=”第6页”59.设置报表左边距eole.ActiveSheet.PageSetup.LeftMargin=2/0.035 (2厘米)60.设置报表右边距eole.ActiveSheet.PageSetup.RightMargin=2/0.03561.设置报表顶边距eole.ActiveSheet.PageSetup.T opMargin=2/0.03562.设置报表底边距eole.ActiveSheet.PageSetup.BottomMargin=2/0.035关于VFP调用Excel 编程最后再罗嗦几句。
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="&9"oExl.ActiveSheet.PageSetup.LeftFooter=""oExl.ActiveSheet.PageSetup.CenterFooter="&9第&P页,共&N页"oExl.ActiveSheet.PageSetup.RightFooter="&9制表人:'+代码+' 制表时间:"+TTOC(DATETIME())*------------------------------------------------------*&&整体格式设置oExl.ActiveSheet.Rows.Font.Size=9='宋体'oExl.ActiveSheet.Rows.RowHeight=0.5/0.035oExl.ActiveSheet.Rows.NumberFormatLocal="0.00_ ;[红色]-0.00"oExl.ActiveSheet.Rows.VerticalAlignment=2 &&单元格内容垂直居中*------------------------------------------------------*&&获取最大行号和最大列号Local nMaxRow,nMaxColnMaxRow=edRange.Rows.CountnMaxCol=edRange.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值edRange.FormatConditions.DeleteedRange.FormatConditions.Add(1,3,'0')edRange.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.*------------------------------------------------------**------------------------------------------------------*&&分类汇总Local Arry(1),nMaxRow,nMaxColArry(1)=5nMaxRow=edRange.Rows.CountnMaxCol=edRange.Columns.CountoExl.ActiveSheet.Range('A3:'+chr(96+nMaxCol)+alltrim(str(nMaxRow))).Subtotal(2,-4157,@A rry,.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 &&激活工作表Sheet3 oExl.Worksheets(3).Activate &&激活(从左到右)第3个工作表oExl.WorkSheets.Count &&工作簿中工作表数oExl.DefaultSaveFormat=39 &&默认格式Excel 5.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 &&历史最大纪录数erName="XXXX" &&用户名oExl.StandardFont="宋体" &&标准字体oExl.StandardFontSize="12" &&标准字体大小oExl.DefaultFilePath="D:\XXXXXX\" &&默认工作目录oExl.EnableSound=False &&声音反馈oExl.RollZoom=False &&智能鼠标缩放oExl.TransitionMenuKey="/" &&Microsoft Office Excel菜单键oExl.ActiveWorkbook.Password="123"oExl.ActiveWorkbook.WritePassword="456"oExl.ActiveWorkbook.ReadOnlyRecommended=FalseoExl.ActiveWorkbook.SetPasswordEncryptionOptions PasswordEncryptionProvider:="", _PasswordEncryptionAlgorithm:="OfficeStandard", PasswordEncryptionKeyLength:=40 _, PasswordEncryptionFileProperties:=FalseIF oExl.ActiveWorkbook.FileFormat==39 &&格式为Excel 5.0工作簿ENDIFoExl.ActiveWorkBook.SaveAs(FileName,FileFormat,PassWord,WriteResPassWord,ReadOnlyR ecommended,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.ActiveWorkBook.SaveAs("d:\1.xls",39)oExl.ActiveWorkbook.saved=.T. &&放弃存盘oExl.ActiveWorkbook.Save &&存盘（自动存盘不提问）oExl.Workbooks.close &&关闭工作簿3、单元格的设置***********************************&&冻结窗格oExl.ActiveSheet.Range('D4').SelectoExl.ActiveWindow.FreezePanes = .T.&&条件格式设定oExl.ActiveSheet.Range('A1:H18').FormatConditions.Add(Type,Operator,Formula1,Formula2) Type 类型(1单元格数值,2公式)Operator 操作符(1介于,2不介于,3等于,4不等于,5大于,6小于,7大于等于,8小于等于) Formula1 表达式1Formula2 表达式2,操作符为1介于或2不介于才有效&&条件格式例句:数值为0者,文字颜色白色edRange.FormatConditions.DeleteedRange.FormatConditions.Add(1,3,'0')edRange.FormatConditions(1).Font.ColorIndex=2&&文字白色&&编辑edRange.Copy &&拷贝整个工作表oExl.ActiveSheet.Range("A1:E2").Copy &&拷贝指定区域oExl.ActiveSheet.Columns("D:D").Delete &&删除列oExl.ActiveSheet.Columns("A:B").Delete &&删除列oExl.ActiveSheet.Rows(1).Insert &&在第1行之前插入一行oExl.ActiveSheet.Columns(2).Insert &&在第2列之前插入一列oExl.ActiveSheet.Cells(1,2).Value="ABTMC" &&给单元格赋值oExl.ActiveSheet.Cells(2,2).Value="&BBBB" &&给单元格赋值oExl.ActiveSheet.Cells(1,4).ClearContents &&清除单元格公式oExl.ActiveSheet.Rows(18).PageBreak=1 &&在第18行之前插入分页符oExl.ActiveSheet.Columns(4).PageBreak=0 &&在第4列之前删除分页符oExl.ActiveSheet.Rows(3).AutoFilter &&自动筛选oExl.ActiveSheet.Cells(1,1).Delete(-4159) &&删除单元格-4159 右边单元格左移-4162 下边单元格上移oExl.Selection.EntireRow.Delete &&所在行整行删除oExl.Selection.EntireColumn.Delete &&所在列整列删除oExl.ActiveSheet.Cells(1,1).Insert(-4142) &&插入单元格-4142 活动单元格下移-4161 活动单元格右移oExl.ActiveSheet.Selection.EntireRow.Insert &&插入行oExl.ActiveSheet.Selection.EntireColumn.Insert &&插入列&&选择性粘贴oExl.ActiveSheet.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=FalsePaste 粘贴数据:8列宽,11公式和数值格式,12数值与数值格式,-4104全部,-4123公式,-4163数值,-4122格式,Operation 运算符:-4142无,2加,3减,4乘,5除SkipBlanks 跳过空白单元:逻辑型Transpose 转置:逻辑型--------------------------------------------------&&排序成功例句:oExl.ActiveSheet.Range('A1:'+CHR(96+nMaxCol)+ALLTRIM(STR(nMaxRow))).Sort(oExl.Active Sheet.Range(CHR(96+ASCAN(Arry,'原币金额'))+'2'),2,,,,,,1)&&按"原币金额"所在列降序排列,有标题oExl.ActiveSheet.Rows('1:28').SortKey1:=Range("A2"), Order1:=xlDescending,Key2:=Range("B2"), Order2:=xlAscending,Key3:=Range("C2"), Order3:=xlDescending,Header:=xlYes,OrderCustom:=6,MatchCase:=True,Orientation:=xlTopToBottom,SortMethod:=xlStroke,DataOption1:=xlSortNormal,DataOption2:=xlSortNormal,DataOption3:=xlSortNormal参数说明:2 xlDescending降序1 xlAscenging升序标题行:0 xlGuess1 xlYes,2 xlNo1 xlTopToBottom排序方法:1 xlPinYin,2 xlStroke0 xlSortNormalKey1 排序字段:取值类型是范围oExl.ActiveSheet.Range('B2')Order1 升序降序:xlAscending=1升序,xlDescending=2降序Header 有无标题:xlGuess=0自动判断,xlYes=1有,xlNo=2无orderCustom 排序顺序:1普通,6日一二三MatchCase 是否区分大小写:T区分,F不区分Orientation 1从头到底,2从左到右SortMethod 排序方法:xlPinYin=1拼音,xlStroke=2字母DataOption1 xlSortNormal=0正常DataOption2DateOption3&&分类汇总LOCAL Array(1)Array(1)=5oExl.ActiveSheet.Range('A1:G28').Subtotal(2,-4157,@Array,.T.,.F.,.T.)参数列表:GroupBy:=2, Function:=xlSum, TotalList:=Array(3), Replace:=True, PageBreaks:=False, SummaryBelowData:=True参数说明:GroupBy 分组字段序号Function 汇总方式:-4157求和,-4112计数TotalList 汇总数据项:保存汇总列序号的数组引用Replace 替换当前分类汇总:逻辑型,默认TPageBreaks 每组数据分页:逻辑型,默认FSummaryBelowData 汇总结果显示在数据下方:逻辑型,默认T&&汇总结果表间切换oExl.ActiveSheet.Outline.ShowLevels RowLevels:=2oExl.ActiveSheet.Outline.ShowLevels RowLevels:=1oExl.ActiveSheet.Outline.ShowLevels RowLevels:=3&&数值格式设置oExl.ActiveSheet.Range("A").NumberFormatLocal="0.00" &&指定区域内的数字显示格式&&对齐设置oExl.ActiveSheet.Range('A1:B1').HorizontalAlignment=3 &&水平方向2左对齐，3居中，4右对齐edRange.VerticalAlignment=2 &&垂直方向1靠上，2居中，3靠下oExl.ActiveSheet.Range('A1:B1').WrapText=.T. &&自动换行oExl.ActiveSheet.Range('A1:B1').Orientation = -4166 &&文字方向-4166 垂直向下oExl.ActiveSheet.Range('A1:B1').AddIndent = False &&缩进oExl.ActiveSheet.Range('A1:B1').IndentLevel = 2 &&缩进2字符oExl.ActiveSheet.Range('A1:B1').ShrinkToFit = .T. &&缩小以填充oExl.ActiveSheet.Range('A1:B1').ReadingOrder = -5002 &&文字阅读方向-5002 根据内容-5003 从左到右oExl.selection.MergeCells=.T. &&合并单元oExl.selection.HorizontalAlignment=2 &&水平方向2左对齐，3居中，4右对齐oExl.selection.VerticalAlignment=2 &&垂直方向1靠上，2居中，3靠下&&字体设置oExl.ActiveSheet.ROWS(1)="黑体" &&设置字体oExl.ActiveSheet.ROWS(1).Font.FontStyle="常规"oExl.ActiveSheet.ROWS(1).Font.Size=14 &&设置字体大小oExl.ActiveSheet.ROWS(1).Font.Bold=.T. &&设置整行字体为粗体oExl.ActiveSheet.ROWS(1).Font.StrikeThrough=.T. &&删除线oExl.ActiveSheet.Cells(1,1).Font.Italic=.T. &&设置字体为斜体oExl.ActiveSheet.ROWS(1).Font.Superscript = .T. &&上标oExl.ActiveSheet.ROWS(1).Font.Subscript = .T. &&下标oExl.ActiveSheet.ROWS(1).Font.OutlineFont = FalseoExl.ActiveSheet.ROWS(1).Font.Shadow = FalseoExl.ActiveSheet.ROWS(1).Font.Underline = -4142 &&下划线oExl.ActiveSheet.ROWS(1).Font.ColorIndex = -4105 &&字体颜色自动设置oExl.ActiveSheet.ROWS(1).Font.Underline = 2 &&下划线类型下划线类型2 单下划线&&边框设置oExl.ActiveSheet.Range("b3:d3").Borders.Weight=2 &&指定边框线宽度其中Borders参数：1－左、2－右、3－顶、4－底、5－斜\、6－斜/；不带参数表示默认上下左右四个边框LineStyle值：1与7－细实、2－细虚、4－点虚、9－双细实线)oExl.ActiveSheet.Range("A1:BE29").Borders(1).LineStyle=1 &&设置四个边框线条的类型oExl.ActiveSheet.Range("A1:BE29").Borders(2).LineStyle=1 &&设置四个边框线条的类型oExl.ActiveSheet.Range("A1:BE29").Borders(3).LineStyle=1 &&设置四个边框线条的类型oExl.ActiveSheet.Range("A1:BE29").Borders(4).LineStyle=1 &&设置四个边框线条的类型&&1 xlContinuous&&2 xlThin&&5 xlDiagonalDown&&6 xlDiagonalUp&&10 xlEdgeRight&&12 xlInsideHorizontal&&行列设置oExl.ActiveSheet.Columns(1).ColumnWidth=20 &&列的宽度(单位：字符个数)oExl.ActiveSheet.Columns(2).ColumnWidth=30oExl.ActiveSheet.Columns("C:BE").ColumnWidth=9.5oExl.ActiveSheet.Columns.AutoFit &&最适合的列宽oExl.ActiveSheet.Rows(1).RowHeight=1/0.035 &&设置指定行的高度(单位：磅)(行高为1厘米，1磅=0.035厘米)oExl.ActiveSheet.Range('A1:B1').Interior.ColorIndex=nColorIndex &&设置底纹常用颜色值ColorIndex1 黑色2 白色3 红色4 鲜绿5 蓝色6 黄色7 粉红8 青绿9 深红10 绿色13 紫罗兰15 灰-25%34 浅青绿36 浅黄38 玫瑰红39 淡紫40 茶色42 水绿46 橙色53 褐色54 梅兰oExl.ActiveSheet.Range('P3:P4').Orientation=-4166 &&文字方向垂直&&同一工作簿中复制工作表副本oExl.WorkSheets(1).Copy(oExl.ActiveWorkBook.Sheets(1))&&不同工作簿oExl.Sheets(1).copy(oExl.Workbooks("BOOK1.XLS").Sheets(1))3、页面设置***********************************************oExl.ActiveSheet.PageSetup.TopMargin=2/0.035 &&设置顶边距oExl.ActiveSheet.PageSetup.BottomMargin=1.5/0.035 &&设置底边距oExl.ActiveSheet.PageSetup.LeftMargin=0 &&设置左边距oExl.ActiveSheet.PageSetup.RightMargin=0 &&设置右边距&&页眉页脚oExl.ActiveSheet.PageSetup.HeaderMargin=1.5/0.035 &&设置页眉边距oExl.ActiveSheet.PageSetup.FooterMargin=0.8/0.035 &&设置页脚边距oExl.ActiveSheet.PageSetup.LeftHeader="&9XXXXXX"oExl.ActiveSheet.PageSetup.CenterHeader=""oExl.ActiveSheet.PageSetup.RightHeader="&9 "oExl.ActiveSheet.PageSetup.LeftFooter=""oExl.ActiveSheet.PageSetup.CenterFooter="&9第&P页,共&N页"oExl.ActiveSheet.PageSetup.RightFooter="&9制表人:xxx "&P页序号&N页总数&D日期,&T时间&S删除线&Y下标&Z路径&F文件&A标签&G图片&X上标&""隶属,倾斜""&9九号字体&U下划线&B粗体&&左页眉图片设定oExl.ActiveSheet.PageSetup.LeftHeaderPicture.Filename="D:\2.jpg"oExl.ActiveSheet.PageSetup.LeftHeaderPicture.Height = 45oExl.ActiveSheet.PageSetup.LeftHeaderPicture.Width = 11.25oExl.ActiveSheet.PageSetup.LeftHeaderPicture.Brightness = 0.51oExl.ActiveSheet.PageSetup.LeftHeaderPicture.ColorType = msoPictureGrayscale oExl.ActiveSheet.PageSetup.LeftHeaderPicture.CropBottom = 28.35oExl.ActiveSheet.PageSetup.LeftHeaderPicture.CropLeft = 28.35oExl.ActiveSheet.PageSetup.LeftHeaderPicture.CropRight = 28.35oExl.ActiveSheet.PageSetup.LeftHeaderPicture.CropTop = 28.35oExl.ActiveSheet.PageSetup.CenterHorizontally=.T. &&设置页面水平居中oExl.ActiveSheet.PageSetup.CenterVertically=.T. &&设置页面垂直居中&&设置页面纸张大小oExl.ActiveSheet.PageSetup.Orientation=2 &&页面方向2表示横向oExl.ActiveSheet.PageSetup.PaperSize=11纸张大小:9 A411 A570 A6oExl.ActiveSheet.PageSetup.Draft=.T.oExl.ActiveSheet.PageSetup.FirstPageNumber=1oExl.ActiveSheet.PageSetup.Order=1&&单色打印oExl.ActiveSheet.PageSetup.BlackAndWhite=.T.oExl.ActiveSheet.PageSetup.PrintComments=-4142oExl.ActiveSheet.PageSetup.Zoom=100 &&缩放100%oExl.ActiveSheet.PageSetup.Zoom=.F. &&禁用缩放oExl.ActiveSheet.PageSetup.FitToPagesWide=1 &&调整为一页宽oExl.ActiveSheet.PageSetup.FitToPagesTall=1 &&调整为一页高oExl.ActiveSheet.PageSetup.PrintHeadings=.T.oExl.ActiveSheet.PageSetup.PrintGridLines=.T.oExl.ActiveSheet.PageSetup.PrintErrors=oExl.ActiveSheet.PageSetup.PrintQuality=600 &&打印质量oExl.ActiveSheet.PageSetup.PrintTitleRows="$1:$3" &&固定标题行oExl.ActiveSheet.PageSetup.PrintTitleColumns="$A:$C" &&固定标题列oExl.ActiveSheet.PageSetup.PrintGridlines=.T. &&打印单元格网线*********************************************************&&打印oExl.ActiveSheet.PrintPreview &&打印预览工作表oExl.ActiveSheet.PageSetup.PrintArea="$A$1:$E$30" &&设置打印区域ActiveSheet.PageSetup.PrintArea = "" &&取消打印区域ActiveWorkbook.WebPagePreview &&网页预览oExl.ActiveSheet.PrintOut &&打印输出工作表5、VFP下直接调用EXCEL、WORD文件DECLARE INTEGER ShellExecute IN shell32.DLL INTEGER HWND,STRING,STRING lpszFile,STRING, STRING,INTEGERoperate="open"ShellExecute(0,operate,"C:\111.XLS",0,0,1) &&EXCEL文件ShellExecute(0,operate,"C:\111.DOC",0,0,1) &&WORD文件oExcel=Createobject("Excel.application")&&创建Excel对象**对象属性1、添加Sheets.Add before:=Sheets(1) '在第1工作表前添加新工作表Sheets.Add after:=Sheets(Sheets.Count) '在最后工作表后添加新工作表2、移动ActiveSheet.Move before:=Sheets(2) '将当前工作表移动至第2工作表之前3、命名="工作表名" '将当前工作表命名为"工作表名"4、删除可以用以下语句删除当前工作表。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
vfp直接读取EXCEL文件示例
vfp直接读取EXCEL文件的示例(转)。
M_File=GETFILE('xls','订单文件')
IF M_File=""
=MESSAGEBOX("请选择待导入的收订单文件！",0+48,"提示")
RETURN
ELSE
IF JUSTEXT(M_FILE)#"XLS" &&获得文件扩展名
=MESSAGEBOX("您选择的不是EXCEL文档！",0+48,"提示")
RETURN
ENDIF
ENDIF
USE 图书订单表
ZAP
myexcel=createobject('excel.application') &&创建一个对象
myexcel.visible=.f.&&不可见
bookexcel=myexcel.workbooks.open(M_File) &&打开指定文件
o_SheetName= &&获取当前激活工作表的名称UsedRange =bookexcel.worksheets(o_SheetName).UsedRange &&返回工作表中可使用的区域，UsedRange表的属性
o_rows=UsedRange.rows.count &&汇总行
o_cols=UsedRange.columns.count &&汇总列
IF o_rows<=1
=MESSAGEBOX("待导入数据行数太少，请检查！",0+16,"提示")
ELSE
FOR i=2 TO o_rows
m1=myexcel.cells(i,1).value &&书号
m2=myexcel.cells(i,2).value &&书名
m3=myexcel.cells(i,3).value &&出版社
m4=myexcel.cells(i,4).value &&作者
m5=myexcel.cells(i,5).value &&定价
m6=myexcel.cells(i,6).value &&数量
APPEND BLANK
REPLACE 书号WITH m1,书名WITH m2,出版社WITH m3,作者WITH m4,定价WITH m5,数量WITH m6
ENDFOR
ENDIF
myexcel.workbooks.close &&关闭工作区
myexcel.quit &&关闭excel
brow。