ABAP_Excel 处理总结

来源：互联网发布：mac更改图标大小编辑：程序博客网时间：2024/06/05 02:50

ABAP_Excel 处理总结
1 调用原理
1.1 创建对象
首先定义OLE对象，创建OLE对象为excel类型。定义格式如下：CREATE OBJECT application 'excel.APPLICATION'.
然后通过excel对象application，创建与excel对应的workbook，sheet，range，cell等对象。
再次通过调用各个对象的方法及属性等，实现对excel的操作。
1.2 Excel宏
利用宏可以查看各个excel对象的方法和属性。宏的录制和利用参见文档：
(略)
2 方法用例
*&---------------------------------------------------------------------*
*& Report ZDEALEXCEL
*& Author ALEX
*& Date    2009.10
*&
*&---------------------------------------------------------------------*
*& 本程序总结了常用的ABAP对EXCEL的操作，包括如下子例程
*&       方法名                           方法说明
*& (1) create_excel                     创建excel
*& (2) insert_row                       插入行
*& (3) fill_cell                        填充单元格
*& (4) columnwidth                      调整列宽
*& (5) create_sheet                     创建sheet
*& (6) copy_sheet_all                   sheet复制
*& (7) copy_sheet_area                  sheet区域复制
*& (8) copy_cells                       复制单元格
*& (9) range_borders                    边框粗细
*& (10) row_color                       行颜色
*& (11) preview_excel                   预览
*& (12) free_object                     释放对象
*& (13) close_excel                     关闭excel
*& (14) download_excel_fromserver       从服务器下载excel模板（进入模板事务代码SMW0）
*& (15) open_excel_hide                 隐蔽打开excel
*& (16) open_excel                      打开excel
*& (17) open_excel_sheet                打开指定的excel的sheet
*& (18) open_excel_by_sheet             打开sheet
*& (19) select_range                    选择范围
*& (20) clear_data                      清除数据
*& (21) merge_cells                     合并单元格
*& (22) process_indcator                进度指示
*& (23) delete_row                      删除行
*& (24) add_comment                     添加注释
*& (25) hide_columns                    隐藏列
*& (26) delete_columns                  删除列
*& (27) hide_row                        隐藏行
* 保护工作区 lock_unlock_excel ：未完成
* 保护整个工作表 lock_sheet ：未完成
*& 本示例程序作为ABAP对EXCEL操作的参考,可以根据需要做适当的调整
*&---------------------------------------------------------------------*
REPORT zdealexcel.
TYPE-POOLS ole2.
TABLES sscrfields.

DEFINE m_message.
case sy-subrc.
    when '0'.
    when others.
      message e000(zmeg)
      with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
endcase.
END-OF-DEFINITION.

DATA: application TYPE ole2_object,       "excel object
      workbook    TYPE ole2_object,       "excel workbook objcet
      sheet       TYPE ole2_object,       "workbook sheet object
      columns     TYPE ole2_object,       "sheet col objcet
      rows        TYPE ole2_object,       "sheet row objcet
      range       TYPE ole2_object,       "range
      range1      TYPE ole2_object,                         "range1
      font        TYPE ole2_object,       "font
      cell        TYPE ole2_object,       "cell
      cell1        TYPE ole2_object,                        "cell1
      sheet1      TYPE ole2_object,       "workbook sheet object
      borders     TYPE ole2_object.       "borders

DATA: index TYPE i VALUE 0.

*定义模板下载到本地保存的全名称
DATA: p_down_file LIKE sapb-sappfad. "定义模板下载到本地保存的全名称

*演示excel操作
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN: PUSHBUTTON 01(12) but0 USER-COMMAND cli0,
PUSHBUTTON 20(15) but2 USER-COMMAND cli2.
SELECTION-SCREEN END OF LINE.

*导入数据块
SELECTION-SCREEN BEGIN OF BLOCK status2 WITH FRAME TITLE text-f02.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT 1(20) text-002.
PARAMETERS: p_file LIKE rlgrap-filename.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN END OF BLOCK status2.

*导出数据块
SELECTION-SCREEN BEGIN OF BLOCK status3 WITH FRAME TITLE text-f03 .
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT 1(20) text-003.
PARAMETERS: p_down LIKE rlgrap-filename.
SELECTION-SCREEN:
PUSHBUTTON 75(20) but1 USER-COMMAND cli1.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN END OF BLOCK status3.

INITIALIZATION.
CREATE OBJECT application 'excel.APPLICATION'.
but0 = '演示excel操作'. "初始化导出button显示名称
but1 = '导出'. "初始化导出button显示名称
but2 = '从模板导出excel'. "从模板导出excel

*-------------------------设置文件上载-------------------------
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
CALL FUNCTION 'WS_FILENAME_GET'
    EXPORTING
      def_path         = p_file
      mask             = ',*.*.'
      mode             = 'O'
      title            = text-f01
    IMPORTING
      filename         = p_file
    EXCEPTIONS
      inv_winsys       = 1
      no_batch         = 2
      selection_cancel = 3
      selection_error = 4
      OTHERS           = 5.
CHECK sy-subrc = 0 AND NOT p_file IS INITIAL.

*-------------------------设置文件下载-------------------------
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_down.
CALL FUNCTION 'WS_FILENAME_GET'
    EXPORTING
      def_path         = p_down
      mask             = ',*.xls,*.xls.'
      mode             = 'S'
      title            = text-f02
    IMPORTING
      filename         = p_down
    EXCEPTIONS
      inv_winsys       = 1
      no_batch         = 2
      selection_cancel = 3
      selection_error = 4
      OTHERS           = 5.
CHECK sy-subrc = 0 AND NOT p_down IS INITIAL.

*--------------------------------------------------------------
AT SELECTION-SCREEN.
IF sscrfields-ucomm EQ 'CLI1'.
    IF p_down IS INITIAL.
      MESSAGE e804(zdev).
    ELSE.
      p_down_file = p_down.
      PERFORM download_excel_fromserver USING 'ZHRI0004' p_down_file .            "下载模板到本地
      MESSAGE i600(zdev) WITH '模板导出成功'.
    ENDIF.
ELSEIF sscrfields-ucomm EQ 'ONLI'.
    IF p_file IS INITIAL.
      MESSAGE e803(zdev).
    ENDIF.
ELSEIF sscrfields-ucomm EQ 'CLI0'.
    PERFORM demo_forms.
ELSEIF sscrfields-ucomm EQ 'CLI2'.
    PERFORM demo_forms_server USING p_file.
ENDIF.

START-OF-SELECTION.
PERFORM open_excel_hide USING p_file.
PERFORM close_excel.
PERFORM open_excel USING p_file.
PERFORM close_excel.
PERFORM open_excel_sheet USING 'sheet2' p_file.

DO 10 TIMES.
    PERFORM insert_row USING 1.
    PERFORM fill_cell USING 1 1 0 'e'.
    PERFORM fill_cell USING 1 2 0 'f'.
    PERFORM fill_cell USING 1 3 0 'g'.
    index = index + 1.
ENDDO.

PERFORM close_excel.
PERFORM free_object.

*&---------------------------------------------------------------------*
*&      Form demo_forms_server
*&---------------------------------------------------------------------*
*       从SMW0下载excel模板
*----------------------------------------------------------------------*
*      -->P_FILE    excel本地路径
*----------------------------------------------------------------------*
FORM demo_forms_server USING p_file.
p_down_file = p_file.

PERFORM process_indcator USING '程序正在下载模板' 0 .
PERFORM download_excel_fromserver USING 'ZHRI0004' p_down_file .

PERFORM process_indcator USING '程序正在初始化OLE控件' 0 .

PERFORM process_indcator USING '程序正在打开excel文档' 0 .

PERFORM open_excel_sheet USING '处分处罚信息批导入' p_file.

DO 10 TIMES.
    PERFORM insert_row USING 5.
    PERFORM fill_cell USING 5 1 0 'e'.
    PERFORM fill_cell USING 5 2 0 'f'.
    PERFORM fill_cell USING 5 3 0 'g'.
    index = index + 1.
ENDDO.

PERFORM clear_data USING 1 6 2 7.
PERFORM merge_cells USING 1 6 2 7.
PERFORM add_comment USING 8 1 'OK'.
PERFORM close_excel.
PERFORM free_object.

ENDFORM. "demo_forms_server

*&---------------------------------------------------------------------*
*& Form demo_forms
*&---------------------------------------------------------------------*
* 演示用form
*----------------------------------------------------------------------*
FORM demo_forms.
PERFORM create_excel.

DO 10 TIMES.
    PERFORM insert_row USING 1.
    PERFORM fill_cell USING 1 1 0 'a'.
    PERFORM fill_cell USING 1 2 0 'b'.
    PERFORM fill_cell USING 1 3 0 'c'.
    index = index + 1.
ENDDO.

PERFORM columnwidth USING 'A:C' '' .
PERFORM create_sheet.
PERFORM create_sheet.
PERFORM copy_sheet_all.
PERFORM copy_sheet_area.
PERFORM copy_cells.
PERFORM range_borders.
PERFORM row_color USING 1.

PERFORM preview_excel.
PERFORM del_sheet USING 'sheet2'.
PERFORM free_object.
ENDFORM. "demo_forms

*&---------------------------------------------------------------------*
*&    (1)   Form create_excel
*&---------------------------------------------------------------------*
*       创建excel
*----------------------------------------------------------------------*
FORM create_excel.
IF sy-subrc <> 0.
    MESSAGE 'EXCEL ERROR' TYPE 'S' DISPLAY LIKE 'E'.
    STOP.
ENDIF.
CALL METHOD OF application 'WORKBOOKS' = workbook.
SET PROPERTY OF application 'VISIBLE' = 1.
SET PROPERTY OF application 'SHEETSINNEWWORKBOOK' = 1.
CALL METHOD OF workbook 'ADD' = sheet.
CALL METHOD OF sheet 'ACTIVE'.
ENDFORM.                    "create_excel

*&---------------------------------------------------------------------*
*&   (2)   Form insert_row
*&---------------------------------------------------------------------*
*       根据行号插入一行
*----------------------------------------------------------------------*
*      -->I_ROW      行号
*----------------------------------------------------------------------*
FORM insert_row USING i_row.
CALL METHOD OF application 'ROWS' = rows
    EXPORTING
    #1 = i_row.
CALL METHOD OF rows 'INSERT'.
ENDFORM.                    "insert_row

*&---------------------------------------------------------------------*
*&    (3) Form fill_cell
*&---------------------------------------------------------------------*
*       填充单元格
*----------------------------------------------------------------------*
*      -->I_ROW      行号
*      -->I_COL      列号
*      -->BOLD       是否加粗
*      -->P_VALUE    值
*----------------------------------------------------------------------*
FORM fill_cell USING i_row i_col bold p_value.
CALL METHOD OF application 'CELLS' = cell
    EXPORTING
    #1 = i_row
    #2 = i_col.
SET PROPERTY OF cell 'VALUE' = p_value.
SET PROPERTY OF cell 'HORIZONTALALIGNMENT' = 2.

GET PROPERTY OF cell 'FONT' = font.
SET PROPERTY OF font 'BOLD' = bold.
SET PROPERTY OF font 'COLORINDEX' = 41.

CALL METHOD OF cell 'BORDERS' = borders
    EXPORTING
    #1 = '2'.
SET PROPERTY OF borders 'LINESTYLE' = '0'.
SET PROPERTY OF borders 'WEIGHT' = 0.
ENDFORM.                    "fill_cell

*&---------------------------------------------------------------------*
*&   (4)   Form columnwidth
*&---------------------------------------------------------------------*
*       设置列宽为自动或是固定值
*----------------------------------------------------------------------*
*      -->COLNUM     列号,可以为单个列或是多个列
*      -->WIDTH      列宽值
*----------------------------------------------------------------------*
FORM columnwidth USING colnum width.
CALL METHOD OF application 'COLUMNS' = columns
    EXPORTING
    #1 = colnum.
IF width = ''.

    CALL METHOD OF columns 'AutoFit'.
ELSE.
    SET PROPERTY OF columns 'COLUMNWIDTH' = width.
ENDIF.
ENDFORM.                    "columnwidth

*&---------------------------------------------------------------------*
*&   (5)   Form create_sheet
*&---------------------------------------------------------------------*
*       创建sheet
*----------------------------------------------------------------------*
FORM create_sheet.
CALL METHOD OF application 'sheets' = sheet.
CALL METHOD OF sheet 'ADD'.
ENDFORM.                    "create_sheet

*&---------------------------------------------------------------------*
*& (6)    Form copy_sheet_all
*&---------------------------------------------------------------------*
*       复制sheet全部内容到另一个sheet
*----------------------------------------------------------------------*
FORM copy_sheet_all.
CALL METHOD OF application 'Sheets' = sheet
    EXPORTING
    #1 = 'Sheet1'.
CALL METHOD OF sheet 'Cells' = cell.
CALL METHOD OF cell 'copy'.

CALL METHOD OF application 'Sheets' = sheet
    EXPORTING
    #1 = 'Sheet3'.
CALL METHOD OF application 'Range' = range
    EXPORTING
    #1 = 'A1'.
CALL METHOD OF range 'PasteSpecial'.

ENDFORM. "copy_sheet_all

*&---------------------------------------------------------------------*
*& (7)    Form copy_sheet_area
*&---------------------------------------------------------------------*
*       复制sheet部分内容到另一个sheet
*----------------------------------------------------------------------*
FORM copy_sheet_area.
CALL METHOD OF application 'Sheets' = sheet
    EXPORTING
    #1 = 'Sheet1'.
CALL METHOD OF sheet 'Range' = range
    EXPORTING
    #1 = 'A1'
    #2 = 'B10'.
CALL METHOD OF range 'copy'.

CALL METHOD OF application 'Sheets' = sheet1
    EXPORTING
    #1 = 'Sheet2'.
CALL METHOD OF sheet1 'Range' = range1
    EXPORTING
    #1 = 'A1'
    #2 = 'B10'.
CALL METHOD OF range1 'PasteSpecial'.

ENDFORM. "copy_sheet_area

*&---------------------------------------------------------------------*
*&   (8)   Form copy_cells
*&---------------------------------------------------------------------*
*       在一个sheet内复制单元格
*----------------------------------------------------------------------*
FORM copy_cells.
CALL METHOD OF application 'Sheets' = sheet
    EXPORTING
    #1 = 'Sheet1'.
CALL METHOD OF sheet 'Range' = range
    EXPORTING
    #1 = 'A1'
    #2 = 'B10'.
CALL METHOD OF range 'copy'.

CALL METHOD OF sheet 'Range' = range1
    EXPORTING
    #1 = 'A12'
    #2 = 'B22'.
CALL METHOD OF range1 'PasteSpecial'.

ENDFORM. "copy_cells

*&---------------------------------------------------------------------*
*& (9)    Form range_borders
*&---------------------------------------------------------------------*
*       指定range加边框
*----------------------------------------------------------------------*
FORM range_borders.
CALL METHOD OF application 'Range' = range
    EXPORTING
    #1 = 'A1'
    #2 = 'B10'.
CALL METHOD OF range 'BORDERS' = borders.
SET PROPERTY OF borders 'LINESTYLE' = 1.
SET PROPERTY OF borders 'WEIGHT' = 10.

ENDFORM. "range_borders

*&---------------------------------------------------------------------*
*& (10)    Form row_color
*&---------------------------------------------------------------------*
*       设定行背景色
*----------------------------------------------------------------------*
*      -->I_ROW      行号
*----------------------------------------------------------------------*
FORM row_color USING i_row.
CALL METHOD OF application 'ROWS' = rows
    EXPORTING
    #1 = i_row.
GET PROPERTY OF rows 'Interior' = font.
SET PROPERTY OF font 'ColorIndex' = '10'.
ENDFORM.                    "row_color

*&---------------------------------------------------------------------*
*& (11)    Form preview_excel
*&---------------------------------------------------------------------*
*       预览excel
*----------------------------------------------------------------------*
FORM preview_excel.
SET PROPERTY OF application 'VISIBLE' = 1.
CALL METHOD OF sheet 'PRINTPREVIEW'.
* m_message.
ENDFORM.                    "preview_excel

*&---------------------------------------------------------------------*
*& (12)    Form free_object
*&---------------------------------------------------------------------*
*       释放object
*----------------------------------------------------------------------*
FORM free_object.
FREE OBJECT font.
FREE OBJECT range.
FREE OBJECT range1.
FREE OBJECT columns.
FREE OBJECT rows.
FREE OBJECT cell.
FREE OBJECT cell1.
FREE OBJECT sheet1.
FREE OBJECT sheet.
FREE OBJECT workbook.
FREE OBJECT application.
ENDFORM.                    "free_object

*&---------------------------------------------------------------------*
*&   (13)   Form close_excel
*&---------------------------------------------------------------------*
*       关闭excel并保存
*----------------------------------------------------------------------*
FORM close_excel.
CALL METHOD OF workbook 'Save'.
CALL METHOD OF workbook 'Close'
    EXPORTING
    #1 = 0.
CALL METHOD OF application 'Quit'.
ENDFORM.                    "close_excel

*&---------------------------------------------------------------------*
*&   (14)   Form download_excel_fromserver
*&---------------------------------------------------------------------*
*       从服务器下载excel模板
*----------------------------------------------------------------------*
*      -->P_OBJID    模板名称
*      -->P_DEST     存档路径
*----------------------------------------------------------------------*
FORM download_excel_fromserver
     USING p_objid LIKE wwwdatatab-objid
           p_dest LIKE sapb-sappfad.

DATA: lo_objdata LIKE wwwdatatab,
        lo_mime LIKE w3mime,
        ls_destination LIKE rlgrap-filename,
        ls_objnam TYPE string,
        li_rc LIKE sy-subrc,
        ls_errtxt TYPE string.

CONCATENATE p_objid '.XLS' INTO ls_objnam.
CONDENSE ls_objnam NO-GAPS.

SELECT SINGLE relid objid FROM wwwdata
    INTO CORRESPONDING FIELDS OF lo_objdata
    WHERE srtf2    = 0
      AND relid    = 'MI'
      AND objid    = p_objid.

IF sy-subrc NE 0 OR lo_objdata-objid EQ space.
CONCATENATE '模板文件：' ls_objnam '不存在，请用TCODE：SMW0进行加载' INTO ls_errtxt.
MESSAGE e600(zdev) WITH ls_errtxt.
ENDIF.

ls_destination = p_dest.

CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
    EXPORTING
      key         = lo_objdata
      destination = ls_destination
    IMPORTING
      rc          = li_rc.

IF li_rc NE 0.
    CONCATENATE '模板文件：' ls_objnam '下载失败' INTO ls_errtxt.
    MESSAGE e600(zdev) WITH ls_errtxt.
ENDIF.
ENDFORM.                    "download_excel_fromserver

*&---------------------------------------------------------------------*
*& (15)    Form open_excel_hide
*&---------------------------------------------------------------------*
*       后台打开指定的excel
*----------------------------------------------------------------------*
*      -->P_FILE      excel本地路径
*----------------------------------------------------------------------*
FORM open_excel_hide USING p_file.
SET PROPERTY OF application 'Visible' = 0.
CALL METHOD OF application 'Workbooks' = workbook.
CALL METHOD OF workbook 'Open' = workbook
    EXPORTING
    #1 = p_file.
CALL METHOD OF workbook 'Worksheets' = sheet
    EXPORTING
    #1 = 'Sheet1'.
CALL METHOD OF sheet 'Activate'.
ENDFORM.                    "open_excel_hide

*&---------------------------------------------------------------------*
*& (16)    Form open_excel
*&---------------------------------------------------------------------*
*      打开指定的excel
*----------------------------------------------------------------------*
*      -->P_FILE     excel本地路径
*----------------------------------------------------------------------*
FORM open_excel USING p_file.
SET PROPERTY OF application 'Visible' = 1.
CALL METHOD OF application 'Workbooks' = workbook.
CALL METHOD OF workbook 'Open' = workbook
    EXPORTING
    #1 = p_file.
CALL METHOD OF workbook 'Worksheets' = sheet
    EXPORTING
    #1 = 'Sheet1'.
CALL METHOD OF sheet 'Activate'.
ENDFORM.                    "open_excel

*&---------------------------------------------------------------------*
*& (17)    Form open_excel_sheet
*&---------------------------------------------------------------------*
*       打开指定的excel的sheet
*----------------------------------------------------------------------*
*      -->P_SHEET    打开的sheet名
*      -->P_FILE     excel本地路径
*----------------------------------------------------------------------*
FORM open_excel_sheet USING p_sheet p_file.
SET PROPERTY OF application 'Visible' = 1.
CALL METHOD OF application 'Workbooks' = workbook.
CALL METHOD OF workbook 'Open' = workbook
    EXPORTING
    #1 = p_file.
CALL METHOD OF workbook 'Worksheets' = sheet
    EXPORTING
    #1 = p_sheet.
CALL METHOD OF sheet 'Activate'.
ENDFORM.                    "open_excel_sheet

*&---------------------------------------------------------------------*
*& (18)    Form open_excel_by_sheet
*&---------------------------------------------------------------------*
*       打开sheet
*----------------------------------------------------------------------*
*      -->P_SHEET    打开的sheet名
*----------------------------------------------------------------------*
FORM open_excel_by_sheet USING p_sheet.
CALL METHOD OF workbook 'WORKSHEETS' = sheet
    EXPORTING
    #1 = p_sheet.

CALL METHOD OF sheet 'ACTIVATE'.
ENDFORM. "open_excel_by_sheet

*&---------------------------------------------------------------------*
*& (19)    Form select_range
*&---------------------------------------------------------------------*
*       选择范围
*----------------------------------------------------------------------*
*      -->P_LEFT     左
*      -->P_TOP      上
*      -->P_RIGHT    右
*      -->P_BUTTOM   下
*----------------------------------------------------------------------*
FORM select_range USING
                     p_left TYPE i
                     p_top TYPE i
                     p_right TYPE i
                     p_buttom TYPE i.
CALL METHOD OF application 'CELLS' = cell
    EXPORTING
    #1 = p_top
    #2 = p_left.

CALL METHOD OF application 'CELLS' = cell1
    EXPORTING
    #1 = p_buttom
    #2 = p_right.

CALL METHOD OF application 'RANGE' = range
    EXPORTING
    #1 = cell
    #2 = cell1.

CALL METHOD OF range 'SELECT'.

ENDFORM. "select_range

*&---------------------------------------------------------------------*
*& (20)    Form clear_data
*&---------------------------------------------------------------------*
*       清除数据
*----------------------------------------------------------------------*
*      -->P_LEFT     左
*      -->P_TOP      上
*      -->P_RIGHT    右
*      -->P_BUTTOM   下
*----------------------------------------------------------------------*
FORM clear_data USING p_left TYPE i
                        p_top TYPE i
                         p_right TYPE i
                         p_buttom TYPE i.
PERFORM select_range USING p_left
                                p_top
                                p_right
                                p_buttom.
CALL METHOD OF range 'ClearContents'.

ENDFORM. "clear_data

*&---------------------------------------------------------------------*
*&   (21)   Form merge_cells
*&---------------------------------------------------------------------*
*       合并单元格
*----------------------------------------------------------------------*
*      -->P_LEFT     text
*      -->P_TOP      text
*      -->P_RIGHT    text
*      -->P_BUTTOM   text
*----------------------------------------------------------------------*
FORM merge_cells USING p_left TYPE i
                        p_top TYPE i
                         p_right TYPE i
                         p_buttom TYPE i.
PERFORM select_range USING p_left
                                p_top
                                p_right
                                p_buttom.
CALL METHOD OF range 'Merge'.

ENDFORM. "merge_cells

*&---------------------------------------------------------------------*
*& (22)    Form process_indcator
*&---------------------------------------------------------------------*
*       进度指示
*----------------------------------------------------------------------*
*      -->TEXT       提示文字
*      -->PERCENTAGE 进度百分比
*----------------------------------------------------------------------*
FORM process_indcator USING text percentage.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
    EXPORTING
      percentage = percentage
      text       = text.
ENDFORM.                    "process_indcator

*&---------------------------------------------------------------------*
*&   (23)   Form delete_row
*&---------------------------------------------------------------------*
*       删除行
*----------------------------------------------------------------------*
*      -->I_ROW      行号
*----------------------------------------------------------------------*
FORM delete_row USING i_row.
CALL METHOD OF application 'ROWS' = rows
    EXPORTING
    #1 = i_row.

CALL METHOD OF rows 'DELETE'.

ENDFORM. "delete_row

*&---------------------------------------------------------------------*
*&   (24)   Form add_comment
*&---------------------------------------------------------------------*
*       添加注释
*----------------------------------------------------------------------*
*      -->I_ROW      行号
*      -->I_COL      列号
*      -->P_TEXT     注释内容
*----------------------------------------------------------------------*
FORM add_comment USING i_row i_col p_text.
CALL METHOD OF application 'Cells' = cell
    EXPORTING
    #1 = i_row
    #2 = i_col.
CALL METHOD OF cell 'AddComment' = cell1.
CALL METHOD OF cell1 'Text'
    EXPORTING
    #1 = p_text.
CALL METHOD OF cell1 'Shape' = font.
SET PROPERTY OF font 'Width' = '20'.
SET PROPERTY OF font 'Height' = '20'.
ENDFORM.                    "add_comment

*&---------------------------------------------------------------------*
*& （25）    Form hide_columns
*&---------------------------------------------------------------------*
*       隐藏列
*----------------------------------------------------------------------*
*      -->COLNUM     输入列号
*----------------------------------------------------------------------*
FORM hide_columns USING colnum.
CALL METHOD OF application 'COLUMNS' = columns
    EXPORTING
    #1 = colnum.
SET PROPERTY OF columns 'Hidden' = 'true'.
ENDFORM.                    "hide_columns

*&---------------------------------------------------------------------*
*&   (26)   Form delete_columns
*&---------------------------------------------------------------------*
*       删除列
*----------------------------------------------------------------------*
*      -->COLNUM     输入列号
*----------------------------------------------------------------------*
FORM delete_columns USING colnum.
CALL METHOD OF application 'COLUMNS' = columns
    EXPORTING
    #1 = colnum.
CALL METHOD OF columns 'DELETE'.
ENDFORM.                    "delete_columns

*&---------------------------------------------------------------------*
*& （27）    Form hide_row
*&---------------------------------------------------------------------*
*       隐藏行
*----------------------------------------------------------------------*
*      -->I_ROW      输入行号
*----------------------------------------------------------------------*
FORM hide_row USING i_row.
CALL METHOD OF application 'ROWS' = rows
    EXPORTING
    #1 = i_row.

SET PROPERTY OF rows 'Hidden' = 'true'.

ENDFORM. "hide_row

3 注意事项
3.1 Sheet名称
Sheet名为中文时，在调用sheet对象时往往会调用不到，用英文字母不存在该问题。
3.2 释放对象
程序结束时需要释放对象，否则Excel有可能无法关闭。
释放对象代码如下：
FORM free_object.
FREE OBJECT font.
FREE OBJECT range.
FREE OBJECT range1.
FREE OBJECT columns.
FREE OBJECT rows.
FREE OBJECT cell.
FREE OBJECT cell1.
FREE OBJECT sheet1.
FREE OBJECT sheet.
FREE OBJECT workbook.
FREE OBJECT application.
ENDFORM.