【ABAP】ole2 excel多sheet导入导出

原理就不分享了
原来是用了动态表格，但是要导出不方便，所以就写死了，excel多sheet导入的类放在另一篇文章里

REPORT  zcdemo17.
INCLUDE ole2incl.

DATA: excel     TYPE ole2_object,
      workbooks TYPE ole2_object,
      workbook  TYPE ole2_object,
      sheet     TYPE ole2_object,
      cell      TYPE ole2_object,
      border    TYPE ole2_object,
      column    TYPE ole2_object,
      borders   TYPE ole2_object,
      entirecol TYPE ole2_object,
      interior  TYPE ole2_object.

DATA: BEGIN OF w_sheet OCCURS 0,
      field01 TYPE string,
      field02 TYPE string,
      field03 TYPE string,
      field04 TYPE string,
      field05 TYPE string,
      field06 TYPE string,
      field07 TYPE string,
      field08 TYPE string,
      field09 TYPE string,
      field10 TYPE string,
      field11 TYPE string,
      field12 TYPE string,
      field13 TYPE string,
      field14 TYPE string,
      field15 TYPE string,
      field16 TYPE string,
      field17 TYPE string,
      field18 TYPE string,
      field19 TYPE string,
      field20 TYPE string,
      field21 TYPE string,
      field22 TYPE string,
      field23 TYPE string,
      field24 TYPE string,
      field25 TYPE string,
      field26 TYPE string,
      field27 TYPE string,
      field28 TYPE string,
      field29 TYPE string,
      field30 TYPE string,
      field31 TYPE string,
      field32 TYPE string,
      field33 TYPE string,
      field34 TYPE string,
      field35 TYPE string,
      field36 TYPE string,
      field37 TYPE string,
      field38 TYPE string,
      field39 TYPE string,
      field40 TYPE string,
      field41 TYPE string,
      field42 TYPE string,
      field43 TYPE string,
      field44 TYPE string,
      field45 TYPE string,
      field46 TYPE string,
      field47 TYPE string,
      field48 TYPE string,
      field49 TYPE string,
      field50 TYPE string,
      field51 TYPE string,
      field52 TYPE string,
      field53 TYPE string,
      field54 TYPE string,
      field55 TYPE string,
      field56 TYPE string,
      field57 TYPE string,
      field58 TYPE string,
      field59 TYPE string,
      field60 TYPE string,
      field61 TYPE string,
      field62 TYPE string,
      field63 TYPE string,
      field64 TYPE string,
      field65 TYPE string,
      field66 TYPE string,
      field67 TYPE string,
      field68 TYPE string,
      field69 TYPE string,
      field70 TYPE string,
      field71 TYPE string,
      field72 TYPE string,
      field73 TYPE string,
      field74 TYPE string,
      field75 TYPE string,
      field76 TYPE string,
      field77 TYPE string,
      field78 TYPE string,
      field79 TYPE string,
      field80 TYPE string,
      field81 TYPE string,
      field82 TYPE string,
      field83 TYPE string,
      field84 TYPE string,
      field85 TYPE string,
      field86 TYPE string,
      field87 TYPE string,
      field88 TYPE string,
      field89 TYPE string,
      field90 TYPE string,
      field91 TYPE string,
      field92 TYPE string,
      field93 TYPE string,
      field94 TYPE string,
      field95 TYPE string,
      field96 TYPE string,
      field97 TYPE string,
      field98 TYPE string,
      field99 TYPE string,
      END OF w_sheet.

DATA: t_sheet LIKE TABLE OF w_sheet.

DATA:BEGIN OF gs_excel,
*       matnr        TYPE mara-matnr,
*       maktx        TYPE makt-maktx,
*       meins        TYPE mara-meins,
*       creationdate TYPE datum,
*       creationtime TYPE uzeit,
      sheet_num TYPE i,
      sheet LIKE TABLE OF w_sheet,
      END OF gs_excel.
DATA gt_excel LIKE TABLE OF gs_excel.

DATA:BEGIN OF gs_excel_sheet1,
       matnr        TYPE mara-matnr,
       meins        TYPE mara-meins,
       creationdate TYPE datum,
       creationtime TYPE uzeit,
     END OF gs_excel_sheet1.
DATA gt_excel_sheet1 LIKE TABLE OF gs_excel_sheet1.

DATA:BEGIN OF gs_excel_sheet2,
       matnr TYPE mara-matnr,
       maktx TYPE makt-maktx,
     END OF gs_excel_sheet2.
DATA gt_excel_sheet2 LIKE TABLE OF gs_excel_sheet2.

FIELD-SYMBOLS <ft_excel> TYPE STANDARD TABLE.
FIELD-SYMBOLS <fs_excel> TYPE ANY.



DATA:lr_excel TYPE REF TO zcl_tab_doc.
DATA lt_files TYPE filetable.
DATA l_rc TYPE i.
DATA l_filename TYPE string.

DATA: zcl_tab_doc TYPE REF TO zcl_tab_doc.

DATA lt_sheetname TYPE zcl_tab_doc=>tt_sheetname_info."存放了如何输入excel的信息
DATA ls_sheetname LIKE LINE OF lt_sheetname.

DATA: lr_functions TYPE REF TO cl_salv_functions_list.

DATA lt_tabc TYPE zcl_tab_doc=>tt_tabc."存放获取sheet页的数据

DATA l_text TYPE string.

PARAMETERS p_file TYPE rlgrap-filename OBLIGATORY MEMORY ID mi.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

  DATA ls_files LIKE LINE OF lt_files.
  CALL METHOD zcl_tab_doc=>file_open_dialog_default
    CHANGING
      ct_file_table           = lt_files
      c_rc                    = l_rc
    EXCEPTIONS
      file_open_dialog_failed = 1
      cntl_error              = 2
      error_no_gui            = 3
      not_support_by_gui      = 4
      OTHERS                  = 5.
  READ TABLE lt_files INTO ls_files INDEX 1.
  IF sy-subrc EQ 0.
    p_file = ls_files-filename.
  ENDIF.

START-OF-SELECTION.

  l_text = 'loading'.

  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
    EXPORTING
*      percentage = 99            "进度
      text       = l_text.

**********************************************************************
  " 动态内表
  TYPE-POOLS:abap.

*  DATA lt_table TYPE TABLE OF dfies. "字段结构表
*  DATA ls_table TYPE dfies.
  DATA lr_struc TYPE REF TO cl_abap_structdescr.
  DATA lr_table TYPE REF TO cl_abap_tabledescr.
  DATA lr_type TYPE REF TO cl_abap_typedescr.
  DATA lr_data TYPE REF TO cl_abap_datadescr.
  DATA lt_comp TYPE abap_component_tab.
  DATA ls_comp LIKE LINE OF lt_comp.
  DATA dyn_wa TYPE REF TO data.
  DATA dyn_table TYPE REF TO data.
  DATA dyn_excel TYPE REF TO data.
  DATA dyn_excel_wa TYPE REF TO data.
  DATA l_string TYPE string.
  FIELD-SYMBOLS: <dyn_table> TYPE STANDARD TABLE,
  <dyn_wa> TYPE ANY.

  DATA tabname TYPE tabname.
  DATA fieldname TYPE fieldname.
  DATA fieldname_tmp TYPE fieldname.
  DATA number_n(3) TYPE n.

  tabname = 'lt_excel'.
  fieldname = 'filed'.
  number_n = 0.

*  lo_elem ?= cl_abap_typedescr=>describe_by_name( 'string' ).

  DO 100 TIMES.
    number_n = number_n + 1.
    CLEAR fieldname_tmp.
    CONCATENATE fieldname number_n INTO fieldname_tmp.
    CONCATENATE tabname '-' fieldname_tmp INTO l_string.
    ls_comp-name = fieldname_tmp.
    ls_comp-type ?= cl_abap_typedescr=>describe_by_name( 'string' )."lo_elem.

    APPEND ls_comp TO lt_comp.
    CLEAR ls_comp.
  ENDDO.
*根据字段目录创建动态结构类型
  CALL METHOD cl_abap_structdescr=>create
    EXPORTING
      p_components = lt_comp
    RECEIVING
      p_result     = lr_struc.
*根据动态结构创建动态内表类型
  CALL METHOD cl_abap_tabledescr=>create
    EXPORTING
      p_line_type = lr_struc
    RECEIVING
      p_result    = lr_table.
*参照动态结构类型和动态内表类型创建内表与工作区
  CREATE DATA dyn_wa TYPE HANDLE lr_struc.
  CREATE DATA dyn_table TYPE HANDLE lr_table.
  CREATE DATA dyn_excel_wa TYPE HANDLE lr_struc.
  CREATE DATA dyn_excel TYPE HANDLE lr_table.
*指定内表与工作区到字段符号
  ASSIGN dyn_wa->* TO <dyn_wa>.
  ASSIGN dyn_table->* TO <dyn_table>.
  ASSIGN dyn_excel_wa->* TO <fs_excel>.
  ASSIGN dyn_excel->* TO <ft_excel>.

**********************************************************************

  REFRESH lt_sheetname.
*  CLEAR ls_sheetname.
**  ls_sheetname-sheetname = 'Sheet1'.
*  ls_sheetname-startrow = 2.
*  ls_sheetname-startcol = 1.
*  ls_sheetname-every_copy_lines = 30.
*  ls_sheetname-endcol = 4.
*  APPEND ls_sheetname TO lt_sheetname.
*
*  CLEAR ls_sheetname.
**  ls_sheetname-sheetname = 'Sheet2'.
*  ls_sheetname-startrow = 2.
*  ls_sheetname-startcol = 1.
*  ls_sheetname-every_copy_lines = 30.
*  ls_sheetname-endcol = 4.
*  APPEND ls_sheetname TO lt_sheetname.

*  lt_sheetname = value #(
*  ( sheetname = 'Sheet1'
*    STARTROW  = 2
*    startcol  = 1
*    EVERY_COPY_LINES = 30
*    endcol = 4
*  )
*  ( SHEETNAME = 'Sheet2'
*    startrow  = 2
*    STARTCOL  = 1
*    every_copy_lines = 30
*    ENDCOL = 2
*  )
*  ).

  CREATE OBJECT lr_excel.
  l_filename = p_file.
  CALL METHOD lr_excel->import_excel_by_ole
    EXPORTING
      i_filename      = l_filename
    CHANGING
      ct_sheetname    = lt_sheetname
      ct_tabc         = lt_tabc
    EXCEPTIONS
      file_open_error = 1
      OTHERS          = 2.
  IF sy-subrc <> 0.
    MESSAGE e398(00) WITH 'file opened failed!'.
*    MESSAGE e398(00) WITH |file opened failed!| ''.
  ENDIF.

  CHECK lt_tabc[] IS NOT INITIAL.

  DATA ls_tabc LIKE LINE OF lt_tabc.
  DATA l_tabix TYPE i.

  REFRESH gt_excel.

  LOOP AT lt_tabc INTO ls_tabc.
    l_tabix = sy-tabix.

    REFRESH t_sheet.
    CALL METHOD lr_excel->excel_convert_table
      EXPORTING
*       i_separator            = CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB
        is_table               = ls_tabc
        i_repid                = sy-repid
        i_special_process      = 'FRM_SPECIAL_PROCESS'
      CHANGING
        ct_table               = t_sheet
   	  EXCEPTIONS
        context_convert_failed = 1
        OTHERS                 = 2.
    IF sy-subrc <> 0.
      MESSAGE e398(00) WITH 'Context converted failed!' ''.
    ELSE.
      CLEAR gs_excel.
      gs_excel-sheet_num = l_tabix.
      gs_excel-sheet[] = t_sheet[].
      APPEND gs_excel TO gt_excel.
    ENDIF.
  ENDLOOP.


  DATA cl_descr TYPE REF TO cl_abap_structdescr.
  FIELD-SYMBOLS <fs_comp> TYPE abap_compdescr.
  FIELD-SYMBOLS <value1> TYPE ANY.
  FIELD-SYMBOLS <value2> TYPE ANY.
  DATA l_index TYPE i.
  DATA p TYPE i.
  DATA q TYPE i.
  DATA sttime LIKE sy-uzeit.
  DATA entime LIKE sy-uzeit.
  DATA sptime TYPE i.

  GET TIME.
  sttime = sy-uzeit.
  l_text = 'loading/'.


  " 导出
  CREATE OBJECT excel 'Excel.Application'.
  CALL METHOD OF excel 'Workbooks' = workbooks.
  CALL METHOD OF workbooks 'Open' = workbooks
    EXPORTING
    #1 = p_file.

  LOOP AT gt_excel INTO gs_excel.
    CALL METHOD OF excel 'Worksheets' = sheet
      EXPORTING #1 = gs_excel-sheet_num.
    CALL METHOD OF sheet 'ACTIVATE'.

    CLEAR l_tabix.
    LOOP AT gs_excel-sheet INTO w_sheet.

      l_tabix = sy-tabix.
      IF l_tabix > 2.
        DO 50 TIMES.
          IF sy-index > 1.
            p = sy-index.
            q = p MOD 2.
            IF q NE 0.
              UNASSIGN: <value1>,<value2>.
              l_index = sy-index - 1.
              ASSIGN COMPONENT l_index OF STRUCTURE w_sheet TO <value1>.
              ASSIGN COMPONENT sy-index OF STRUCTURE w_sheet TO <value2>.
              IF <value1> IS NOT INITIAL.
                <value2> = 'TEST'.
                PERFORM fill_cell USING l_tabix sy-index <value2>.

                GET TIME.
                entime = sy-uzeit.
                sptime = entime - sttime.

                IF sptime >= 1.

                  IF l_text = 'loading--'.
                    l_text = 'loading\'.
                  ELSEIF l_text = 'loading\'.
                    l_text = 'loading |'.
                  ELSEIF l_text = 'loading |'.
                    l_text = 'loading/'.
                  ELSEIF l_text = 'loading/'.
                    l_text = 'loading--'.
                  ENDIF.

                  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
                   EXPORTING
*      percentage = 99            "进度
                     text       = l_text.

                  sttime = entime.

                ENDIF.

              ENDIF.

            ENDIF.
          ENDIF.
        ENDDO.
      ENDIF.
    ENDLOOP.

  ENDLOOP.

  SET PROPERTY OF excel 'Visible' = 1.
  SET PROPERTY OF excel  'screenupdating'  = 1.
  MESSAGE '导出成功' TYPE 'S'.
  EXIT.


*  READ TABLE lt_tabc INTO ls_tabc WITH KEY sheetname = 'Sheet1'.
*  IF sy-subrc EQ 0.
*    CALL METHOD lr_excel->excel_convert_table
*      EXPORTING
**       i_separator            = CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB
*        is_table               = ls_tabc
*        i_repid                = 'Z_TAB_TEST04'
*        i_special_process      = 'FRM_SPECIAL_PROCESS'
*      CHANGING
*        ct_table               = gt_excel_sheet1
*      EXCEPTIONS
*        context_convert_failed = 1
*        OTHERS                 = 2.
*    IF sy-subrc <> 0.
*      MESSAGE e398(00) WITH 'Context converted failed!' ''.
*    ENDIF.
*  ENDIF.
*
*  CHECK gt_excel_sheet1[] IS NOT INITIAL.
*
*  READ TABLE lt_tabc INTO ls_tabc WITH KEY sheetname = 'Sheet2'.
*  IF sy-subrc EQ 0.
*    CALL METHOD lr_excel->excel_convert_table
*      EXPORTING
**       i_separator            = CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB
*        is_table               = ls_tabc
*        i_repid                = 'Z_TAB_TEST04'
*        i_special_process      = 'FRM_SPECIAL_PROCESS'
*      CHANGING
*        ct_table               = gt_excel_sheet2
*      EXCEPTIONS
*        context_convert_failed = 1
*        OTHERS                 = 2.
*    IF sy-subrc <> 0.
*      MESSAGE e398(00) WITH 'Context converted failed!' ''.
*    ENDIF.
*  ENDIF.

*  SORT gt_excel_sheet2 BY matnr.
*
*  LOOP AT gt_excel_sheet1 INTO gs_excel_sheet1.
*    READ TABLE gt_excel_sheet2 INTO gs_excel_sheet2 WITH KEY matnr = gs_excel_sheet1-matnr BINARY SEARCH.
*    IF sy-subrc EQ 0.
*      gs_excel-maktx = gs_excel_sheet2-maktx.
*    ENDIF.
*    MOVE-CORRESPONDING gs_excel_sheet1 TO gs_excel.
*    APPEND gs_excel TO gt_excel.
*    CLEAR gs_excel.
*  ENDLOOP.

  DATA go_alv TYPE REF TO cl_salv_table.

  TRY.
      cl_salv_table=>factory(
        IMPORTING
          r_salv_table = go_alv
        CHANGING
          t_table      = <ft_excel>[] ).

    CATCH cx_salv_msg.
  ENDTRY.
  lr_functions = go_alv->get_functions( ).
  lr_functions->set_all( 'X' ).
  CHECK go_alv IS BOUND.
  go_alv->display( ).

*&---------------------------------------------------------------------*
*&      Form  frm_special_process
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->IS_COMPONET  text
*      -->I_ROW        text
*      -->I_COL        text
*      <--C_CELL       text
*----------------------------------------------------------------------*
FORM frm_special_process USING
      is_componet TYPE abap_compdescr
      i_row TYPE i
      i_col TYPE i
  CHANGING
      c_cell TYPE string.
  DATA lv_matnr TYPE mara-matnr.
  CHECK c_cell IS NOT INITIAL.
*  CASE i_col.
*    WHEN '1'.
*      "内外部转化
*      lv_matnr = c_cell.
*      CALL FUNCTION 'CONVERSION_EXIT_MATN1_INPUT'
*        EXPORTING
*          input        = lv_matnr
*        IMPORTING
*          output       = lv_matnr
*        EXCEPTIONS
*          length_error = 1
*          OTHERS       = 2.
*      IF sy-subrc <> 0.
*        CLEAR lv_matnr.
*      ENDIF.
*      c_cell = lv_matnr.
*    WHEN '2'.
*      CHECK is_componet-name = 'MEINS'."转化单位
*      CALL FUNCTION 'CONVERSION_EXIT_CUNIT_INPUT'
*        EXPORTING
*          input          = c_cell
*          language       = sy-langu
*        IMPORTING
*          output         = c_cell
*        EXCEPTIONS
*          unit_not_found = 1
*          OTHERS         = 2.
*      IF sy-subrc <> 0.
*        MESSAGE e398(00) WITH 'unit convert failed!|'.
**        MESSAGE e398(00) WITH |unit convert failed!| ''.
*      ENDIF.
*    WHEN '3'.
*      DATA: lt_cell LIKE TABLE OF c_cell WITH HEADER LINE.
*      DATA: ls_cell LIKE LINE OF lt_cell.
*
*      "日期转换
*      DATA l_num2(2) TYPE n.
*      SPLIT c_cell AT '/' INTO TABLE lt_cell.
*      CLEAR c_cell.
*      LOOP AT lt_cell INTO ls_cell.
*        CASE sy-tabix.
*          WHEN 1.
*            c_cell = ls_cell.
*          WHEN 2 OR 3.
*            l_num2 = ls_cell.
**            c_cell = |{ C_CELL }{ L_NUM2 }|.
*            CONCATENATE c_cell l_num2 INTO c_cell.
*          WHEN OTHERS.
*        ENDCASE.
*      ENDLOOP.
*      REPLACE ALL OCCURRENCES OF '-' IN c_cell WITH ''.
*      REPLACE ALL OCCURRENCES OF '/' IN c_cell WITH ''.
*      CONDENSE c_cell NO-GAPS.
*    WHEN '4'.
*      "时间转换
*      REPLACE ALL OCCURRENCES OF ':' IN c_cell WITH ''.
*      CONDENSE c_cell NO-GAPS.
*    WHEN OTHERS.
*  ENDCASE.
ENDFORM.                    "frm_special_process
*&---------------------------------------------------------------------*
*&      Form  fill_cell
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_I        text
*      -->P_J        text
*      -->P_VAL      text
*----------------------------------------------------------------------*
FORM fill_cell USING    p_i
                        p_j
                        p_val.
  CALL METHOD OF excel 'CELLS' = cell
    EXPORTING #1 = p_i #2 = p_j.

  SET PROPERTY OF cell 'VALUE' = p_val.
ENDFORM.                    "fill_cell