[ABAP] Use OLE to Manipulate EXCEL

透過 SAP GUI 的功能直接操作 EXCEL

---

首先定義 Object :

TYPE-POOLS: ole2.

* OLE objects Declarations
DATA: w_excel       TYPE ole2_object,
      w_workbooks   TYPE ole2_object,
      w_workbook    TYPE ole2_object,
      w_worksheets  TYPE ole2_object,
      w_worksheet   TYPE ole2_object,
      w_columns     TYPE ole2_object,
      w_column_ent  TYPE ole2_object,
      w_cell        TYPE ole2_object,
      w_int         TYPE ole2_object,
      w_range       TYPE ole2_object,
      w_font        TYPE ole2_object,
      w_window      TYPE ole2_object.

接著開啟 Excel :

FORM excel_open.
  CREATE OBJECT w_excel 'EXCEL.APPLICATION'. "Create object for Excel
  IF p_back = 'X'.
    SET PROPERTY OF w_excel 'Visible' = 0.     " 0: In Background Mode
  ELSE.
    SET PROPERTY OF w_excel 'Visible' = 1.     " 1: In Foreground Mode
  ENDIF.
  SET PROPERTY OF w_excel 'SheetsInNewWorkbook' = 1.

  CALL METHOD OF w_excel 'Workbooks' = w_workbooks.
  CALL METHOD OF w_workbooks 'Add' = w_workbook. "Create a new Workbook
  CALL METHOD OF w_workbook 'WORKSHEETS' = w_worksheet
    EXPORTING
      #1 = gv_sheetcount.
  CALL METHOD OF w_worksheet 'ACTIVATE'.
  CALL METHOD OF w_workbook 'sheets' = w_worksheets.
ENDFORM.                    "excel_open

如果你要新增一個 Sheet 的話 ：

CALL METHOD OF w_worksheets 'ADD' = w_worksheet.
CALL METHOD OF w_worksheet 'ACTIVATE'.

如果你要修改 Sheet 底下頁籤的名稱的話：

SET PROPERTY OF w_worksheet 'NAME' = lv_sheetname.

如果你要修改 Cell 的底色並將字體加粗的話：

CALL METHOD OF w_excel 'RANGE' = w_range
  EXPORTING
    #1 = 'A1'
    #2 = 'G1'.
CALL METHOD OF w_range 'INTERIOR' = w_int.
SET PROPERTY OF w_int   'ColorIndex' = 6.
SET PROPERTY OF w_int   'Pattern'    = 1.

GET PROPERTY OF w_range 'Font'       = w_font.
SET PROPERTY OF w_font  'Bold'       = 1.

如果你想要將欄位屬性改成文字格式的話：

SET PROPERTY OF w_range 'NumberFormatLocal' = '@'. " Char

如果你想要將欄位屬性改成通用格式的話：

SET PROPERTY OF w_range 'NumberFormatLocal' = '#'. " General

如果你要把 Internal Table 的內容複製到 Excel 上的話，要先將 internal table 的內容轉移到另一個適合複製的 it_tab 上：

DATA: w_deli(1) TYPE c, "Delimiter
      w_hex     TYPE x.

FIELD-SYMBOLS: <sepflag>   TYPE ANY,
               <dyn_table> TYPE STANDARD TABLE,
               <dyn_wa>    TYPE ANY,
               <dyn_field> TYPE ANY. 

CONSTANTS: wl_c09(2)       TYPE n VALUE 09. 

* Separated Flag
  ASSIGN w_deli TO <sepflag> TYPE 'X'.
  w_hex      = wl_c09.
  <sepflag>  = w_hex.


* Insert Fields Data
  DESCRIBE TABLE: namtab      LINES lv_fieldscounter,
                  <dyn_table> LINES lv_datacounter.
  LOOP AT <dyn_table> INTO <dyn_wa>.
    CLEAR: wa_tab.
    DO lv_fieldscounter TIMES.
      ASSIGN COMPONENT sy-index OF STRUCTURE <dyn_wa> TO <dyn_field>.
      lv_chars = <dyn_field>.
      IF sy-index = 1.
        wa_tab = lv_chars.
      ELSE.
        CONCATENATE wa_tab lv_chars INTO wa_tab
            SEPARATED BY w_deli.
      ENDIF.
    ENDDO.
    APPEND wa_tab TO it_tab.
  ENDLOOP.

然後再把 it_tab 的內容複製到 Excel 上：

* Export the contents in the internal table to the clipboard
  CALL METHOD cl_gui_frontend_services=>clipboard_export
    IMPORTING
      data                 = it_tab
    CHANGING
      rc                   = w_rc
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.

* Paste the contents in the clipboard to the worksheet
  CALL METHOD OF w_worksheet 'Range' = w_range
    EXPORTING
      #1 = 'A1'.

  CALL METHOD OF w_worksheet 'PasteSpecial'
    EXPORTING
      #1 = 0 " 'xlClipboardFormatText'
      #2 = 0
      #3 = 0.

如果你要自動調整欄寬的話：

* Autofit the columns according to the contents
  CALL METHOD OF w_excel 'Columns' = w_columns.
  CALL METHOD OF w_columns 'AutoFit'.

如果你要凍結窗格的話：

* Freeze Panes
  CALL METHOD OF w_worksheet 'Range' = w_range
    EXPORTING
      #1 = 'B2'.
  CALL METHOD OF w_range 'Select'.
  GET PROPERTY OF w_excel 'ActiveWindow' = w_window.
  SET PROPERTY OF w_window 'FreezePanes' = 1.

最後將 Excel 存檔離開：

FORM excel_close USING value(lv_path).
  DATA: lv_filename TYPE string.

  CONCATENATE lv_path '\' sy-sysid sy-mandt '-' gt_img-group '-'
              sy-datum sy-uzeit '.xls'
      INTO lv_filename.
** Save the Excel file
  GET PROPERTY OF w_excel 'ActiveWorkbook' = w_workbook.
  CALL METHOD OF w_workbook 'SAVEAS'
    EXPORTING
      #1 = lv_filename.

*release Excel
  CALL METHOD OF w_excel 'QUIT'.
  FREE OBJECT w_excel.
  w_excel-handle = -1.

  FREE OBJECT: w_worksheet, w_excel.

ENDFORM.                    "excel_close

還有一些小細節要注意的，像是 Sheet 頁籤的名稱不可以超過 31 個字元啦，不可以含有 \[*?:/] 的字元等等。