随笔】"/>
2021.09.08 【ABAP随笔】
个人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_defaultCHANGINGct_file_table = lt_filesc_rc = l_rcEXCEPTIONSfile_open_dialog_failed = 1cntl_error = 2error_no_gui = 3not_support_by_gui = 4OTHERS = 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 = 2startcol = 1every_copy_lines = 30endcol = 4)( sheetname = 'Sheet2'startrow = 2startcol = 1every_copy_lines = 30endcol = 2)).
CREATE OBJECT lr_excel.l_filename = p_file.CALL METHOD lr_excel->import_excel_by_oleEXPORTINGi_filename = l_filenameCHANGINGct_sheetname = lt_sheetnamect_tabc = lt_tabcEXCEPTIONSfile_open_error = 1OTHERS = 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_tableEXPORTING
* i_separator = CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TABis_table = ls_tabci_repid = 'Z_TAB_TEST04'i_special_process = 'FRM_SPECIAL_PROCESS'CHANGINGct_table = gt_excel_sheet1EXCEPTIONScontext_convert_failed = 1OTHERS = 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_tableEXPORTING
* i_separator = CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TABis_table = ls_tabci_repid = 'Z_TAB_TEST04'i_special_process = 'FRM_SPECIAL_PROCESS'CHANGINGct_table = gt_excel_sheet2EXCEPTIONScontext_convert_failed = 1OTHERS = 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(IMPORTINGr_salv_table = go_alvCHANGINGt_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 USINGis_componet TYPE abap_compdescri_row TYPE ii_col TYPE iCHANGINGc_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'EXPORTINGinput = lv_matnrIMPORTINGoutput = lv_matnrEXCEPTIONSlength_error = 1OTHERS = 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'EXPORTINGinput = c_celllanguage = sy-languIMPORTINGoutput = c_cellEXCEPTIONSunit_not_found = 1OTHERS = 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 DEFINITIONPUBLICCREATE 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_defaultIMPORTINGVALUE(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 OPTIONALVALUE(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 OPTIONALEXCEPTIONScntl_errorerror_no_guinot_supported_by_guiinvalid_default_file_name ."读取文件CLASS-METHODS file_open_dialog_defaultIMPORTINGVALUE(i_window_title) TYPE string DEFAULT 'Select Upload File'VALUE(i_default_extension) TYPE string DEFAULT '.xlsx|.xls'VALUE(i_default_filename) TYPE string OPTIONALVALUE(i_file_filter) TYPE string DEFAULT 'Excel(*.xlsx)|*.xlsx|Excel(*.xlsm)|*.xlsm|Excel 97-2003(*.xls)|*.xls'VALUE(i_with_encoding) TYPE abap_bool OPTIONALVALUE(i_initial_directory) TYPE string OPTIONALVALUE(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 OPTIONALEXCEPTIONSfile_open_dialog_failedcntl_errorerror_no_guinot_support_by_gui ."导入excel到内表CLASS-METHODS import_excel_by_oleIMPORTINGVALUE(i_filename) TYPE stringCHANGINGVALUE(ct_sheetname) TYPE tt_sheetname_infoVALUE(ct_tabc) TYPE tt_tabcEXCEPTIONSfile_open_error ."EXCEL 内容转化为 内表-(由调用程序决定)METHODS excel_convert_tableIMPORTINGVALUE(i_separator) TYPE abap_char1 DEFAULT cl_abap_char_utilities=>horizontal_tabVALUE(is_table) TYPE ty_tabcVALUE(i_repid) TYPE sy-repid DEFAULT sy-repidVALUE(i_special_process) TYPE string OPTIONAL"special process formCHANGING!ct_table TYPE STANDARD TABLEEXCEPTIONScontext_convert_failed .PROTECTED SECTION."单元格的特殊处理METHODS excel_convert_special_processIMPORTING!is_componet TYPE abap_compdescr OPTIONAL!i_row TYPE i!i_col TYPE iCHANGING!c_cell TYPE stringEXCEPTIONSconvert_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_TABCHECK m_special_process_form IS NOT INITIAL.PERFORM (m_special_process_form) IN PROGRAM (m_repid) IF FOUNDUSING 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.DATAlr_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_processm_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 ROWlv_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_processEXPORTINGis_componet = ls_component "字段信息i_row = lv_row "行i_col = lv_col "列CHANGINGc_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 rightDATA(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_dialogEXPORTINGwindow_title = i_window_titledefault_extension = i_default_extensiondefault_filename = i_default_filenamefile_filter = i_file_filterwith_encoding = i_with_encodinginitial_directory = i_initial_directorymultiselection = i_multiselectionCHANGINGfile_table = ct_file_tablerc = c_rcuser_action = c_user_actionfile_encoding = c_file_encodingEXCEPTIONSfile_open_dialog_failed = 1cntl_error = 2error_no_gui = 3not_supported_by_gui = 4OTHERS = 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_dialogEXPORTINGwindow_title = i_window_titledefault_extension = i_default_extensiondefault_file_name = i_default_filenamewith_encoding = i_with_encodingfile_filter = i_file_filterinitial_directory = i_initial_directoryprompt_on_overwrite = i_prompt_on_overwriteCHANGINGfilename = c_filenamepath = c_pathfullpath = c_fullpathuser_action = c_user_actionfile_encoding = c_file_encodingEXCEPTIONScntl_error = 1error_no_gui = 2not_supported_by_gui = 3invalid_default_file_name = 4OTHERS = 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 objectCREATE 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 OFole_workbooks'Open' = ole_workbookEXPORTING#1 = i_filename. "文件名
*->get SheetDATA 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_worksheetsEXPORTING#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 OFole_worksheets'Cells' = ole_cell_beginEXPORTING#1 = lv_begin_row#2 = lv_begin_col.
CALL METHOD OFole_worksheets'Cells' = ole_cell_endEXPORTING#1 = lv_end_row#2 = lv_end_col.
CALL METHOD OFole_worksheets'RANGE' = ole_rangeEXPORTING#1 = ole_cell_begin#2 = ole_cell_end.
CALL METHOD OFole_range'SELECT'.IF sy-subrc <> 0.EXIT.ENDIF.
CALL METHOD OFole_range'COPY'.
* read clipboard into ABAPCALL METHOD cl_gui_frontend_services=>clipboard_importIMPORTINGdata = lt_excel_tabEXCEPTIONScntl_error = 1error_no_gui = 2not_supported_by_gui = 3OTHERS = 4.IF sy-subrc <> 0.CALL METHOD OFole_excel'QUIT'.##NO_TEXTMESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1sy-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 clipboardCALL METHOD cl_gui_frontend_services=>clipboard_exportIMPORTINGdata = lt_excel_tabCHANGINGrc = lv_rcEXCEPTIONScntl_error = 1error_no_gui = 2not_supported_by_gui = 3OTHERS = 4.IF sy-subrc NE 0.MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1sy-msgv2 sy-msgv3 sy-msgv4.ENDIF.APPEND lw_tabc TO lt_tabc.CLEAR:lw_tabc.
CALL METHOD OFole_worksheets'COLSE'.
ENDLOOP.
ct_tabc = lt_tabc.
CALL METHOD OFole_excel'QUIT'.FREE OBJECT:ole_excel ,ole_workbooks ,ole_workbook ,ole_worksheets ,ole_worksheet ,ole_cell_begin ,ole_cell_end ,ole_range .ENDMETHOD.
ENDCLASS.
更多推荐
2021.09.08 【ABAP随笔】
发布评论