[VFP] Automation 在 Excel 上的應用

常用語法：

1. loExcel.ActiveSheet..... 和 loExcel.... 皆代表對現在Excel 上擁有控制權的 Sheet 進行動作。
2. 指定 Sheet 的方法有三種：ActiveSheet 現在 Excel 上擁有控制權的 Sheet、Sheets(Var) 和 WorkSheets(Var) 為指定 Sheet 的兩種方式，Var 可以為 Sheet 名稱或是索引值。

-- 以下語法大多利用 ActiveSheet 來指定 Sheet，當然也可以利用 Sheet(Var)來達到相同效果

loExcel = CREATEOBJECT("Excel.Application") 
  -- 建立 Excel 物件

loExcel.SheetsInNewWorkBook = liSheetQty 
  -- 指定 Excel Sheet 數目

loExcel.WorkBooks.Add 
  -- 根據 SheetsInNewWorkBook 的設定值，把 Sheet 加入 Excel 內

lnSheetQty = loExcel.Sheets.Count 
  -- 查詢 Excel 內有多少個 Sheet

loExcel.Sheets(Var).NAME = SheetName
  -- Sheet 名稱更名

loExcel.ActiveSheet.Delete() 
loExcel.Sheets(Var).Delete() 
  -- 刪除 Sheet

loExcel.Visible = .T. 
  -- 顯示 Excel 物件，預設為不顯示

loExcel.WorkSheets(Var).Activate -- 書上列的
loExcel.Sheets(Var).Activate -- 自己常用
  -- 指定 Excel Sheet

loExcel.WorkBooks.OPEN((lcExcelPath)) 
  -- 打開 Excel 檔案 ； lcExcelPath 必須包含副檔（XLS、XLSX）
  
loExcel.ActiveSheet.Cells(lnRows,lnColumns) = Values
loExcel.Sheets(Var).Cells(lnRows,lnColumns) = Values
loExcel.Cells(lnRows,lnColumns).Value = Values
  -- 設定儲存格的資料，EX：Cells(3,5)代表第三行、第五列（欄）
  -- 前兩種方式沒有 Value 屬姓

loExcel.ActiveSheet.Rows(lnRows).FONT.SIZE = lnFontSize
loExcel.ActiveSheet.Columns(lnColumns).FONT.SIZE = lnFontSize
  -- 設定第 lnRows 行和第 lnColumns 列的字形大小

loExcel.ActiveSheet.Rows(lnRows).RowHeight = lnHeight
loExcel.ActiveSheet.Columns(lnColumns).ColumnWidth = lnWidth
  -- 設定第 lnRow 行的高度；設定第 lnColumns 的寬度

loExcel.ActiveSheet.Range(lcRange).Select
  -- 選定區域；lcRange 為儲存格範圍，EX：A1:D10
loExcel.SELECTION.Locked = .F.
  -- 解除選取範圍內的鎖定
loExcel.ActiveSheet.Protect(Password)
  -- 保護現有Sheet，Password 為保護密碼
loExcel.ActiveSheet.unProtect(Password)
  -- 取消保護現有Sheet，Password 為保護密碼

loExcel.ActiveSheet.Range(lcRange).Borders.LineStyle = nLineInt -- 設定上下左右的線條
loExcel.ActiveSheet.Range(lcRange).Border(nInt).LineStyle = nLineInt -- 依據 nInt 值設定哪一邊的線條
  -- lcRange 為儲存格範圍，EX：A1:D10
  -- nLineInt，1 和 7 為細實線、2 為細虛線、4 為點虛線、9 為雙細實線
  -- nInt 參數為：1 - 左、2 - 右、3 - 上、4 - 下、5 - 左斜、6 - 右斜

loExcel.ActiveSheet.Range(lcRange).Borders.Weight = nInt
  -- 設定邊框線粗細

loExcel.ActiveSheet.Cells(lnRows,lnColumns).VerticalAlignment = lnValue
loExcel.ActiveSheet.Rows(lnRows).VerticalAlignment = lnValue
loExcel.ActiveSheet.Columns(lnColumns).VerticalAlignment = lnValue
  -- 設定垂直對齊方式，lnValue 1 為靠上對齊、2為置中對齊、3為靠下對齊

loExcel.ActiveSheet.Cells(lnRow,lnColumns).HorizontalAlignment = lnValue
loExcel.ActiveSheet.Rows(lnRows).HorizontalAlignment = lnValue
loExcel.ActiveSheet.Columns(lnColumns).HorizontalAlignment = lnValue
  -- 設定水平對齊方式，lnValue 1 為向右對齊、2為向左對齊、3為置中對齊

loExcel.ActiveSheet.Rows(lnRows).Insert
  -- 在第 lnRows 行前插入一行

loExcel.ActiveSheet.PageSetup.PaperSize = nPrinterSetting 
  -- 設定 Sheet 的紙張大小，常見設定值： 8 為 A3、9 為 A4
  -- 查詢 Help 中的 PrtInfo() 可以查到全部紙張的 nPrinterSetting 

loExcel.ActiveSheet.PageSetup.Orientation = nPrinterSetting 
  -- 設定 Sheet 紙張方向， 1 為直向、2 為橫向

loExcel.ActiveSheet.Range(lcRange).Merge
  --合併資料格，lcRange 為儲存格範圍，EX：A1:D1

loExcel.ActiveSheet.PageSetup.TopMargin = 1 / 0.035 -- 上邊界
loExcel.ActiveSheet.PageSetup.BottomMargin = 1 / 0.035 -- 下邊界
loExcel.ActiveSheet.PageSetup.LeftMargin = 1 / 0.035 -- 左邊界
loExcel.ActiveSheet.PageSetup.RightMargin = 1 / 0.035 -- 右邊界
  -- 設定邊界，1 / 0.035 = 1 mm

loExcel.ActiveSheet.PageSetup.LeftHeader = lcContent -- 頁首左邊
loExcel.ActiveSheet.PageSetup.CenterHeader = lcContent -- 頁首中間
loExcel.ActiveSheet.PageSetup.RightHeader = lcContent -- 頁首右邊

loExcel.ActiveSheet.PageSetup.LeftFooter = lcContent -- 頁尾左邊
loExcel.ActiveSheet.PageSetup.CenterFooter = lcContent -- 頁尾中間
loExcel.ActiveSheet.PageSetup.RightFooter = lcContent -- 頁尾右邊

loExcel.ActiveSheet.Columns(lnColumns).NumberFormatlocal = Format
  --  Format 為欲設定格式
  -- "@" 設定為文字
  -- "G/通用格式" 設定為通用格式
  -- "#,##0_ " 設定資料為數值，且有千分位符號
  -- "[$-404]e/m/d;@"  西元年格式改為民國年格式
  -- "[>99999999]0000-000-000;000-000-000" 行動電話、呼叫器號碼

loExcel.ActiveSheet.Cells(lnRows,lnColumns).Formula = Formula
  -- Formula 設定公式
  -- "=SUM(A1:B1)" 加總
  -- "=AVERAGE(A1:B1)" 平均
  -- 簡易實務範例：
  -- loExcel.ActiveSheet.Cells(lnRows,lnColumns).Copy 選定某一格的公式
  -- loExcel.ActiveSheet.RANGE(lcRange).SELECT 目標儲存格
  -- loExcel.ActiveSheet.PASTE 進行公式複製

loExcel.SELECTION.WrapText = .T.
  -- 自動換列

lcPrintTitleRange = "lnROWStart:lnROWEnd" -- EX： 1:3
loExcel.ActiveSheet.RANGE(lcPrintTitleRange).SELECT
loExcel.ActiveSheet.PageSetup.PrintTitleRows = lcPrintTitleRange
  -- 設定列印標頭

loExcel.ActiveWindow.SplitColumn = lnCol
loExcel.ActiveWindow.SplitRow = lnRow
loExcel.ActiveWindow.FreezePanes = .T.
  -- 凍結視窗設定
  -- SplitColumn = lnCol 代表凍結前 N Column
  -- SplitRow = lnRow 代表凍結前 N Row

loExcel.ActiveSheet.COLUMNS(lnColumns).HIDDEN = Visible
loExcel.ActiveSheet.ROWS(lnRows).HIDDEN =  Visible
  -- 隱藏 Column 或 Row；. Visible  為 T.為隱藏、.F.為顯示（預設值）

loExcel.DisplayAlerts = .F.
  -- 關閉 Excel 警示，
  -- EX1：未儲存前，嘗試關閉 Excel，會詢問是否要儲存
  -- EX2：刪除 Sheet 時，會 DBCheck 是否要刪除 Sheet 
  -- EX3：合併儲存格會跳出警告

loExcel.ActiveWorkbook.Save
  -- 對 Excel 進行儲存

loExcel.ActiveWorkbook.SaveAs("D:\Excel.XLSX")
  -- 對 Excel 進行另存新檔

loExcel.ActiveWorkbook.SaveAs("D:\Excel.XLS" , FileFormat)
  -- 對 Excel 進行另存新檔並轉為其他格式
  -- FileFormat 為 56：97 - 2003 xls 格式
  -- FileFormat 為 6：csv 格式

loExcel.Quit
  -- 關閉 Excel

資料匯出

SELECT curData -- 欲匯出資料 Cursor
SCAN
   FOR lnCount = 1 TO FCOUNT("curData")
       lcField = EVALUATE(FIELD(lnCount)) -- 利用 Evaluate() 抓值
       -- 假設已事先打開 Excel 物件
       loExcel.Sheets(Var).cells(RECNO("curData"), lnCount) = lcField
       -- 或 loExcel.ActiveSheet.cells(RECNO("curData"), lnCount) = lcField
   ENDFOR

   SELECT curData
ENDSCAN

利用錄製巨集來了解 VBA 語法

以此為例子說明如何利用錄製巨集了解 VBA 語法：在 cell(1,1) 輸入手機號碼，並設定格式為"行動電話、呼叫器號碼"。

1. 開啟錄製巨集
2. 在 cell(1,1)內輸入手機號碼 0916123456，輸入完後在 cell(1,1) 內會顯示 916123456
3. 儲存格格式內，數值 Tag -> 類別（特殊）-> 類型（行動電話、呼叫器號碼）
4. 編輯已錄製的巨集

-- 巨集內容
Sub Macro1()
    ActiveCell.FormulaR1C1 = "916123456"
    Range("A1").Select
    Selection.NumberFormatLocal = "[>99999999]0000-000-000;000-000-000"
    Application.Goto Reference:="Macro1"
End Sub

HighLight 的 "[>99999999]0000-000-000;000-000-000"　就是行動電話、呼叫器號碼的格式。

參考資料：
• 如何利用 Excel 巨集，找到 VBA Code
• 如何利用 Excel 巨集，找到 VBA Code 變數值
• Wiki - Excel Automation
• Useful Excel Automation examples
• 在 Excel 上新增 Sheet
• 在 Excel 上設定公式
• 在 Excel 上插入圖片
• 指定開啟 Office 版本