ABAP-Excel上传下载

OLE

早期 SAP 解决与 Office 集成的技术。
OLE 语法参照 VBA，在 ABAP 中调用方法来操作 Excel。
语法的友好性差；另外将数据从 ABAP 内表写入到 Excel ，速度特别慢，性能难以接受。

方法:

创建实例
- ```
CREATE OBJECT name_obj  'app'
```
设置属性
- ```
SET PROPERTY OF name_obj 'XXX' = f .
```
读取属性
- ```
GET PROPERTY OF name_obj ‘XXX' = f .
```

调用实例方法

CALL METHOD OF
  name_obj 
    'XXX'    = f    "由f来接收返回值
  EXPORTING
    #1       = f1. "调用name_obj的方法xxx 传入参数f1…fn

释放实例
- ```
FREE OBJECT name_obj . "释放.
```

对象

DATA: GO_EXCEL  TYPE OLE2_OBJECT, "Excel
      GO_BOOKS  TYPE OLE2_OBJECT, "工作簿
      GO_BOOK   TYPE OLE2_OBJECT, "工作表
      GO_CELL   TYPE OLE2_OBJECT. "单元格

OLE-获取模板

SMW0 上传的模板，直接下载 MIME 数据到本地

DATA:
  lv_fname TYPE rlgrap-filename,
  lv_key   TYPE wwwdatatab,
  lv_rc    TYPE i,
  lv_objid TYPE wwwdata-objid VALUE 'Z9050'.

SELECT SINGLE relid FROM wwwdata
  INTO @DATA(lv_relid)
 WHERE relid = @lc_mi
   AND objid = @lv_objid
   AND srtf2 = 0.
IF sy-subrc <> 0.
  MESSAGE i000 WITH text-e03 DISPLAY LIKE zp3ps_e." Check template
  EXIT.
ENDIF.

lv_fname = pv_fullpath.
CONCATENATE lc_mi lv_objid INTO lv_key.

CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
  EXPORTING
    key         = lv_key
    destination = lv_fname
  IMPORTING
    rc          = lv_rc.

OLE-打开 Excel 并激活工作表　

"打开Excel　　
CREATE OBJECT GO_EXCEL ’Excel.Application’.
"设置Excel可见
SET PROPERTY OF GO_EXCEL ’Visible’ = 1.
"创建空白工作簿
CALL METHOD OF GO_EXCEL 'Workbooks' = GO_BOOKS.
"创建工作表
CALL METHOD OF GO_BOOKS 'Add' = GO_BOOK.
"选择工作表
CALL METHOD OF GO_EXCEL 'Worksheets' = GO_BOOK
  EXPORTING
    #1 = 1.
"设置工作表名
SET PROPERTY OF GO_BOOK 'Name' = 'Sheet1'.
"激活工作表
CALL METHOD OF GO_BOOK 'Activate'.

OLE-选择单元格赋值

通过将数据复制到粘贴板进行数据插入

"清空单元格内容　　
CALL METHOD OF GO_EXCEL 'Cells' = LO_CELL1    
  EXPORTING
    #1 = 1
    #2 = 1.
  
GET PROPERTY OF GO_EXCEL 'ActiveCell' = LO_CELL2.

CALL METHOD OF LO_CELL2 'SpecialCells' = LO_CELL3
  EXPORTING    
    #1 = '11'. "'xlLastCell'.
  
CALL METHOD OF GO_EXCEL 'Range' = GO_RANGE    
  EXPORTING
    #1 = LO_CELL1
    #2 = LO_CELL3.

CALL METHOD OF GO_RANGE 'ClearContents'. 
"将要下载的表格复制到剪贴板
CL_GUI_FRONTEND_SERVICES=>CLIPBOARD_EXPORT(
  IMPORTING
    DATA                 = LT_CLIPTAB[]
  CHANGING
    RC                   = LV_RD
  EXCEPTIONS
    CNTL_ERROR           = 1    
    ERROR_NO_GUI         = 2    
    NOT_SUPPORTED_BY_GUI = 3    
    OTHERS               = 4 ).
"粘贴数据
CALL METHOD OF GO_EXCEL 'Cells' = LO_CELL1
  EXPORTING
    #1 = 1
    #2 = 1.
  
CALL METHOD OF GO_EXCEL 'Cells' = LO_CELL2
  EXPORTING
    #1 = LV_ROW
    #2 = LV_COL.
  
CALL METHOD OF GO_EXCEL 'Range' = GO_RANGE
  EXPORTING
    #1 = LO_CELL1
    #2 = LO_CELL2.
  
CALL METHOD OF GO_RANGE 'Select'.
CALL METHOD OF GO_SHEET 'Paste'.

OLE-设置单元格格式

单元格内设置公式，例如，合计数据
- 行合计：PV_VAL = ‘=SUM(RC[1]:RC[7]) ’ 合计第一列到第七列数据
- 列合计：PV_VAL = ‘=SUM(R[1]C:R[7]C) ’ 合计第一行到第七行数据

设置单个单元格数据

CALL METHOD OFGO_EXCEL 'Cells'  = LO_CELL
  EXPORTING
    #1       = PV_ROW
    #2       = PV_COL.

SET PROPERTY OF LO_CELL 'Value' = PV_VAL.
FREE OBJECT LO_CELL. 
"数据类型 '@'：常规；'#,##0' ：金额
SET PROPERTY OF GO_RANGE 'NumberFormat' = '@' . 
"对齐方式 2：左对齐 3：居中 4:右对齐
SET PROPERTY OF GO_RANGE 'HorizontalAlignment' = 2.
"颜色 Eg：15 浅灰  16深灰 6 黄色
CALL METHOD OF GO_RANGE 'Interior' = GO_CELLINT.  
SET PROPERTY OF GO_CELLINT 'Colorindex' = PV_COLOR. 
"边框
CALL METHOD OF GO_RANGE 'Borders' = GO_BORDER.
SET PROPERTY OF GO_BORDER 'Weight' = 2.
SET PROPERTY OF GO_BORDER 'Linestyle' = 1. 
"自适应宽度
CALL METHOD OF GO_EXCEL 'Columns' = GO_COLUMNS.
CALL METHOD OF GO_COLUMNS 'AutoFit'.
"执行宏
CALL METHOD OF GO_EXCEL 'RUN' EXPORTING #1 = 'ZMAC'.

OLE-保存文件

"保存文件　　
CALL METHOD OF GO_BOOKS 'Save'.
"另存为
CALL METHOD OF GO_BOOKS 'SaveAs' 
  EXPORTING 
    #1 = 'c:\test.xls' 
    #2 = 1.
"关闭工作区
CALL METHOD OF GO_BOOKS 'Close'.
"退出Excel
CALL METHOD OF GO_EXCEL 'Quit'.

DOI

DOI 是 Desktop Office Integration 的缩写，是 SAP 提供的解决与 Office 集成的技术方案。
DOI 可以看作 OLE 的替代品，用面向对象的方式实现。
很好的解决了 OLE 的两个问题。

为了操作 Excel 文档，至少需要以下四个对象：
- Container
  - 存放 Excel 电子表格 (spreadsheet) 的容器。这个应该比较容易理解，容器一般在 dialog screen 中定义，也可以直接使用 ABAP 程序中默认的 screen（即 screen 号码为 1000 的屏幕)
- Container Control
- 容器中用于创建和管理其他 Office 集成所需要的对象，container control 是一个接口。
  
  类型为 i_oi_container_control。
- Document Proxy
  - 每一个 document proxy 的实例代表用 office application 打开的文档，可以是 Excel，也可以是 Word，如果想打开多个文档，需要定义多个实例。document proxy 是一个接口。
    
    类型为 i_oi_document_proxy。
- Spreadsheet
  - spreadsheet 接口，代表最终要操作的 Excel 文档。
    
    类型是 i_oi_spreadsheet
- business document set
  - 如果读取服务器上的文档模板，用 cl_bds_document_set 类，将 business document set 缩写为 bds，bds 用于管理要操作的文档，可以包含一个或多个文档。

DOI-打开 Excel

实例化 container control、container 和 Document Proxy

DATA:GO_CONTROL     TYPE REF TO I_OI_CONTAINER_CONTROL,
     GO_DOCU_PROXY  TYPE REF TO I_OI_DOCUMENT_PROXY,
     GO_SHEET       TYPE REF TO I_OI_SPREADSHEET,
     GO_ERROR       TYPE REF TO I_OI_ERROR. 
"实例化container control、container和Document Proxy
c_oi_container_control_creator=>get_container_control(
    IMPORTING
      control = go_control
      error   = go_error ).
  go_error->raise_message( EXPORTING type = zp3ps_e ).

  go_control->init_control(
    EXPORTING
      r3_application_name      = 'EXCEL CONTAINER'
      inplace_enabled          = space
      inplace_resize_documents = zp3ps_x
      inplace_scroll_documents = zp3ps_x
      inplace_show_toolbars    = zp3ps_x
      parent                   = cl_gui_container=>screen0
      register_on_close_event  = space
      register_on_custom_event = space
    IMPORTING
      error                    = go_error ).
  go_error->raise_message( EXPORTING type = zp3ps_e ).

  DATA lv_doc_type(80) VALUE soi_doctype_excel_sheet . " Excel.Sheet
  go_control->get_document_proxy(
    EXPORTING
      document_type  = lv_doc_type 
    IMPORTING
      document_proxy = go_docu_proxy
      error          = go_error ).
  go_error->raise_message( EXPORTING type = zp3ps_e ).

DOI-新建空白工作簿

对于有模板的，可以通过打开模板进行后续操作；没有模板的，需要创建一个空白工作簿，如下

DATA:lv_temp TYPE i.
IF pv_create IS NOT INITIAL.
  go_docu_proxy->create_document(
    EXPORTING
      document_title = 'excel'
      open_inplace   = zp3ps_x
      no_flush       = zp3ps_x
    IMPORTING
      error          = go_error ).
  go_error->raise_message( EXPORTING type = zp3ps_e ).
ENDIF.

打开工作簿后，需要实例化工作表对象

*Indicates Whether Interface Is Supported For This Doc. Type
  go_docu_proxy->has_spreadsheet_interface(
    IMPORTING
      is_available = lv_temp ).

  CHECK lv_temp = 1.
*Returns an Interface Reference
  go_docu_proxy->get_spreadsheet_interface(
    IMPORTING
      error           = go_error
      sheet_interface = go_sheet ).
  IF go_error->has_failed = zp3ps_x.
    go_error->raise_message( EXPORTING type = zp3ps_e ).
  ENDIF.

DOI-获取模板

方法一：SMW0 上传的模板，获取模板数据到内表进行打开

DATA:
    lt_doc_table      LIKE  TABLE OF w3mime,
    lv_doc_size       TYPE  i,
    lv_doc_type(80)   VALUE soi_doctype_excel_sheet,
    lv_doc_format(80) TYPE  c,
    lv_retcode        TYPE soi_ret_string,
    lv_objid          TYPE wwwdata-objid VALUE 'Z9050'.

  CALL FUNCTION 'SAP_OI_LOAD_MIME_DATA'
    EXPORTING
      object_id        = lv_objid
    IMPORTING
      data_size        = lv_doc_size
      document_format  = lv_doc_format
      document_type    = lv_doc_type
    TABLES
      data_table       = lt_doc_table
    EXCEPTIONS
      object_not_found = 1
      internal_error   = 2
      OTHERS           = 3.

  go_docu_proxy->open_document_from_table(
    EXPORTING
      document_table = lt_doc_table[]
      document_size  = lv_doc_size
      no_flush       = zp3ps_x
    IMPORTING
      retcode        = lv_retcode ).

  IF lv_retcode <> c_oi_errors=>ret_ok.
    MESSAGE i000 WITH text-e02 DISPLAY LIKE zp3ps_e.  EXIT.
  ENDIF.

方法二：OAOR 上传的模板，获取模板到 URL 进行打开

DATA:
  lo_bds_documents TYPE REF TO                              cl_bds_document_set,

  lv_classname TYPE sbdst_classname  
               VALUE 'HRFPM_EXCEL_STANDARD',

  lv_classtype TYPE sbdst_classtype VALUE 'OT',

  lv_objectkey TYPE sbdst_object_key 
               VALUE 'DOITEST',

  lv_doc_components TYPE sbdst_components,
  lv_doc_signature  TYPE sbdst_signature.

DATA:
  lt_bds_uris TYPE sbdst_uri,
  ls_bds_url  LIKE LINE OF lt_bds_uris,
  lv_url(256) TYPE c.

CREATE OBJECT lo_bds_documents.

cl_bds_document_set=>get_info(
  EXPORTING
    classname  = lv_classname
    classtype  = lv_classtype
    object_key = lv_objectkey
  CHANGING
    components = lv_doc_components
    signature  = lv_doc_signature ).
    
cl_bds_document_set=>get_with_url(
  EXPORTING
    classname  = lv_classname
    classtype  = lv_classtype
    object_key = lv_objectkey
  CHANGING
    uris       = lt_bds_uris
    signature  = lv_doc_signature ).

FREE lo_bds_documents.

READ TABLE lt_bds_uris INTO ls_bds_url 
INDEX 1.
lv_url = ls_bds_url-uri.

go_docu_proxy->open_document(
  EXPORTING
    document_title = 'excel'
    document_url   = lv_url
    open_inplace   = space
  IMPORTING
    error          = go_error ).
IF go_error->has_failed = zp3ps_x
  go_error->raise_message( 
            EXPORTING type = zp3ps_e ).
ENDIF.

DOI-激活工作表

获取所有工作表名

DATA: lt_sheets TYPE soi_sheets_table.
 go_sheet->get_sheets(
   IMPORTING
     sheets = lt_sheets
     error  = go_error ).

修改工作表名

"修改Sheet1为Header
go_sheet->set_sheet_name(
    EXPORTING newname  = 'Header'
              oldname  = 'Sheet1'
    IMPORTING error    = go_error ).

删除工作表

"删除Sheet2
go_sheet->delete_sheet( 
   EXPORTING name  = 'Sheet2'
   IMPORTING error = go_error ).

新增工作表

"新增的sheet会放在当前激活sheet前
go_sheet->add_sheet(
   EXPORTING name  = 'Item'
   IMPORTING error = go_error ).

选择工作表

"选择Header进行后续操作
go_sheet->select_sheet(
    EXPORTING
      name     = 'Header'
      no_flush = 'X'
    IMPORTING
      error    = go_error ).

DOI-选择单元格赋值

方法 1：通过函数获取内表字段类型，然后将整个表格插入到 Excel 中

DATA:lv_line       TYPE i,
     lv_column     TYPE i,
     lt_fields     LIKE TABLE OF rfc_fields. 

  DESCRIBE TABLE gt_main LINES lv_line.
  DESCRIBE TABLE gt_fieldcat LINES lv_column.

* Insert space range
  go_sheet->insert_range_dim(
    EXPORTING
      name     = 'DATA'
      top      = 2
      left     = 1
      rows     = lv_line   " total row
      columns  = lv_column " total column
      updating = 0
    IMPORTING
      error    = go_error ).
* Get fileds
  CALL FUNCTION 'DP_GET_FIELDS_FROM_TABLE'
    TABLES
      data             = gt_main
      fields           = lt_fields
    EXCEPTIONS
      dp_invalid_table = 1
      OTHERS           = 2.

*  Insert a Table
  go_sheet->insert_one_table(
    EXPORTING
      data_table   = gt_main[]
      fields_table = lt_fields
      rangename    = 'DATA'
      wholetable   = 'X'
      updating     = 1 ).

方法 2：通过设置行，列，值的方式，将数据处理到结果表中,可以用来处理数据量较少或未处理数据，例如字段标题

DATA:lv_column TYPE i,
     lt_ranges TYPE soi_range_list,
     ls_ranges TYPE soi_range_item,
     lt_title  TYPE soi_generic_table,
     ls_title  TYPE soi_generic_item.

DESCRIBE TABLE gt_fieldcat LINES lv_column.

* Select range
REFRESH: lt_ranges,lt_title.
CLEAR ls_ranges.
ls_ranges-name    = 'TITLE'.
ls_ranges-columns = lv_column.
ls_ranges-rows    = 1.
ls_ranges-code    = 1.
APPEND ls_ranges TO lt_ranges.
  
LOOP AT gt_fieldcat INTO gs_fieldcat.
  CLEAR ls_title.
  ls_title-row = 1.
  ls_title-column = sy-tabix.
  ls_title-value  = gs_fieldcat-scrtext_l.
  APPEND ls_title TO lt_title.
ENDLOOP.

CALL METHOD go_sheet->set_ranges_data
  EXPORTING
    ranges   = lt_ranges
    contents = lt_title
  IMPORTING
    error    = go_error.

DOI-设置单元格格式

单元格分区

DATA:lt_ranges TYPE soi_full_range_table.
lt_ranges[] = VALUE #(
( name = 'TITL' top = 1 left = 1 rows = 1 columns = 9 )
( name = 'BODY' top = 2 left = 1 rows = lv_rows columns = 9 )
( name = 'KEY'  top = 2 left = 1 rows = lv_rows columns = 2 ) ).
go_sheet->insert_ranges( EXPORTING ranges = lt_ranges
                         IMPORTING error  = go_error ).
"工作表分为3块：
"A1:I1：TITL
"A2:I?： BODY
"A2:B?： KEY

字体设置

go_sheet->set_font( EXPORTING rangename = 'BODY'
                              family    = 'Arial'
                              size      = 20
                              bold      = 1
                              italic    = 0
                              align     = 2
                    IMPORTING error     = go_error ).
"字体：Arial
"字体大小：20
"字体加粗：0 不加粗；  1 加粗
"斜体：0 非斜体；  1 斜体
"对齐方式：0 左对齐；1  居中；2 右对齐

单元格设置

DATA:lt_format TYPE soi_format_table.
lt_format[] = VALUE #( ( name = 'KEY' front = 3 back = 6 bold = 1 
                         frametyp = 127 framecolor = 4 ) ).
go_sheet->set_ranges_format( EXPORTING formattable = lt_format 
                             IMPORTING error       = go_error ). 
"设置KEY 区域
"字体颜色为红色，
"背景颜色为黄色，
"字体加粗，
"并添加绿色边框

宽度自适应

go_sheet->fit_widest( EXPORTING name  = 'TITL'
                      IMPORTING error = go_error ).

单元格边框

go_sheet->set_frame( EXPORTING rangename = 'TITL'
                               typ       = '127'
                               color     = '1'
                     IMPORTING error     = go_error ).

执行宏

go_docu_proxy->execute_macro( EXPORTING macro_string = 'MACRO1' ).

DOI-保存文件

保存文档到本地

IF go_docu_proxy IS NOT INITIAL.
  go_docu_proxy->save_as(
    EXPORTING
      file_name = pv_filename
    IMPORTING
      error     = go_error ).
ENDIF.

保存文档到 url

go_docu_proxy->save_document_to_url(
    EXPORTING
      url           = lv_url
    IMPORTING
      error         = go_error
    CHANGING
      document_size = lv_size ).

保存文档到内表

go_docu_proxy->save_document_to_table(
   IMPORTING
     error          = go_error
   CHANGING
     document_size  = lv_size
     document_table = lt_table ).

退出文档，销毁对象

IF go_docu_proxy NOT IS INITIAL.
  go_docu_proxy->close_document( ).
  FREE go_docu_proxy.
ENDIF.

IF go_control NOT IS INITIAL.
  go_control->destroy_control( ).
  FREE go_control.
ENDIF.

DOI-ALV Tree 下载

对于 Tree 的下载，可以使用 DOI 的 SET_HIERARCHY_TABLE 方法设置 Excel 的层级结构

DATA: gt_hiera TYPE soi_hierarchy_table.
 CALL METHOD go_sheet->set_hierarchy_table
   EXPORTING
     table = gt_hiera.
"传入参数GT_HIERA的结构如下：
TYPES: BEGIN OF SOI_HIERARCHY_ITEM,
         FIRSTLINE TYPE I,
         LENGTH TYPE I,
         SHEET(128) TYPE C,
         MODE TYPE I,
       END OF SOI_HIERARCHY_ITEM. 
"需要设置层级开始行，层级包含的行数，和层级结构使用的sheet名
"[注] 多个层级时需要添加空行，否则连续层级会并到一起
"传入参数的处理逻辑可以参考程序YHY_BCALV_TREE中的FORM GET_DOWNLOAD_DATA .

XML

用 Open Office 技术，实现与 ALV 自带下载功能一致的下载效果

使用方法简单，适合于下载 ALV 报表，可以直接使用 ALV 设置的 FIELDCATALOG, LAYOUT, SORT 等属性对下载数据设置格式，无需其他数据转换

选择下载格式

DATA:
  ls_xml_choice TYPE 
  if_salv_bs_xml=>s_type_xml_choice,
  lt_xml_choice TYPE 
  if_salv_bs_xml=>t_type_xml_choice,
  lv_xml        TYPE xstring,
  r_result_data TYPE REF TO cl_salv_ex_result_data_table,
  lo_dref       TYPE REF TO data,
  lv_flavour    TYPE string,
  lv_version    TYPE string. 

lt_xml_choice = cl_salv_export_xml_dialog=>get_gui_spreadsheet_formats(  ).
SORT lt_xml_choice BY xml_type.
READ TABLE lt_xml_choice INTO ls_xml_choice 
WITH KEY xml_type = '10' BINARY SEARCH.

根据 ALV 相关设置,format 数据

GET REFERENCE OF gt_main[] INTO lo_dref.
r_result_data = cl_salv_ex_util=>factory_result_data_table(
                  r_data            = lo_dref
                s_layout          = gs_layout 
                t_fieldcatalog    = gt_fieldcat
                t_sort            = gt_sort ).

转换数据为 XML 格式

CASE cl_salv_bs_a_xml_base=>get_version( ).
  WHEN if_salv_bs_xml=>version_25.
    lv_version = if_salv_bs_xml=>version_25.
  WHEN if_salv_bs_xml=>version_26.
    lv_version = if_salv_bs_xml=>version_26.
ENDCASE.

lv_flavour = if_salv_bs_c_tt=>c_tt_xml_flavour_export.
CALL METHOD cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform
  EXPORTING
    xml_type      = ls_xml_choice-xml_type
    xml_version   = lv_version
    r_result_data = r_result_data
    xml_flavour   = if_salv_bs_c_tt=>c_tt_xml_flavour_export
  IMPORTING
    xml           = lv_xml.

下载数据并打开

GET REFERENCE OF gt_main[] INTO lo_dref.
r_result_data = cl_salv_ex_util=>factory_result_data_table(
                r_data          = lo_dref
                t_fieldcatalog  = gt_fieldcat
                s_layout        = gs_layout ).

通过 XML 方式下载的数据，只需要定义好 FIELDCATALOG 等属性就可以适应字段转换，但是其他方式下载，需要进行一定转换

"有单位字段,例如金额，参照货币转换为外部格式和千分位，并将负号提前
WRITE gt_main-price TO lv_price CURRENCY gt_main-currency.
CONDENSE: lv_price.
"数值字段 以文本格式存储的数值，需要将负号提前，必要时增加千分位
"负号提前：使用函数CLOI_PUT_SIGN_IN_FRONT
"千分位：WRITE lv_number TO lv_char.
"日期格式 格式适应外部格式
"1.使用函数转换日期格式：CONVERT_DATE_TO_EXTERNAL将日期格式转换为User Profile设置的日期格式
"2.使用掩码：WRITE lv_time TO lv_char USING EDIT MASK '__:__:__'.
"含内外码
"根据字段的convexit属性转换成外码，例如N类型前导0，项目号等
"去掉前导0：SHIFT lv_char LEFT DELETING LEADING zp3ps_num_0.
"其他含转换的字段：WRITE lv_proj USING EDIT MASK '==ABPSN'.

文件路径获取

获取文件保存路径

FORM get_download_file  USING  pv_fullpath TYPE string.
  DATA:lv_fname    TYPE string,
       lv_path     TYPE string,
       lv_def_path TYPE string.

  CALL METHOD cl_gui_frontend_services=>directory_get_current “获取当前默认路径
    CHANGING
      current_directory = lv_def_path.

  CALL METHOD cl_gui_frontend_services=>file_save_dialog      “弹框选择保存路径
    EXPORTING
      window_title              = 'Excel Download'
      default_extension         = 'XLSX'
      file_filter               = cl_gui_frontend_services=>filetype_excel
      initial_directory         = lv_def_path
    CHANGING
      filename                  = lv_fname
      path                      = lv_path
      fullpath                  = pv_fullpath
    EXCEPTIONS
      cntl_error                = 1
      error_no_gui              = 2
      not_supported_by_gui      = 3
      invalid_default_file_name = 4
      OTHERS                    = 5.

WINDOW_TITLE: 设置弹出屏幕标题

DEFAULT_FILE_NAME：默认文件名

WITH_ENCODING：'X'显示 Encoding 选择框

FILE_FILTER：限制文件类型

判断文件路径可用性

DATA: lv_rc TYPE i.
CALL METHOD cl_gui_frontend_services=>file_exist
  EXPORTING
    file                 = pv_fullpath
  RECEIVING
    result               = DATA(lv_res)
  EXCEPTIONS
    cntl_error           = 1
    error_no_gui         = 2
    wrong_parameter      = 3
    not_supported_by_gui = 4
    OTHERS               = 5.

IF lv_res EQ zp3ps_x. " If the file already exists...
  cl_gui_frontend_services=>file_delete(
                                  EXPORTING filename = pv_fullpath
                                  CHANGING  rc = lv_rc ).
  IF sy-subrc NE 0.
    MESSAGE i000 WITH text-e01 DISPLAY LIKE 'E'.
    " File already open. check please.
    RETURN.
  ENDIF.
ENDIF.

Excel 上传

GUI

通过函数 GUI_UPLOAD 或者 cl_gui_frontend_services 下的 gui_download 方法。

该方法需要设置 codepage 来适应语言，容易出现乱码，更适合 TXT,CSV 等其他文件的上传

通过 GUI_UPLOAD 上传 TXT 文档，输出表字段类型应为字符型

根据结果表创建一个字段类型为字符型的内表

DATA:lo_table    TYPE REF TO cl_abap_tabledescr,
     lo_struc    TYPE REF TO cl_abap_structdescr,
     lt_data     TYPE REF TO data,
     lv_length   TYPE i.
FIELD-SYMBOLS: <ft_tab> TYPE STANDARD TABLE.

lo_table ?= cl_abap_tabledescr=>describe_by_data( gt_main[] ).
lo_struc ?= lo_table->get_table_line_type( ).
DATA(lt_components) = lo_struc->components.

DATA(lt_fieldcat) = VALUE lvc_t_fcat( FOR ls_components IN lt_components
( fieldname = ls_components-name inttype = 'C' intlen = '1024' ) ).

cl_alv_table_create=>create_dynamic_table(
  EXPORTING
    it_fieldcatalog           = lt_fieldcat
  IMPORTING
    ep_table                  = lt_data ).
ASSIGN lt_data->* TO <ft_tab>.

cl_gui_frontend_services=>gui_upload(
  EXPORTING
    filename                = CONV #( pv_fname )
    filetype                = 'ASC'
    has_field_separator     = 'X'
    codepage                = '8500'
  IMPORTING
    filelength              = lv_length
  CHANGING
    data_tab                = <ft_tab> ).

DOI

通过函数 TEXT_CONVERT_XLS_TO_SAP 上传数据

该函数内部是通过 DOI 的方式获取数据的

获取数据可以直接存到最终需要表，但是不建议直接存，防止非法数据导致错误，例如日期时间格式等，比较适合纯文本的数据

通过 TEXT_CONVERT_XLS_TO_SAP 上传 Excel 文档

如果直接输出到日期时间字段，日期格式需满足‘YYYYMMDD’时间格式需满足‘HHMMSS’，输出到数值字段，不能有负数和千分为符

FORM upload_data_doi USING pv_fname TYPE rlgrap-filename.
  DATA: lt_data     LIKE TABLE OF gt_main,
        lt_raw_data TYPE truxs_t_text_data.

  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
      i_line_header        = 'X'
      i_tab_raw_data       = lt_raw_data
      i_filename           = pv_fname
    TABLES
      i_tab_converted_data = lt_data
    EXCEPTIONS
      conversion_failed    = 1
      OTHERS               = 2.
ENDFORM.

OLE

通过函数 ALSM_EXCEL_TO_INTERNAL_TABLE 上传数据

该函数内部是通过 OLE 的方式获取数据的

获取数据结果为行，列，值的形式，需要自行转换到内表中，且值限制为 50 位

同类扩展函数有 ZP3CMZ_ALSM_EXCEL_TO_ITAB，可以选择多个 Sheet

通过 ALSM_EXCEL_TO_INTERNAL_TABLE 上传 Excel 文档

需要指定开始和结束行列,值限制 50 位，扩展函数 ZP3CMZ_ALSM_EXCEL_TO_ITAB 放大字符限制为 200 位

DATA: lt_data LIKE TABLE OF alsmex_tabline.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  EXPORTING
    filename                = pv_fname
    i_begin_col             = '1'
    i_begin_row             = '2'
    i_end_col               = '9'
    i_end_row               = '5000'
  TABLES
    intern                  = lt_data
  EXCEPTIONS
    inconsistent_parameters = 1
    upload_ole              = 2
    OTHERS                  = 3.

Excel 上传-文件路径获取

弹出文件选择窗口获取文件路径

FORM upload_filename_get  CHANGING pv_fname TYPE rlgrap-filename  pv_flag  TYPE c.
  DATA: lv_def_path     TYPE string,
        lt_file_table   TYPE filetable,
        lv_rc           TYPE i,
        lv_action       TYPE i.

  cl_gui_frontend_services=>directory_get_current(
    CHANGING current_directory = lv_def_path ).

  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      window_title            = 'Upload'
      default_extension       = 'XLS'
      default_filename        = 'Export'
      file_filter             = cl_gui_frontend_services=>filetype_excel
      initial_directory       = lv_def_path
    CHANGING
      file_table              = lt_file_table
      rc                      = lv_rc
      user_action             = lv_action
    EXCEPTIONS
      file_open_dialog_failed = 1
      cntl_error              = 2
      error_no_gui            = 3
      not_supported_by_gui    = 4
      OTHERS                  = 5.
  IF sy-subrc <> 0.
    MESSAGE s005(sbcs_send) DISPLAY LIKE zp3ps_e." Error when uploading the document
    pv_flag = zp3ps_x. RETURN.
  ENDIF.
          
  IF lv_action = cl_gui_frontend_services=>action_cancel.  “用户取消上传操作
    pv_flag = zp3ps_x. RETURN.
  ENDIF.

  CHECK lt_file_table IS NOT INITIAL.
  READ TABLE lt_file_table INTO DATA(ls_file) INDEX 1.
  CHECK sy-subrc EQ 0.
  IF strlen( ls_file ) GE 128.                             “文件路径长度不可大于128
    MESSAGE s000 WITH text-e02 DISPLAY LIKE zp3ps_e. RETURN.
    " The length of file path should be less than 128
  ELSE.
    pv_fname = ls_file.
    TRANSLATE pv_fname TO UPPER CASE.
  ENDIF.
ENDFORM.

文档选择的 Search Help

PARAMETERS: p_file LIKE rlgrap-filename DEFAULT 'C:\'.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  CALL FUNCTION 'F4_FILENAME'
    EXPORTING
      field_name = 'C:\Actual M/M Approval.xlsx'
    IMPORTING
      file_name  = p_file.

Excel 上传数据检查

以 OLE 方式上传的数据为例，进行数据有效性检查和转化

"获取字段属性用于有效性校验
FORM processing_data TABLES pt_data STRUCTURE alsmex_tabline.
  DATA:BEGIN OF lt_refname OCCURS 0,
         ref_fieldname LIKE dd03p-fieldname,
         ref_tabname   LIKE dd03p-tabname,
       END OF lt_refname,
       lt_fieldcat TYPE slis_t_fieldcat_alv.
  CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE'
    EXPORTING
      i_program_name         = sy-repid
      i_internal_tabname     = 'GT_MAIN'
      i_client_never_display = zp3ps_x
      i_inclname             = sy-repid
      i_bypassing_buffer     = zp3ps_x
      i_buffer_active        = zp3ps_x
    CHANGING
      ct_fieldcat            = lt_fieldcat
    EXCEPTIONS
      inconsistent_interface = 1
      program_error          = 2
      OTHERS                 = 3.

  MOVE-CORRESPONDING lt_fieldcat TO lt_refname[].
  SORT lt_refname BY ref_tabname ref_fieldname.
  DELETE ADJACENT DUPLICATES FROM lt_refname COMPARING ref_tabname ref_fieldname.
  IF lt_refname[] IS NOT INITIAL.
    SELECT * FROM dd03n INTO TABLE @DATA(lt_dd03n)
       FOR ALL ENTRIES IN @lt_refname
     WHERE tabname = @lt_refname-ref_tabname
       AND fieldname = @lt_refname-ref_fieldname.
  ENDIF.
  "根据字段属性转化为内部格式
  DATA:lt_main LIKE TABLE OF gt_main,
       ls_main LIKE LINE OF lt_main,
       lv_function TYPE rs38l_fnam.
  FIELD-SYMBOLS: <fv_field>.

  SORT: pt_data BY row col,
        lt_fieldcat BY col_pos,
        lt_dd03n BY tabname fieldname.
  LOOP AT pt_data INTO DATA(ls_data).
    ASSIGN COMPONENT ls_data-col OF STRUCTURE ls_main TO <fv_field>.
    CHECK sy-subrc = 0.
    READ TABLE lt_fieldcat WITH KEY col_pos = ls_data-col
    INTO DATA(ls_fieldcat) BINARY SEARCH.
    CHECK sy-subrc = 0.

    PERFORM check_data_type USING ls_fieldcat ls_data CHANGING ls_main.

    READ TABLE lt_dd03n WITH KEY tabname = ls_fieldcat-ref_tabname
    fieldname = ls_fieldcat-ref_fieldname INTO DATA(ls_dd03n) BINARY SEARCH.
    IF ls_dd03n-convexit IS NOT INITIAL.
      CONDENSE ls_dd03n-convexit.
      CONCATENATE 'CONVERSION_EXIT_' ls_dd03n-convexit '_INPUT' INTO lv_function.
      CALL FUNCTION lv_function
        EXPORTING
          input  = <fv_field>
        IMPORTING
          output = <fv_field>.
    ENDIF.
    AT END OF row.
      PERFORM convert_curr_to_internal USING lt_fieldcat CHANGING ls_main.
      APPEND ls_main TO lt_main. CLEAR ls_main.
    ENDAT. 
  ENDLOOP.          
ENDFORM.

"检查数据类型是否符合(日期类型字段)
FORM check_data_type  USING    ls_fieldcat TYPE slis_fieldcat_alv
                               ls_data TYPE alsmex_tabline
                      CHANGING ls_main LIKE gt_main.
  DATA: lv_year  TYPE n LENGTH 4,
        lv_month TYPE n LENGTH 2,
        lv_day   TYPE n LENGTH 3,
        lv_time  TYPE n LENGTH 6.
  FIELD-SYMBOLS: <fv_field>.
  ASSIGN COMPONENT ls_fieldcat-fieldname OF STRUCTURE ls_main TO <fv_field>.
  CHECK sy-subrc = 0.

  CASE ls_fieldcat-inttype.
    WHEN zp3ps_d.
      CLEAR: lv_year, lv_month, lv_day.
      SPLIT ls_data-value AT zgfi7_demarcation INTO lv_year lv_month lv_day.
      IF strlen( lv_year ) <> 4.
        MESSAGE s000 WITH 'date format must YYYY/MM/DD' DISPLAY LIKE zp3ps_e.
        RETURN.
      ENDIF.

      <fv_field> = lv_year && lv_month && lv_day.
      CALL FUNCTION 'DATE_CHECK_PLAUSIBILITY'
        EXPORTING
          date                      = <fv_field>
        EXCEPTIONS
          plausibility_check_failed = 1
          OTHERS                    = 2.
      IF sy-subrc <> 0.
        MESSAGE s000 WITH 'Invalid Date' DISPLAY LIKE zp3ps_e.  RETURN. 
      ENDIF.
    WHEN zp3ps_t.
      TRANSLATE ls_data-value USING ': '.
      CONDENSE ls_data-value NO-GAPS.
      lv_time = ls_data-value.

      <fv_field> = lv_time.
      CALL FUNCTION 'TIME_CHECK_PLAUSIBILITY'
        EXPORTING
          time                      = <fv_field>
        EXCEPTIONS
          plausibility_check_failed = 1
          OTHERS                    = 2.
      IF sy-subrc <> 0.
        MESSAGE s000 WITH 'Invalid Time' DISPLAY LIKE zp3ps_e.
        RETURN. 
      ENDIF.
    WHEN zp3ps_p.
      REPLACE ALL OCCURRENCES OF ',' IN ls_data-value WITH ''.
      IF cl_abap_matcher=>matches(
         pattern = '^(-?([1-9]\d*|0)(\.\d*)?)$'
         text = ls_data-value ) = abap_false.
        MESSAGE s000 WITH 'Invalid data type.' DISPLAY LIKE zp3ps_e.
        RETURN.
      ENDIF.

      <fv_field> = ls_data-value.
    WHEN OTHERS.
      <fv_field> = ls_data-value.
  ENDCASE.
ENDFORM.   
"检查是否有金额字段，转化金额为内部格式
FORM convert_curr_to_internal  USING pt_fieldcat TYPE slis_t_fieldcat_alv
                            CHANGING ps_main LIKE gt_main.
  DATA:lv_amount TYPE bapicurr-bapicurr,
       lv_waers  TYPE tcurc-waers,
       lv_length TYPE i.
  FIELD-SYMBOLS: <fv_amount> TYPE any,
                 <fv_waers>  TYPE any.

  READ TABLE pt_fieldcat WITH KEY datatype = 'CURR' TRANSPORTING NO FIELDS.
  IF sy-subrc = 0.
    LOOP AT pt_fieldcat INTO DATA(ls_fieldcat) WHERE datatype = 'CURR'.
      ASSIGN COMPONENT ls_fieldcat-fieldname  OF STRUCTURE ps_main TO <fv_amount>.
      ASSIGN COMPONENT ls_fieldcat-cfieldname OF STRUCTURE ps_main TO <fv_waers>.
      lv_length = ls_fieldcat-intlen.
      IF <fv_waers>  IS NOT INITIAL AND
         <fv_amount> IS NOT INITIAL.
        lv_amount = <fv_amount>.
        lv_waers = <fv_waers>.

        CALL FUNCTION 'BAPI_CURRENCY_CONV_TO_INTERNAL'
          EXPORTING
            currency             = lv_waers
            amount_external      = lv_amount
            max_number_of_digits = lv_length
          IMPORTING
            amount_internal      = <fv_amount>.
      ENDIF.
    ENDLOOP.
  ENDIF.
ENDFORM.                    " CONVERT_CURR_TO_INTERNAL

EXCEL模板下载

*&---------------------------------------------------------------------*
*&      Form  FRM_DONLOAD
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_LV_FILENAME  text
*----------------------------------------------------------------------*
FORM frm_donload  USING pv_objid pv_fname.
  DATA:lv_init_path TYPE string,
       lv_title     TYPE string,
       lv_path      TYPE string,
       lv_fpath     TYPE string,
       lv_file      TYPE rlgrap-filename.

  DATA:lv_objid TYPE char20,
       ls_wdatb LIKE wwwdatatab,
       lv_subrc TYPE sy-subrc,
       lv_msg   TYPE string.

  "获取桌面路径
  CALL METHOD cl_gui_frontend_services=>get_desktop_directory
    CHANGING
      desktop_directory    = lv_init_path
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.
  IF sy-subrc <> 0.
    EXIT.
  ENDIF.

  "保存文件
  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      window_title         = lv_title
      default_extension    = 'xls'
      default_file_name    = pv_fname
      initial_directory    = lv_init_path
      file_filter          = 'EXCEL文件(*.xls)||全部文件(*.*)||'
      prompt_on_overwrite  = 'X'
    CHANGING
      filename             = pv_fname
      path                 = lv_path
      fullpath             = lv_fpath
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ELSE.
    CLEAR:lv_file.
    lv_file = lv_fpath.
    IF lv_file IS NOT INITIAL.
      "判断文件模板是否存在
      SELECT SINGLE
             relid
             objid
        FROM wwwdata
        INTO CORRESPONDING FIELDS OF ls_wdatb
       WHERE srtf2 = 0
         AND relid = 'MI'
         AND objid = pv_objid.

      IF ls_wdatb IS INITIAL.
        MESSAGE text-e03 TYPE 'E'.
      ELSE.

        CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
          EXPORTING
            key         = ls_wdatb
            destination = lv_file
          IMPORTING
            rc          = lv_subrc.
        IF lv_subrc <> 0.
          MESSAGE text-e02 TYPE 'E'.
        ELSE.
          CLEAR:lv_msg.
          CONCATENATE '模板下载到本地文件' lv_file INTO lv_msg.
          MESSAGE lv_msg TYPE 'S'.
        ENDIF.
      ENDIF.
    ELSE.
      MESSAGE text-e01 TYPE 'E' .
    ENDIF.
  ENDIF.
ENDFORM.                    " FRM_DONLOAD

选择屏幕文件路径选择帮助

*&---------------------------------------------------------------------*
*&选择屏幕
*&---------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK bk1 WITH FRAME TITLE text-001.
PARAMETERS:p_file TYPE string.
SELECTION-SCREEN END OF BLOCK bk1.

*&---------------------------------------------------------------------*
*&AT SELECTION-SCREEN ON VALUE-REQUEST
*&---------------------------------------------------------------------*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
****文件上传路径搜索帮助
  PERFORM f4_p_file.
*&---------------------------------------------------------------------*
*&      Form  F4_P_FILE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM f4_p_file .
  DATA:lt_tab TYPE filetable,
       lv_qty TYPE i,"返回的文件数量
       ls_wa TYPE string.

  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      window_title     = '选择文件' "窗口标题
      default_filename = '' "默认文件名
      multiselection   = '' "是否多选，X=一次性可多选，空=单选
      file_filter      = 'Excel表格| *.xls;*.xlsx;*.xlsm'
    CHANGING
      file_table       = lt_tab  "返回的文件结果
      rc               = lv_qty.  "返回的文件个数

  LOOP AT lt_tab INTO ls_wa.
    p_file = ls_wa."文件选择框内 = 返回的所选文件路径
  ENDLOOP.
  CALL METHOD cl_gui_cfw=>flush.
ENDFORM.                    " F4_P_FILE

EXCLE上传程序模板

*&---------------------------------------------------------------------*
*& Report ZPPU012
*&---------------------------------------------------------------------*
*&作者:ZJ
*&日期:20240117
*&描述:美仓批量转单
*&类型:功能
*&---------------------------------------------------------------------*
REPORT zppu012.
*&---------------------------------------------------------------------*
*&INCLUDE
*&---------------------------------------------------------------------*
INCLUDE zppu012_c01.
INCLUDE zppu012_top.
INCLUDE zppu012_s01.
INCLUDE zppu012_f01.
INCLUDE zppu012_o01.
INCLUDE zppu012_i01.
*&---------------------------------------------------------------------*
*&INITIALIZATION
*&---------------------------------------------------------------------*
INITIALIZATION.
  PERFORM frm_init."初始化事件
*&---------------------------------------------------------------------*
*&AT SELECTION-SCREEN
*&---------------------------------------------------------------------*
AT SELECTION-SCREEN.
  PERFORM frm_pai."PAI事件
*&---------------------------------------------------------------------*
*&AT SELECTION-SCREEN ON VALUE-REQUEST
*&---------------------------------------------------------------------*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
****文件上传路径搜索帮助
  PERFORM f4_p_file.
*&---------------------------------------------------------------------*
*&START-OF-SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION.
  PERFORM frm_check_data."选择屏幕数据检查
  PERFORM frm_get_data."数据获取
*&---------------------------------------------------------------------*
*&END-OF-SELECTION
*&---------------------------------------------------------------------*
END-OF-SELECTION.
  CALL SCREEN 100.

*&---------------------------------------------------------------------*
*& 包含               ZPPU012_C01
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*&事件类定义
*&---------------------------------------------------------------------*

CLASS lcl_event_receiver DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS:
      handle_toolbar FOR EVENT toolbar OF cl_gui_alv_grid "自定义工具栏
        IMPORTING e_object e_interactive,
      handle_user_command       "自定义按钮事件
        FOR EVENT user_command OF cl_gui_alv_grid IMPORTING e_ucomm.

ENDCLASS.

*&---------------------------------------------------------------------*
*&事件类实现
*&---------------------------------------------------------------------*
CLASS lcl_event_receiver IMPLEMENTATION.
  METHOD handle_toolbar.
    "工具栏
    PERFORM frm_handle_toolbar CHANGING e_object.
  ENDMETHOD.

  METHOD handle_user_command.
    "按钮事件
    PERFORM frm_handle_user_command USING e_ucomm.
  ENDMETHOD.
ENDCLASS.

*&---------------------------------------------------------------------*
*& 包含               ZPPU012_TOP
*&---------------------------------------------------------------------*
TABLES:zppt025,sscrfields.
*&---------------------------------------------------------------------*
*&全局类型
*&---------------------------------------------------------------------*
TYPES: BEGIN OF gty_cols_rows,
         begin_col TYPE i,
         begin_row TYPE i,
         end_col   TYPE i,
         end_row   TYPE i,
       END OF gty_cols_rows,

       BEGIN OF gty_excel,
         werks TYPE werks_d,
         ebeln TYPE ebeln,
         ebelp TYPE ebelp,
       END OF gty_excel.
*&---------------------------------------------------------------------*
*&全局表
*&---------------------------------------------------------------------*
DATA:BEGIN OF gs_data,
       icon TYPE icon_d, "指示灯
       msg  TYPE char255. "消息
       INCLUDE TYPE zppt025.
DATA:END OF gs_data.

DATA:gt_data   LIKE TABLE OF gs_data,
     gt_upload TYPE TABLE OF alsmex_tabline,
     gt_excel  TYPE TABLE OF gty_excel.

*&---------------------------------------------------------------------*
*&全局变量
*&---------------------------------------------------------------------*
DATA: gv_ok_code   TYPE sy-ucomm,
      ok_code      TYPE sy-ucomm,
      gv_col       TYPE i,
      gs_cols_rows TYPE gty_cols_rows,
      gv_dynnr     TYPE sy-dynnr.
*&---------------------------------------------------------------------*
*&ALV全局变量
*&---------------------------------------------------------------------*
DATA:go_grid     TYPE REF TO cl_gui_alv_grid,
     go_docking  TYPE REF TO cl_gui_docking_container,
     gs_layout   TYPE lvc_s_layo,
     gt_fieldcat TYPE lvc_t_fcat,
     gs_fieldcat TYPE lvc_s_fcat,
     gt_exclude  TYPE ui_functions.

*&---------------------------------------------------------------------*
*&宏
*&---------------------------------------------------------------------*
DEFINE fieldcat_add.
  CLEAR gs_fieldcat.
  gv_col = gv_col + 1.
  gs_fieldcat-col_pos    = gv_col. "列的显示位置
  gs_fieldcat-fieldname  = &1.      "表字段
  gs_fieldcat-reptext    = &2.      "列标题
  gs_fieldcat-icon  = &3.      "图标
  gs_fieldcat-convexit    = &4.      "例程
  gs_fieldcat-edit   = &5.      "可编辑
  gs_fieldcat-just       = &6.      "输出格式(L:靠左,C:居中,R:靠右)
  gs_fieldcat-hotspot    = &7.      "是否单点
  gs_fieldcat-ref_table  = &8.      "参考表
  gs_fieldcat-ref_field  = &9.      "参考字段
*  gs_fieldcat-convexit   = &10.     "转换例程
*  gs_fieldcat-edit       = &11.     "这一列设为可编辑状态

  IF mytab-dynnr EQ 9001 AND gs_fieldcat-fieldname = 'AUART'. "9001订单类型可编辑
    gs_fieldcat-edit = 'X'.
  ENDIF.
  APPEND gs_fieldcat TO gt_fieldcat.
END-OF-DEFINITION.

*&---------------------------------------------------------------------*
*& 包含               ZPPU012_S01
*&---------------------------------------------------------------------*
****导入
SELECTION-SCREEN BEGIN OF SCREEN 9001 AS SUBSCREEN.
  SELECTION-SCREEN BEGIN OF BLOCK bk1 WITH FRAME TITLE TEXT-001.
    PARAMETERS:p_file   TYPE rlgrap-filename, "文件路径
               p_werks1 TYPE zppt025-werks. "交货工厂
    SELECT-OPTIONS:s_dispo1 FOR zppt025-dispo."MRP控制者
  SELECTION-SCREEN END OF BLOCK bk1.
SELECTION-SCREEN END OF SCREEN 9001.

****查询
SELECTION-SCREEN BEGIN OF SCREEN 9002 AS SUBSCREEN .
  SELECTION-SCREEN BEGIN OF BLOCK bk2 WITH FRAME TITLE TEXT-002.
    PARAMETERS:p_werks2 TYPE zppt025-werks."工厂
    SELECT-OPTIONS:s_ebeln FOR zppt025-/zpc1/zglxd,"采购订单号
                   s_werks FOR zppt025-werks_jh,"交货工厂
                   s_dispo FOR zppt025-dispo,"MRP控制者
                   s_matnr FOR zppt025-matnr."物料号
  SELECTION-SCREEN END OF BLOCK bk2.
SELECTION-SCREEN END OF SCREEN 9002.

****下载按钮
SELECTION-SCREEN FUNCTION KEY 1.
****页签
SELECTION-SCREEN:BEGIN OF TABBED BLOCK mytab FOR 25 LINES,
TAB (20) p_tab1 USER-COMMAND tab1,
TAB (20) p_tab2 USER-COMMAND tab2,
END OF BLOCK mytab.

*&---------------------------------------------------------------------*
*& 包含               ZPPU012_F01
*&---------------------------------------------------------------------*

*&---------------------------------------------------------------------*
*& Form get_data
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_get_data .
  CASE mytab-dynnr.
    WHEN 9001.
      PERFORM frm_import_data."模板导入
      PERFORM frm_process_data."EXCEL数据处理
    WHEN 9002.
      PERFORM frm_select_data."获取数据
    WHEN OTHERS.
  ENDCASE.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_build_fcat
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_build_fcat .
  CLEAR:gv_col,gs_fieldcat,gt_fieldcat.
  gv_col = 1.

  IF mytab-dynnr EQ 9001.
    fieldcat_add: 'ICON' '指示灯' 'X' '' '' '' '' '' '',
                  'MSG' '消息' '' '' '' '' '' '' ''.
  ENDIF.
  fieldcat_add: 'WERKS' '工厂' '' '' '' '' '' 'ZPPT025' 'WERKS',
                'WERKS_JH' '交货工厂' '' '' '' '' '' 'ZPPT025' 'WERKS_JH',
                '/ZPC1/ZGLXD' '采购订单号' '' '' '' '' '' '' '',
                '/ZPC1/ZGLHH' '采购订单行号' '' '' '' '' '' '' '',
                'MATNR' '物料号' '' '' '' '' '' 'ZPPT025' 'MATNR',
                'MAKTX' '物料描述' '' '' '' '' '' 'ZPPT025' 'MAKTX',
                'DISPO' 'MRP控制者' '' '' '' '' '' 'ZPPT025' 'DISPO',
                'DSNAM' 'MRP控制者描述' '' '' '' '' '' 'ZPPT025' 'DSNAM',
                'GAMNG' '数量' '' '' '' '' '' 'ZPPT025' 'GAMNG',
                'MEINS' '单位' '' '' '' '' '' 'ZPPT025' 'MEINS',
                'AUART' '订单类型' '' '' '' '' '' 'ZPPT025' 'AUART',
                'GSTRP' '订单开始' '' '' '' '' '' 'ZPPT025' 'GSTRP',
                'GLTRP' '订单结束' '' '' '' '' '' 'ZPPT025' 'GLTRP',
                'AUFNR' '工单号' '' '' '' '' '' 'ZPPT025' 'AUFNR'.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_set_layout
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_set_layout .
  CLEAR: gs_layout.
  gs_layout-zebra = 'X'.
  gs_layout-cwidth_opt = 'X'.
  gs_layout-sel_mode = 'A'.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form exclude_tb_functions
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*&      --> GT_EXCLUDE
*&      --> PERFORM
*&      --> DISP_ALV
*&---------------------------------------------------------------------*

FORM exclude_tb_functions  TABLES pt_exclude TYPE ui_functions .
  DATA: ls_exclude TYPE ui_func.
*  ls_exclude = cl_gui_alv_grid=>mc_fc_maximum .
*  APPEND ls_exclude TO pt_exclude.
*  ls_exclude = cl_gui_alv_grid=>mc_fc_minimum .
*  APPEND ls_exclude TO pt_exclude.
*  ls_exclude = cl_gui_alv_grid=>mc_fc_subtot .
*  APPEND ls_exclude TO pt_exclude.
*  ls_exclude = cl_gui_alv_grid=>mc_fc_sum .
*  APPEND ls_exclude TO pt_exclude.
*  ls_exclude = cl_gui_alv_grid=>mc_fc_average .
*  APPEND ls_exclude TO pt_exclude.
*  ls_exclude = cl_gui_alv_grid=>mc_mb_sum .
*  APPEND ls_exclude TO pt_exclude.
*  ls_exclude = cl_gui_alv_grid=>mc_mb_subtot .
*  APPEND ls_exclude TO pt_exclude.
*  ls_exclude = cl_gui_alv_grid=>mc_fc_sort_asc.
*  APPEND ls_exclude TO pt_exclude.
*  ls_exclude = cl_gui_alv_grid=>mc_fc_sort_dsc .
*  APPEND ls_exclude TO pt_exclude.
*  ls_exclude = cl_gui_alv_grid=>mc_fc_find .
*  APPEND ls_exclude TO pt_exclude.
*  ls_exclude = cl_gui_alv_grid=>mc_fc_filter .
*  APPEND ls_exclude TO pt_exclude.
  ls_exclude = cl_gui_alv_grid=>mc_fc_print .
  APPEND ls_exclude TO pt_exclude.
*  ls_exclude = cl_gui_alv_grid=>mc_fc_print_prev .
*  APPEND ls_exclude TO pt_exclude.
*  ls_exclude = cl_gui_alv_grid=>mc_mb_export .
*  APPEND ls_exclude TO pt_exclude.
*  ls_exclude = cl_gui_alv_grid=>mc_fc_graph .
*  APPEND ls_exclude TO pt_exclude.
*  ls_exclude = cl_gui_alv_grid=>mc_mb_view .
*  APPEND ls_exclude TO pt_exclude.
*  ls_exclude = cl_gui_alv_grid=>mc_fc_detail .
*  APPEND ls_exclude TO pt_exclude.
*  ls_exclude = cl_gui_alv_grid=>mc_fc_help .
*  APPEND ls_exclude TO pt_exclude.
*  ls_exclude = cl_gui_alv_grid=>mc_fc_info .
*  APPEND ls_exclude TO pt_exclude.
*  ls_exclude = cl_gui_alv_grid=>mc_mb_variant.
*  APPEND ls_exclude TO pt_exclude.
  ls_exclude = cl_gui_alv_grid=>mc_fc_loc_append_row.
  APPEND ls_exclude TO pt_exclude.
  ls_exclude = cl_gui_alv_grid=>mc_fc_loc_copy_row.
  APPEND ls_exclude TO pt_exclude.
  ls_exclude = cl_gui_alv_grid=>mc_fc_loc_cut.
  APPEND ls_exclude TO pt_exclude.
  ls_exclude = cl_gui_alv_grid=>mc_fc_loc_delete_row.
  APPEND ls_exclude TO pt_exclude.
  ls_exclude = cl_gui_alv_grid=>mc_fc_loc_insert_row.
  APPEND ls_exclude TO pt_exclude.
  ls_exclude = cl_gui_alv_grid=>mc_fc_loc_move_row.
  APPEND ls_exclude TO pt_exclude.
  ls_exclude = cl_gui_alv_grid=>mc_fc_loc_paste.
  APPEND ls_exclude TO pt_exclude.
  ls_exclude = cl_gui_alv_grid=>mc_fc_loc_paste_new_row.
  APPEND ls_exclude TO pt_exclude.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form disp_alv
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM disp_alv .
  DATA:lo_event TYPE REF TO lcl_event_receiver.
  FIELD-SYMBOLS: <lfs_tab> TYPE STANDARD TABLE.
  ASSIGN gt_data TO <lfs_tab>.
  CHECK <lfs_tab> IS ASSIGNED.

  CREATE OBJECT go_docking
    EXPORTING
      repid                       = sy-repid
      dynnr                       = sy-dynnr
      side                        = cl_gui_docking_container=>dock_at_bottom "dock_at_top
      extension                   = 500
    EXCEPTIONS
      cntl_error                  = 1
      cntl_system_error           = 2
      create_error                = 3
      lifetime_error              = 4
      lifetime_dynpro_dynpro_link = 5
      OTHERS                      = 6.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
               WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

  CREATE OBJECT go_grid
    EXPORTING
      i_parent          = go_docking
    EXCEPTIONS
      error_cntl_create = 1
      error_cntl_init   = 2
      error_cntl_link   = 3
      error_dp_create   = 4
      OTHERS            = 5.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
               WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

  CALL METHOD go_grid->set_table_for_first_display
    EXPORTING
      i_save                        = 'A'
      is_layout                     = gs_layout
      it_toolbar_excluding          = gt_exclude
    CHANGING
      it_outtab                     = <lfs_tab>
      it_fieldcatalog               = gt_fieldcat
    EXCEPTIONS
      invalid_parameter_combination = 1
      program_error                 = 2
      too_many_lines                = 3
      OTHERS                        = 4.
  CREATE OBJECT lo_event.

  SET HANDLER lo_event->handle_toolbar FOR go_grid.
  SET HANDLER lo_event->handle_user_command FOR go_grid.

  "调用此方法才能激活工具栏上增加的自定义按钮
  CALL METHOD go_grid->set_toolbar_interactive.
ENDFORM.

FORM frm_refresh_alv USING po_grid TYPE REF TO cl_gui_alv_grid.
  DATA:ls_stable TYPE lvc_s_stbl.

  ls_stable-row = 'X'." 基于行的稳定刷新
  ls_stable-col = 'X'." 基于列稳定刷新

  "重新设置布局
  CALL METHOD po_grid->set_frontend_layout
    EXPORTING
      is_layout = gs_layout.

  CALL METHOD po_grid->refresh_table_display
    EXPORTING
      is_stable = ls_stable
    EXCEPTIONS
      finished  = 1
      OTHERS    = 2.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_init
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_init .
  DATA: lv_functxt TYPE smp_dyntxt.

  "图标初始化
  lv_functxt-icon_id   = icon_export. "图标，可以通过ICON查找
  lv_functxt-icon_text = TEXT-003."描述
  sscrfields-functxt_01 = lv_functxt.

  p_tab1 = TEXT-004.
  p_tab2 = TEXT-005.

  CLEAR gv_dynnr.
  mytab-prog = sy-repid.

  IMPORT gv_dynnr FROM MEMORY ID 'SCREEN'."传入上次执行的屏幕号
  FREE MEMORY ID 'SCREEN'.
  IF gv_dynnr = space.
    mytab-dynnr = 9001.
    mytab-activetab = 'TAB1'.
  ELSE.
    CASE gv_dynnr.
      WHEN 9001.
        mytab-dynnr = 9001.
        mytab-activetab = 'TAB1'.
      WHEN 9002.
        mytab-dynnr = 9002.
        mytab-activetab = 'TAB2'.
    ENDCASE.
  ENDIF.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_pai
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_pai .
  CASE sy-dynnr.
    WHEN 1000.
      CASE sy-ucomm.
        WHEN 'TAB1'.
          mytab-dynnr = 9001.
          mytab-activetab = 'P_TAB1'.
          gv_dynnr     = 9001.
          EXPORT gv_dynnr TO MEMORY ID 'SCREEN'.     "录入ABAP内存屏幕号
        WHEN 'TAB2'.
          mytab-dynnr = 9002.
          mytab-activetab = 'P_TAB2'.
          gv_dynnr     = 9002.
          EXPORT gv_dynnr TO MEMORY ID 'SCREEN'.     "录入ABAP内存屏幕号
        WHEN 'FC01'.
          PERFORM frm_export_excle."模板导出
      ENDCASE.
  ENDCASE.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_export_excle
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_export_excle .
  DATA:lv_init_path TYPE string,
       lv_title     TYPE string,
       lv_path      TYPE string,
       lv_fpath     TYPE string,
       lv_file      TYPE rlgrap-filename.

  DATA:ls_wdatb LIKE wwwdatatab,
       lv_subrc TYPE sy-subrc,
       lv_msg   TYPE string.

  DATA:lv_objid TYPE w3objid,
       lv_fname TYPE string.

  lv_objid = 'ZPPU012'.
  lv_fname = '美仓批量转单导入模板'.


  "获取桌面路径
  CALL METHOD cl_gui_frontend_services=>get_desktop_directory
    CHANGING
      desktop_directory    = lv_init_path
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.
  IF sy-subrc <> 0.
    EXIT.
  ENDIF.

  "保存文件
  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      window_title         = lv_title
      default_extension    = 'xlsx'
      default_file_name    = lv_fname
      initial_directory    = lv_init_path
      file_filter          = 'EXCEL文件(*.xlsx)||全部文件(*.*)||'
      prompt_on_overwrite  = 'X'
    CHANGING
      filename             = lv_fname
      path                 = lv_path
      fullpath             = lv_fpath
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ELSE.
    CLEAR:lv_file.
    lv_file = lv_fpath.
    IF lv_file IS NOT INITIAL.
      "判断文件模板是否存在
      SELECT SINGLE
             relid
             objid
        FROM wwwdata
        INTO CORRESPONDING FIELDS OF ls_wdatb
       WHERE srtf2 = 0
         AND relid = 'MI'
         AND objid = lv_objid.

      IF ls_wdatb IS INITIAL.
        MESSAGE TEXT-e03 TYPE 'E'.
      ELSE.

        CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
          EXPORTING
            key         = ls_wdatb
            destination = lv_file
          IMPORTING
            rc          = lv_subrc.
        IF lv_subrc <> 0.
          MESSAGE TEXT-e02 TYPE 'E'.
        ELSE.
          CLEAR:lv_msg.
          CONCATENATE '模板下载到本地文件' lv_file INTO lv_msg.
          MESSAGE lv_msg TYPE 'S'.
        ENDIF.
      ENDIF.
    ELSE.
      MESSAGE TEXT-e01 TYPE 'E' .
    ENDIF.
  ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form f4_p_file
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM f4_p_file .
  DATA:lt_tab TYPE filetable,
       lv_qty TYPE i, "返回的文件数量
       ls_wa  TYPE string.

  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      window_title     = '选择文件' "窗口标题
      default_filename = '' "默认文件名
      multiselection   = '' "是否多选，X=一次性可多选，空=单选
      file_filter      = 'Excel表格| *.xls;*.xlsx;*.xlsm'
    CHANGING
      file_table       = lt_tab  "返回的文件结果
      rc               = lv_qty.  "返回的文件个数

  LOOP AT lt_tab INTO ls_wa.
    p_file = ls_wa."文件选择框内 = 返回的所选文件路径
  ENDLOOP.
  CALL METHOD cl_gui_cfw=>flush.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_check_data
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_check_data .
  CASE mytab-dynnr.
    WHEN 9001.
      IF p_file IS INITIAL OR
         p_werks1 IS INITIAL.
        MESSAGE '文件路径/交货工厂不能为空' TYPE 'S' DISPLAY LIKE 'E'.
        LEAVE LIST-PROCESSING.
      ENDIF.
    WHEN 9002.
      IF p_werks2 IS INITIAL.
        MESSAGE '工厂不能为空' TYPE 'S' DISPLAY LIKE 'E'.
        LEAVE LIST-PROCESSING.
      ENDIF.
  ENDCASE.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_import_data
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_import_data .
  PERFORM frm_upload_excel."EXCEL数据上传
  PERFORM frm_process_excel."EXCEL数据处理
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_upload_excel
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_upload_excel .
  CLEAR:gs_cols_rows.
  gs_cols_rows-begin_col = '1'.
  gs_cols_rows-begin_row = '2'.
  gs_cols_rows-end_row = '99999'.
  gs_cols_rows-end_col = '3'.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = p_file
      i_begin_col             = gs_cols_rows-begin_col
      i_begin_row             = gs_cols_rows-begin_row
      i_end_col               = gs_cols_rows-end_col
      i_end_row               = gs_cols_rows-end_row
    TABLES
      intern                  = gt_upload
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_process_excel
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_process_excel .
  DATA:ls_zsdt006 TYPE zsdt006,
       ls_excel   TYPE gty_excel.

  FIELD-SYMBOLS: <fv_field>.

  LOOP AT gt_upload INTO DATA(ls_upload).

    ASSIGN COMPONENT ls_upload-col OF STRUCTURE ls_excel TO FIELD-SYMBOL(<fv_data>).
    IF <fv_data> IS ASSIGNED.
      <fv_data> = ls_upload-value.
    ENDIF.

    CASE ls_upload-col.
      WHEN 2.
        "采购订单号补充前导零
        <fv_data> = |{ <fv_data> ALPHA = IN }|.
    ENDCASE.
    AT END OF row.
      APPEND ls_excel TO gt_excel.
      CLEAR:ls_excel.
    ENDAT.
    CLEAR:ls_upload.
  ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_process_data
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_process_data .
  PERFORM frm_set_data."数据填充
  PERFORM frm_process_bom."BOM展开
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_set_data
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_set_data .
  "数据查询
  SELECT a~ebeln,a~ebelp,b~matnr,b~menge,c~maktx,d~dispo,e~dsnam,f~eindt,b~meins
    FROM @gt_excel AS a
    LEFT JOIN ekpo AS b
      ON a~ebeln EQ b~ebeln
     AND a~ebelp EQ b~ebelp
    LEFT JOIN makt AS c
      ON b~matnr EQ c~matnr
    LEFT JOIN marc AS d
      ON d~matnr EQ b~matnr
    LEFT JOIN t024d AS e
      ON d~dispo EQ e~dispo
     AND d~werks EQ e~werks
    LEFT JOIN eket AS f
      ON f~ebeln EQ a~ebeln
     AND f~ebelp EQ a~ebelp
   WHERE c~spras EQ @sy-langu
     AND d~werks EQ @p_werks1
     AND f~etenr EQ '0001'
    INTO TABLE @DATA(lt_ekpo).
  SORT lt_ekpo BY ebeln ebelp.

  LOOP AT gt_excel INTO DATA(ls_excel).

    "工厂
    gs_data-werks = ls_excel-werks.
    "交货工厂
    gs_data-werks_jh = p_werks1.
    "采购订单号
    gs_data-/zpc1/zglxd = ls_excel-ebeln.
    "采购订单行号
    gs_data-/zpc1/zglhh = ls_excel-ebelp.
    READ TABLE lt_ekpo INTO DATA(ls_ekpo)
                       WITH KEY ebeln = ls_excel-ebeln
                                ebelp = ls_excel-ebelp
                       BINARY SEARCH.
    IF sy-subrc EQ 0.
      "物料号
      gs_data-matnr = ls_ekpo-matnr.
      "物料描述
      gs_data-maktx = ls_ekpo-maktx.
      "MRP控制者
      gs_data-dispo = ls_ekpo-dispo.
      "MRP控制者描述
      gs_data-dsnam = ls_ekpo-dsnam.
      "数量
      gs_data-gamng = ls_ekpo-menge.
      "单位
      gs_data-meins = ls_ekpo-meins.
      "订单开始
      gs_data-gstrp = sy-datum.
      "订单结束
      gs_data-gltrp = ls_ekpo-eindt.

      "订单类型
      CASE gs_data-werks_jh.
        WHEN '8000'.
          gs_data-auart = 'ZD01'.
        WHEN '8010'.
          gs_data-auart = 'ZY01'.
        WHEN '8020'.
          gs_data-auart = 'ZC01'.
        WHEN '6040'.
          gs_data-auart = 'ZV01'.
      ENDCASE.
    ELSE.
      gs_data-icon = icon_red_light.
      gs_data-msg = '不存在该采购订单/行'.
    ENDIF.
    APPEND gs_data TO gt_data.
    CLEAR:gs_data.
  ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_process_bom
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_process_bom .

  DATA(lt_data) = gt_data.

  LOOP AT lt_data INTO gs_data WHERE icon IS INITIAL.

    PERFORM frm_get_bom."BOM数据获取
  ENDLOOP.
  DELETE gt_data WHERE dispo NOT IN s_dispo1.
  SORT gt_data BY werks /zpc1/zglxd /zpc1/zglhh.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_get_bom
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_get_bom .

  DATA:lv_bmeng TYPE stko-bmeng,
       lv_idnrk TYPE idnrk,
       lt_stpox TYPE TABLE OF stpox,
       ls_data  LIKE gs_data.

  lv_bmeng = gs_data-gamng.

  "获取备选BOM
  SELECT SINGLE stlal
    INTO @DATA(lv_stlal)
    FROM mkal
   WHERE matnr = @gs_data-matnr
     AND werks = @gs_data-werks_jh.

  CALL FUNCTION 'CS_BOM_EXPL_MAT_V2'
    EXPORTING
      capid                 = 'PP01'         "Application ID
      datuv                 = sy-datum        "Validity date
      emeng                 = lv_bmeng         "Required quantity
      auskz                 = space            "scrap
      mehrs                 = 'X'            "Multi-level explosion
      mtnrv                 = gs_data-matnr       "Material
      stlal                 = lv_stlal           "Alternative BOM
      stlan                 = '1'        "BOM usage
      werks                 = gs_data-werks_jh        "Plant
    TABLES
      stb                   = lt_stpox
    EXCEPTIONS
      alt_not_found         = 1
      call_invalid          = 2
      material_not_found    = 3
      missing_authorization = 4
      no_bom_found          = 5
      no_plant_data         = 6
      no_suitable_bom_found = 7
      conversion_error      = 8
      OTHERS                = 9. "CS_BOM_EXPL_MAT_V2

  "查询子料相关信息---"MARC-BESKZ=E OR X and ZPPR008-子件料号<>4* AND 基础数量 > 0
  WITH +i AS ( SELECT DISTINCT idnrk
                 FROM @lt_stpox AS k
                WHERE mngko > 0 )
SELECT i~idnrk,a~maktx,b~dispo,
       b~beskz,c~dsnam,d~matkl
  FROM +i AS i
  LEFT JOIN makt AS a
    ON i~idnrk EQ a~matnr
  LEFT JOIN marc AS b
    ON b~matnr EQ i~idnrk
  LEFT JOIN t024d AS c
    ON c~dispo EQ b~dispo
   AND b~werks EQ c~werks
  LEFT JOIN mara AS d
    ON d~matnr EQ i~idnrk
 WHERE a~spras EQ @sy-langu
   AND b~werks EQ @gs_data-werks_jh
   AND ( b~beskz EQ 'E' OR
         b~beskz EQ 'X' )
   AND d~matkl NE '331001'
   AND substring( ltrim( i~idnrk,'0' ),1,1 ) NE '4'
   AND b~dispo IN @s_dispo1
  INTO TABLE @DATA(lt_marc).
  SORT lt_marc BY idnrk.

  LOOP AT lt_stpox INTO DATA(ls_stopx) WHERE mngko > 0.

    CLEAR:lv_idnrk.

    CALL FUNCTION 'CONVERSION_EXIT_MATN1_OUTPUT'
      EXPORTING
        input  = ls_stopx-idnrk
      IMPORTING
        output = lv_idnrk.

    READ TABLE lt_marc INTO DATA(ls_marc)
                       WITH KEY idnrk = ls_stopx-idnrk
                       BINARY SEARCH.
    IF sy-subrc EQ 0 .

      ls_data = CORRESPONDING #( gs_data ).

      "物料号
      ls_data-matnr = ls_marc-idnrk.
      "物料描述
      ls_data-maktx = ls_marc-maktx.
      "MRP控制者
      ls_data-dispo = ls_marc-dispo.
      "MRP控制着描述
      ls_data-dsnam = ls_marc-dsnam.
      "数量
      ls_data-gamng = ls_stopx-mngko * ( 1 + ( ls_stopx-ausch / 100 ) ).
      "单位
      ls_data-meins = ls_stopx-mmein.

      "如果单位是EA,向上取整
      IF ls_data-meins EQ 'EA'.
        ls_data-gamng = CEIL( ls_data-gamng ).
      ENDIF.
      COLLECT ls_data INTO gt_data.
      CLEAR:ls_data.
    ENDIF.
  ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_handle_toolbar
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*&      <-- E_OBJECT
*&---------------------------------------------------------------------*
FORM frm_handle_toolbar CHANGING po_object TYPE REF TO cl_alv_event_toolbar_set.

  DATA: ls_toolbar  TYPE stb_button.

  MOVE 'POST' TO ls_toolbar-function.
  MOVE icon_generate TO ls_toolbar-icon.
  MOVE '生成工单' TO ls_toolbar-quickinfo.
  MOVE '生成工单'(112) TO ls_toolbar-text.
  APPEND ls_toolbar TO po_object->mt_toolbar.
  CLEAR ls_toolbar.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_handle_user_command
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*&      --> E_UCOMM
*&---------------------------------------------------------------------*
FORM frm_handle_user_command USING pv_comm TYPE sy-ucomm.

  CASE pv_comm.
    WHEN 'POST'.
      PERFORM frm_post_data."生成工单
  ENDCASE.

  PERFORM frm_refresh_alv USING go_grid.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_post_data
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_post_data .

  DATA:lt_rows TYPE lvc_t_roid,
       ls_rows TYPE lvc_s_roid.
  DATA:ls_orderdata TYPE bapi_pp_order_create.
  DATA:ls_return       TYPE bapiret2,
       lv_order_number TYPE bapi_order_key-order_number,
       ls_zppt025      TYPE zppt025.

  "获取选中行
  CALL METHOD go_grid->get_selected_rows
    IMPORTING
      et_row_no = lt_rows.
  IF lt_rows IS INITIAL.
    MESSAGE '没有选中行' TYPE 'S' DISPLAY LIKE 'E'.
    RETURN.
  ELSE.

    LOOP AT lt_rows INTO ls_rows.

      READ TABLE gt_data INTO gs_data INDEX ls_rows-row_id.
      IF sy-subrc EQ 0 AND gs_data-icon IS INITIAL.
        CLEAR:ls_orderdata,ls_return.
        "物料编码
        ls_orderdata-material_long = gs_data-matnr.
        "交货工厂
        ls_orderdata-plant = gs_data-werks_jh.
        "订单类型
        ls_orderdata-order_type = gs_data-auart.
        "数量
        ls_orderdata-quantity = gs_data-gamng.
        "订单开始日期
        ls_orderdata-basic_start_date = gs_data-gstrp.
        "订单结束日期
        ls_orderdata-basic_end_date = gs_data-gltrp.

        "调用BAPI创建工单
        CALL FUNCTION 'BAPI_PRODORD_CREATE'
          EXPORTING
            orderdata    = ls_orderdata
          IMPORTING
            return       = ls_return
            order_number = gs_data-aufnr.

        IF ls_return-type = 'E'.
          CALL FUNCTION 'BAPI_TRANSACTION_ROLLBACK'.

          gs_data-icon = icon_red_light.
          gs_data-msg = ls_return-message.

        ELSE.
          CALL FUNCTION 'BAPI_TRANSACTION_COMMIT'
            EXPORTING
              wait = 'X'.

          "工单创建成功后，更新采购订单和行项目
          DO 50 TIMES.
            SELECT COUNT(*)
              FROM aufk
             WHERE aufnr EQ gs_data-aufnr.
            IF sy-subrc EQ 0.
              EXIT.
            ELSE.
              WAIT UP TO '0.3' SECONDS.
            ENDIF.
          ENDDO.

          "更新采购订单和行
          UPDATE aufk SET /zpc1/zglxd = gs_data-/zpc1/zglxd
                          /zpc1/zglhh = gs_data-/zpc1/zglhh
                    WHERE aufnr = gs_data-aufnr.
          IF sy-subrc EQ 0.
            COMMIT WORK AND WAIT.
          ENDIF.

          ls_zppt025 = CORRESPONDING #( gs_data ).

          "更新自建表数据
          MODIFY zppt025 FROM ls_zppt025.
          COMMIT WORK AND WAIT.

          gs_data-icon = icon_green_light.
          gs_data-msg = '工单创建成功'.

        ENDIF.
        MODIFY gt_data FROM gs_data INDEX ls_rows-row_id TRANSPORTING icon msg aufnr.
        CLEAR:gs_data.
      ENDIF.
      CLEAR:ls_rows.
    ENDLOOP.
  ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_select_data
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_select_data .
  "获取数据
  SELECT *
    FROM zppt025
    INTO CORRESPONDING FIELDS OF TABLE gt_data
   WHERE werks = p_werks2
     AND /zpc1/zglxd IN s_ebeln
     AND werks_jh IN s_werks
     AND dispo IN s_dispo
     AND matnr IN s_matnr.

  SORT gt_data BY werks /zpc1/zglxd /zpc1/zglhh.
ENDFORM.

*&---------------------------------------------------------------------*
*& 包含               ZPPU012_O01
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Module STATUS_0100 OUTPUT
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
MODULE status_0100 OUTPUT.
  SET PF-STATUS '0100'.
  SET TITLEBAR '0100'.
ENDMODULE.
*&---------------------------------------------------------------------*
*& Module INIT_ALV OUTPUT
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
MODULE init_alv OUTPUT.
  IF go_docking IS NOT BOUND.
    "创建ALV结构
    PERFORM frm_build_fcat.
    "设置ALV输出格式及变式
    PERFORM frm_set_layout.
    PERFORM exclude_tb_functions  TABLES gt_exclude.
    "显示ALV数据
    PERFORM disp_alv.
  ELSE.
    PERFORM frm_refresh_alv USING go_grid.
  ENDIF.
ENDMODULE.

*&---------------------------------------------------------------------*
*& 包含               ZPPU012_I01
*&---------------------------------------------------------------------*

*&---------------------------------------------------------------------*
*&      Module  EXIT  INPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
MODULE exit INPUT.
  CLEAR:gv_ok_code.
  gv_ok_code = ok_code.
  CLEAR:ok_code.
  CASE gv_ok_code.
    WHEN 'BACK' OR 'EXIT' OR 'CANCLE'.
      LEAVE TO SCREEN 0.
  ENDCASE.
ENDMODULE.

posted @ 2022-08-04 10:31 阿胖的阿多阅读(954) 评论(2) 编辑收藏举报

刷新页面返回顶部

阿胖的阿多