ppt免费下载的网站,手机网站 幻灯片js,怎么去掉2345网址导航,wordpress去广告插件EXCEL批导函数相信大家熟悉的不能再熟悉了#xff0c;但是特殊场景不同函数对工作的影响真的很大。
整理本篇文章是因为公司电脑统一使用了加密系统#xff0c;通过先前的统一函数无法直接上传#xff0c;每次都要先另存为TEXT文本后上传#xff0c;用户体验非常差#x…EXCEL批导函数相信大家熟悉的不能再熟悉了但是特殊场景不同函数对工作的影响真的很大。
整理本篇文章是因为公司电脑统一使用了加密系统通过先前的统一函数无法直接上传每次都要先另存为TEXT文本后上传用户体验非常差使用起来属实不便。后来偶然的发现函数 ALSM_EXCEL_TO_INTERNAL_TABLE 对 加密的EXCEL 同样可以丝滑读取所以重新封装了下在这里做个记录。同时新封装函数不但可以输出数据还可以输出首行列名在列名变化的需求中常用。 废话不多说直接上代码。如果代码臃肿自行精简
FUNCTION zz_excel_to_internal_table.
*----------------------------------------------------------------------
**本地接口
* IMPORTING
* REFERENCE(IV_FILENAME) TYPE RLGRAP-FILENAME
* REFERENCE(IV_SKIP) TYPE AS4FLAG DEFAULT X
* REFERENCE(IV_SKIP_TO) TYPE I OPTIONAL
* REFERENCE(IV_END_COL) TYPE I OPTIONAL
* REFERENCE(IV_END_ROW) TYPE I DEFAULT 65000
* TABLES
* T_DATA TYPE STANDARD TABLE
* T_TITLE TYPE STANDARD TABLE OPTIONAL
* T_RETURN TYPE BAPIRET2_T OPTIONAL
*----------------------------------------------------------------------
*——————————————————————————————————————注意事项1、默认跳过第一行则第一行作为抬头行接收如果不跳过则不作为抬头行2、结束列数 作为读取文件的最大列数如果不设置默认以传入内表的列数为准3、结束行数 默认值为65000行数值过大系统本身就无法处理导致卡顿4、函数逻辑 以通用的顺序作为判断依据需自行识别接收表和模板字段的匹配顺序应严格保持一致
*——————————————————————————————————————
*Global data declarationsTYPE-POOLS: truxs.DATA: cxroot TYPE REF TO cx_root,lv_file TYPE rlgrap-filename,lv_string TYPE string, 文件地址lv_file_name TYPE string, 文件名称含地址lv_file_type TYPE string, 文件类型后缀lv_message TYPE string,lt_intern TYPE TABLE OF zst_mm03_alsmex_tabline WITH HEADER LINE , alsmex_tablinels_intern TYPE zst_mm03_alsmex_tabline, alsmex_tabline,lv_skip_to TYPE i,lv_end_col TYPE i,lv_end_row TYPE i,ref_tdescr TYPE REF TO cl_abap_typedescr,ref_tdescr_title TYPE REF TO cl_abap_typedescr,ref_sdescr TYPE REF TO cl_abap_structdescr,ref_sdescr_title TYPE REF TO cl_abap_structdescr,ref_cx TYPE REF TO cx_root,ref_data TYPE REF TO data,ref_data_title TYPE REF TO data,ls_new_line TYPE REF TO data,ls_new_line_title TYPE REF TO data,ls_comp TYPE abap_compdescr,ls_fcat TYPE lvc_s_fcat,lt_fcat TYPE lvc_t_fcat,lt_fcat_title TYPE lvc_t_fcat,lt_raw TYPE truxs_t_text_data,lt_file TYPE STANDARD TABLE OF string.FIELD-SYMBOLS: lfs_table TYPE STANDARD TABLE,lfs_table_title TYPE STANDARD TABLE,lfs_struc TYPE any,lfs_struc_title TYPE any,lfs_field TYPE any,lfs_value TYPE any,lfs_value_title TYPE any,lfs_intern TYPE zst_mm03_alsmex_tabline. alsmex_tabline.检查文件路径IF iv_filename IS INITIAL.CLEAR: lv_message.lv_message |File path is null,please check!|.PERFORM frm_append_message TABLES t_return USING 00 001 E lv_message .RETURN.ENDIF.解析数据内表CALL METHOD cl_abap_structdescrdescribe_by_dataEXPORTINGp_data t_dataRECEIVINGp_descr_ref ref_tdescr.TRY.ref_sdescr ? ref_tdescr.CATCH cx_root INTO ref_cx.CLEAR: lv_message.lv_message ref_cx-get_text( ).PERFORM frm_append_message TABLES t_return USING 00 001 E lv_message .RETURN.ENDTRY.组装字段名用于生成新的动态内表CLEAR:lv_end_col.LOOP AT ref_sdescr-components INTO ls_comp.CLEAR: ls_fcat.ls_fcat-fieldname ls_comp-name.ls_fcat-ref_table DOCS.ls_fcat-ref_field LINES.APPEND ls_fcat TO lt_fcat.lv_end_col lv_end_col 1.ENDLOOP.IF iv_end_col IS NOT INITIAL.lv_end_col iv_end_col.ENDIF.IF iv_end_row IS NOT INITIAL.lv_end_row iv_end_row.ELSE.lv_end_row 65000.ENDIF.生成动态内表CALL METHOD cl_alv_table_createcreate_dynamic_tableEXPORTINGit_fieldcatalog lt_fcatIMPORTINGep_table ref_dataEXCEPTIONSgenerate_subpool_dir_full 1.IF sy-subrc 0 OR ref_data IS NOT BOUND.CLEAR: lv_message.lv_message |Dynamic table creation failed|.PERFORM frm_append_message TABLES t_return USING 00 001 E lv_message .RETURN.ENDIF.ASSIGN ref_data-* TO lfs_table.IF iv_skip X.跳过首行默认将首行作为抬头行输出IF t_title IS SUPPLIED.解析数据内表CALL METHOD cl_abap_structdescrdescribe_by_dataEXPORTINGp_data t_titleRECEIVINGp_descr_ref ref_tdescr_title.TRY.ref_sdescr_title ? ref_tdescr_title.CATCH cx_root INTO ref_cx.CLEAR: lv_message.lv_message ref_cx-get_text( ).PERFORM frm_append_message TABLES t_return USING 00 001 E lv_message .RETURN.ENDTRY.LOOP AT ref_sdescr_title-components INTO ls_comp.CLEAR: ls_fcat.ls_fcat-fieldname ls_comp-name.ls_fcat-ref_table DOCS.ls_fcat-ref_field LINES.APPEND ls_fcat TO lt_fcat_title.ENDLOOP.CALL METHOD cl_alv_table_createcreate_dynamic_tableEXPORTINGit_fieldcatalog lt_fcat_titleIMPORTINGep_table ref_data_titleEXCEPTIONSgenerate_subpool_dir_full 1.IF sy-subrc 0 OR ref_data_title IS NOT BOUND.CLEAR: lv_message.lv_message |Dynamic table for title creation failed|.PERFORM frm_append_message TABLES t_return USING 00 001 E lv_message .RETURN.ENDIF.ASSIGN ref_data_title-* TO lfs_table_title.ENDIF.ENDIF.CLEAR:lv_string,lv_file_name,lv_file_type .lv_string iv_filename.根据文件地址获取文件后缀名防止文件路径中多次出现文件类型只取最后一个有效文件类型lv_file_type iv_filename.WHILE lv_file_type CS ..SPLIT lv_file_type AT . INTO lv_file_name lv_file_type.ENDWHILE.TRANSLATE lv_file_type TO LOWER CASE.兼容Excel 和 TXT 文本CASE lv_file_type.WHEN xls OR xlsx.CLEAR:lt_intern[].CALL FUNCTION ZALSM_EXCEL_TO_INTERNAL_TABLE ALSM_EXCEL_TO_INTERNAL_TABLEEXPORTINGfilename iv_filenamei_begin_col 1i_begin_row 1i_end_col lv_end_coli_end_row lv_end_rowTABLESintern lt_internEXCEPTIONSinconsistent_parameters 1upload_ole 2OTHERS 3.IF sy-subrc 0.CLEAR: lv_message.MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_message.PERFORM frm_append_message TABLES t_return USING sy-msgid sy-msgno sy-msgty lv_message .RETURN.ENDIF.CREATE DATA ls_new_line LIKE LINE OF lfs_table.ASSIGN ls_new_line-* TO lfs_struc.IF lfs_table_title IS ASSIGNED.CREATE DATA ls_new_line_title LIKE LINE OF lfs_table_title.ASSIGN ls_new_line_title-* TO lfs_struc_title.ENDIF.LOOP AT lt_intern ASSIGNING lfs_intern.IF iv_skip EQ abap_true .IF lfs_intern-row 1.首行数据根据顺序依次填入结构中IF lfs_struc_title IS ASSIGNED.UNASSIGN lfs_value_title.ASSIGN COMPONENT lfs_intern-col OF STRUCTURE lfs_struc_title TO lfs_value_title.IF lfs_value_title IS ASSIGNED.TRY.lfs_value_title lfs_intern-value.CATCH cx_root INTO cxroot.CLEAR: lv_message.lv_message cxroot-get_text( ).PERFORM frm_append_message TABLES t_return USING 00 001 E lv_message .RETURN.ENDTRY.ENDIF.ELSE.CONTINUE.ENDIF.ELSEIF lfs_intern-row 2.在读取第二行第一列的时候将抬头数据 参入参数中IF lfs_intern-col 1 AND lfs_struc_title IS ASSIGNED.MOVE-CORRESPONDING lfs_struc_title TO t_title.APPEND t_title.CLEAR: lfs_struc_title.ENDIF.ENDIF.ENDIF.防止因为要抬头行没有跳过IF iv_skip EQ abap_true AND lfs_intern-row 1.CONTINUE.ENDIF.如果需要跳过多行根据行号判断IF iv_skip_to 0.IF lfs_intern-row iv_skip_to.CONTINUE.ENDIF.ENDIF.ASSIGN COMPONENT lfs_intern-col OF STRUCTURE lfs_struc TO lfs_value.IF lfs_value IS ASSIGNED.CONDENSE lfs_intern-value NO-GAPS.TRY.lfs_value lfs_intern-value.CATCH cx_root INTO cxroot.CLEAR: lv_message.lv_message cxroot-get_text( ).PERFORM frm_append_message TABLES t_return USING 00 001 E lv_message .RETURN.ENDTRY.ENDIF.AT END OF row.MOVE-CORRESPONDING lfs_struc TO t_data.APPEND t_data.CLEAR: lfs_struc.ENDAT.ENDLOOP.WHEN txt.CALL METHOD cl_gui_frontend_servicesgui_uploadEXPORTINGfilename lv_stringfiletype ASChas_field_separator cl_abap_char_utilitieshorizontal_tabCHANGINGdata_tab lfs_tableEXCEPTIONSfile_open_error 1file_read_error 2no_batch 3gui_refuse_filetransfer 4invalid_type 5no_authority 6unknown_error 7bad_data_format 8header_not_allowed 9separator_not_allowed 10header_too_long 11unknown_dp_error 12access_denied 13dp_out_of_memory 14disk_full 15dp_timeout 16not_supported_by_gui 17error_no_gui 18.IF sy-subrc 0.CLEAR: lv_message.MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_message.PERFORM frm_append_message TABLES t_return USING sy-msgid sy-msgno sy-msgty lv_message .RETURN.ENDIF.IF iv_skip EQ abap_true.IF lfs_struc_title IS ASSIGNED.READ TABLE lfs_table ASSIGNING lfs_struc INDEX 1.TRY.MOVE-CORRESPONDING lfs_struc TO t_title.CATCH cx_root INTO ref_cx.CLEAR: lv_message.lv_message ref_cx-get_text( ).PERFORM frm_append_message TABLES t_return USING 00 001 E lv_message .CLEAR: lv_message.MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_message.PERFORM frm_append_message TABLES t_return USING sy-msgid sy-msgno sy-msgty lv_message .RETURN.ENDTRY.ENDIF.DELETE lfs_table INDEX 1.ENDIF.IF iv_skip_to 1.CLEAR:lv_skip_to.IF iv_skip EQ abap_true.因为LV_INDEX X已经将第一行删除故少删除一行lv_skip_to iv_skip_to - 1.ELSE.lv_skip_to iv_skip_to.ENDIF.DELETE lfs_table FROM 1 TO lv_skip_to .ENDIF.LOOP AT lfs_table ASSIGNING lfs_struc.TRY.MOVE-CORRESPONDING lfs_struc TO t_data.CATCH cx_root INTO ref_cx.CLEAR: lv_message.lv_message ref_cx-get_text( ).PERFORM frm_append_message TABLES t_return USING 00 001 E lv_message .CLEAR: lv_message.MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_message.PERFORM frm_append_message TABLES t_return USING sy-msgid sy-msgno sy-msgty lv_message .RETURN.ENDTRY.APPEND t_data.ENDLOOP.WHEN OTHERS.CLEAR: lv_message.lv_message |The file type { lv_file_type } is not supported|.PERFORM frm_append_message TABLES t_return USING 00 001 E lv_message .RETURN.ENDCASE.
ENDFUNCTION.
补上消息处理的子例程.
*---------------------------------------------------------------------*
* Form frm_append_message
*---------------------------------------------------------------------*
* text
*---------------------------------------------------------------------*
* -- T_RETURN
* -- LV_MSG_ID
* -- LV_MSG_NUM
* -- LV_MSG_TYPE
* -- LV_MSG
*---------------------------------------------------------------------*
FORM frm_append_message TABLES pt_return STRUCTURE bapiret2USING pv_msg_idpv_msg_numpv_msg_typepv_msg.DATA: ls_msg TYPE bapiret2.CLEAR: ls_msg.ls_msg-id 00.ls_msg-type E.ls_msg-number 001.ls_msg-message pv_msg.APPEND ls_msg to pt_return.
ENDFORM.
另外由于函数 ALSM_EXCEL_TO_INTERNAL_TABLE 中 接收数据的 VALUE字段只有50个字符完全无法满足通用场景故复制一版使用ZST_ALSMEX_TABLINE 替换了原先的 ALSMEX_TABLINE除了结构替换包括子例程用到该结构的都要替换其他代码保持一致即可。 当然原先的封装逻辑也是很好的同时支持excel,csv,text等不分好坏只是场景适不适合而已。
FUNCTION ZZZZ_UPLOAD_FILE.
*----------------------------------------------------------------------
**本地接口
* IMPORTING
* REFERENCE(IV_FILENAME) TYPE RLGRAP-FILENAME
* REFERENCE(IV_SKIP) TYPE AS4FLAG DEFAULT X
* REFERENCE(IV_INDEX) TYPE I OPTIONAL
* TABLES
* T_DATA TYPE STANDARD TABLE
* T_RETURN TYPE BAPIRET2_T OPTIONAL
*----------------------------------------------------------------------*Global data declarationsTYPE-POOLS: truxs.DATA: lv_seqno TYPE n LENGTH 5,lv_str1 TYPE string,lv_str2 TYPE string,lv_file TYPE rlgrap-filename,lv_count TYPE i,ref_tdescr TYPE REF TO cl_abap_typedescr,ref_sdescr TYPE REF TO cl_abap_structdescr,ref_cx TYPE REF TO cx_root,ref_data TYPE REF TO data,ls_comp TYPE abap_compdescr,ls_fcat TYPE lvc_s_fcat,lt_fcat TYPE lvc_t_fcat,lt_raw TYPE truxs_t_text_data,lt_file TYPE STANDARD TABLE OF string.FIELD-SYMBOLS: lfs_table TYPE STANDARD TABLE,lfs_struc TYPE any,lfs_field TYPE any.CALL METHOD cl_abap_structdescrdescribe_by_dataEXPORTINGp_data t_dataRECEIVINGp_descr_ref ref_tdescr.TRY.ref_sdescr ? ref_tdescr.CATCH cx_root INTO ref_cx.lv_str1 ref_cx-get_text( ).###¡§#######¨®#¡ì¡ã### CLEAR: t_return.t_return-id 00.t_return-type E.t_return-number 001.t_return-message_v1 TEXT-001.REPLACE FIRST OCCURRENCE OF IN t_return-message_v1 WITH T_DATA.t_return-message_v2 lv_str1.MESSAGE ID t_return-idTYPE t_return-typeNUMBER t_return-numberINTO t_return-messageWITH t_return-message_v1 t_return-message_v2.APPEND t_return.RETURN.ENDTRY.lv_seqno 1.LOOP AT ref_sdescr-components INTO ls_comp.CLEAR: ls_fcat.ls_fcat-fieldname ls_comp-name.ls_fcat-ref_table DOCS.ls_fcat-ref_field LINES.APPEND ls_fcat TO lt_fcat.ADD 1 TO lv_seqno.ENDLOOP.CALL METHOD cl_alv_table_createcreate_dynamic_tableEXPORTINGit_fieldcatalog lt_fcatIMPORTINGep_table ref_dataEXCEPTIONSgenerate_subpool_dir_full 1.IF sy-subrc 0 OR ref_data IS NOT BOUND.处理消息CLEAR: t_return.t_return-id 00.t_return-type E.t_return-number 001.t_return-message_v1 TEXT-002.REPLACE FIRST OCCURRENCE OF IN t_return-message_v1WITH ref_sdescr-absolute_name.MESSAGE ID t_return-idTYPE t_return-typeNUMBER t_return-numberINTO t_return-messageWITH t_return-message_v1.APPEND t_return.RETURN.ENDIF.ASSIGN ref_data-* TO lfs_table.获取最终的文件类型lv_str2 iv_filename.WHILE lv_str2 CS ..SPLIT lv_str2 AT . INTO lv_str1 lv_str2.ENDWHILE.TRANSLATE lv_str2 TO LOWER CASE.根据文件类型分别读取CASE lv_str2.WHEN xls OR xlsx.CALL FUNCTION TEXT_CONVERT_XLS_TO_SAPEXPORTINGi_tab_raw_data lt_rawi_filename iv_filenameTABLESi_tab_converted_data lfs_tableEXCEPTIONSconversion_failed 1.IF sy-subrc 0.MESSAGE ID sy-msgidTYPE sy-msgtyNUMBER sy-msgnoWITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4RAISING file_convert_failed.ENDIF.WHEN csv.lv_str1 iv_filename.CALL METHOD cl_gui_frontend_servicesgui_uploadEXPORTINGfilename lv_str1filetype DATCHANGINGdata_tab lt_fileEXCEPTIONSfile_open_error 1file_read_error 2no_batch 3gui_refuse_filetransfer 4invalid_type 5no_authority 6unknown_error 7bad_data_format 8header_not_allowed 9separator_not_allowed 10header_too_long 11unknown_dp_error 12access_denied 13dp_out_of_memory 14disk_full 15dp_timeout 16not_supported_by_gui 17error_no_gui 18.IF sy-subrc 0.MESSAGE ID sy-msgidTYPE sy-msgtyNUMBER sy-msgnoWITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4RAISING file_convert_failed.ENDIF.LOOP AT lt_file INTO lv_str1.APPEND INITIAL LINE TO lfs_table ASSIGNING lfs_struc.lv_count 1.WHILE lv_str1 CS ,.ADD 1 TO lv_count.SPLIT lv_str1 AT , INTO lv_str2 lv_str1.ASSIGN COMPONENT sy-index OF STRUCTURE lfs_struc TO lfs_field.IF sy-subrc EQ 0.MOVE lv_str2 TO lfs_field.ELSE.EXIT.ENDIF.ENDWHILE.ADD 1 TO lv_count.IF lv_str1 IS NOT INITIAL.ASSIGN COMPONENT lv_count OF STRUCTURE lfs_struc TO lfs_field.IF sy-subrc EQ 0.MOVE lv_str1 TO lfs_field.ELSE.ENDIF.ENDIF.ENDLOOP.WHEN txt.lv_str1 iv_filename.CALL METHOD cl_gui_frontend_servicesgui_uploadEXPORTINGfilename lv_str1filetype ASChas_field_separator cl_abap_char_utilitieshorizontal_tabCHANGINGdata_tab lfs_tableEXCEPTIONSfile_open_error 1file_read_error 2no_batch 3gui_refuse_filetransfer 4invalid_type 5no_authority 6unknown_error 7bad_data_format 8header_not_allowed 9separator_not_allowed 10header_too_long 11unknown_dp_error 12access_denied 13dp_out_of_memory 14disk_full 15dp_timeout 16not_supported_by_gui 17error_no_gui 18.IF sy-subrc 0.MESSAGE ID sy-msgidTYPE sy-msgtyNUMBER sy-msgnoWITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4RAISING file_convert_failed.ENDIF.WHEN OTHERS.CLEAR: t_return.t_return-id 00.t_return-type E.t_return-number 001.t_return-message_v1 TEXT-003.t_return-message_v2 lv_str2.MESSAGE ID t_return-idTYPE t_return-typeNUMBER t_return-numberINTO t_return-messageWITH t_return-message_v1.APPEND t_return.ENDCASE.IF iv_skip EQ abap_true.IF iv_index .DELETE lfs_table INDEX 1.ELSE.DELETE lfs_table FROM 1 TO iv_index .ENDIF.ENDIF.LOOP AT lfs_table ASSIGNING lfs_struc.TRY.MOVE-CORRESPONDING lfs_struc TO t_data.CATCH cx_root INTO ref_cx.¡Á######¡ì¡ã###CLEAR: t_return.t_return-id 00.t_return-type E.t_return-number 001.t_return-message_v1 TEXT-004.t_return-message_v2 sy-tabix.CONDENSE t_return-message_v2 NO-GAPS.REPLACE FIRST OCCURRENCE OF IN t_return-message_v1WITH t_return-message_v2.t_return-message_v2 ref_cx-get_text( ).MESSAGE ID t_return-idTYPE t_return-typeNUMBER t_return-numberINTO t_return-messageWITH t_return-message_v1t_return-message_v2.APPEND t_return.RETURN.ENDTRY.APPEND t_data.ENDLOOP.READ TABLE t_return WITH KEY type E TRANSPORTING NO FIELDS.IF sy-subrc EQ 0.CLEAR: t_data.ENDIF.ENDFUNCTION.
