abap使用EXCEL表格导入

主要有两种，都需要表格每列跟定义的字段想对应

一种是很早就有的，但是每个值需要单独赋值，很少遇到莫名奇妙导入不进去的问题，可以先获取表字段，

再使用指针赋值(参考后面的代码)。推荐使用！ALSM_EXCEL_TO_INTERNAL_TABLE

PERFORM frm_file_data2.

另外一种是整表直接导入，可以先判定操作系统再做选择方法

PERFORM frm_get_filed.

再附上form

FORM frm_file_data2 .
  DATA: msg(20) TYPE c.
  DATA: lt_data  TYPE alsmex_tabline OCCURS 0 WITH HEADER LINE.
  DATA  lv_filename TYPE rlgrap-filename.
  lv_filename = p_file2.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'    "读取excel文件中的内容
    EXPORTING
      filename                = lv_filename
      i_begin_col             = '1'
      i_begin_row             = '2'
      i_end_col               = '28'
      i_end_row               = '99999'
    TABLES
      intern                  = lt_data
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.
  LOOP AT lt_data.
    CASE  lt_data-col.
      WHEN 1.
        gs_output-zxuhao = lt_data-value."序号
      WHEN 2.
        gs_output-bukrs = lt_data-value."公司代码
        IF sy-mandt = '886'.
          AUTHORITY-CHECK OBJECT 'ZHL_AUTHOR'
             ID 'ZBUKRS' FIELD gs_output-bukrs.
          IF sy-subrc IS INITIAL.
          ELSE.
            CLEAR: msg.
            CONCATENATE '无权公司' gs_output-bukrs '权限!' INTO msg.
            MESSAGE msg TYPE 'S' DISPLAY LIKE 'E'.
            LEAVE TO TRANSACTION sy-tcode.
          ENDIF.
        ENDIF.
      WHEN 3.
        gs_output-budat = lt_data-value."过账日期
      WHEN 4.
        gs_output-bldat = lt_data-value."凭证日期
      WHEN 5.
        gs_output-blart = lt_data-value."凭证类型
      WHEN 6.
        gs_output-waers = lt_data-value."货币码
      WHEN 7.
        gs_output-kursr = lt_data-value."汇率
      WHEN 8.
        gs_output-bktxt = lt_data-value."抬头文本
      WHEN 9.
        gs_output-bschl = lt_data-value."记账代码
      WHEN 10.
        gs_output-hkont = lt_data-value."科目
        PERFORM frm_add_zero USING gs_output-hkont CHANGING gs_output-hkont.
      WHEN 11.
        gs_output-kostl = lt_data-value."成本中心
      WHEN 12.
        gs_output-mwsk1 = lt_data-value."税码
      WHEN 13.
        gs_output-zuonr = lt_data-value."分配编号
      WHEN 14.
        gs_output-sgtxt = lt_data-value."项目文本
      WHEN 15.
        gs_output-aufnr = lt_data-value."订单号
      WHEN 16.
        gs_output-xref1 = lt_data-value."参考一
      WHEN 17.
        gs_output-xref2 = lt_data-value."参考二
      WHEN 18.
        gs_output-dmbtr = lt_data-value."金额
*      WHEN 19.
*        gs_output-kunnr = lt_data-value."客户
      WHEN 19.
        gs_output-umskz = lt_data-value."特殊总账标识
*      WHEN 21.
*        gs_output-lifnr = lt_data-value."供应商
      WHEN 20.
        gs_output-zterm = lt_data-value."付款条款
      WHEN 21.
        gs_output-zfbdt = lt_data-value."付款起算日期、
      WHEN 22.
        gs_output-vkorg = lt_data-value."销售组织
      WHEN 23.
        gs_output-vtweg = lt_data-value."分销渠道
      WHEN 24.
        gs_output-kndnr = lt_data-value."客户(获利能力段)
      WHEN 25.
        gs_output-fkber = lt_data-value."功能范围
      WHEN 26.
        gs_output-skost = lt_data-value."发送方成本中心
      WHEN 27.
        gs_output-matnr = lt_data-value."物料
      WHEN 28.
        gs_output-rstgr = lt_data-value."付款原因代码
    ENDCASE.

    AT END OF row.
      APPEND gs_output TO gt_output.
      CLEAR gs_output.
    ENDAT.

  ENDLOOP.
  IF gt_output[] IS INITIAL.
    MESSAGE '导入数据为空' TYPE 'S' DISPLAY LIKE 'E'.
    STOP.
  ENDIF.
ENDFORM.

 

FORM frm_get_filed .
  DATA: lv_i_tab_raw_data TYPE truxs_t_text_data,
        lv_i_filename     LIKE rlgrap-filename.
  CLEAR gt_output.
  DATA(lv_platform_code) = cl_gui_frontend_services=>get_platform( ).
  IF lv_platform_code EQ '14'." WINDOWS
    lv_i_filename = p_file2.
    CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
      EXPORTING
        i_line_header        = 'X'
        i_tab_raw_data       = lv_i_tab_raw_data
        i_filename           = lv_i_filename
      TABLES
        i_tab_converted_data = gt_output
      EXCEPTIONS
        conversion_failed    = 1
        OTHERS               = 2.
    IF sy-subrc <> 0.
    ENDIF.
  ELSEIF lv_platform_code EQ '13'." MAC
    cl_gui_frontend_services=>gui_upload(
      EXPORTING
        filename = p_file2
        has_field_separator = 'X'
        filetype = 'ASC'
      CHANGING
        data_tab = gt_output
      EXCEPTIONS
        file_open_error = 1
        file_read_error = 2
        no_batch = 3
        gui_refuse_filetransfer = 4
        invalid_type = 5
        no_authority = 6
        unknown_error = 7
        bad_data_format = 8
        header_not_allowed = 9
        separator_not_allowed = 10
        header_too_long = 11
        unknown_dp_error = 12
        access_denied = 13
        dp_out_of_memory = 14
        disk_full = 15
        dp_timeout = 16
        not_supported_by_gui = 17
        error_no_gui = 18
        OTHERS = 19 ).
    IF sy-subrc <> 0.
    ENDIF.
  ELSE.
    MESSAGE e008(zfico039).   
ENDIF. 

ENDFORM.

View Code

2021年12月15日：

获取表结构和使用指针导入，无需一个个增加。

  "获取GT_ITEM[]的字段
  DATA(lo_table_basic) = CAST cl_abap_tabledescr( cl_abap_tabledescr=>describe_by_data( gt_item ) ).
  DATA(lo_struct_basic) = CAST cl_abap_structdescr( lo_table_basic->get_table_line_type( ) ).
  DATA(lt_component_basic_all) = lo_struct_basic->get_components( ).

  DATA(lt_component_include) = lt_component_basic_all.
  DELETE lt_component_include WHERE as_include NE abap_true.
  DELETE lt_component_basic_all WHERE as_include EQ abap_true.

  "展开include结构中
  LOOP AT lt_component_include ASSIGNING FIELD-SYMBOL(<fs_component>).
    lo_struct_basic = CAST cl_abap_structdescr( <fs_component>-type ).
    DATA(lt_components) = lo_struct_basic->get_components( ).
    APPEND LINES OF lt_components TO lt_component_basic_all.
  ENDLOOP.

  "获取EXCEL数据
  FIELD-SYMBOLS :<dyn_table> TYPE table,
                 <dyn_wa>    TYPE any,
                 <dyn_field> TYPE any,
                 <fs_str>    TYPE any.

  DATA: lv_end TYPE c.
  DATA: lt_data  TYPE alsmex_tabline OCCURS 0 WITH HEADER LINE.
  DATA  lv_filename TYPE rlgrap-filename.
  DATA lv_tabix TYPE sy-tabix.
  lv_filename = p_path.
  DATA: lv_lines TYPE sy-tabix.
  lv_lines = lines( lt_component_basic_all ).

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'    "读取EXCEL文件中的内容
    EXPORTING
      filename                = lv_filename
      i_begin_col             = '1'
      i_begin_row             = '2'
      i_end_col               = lv_lines "'25'
      i_end_row               = '99999'
    TABLES
      intern                  = lt_data
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.

  LOOP AT lt_data.
*    CLEAR LV_END.
*    AT END OF ROW.
*      LV_END = 'X'.
*    ENDAT.
    lv_tabix = lt_data-col.
*    ADD 1 TO LV_TABIX.
    READ TABLE lt_component_basic_all INTO DATA(ls_basica_all) INDEX lv_tabix.
    IF sy-subrc EQ 0.
      ASSIGN COMPONENT ls_basica_all-name OF STRUCTURE wa_item TO <fs_str>.
      <fs_str> = lt_data-value.
    ENDIF.

    AT END OF row.
*      APPEND <DYN_WA> TO <DYN_TABLE>.
*      MOVE-CORRESPONDING <dyn_wa> TO gs_input.
*      gs_input-so = |{ gs_input-so ALPHA = IN }|. "补前置0
      APPEND wa_item TO GT_item.
      CLEAR: wa_item.
    ENDAT.

  ENDLOOP.

View Code

使用ALSM_EXCEL_TO_INTERNAL_TABLE的长度有限制，需复制并更改下value长度，这里设置的为1000.

有些文档是从别的软件中直接导出的，会有双引号的问题，比如旺店通的。要先处理下。

*旺店通导出的表格，同一个字符串，会分成两行，比如"这个换行噢 "，会变成两行，两个双引号是不需要的，第二个双引号前面的是换行符，所以会变成两行
*"这个换行噢
* "
  LOOP AT lt_data ASSIGNING FIELD-SYMBOL(<ls_data>).
    AT END OF row.
      lv_end2 = 'X'.
    ENDAT.
    <ls_data>-col = <ls_data>-col - lv_col.
    IF <ls_data>-value+0(1) = '"'.
      CONDENSE <ls_data>-value NO-GAPS.
      IF <ls_data>-value = '"'.
        ADD 1 TO lv_col.
        DELETE lt_data[].
        CONTINUE.
      ELSE.
        <ls_data>-value+0(1) = ''.
      ENDIF.
    ENDIF.
  ENDLOOP.