一般使用标准的excel导入方法9999行，修改了标准的excel导入FM 整合出类：excel的 模板下载，excel上传，ALV内表下载功能。
在项目一开始可以SE24创建一个类来供整体开发使用，或者直接复制代码到程序里来使用。
ZCHAR30000 这个字段给了excel每一行30000字符的限定。
*&---------------------------------------------------------------------*
*& 包含 ZBP_CREATE_CHANGE_CLS
*&---------------------------------------------------------------------*
CLASS cl_bc_excel DEFINITION. PUBLIC SECTION.
TYPES:BEGIN OF ty_excel_tabline,
row TYPE numc06,
col TYPE numc06,
value TYPE text4096,
END OF ty_excel_tabline,
ty_t_excel_tabline TYPE TABLE OF ty_excel_tabline.
DATA lt_excel_tabline TYPE ty_t_excel_tabline.
TYPES: zchar30000 TYPE c LENGTH ."每一行数据为30000字符 上限 CLASS-METHODS f4_file_excel "获取 excel 路径
IMPORTING
VALUE(iv_window_title) TYPE string OPTIONAL
VALUE(iv_file_filter) TYPE string OPTIONAL
VALUE(iv_multiselection) TYPE char01 OPTIONAL
EXPORTING
!ev_rc TYPE i
!et_file_table TYPE filetable
CHANGING
VALUE(cv_file_name) TYPE localfile .
CLASS-METHODS download_web_object "下载对象 SMW0
IMPORTING
VALUE(iv_relid) TYPE indx_relid DEFAULT 'MI'
VALUE(iv_objid) TYPE w3objid
VALUE(iv_window_title) TYPE string OPTIONAL
VALUE(iv_default_extension) TYPE string OPTIONAL
VALUE(iv_file_filter) TYPE string OPTIONAL
VALUE(iv_default_file_name) TYPE string OPTIONAL
EXPORTING
!ev_rc TYPE sy-subrc
!ev_filepath TYPE string .
CLASS-METHODS download_template "下载模板
IMPORTING
VALUE(name_of_template) TYPE char100
VALUE(iv_relid) TYPE indx_relid DEFAULT 'MI'
VALUE(iv_objid) TYPE w3objid
EXPORTING
VALUE(rv_filepath) TYPE string
EXCEPTIONS
download_error . CLASS-METHODS update_excel_ole "上载excel
IMPORTING
REFERENCE(pi_filename) TYPE string
REFERENCE(pi_sheetname) TYPE string DEFAULT 'Sheet1'
REFERENCE(pi_startline) TYPE i DEFAULT ''
REFERENCE(pi_startcolumn) TYPE i DEFAULT ''
REFERENCE(pi_every_copy_endline) TYPE i DEFAULT ''
REFERENCE(pi_endcolumn) TYPE i DEFAULT ''
REFERENCE(pi_skpcl_tbl) TYPE i DEFAULT ''
CHANGING
VALUE(pt_tab) TYPE ANY TABLE
EXCEPTIONS
open_file_err. CLASS-METHODS excel_2_inner_table "将excel 导入内表
IMPORTING
VALUE(pi_filename) TYPE string
VALUE(pi_sheetname) TYPE string DEFAULT 'Sheet1'
VALUE(pi_every_copy_endline) TYPE i DEFAULT ''
VALUE(pi_endcolumn) TYPE i DEFAULT ''
CHANGING
VALUE(pt_tab) TYPE ANY TABLE
EXCEPTIONS
file_open_error
protected section. CLASS-METHODS download_to_excel "下载内表到excel
IMPORTING
VALUE(sheet_name) TYPE char20 DEFAULT 'DATA'
VALUE(excel_id) TYPE w3objid
VALUE(path) TYPE localfile
VALUE(excel_fname) TYPE char30
VALUE(show_excel) TYPE char1 OPTIONAL
EXPORTING
VALUE(e_message) TYPE char100
CHANGING
VALUE(i_data) TYPE ty_t_excel_tabline
EXCEPTIONS
excel_error. PRIVATE SECTION. ENDCLASS. CLASS cl_bc_excel IMPLEMENTATION. * <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZCL_BC_EXCEL=>DOWNLOAD_TEMPLATE
* +-------------------------------------------------------------------------------------------------+
* | [--->] NAME_OF_TEMPLATE TYPE CHAR100
* | [--->] IV_RELID TYPE INDX_RELID (default ='MI')
* | [--->] IV_OBJID TYPE W3OBJID
* | [<---] RV_FILEPATH TYPE STRING
* | [EXC!] DOWNLOAD_ERROR
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD download_template.
* DATA: lv_objid TYPE wwwdatatab-objid VALUE 'ZMM004'.
DATA: lv_default_file_name TYPE string.
DATA: lv_rc TYPE sy-subrc.
* DATA: lv_filepath TYPE string. CONCATENATE name_of_template '-' sy-datum+() sy-timlo+()
INTO lv_default_file_name. CALL METHOD cl_bc_excel=>download_web_object
EXPORTING
iv_relid = iv_relid
iv_objid = iv_objid
iv_default_file_name = lv_default_file_name
IMPORTING
ev_rc = lv_rc
ev_filepath = rv_filepath.
IF lv_rc <> .
RAISE download_error.
ENDIF.
ENDMETHOD. METHOD download_web_object.
DATA: ls_key TYPE wwwdatatab,
ls_wwwdata TYPE wwwdata,
lv_filename TYPE string,
lv_path TYPE string,
lv_fullpath TYPE string,
lv_extension TYPE c LENGTH ,
lv_user_action TYPE i.
DATA: lv_offset TYPE i,
lv_length TYPE i.
CLEAR ev_filepath.
IF iv_relid IS INITIAL.
SELECT SINGLE relid objid srtf2 checkout checknew chname tdate ttime text
INTO CORRESPONDING FIELDS OF ls_wwwdata
FROM wwwdata
WHERE objid = iv_objid.
iv_relid = ls_wwwdata-relid.
ELSE.
SELECT SINGLE relid objid srtf2 checkout checknew chname tdate ttime text
INTO CORRESPONDING FIELDS OF ls_wwwdata
FROM wwwdata
WHERE relid = iv_relid AND objid = iv_objid.
ENDIF.
IF iv_window_title IS INITIAL.
iv_window_title = '请选择文件存储路径...'().
ENDIF.
* IF iv_file_filter IS INITIAL.
* iv_file_filter = '(*.xls)|*.xls'.
* ENDIF.
* IF iv_default_extension IS INITIAL.
* iv_default_extension = '.xls'.
* ENDIF.
IF iv_default_extension IS INITIAL.
CALL FUNCTION 'WWWPARAMS_READ'
EXPORTING
relid = ls_wwwdata-relid
objid = ls_wwwdata-objid
name = 'fileextension' "c_extension of include LSHTMTOP
IMPORTING
value = lv_extension
EXCEPTIONS
entry_not_exists =
OTHERS = .
IF sy-subrc <> .
ENDIF.
iv_default_extension = lv_extension.
ENDIF.
IF iv_file_filter IS INITIAL.
iv_file_filter = '(*' && iv_default_extension && ')|*' && iv_default_extension.
ENDIF.
IF iv_default_file_name IS INITIAL.
iv_default_file_name = ls_wwwdata-text.
ELSE.
WHILE iv_default_file_name CA './'.
lv_offset = sy-fdpos.
lv_length = lv_offset.
lv_offset = lv_offset + .
iv_default_file_name = iv_default_file_name+(lv_length) && iv_default_file_name+lv_offset.
ENDWHILE.
ENDIF. ls_key-relid = iv_relid.
ls_key-objid = iv_objid.
"File path of selection
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
window_title = iv_window_title
default_extension = iv_default_extension
default_file_name = iv_default_file_name
file_filter = iv_file_filter
CHANGING
filename = lv_filename
path = lv_path
fullpath = lv_fullpath
user_action = lv_user_action
EXCEPTIONS
cntl_error =
error_no_gui =
not_supported_by_gui =
OTHERS = .
IF sy-subrc <> .
ev_rc = sy-subrc.
RETURN.
* MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
* WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
IF lv_user_action <> .
ev_rc = lv_user_action.
RETURN.
ENDIF. ev_filepath = lv_fullpath.
"Download web object
DATA: lv_destination TYPE rlgrap-filename.
lv_destination = ev_filepath.
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
key = ls_key
destination = lv_destination
IMPORTING
rc = ev_rc
CHANGING
temp = lv_destination.
ENDMETHOD. * <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZCL_BC_EXCEL=>F4_FILE_EXCEL
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_WINDOW_TITLE TYPE STRING(optional)
* | [--->] IV_FILE_FILTER TYPE STRING(optional)
* | [--->] IV_MULTISELECTION TYPE CHAR01(optional)
* | [<---] EV_RC TYPE I
* | [<---] ET_FILE_TABLE TYPE FILETABLE
* | [<-->] CV_FILE_NAME TYPE LOCALFILE
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD f4_file_excel.
DATA: ls_file TYPE file_table,
lv_len_s TYPE i,
lv_len_d TYPE i.
DATA: lv_default_filename TYPE string.
IF iv_window_title IS INITIAL.
iv_window_title = '请选择文件上传路径...'().
ENDIF.
IF iv_file_filter IS INITIAL.
iv_file_filter = 'Excel(*.xlsx)|*.xlsx|Excel(*.xlsm)|*.xlsm|Excel 97-2003(*.xls)|*.xls'.
ENDIF.
lv_default_filename = cv_file_name. CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title = iv_window_title
* default_extension =
default_filename = lv_default_filename
file_filter = iv_file_filter
* with_encoding =
* initial_directory =
multiselection = iv_multiselection
CHANGING
file_table = et_file_table
rc = ev_rc
* user_action =
* file_encoding =
EXCEPTIONS
file_open_dialog_failed =
cntl_error =
error_no_gui =
not_supported_by_gui =
OTHERS = .
IF sy-subrc <> .
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF. IF ev_rc < .
RETURN.
ENDIF.
READ TABLE et_file_table INTO ls_file INDEX .
IF sy-subrc = .
lv_len_s = strlen( ls_file-filename ).
DESCRIBE FIELD cv_file_name LENGTH lv_len_d IN CHARACTER MODE.
IF lv_len_s > lv_len_d.
MESSAGE i398() WITH '文件路径太长'().
RETURN.
ENDIF.
cv_file_name = ls_file-filename.
ENDIF.
ENDMETHOD. METHOD update_excel_ole.
DATA:
lt_tabc TYPE STANDARD TABLE OF zchar30000,
lw_tabc TYPE zchar30000,
lv_tabix TYPE sy-tabix,
lt_cell TYPE STANDARD TABLE OF string,
lw_cell TYPE string,
lv_cell_tabix TYPE sy-tabix. DATA:
lv_column_num TYPE i,
lv_column_skp TYPE i,
lw_tab_ref TYPE REF TO data. DATA:
lo_cx_root TYPE REF TO cx_root.
DATA:
lv_message TYPE string.
DATA
descr_ref TYPE REF TO cl_abap_structdescr. FIELD-SYMBOLS:
<lw_tab> TYPE any,
<lv_value> TYPE any. CREATE DATA lw_tab_ref LIKE LINE OF pt_tab.
ASSIGN lw_tab_ref->* TO <lw_tab>. lv_column_skp = pi_skpcl_tbl. CALL METHOD cl_bc_excel=>excel_2_inner_table
EXPORTING
pi_filename = pi_filename
pi_sheetname = pi_sheetname
pi_every_copy_endline = pi_every_copy_endline
pi_endcolumn = pi_endcolumn
CHANGING
pt_tab = lt_tabc
EXCEPTIONS
file_open_error =
OTHERS = . *->把数据切割到内表
LOOP AT lt_tabc INTO lw_tabc.
lv_tabix = sy-tabix. CHECK lv_tabix >= pi_startline.
CLEAR lt_cell. SPLIT lw_tabc AT cl_abap_char_utilities=>horizontal_tab INTO TABLE lt_cell. LOOP AT lt_cell INTO lw_cell.
lv_cell_tabix = sy-tabix. lv_column_num = lv_cell_tabix - pi_startcolumn + + lv_column_skp. CHECK lv_column_num > . ASSIGN COMPONENT lv_column_num OF STRUCTURE <lw_tab> TO <lv_value>.
CHECK sy-subrc = .
TRY.
<lv_value> = lw_cell.
CATCH cx_root INTO lo_cx_root.
descr_ref ?= cl_abap_typedescr=>describe_by_data( pt_tab ).
READ TABLE descr_ref->components INTO DATA(ls_name) INDEX lv_column_num.
lv_message = lv_tabix && 'Lines' && lv_column_num && 'Name of column is ' && ls_name-name && 'error value:' && lw_cell.
MESSAGE e001() WITH lv_tabix lv_column_num lv_message RAISING open_file_err.
ENDTRY.
ENDLOOP.
IF <lw_tab> IS NOT INITIAL.
INSERT <lw_tab> INTO TABLE pt_tab.
CLEAR <lw_tab>.
ENDIF. ENDLOOP. ENDMETHOD. METHOD excel_2_inner_table.
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 . lv_add_rows = pi_every_copy_endline. DATA:
lt_excel_tab TYPE STANDARD TABLE OF zchar30000,
lw_excel_tab TYPE zchar30000,
lw_excel_tab_tmp TYPE zchar30000. *->生成Excel object
CREATE OBJECT ole_excel 'Excel.Application'.
IF sy-subrc <> .
MESSAGE e001(zbc) RAISING file_open_error.
ENDIF. *->
SET PROPERTY OF ole_excel 'Visible' = . GET PROPERTY OF ole_excel 'Workbooks' = ole_workbooks. CALL METHOD OF
ole_workbooks
'Open' = ole_workbook
EXPORTING
# = pi_filename. *->取得Sheet
GET PROPERTY OF ole_workbook 'Worksheets' = ole_worksheets
EXPORTING
# = pi_sheetname. IF sy-subrc = .
CALL METHOD OF
ole_worksheets
'Activate'.
ELSE.
CALL METHOD OF
ole_excel
'QUIT'.
MESSAGE e001() WITH 'error in open Sheet , please check Sheet name' RAISING file_open_error.
ENDIF. *->把Sheet上数据Copy 到 ClipBoard
lv_begin_col = .
lv_end_col = pi_endcolumn.
lv_begin_row = .
lv_end_row = . WHILE lv_subrc IS INITIAL. IF lv_begin_row IS INITIAL.
lv_begin_row = .
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
# = lv_begin_row
# = lv_begin_col. CALL METHOD OF
ole_worksheets
'Cells' = ole_cell_end
EXPORTING
# = lv_end_row
# = lv_end_col. CALL METHOD OF
ole_worksheets
'RANGE' = ole_range
EXPORTING
# = ole_cell_begin
# = ole_cell_end. CALL METHOD OF
ole_range
'SELECT'.
IF sy-subrc <> .
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 =
error_no_gui =
not_supported_by_gui =
OTHERS = .
IF sy-subrc <> .
CALL METHOD OF
ole_excel
'QUIT'.
MESSAGE 'Error during import of clipboard contents' TYPE 'A'.
ENDIF. lv_subrc = .
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 ).
INSERT lw_excel_tab INTO TABLE pt_tab.
CLEAR lv_subrc.
ENDIF.
ENDLOOP. CLEAR lt_excel_tab. ENDWHILE. DATA:
lv_rc TYPE i.
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = lt_excel_tab
CHANGING
rc = lv_rc
EXCEPTIONS
cntl_error =
* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3
OTHERS = . * CALL METHOD OF
* ole_worksheets
* 'COLSE'. * CALL METHOD OF
* ole_workbook
* 'COLSE'. * CALL METHOD OF
* ole_workbooks
* 'CLOSE'. 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. METHOD download_to_excel. DATA: g_excel TYPE ole2_object,
g_workbookl TYPE ole2_object,
g_workbook TYPE ole2_object,
g_sheet TYPE ole2_object,
g_cell TYPE ole2_object,
g_cell1 TYPE ole2_object,
g_range TYPE ole2_object. DATA: l_formkey TYPE wwwdatatab.
DATA: l_excel_template_path TYPE localfile,
l_excel_path TYPE localfile,
l_data_path TYPE localfile.
DATA: l_msg TYPE char128. * Download the excel template from server
CONCATENATE 'MI' excel_id INTO l_formkey.
CONCATENATE path '\' excel_fname '.XLT' INTO l_excel_template_path.
REPLACE ALL OCCURRENCES OF '\\' IN l_excel_template_path WITH '\'. * PERFORM DOWNLOAD_TEMPLATE USING L_FORMKEY L_EXCEL_TEMPLATE_PATH .
DATA: l_path TYPE localfile.
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
key = l_formkey
destination = l_excel_template_path
CHANGING
temp = l_path. * Create EXCEL application
* PERFORM CREATE_APPLICATION CHANGING E_MESSAGE.
DATA: l_indicator TYPE i VALUE 0.
IF g_excel IS INITIAL.
CREATE OBJECT g_excel 'EXCEL.APPLICATION'.
ENDIF.
* PERFORM err_hdl CHANGING e_message.
IF sy-subrc NE 0.
e_message = 'EXCEL操作没成功执行' .
RAISE excel_error.
ENDIF.
SET PROPERTY OF g_excel 'Visible' = l_indicator.
* PERFORM err_hdl CHANGING e_message.
IF sy-subrc NE 0.
e_message = 'EXCEL操作没成功执行' .
RAISE excel_error.
ENDIF.
SET PROPERTY OF g_excel 'DisplayAlerts' = l_indicator. * Download to Excel indicator
l_msg = '下载数据到EXCEL,请等待....'.
* PERFORM progress_indicator USING l_msg.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
percentage = 0
text = l_msg.
* Download data to Front
CONCATENATE path '\' excel_fname '.TXT' INTO l_data_path.
REPLACE ALL OCCURRENCES OF '\\' IN l_data_path WITH '\'.
* PERFORM download_data_file TABLES i_data USING l_data_path.
SORT i_data BY row col.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = l_data_path
write_field_separator = cl_abap_char_utilities=>horizontal_tab
codepage = ''
TABLES
data_tab = i_data.
IF sy-subrc <> 0.
ENDIF.
CLEAR i_data[].
* Open excel template
* PERFORM open_excel_template USING l_excel_template_path
* CHANGING e_message .
CALL METHOD OF g_excel 'Workbooks' = g_workbookl.
* PERFORM err_hdl CHANGING e_message.
IF sy-subrc NE 0.
e_message = 'EXCEL操作没成功执行' .
RAISE excel_error.
ENDIF.
* OPen template file
CALL METHOD OF g_workbookl 'Open' = g_workbook
EXPORTING
#1 = l_excel_template_path.
IF sy-subrc NE 0.
e_message = 'EXCEL操作没成功执行' .
RAISE excel_error.
ENDIF.
* Open excel sheet
* PERFORM open_excel_sheet USING sheet_name
* CHANGING e_message.
CALL METHOD OF g_workbook 'WORKSHEETS' = g_sheet
EXPORTING
#1 = sheet_name.
IF sy-subrc NE 0.
e_message = 'EXCEL操作没成功执行' .
RAISE excel_error.
ENDIF.
CALL METHOD OF g_sheet 'ACTIVATE'.
IF sy-subrc NE 0.
e_message = 'EXCEL操作没成功执行' .
RAISE excel_error.
ENDIF.
* Convert data file to excel
* PERFORM run_macro_open_text USING l_data_path sheet_name.
CALL METHOD OF g_excel 'RUN'
EXPORTING
#1 = 'Open_text'
#2 = l_data_path
#3 = sheet_name.
* Save excel file
CONCATENATE path '\' excel_fname '.XLS' INTO l_excel_path.
REPLACE ALL OCCURRENCES OF '\\' IN l_excel_path WITH '\'.
* PERFORM run_macro_save_excel USING l_excel_path.
CALL METHOD OF g_excel 'RUN'
EXPORTING
#1 = 'Save_excel'
#2 = l_excel_path. IF show_excel = 'X'.
SET PROPERTY OF g_excel 'VISIBLE' = 1 NO FLUSH.
ENDIF.
* Close excel
* PERFORM close_excel CHANGING show_excel.
* Free objects
IF show_excel = space.
CALL METHOD OF g_excel 'Quit'.
ENDIF.
FREE OBJECT g_cell.
FREE OBJECT g_cell1.
FREE OBJECT g_range.
FREE OBJECT g_sheet.
FREE OBJECT g_workbook.
FREE OBJECT g_workbookl. * force flush
CALL FUNCTION 'FLUSH'
EXCEPTIONS
OTHERS = 0. * Delete data file & template file
* PERFORM delete_file USING l_data_path.
CALL FUNCTION 'GUI_DELETE_FILE'
EXPORTING
file_name = l_data_path
EXCEPTIONS
failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.
* PERFORM delete_file USING l_excel_template_path.
CALL FUNCTION 'GUI_DELETE_FILE'
EXPORTING
file_name = l_excel_template_path
EXCEPTIONS
failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.
ENDMETHOD. ENDCLASS.
仅供参考