ABAP随笔-EXCEL-3-批量导入(突破标准函数的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.

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.