ABAP-DOI导出EXCEL

1.输入事务代码OAOR

 2.选择表模板

 3.选择文件路径

 4.如下图就可以了

5.要在程序中创建屏幕，如图

 最后附上源代码，可以直接复制运行

REPORT y4_b4_test6.

TABLES : ekko.

*-->控件及界面定义
DATA: gv_container  TYPE scrfname VALUE 'ALV_CON',  "ALV控键定义
      g_cust_con    TYPE REF TO cl_gui_custom_container,
      g_container   TYPE REF TO cl_gui_container,
      g_control     TYPE REF TO i_oi_container_control,
      g_document    TYPE REF TO i_oi_document_proxy,
      g_spreadsheet TYPE REF TO i_oi_spreadsheet.

*-->类型池定义
DATA: g_bds_documents      TYPE REF TO cl_bds_document_set,
      gv_classname         TYPE sbdst_classname,
      gv_classtype         TYPE sbdst_classtype,
      gv_objectkey         TYPE sbdst_object_key,
      gv_doc_components    TYPE sbdst_components,
      gv_doc_signature     TYPE sbdst_signature,
      gt_bds_uris          TYPE sbdst_uri,
      gs_bds_url           LIKE LINE OF gt_bds_uris,
      gv_template_url(256) TYPE c.    "模板url存储

*-->定义写入excel时需要的ranges和contents
DATA: gt_ranges   TYPE soi_range_list,
      gs_range    TYPE soi_range_item,
      gt_contents TYPE soi_generic_table,
      gs_content  TYPE soi_generic_item.

*-->内表定义
DATA : BEGIN OF gw_ekko,
         ebeln TYPE ekko-ebeln,
         bsart TYPE ekko-bsart,
         aedat TYPE ekko-aedat,
         lifnr TYPE ekko-lifnr,
       END OF gw_ekko.
DATA : gt_ekko LIKE TABLE OF gw_ekko.

*-->辅助字段定义
DATA : ok_code TYPE sy-ucomm.

*-->选择屏幕定义
SELECTION-SCREEN BEGIN OF BLOCK blc_01 WITH FRAME TITLE TEXT-001.
  SELECT-OPTIONS : s_ebeln FOR ekko-ebeln.
SELECTION-SCREEN END OF BLOCK blc_01.

INITIALIZATION.
  "为参数赋值,此处对应事务码OAOR上传的文档信息
  gv_classname = 'HRFPM_EXCEL_STANDARD'.
  gv_classtype = 'OT'.
  gv_objectkey = 'ZDOITEST'.

START-OF-SELECTION.

  PERFORM frm_get_data.

  PERFORM frm_deal_data.

  CALL SCREEN 100.

END-OF-SELECTION.

FORM frm_get_data .
  SELECT ebeln
         bsart
         aedat
         lifnr
    FROM ekko
    INTO TABLE gt_ekko
    WHERE ebeln IN s_ebeln.
ENDFORM.

FORM frm_deal_data .

ENDFORM.

MODULE status_0100 OUTPUT.
  SET PF-STATUS '100'.
  SET TITLEBAR '100'.
  PERFORM frm_excel_output.
ENDMODULE.

MODULE user_command_0100 INPUT.

  CASE ok_code.
    WHEN '&F03' OR '&F12' OR '&F15'.
      "退出前进行对象的释放
      PERFORM release_objects.
      LEAVE TO SCREEN 0.
  ENDCASE.

ENDMODULE.

FORM frm_excel_output .

  DATA: gv_available TYPE i.
  DATA: gv_row       TYPE i.  "计算内表有多少行
  DATA: gv_col       TYPE i.  "计算内表有多少列
  DATA: row_index    TYPE i.  "数据写入时的行

  DEFINE add_content.
    gs_content-row    = &1.
    gs_content-column = &2.
    gs_content-value  = &3.
    APPEND gs_content TO gt_contents.
    CLEAR gs_content.
  END-OF-DEFINITION.
******************************开始*****************************************
*屏幕大小固定
**-->创建容器 create container control
*  CALL METHOD c_oi_container_control_creator=>get_container_control
*    IMPORTING
*      control = g_control.
*
*  IF g_cust_con IS INITIAL.
*    CREATE OBJECT g_cust_con
*      EXPORTING
*        container_name = gv_container.
*  ENDIF.
*
*  CALL METHOD g_control->init_control
*    EXPORTING
*      inplace_enabled          = 'X '
*      inplace_scroll_documents = 'X'
*      register_on_close_event  = 'X'
*      register_on_custom_event = 'X'
*      r3_application_name      = 'JYH test doi'
*      parent                   = g_cust_con.
*******************************结束*******************************************

*******************************开始*******************************************
*屏幕大小随窗口大小动态变化
  CALL METHOD c_oi_container_control_creator=>get_container_control
    IMPORTING
      control = g_control.

  DATA: container TYPE REF TO cl_gui_docking_container.
  CREATE OBJECT container
    EXPORTING
      repid                       = sy-repid
      dynnr                       = sy-dynnr
      extension                   = 2050
      side                        = cl_gui_docking_container=>property_floating
    EXCEPTIONS
      cntl_error                  = 1
      cntl_system_error           = 2
      create_error                = 3
      lifetime_error              = 4
      lifetime_dynpro_dynpro_link = 5.
  IF sy-subrc NE 0.

  ENDIF.

*-->初始化
  CALL METHOD g_control->init_control
    EXPORTING
      inplace_enabled          = 'X '
      inplace_scroll_documents = 'X'
      register_on_close_event  = 'X'
      register_on_custom_event = 'X'
      r3_application_name      = 'JYH test doi'
      parent                   = container.
********************************结束************************************

*-->url获取
  CREATE OBJECT g_bds_documents.
  CALL METHOD cl_bds_document_set=>get_info
    EXPORTING
      classname  = gv_classname  "initialization位置下定义
      classtype  = gv_classtype  "initialization位置下定义
      object_key = gv_objectkey  "initialization位置下定义
    CHANGING
      components = gv_doc_components
      signature  = gv_doc_signature.

  CALL METHOD cl_bds_document_set=>get_with_url
    EXPORTING
      classname  = gv_classname
      classtype  = gv_classtype
      object_key = gv_objectkey
    CHANGING
      uris       = gt_bds_uris
      signature  = gv_doc_signature.

  FREE g_bds_documents.

  "读取
  READ TABLE gt_bds_uris INTO gs_bds_url INDEX 1.
  gv_template_url = gs_bds_url-uri.

*-->打开上传的excel文档
  "
  CALL METHOD g_control->get_document_proxy
    EXPORTING
      document_type      = 'Excel.Sheet'
      no_flush           = 'X'
      register_container = 'X'
    IMPORTING
      document_proxy     = g_document.

  CALL METHOD g_document->open_document
    EXPORTING
      open_inplace = 'X'
      document_url = gv_template_url.

  CALL METHOD g_document->has_spreadsheet_interface
    EXPORTING
      no_flush     = 'X'
    IMPORTING
      is_available = gv_available.

  CALL METHOD g_document->get_spreadsheet_interface
    EXPORTING
      no_flush        = 'X'
    IMPORTING
      sheet_interface = g_spreadsheet.

  "选择展示的sheet页
  CALL METHOD g_spreadsheet->select_sheet
    EXPORTING
      name     = 'Sheet1'
      no_flush = 'X'.

*-->往excel中写入数据
  "检查内表是否有数据
  CHECK gt_ekko IS NOT INITIAL.

  "获取内表的行数和列数
  PERFORM frm_get_line_col USING gv_row gv_col.

  CALL METHOD g_spreadsheet->insert_range_dim
    EXPORTING
      name     = 'cell'
      no_flush = 'X'
      top      = 3        "从第几行开始
      left     = 1        "从第几列考试
      rows     = gv_row   "写几行
      columns  = gv_col.  "写几列

  "根据行列填充ranges
  CLEAR :gs_range,gt_ranges.
  gs_range-name = 'cell'.
  gs_range-rows = gv_row.
  gs_range-columns = gv_col.
  gs_range-code = 4.
  APPEND gs_range TO gt_ranges.

  "批量的把数据写入
  CLEAR row_index .
  row_index = '1'.

  "把值精确到每一行每一列的单元格
  LOOP AT gt_ekko INTO gw_ekko.
    CLEAR gs_content.
    add_content row_index 1 gw_ekko-ebeln.
    add_content row_index 2 gw_ekko-bsart.
    add_content row_index 3 gw_ekko-aedat.
    add_content row_index 4 gw_ekko-lifnr.
    row_index = row_index + 1.
  ENDLOOP.

  CALL METHOD g_spreadsheet->set_ranges_data
    EXPORTING
      ranges   = gt_ranges
      contents = gt_contents
      no_flush = 'X'.

  "把数据精确写入到某一行某一列

  PERFORM frm_write_single USING '1' '7' sy-uname.
  PERFORM frm_write_single USING '2' '7' sy-datum.

ENDFORM.

FORM frm_get_line_col  USING p_row p_col.
  CLEAR : p_row,p_col.
  FIELD-SYMBOLS <fs>.
  "获取行数
  p_row = lines( gt_ekko ).
  "获取列数
  DO.
    ASSIGN COMPONENT sy-index OF STRUCTURE gw_ekko TO <fs>.
    IF sy-subrc IS INITIAL.
      p_col = p_col + 1.
    ELSE.
      EXIT.
    ENDIF.
  ENDDO.
ENDFORM.

FORM release_objects .

  IF NOT g_document IS INITIAL.
    CALL METHOD g_document->close_document.
    FREE g_document.
  ENDIF.

  IF NOT g_control IS INITIAL.
    CALL METHOD g_control->destroy_control.
    FREE g_control.
  ENDIF.

  IF g_container IS NOT INITIAL.
    CALL METHOD g_container->free.
  ENDIF.


ENDFORM.

FORM frm_write_single  USING  p_row p_col p_value.
  DATA : lt_ranges   TYPE soi_range_list,
         ls_ranges   TYPE soi_range_item,
         lt_contents TYPE soi_generic_table,
         ls_contents TYPE soi_generic_item.

  CLEAR :lt_ranges,ls_ranges,lt_contents,ls_contents.
  "ranges中只存储一行一列
  ls_ranges-name = 'cell' .
  ls_ranges-columns = 1.
  ls_ranges-rows = 1.
  ls_ranges-code = 4.
  APPEND ls_ranges TO lt_ranges.
  "写入数据也只存一个值
  ls_contents-column = 1.
  ls_contents-row = 1.
  ls_contents-value = p_value.
  APPEND ls_contents TO lt_contents.

  "每次只写一行一列
  CALL METHOD g_spreadsheet->insert_range_dim
    EXPORTING
      name     = 'cell'
      no_flush = 'X'
      top      = p_row
      left     = p_col
      rows     = 1
      columns  = 1.

  CALL METHOD g_spreadsheet->set_ranges_data
    EXPORTING
      ranges   = lt_ranges
      contents = lt_contents
      no_flush = 'X'.
ENDFORM.