SAP将指定EXCEL工作SHEET的数据上传到内表
本文描述了一个SAP ABAP类方法
upload_excel_2internaltab，用于将Excel文件数据上传到内部表。主要功能包括：
- 验证Excel行列范围有效性，若起始值大于结束值则抛出异常
- 检查文件是否存在，支持直接指定路径或通过文件对话框选择
- 使用OLE自动化技术操作Excel，包括打开工作簿、选择工作表、复制指定单元格范围数据
- 通过剪贴板将Excel数据导入ABAP内部表，调用标准函数进行格式转换
- 包含完善的异常处理机制，针对文件不存在、操作失败等情况抛出定制异常类ZCX_AB_K2_SERVICE的相应错误
- 最后释放所有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.