APO自带的标准函数读取Excel时,如果数据量比较大,会非常慢,改造了一下,读取速度快多了,代码如下:
Importing参数如下:
table参数如下:
完整代码如下:
FUNCTION zapo_upload_xls_file_2_itab.
*"----------------------------------------------------------------------
*"*"本地接口:
*" IMPORTING
*" VALUE(I_FILENAME) TYPE RLGRAP-FILENAME OPTIONAL
*" VALUE(I_BEGIN_ROW) TYPE I DEFAULT 1
*" TABLES
*" E_ITAB TYPE STANDARD TABLE OPTIONAL
*"----------------------------------------------------------------------
* Define Screen Container
DATA: obj_container TYPE REF TO cl_gui_custom_container.
DATA: o_error TYPE REF TO i_oi_error,
o_control TYPE REF TO i_oi_container_control,
o_document TYPE REF TO i_oi_document_proxy,
o_spreadsheet TYPE REF TO i_oi_spreadsheet.
* Data declarations.
DATA: t_files TYPE filetable,
s_files TYPE file_table,
v_doc_name TYPE char256,
v_rcode TYPE int4,
lsc_content TYPE soi_generic_table,
lsc_cell TYPE soi_generic_item,
v_action TYPE int4.
* Grid controls
DATA:
lgc_top TYPE i VALUE 1, "top-left corner in the grid
lgc_left TYPE i VALUE 1,
lgc_rows TYPE i VALUE 9999, "size of the processed part
lgc_empty_rows TYPE i, "stop processing by x empty rows
lgc_columns TYPE i, "count of columns in the e_itab
lgc_column TYPE i. "currently processed column
* Sheet controls and content
DATA:
lsc_rcode TYPE soi_ret_string,
lsc_range(128) TYPE c VALUE 'BAUP_rng', "#EC NOTEXT
lsc_range_list TYPE soi_range_list,
l_flg TYPE i.
DATA:ref_structure TYPE REF TO cl_abap_structdescr.
FIELD-SYMBOLS: <itab_field>.
IF i_begin_row IS NOT INITIAL.
lgc_top = i_begin_row.
ENDIF.
CLASS c_oi_errors DEFINITION LOAD.
* Create Instance control for container
CALL METHOD c_oi_container_control_creator=>get_container_control
IMPORTING
control = o_control
error = o_error.
IF o_error->has_failed = 'X'.
CALL METHOD o_error->raise_message
EXPORTING
type = 'E'.
ENDIF.
* Create generic container linked to container in screen 100
CREATE OBJECT obj_container
EXPORTING
container_name = 'CONTAINER'
EXCEPTIONS
cntl_error = 1
cntl_system_error = 2
create_error = 3
lifetime_error = 4
lifetime_dynpro_dynpro_link = 5
OTHERS = 6.
IF sy-subrc <> 0.
MESSAGE e208(00) WITH 'Error creating container'.
ENDIF.
* Establish connection to GUI Control
CALL METHOD o_control->init_control
EXPORTING
r3_application_name = 'Excel Document Container'
inplace_enabled = 'X'
parent = obj_container
IMPORTING
error = o_error.
IF o_error->has_failed = 'X'.
CALL METHOD o_error->raise_message
EXPORTING
type = 'E'.
ENDIF.
* Create Document Proxy
CALL METHOD o_control->get_document_proxy
EXPORTING
document_type = soi_doctype_excel_sheet
IMPORTING
document_proxy = o_document
error = o_error.
IF o_error->has_failed = 'X'.
CALL METHOD o_error->raise_message
EXPORTING
type = 'E'.
ENDIF.
IF i_filename IS NOT INITIAL.
CONCATENATE 'FILE://' i_filename INTO v_doc_name.
ELSE.
* Call dialog to navigate to file
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
default_extension = '.xls'
file_filter = '*.xls'
initial_directory = 'C:\'
CHANGING
file_table = t_files
rc = v_rcode
user_action = v_action
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error = 2
error_no_gui = 3
OTHERS = 4.
IF sy-subrc <> 0.
MESSAGE e208(00) WITH 'FILE_OPEN_DIALOG'.
ENDIF.
* Only continue if User hasn't cancelled
CHECK: v_action = 0.
* Determine filename to open Excel document
READ TABLE t_files INDEX 1 INTO s_files.
IF sy-subrc = 0.
CONCATENATE 'FILE://' s_files-filename INTO v_doc_name.
ELSE.
MESSAGE e208(00).
ENDIF.
ENDIF.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
text = TEXT-000.
* Open Spreadsheet in SAPWORKDIR
CALL METHOD o_document->open_document
EXPORTING
open_inplace = 'X'
document_title = 'Excel'
document_url = v_doc_name
no_flush = ''
IMPORTING
error = o_error.
IF o_error->has_failed = 'X'.
CALL METHOD o_error->raise_message
EXPORTING
type = 'E'.
ENDIF.
* Open Spreadsheet interface
CALL METHOD o_document->get_spreadsheet_interface
EXPORTING
no_flush = ''
IMPORTING
sheet_interface = o_spreadsheet
error = o_error.
IF o_error->has_failed = 'X'.
CALL METHOD o_error->raise_message
EXPORTING
type = 'E'.
ENDIF.
* Get the target itab structure
ref_structure ?= cl_abap_typedescr=>describe_by_data( e_itab ).
DESCRIBE TABLE ref_structure->components LINES lgc_columns.
DO.
* Get the next part of the grid to process
CLEAR: lsc_content[], lsc_cell.
CALL METHOD o_spreadsheet->set_selection
EXPORTING
top = lgc_top
left = lgc_left
rows = lgc_rows
columns = lgc_columns
IMPORTING
retcode = lsc_rcode.
CALL METHOD o_spreadsheet->insert_range
EXPORTING
columns = lgc_columns
rows = lgc_rows
name = lsc_range
IMPORTING
retcode = lsc_rcode.
CALL METHOD o_spreadsheet->get_ranges_names
IMPORTING
ranges = lsc_range_list
retcode = lsc_rcode.
DELETE lsc_range_list WHERE name <> lsc_range.
CALL METHOD o_spreadsheet->get_ranges_data
IMPORTING
contents = lsc_content
retcode = lsc_rcode
CHANGING
ranges = lsc_range_list.
* Convert xls cells to itab
LOOP AT lsc_content INTO lsc_cell.
ADD 1 TO lgc_column.
ASSIGN COMPONENT lgc_column OF STRUCTURE e_itab TO <itab_field>.
IF sy-subrc = 0.
<itab_field> = lsc_cell-value.
ENDIF.
AT END OF row.
CLEAR lgc_column.
IF e_itab IS INITIAL.
ADD 1 TO lgc_empty_rows.
ELSE.
APPEND e_itab.
CLEAR: e_itab, lgc_empty_rows.
ENDIF.
ENDAT.
ENDLOOP.
ADD lgc_rows TO lgc_top.
CHECK lgc_empty_rows > 4 OR lsc_content[] IS INITIAL.
EXIT.
ENDDO.
* Final clean-up
FREE o_spreadsheet.
CALL METHOD o_document->close_document.
CALL METHOD o_document->release_document.
FREE o_document.
CALL METHOD o_control->release_all_documents.
CALL METHOD o_control->destroy_control.
ENDFUNCTION.