SAP將指定EXCEL工作SHEET的數據上傳到內表

?本文描述了一個SAP ABAP類方法upload_excel_2internaltab，用于將Excel文件數據上傳到內部表。主要功能包括：

1. 驗證Excel行列范圍有效性，若起始值大于結束值則拋出異常
2. 檢查文件是否存在，支持直接指定路徑或通過文件對話框選擇
3. 使用OLE自動化技術操作Excel，包括打開工作簿、選擇工作表、復制指定單元格范圍數據
4. 通過剪貼板將Excel數據導入ABAP內部表，調用標準函數進行格式轉換
5. 包含完善的異常處理機制，針對文件不存在、操作失敗等情況拋出定制異常類ZCX_AB_K2_SERVICE的相應錯誤
6. 最后釋放所有OLE對象以關閉Excel進程

該方法實現了Excel與SAP系統間的數據交互，適用于需要批量導入數據的業務場景。

class?ZCX_AB_K2_SERVICE?definition
??public
??inheriting?from?CX_STATIC_CHECK
??create?public?.

public?section.

??interfaces?IF_T100_DYN_MSG?.
??interfaces?IF_T100_MESSAGE?.

??constants:
????begin?of?PROCESSID_NOT_CONFIGED,
??????msgid?type?symsgid?value?'ZMSG_AB_I02',
??????msgno?type?symsgno?value?'001',
??????attr1?type?scx_attrname?value?'MV_APTYP',
??????attr2?type?scx_attrname?value?'MV_BUKRS',
??????attr3?type?scx_attrname?value?'',
??????attr4?type?scx_attrname?value?'',
????end?of?PROCESSID_NOT_CONFIGED?.
??constants:
????begin?of?PROCESSID_NOT_UNIQUE,
??????msgid?type?symsgid?value?'ZMSG_AB_I02',
??????msgno?type?symsgno?value?'016',
??????attr1?type?scx_attrname?value?'',
??????attr2?type?scx_attrname?value?'',
??????attr3?type?scx_attrname?value?'',
??????attr4?type?scx_attrname?value?'',
????end?of?PROCESSID_NOT_UNIQUE?.
??constants:
????begin?of?WRONG_COMPANY_CODE,
??????msgid?type?symsgid?value?'ZMSG_AB_I02',
??????msgno?type?symsgno?value?'017',
??????attr1?type?scx_attrname?value?'MV_BUKRS',
??????attr2?type?scx_attrname?value?'',
??????attr3?type?scx_attrname?value?'',
??????attr4?type?scx_attrname?value?'',
????end?of?WRONG_COMPANY_CODE?.
??constants:
????begin?of?BEGIN_OVER_END,
??????msgid?type?symsgid?value?'ZMSG_AB_I02',
??????msgno?type?symsgno?value?'023',
??????attr1?type?scx_attrname?value?'',
??????attr2?type?scx_attrname?value?'',
??????attr3?type?scx_attrname?value?'',
??????attr4?type?scx_attrname?value?'',
????end?of?BEGIN_OVER_END?.
??constants:
????begin?of?UPLOAD_FILE_FAILED,
??????msgid?type?symsgid?value?'ZMSG_AB_I02',
??????msgno?type?symsgno?value?'022',
??????attr1?type?scx_attrname?value?'',
??????attr2?type?scx_attrname?value?'',
??????attr3?type?scx_attrname?value?'',
??????attr4?type?scx_attrname?value?'',
????end?of?UPLOAD_FILE_FAILED?.
??data?MV_APTYP?type?ZE_APTYP?.
??data?MV_BUKRS?type?ZE_BUKRS?.

??methods?CONSTRUCTOR
????importing
??????!TEXTID?like?IF_T100_MESSAGE=>T100KEY?optional
??????!PREVIOUS?like?PREVIOUS?optional
??????!MV_APTYP?type?ZE_APTYP?optional
??????!MV_BUKRS?type?ZE_BUKRS?optional?.

METHOD?upload_excel_2internaltab.

????DATA:?lt_excel_tab???TYPE?tyt_send_line,
??????????lo_application?TYPE?ole2_object,
??????????lo_workbook????TYPE?ole2_object,
??????????lo_worksheet???TYPE?ole2_object,
??????????lo_cell????????TYPE?ole2_object,
??????????lo_cell_1??????TYPE?ole2_object,
??????????lo_range???????TYPE?ole2_object,
??????????lv_return_code?TYPE?i,
??????????lt_file_table??TYPE?filetable.

????IF?iv_begin_colomn?>?iv_end_colomn.
??????"起止大于截止
??????RAISE?EXCEPTION?TYPE?zcx_ab_k2_service
????????EXPORTING
??????????textid?=?zcx_ab_k2_service=>begin_over_end.
????ENDIF.

????IF?iv_begin_row?>?iv_end_row.
??????RAISE?EXCEPTION?TYPE?zcx_ab_k2_service
????????EXPORTING
??????????textid?=?zcx_ab_k2_service=>begin_over_end.
????ENDIF.

????IF?iv_file_name?IS?NOT?INITIAL.
??????CALL?METHOD?cl_gui_frontend_services=>file_exist
????????EXPORTING
??????????file?????????????????=?iv_file_name
????????RECEIVING
??????????result???????????????=?DATA(lv_file_exist)
????????EXCEPTIONS
??????????cntl_error???????????=?1
??????????error_no_gui?????????=?2
??????????wrong_parameter??????=?3
??????????not_supported_by_gui?=?4
??????????OTHERS???????????????=?5.
??????IF?sy-subrc?<>?0?OR?lv_file_exist?EQ?abap_false.
????????"文件不存在
????????RAISE?EXCEPTION?TYPE?zcx_ab_k2_service
??????????EXPORTING
????????????textid?=?zcx_ab_k2_service=>upload_file_failed.
??????ELSE.
????????DATA(lv_file_name)?=?iv_file_name.
??????ENDIF.

????ELSE.
??????CALL?METHOD?cl_gui_frontend_services=>get_desktop_directory
????????CHANGING
??????????desktop_directory????=?lv_file_name
????????EXCEPTIONS
??????????cntl_error???????????=?1
??????????error_no_gui?????????=?2
??????????not_supported_by_gui?=?3
??????????OTHERS???????????????=?4.
??????IF?sy-subrc?<>?0.
????????RAISE?EXCEPTION?TYPE?zcx_ab_k2_service
??????????EXPORTING
????????????textid?=?zcx_ab_k2_service=>upload_file_failed.
??????ENDIF.

??????CALL?METHOD?cl_gui_frontend_services=>file_open_dialog
????????EXPORTING
??????????window_title????????????=?CONV?#(?TEXT-001?)
??????????default_extension???????=?CONV?#(?TEXT-002?)
*?????????default_filename????????=?'上傳模板'
??????????file_filter?????????????=?cl_gui_frontend_services=>filetype_excel
*?????????with_encoding???????????=
??????????initial_directory???????=?lv_file_name
??????????multiselection??????????=?abap_false
????????CHANGING
??????????file_table??????????????=?lt_file_table
??????????rc??????????????????????=?lv_return_code
*?????????user_action?????????????=
*?????????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.
????????RAISE?EXCEPTION?TYPE?zcx_ab_k2_service
??????????EXPORTING
????????????textid?=?zcx_ab_k2_service=>upload_file_failed.
??????ELSE.
????????lv_file_name?=?lt_file_table[?1?].
??????ENDIF.

????ENDIF.

????CLASS?cl_abap_char_utilities?DEFINITION?LOAD.
????DATA(lv_separator)?=?cl_abap_char_utilities=>horizontal_tab.

????IF?lo_application-header?=?abap_false
??????OR?lo_application-handle?=?-1.
??????CREATE?OBJECT?lo_application?'Excel.Application'.
????ENDIF.

????CALL?METHOD?OF?lo_application?'Workbooks'?=?lo_workbook.

????"必須參考RLGRAP-FILENAME
????DATA(lv_excel_name)?=?CONV?localfile(?lv_file_name?).
????CALL?METHOD?OF?lo_workbook?'Open'?EXPORTING?#1?=?lv_file_name.

????IF?iv_sheet_name?IS?INITIAL.
*??set?property?of?application?'Visible'?=?1.
??????GET?PROPERTY?OF??lo_application?'ACTIVESHEET'?=?lo_worksheet.
????ELSE.
??????CALL?METHOD?OF?lo_application?'WORKSHEETS'?=?lo_worksheet
??????EXPORTING?#1?=?iv_sheet_name.

??????CALL?METHOD?OF?lo_worksheet?'Activate'.
????ENDIF.

*?mark?whole?spread?sheet
????CALL?METHOD?OF?lo_worksheet?'Cells'?=?lo_cell
????????EXPORTING?#1?=?iv_begin_row?#2?=?iv_begin_colomn.

????CALL?METHOD?OF?lo_worksheet?'Cells'?=?lo_cell_1
????????EXPORTING?#1?=?iv_end_row?#2?=?iv_end_colomn.

????CALL?METHOD??OF?lo_worksheet?'RANGE'?=?lo_range
???????????????????EXPORTING?#1?=?lo_cell?#2?=?lo_cell_1.

????CALL?METHOD?OF?lo_range?'SELECT'.

*?copy?marked?area?(whole?spread?sheet)?into?Clippboard
????CALL?METHOD?OF?lo_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.
??????RAISE?EXCEPTION?TYPE?zcx_ab_k2_service
????????EXPORTING
??????????textid?=?zcx_ab_k2_service=>upload_file_failed.
????ENDIF.

????IF?lt_excel_tab?IS?NOT?INITIAL.
??????PERFORM?separated_to_intern_convert?IN?PROGRAM?saplalsmex
??????????????????????????????????????????TABLES?lt_excel_tab?rt_internal_table
??????????????????????????????????????????USING??lv_separator.

*?clear?clipboard
??????REFRESH?lt_excel_tab.
??????CALL?METHOD?cl_gui_frontend_services=>clipboard_export
????????IMPORTING
??????????data?????????????????=?lt_excel_tab
????????CHANGING
??????????rc???????????????????=?lv_return_code
????????EXCEPTIONS
??????????cntl_error???????????=?1
??????????error_no_gui?????????=?2
??????????not_supported_by_gui?=?3
??????????OTHERS???????????????=?4.
??????IF?sy-subrc?NE?0.
????????RAISE?EXCEPTION?TYPE?zcx_ab_k2_service
??????????EXPORTING
????????????textid?=?zcx_ab_k2_service=>upload_file_failed.
??????ENDIF.
????ENDIF.

*?quit?Excel?and?free?ABAP?Object?-?unfortunately,?this?does?not?kill
*?the?Excel?process
????CALL?METHOD?OF?lo_workbook?'CLOSE'.
????CALL?METHOD?OF?lo_application?'QUIT'.

*?>>>>>?Begin?of?change?note?575877
*?to?kill?the?Excel?process?it's?necessary?to?free?all?used?objects
????FREE?OBJECT?lo_cell.
????FREE?OBJECT?lo_cell_1.
????FREE?OBJECT?lo_range.
????FREE?OBJECT?lo_worksheet.
????FREE?OBJECT?lo_workbook.
????FREE?OBJECT?lo_application.
*?<<<<<?End?of?change?note?575877
??ENDMETHOD.