2020.02.27 【ABAP随笔】- EXCEL批导程式2 - 突破9999行的束缚
废话不多说，直接执行：
关键是使用了OLE的方法，使用粘贴板的功能从excel中每次复制自己规定的数据量，从而突破了9999行。
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.
方法如下：
CLASS-METHODS update_excel_ole "上载excel IMPORTING REFERENCE(pi_filename) TYPE string "文件路径 REFERENCE(pi_sheetname) TYPE string DEFAULT 'Sheet1' " sheet 名称 REFERENCE(pi_startline) TYPE i DEFAULT '1'"从第几行开始导入 REFERENCE(pi_startcolumn) TYPE i DEFAULT '1'"从第几列开始导入 REFERENCE(pi_every_copy_endline) TYPE i DEFAULT '3000'"每次导入的行数 REFERENCE(pi_endcolumn) TYPE i DEFAULT '256'"导入最大列数 REFERENCE(pi_skpcl_tbl) TYPE i DEFAULT '0' "从内表第几列开始插入数据 CHANGING VALUE(pt_tab) TYPE STANDARD TABLE "内表 EXCEPTIONS open_file_err.
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.
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 '3000' VALUE(PI_ENDCOLUMN) TYPE I DEFAULT '256' CHANGING VALUE(PT_TAB) TYPE STANDARD TABLE EXCEPTIONS FILE_OPEN_ERROR PROTECTED SECTION.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." PI_EVERY_COPY_ENDLINE 每次增加复制的行数
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.
-Tab Zhu 不念过去 不畏将来
