SAP 中的EXCEL批导入模板

1. Introduction

对于字段做数字、日期、时间格式转换，标准字段的转换例程转换。

2 Code

*&---------------------------------------------------------------------*
* ==批导-导入-模板
*
*&---------------------------------------------------------------------*


types: begin of typ_imp,
         zaufnr    type zppt017-zaufnr,
         aufnr     type zppt017-aufnr,
         plnbez    type zppt017-plnbez,
         maktx_p   type makt-maktx,
         werks     type zppt017-werks,
         matnr     type zppt017-matnr,
         maktx     type makt-maktx,
         zscjt     type zppt017-zscjt,
         zjycl     type zppt017-zjycl,
         maktx_c   type makt-maktx,

         zjycl_new type zppt017-zjycl, "新材料
         zjypc     type zppt017-zjypc,
         zjypc_new type zppt017-zjypc, "新批次
         zjhsl     type zppt017-zjhsl,
         lgort     type zppt017-lgort,
       end of typ_imp.

data: begin of gs_out,
        sel,
        op    type n,
        icon  type char04,
        msg   type char100.

        include type typ_imp.
data:   enmng type resb-enmng,
        data  type table of zppt017,

      end of gs_out.
data: gt_out like table of gs_out.
field-symbols: <fs_out> like gs_out.



parameters: p_file type rlgrap-filename memory id pfn.


at selection-screen on value-request for p_file.
  perform frm_select_file changing p_file.

end-of-SELECTION.
  PERFORM frm_upload.


form frm_select_file changing cv_fn.

  data: lt_ft type filetable,
        ls_ft type file_table,
        lv_rc type i,
        lv_ua type i.

  data:lv_fn type string.

  data: lv_filter type string.
  lv_filter = cl_gui_frontend_services=>filetype_excel && ';' && cl_gui_frontend_services=>filetype_text.

  call method cl_gui_frontend_services=>file_open_dialog
    exporting
      file_filter             = lv_filter
    changing
      file_table              = lt_ft
      rc                      = lv_rc
*     user_action             = lwk_ua
    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 id sy-msgid type sy-msgty number sy-msgno
               with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  else.
    check lv_ua = 0.
    read table lt_ft into ls_ft index 1.
    if sy-subrc = 0.
      cv_fn = ls_ft-filename.
    endif.
  endif.
endform. " VQ_FN

*&---------------------------------------------------------------------*
*&      Form  UPLOAD
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form frm_upload .
*上传：
  data: lt_excel type table of zalsmex_tabline.
  field-symbols: <value> type any.
  field-symbols: <f_table> type standard table,
                 <f_struc> type any.
  data: lo_tabdescr type ref to cl_abap_structdescr.
  data: ls_field type dfies,
        lt_dfies type ddfields.

  data: lv_convexit type dfies-convexit,
        lv_funcname type rs38l_fnam.

  "可以考虑修改函数
  call function 'ZALSM_EXCEL_TO_INTERNAL_TABLE'
*倒入EXCEL数据到内表
    exporting
      filename                = p_file
      i_begin_col             = 1
      i_begin_row             = 2
      i_end_col               = 15
      i_end_row               = 9999
    tables
      intern                  = lt_excel
    exceptions
      inconsistent_parameters = 1
      upload_ole              = 2
      others                  = 3.
  if sy-subrc ne 0.
    return.
  endif.
  sort lt_excel by row col value.


  "导入数据结构定义
  data: ls_imp type typ_imp,
        lt_imp type table of typ_imp.
  clear: lt_imp[].
  assign ls_imp to <f_struc>.
  assign lt_imp to <f_table>.

  lo_tabdescr ?= cl_abap_structdescr=>describe_by_data( <f_struc> ).
  call method cl_salv_data_descr=>read_structdescr
    exporting
      r_structdescr = lo_tabdescr
    receiving
      t_dfies       = lt_dfies.

  loop at lt_excel into data(ls_excel).
    assign component ls_excel-col of structure <f_struc> to field-symbol(<f_field>).
    if <f_field> is assigned and ls_excel-value is not initial.
      "假如接受类型是数值，发现数值
      data: datatype type ref to cl_abap_datadescr.
      datatype ?= cl_abap_typedescr=>describe_by_data( <f_field> ).
      case datatype->type_kind.
        when 'P' or 'I' .
          perform find_num changing ls_excel-value.
        when 'D'.
          perform find_date changing ls_excel-value.
        when 'T'.
          perform find_time changing ls_excel-value.
        when others.
      endcase.

      condense ls_excel-value.
      <f_field> = conv #( ls_excel-value ).
      clear lv_convexit.
      lv_convexit = value #( lt_dfies[ ls_excel-col ]-convexit optional ).
      if lv_convexit is not initial.
        lv_funcname = |CONVERSION_EXIT_{ lv_convexit }_INPUT|.
        call function lv_funcname
          exporting
            input  = <f_field>
          importing
            output = <f_field>.
      endif.
    endif.

    at end of row.
      append <f_struc> to <f_table>.
      clear <f_struc>.
    endat.
  endloop.

  call function 'SAPGUI_PROGRESS_INDICATOR'
    exporting
      percentage = 50
      text       = '导入数据成功，检查数据中'.

  "插入到显示内表
  clear: gt_out[].
  loop at lt_imp into ls_imp.
    clear: gs_out.
    move-corresponding ls_imp to gs_out.

    append gs_out to gt_out.
  endloop.
endform.                    " UPLOAD



"匹配数字
form find_num changing text.
  data: l_alpha_numeric type string.
  data: l_strlen type i.
  data: l_char type c.
  l_alpha_numeric = '0123456789.'.
  l_strlen = strlen( text ).
  while l_strlen <> 0.
    l_strlen = l_strlen - 1.
    l_char = text+l_strlen(1).
    if l_alpha_numeric na l_char.
      replace section offset l_strlen length 1 of text with ''.
    endif.
  endwhile.
endform.

"匹配日期
form find_date changing text.
  data: lv_split type c.
  data: lv_year type n length 4.
  data: lv_month type n length 2.
  data: lv_month_i type i.
  data: lv_day type n length 2.
  data: lv_day_i type i.
  if text ca '.' .
    lv_split = '.'.
  endif.
  if text ca '/'.
    lv_split = '/'.
  endif.
  if text ca '-'.
    lv_split = '-'.
  endif.

  if lv_split is not initial.
    split text at lv_split into lv_year lv_month lv_day.
    lv_month = lv_month_i = lv_month. "补0
    lv_day = lv_day_i = lv_day. "补0
    text = lv_year && lv_month && lv_day.

  else.
    data: l_alpha_numeric type string.
    data: l_strlen type i.
    data: l_char type c.
    l_alpha_numeric = '0123456789'.
    l_strlen = strlen( text ).
    while l_strlen <> 0.
      l_strlen = l_strlen - 1.
      l_char = text+l_strlen(1).
      if l_alpha_numeric na l_char.
        replace section offset l_strlen length 1 of text with ''.
      endif.
    endwhile.
  endif.
endform.

"匹配时间
form find_time changing text.
  data: lv_split type c.
  data: lv_hour type n length 2.
  data: lv_hour_i type i.
  data: lv_min type n length 2.
  data: lv_min_i type i.
  data: lv_sec type n length 2.
  data: lv_sec_i type i.
  if text ca ':' .
    lv_split = ':'.
  endif.
  if text ca '：'.
    lv_split = '：'.
  endif.

  if lv_split is not initial.
    split text at lv_split into lv_hour lv_min lv_sec.
    lv_hour = lv_hour_i = lv_hour. "补0
    lv_min = lv_min_i = lv_min. "补0
    lv_sec = lv_sec_i = lv_sec. "补0
    text = lv_hour && lv_min && lv_sec.

  else.
    data: l_alpha_numeric type string.
    data: l_strlen type i.
    data: l_char type c.
    l_alpha_numeric = '0123456789'.
    l_strlen = strlen( text ).
    while l_strlen <> 0.
      l_strlen = l_strlen - 1.
      l_char = text+l_strlen(1).
      if l_alpha_numeric na l_char.
        replace section offset l_strlen length 1 of text with ''.
      endif.
    endwhile.
  endif.
endform.