2021.09.08 【ABAP随笔】-批量处理 - Excel导入模板1.1-支持多Sheet页
个人Excel上载模板(兼容多个Sheet)
之前的excel模板使用了很久，但是最近老是遇到多个sheet页面上传的需求，所以自己整理了一个上传多个sheet页excel上载模板，各位客官可以选择性食用(Ctrl+C&V)
1. 首先，我们需要在Report程序中添加一个类型TY_SHEETNAME_INFO 参考类型为ZCL_TAB_DOC=>TT_SHEETNAME_INFO,存放了如何输入Excel的信息,
LT_TABC 的类型是参考的ZCL_TAB_DOC=>TT_TABC自定义的类型请看代码测试为通过Excel 上传数据，且支持多个Sheet页签
我们准备2个sheet 分别上传物料的不同信息
Sheet1
Sheet2
2.调用默认类打开需要上传的Excel(其实就是封装了一下cl_gui_frontend_services=>file_open_dialog 给下默认值)
3.调用Import_excel_by_ole类,获取excel数据，按照Sheet名称对应放在LT_TABC中
4.调用excel_convert_table 来将excel中每个sheet页数据填入到对应的内表中，
其中：FRM_SPECIAL_PROCESS 为需要执行的特殊处理，比如日期转化(2021/9/8->20210908),单位转化(PC->ST)等操作
下面是调用的模板程序(Copy 可用）
调用程序示例
*&---------------------------------------------------------------------* *& Report Z_TAB_TEST04 *&---------------------------------------------------------------------* REPORT z_tab_test04. *&=====================================================================* *& CREATE date: 2021.09.08 *& created by: tab-zhu (微信公众号：tab_jxzhu) *&=====================================================================* DATA:BEGIN OF gs_excel, matnr TYPE mara-matnr, maktx TYPE makt-maktx, meins TYPE mara-meins, creationdate TYPE datum, creationtime TYPE uzeit, END OF gs_excel. DATA gt_excel LIKE TABLE OF gs_excel. DATA:BEGIN OF gs_excel_sheet1, matnr TYPE mara-matnr, meins TYPE mara-meins, creationdate TYPE datum, creationtime TYPE uzeit, END OF gs_excel_sheet1. DATA gt_excel_sheet1 LIKE TABLE OF gs_excel_sheet1. DATA:BEGIN OF gs_excel_sheet2, matnr TYPE mara-matnr, maktx TYPE makt-maktx, END OF gs_excel_sheet2. DATA gt_excel_sheet2 LIKE TABLE OF gs_excel_sheet2. DATA:lr_excel TYPE REF TO zcl_tab_doc. DATA lt_files TYPE filetable. DATA l_rc TYPE i. DATA l_filename TYPE string. DATA lt_sheetname TYPE zcl_tab_doc=>tt_sheetname_info."存放了如何输入excel的信息 DATA: lr_functions TYPE REF TO cl_salv_functions_list. DATA lt_tabc TYPE zcl_tab_doc=>tt_tabc."存放获取sheet页的数据 PARAMETERS:p_file TYPE rlgrap-filename OBLIGATORY MEMORY ID mi. AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file. " CALL METHOD zcl_tab_doc=>file_open_dialog_default CHANGING ct_file_table = lt_files c_rc = l_rc EXCEPTIONS file_open_dialog_failed = 1 cntl_error = 2 error_no_gui = 3 not_support_by_gui = 4 OTHERS = 5. READ TABLE lt_files INTO DATA(ls_files) INDEX 1. IF sy-subrc EQ 0. p_file = ls_files-filename. ENDIF. START-OF-SELECTION. lt_sheetname = VALUE #( ( sheetname = 'Sheet1' startrow = 2 startcol = 1 every_copy_lines = 30 endcol = 4 ) ( sheetname = 'Sheet2' startrow = 2 startcol = 1 every_copy_lines = 30 endcol = 2 ) ). CREATE OBJECT lr_excel. l_filename = p_file. CALL METHOD lr_excel->import_excel_by_ole EXPORTING i_filename = l_filename CHANGING ct_sheetname = lt_sheetname ct_tabc = lt_tabc EXCEPTIONS file_open_error = 1 OTHERS = 2. IF sy-subrc <> 0. MESSAGE e398(00) WITH |File opened failed!| ''. ENDIF. CHECK lt_tabc[] IS NOT INITIAL. READ TABLE lt_tabc INTO DATA(ls_tabc) WITH KEY sheetname = 'Sheet1'. IF sy-subrc EQ 0. CALL METHOD lr_excel->excel_convert_table EXPORTING * i_separator = CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB is_table = ls_tabc i_repid = 'Z_TAB_TEST04' i_special_process = 'FRM_SPECIAL_PROCESS' CHANGING ct_table = gt_excel_sheet1 EXCEPTIONS context_convert_failed = 1 OTHERS = 2. IF sy-subrc <> 0. MESSAGE e398(00) WITH 'Context converted failed!' ''. ENDIF. ENDIF. CHECK gt_excel_sheet1[] IS NOT INITIAL. READ TABLE lt_tabc INTO ls_tabc WITH KEY sheetname = 'Sheet2'. IF sy-subrc EQ 0. CALL METHOD lr_excel->excel_convert_table EXPORTING * i_separator = CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB is_table = ls_tabc i_repid = 'Z_TAB_TEST04' i_special_process = 'FRM_SPECIAL_PROCESS' CHANGING ct_table = gt_excel_sheet2 EXCEPTIONS context_convert_failed = 1 OTHERS = 2. IF sy-subrc <> 0. MESSAGE e398(00) WITH 'Context converted failed!' ''. ENDIF. ENDIF. SORT gt_excel_sheet2 BY matnr. LOOP AT gt_excel_sheet1 INTO gs_excel_sheet1. READ TABLE gt_excel_sheet2 INTO gs_excel_sheet2 WITH KEY matnr = gs_excel_sheet1-matnr BINARY SEARCH. IF sy-subrc EQ 0. gs_excel-maktx = gs_excel_sheet2-maktx. ENDIF. MOVE-CORRESPONDING gs_excel_sheet1 TO gs_excel. APPEND gs_excel TO gt_excel. CLEAR gs_excel. ENDLOOP. DATA go_alv TYPE REF TO cl_salv_table. TRY. cl_salv_table=>factory( IMPORTING r_salv_table = go_alv CHANGING t_table = gt_excel[] ). CATCH cx_salv_msg. ENDTRY. lr_functions = go_alv->get_functions( ). lr_functions->set_all( 'X' ). CHECK go_alv IS BOUND. go_alv->display( ). FORM frm_special_process USING is_componet TYPE abap_compdescr i_row TYPE i i_col TYPE i CHANGING c_cell TYPE string. DATA lv_matnr TYPE mara-matnr. CHECK c_cell IS NOT INITIAL. CASE i_col. WHEN '1'. "内外部转化 lv_matnr = c_cell. CALL FUNCTION 'CONVERSION_EXIT_MATN1_INPUT' EXPORTING input = lv_matnr IMPORTING output = lv_matnr EXCEPTIONS length_error = 1 OTHERS = 2. IF sy-subrc <> 0. CLEAR lv_matnr. ENDIF. c_cell = lv_matnr. WHEN '2'. CHECK is_componet-name = 'MEINS'."转化单位 CALL FUNCTION 'CONVERSION_EXIT_CUNIT_INPUT' EXPORTING input = c_cell language = sy-langu IMPORTING output = c_cell EXCEPTIONS unit_not_found = 1 OTHERS = 2. IF sy-subrc <> 0. MESSAGE e398(00) WITH |Unit convert failed!| ''. ENDIF. WHEN '3'. "日期转换 DATA l_num2(2) TYPE n. SPLIT c_cell AT '/' INTO TABLE DATA(lt_cell). CLEAR c_cell. LOOP AT lt_cell INTO DATA(ls_cell). CASE sy-tabix. WHEN 1. c_cell = ls_cell. WHEN 2 OR 3. l_num2 = ls_cell. c_cell = |{ c_cell }{ l_num2 }|. WHEN OTHERS. ENDCASE. ENDLOOP. REPLACE ALL OCCURRENCES OF '-' IN c_cell WITH ''. REPLACE ALL OCCURRENCES OF '/' IN c_cell WITH ''. CONDENSE c_cell NO-GAPS. WHEN '4'. "时间转换 REPLACE ALL OCCURRENCES OF ':' IN c_cell WITH ''. CONDENSE c_cell NO-GAPS. WHEN OTHERS. ENDCASE. ENDFORM.
Excel文档处理类
CLASS zcl_tab_doc DEFINITION PUBLIC CREATE PUBLIC *&=====================================================================* *& CREATE date: 2021.09.08 *& created by: tab-zhu (微信公众号：tab_jxzhu) *&---------------------------------------------------------------------* GLOBAL FRIENDS cl_gui_frontend_services . PUBLIC SECTION. TYPES: BEGIN OF ty_sheetname_info, sheetname TYPE string, startrow TYPE i, startcol TYPE i, every_copy_lines TYPE i, endcol TYPE i, END OF ty_sheetname_info . TYPES: tt_sheetname_info TYPE STANDARD TABLE OF ty_sheetname_info . TYPES: ty_char30000 TYPE c LENGTH 30000 . TYPES: tt_char30000 TYPE TABLE OF ty_char30000 . DATA: dt_excel_context TYPE STANDARD TABLE OF ty_char30000 . TYPES: BEGIN OF ty_tabc, sheetname TYPE string, "sheet名称 context LIKE dt_excel_context, "sheet页的数据 END OF ty_tabc . TYPES: tt_tabc TYPE STANDARD TABLE OF ty_tabc . "保存文件 CLASS-METHODS file_save_dialog_default IMPORTING VALUE(i_window_title) TYPE string DEFAULT 'Select Download Path' VALUE(i_default_extension) TYPE string DEFAULT 'xlsx' VALUE(i_default_filename) TYPE string DEFAULT 'Download' !i_with_encoding TYPE abap_bool OPTIONAL VALUE(i_file_filter) TYPE string DEFAULT 'Excel(*.xlsx)|*.xlsx|Excel(*.xlsm)|*.xlsm|Excel 97-2003(*.xls)|*.xls' VALUE(i_initial_directory) TYPE string OPTIONAL !i_prompt_on_overwrite TYPE abap_bool DEFAULT 'X' CHANGING !c_filename TYPE string !c_path TYPE string !c_fullpath TYPE string !c_user_action TYPE i OPTIONAL !c_file_encoding TYPE abap_encoding OPTIONAL EXCEPTIONS cntl_error error_no_gui not_supported_by_gui invalid_default_file_name . "读取文件 CLASS-METHODS file_open_dialog_default IMPORTING VALUE(i_window_title) TYPE string DEFAULT 'Select Upload File' VALUE(i_default_extension) TYPE string DEFAULT '.xlsx|.xls' VALUE(i_default_filename) TYPE string OPTIONAL VALUE(i_file_filter) TYPE string DEFAULT 'Excel(*.xlsx)|*.xlsx|Excel(*.xlsm)|*.xlsm|Excel 97-2003(*.xls)|*.xls' VALUE(i_with_encoding) TYPE abap_bool OPTIONAL VALUE(i_initial_directory) TYPE string OPTIONAL VALUE(i_multiselection) TYPE abap_bool DEFAULT '' CHANGING !ct_file_table TYPE filetable !c_rc TYPE i !c_user_action TYPE i OPTIONAL !c_file_encoding TYPE abap_encoding OPTIONAL EXCEPTIONS file_open_dialog_failed cntl_error error_no_gui not_support_by_gui . "导入excel到内表 CLASS-METHODS import_excel_by_ole IMPORTING VALUE(i_filename) TYPE string CHANGING VALUE(ct_sheetname) TYPE tt_sheetname_info VALUE(ct_tabc) TYPE tt_tabc EXCEPTIONS file_open_error . "EXCEL 内容转化为 内表-(由调用程序决定) METHODS excel_convert_table IMPORTING VALUE(i_separator) TYPE abap_char1 DEFAULT cl_abap_char_utilities=>horizontal_tab VALUE(is_table) TYPE ty_tabc VALUE(i_repid) TYPE sy-repid DEFAULT sy-repid VALUE(i_special_process) TYPE string OPTIONAL"special process form CHANGING !ct_table TYPE STANDARD TABLE EXCEPTIONS context_convert_failed . PROTECTED SECTION. "单元格的特殊处理 METHODS excel_convert_special_process IMPORTING !is_componet TYPE abap_compdescr OPTIONAL !i_row TYPE i !i_col TYPE i CHANGING !c_cell TYPE string EXCEPTIONS convert_error . PRIVATE SECTION. DATA m_special_process_form TYPE string. DATA m_repid TYPE sy-repid VALUE sy-repid ##NO_TEXT. ENDCLASS. CLASS ZCL_TAB_DOC IMPLEMENTATION. * <SIGNATURE>---------------------------------------------------------------------------------------+ * | Instance Protected Method ZCL_TAB_DOC->EXCEL_CONVERT_SPECIAL_PROCESS * +-------------------------------------------------------------------------------------------------+ * | [--->] IS_COMPONET TYPE ABAP_COMPDESCR(optional) * | [--->] I_ROW TYPE I * | [--->] I_COL TYPE I * | [<-->] C_CELL TYPE STRING * | [EXC!] CONVERT_ERROR * +--------------------------------------------------------------------------------------</SIGNATURE> METHOD excel_convert_special_process. "table ZTDOC_CONFIG_TAB CHECK m_special_process_form IS NOT INITIAL. PERFORM (m_special_process_form) IN PROGRAM (m_repid) IF FOUND USING is_componet i_row i_col CHANGING c_cell. ENDMETHOD. * <SIGNATURE>---------------------------------------------------------------------------------------+ * | Instance Public Method ZCL_TAB_DOC->EXCEL_CONVERT_TABLE * +-------------------------------------------------------------------------------------------------+ * | [--->] I_SEPARATOR TYPE ABAP_CHAR1 (default =CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB) * | [--->] IS_TABLE TYPE TY_TABC * | [--->] I_REPID TYPE SY-REPID (default =SY-REPID) * | [--->] I_SPECIAL_PROCESS TYPE STRING(optional) * | [<-->] CT_TABLE TYPE STANDARD TABLE * | [EXC!] CONTEXT_CONVERT_FAILED * +--------------------------------------------------------------------------------------</SIGNATURE> METHOD excel_convert_table. DATA lv_row TYPE i. DATA lv_col TYPE i. DATA lv_tabix TYPE i. DATA: lt_cell TYPE TABLE OF string, ls_cell TYPE string. DATA: lo_cx_root TYPE REF TO cx_root. DATA lr_descr TYPE REF TO cl_abap_structdescr. DATA lr_line TYPE REF TO data. FIELD-SYMBOLS: <value> TYPE any, <line> TYPE any. CREATE DATA lr_line LIKE LINE OF ct_table. ASSIGN lr_line->* TO <line>. lr_descr ?= cl_abap_typedescr=>describe_by_data( <line> ). "m_repid m_special_process m_repid = i_repid. m_special_process_form = i_special_process. LOOP AT is_table-context INTO DATA(ls_context). lv_row = sy-tabix. SPLIT ls_context AT cl_abap_char_utilities=>horizontal_tab INTO TABLE lt_cell. LOOP AT lt_cell INTO ls_cell. lv_tabix = sy-tabix."DISPOSAL CURRENT ROW lv_col = lv_tabix. READ TABLE lr_descr->components INTO DATA(ls_component) INDEX lv_tabix. IF sy-subrc EQ 0. CALL METHOD me->excel_convert_special_process EXPORTING is_componet = ls_component "字段信息 i_row = lv_row "行 i_col = lv_col "列 CHANGING c_cell = ls_cell. ASSIGN COMPONENT lv_col OF STRUCTURE <line> TO <value>. IF sy-subrc EQ 0. TRY. <value> = ls_cell. CATCH cx_root INTO lo_cx_root. "In sheet &1,the data format of Row &2 Field &3 is not right DATA(lv_text) = |In sheet{ is_table-sheetname },the data format of Row { lv_row } Field { ls_component-name } is not right|. MESSAGE e368(00) WITH lv_text '' RAISING context_convert_failed. ENDTRY. ENDIF. ENDIF. ENDLOOP. APPEND <line> TO ct_table. CLEAR <line>. ENDLOOP. ENDMETHOD. * <SIGNATURE>---------------------------------------------------------------------------------------+ * | Static Public Method ZCL_TAB_DOC=>FILE_OPEN_DIALOG_DEFAULT * +-------------------------------------------------------------------------------------------------+ * | [--->] I_WINDOW_TITLE TYPE STRING (default ='Select Upload File') * | [--->] I_DEFAULT_EXTENSION TYPE STRING (default ='.xlsx|.xls') * | [--->] I_DEFAULT_FILENAME TYPE STRING(optional) * | [--->] I_FILE_FILTER TYPE STRING (default ='Excel(*.xlsx)|*.xlsx|Excel(*.xlsm)|*.xlsm|Excel 97-2003(*.xls)|*.xls') * | [--->] I_WITH_ENCODING TYPE ABAP_BOOL(optional) * | [--->] I_INITIAL_DIRECTORY TYPE STRING(optional) * | [--->] I_MULTISELECTION TYPE ABAP_BOOL (default ='') * | [<-->] CT_FILE_TABLE TYPE FILETABLE * | [<-->] C_RC TYPE I * | [<-->] C_USER_ACTION TYPE I(optional) * | [<-->] C_FILE_ENCODING TYPE ABAP_ENCODING(optional) * | [EXC!] FILE_OPEN_DIALOG_FAILED * | [EXC!] CNTL_ERROR * | [EXC!] ERROR_NO_GUI * | [EXC!] NOT_SUPPORT_BY_GUI * +--------------------------------------------------------------------------------------</SIGNATURE> METHOD file_open_dialog_default. CALL METHOD cl_gui_frontend_services=>file_open_dialog EXPORTING window_title = i_window_title default_extension = i_default_extension default_filename = i_default_filename file_filter = i_file_filter with_encoding = i_with_encoding initial_directory = i_initial_directory multiselection = i_multiselection CHANGING file_table = ct_file_table rc = c_rc user_action = c_user_action file_encoding = c_file_encoding EXCEPTIONS file_open_dialog_failed = 1 cntl_error = 2 error_no_gui = 3 not_supported_by_gui = 4 OTHERS = 5. IF sy-subrc <> 0. CASE sy-subrc. WHEN 1. RAISE file_open_dialog_failed. WHEN 2. RAISE cntl_error. WHEN 3. RAISE error_no_gui. WHEN 4. RAISE not_support_by_gui. WHEN OTHERS. ENDCASE. ENDIF. ENDMETHOD. * <SIGNATURE>---------------------------------------------------------------------------------------+ * | Static Public Method ZCL_TAB_DOC=>FILE_SAVE_DIALOG_DEFAULT * +-------------------------------------------------------------------------------------------------+ * | [--->] I_WINDOW_TITLE TYPE STRING (default ='Select Download Path') * | [--->] I_DEFAULT_EXTENSION TYPE STRING (default ='xlsx') * | [--->] I_DEFAULT_FILENAME TYPE STRING (default ='Download') * | [--->] I_WITH_ENCODING TYPE ABAP_BOOL(optional) * | [--->] I_FILE_FILTER TYPE STRING (default ='Excel(*.xlsx)|*.xlsx|Excel(*.xlsm)|*.xlsm|Excel 97-2003(*.xls)|*.xls') * | [--->] I_INITIAL_DIRECTORY TYPE STRING(optional) * | [--->] I_PROMPT_ON_OVERWRITE TYPE ABAP_BOOL (default ='X') * | [<-->] C_FILENAME TYPE STRING * | [<-->] C_PATH TYPE STRING * | [<-->] C_FULLPATH TYPE STRING * | [<-->] C_USER_ACTION TYPE I(optional) * | [<-->] C_FILE_ENCODING TYPE ABAP_ENCODING(optional) * | [EXC!] CNTL_ERROR * | [EXC!] ERROR_NO_GUI * | [EXC!] NOT_SUPPORTED_BY_GUI * | [EXC!] INVALID_DEFAULT_FILE_NAME * +--------------------------------------------------------------------------------------</SIGNATURE> METHOD file_save_dialog_default. CALL METHOD cl_gui_frontend_services=>file_save_dialog EXPORTING window_title = i_window_title default_extension = i_default_extension default_file_name = i_default_filename with_encoding = i_with_encoding file_filter = i_file_filter initial_directory = i_initial_directory prompt_on_overwrite = i_prompt_on_overwrite CHANGING filename = c_filename path = c_path fullpath = c_fullpath user_action = c_user_action file_encoding = c_file_encoding EXCEPTIONS cntl_error = 1 error_no_gui = 2 not_supported_by_gui = 3 invalid_default_file_name = 4 OTHERS = 5. IF sy-subrc <> 0. CASE sy-subrc. WHEN 1. RAISE cntl_error. WHEN 2. RAISE error_no_gui. WHEN 3. RAISE not_supported_by_gui. WHEN 4. RAISE invalid_default_file_name. WHEN OTHERS. ENDCASE. ENDIF. ENDMETHOD. * <SIGNATURE>---------------------------------------------------------------------------------------+ * | Static Public Method ZCL_TAB_DOC=>IMPORT_EXCEL_BY_OLE * +-------------------------------------------------------------------------------------------------+ * | [--->] I_FILENAME TYPE STRING * | [<-->] CT_SHEETNAME TYPE TT_SHEETNAME_INFO * | [<-->] CT_TABC TYPE TT_TABC * | [EXC!] FILE_OPEN_ERROR * +--------------------------------------------------------------------------------------</SIGNATURE> METHOD import_excel_by_ole. DATA lw_tabc TYPE ty_tabc. DATA lt_tabc LIKE TABLE OF lw_tabc. DATA: lv_column_num TYPE i, lv_column_skp TYPE i, lw_tab_ref TYPE REF TO data. TYPE-POOLS: ole2. DATA: ole_excel TYPE ole2_object, ole_workbooks TYPE ole2_object, ole_workbook TYPE ole2_object, ole_worksheets TYPE ole2_object, ole_worksheet TYPE ole2_object, ole_cell_begin TYPE ole2_object, ole_cell_end TYPE ole2_object, ole_range TYPE ole2_object. DATA: lv_subrc TYPE sy-subrc, lv_begin_col TYPE i, lv_end_col TYPE i, lv_begin_row TYPE i, lv_end_row TYPE i, lv_add_rows TYPE i VALUE 3000. "默认，每次COPY3000行 DATA: lt_excel_tab TYPE STANDARD TABLE OF ty_char30000, lw_excel_tab TYPE ty_char30000, lw_excel_tab_tmp TYPE ty_char30000. *->生成Excel object CREATE OBJECT ole_excel 'Excel.Application'. IF sy-subrc <> 0. MESSAGE e368(00) WITH 'file_open_failed' RAISING file_open_error. ENDIF. SET PROPERTY OF ole_excel 'Visible' = 0. GET PROPERTY OF ole_excel 'Workbooks' = ole_workbooks. CALL METHOD OF ole_workbooks 'Open' = ole_workbook EXPORTING #1 = i_filename. "文件名 *->get Sheet DATA lv_process_text TYPE string. DATA(l_count) = lines( ct_sheetname )."Sheets 名称 LOOP AT ct_sheetname INTO DATA(ls_sheetname). DATA(l_tabix) = sy-tabix. * DATA(l_percentage) = l_tabix / l_count * 100. * ##NO_TEXT * lv_process_text = 'Uploading Sheet:' && ls_sheetname-sheetname. * * CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' * EXPORTING * percentage = l_percentage * text = lv_process_text. GET PROPERTY OF ole_workbook 'Worksheets' = ole_worksheets EXPORTING #1 = ls_sheetname-sheetname. lw_tabc-sheetname = ls_sheetname-sheetname. CALL METHOD OF ole_worksheets 'Activate'. lv_begin_col = ls_sheetname-startcol."开始列 lv_end_col = ls_sheetname-endcol."开始行 lv_begin_row = 0. lv_end_row = 0. lv_add_rows = ls_sheetname-every_copy_lines. CLEAR lv_subrc. WHILE lv_subrc IS INITIAL. IF lv_begin_row IS INITIAL. lv_begin_row = ls_sheetname-startrow. lv_end_row = lv_add_rows. ELSE. lv_begin_row = lv_begin_row + lv_add_rows. lv_end_row = lv_end_row + lv_add_rows. ENDIF. CALL METHOD OF ole_worksheets 'Cells' = ole_cell_begin EXPORTING #1 = lv_begin_row #2 = lv_begin_col. CALL METHOD OF ole_worksheets 'Cells' = ole_cell_end EXPORTING #1 = lv_end_row #2 = lv_end_col. CALL METHOD OF ole_worksheets 'RANGE' = ole_range EXPORTING #1 = ole_cell_begin #2 = ole_cell_end. CALL METHOD OF ole_range 'SELECT'. IF sy-subrc <> 0. EXIT. ENDIF. CALL METHOD OF ole_range 'COPY'. * read clipboard into ABAP CALL METHOD cl_gui_frontend_services=>clipboard_import IMPORTING data = lt_excel_tab EXCEPTIONS cntl_error = 1 error_no_gui = 2 not_supported_by_gui = 3 OTHERS = 4. IF sy-subrc <> 0. CALL METHOD OF ole_excel 'QUIT'. ##NO_TEXT MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ENDIF. lv_subrc = 4. LOOP AT lt_excel_tab INTO lw_excel_tab. lw_excel_tab_tmp = lw_excel_tab. REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>horizontal_tab IN lw_excel_tab_tmp WITH space. IF NOT ( lw_excel_tab_tmp = space OR lw_excel_tab_tmp IS INITIAL ). APPEND lw_excel_tab TO lw_tabc-context[]. CLEAR lv_subrc. ENDIF. ENDLOOP. CLEAR lt_excel_tab. ENDWHILE. DATA: lv_rc TYPE i. * clear clipboard CALL METHOD cl_gui_frontend_services=>clipboard_export IMPORTING data = lt_excel_tab CHANGING rc = lv_rc EXCEPTIONS cntl_error = 1 error_no_gui = 2 not_supported_by_gui = 3 OTHERS = 4. IF sy-subrc NE 0. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ENDIF. APPEND lw_tabc TO lt_tabc. CLEAR:lw_tabc. CALL METHOD OF ole_worksheets 'COLSE'. ENDLOOP. ct_tabc = lt_tabc. CALL METHOD OF ole_excel 'QUIT'. FREE OBJECT: ole_excel , ole_workbooks , ole_workbook , ole_worksheets , ole_worksheet , ole_cell_begin , ole_cell_end , ole_range . ENDMETHOD. ENDCLASS.
