EXCEL OPRATION

 ***INCLUDE Z_EXCEL_HEADER .
INCLUDE ole2incl.
CONSTANTS: xlleft   VALUE '0',
           xlcenter VALUE '3',
           xlright  VALUE '4',
           xltop    VALUE '1',
           xlmiddle VALUE '2',
           xlbottom VALUE '0',
           xlfill   VALUE '5'.

DATA : excel TYPE ole2_object,
       books TYPE ole2_object,
       book  TYPE ole2_object,
       sheet TYPE ole2_object,
       cell  TYPE ole2_object,
       cell1 TYPE ole2_object,
       cell2 TYPE ole2_object,
       range TYPE ole2_object,
       line  TYPE i,
       row   TYPE i,
       col   TYPE i.

* VARIABLES FROM INCLUDE FILE EXCLDEF .
DATA: macro  TYPE ole2_object,           "MACRO object
      w_book TYPE ole2_object,           "list of workbooks
      w_sheet TYPE ole2_object,           "list of sheet
      seletion TYPE ole2_object,           "Selection
      value  TYPE ole2_object,           "value
      cells   TYPE ole2_object,          "cellS
      celle   TYPE ole2_object,          "cellE
      font   TYPE ole2_object,           "font
      border TYPE ole2_object,           "Border
      column TYPE ole2_object,           "Column
      interior TYPE ole2_object .        "Interior

*---------------------------------------------------------------------*
*       FORM ERR_HDL                                                  *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
FORM err_hdl.
  IF sy-subrc <> 0.
    WRITE: / 'Error Code in OLE-Automation:'(010), sy-subrc.
    STOP.
  ENDIF.
ENDFORM.                    " ERR_HDL

*---------------------------------------------------------------------*
*       FORM READ_CELL                                                *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  I                                                             *
*  -->  J                                                             *
*  -->  VAL                                                           *
*---------------------------------------------------------------------*
FORM read_cell USING i j val .

  CALL METHOD OF excel 'Cells' = cell EXPORTING #1 = i #2 = j.
  CALL METHOD OF cell 'Value' = val .
ENDFORM.                    "read_cell

*---------------------------------------------------------------------*
*       FORM READ_CELL                                                *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  I                                                             *
*  -->  J                                                             *
*  <--  VAL                                                           *
*---------------------------------------------------------------------*
FORM read_cell_text USING i j CHANGING val .

  CALL METHOD OF excel 'Cells' = cell EXPORTING #1 = i #2 = j.
  CALL METHOD OF cell 'Text' = val .
ENDFORM.                    "read_cell_text

*---------------------------------------------------------------------*
*       FORM FILL_CELL                                                *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  I                                                             *
*  -->  J                                                             *
*  -->  VAL                                                           *
*---------------------------------------------------------------------*
FORM fill_cell USING i j val.
  CALL METHOD OF excel 'CELLS' = cell EXPORTING #1 = i #2 = j.
  SET PROPERTY OF cell  'VALUE' = val .
ENDFORM.                    "fill_cell

*---------------------------------------------------------------------*
*       FORM fill_cell_addcol                                         *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  I                                                             *
*  -->  J                                                             *
*  -->  VAL                                                           *
*---------------------------------------------------------------------*
FORM fill_cell_addcol USING i j val.
  CALL METHOD OF excel 'CELLS' = cell EXPORTING #1 = i #2 = j.
  SET PROPERTY OF cell  'VALUE' = val .
  ADD 1 TO j.
ENDFORM.                    "fill_cell_addcol
*---------------------------------------------------------------------*
*       FORM fill_cell_addrow                                         *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  I                                                             *
*  -->  J                                                             *
*  -->  VAL                                                           *
*---------------------------------------------------------------------*
FORM fill_cell_addrow USING i j val.
  CALL METHOD OF excel 'CELLS' = cell EXPORTING #1 = i #2 = j.
  SET PROPERTY OF cell  'VALUE' = val .
  ADD 1 TO i.
ENDFORM.                    "fill_cell_addcol

*&---------------------------------------------------------------------*
*&      Form  fill_cell_with_border
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->I          text
*      -->J          text
*      -->VAL        text
*      -->EGYN       text
*----------------------------------------------------------------------*
FORM fill_cell_with_border USING i j val.
  CALL METHOD OF excel 'Cells' = cell EXPORTING #1 = i #2 = j.
  SET PROPERTY OF cell 'Value' = val .

  CALL METHOD OF cell 'Borders' = border .
  SET PROPERTY OF border 'LineStyle' = 1.    "1 -- 实线, 2 -- 虚线
  SET PROPERTY OF border 'Weight' = 2.       "边框线的粗细
ENDFORM.                    "fill_cell_with_border

*---------------------------------------------------------------------*
*       FORM set_range                                                *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  LINE1                                                         *
*  -->  ROW1                                                          *
*  -->  LINE2                                                         *
*  -->  ROW2                                                          *
*  -->  P_RANGE                                                       *
*---------------------------------------------------------------------*
FORM set_range USING line1 row1 line2 row2
               CHANGING p_range.
  DATA: l_cell1 TYPE ole2_object,
        l_cell2 TYPE ole2_object.

  CALL METHOD OF excel 'CELLS' = l_cell1
    EXPORTING #1 = line1 #2 = row1.

  CALL METHOD OF excel 'CELLS' = l_cell2
    EXPORTING #1 = line2 #2 = row2.

  CALL METHOD OF excel 'Range' = p_range
    EXPORTING #1 = l_cell1 #2 = l_cell2.
ENDFORM.                    "set_range

*---------------------------------------------------------------------*
*       FORM merge_cell                                               *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  LINE1                                                         *
*  -->  ROW1                                                          *
*  -->  LINE2                                                         *
*  -->  ROW2                                                          *
*---------------------------------------------------------------------*
FORM merge_cell USING line1 row1 line2 row2 halign valign.
  DATA: l_range TYPE ole2_object.

  PERFORM set_range
    USING line1 row1
          line2 row2
    CHANGING l_range.

  SET PROPERTY OF l_range 'MergeCells' = 1.

  IF halign <> '-1'.
    SET PROPERTY OF l_range 'HorizontalAlignment' = halign.
  ENDIF.

  IF valign <> '-1'.
    SET PROPERTY OF l_range 'VerticalAlignment'  = valign.
  ENDIF.
ENDFORM.                    "merge_cell

*---------------------------------------------------------------------*
*       FORM read_from_file                                           *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  P_FILE                                                        *
*---------------------------------------------------------------------*
FORM open_from_template USING p_file.
  CREATE OBJECT excel 'EXCEL.APPLICATION'.
  SET PROPERTY OF excel 'VISIBLE' = 0. "1 -- Visible, 0 -- invisiable
  CALL METHOD OF excel 'Workbooks' = books.
  CALL METHOD OF books 'Open' EXPORTING #1 = p_file .
  CALL METHOD OF excel 'WORKSHEETS' = sheet EXPORTING #1 = 1 .

  IF sy-subrc <> 0.
    CALL METHOD OF excel 'Quit'.
    FREE OBJECT excel .
    MESSAGE e398(00) WITH '没有找到EXCEL文件!' '' '' ''.
  ENDIF.
ENDFORM.                    "open_from_template

*---------------------------------------------------------------------*
*       FORM open_blank_book                                          *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
FORM open_blank_books.
* 启动EXCEL
  CREATE OBJECT excel 'EXCEL.APPLICATION'.
  SET PROPERTY OF excel 'VISIBLE' = 1.
  CALL METHOD OF excel 'Workbooks' = books.
  CALL METHOD OF books 'ADD' = book.
  CALL METHOD OF book 'SHEETS' = sheet EXPORTING #1 = 1 .
  CALL METHOD OF sheet 'ACTIVATE'.
ENDFORM.                    "open_blank_books

*---------------------------------------------------------------------*
*       FORM open_invisible_books                                     *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
FORM open_invisible_books.
* 启动EXCEL
  CREATE OBJECT excel 'EXCEL.APPLICATION'.
  SET PROPERTY OF excel 'VISIBLE' = 0.
  CALL METHOD OF excel 'Workbooks' = books.
  CALL METHOD OF books 'ADD' = book.
  CALL METHOD OF book 'SHEETS' = sheet EXPORTING #1 = 1 .
  CALL METHOD OF sheet 'ACTIVATE'.
ENDFORM.                    "open_invisible_books

*---------------------------------------------------------------------*
*       FORM quit_excel                                               *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
FORM close_excel.
  SET PROPERTY OF excel 'DisplayAlerts' = 0.
  CALL METHOD OF excel 'Quit'.
  FREE OBJECT excel .
ENDFORM.                    "close_excel

*---------------------------------------------------------------------*
*       FORM SELSHEET                                                 *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  P_SHEET                                                       *
*---------------------------------------------------------------------*
FORM selsheet USING p_sheet .
  CALL METHOD OF excel 'WORKSheets' = sheet EXPORTING #1 = p_sheet .
  CALL METHOD OF sheet 'SELECT' .
  CALL METHOD OF sheet 'ACTIVATE'.
ENDFORM .                    "selsheet

*---------------------------------------------------------------------*
*       FORM set_cell_border                                          *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  P_ROW                                                         *
*  -->  P_COL                                                         *
*  -->  P_STYLE                                                       *
*  -->  P_WEIGHT                                                      *
*---------------------------------------------------------------------*
FORM set_cell_border USING p_row1 p_col1
                           p_row2 p_col2
                           p_style p_weight.
  DATA: l_range  TYPE ole2_object,
        l_border TYPE ole2_object.

  PERFORM set_range
    USING p_row1
          p_col1
          p_row2
          p_col2
    CHANGING l_range.

  CALL METHOD OF l_range 'Borders' = l_border .
  SET PROPERTY OF l_border 'LineStyle' = p_style.
  SET PROPERTY OF l_border 'Weight' = p_weight.
ENDFORM.                    "set_cell_border

*---------------------------------------------------------------------*
*       FORM load_from_excel_file                                     *
*---------------------------------------------------------------------*
* 将EXCEL文件内容导入内部表                                           *
*---------------------------------------------------------------------*
*  -->  I_ITAB                                                        *
*  -->  P_FILE                                                        *
*  -->  P_COLS:   需要导入EXCEL的列数,以空格分割                      *
*                 如果未输入列数,导入所有有表头的列
*  -->  P_FIELDS: 对应内部字段的列数,以空格分割                       *
*                 如果未输入字段列数,默认为从第一个字段依次导入       *
*---------------------------------------------------------------------*
FORM load_from_excel_file TABLES i_itab
                          USING  p_file
                                 p_cols
                                 p_fields.
  DATA: BEGIN OF i_col OCCURS 0,
          number(3) TYPE n,
        END OF i_col.

  DATA: BEGIN OF i_field OCCURS 0,
          number(3) TYPE n,
        END OF i_field.

  DATA: cell_value TYPE string,
        tabix LIKE sy-tabix.

  FIELD-SYMBOLS: <fs> TYPE ANY,
                 <f1> TYPE ANY.

  SPLIT: p_cols   AT space INTO TABLE i_col,
         p_fields AT space INTO TABLE i_field.

  PERFORM open_from_template USING p_file.

* 如果未输入列数,导入所有有表头的列
* 对应的列每一列必须有表头
  IF i_col[] IS INITIAL.
    DO.
      PERFORM read_cell_text
        USING 1 sy-index
        CHANGING cell_value.
      IF cell_value = space.
        EXIT.
      ENDIF.

      CLEAR i_col.
      i_col-number = sy-index.
      APPEND i_col.
    ENDDO.
  ENDIF.

* 如果未输入字段列数,默认为从第一个字段依次导入
  IF i_field[] IS INITIAL.
    ASSIGN i_itab TO <fs>.
    DO.
      ASSIGN COMPONENT sy-index OF STRUCTURE <fs> TO <f1>.
      IF sy-subrc <> 0.
        EXIT.
      ENDIF.

      CLEAR i_field.
      i_field-number = sy-index.
      APPEND i_field.
    ENDDO.
  ENDIF.

  row = 2.
  DO.
    PERFORM read_cell_text
      USING row 1
      CHANGING cell_value.
    IF cell_value = space.
      EXIT.
    ENDIF.

    CLEAR: tabix,
           i_itab.
    LOOP AT i_col.
      ADD 1 TO tabix.
      col = i_col-number.
      PERFORM read_cell_text
        USING row col
        CHANGING cell_value.

      ASSIGN i_itab TO <fs>.
      READ TABLE i_field INDEX tabix.
      IF sy-subrc = 0.
        ASSIGN COMPONENT i_field-number OF STRUCTURE <fs> TO <f1>.
        <f1> = cell_value.
      ENDIF.
    ENDLOOP.
    APPEND i_itab.

    ADD 1 TO row.
  ENDDO.

  PERFORM close_excel.
ENDFORM.                    "load_from_excel_file