ABAP 随笔 EXCEL3批量导入突破标准函数的9999行
原创 Tab Zhu [ SAP TAB ](javascript:void\(0\);)
**点击蓝字关注我们**
富强 民主 文明 和谐
自由 平等 公正 法制
爱国 敬业 诚信 友善
_**01** _
**EXCEL批量导入2-突破9999行的限制**
**不念过** **去**
**START**
**不畏将来**
废话不多说，直接执行：
关键是使用了OLE的方法，使用粘贴板的功能从excel中每次复制自己规定的数据量，从而突破了9999行。
FORM frm_get_excel_2 .data lv_filename TYPE string.
lv_filename = p_file.
CALL METHOD zcl_document_jxzhu=>update_excel_oleEXPORTING
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_zmmt001EXCEPTIONS
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.方法如下：
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_tableEXPORTING
pi_filename = pi_filename
pi_sheetname = pi_sheetname
pi_every_copy_endline = pi_every_copy_endline
pi_endcolumn = pi_endcolumn
CHANGING
pt_tab = lt_tabcEXCEPTIONS
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 ) INDEXlv_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 OFOLE_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 OFOLE_WORKSHEETS
'Activate' .
ELSE .
GET PROPERTY OF OLE_EXCEL 'ACTIVESHEET' = OLE_WORKSHEETS .
IF SY \- SUBRC NE 0 .
CALL METHOD OFOLE_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 OFOLE_WORKSHEETS
'Cells' = OLE_CELL_BEGIN
EXPORTING
#1 = LV_BEGIN_ROW
#2 = LV_BEGIN_COL .
CALL METHOD OFOLE_WORKSHEETS
'Cells' = OLE_CELL_END
EXPORTING
#1 = LV_END_ROW
#2 = LV_END_COL .
CALL METHOD OFOLE_WORKSHEETS
'RANGE' = OLE_RANGE
EXPORTING
#1 = OLE_CELL_BEGIN
#2 = OLE_CELL_END .
CALL METHOD OFOLE_RANGE
'SELECT' .
IF SY \- SUBRC <> 0 .EXIT .
ENDIF .CALL METHOD OFOLE_RANGE
'COPY' .
_ * read clipboard into ABAP _
CALL METHOD CL_GUI_FRONTEND_SERVICES => CLIPBOARD_IMPORTIMPORTING
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 OFOLE_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_EXPORTIMPORTING
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 OFOLE_EXCEL
'QUIT' .
FREE OBJECT
OLE_EXCEL ,
OLE_WORKBOOKS ,
OLE_WORKBOOK ,
OLE_WORKSHEETS ,
OLE_WORKSHEET ,
OLE_CELL_BEGIN ,
OLE_CELL_END ,
OLE_RANGE .
ENDMETHOD .-Tab Zhu 不念过去 不畏将来
**不念过** **去**
**END**
**不畏将来**
**SAP ABAP TAB**
微信号 : TAB_JXZHU
后台回复'ABAP'免费领取学习资料
长按二维码关注我们
