ABAP将Excel导入SAP数据表

如何将Excel导入SAP系统，在sap可执行程序中可以通过调用函数ALSM_EXCEL_TO_INTERNAL_TABLE实现。
实现简单效果如下：

选择文件：

点击选择后，文件地址会写入地址栏：

最后点击执行：

具体代码如下：
REPORT ztest_xt2.

TABLES: zschool.

"假设我们要上传的内容有三个字段：学校ID，名称，地址。然后定义类型。 重点：与EXCEL表的要完全对应上
TYPES:BEGIN OF ty_school,

•    mandt  TYPE sy-mandt ,          "客户端号
    zsid   TYPE zschool-zsid,       "学校ID
    zsname TYPE zschool-zsname,     "名称
    zsadd  TYPE zschool-zsadd,      "地址
  END OF ty_school.
  

“定义参照TY_SCHOOL类型的内表和工作区，用于暂存取到的Excel内容”
DATA:lt_school TYPE TABLE OF ty_school,
ls_school TYPE ty_school.

“定义内表，用于insert或modify透明表内容”
DATA:gt_school TYPE TABLE OF zschool, “直接参照透明表”
gs_school TYPE zschool.

“选择屏幕”
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
PARAMETERS p_file(80). “地址栏”
SELECTION-SCREEN END OF BLOCK b1.

INITIALIZATION.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
PERFORM open_dialog.“调用选择文件函数”

AT SELECTION-SCREEN.“用于屏幕输入检验”

IF p_file IS INITIAL.
MESSAGE ‘文件地址不可为空！’ TYPE ‘S’ DISPLAY LIKE ‘E’.
STOP.
ENDIF.

START-OF-SELECTION.

PERFORM upload.

END-OF-SELECTION.

FORM open_dialog.“选择文件会话。”
DATA:lt_file_table TYPE filetable.
DATA:lw_file_table TYPE file_table.
DATA:l_rc TYPE i.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title = ‘选择文件’
default_filename = ‘*.XLSX’ “默认excel文件”
initial_directory = 'D:\ ’ “默认打开D盘，也可以默认空”
multiselection = ‘’ “文件单选”
CHANGING
file_table = lt_file_table
rc = l_rc
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error = 2
error_no_gui = 3
not_supported_by_gui = 4
OTHERS = 5.

CHECK l_rc EQ 1.“因为文件单选，所以这里判断一下选择的数量为1”

READ TABLE lt_file_table INDEX 1 INTO p_file. “将选择的文件地址写入到地址栏”

ENDFORM.

FORM upload. "最后也是最关键的一步
"定义表格结构内表，ALSMEX_TABLINE是具有Excel数据的表行，有三个组件，row col value 。
DATA: i_excel TYPE TABLE OF alsmex_tabline, "表格结构。
w_excel TYPE alsmex_tabline.

DATA:get_file TYPE rlgrap-filename.“这里参照系统中的结构字段。”
MOVE p_file TO get_file.“将地址栏的值赋值给GET_FILE”

“调用此函数，将Excel中的内容以类似坐标的形式存储到I_excel内表中。”
CALL FUNCTION ‘ALSM_EXCEL_TO_INTERNAL_TABLE’
EXPORTING
filename = get_file
i_begin_col = 1
i_begin_row = 1
i_end_col = 8
i_end_row = 65535
TABLES
intern = i_excel
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.

DELETE i_excel WHERE row = 1.“删除第一行抬头。如果excel文件中不存在抬头的话，可不写此句。”

IF i_excel IS INITIAL.
CALL FUNCTION ‘POPUP_TO_CONFIRM_WITH_MESSAGE’
EXPORTING

•   DEFAULTOPTION = 'Y'
    diagnosetext1 = '数据错误'
  

•   DIAGNOSETEXT2 = ' '
  

•   DIAGNOSETEXT3 = ' '
    textline1     = 'Excel表格中没有内容！'
    titel         = '提示'.
  

  RETURN.
  ENDIF.

  CLEAR:lt_school.
  FIELD-SYMBOLS:. "这里涉及到SAP内部字段的使用。
  SORT i_excel BY row col.
  DATA:num_col TYPE i.

  LOOP AT i_excel INTO w_excel.
  num_col = w_excel-col.
  ASSIGN COMPONENT num_col OF STRUCTURE ls_school TO .
  = w_excel-value.
  AT END OF row.
  APPEND ls_school TO lt_school.
  CLEAR:ls_school.
  ENDAT.
  CLEAR:w_excel.
  ENDLOOP.

  LOOP AT lt_school INTO ls_school.
  gs_school-mandt = sy-mandt.“客户端号，创建透明表必须包含的字段。”
  gs_school-zsid = ls_school-zsid.
  gs_school-zsname = ls_school-zsname.
  gs_school-zsadd = ls_school-zsadd.
  APPEND gs_school TO gt_school.
  CLEAR:gs_school.
  CLEAR:ls_school.
  ENDLOOP.

  “将数据库中内容更新”
  MODIFY zschool FROM TABLE gt_school.“这里使用modify，兼顾增加和修改，若主键存在则为修改，否则增加”
  IF sy-subrc = 0.
  COMMIT WORK AND WAIT.“成功提交，或者最后程序结束系统也会自动提交”
  DATA:num_tab(6) TYPE c.
  DESCRIBE TABLE gt_school LINES num_tab.
  CONDENSE num_tab NO-GAPS.
  DATA:str_line1 TYPE string .
  CONCATENATE ‘成功导入数据’ num_tab ‘行’ INTO str_line1 .
  “读出excel中的数据条数，用于消息提示”
  CALL FUNCTION ‘POPUP_TO_CONFIRM_WITH_MESSAGE’
  EXPORTING
  diagnosetext1 = ‘成功’
  textline1 = str_line1
  titel = ‘提示’.
  ELSE.
  ROLLBACK WORK.“否则失败”
  ENDIF.

ENDFORM.