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.ActiveSheet.PageSetup.Orientation=2 &&页面方向:1纵向/2横向
*oExl.ActiveSheet.PageSetup.PaperSize=11 &&纸张尺寸:9-A4/11-A5
oExl.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.035
oExl.ActiveSheet.PageSetup.FooterMargin=0.5/0.035
oExl.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.035
oExl.ActiveSheet.Rows.NumberFormatLocal="0.00_ ;[红色]-0.00"
oExl.ActiveSheet.Rows.VerticalAlignment=2 &&单元格内容垂直居中
*------------------------------------------------------*
&&获取最大行号和最大列号
Local nMaxRow,nMaxCol
nMaxRow=edRange.Rows.Count
nMaxCol=edRange.Columns.Count
&&表头格式设置
oExl.ActiveSheet.Rows(1).Font.Size=16
oExl.ActiveSheet.Rows(1).Font.Bold=.T.
oExl.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:'+CHR(96+nMaxCol)+'2').Merge
*------------------------------------------------------*
&&条件格式隐藏0值
edRange.FormatConditions.Delete
edRange.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').Select
oExl.ActiveWindow.FreezePanes = .T.
*------------------------------------------------------*
*------------------------------------------------------*
&&分类汇总
Local Arry(1),nMaxRow,nMaxCol
Arry(1)=5
nMaxRow=edRange.Rows.Count
nMaxCol=edRange.Columns.Count
oExl.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 &&新建工作簿默认包含工作表个数
