01
EXCEL批量导入2-突破9999行的限制
废话不多说，直接执行：
关键是使用了OLE的方法，使用粘贴板的功能从excel中每次复制自己规定的数据量，从而突破了9999行。
代码语言：javascript复制FORM frm_get_excel_2 .
data lv_filename TYPE string.
lv_filename = p_file.
CALL METHOD zcl_document_jxzhu=>update_excel_ole
EXPORTING
pi_filename = lv_filename "excel路径
pi_sheetname = 'Sheet1' "sheet name
pi_startline = '2' "从第几行开始上载
pi_startcolumn = '1' "从第几列开始上载
pi_every_copy_endline = '300' "每次从打开的excel中复制的行数 （建议不要太大，因为我们复制粘贴的时候，粘贴板是有大小限制的
pi_endcolumn = '30' "到第几列结束
* pi_skpcl_tbl = '0'
CHANGING
pt_tab = lt_zmmt001
EXCEPTIONS
open_file_err = 1.
IF sy-subrc <> 0.
MESSAGE e001(00) WITH sy-msgv1 sy-msgv2 sy-msgv3.
STOP.
ENDIF.
cl_demo_output=>display( lt_zmmt001 ).
ENDFORM.方法如下：
代码语言：javascript复制METHOD update_excel_ole.
DATA:
lt_tabc TYPE STANDARD TABLE OF zchar3000,”这个值需要大于excel一行的数据
lw_tabc TYPE zchar3000,
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 zcl_document_jxzhu=>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 = 1
OTHERS = 2.
*->把数据切割到内表
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 1 lv_column_skp.
CHECK lv_column_num > 0.
ASSIGN COMPONENT lv_column_num OF STRUCTURE <lw_tab> TO <lv_value>.
CHECK sy-subrc = 0.
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(00) WITH lv_tabix lv_column_num lv_message RAISING open_file_err.
ENDTRY.
ENDLOOP.
IF <lw_tab> IS NOT INITIAL.
APPEND <lw_tab> TO 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 3000.
LV_ADD_ROWS = PI_EVERY_COPY_ENDLINE.
DATA:
LT_EXCEL_TAB TYPE STANDARD TABLE OF ZCHAR3000,
LW_EXCEL_TAB TYPE ZCHAR3000,
LW_EXCEL_TAB_TMP TYPE ZCHAR3000.
*->生成Excel object
CREATE OBJECT OLE_EXCEL 'Excel.Application'.
IF SY-SUBRC <> 0.
MESSAGE E001(00) RAISING FILE_OPEN_ERROR.
ENDIF.
*->
SET PROPERTY OF OLE_EXCEL 'Visible' = 0.
GET PROPERTY OF OLE_EXCEL 'Workbooks' = OLE_WORKBOOKS.
CALL METHOD OF
OLE_WORKBOOKS
'Open' = OLE_WORKBOOK
EXPORTING
#1 = PI_FILENAME.
*->取得Sheet
GET PROPERTY OF OLE_WORKBOOK 'Worksheets' = OLE_WORKSHEETS
EXPORTING
#1 = PI_SHEETNAME.
IF SY-SUBRC = 0.
CALL METHOD OF
OLE_WORKSHEETS
'Activate'.
ELSE.
GET PROPERTY OF OLE_EXCEL 'ACTIVESHEET' = OLE_WORKSHEETS.
IF SY-SUBRC NE 0.
CALL METHOD OF
OLE_EXCEL
'QUIT'.
MESSAGE E001(00) WITH 'error in open Sheet , please check Sheet name' RAISING FILE_OPEN_ERROR.
ENDIF.
ENDIF.
*->把Sheet上数据Copy 到 ClipBoard
LV_BEGIN_COL = 1.
LV_END_COL = PI_ENDCOLUMN.
LV_BEGIN_ROW = 0.
LV_END_ROW = 0.
"当复制内容为空的时候，LV_SUBRC = 4.
WHILE LV_SUBRC IS INITIAL.
IF LV_BEGIN_ROW IS INITIAL.
LV_BEGIN_ROW = 1.
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
#1 = LV_BEGIN_ROW
#2 = LV_BEGIN_COL.
CALL METHOD OF
OLE_WORKSHEETS
'Cells' = OLE_CELL_END
EXPORTING
#1 = LV_END_ROW
#2 = LV_END_COL.
CALL METHOD OF
OLE_WORKSHEETS
'RANGE' = OLE_RANGE
EXPORTING
#1 = OLE_CELL_BEGIN
#2 = OLE_CELL_END.
CALL METHOD OF
OLE_RANGE
'SELECT'.
IF SY-SUBRC <> 0.
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 = 1
ERROR_NO_GUI = 2
NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4.
IF SY-SUBRC <> 0.
CALL METHOD OF
OLE_EXCEL
'QUIT'.
MESSAGE 'Error during import of clipboard contents' TYPE 'A'.
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 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 = 1
* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4.
* 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.
