SAP 导入XLS

Report z_po01.

DATA:   BEGIN OF i_table OCCURS 0,
          a(10)  TYPE c,
          b(10)  TYPE c,
          c(10)  TYPE c,
          d(10)  TYPE c,
          e(10)  TYPE c,
        END OF i_table.
DATA : l_intern TYPE kcde_cells OCCURS 0 WITH HEADER LINE.
DATA : l_index TYPE i.
FIELD-SYMBOLS : <fs>.
PARAMETERS: p_file LIKE rlgrap-filename
              DEFAULT 'c:\xxxx\xxxxxx.xls'.
CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
  EXPORTING
    filename    = p_file
    i_begin_col = 1
    i_begin_row = 1
    i_end_col   = 256
    i_end_row   = 65536
  TABLES
    intern      = l_intern.
SORT l_intern BY row col.
LOOP AT l_intern.
  MOVE l_intern-col TO l_index.
  ASSIGN COMPONENT l_index OF STRUCTURE i_table TO <fs>.
  MOVE l_intern-value TO <fs>.
  AT END OF row.
    APPEND i_table.
    CLEAR i_table.
  ENDAT.
ENDLOOP.
LOOP AT i_table.
  WRITE: / i_table.
ENDLOOP.

 上面的一段代码是很久以前下载过来 应该是可以的现在在贴一段是测试通过的 这是导入数据后用alv输出显示等用户再次确认后在会写入数据库

*&---------------------------------------------------------------------*
*& Report  ZMM_062A
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

report  zmm_062a.

type-pools: slis.
data: gt_fieldcat type slis_t_fieldcat_alv.
data: gs_layout type slis_layout_alv.

tables: mkpf,zguan.


"导入数据
data: begin of im_tab occurs 0 ,
  bilak type char10,      "期间标示
  bukrs like zguan-bukrs,      " 公司
  docnr like zguan-docnr,      "关封申请表编号
  kunli like zguan-kunnr,      " 客户/供应商
  docum like zguan-docum,      "关封序号
  sapma like zguan-sapma,      "SAP料号
  klima like zguan-kunma,      "客户供应商料号
  vsten like zguan-vsten,      "VST项号
  kunen like zguan-kunen,      "客户项号
  eanw1 like zguan-eanw1,     "商品编码
  makt1 like zguan-maktx,     "商品名称
  typbz like zguan-typbz ,    "规格型号
  denum like zguan-denum,     "关封数量
  mein2 like zguan-meins,      "数量单位
  kompf like zguan-kompf,     "转换比例因子
  degew like zguan-degew,      "关封重量
  deewe like zguan-deewe,      "重量单位
  handk like zguan-handk,      "手册号
  adatu type char10,      "关封使用起始日期
  datbi type char10,      "关封使用有效期
  end of im_tab.

data: tab like zguan occurs 0 with header line.


data:begin of iexcel occurs 0.
        include structure alsmex_tabline.  "具有 Excel 数据的表行
data: end of iexcel.


data: begin of updata_tab  occurs 0 ,
 mblnr like mseg-mblnr, "收发货单号
 mblpo like mseg-zeile, "项目
 menge like mseg-menge, "交易数量
end of updata_tab.


data: begin of gt_bukrs occurs 0,  "公司
  bukrs like t001k-bukrs,
  end of gt_bukrs.


data: begin of gt_lifnr occurs 0 , "供应商
  lifnr like lfa1-lifnr,
  end of gt_lifnr.


data: begin of gt_kunnr occurs 0,  "客户
  kunnr like kna1-kunnr,
  end of gt_kunnr.

data: begin of gt_mara occurs 0, "物料
  matnr like mara-matnr,
  end of gt_mara.

data: filename type string,
     lf_rc type c,
     col type i.
field-symbols : <fs>.

data: l_index type i.
data: l_time type sy-uzeit.
data: xa(12) type c.
data: ind2(6) type c.
data: im_date(8) type c.


data:qus type string,
     ans type c.

data: v_date(10) type c,
      p_date like sy-datum.

ranges:s_time for mkpf-budat.


selection-screen begin of block b3 with frame title t3.
parameters: p_bukrs like mseg-bukrs default 'C101' obligatory.

selection-screen begin of line.
selection-screen comment 1(7) p_lif for field p_lifnr .
parameters p_lifnr radiobutton group g1 default 'X'  .  "供应商
selection-screen comment 15(7) p_kunn for field p_kunnr.
parameters p_kunnr radiobutton group g1  .  "客户
selection-screen end of line.

selection-screen end of block b3.



selection-screen begin of block b2 with frame title t2.



" parameters: up_s as checkbox.
parameters: fn1 like rlgrap-filename memory id mol.
select-options s_budat for mkpf-budat modif id g3.
selection-screen uline.

selection-screen begin of line  .
selection-screen comment 1(5) rd1 for field p_update .
parameters p_update radiobutton group  g2 user-command com  .  "供应商
selection-screen comment 15(7) rd2 for field p_add.
parameters p_add radiobutton group g2  default 'X'.  "客户
selection-screen end of  line.

selection-screen end of block b2.

initialization.
  t3 = '选择你导入的清单类型'.
  t2 = '导入数据'.
  rd1 = '覆盖导入'.
  rd2 = '新增导入'.

  p_lif = '收货清单'.
  p_kunn = '发货清单'.

at selection-screen on value-request for fn1.
  call function 'F4_FILENAME'  "文件选择框
    exporting
      program_name  = syst-cprog
      dynpro_number = syst-dynnr
      field_name    = ''
    importing
      file_name     = fn1.


at selection-screen output.
  loop at screen.
    if screen-group1 = 'G3' and p_add = 'X' .
      " screen-invisible = 1.
      screen-active = 0.
      modify screen.
    endif.
  endloop.

at selection-screen.
  filename = fn1.
  call method cl_gui_frontend_services=>file_exist   "判断物理文件是否存在
    exporting
      file                 = filename
    receiving
      result               = lf_rc
    exceptions
      cntl_error           = 1
      error_no_gui         = 2
      wrong_parameter      = 3
      not_supported_by_gui = 4
      others               = 5.

  if lf_rc <> 'X'.
    " message e000(oo) with 'The file is not exist'.
    message e000(oo) with '该文件不存在'.
  endif.



start-of-selection.
  if p_update = 'X' and s_budat-low = 000000.
    message e000(oo) with '请选择覆盖期间'.
    leave list-processing.
  endif.

  perform auth_check.   "权限检查
  perform get_s_fname.  "上传文件
  perform move_data.    "把上传的数据保存到 关联数据库的内表
  perform show.       " 显示数据



*&---------------------------------------------------------------------*
*&      Form  get_s_fname
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form get_s_fname.

  call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    exporting
      filename                = fn1
      i_begin_col             = 1
      i_begin_row             = 1
      i_end_col               = 256
      i_end_row               = 65536
    tables
      intern                  = iexcel
    exceptions
      inconsistent_parameters = 1
      upload_ole              = 2
      others                  = 3.
*IF SY-SUBRC <> 0.
** MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
**         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
*ENDIF.

  if sy-subrc <> 0.
    "  message 'Open the file error' type 'I'.
    message e000(oo) with '打开文件失败'.
  else.
    loop at iexcel.
      if iexcel-row <> 1.
        move iexcel-col to l_index.
        assign component l_index of structure im_tab to <fs> .
        translate iexcel-value  to upper case.
        move iexcel-value to <fs>.
      endif.
      at end of row.
        if iexcel-row <> 1.
          append im_tab.
          clear im_tab.
        endif.
      endat.
    endloop.
  endif.

endform.                    "get_s_fname


*&---------------------------------------------------------------------*
*&      Form  show
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form show.
  perform fieldcat_init using :
         'BILAK' '导入日期'        13 '' '' '' '' '' '' '' '' '',
        " 'EWAEL'  '主键标示'        13 '' '' '' '' '' '' '' '' '',
         'BUKRS'     '公司'        13 '' '' '' '' '' '' '' '' '',
         'DOCNR' '关封申请表编号'        13 '' '' '' '' '' '' '' '' '',
         'KUNLI' '客户/供应商 '        15 '' '' '' '' '' '' '' '' '',
         'DOCUM' '关封序号'       8  '' '' '' '' '' '' '' '' '',
         'SAPMA' 'SAP料号'       7  '' '' '' '' '' '' '' '' '',
         'KLIMA' '客户供应商料号'     20 '' '' '' '' '' '' '' '' '',
         'VSTEN' 'VS项号'       14 '' '' '' '' '' '' '' '' '',
         'KUNEN' '客户项号'      10 '' '' '' '' '' '' '' '' '',
         'EANW1' '商品编码'        8 '' '' '' '' '' '' '' '' '',
         'MAKT1' '商品名称'          10 '' '' '' '' '' '' '' '' '',
         'TYPBZ' '规格型号'   10 '' 'Q' '' '' '' '' '' '' '',
         'DENUM' '关封数量'        10 ' ' 'Q' '' '' '' '' '' '' '',
         'MEIN2' '数量单位'        10 ' ' '' '' '' '' '' '' '' '',
         'KOMPF' '转换比例因子'            10 ' ' '' '' '' '' '' '' '' '',
         'DEGEW' '关封重量'        10 ' ' '' '' '' '' '' '' '' '',
         'DEEWE' '重量单位'        10 ' ' '' '' '' '' '' '' '' '',
         'HANDK' '手册号'        10 ' ' '' '' '' '' '' '' '' '',
         'ADATU' '关封使用起始日期'        10 ' ' 'Q' '' '' '' '' '' '' '',
         'DATBI' '关封使用有效期'    10 ' ' '' '' '' '' '' '' '' ''.


  constants cns_pf_status type slis_formname value 'ALV_PF_STATUS'.       "alv自定义按钮
  constants cns_user_command type slis_formname value 'ALV_USER_COMMAND'. "alv自定义按钮响应事件
  gs_layout-info_fieldname = 'COLOR'.
  call function 'REUSE_ALV_GRID_DISPLAY'
       exporting
            i_callback_program       = sy-repid       "当前程序
*            i_callback_pf_status_set = 'SET_PF_STATUS'
*            i_callback_user_command  = 'USER_COMMAND'
            i_callback_top_of_page            = 'F_TOP_OF_PAGE2 '
            it_fieldcat              = gt_fieldcat[]  "子函数field填充的各列
            is_layout                = gs_layout    "子函数 layout_buidld填充格式

            i_callback_pf_status_set = cns_pf_status " 设置alv 自定义按钮GUI
            i_callback_user_command =  cns_user_command "设置响应事件

            i_save                   = 'X'
       tables
            t_outtab                 = im_tab.
endform.                    "show



*&---------------------------------------------------------------------*
*&      Form  move_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form move_data.

  if p_kunnr = 'X'.
    select kunnr into table gt_kunnr from kna1.  "客户
  else.
    select lifnr into table gt_lifnr from lfa1.  "供应商
  endif.
  select  matnr into table gt_mara from mara.
  select bukrs into table gt_bukrs from t001k.

  data: l_index type i value 1.

  read table im_tab index 1.
  s_time-low = im_tab-bilak.
  s_time-high = im_tab-bilak.
  loop at im_tab.


    data: l_type(4) type c.
    call function 'NUMERIC_CHECK'
      exporting
        string_in = im_tab-sapma
      importing
        htype     = l_type.

    if l_type = 'NUMC' and im_tab-sapma >= '000000000000000000'  and im_tab-sapma <= '999999999999999999'.
      call function 'CONVERSION_EXIT_ALPHA_INPUT'
        exporting
          input  = im_tab-sapma
        importing
          output = im_tab-sapma.
      tab-sapma = im_tab-sapma.
    endif.


*--------判断导入的 公司 客户 物料----------*
    if p_kunnr = 'X'.
      read table gt_kunnr with key kunnr = im_tab-kunli.
      if  sy-subrc <> 0.
        message e000(oo) with '导入的客户不存在'.
      endif.

    else.
      read table gt_lifnr with key lifnr = im_tab-kunli.
      if  sy-subrc <> 0.
        message e000(oo) with '导入的供应商不存在'.
      endif.
    endif.

    read table gt_mara with key matnr = im_tab-sapma.
    if  sy-subrc <> 0.
      message e000(oo) with '导入的物料不存在'.
    endif.

    read table gt_bukrs with key bukrs = im_tab-bukrs.
    if  sy-subrc <> 0.
      message e000(oo) with '导入公司不存在'.
    endif.

    if im_tab-handk = ''.
      message e000(oo) with '导入的手册号为空'.
    endif.

    if im_tab-degew = ''.
      message e000(oo) with '导入的关封重量为空'.
    endif.

*----------------- 转化成大写--------------*
*    translate im_tab-bukrs to upper case. "工厂
*    translate im_tab-kunli to upper case.
*    translate im_tab-sapma to upper case.
*    translate im_tab-deewe to upper case. "重量单位
*    translate im_tab-handk to upper case. "手册号

    if s_time-low > im_tab-bilak.
      s_time-low = im_tab-bilak.
    endif.
    if s_time-high < im_tab-bilak.
      s_time-high = im_tab-bilak.
    endif.
    move-corresponding im_tab to tab.
*--------------查找日期特俗符号  为数字类型补0--------*

    v_date  =  im_tab-bilak   .
    perform date_check using v_date changing p_date.
    if p_date = ' '.
      message e000(oo) with '导入日期格式有误请检查请按照20121212'.
    else.
      tab-bilak =  p_date.
    endif.

    clear: v_date,p_date.
    v_date =  im_tab-adatu .
    perform date_check using v_date changing p_date.
    if p_date = ' '.
      message e000(oo) with '关封起始日期中有误请检查'.
    else.
      tab-adatu = p_date.
    endif.

    clear: v_date,p_date.
    v_date = im_tab-datbi.
    perform date_check using v_date changing p_date.
    if p_date = ' '.
      message e000(oo) with '关封结束日期中有误请检查'.
    else.
      tab-datbi = p_date.
    endif.



*-----------------***********----------*

    get time.
    l_time = sy-uzeit.
    xa = l_time .
    ind2 = l_index.
    condense ind2. "去掉首位空格
    concatenate xa ind2 into tab-ewael.
    if p_kunnr = 'X'.
      tab-flag  = ' ' .
      tab-kunnr = im_tab-kunli.
      tab-kunma = im_tab-klima.
    else.
      tab-lifnr = im_tab-kunli.
      tab-lifma = im_tab-klima.
      tab-flag = 'X'.
    endif.
    tab-meins = im_tab-mein2.
    tab-maktx = im_tab-makt1.

    append tab.
    l_index = l_index + 1.
  endloop.

  s_time-sign = 'I'.
  s_time-option = 'BT'.
  append s_time.

endform.                    "move_data


*&---------------------------------------------------------------------*
*&      Form  alv_user_command
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->R_UCOMM      text
*      -->RS_SELFIELD  text
*----------------------------------------------------------------------*
form alv_user_command using r_ucomm like sy-ucomm
                     rs_selfield type slis_selfield.

  data: s type string.
  case r_ucomm.
    when '&EXECUTE'.

      if p_update = 'X'.
        if s_budat-high = '00000000'.
          s_budat-high = s_budat-low.
        endif.
*============判断输入日期与导入日期是否有差异

        if s_time-low < s_budat-low  or s_time-high > s_budat-high.
          concatenate '导入时间是:' s_time-low '-' s_time-high '　   '
           '覆盖时间是:' s_budat-low '-' s_budat-high  '　　　　存在差异是否导入' into s.

          qus = s.
          call function 'POPUP_TO_CONFIRM'
            exporting
              text_question = qus
            importing
              answer        = ans.
        else.
*============日期没有差异直接保存
          if p_kunnr = 'X'.
            delete from zguan where zguan~bilak in s_budat and flag = ''.

          else.
            delete from zguan where zguan~bilak in s_budat and flag = 'X'.

          endif.
          modify zguan from table tab[].
        endif.
*============点击确认后保存
        if ans = '1'.
          if p_kunnr = 'X'.
            delete from zguan where zguan~bilak in s_budat and flag = ''.

          else.
            delete from zguan where zguan~bilak in s_budat and flag = 'X'.

          endif.
          modify zguan from table tab[].
        endif.

        if sy-subrc = 0 and ans = '2' or ans = 'A'.
          " message e000(oo) with '操作成功'.
          message '操作取消' type 'S'.
          " elseif sy-subrc = 0 and ans = 1.
          "  message '操作成功' type 'S'.
        else.
          message '操作成功' type 'S'.
        endif.

      else.
        modify zguan from table tab[].    "------新增保存
        if sy-subrc = 0.
          message '操作成功' type 'S'.
        endif.
      endif.

  endcase.

endform.                    "alv_user_command



*&---------------------------------------------------------------------*
*&      Form  alv_pf_status
*&---------------------------------------------------------------------*
*       text       设置GUI状态
*----------------------------------------------------------------------*
*      -->RT_EXTAB   text
*----------------------------------------------------------------------*
form alv_pf_status using rt_extab type slis_t_extab.
  set pf-status 'STANDARD_004' excluding rt_extab.
endform .                    "alv_pf_status



*&---------------------------------------------------------------------*
*&      Form  fieldcat_init
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->FIELD_NAME         text
*      -->FIELD_TEXT         text
*      -->FIELD_LENTH        text
*      -->FIELD_EDIT         text
*      -->FIELD_TYPE         text
*      -->FIELD_KEY          text
*      -->FIELD_HOTSPOT      text
*      -->FIELD_CHECKBOX     text
*      -->FIELD_NO_ZERO      text
*      -->FIELD_REF_TABNAME  text
*      -->FIELDCAT_DO_SUM    text
*      -->FIELD_EMPHASIZE    text
*----------------------------------------------------------------------*
form fieldcat_init using
  field_name      type c
  field_text      type c
  field_lenth     type i
  field_edit      type c
  field_type      type c
  field_key       type c
  field_hotspot   type c
  field_checkbox  type c
  field_no_zero   type c
  field_ref_tabname type c
  fieldcat_do_sum type c"        总计列值总和
  field_emphasize type c.
  data: ls_fieldcat type slis_fieldcat_alv.
  clear ls_fieldcat.

  ls_fieldcat-fieldname = field_name.
  ls_fieldcat-seltext_l = field_text.
  ls_fieldcat-seltext_m = field_text.
  ls_fieldcat-seltext_s = field_text.
  ls_fieldcat-checkbox  = field_checkbox.
*  LS_FIELDCAT-ROUND = 1.
  ls_fieldcat-edit = field_edit.
  if field_type = 'Q'.
    ls_fieldcat-just     = 'R'.
    ls_fieldcat-datatype = 'QUAN'.
    ls_fieldcat-inttype = 'C'.

  else.
    ls_fieldcat-just = 'L'.
  endif.
  ls_fieldcat-key  = field_key.
  ls_fieldcat-hotspot = field_hotspot .
  ls_fieldcat-outputlen = field_lenth.
  ls_fieldcat-no_zero = field_no_zero.
  ls_fieldcat-ref_tabname = field_ref_tabname.
  ls_fieldcat-do_sum  = fieldcat_do_sum.
  ls_fieldcat-emphasize = field_emphasize .

  append ls_fieldcat to gt_fieldcat.

endform.                    "fieldcat_init



*&---------------------------------------------------------------------*
*&      Form  f_top_of_page2
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form f_top_of_page2.
  data: i_header  type slis_t_listheader,
        wa_header type slis_listheader.

  data: l_name type string,
        name_frist like adrp-name_first,
        name_last like adrp-name_last.


  select single adrp~name_first adrp~name_last
  into (name_frist, name_last)
  from adrp
  inner join usr21 on adrp~persnumber = usr21~persnumber
  where usr21~bname = sy-uname.

  if sy-subrc = 0.
    concatenate name_last name_frist into l_name.
  else.
    l_name = sy-uname.
  endif.
  clear name_frist.
  clear name_last.

  data: l_date type string.

  concatenate sy-datum+0(4)'-'
              sy-datum+4(2)'-'
              sy-datum+6(2)'' into l_date.


  wa_header-typ = 'H'.
  if p_kunnr = 'X'.
    wa_header-info = '发货清单'.
  else.
    wa_header-info = '收货清单'.
  endif.
  append wa_header to i_header.
  clear wa_header.

  concatenate '制表人:' l_name into l_name.
  concatenate '制表日期:' l_date into l_date.
  wa_header-typ ='S'.
  wa_header-key = l_name.
  wa_header-info = l_date.
  append wa_header to i_header.
  clear wa_header.

  call function 'REUSE_ALV_COMMENTARY_WRITE'
    exporting
      it_list_commentary       = i_header
*   I_LOGO                   =
*   I_END_OF_LIST_GRID       =
    i_alv_form               = 'X'.
endform.                    "f_top_of_page2




*&---------------------------------------------------------------------*
*&      Form  date_check
*&---------------------------------------------------------------------*
form date_check using v_date changing p_date.



  data: year(8)  type n,
        month(3) type n,
        day(3)   type n.

  split v_date at '/' into year month day.
  if strlen( year ) > 4.
    split v_date at '-' into year month day.
  endif.

  if strlen( year ) > 4.
    split v_date at '.' into year month day.
  endif.

  if strlen( year ) > 4.
    p_date = year.
  else.
    month = 100 + month.
    day = 100 + day.
    concatenate year month+1(2) day+1(2) into p_date.
  endif.

  call function 'DATE_CHECK_PLAUSIBILITY'
    exporting
      date                      = p_date
    exceptions
      plausibility_check_failed = 1
      others                    = 2.

  if sy-subrc ne 0.
    p_date = ''.
  endif.

endform.                    "date_check


*&---------------------------------------------------------------------*
*&      Form  权限检查
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form auth_check.
  authority-check object 'M_MATE_BUK'
                    id 'ACTVT'  dummy  " 作业
                    id 'BUKRS'  field p_bukrs.  " 公司代码
  if sy-subrc <> 0.
    message s888(sabapdocu) with  '无相应的公司权限，请重新输入'.
    leave list-processing.
  endif.

endform.                    "auth_check