将EXCEL数据导入SAP数据库表

休几日

已于 2022-02-11 09:40:58 修改

阅读量2.1k

点赞数 2

分类专栏：笔记文章标签： abap

于 2021-09-02 14:27:51 首次发布

原文链接：https://blog.csdn.net/weixin_42808203/article/details/103907047?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522163892682516780357227489%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=163892682516780357227489&b

版权

笔记专栏收录该内容

7 篇文章 2 订阅

订阅专栏

将EXCEL数据导入SAP数据库表

日期格式转换
EXCEL中的日期格式和SAP数据库中的日期格式不兼容，要将日期格式转换为SAP数据库类型匹配的类型。

*日期格式转换
IF <wa_itab>-value IS NOT INITIAL AND <wa_itab>-value CP '*/*/*'.
  CLEAR: zyear, zmon, zday.
  SPLIT <wa_itab>-value AT '/' INTO  zyear zmon zday.
  CONDENSE  zyear NO-GAPS.
  CONDENSE  zmon  NO-GAPS.
  CONDENSE  zday  NO-GAPS.
  IF strlen( zmon )  = 1.
    CONCATENATE '0' zmon INTO zmon.
  ENDIF.
  IF strlen( zday )  = 1.
    CONCATENATE '0' zday INTO zday.
  ENDIF.
  REPLACE ALL OCCURRENCES OF  '-'  IN <wa_itab>-value  WITH space.
  REPLACE ALL OCCURRENCES OF  '.'  IN <wa_itab>-value  WITH space.
  REPLACE ALL OCCURRENCES OF  '/'  IN <wa_itab>-value  WITH space.
  CONCATENATE zyear zmon zday INTO <wa_itab>-value.
ENDIF.

通过上传EXCEL文件，将文件中的数据导入到数据库表 ZHQ_INCOME_01
1、定义我们要上上传的内容的字段（这些字段要与EXCEL对应）；

TABLES: zhq_income_01.
"假设我们要上传的内容有三个字段：学校ID，名称，地址。然后定义类型。 重点：与EXCEL表的要完全对应上"
TYPES:BEGIN OF ty_zhq_income_01,
        mandt   TYPE sy-mandt ,
        zyear   TYPE zhq_income_01-zyear  ,
        company TYPE zhq_income_01-company,
        zmonth  TYPE zhq_income_01-zmonth ,
        income  TYPE zhq_income_01-income ,
        waers   TYPE zhq_income_01-waers  ,
      END OF ty_zhq_income_01.
"定义参照TY_ZHQ_INCOME_01类型的内表和工作区，用于暂存取到的Excel内容"
DATA:lt_zhq_income_01 TYPE TABLE OF ty_zhq_income_01,
     ls_zhq_income_01 TYPE          ty_zhq_income_01.
"定义内表，用于insert或modify透明表内容"
DATA:gt_zhq_income_01 TYPE TABLE OF zhq_income_01, "直接参照透明表”
     gs_zhq_income_01 TYPE          zhq_income_01.

2、设置选择屏幕

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

3、设置F4搜索帮助，显示选择文件界面

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.

4、开始导入EXCEL文件的数据

"最后也是最关键的一步"
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               = 6   "列数"
      i_end_row               = 21  "行数"
    TABLES
      intern                  = i_excel
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.
"删除第一行抬头,如果excel文件中不存在抬头的话，可不写此句。"
*  DELETE i_excel WHERE row = 1.
  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_zhq_income_01.
  FIELD-SYMBOLS:<f_s>. "这里涉及到SAP内部字段的使用。"
  SORT i_excel BY row col.
  DATA:num_col TYPE i.
  LOOP AT i_excel INTO w_excel.
    num_col = w_excel-col."w_excel-col是当前EXCEL行对应的列，则num_col也表示列数"
 "assign将结构ls_zhq_income_01的第num_col个字段的值分配给<f_s>,则<f_s>的值改变，ls_zhq_income_01的第num_col个字段的值也改变。"
    ASSIGN COMPONENT num_col OF STRUCTURE ls_zhq_income_01 TO <f_s>.
    <f_s> = w_excel-value.
"在本行的最后一列值赋值给ls_zhq_income_01后，将ls_zhq_income_01的值append到内表 lt_zhq_income_01"
    AT END OF row.
      APPEND ls_zhq_income_01 TO lt_zhq_income_01.
      CLEAR:ls_zhq_income_01.
    ENDAT.
    CLEAR:w_excel.
  ENDLOOP.
  LOOP AT lt_zhq_income_01 INTO ls_zhq_income_01.
    gs_zhq_income_01-mandt   = sy-mandt."客户端号，创建透明表必须包含的字段。"
    gs_zhq_income_01-zyear   = ls_zhq_income_01-zyear    .
    gs_zhq_income_01-company = ls_zhq_income_01-company  .
    gs_zhq_income_01-zmonth  = ls_zhq_income_01-zmonth   .
    gs_zhq_income_01-income  = ls_zhq_income_01-income   .
    gs_zhq_income_01-waers   = ls_zhq_income_01-waers    .
    APPEND gs_zhq_income_01 TO gt_zhq_income_01.
    CLEAR:gs_zhq_income_01.
    CLEAR:ls_zhq_income_01.
  ENDLOOP.
  "将数据库中内容更新"
  "这里使用modify，兼顾增加和修改，若主键存在则为修改，否则增加"
  MODIFY zhq_income_01 FROM TABLE gt_zhq_income_01.
  IF sy-subrc = 0.
    COMMIT WORK AND WAIT."成功提交，或者最后程序结束系统也会自动提交"
    DATA:num_tab(6) TYPE c.
    DESCRIBE TABLE gt_zhq_income_01 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.

若要只修改某个字段，则可以用update

 LOOP AT lt_zhq_income_01 INTO ls_zhq_income_01.
    gs_zhq_income_01-mandt   = sy-mandt."客户端号，创建透明表必须包含的字段。"
    gs_zhq_income_01-zyear   = ls_zhq_income_01-zyear    .
    gs_zhq_income_01-company = ls_zhq_income_01-company .
    gs_zhq_income_01-zmonth  = ls_zhq_income_01-zmonth   .
    gs_zhq_income_01-income  = ls_zhq_income_01-income   .
*    gs_zhq_income_01-waers   = ls_zhq_income_01-waers    .

    UPDATE zhq_income_01 SET income  = ls_zhq_income_01-income
                         WHERE zyear   = ls_zhq_income_01-zyear
                           AND company = ls_zhq_income_01-company
                           AND zmonth  = ls_zhq_income_01-zmonth  .
    CLEAR:ls_zhq_income_01.
  ENDLOOP.
  IF sy-subrc = 0.
    COMMIT WORK AND WAIT."成功提交，或者最后程序结束系统也会自动提交"
    DATA:num_tab(6) TYPE c.
    DESCRIBE TABLE lt_zhq_income_01 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.

5、源码
可直接运行（前提是把更新表换成自己需要导入数据的表）

*&---------------------------------------------------------------------*
*& Report  Z_TEST_WJ03
*&         Description:
*&---------------------------------------------------------------------*
*&         Author:
*&         Date:
*&---------------------------------------------------------------------*

REPORT z_test_wj03.

TABLES: zhq_income_01.

"假设我们要上传的内容有三个字段：学校ID，名称，地址。然后定义类型。 重点：与EXCEL表的要完全对应上"
TYPES:BEGIN OF ty_zhq_income_01,
        mandt   TYPE sy-mandt ,
        zyear   TYPE zhq_income_01-zyear  ,
        company TYPE zhq_income_01-company,
        zmonth  TYPE zhq_income_01-zmonth ,
        income  TYPE zhq_income_01-income ,
        waers   TYPE zhq_income_01-waers  ,
      END OF ty_zhq_income_01.

"定义参照TY_ZHQ_INCOME_01类型的内表和工作区，用于暂存取到的Excel内容"
DATA:lt_zhq_income_01 TYPE TABLE OF ty_zhq_income_01,
     ls_zhq_income_01 TYPE          ty_zhq_income_01.

"定义内表，用于insert或modify透明表内容"
DATA:gt_zhq_income_01 TYPE TABLE OF zhq_income_01, "直接参照透明表"
     gs_zhq_income_01 TYPE          zhq_income_01.

"选择屏幕"
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
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
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
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
"最后也是最关键的一步"
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               = 6   "列数"
      i_end_row               = 21  "行数"
    TABLES
      intern                  = i_excel
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.

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

  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_zhq_income_01.
  FIELD-SYMBOLS:<f_s>. "这里涉及到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_zhq_income_01 TO <f_s>.
    <f_s> = w_excel-value.
    AT END OF row.
      APPEND ls_zhq_income_01 TO lt_zhq_income_01.
      CLEAR:ls_zhq_income_01.
    ENDAT.
    CLEAR:w_excel.
  ENDLOOP.

  LOOP AT lt_zhq_income_01 INTO ls_zhq_income_01.
    gs_zhq_income_01-mandt   = sy-mandt."客户端号，创建透明表必须包含的字段。"
    gs_zhq_income_01-zyear   = ls_zhq_income_01-zyear    .
    gs_zhq_income_01-company = ls_zhq_income_01-company .
    gs_zhq_income_01-zmonth  = ls_zhq_income_01-zmonth   .
    gs_zhq_income_01-income  = ls_zhq_income_01-income   .
    gs_zhq_income_01-waers   = ls_zhq_income_01-waers    .
    APPEND gs_zhq_income_01 TO gt_zhq_income_01.
    CLEAR:gs_zhq_income_01.
    CLEAR:ls_zhq_income_01.
  ENDLOOP.

  "将数据库中内容更新"
  "这里使用modify，兼顾增加和修改，若主键存在则为修改，否则增加"
  MODIFY zhq_income_01 FROM TABLE gt_zhq_income_01.
  IF sy-subrc = 0.
    COMMIT WORK AND WAIT."成功提交，或者最后程序结束系统也会自动提交"
    DATA:num_tab(6) TYPE c.
    DESCRIBE TABLE gt_zhq_income_01 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.

6、结果图
在这里插入图片描述

休几日

关注

2
点赞
踩
11

收藏

觉得还不错? 一键收藏
2
评论
将EXCEL数据导入SAP数据库表

FORM import_from_file1 . DATA:stbl TYPE lvc_s_stbl. DATA:p_file TYPE rlgrap-filename. DATA:itab TYPE STANDARD TABLE OF alsmex_tabline WITH DEFAULT KEY. DATA:gt_save TYPE TABLE OF zcwgz. FIELD-SYMBOLS:<wa_itab> TYPE alsmex_tabline. CLEAR
复制链接

扫一扫