REPORT  zzzzzzz_ver2.

TYPE-POOLS ole2.

DATA sc     TYPE ole2_object.
DATA vbcode TYPE string.

DATA excel_filename   TYPE string VALUE 'C:\KKu\Книга3.xls'.

DATA long_string_from_excel TYPE string.
DATA itab_values_from_excel TYPE TABLE OF string.
DATA itab_allrows_from_excel TYPE TABLE OF string.

DATA: BEGIN OF wa_allcells,
        col_a TYPE string,
        col_b TYPE string,
        col_c TYPE string,
        col_d TYPE string,
        col_e TYPE string,
      END OF wa_allcells,
      it_allcells LIKE STANDARD TABLE OF wa_allcells.

DATA range_address TYPE string VALUE 'A1:E65000'.
DATA row_separator TYPE string VALUE '~/|\~'. "вместо "стандартного" CR-LF
DATA col_separator TYPE string VALUE '~|='.   "вместо "стандартного" TAB

DATA rows_in_part TYPE i VALUE 200. "размер (кол-во строк) "партии"
DATA row_offset TYPE i.

START-OF-SELECTION.

  CONCATENATE
  'Option Explicit'

  'Dim xlApp, rngAll, wbk, varArray, retString'
  'Dim rowSeparator, colSeparator, allRowLast'

  'Sub beforeLoop(fileName, rngAddr, rowSep, colSep)'
  '    Set xlApp = CreateObject("Excel.Application")'
  '    Set rngAll = xlApp.Workbooks.Open(fileName).Application.Range(rngAddr)'
  '    allRowLast = rngAll.Row + rngAll.Rows.Count - 1'
  '    rowSeparator = rowSep'
  '    colSeparator = colSep'
  'End Sub'

  'Sub afterLoop()'
  '    Set rngAll = Nothing'
  '    For Each wbk In xlApp.Workbooks'
  '        wbk.Close False'
  '    Next'
  '    Set wbk = Nothing'
  '    xlApp.Quit'
  '    Set xlApp = Nothing'
  'End Sub'

  'Function GetExcelData2(rowOffset, rowsNumber)'
  '    Dim rngPart, partRowFirst, partRowLast, i, j'

  '    partRowFirst = rngAll.Row + rowOffset'
  '    If partRowFirst > rngAll.Parent.Rows.Count Then'
  '        GetExcelData2 = ""'
  '        Exit Function'
  '    End If'
  '    partRowLast = xlApp.WorksheetFunction.Min(partRowFirst + rowsNumber - 1, allRowLast, rngAll.Parent.Rows.Count)'
  '    If partRowLast >= partRowFirst Then'
  '        Set rngPart = rngAll.Resize(partRowLast - partRowFirst + 1).Offset(rowOffset)'
  '    Else'
  '        GetExcelData2 = ""'
  '        Exit Function'
  '    End If'

  '    varArray = rngPart.Value'

  '    retString = ""'
  '    For i = 1 To UBound(varArray, 1)'
  '        For j = 1 To UBound(varArray, 2)'
  '            retString = retString & CStr(varArray(i, j)) & colSeparator'
  '        Next'
  '        retString = Left(retString, Len(retString) - Len(colSeparator))'
  '        retString = retString & rowSeparator'
  '    Next'
  '    GetExcelData2 = Left(retString, Len(retString) - Len(rowSeparator))'
  'End Function'
  INTO vbcode
  SEPARATED BY cl_abap_char_utilities=>cr_lf.

  CREATE OBJECT sc 'MSScriptControl.ScriptControl' NO FLUSH.
  SET PROPERTY OF sc 'Timeout' = -1 no flush.
  SET PROPERTY OF sc 'Language' = 'vbscript' no flush.
  CALL METHOD OF sc 'AddCode' NO FLUSH
    EXPORTING #1 = vbcode.

  CALL METHOD OF sc 'Run' NO FLUSH
    EXPORTING #0 = 'beforeLoop'
    #1 = excel_filename
    #2 = range_address
    #3 = row_separator
    #4 = col_separator.
  CALL METHOD cl_gui_cfw=>flush.

  DO 325 TIMES.
    row_offset = ( sy-index - 1 ) * rows_in_part.

    CLEAR long_string_from_excel.
    CALL METHOD OF sc 'Run' = long_string_from_excel "NO FLUSH
      EXPORTING #0 = 'GetExcelData2'
      #1 = row_offset
      #2 = rows_in_part.

    SPLIT long_string_from_excel AT row_separator
      INTO TABLE itab_values_from_excel.

    APPEND LINES OF itab_values_from_excel TO itab_allrows_from_excel.

  ENDDO.

  CALL METHOD OF sc 'Run' "NO FLUSH
    EXPORTING #0 = 'afterLoop'.

  FREE OBJECT: sc.

  LOOP AT itab_allrows_from_excel INTO long_string_from_excel.
    SPLIT long_string_from_excel
      AT col_separator INTO wa_allcells-col_a
                            wa_allcells-col_b
                            wa_allcells-col_c
                            wa_allcells-col_d
                            wa_allcells-col_e.
    APPEND wa_allcells TO it_allcells.
  ENDLOOP.

  WRITE: / 'Процесс закончен'.

REPORT  zzzzzz_v3.

TYPE-POOLS ole2.

DATA:
  excel_filename          TYPE string VALUE `C:\KKu\Книга3.xls`,
  rst_excel               TYPE ole2_object,
  conn_string             TYPE string,
  long_string_from_excel  TYPE string,
  itab_allrows_from_excel TYPE TABLE OF string,
  itab_values_from_excel  TYPE TABLE OF string.

DATA:
  BEGIN OF wa_allcells,
    col_a TYPE string,
    col_b TYPE string,
    col_c TYPE string,
    col_d TYPE string,
    col_e TYPE string,
  END OF wa_allcells,
  it_allcells LIKE STANDARD TABLE OF wa_allcells.

DATA:
  row_separator TYPE string
                VALUE cl_abap_char_utilities=>cr_lf,
  col_separator TYPE string
                VALUE cl_abap_char_utilities=>horizontal_tab.


START-OF-SELECTION.

*  CONCATENATE `Provider=Microsoft.Jet.OLEDB.4.0;`
*              `Data Source=`
*               excel_filename `;`
*              `Extended Properties='Excel 8.0;HDR=No';`
*         INTO conn_string.

  CONCATENATE `Provider=Microsoft.ACE.OLEDB.12.0;`
              `Data Source=`
               excel_filename `;`
              `Extended Properties='Excel 12.0;HDR=No';`
         INTO conn_string.

  CREATE OBJECT rst_excel 'ADODB.Recordset'.
  CALL METHOD OF rst_excel 'Open'
    EXPORTING #1 = `SELECT * FROM [Лист1$A1:E65000]`
              #2 = conn_string.

  CALL METHOD OF rst_excel 'GetString' = long_string_from_excel.

  SPLIT long_string_from_excel AT row_separator(1) "..(1) здесь
    INTO TABLE itab_allrows_from_excel. "в данном конкретном случае
  "для именно этого разделителя по умолчанию - важно!

  LOOP AT itab_allrows_from_excel INTO long_string_from_excel.
    SPLIT long_string_from_excel
      AT col_separator INTO wa_allcells-col_a
                            wa_allcells-col_b
                            wa_allcells-col_c
                            wa_allcells-col_d
                            wa_allcells-col_e.
    APPEND wa_allcells TO it_allcells.
  ENDLOOP.

  WRITE: / 'Процесс закончен'.