内表到多工作表

REPORT ytest_mutiexport.

"OLE
TYPES:BEGIN OF ty_t_sender,
        line(4096),
      END OF   ty_t_sender.

DATA: application TYPE  ole2_object,
      workbook    TYPE  ole2_object,
      range       TYPE  ole2_object,
      worksheet   TYPE  ole2_object,
      h_cell      TYPE  ole2_object,
      h_cell1     TYPE  ole2_object,
      ld_rc       TYPE i.

DATA: excel_tab TYPE  STANDARD TABLE OF ty_t_sender,
      excel_wa  TYPE  ty_t_sender.


DATA tabcr_ref TYPE REF TO cl_abap_tabledescr.
DATA descr_ref TYPE REF TO cl_abap_structdescr.
DATA lv_tab    TYPE tabname.
DATA lv_flg    TYPE c.
DATA lv_times  TYPE i.
DATA lv_count  TYPE i.
DATA lv_str    TYPE string.

FIELD-SYMBOLS:<fs_dept> TYPE abap_compdescr,
              <fs>      TYPE any,
              <fs_tab>  TYPE STANDARD TABLE.

DEFINE m_message.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE 'S' NUMBER sy-msgno
    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 DISPLAY LIKE sy-msgty.
    RETURN.
  ENDIF.
END-OF-DEFINITION.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
PARAMETERS:filename TYPE  localfile,
           sheet    TYPE fieldname OBLIGATORY DEFAULT 'Sheet1',
           visible  AS CHECKBOX,
           exit     AS CHECKBOX,
           save     AS CHECKBOX,
           i_begc   TYPE i DEFAULT 1,
           i_begr   TYPE i DEFAULT 1,
           sheet1   TYPE fieldname DEFAULT 'Sheet2',
           sheet2   TYPE fieldname DEFAULT 'Sheet3'.
SELECTION-SCREEN END   OF BLOCK b1.


START-OF-SELECTION.
  SELECT * INTO TABLE @DATA(data)  FROM tj02.
  SELECT * INTO TABLE @DATA(data1) FROM tj02t WHERE spras = @sy-langu.
  SELECT * INTO TABLE @DATA(data2) FROM tj30t WHERE spras = @sy-langu.

  DATA : lv_file TYPE string,
         lv_path TYPE string,
         lv_shet TYPE fieldname.

  IF filename IS INITIAL.
    CALL METHOD cl_gui_frontend_services=>file_save_dialog
      EXPORTING
        default_extension    = 'XLSX'
        default_file_name    = 'TEST'
      CHANGING
        filename             = lv_file
        path                 = lv_path
        fullpath             = lv_path
      EXCEPTIONS
        cntl_error           = 1
        error_no_gui         = 2
        not_supported_by_gui = 3
        OTHERS               = 4.
    IF sy-subrc = 0.
      filename = lv_path && lv_file.
    ENDIF.
  ENDIF.

  CHECK filename IS NOT INITIAL.

  CREATE OBJECT application 'Excel.Application'.
  m_message.

  lv_times = 1.

  IF sheet1 IS NOT INITIAL AND data1[] IS NOT INITIAL.
    ADD 1 TO lv_times.
  ENDIF.

  IF sheet2 IS NOT INITIAL AND data2[] IS NOT INITIAL.
    ADD 1 TO lv_times.
  ENDIF.

  IF visible = 'X'.
    SET PROPERTY OF application 'Visible' = 1 .
  ELSE.
    SET PROPERTY OF application 'Visible' = 0 .
  ENDIF.
  m_message.

  CALL METHOD OF application 'Workbooks' = workbook.
  m_message.

  CALL METHOD OF workbook 'Open' EXPORTING #1 = filename.
  m_message.

  CLEAR lv_count.
  DO lv_times TIMES.
    REFRESH excel_tab.
    ADD 1 TO lv_count.

    CASE lv_count.
      WHEN 1.
        lv_shet = sheet.
        ASSIGN ('DATA[]') TO <fs_tab>.
      WHEN 2.
        lv_shet = sheet1.
        ASSIGN ('DATA1[]') TO <fs_tab>.
      WHEN 3.
        lv_shet = sheet2.
        ASSIGN ('DATA2[]') TO <fs_tab>.
    ENDCASE.

    CHECK <fs_tab> IS ASSIGNED.
    "内表字段拼接成 TAB 分隔的字符串
    tabcr_ref ?= cl_abap_typedescr=>describe_by_data( <fs_tab> ).
    descr_ref ?= tabcr_ref->get_table_line_type( ).

    LOOP AT <fs_tab> ASSIGNING FIELD-SYMBOL(<wa>).
      LOOP AT descr_ref->components ASSIGNING <fs_dept>.
        lv_flg = ''.
        AT FIRST.
          lv_flg = 'X'.
        ENDAT.
        ASSIGN COMPONENT <fs_dept>-name OF STRUCTURE <wa> TO <fs>.
        IF <fs> IS INITIAL.
          lv_str = ''.
        ELSE.
          lv_str = <fs>.
        ENDIF.
        CONDENSE lv_str.
        IF lv_flg = ''.
          CONCATENATE excel_wa-line lv_str INTO excel_wa-line SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
        ELSE.
          CONCATENATE excel_wa-line lv_str INTO excel_wa-line.
        ENDIF.
      ENDLOOP.
      APPEND excel_wa TO excel_tab.
      CLEAR excel_wa.
    ENDLOOP.

    CALL METHOD OF application 'WORKSHEETS' = worksheet
    EXPORTING
      #1 = lv_shet.
    m_message.

    "激活工作表
    CALL METHOD OF worksheet 'Activate'.
    m_message.

    "选择一个单格直接粘贴内表
    CALL METHOD OF worksheet 'Cells' = h_cell
    EXPORTING #1 = i_begr #2 = i_begc.
    m_message.

    CALL METHOD OF worksheet 'Cells' = h_cell1
    EXPORTING #1 = i_begr #2 = i_begr.
    m_message.

    CALL METHOD  OF worksheet 'RANGE' = range
               EXPORTING #1 = h_cell #2 = h_cell1.
    m_message.

    CALL METHOD OF range 'SELECT'.
    m_message.

    CALL METHOD cl_gui_frontend_services=>clipboard_export
      IMPORTING
        data                 = excel_tab
      CHANGING
        rc                   = ld_rc
      EXCEPTIONS
        cntl_error           = 1
        error_no_gui         = 2
        not_supported_by_gui = 3
        OTHERS               = 4.

    CALL METHOD OF worksheet 'Paste'.
    m_message.
  ENDDO.
  IF save = 'X'.
    CALL METHOD OF application 'SAVE'.
    m_message.
  ENDIF.
  IF exit = 'X'.
    CALL METHOD OF application 'QUIT'.
    m_message.
  ENDIF.

  FREE OBJECT h_cell.     m_message.
  FREE OBJECT h_cell1.    m_message.
  FREE OBJECT range.      m_message.
  FREE OBJECT worksheet.  m_message.
  FREE OBJECT workbook.   m_message.
  FREE OBJECT application.m_message.

  "清空剪切板
  REFRESH excel_tab.
  CALL METHOD cl_gui_frontend_services=>clipboard_export
    IMPORTING
      data                 = excel_tab
    CHANGING
      rc                   = ld_rc
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.