Read an area from an Excel sheet and write out a diagnostic

• Vladimir Vyukov
• Published: 09 Dec 2002

This tip is a developable example of how to open an Excel file, find and activate a sheet, read in a rectangular area from it and write out a diagnostics. To run the program, prepare Excel test file 'c:temptest_ole.xls ' with the second sheet named 'Test' and add some data in its first two columns up to 11th row. (Written in version 4.6C, NT.)
 

REPORT ZVVTST_OLE .
INCLUDE OLE2INCL.
type-pools kcde.
Parameters: w_book  like RLGRAP-FILENAME obligatory lower case
                                   default 'c:temptest_ole.xls',
            w_sheet(50)                  obligatory lower case
                                   default 'Test'.
Select-options:
  rows for sy-index default 2 to 11 obligatory NO-EXTENSION,
  cols for sy-index default 1 to  2 obligatory NO-EXTENSION.
Parameters: visible  as checkbox default 'X',
            out_diag as checkbox default 'X'.

DATA:  EXCEL     TYPE OLE2_OBJECT,
       WORKBOOK  TYPE OLE2_OBJECT,
       SHEET     TYPE OLE2_OBJECT,
       RANGE     TYPE OLE2_OBJECT,
       CELL1     TYPE OLE2_OBJECT,
       CELL2     TYPE OLE2_OBJECT,
     ret_code,
     EXCEL_TAB TYPE KCDE_SENDER.

at selection-screen on value-request for w_book.
  perform f4_filename changing w_book.

at selection-screen output.
  import w_sheet from memory id 'SH'.  free memory id 'SH'.
  import w_book  from memory id 'WB'.  free memory id 'WB'.

start-of-selection.
  perform workbook.
  perform open_workbook using w_book changing  ret_code.
    if   not ret_code is initial.
      concatenate w_book '- NOT FOUND' into w_book separated by space.
      export w_book to memory id 'WB'.
      perform quit_excel.
      exit.
    endif.
  perform open_sheet    using w_sheet changing ret_code.
    if   not ret_code is initial.
      concatenate w_sheet '- NOT FOUND' into w_sheet separated by space.
      export w_sheet to memory id 'SH'.
    endif.
  perform swallow_range  changing ret_code.
  perform spit_out_diag  changing ret_code.
  perform quit_excel.
    if   not ret_code is initial.
      concatenate w_sheet '- SOME ERR' into w_sheet separated by space.
      export w_sheet to memory id 'SH'.
    endif.
*----------------------------------------------------------------------*
FORM open_sheet USING  value(P_SH)
             changing  p_ret_code.
data sheet_name(51).
  concatenate '#' P_SH into sheet_name.
  while sheet_name <> p_sh.
    CALL METHOD OF EXCEL 'Worksheets' = SHEET  EXPORTING #1 = sy-index.
    if   sy-subrc <> 0.
      move 'X' to p_ret_code.
      exit.
    endif.
    get PROPERTY OF SHEET 'Name' = sheet_name.
  endwhile.
  if    p_ret_code is initial.
    CALL METHOD OF SHEET  'Activate'.
  endif.
ENDFORM.                    " open_sheet
*----------------------------------------------------------------------*
FORM open_workbook USING value(P_WB) changing  p_ret_code.
  CALL METHOD  OF workbook 'Open' EXPORTING #1 = p_wb.
  if   sy-subrc <> 0.
    move 'X' to p_ret_code.
  endif.
ENDFORM.                    " open_workbook
*----------------------------------------------------------------------*
FORM workbook.
  CREATE OBJECT EXCEL 'excel.application'.
  if   visible = 'X'.
    SET PROPERTY OF EXCEL 'visible' = 1.
  endif.
  CALL METHOD OF EXCEL 'Workbooks' = workbook.
*  CALL METHOD OF workbook 'Add'.
ENDFORM.                    " workbook
*----------------------------------------------------------------------*
FORM swallow_range CHANGING P_RET_CODE.
constants:  delimiter    type x value 9.
data:       cells type string occurs 0 with header line,
            n_of_rows type i.
field-symbols: <ex_fs> like line of excel_tab.

 CALL METHOD OF sheet 'Cells' = CELL1
                            EXPORTING #1 = rows-low  #2 = cols-low.
 CALL METHOD OF sheet 'Cells' = CELL2
                            EXPORTING #1 = rows-high #2 = cols-high.
 CALL METHOD OF sheet 'RANGE' = RANGE EXPORTING #1 = CELL1 #2 = CELL2.
 CALL METHOD OF RANGE 'SELECT'.
 CALL METHOD OF RANGE 'COPY'.
 CALL FUNCTION 'CONTROL_FLUSH'  EXCEPTIONS    OTHERS = 3.
 CALL FUNCTION 'CLPB_IMPORT'    TABLES      DATA_TAB   = EXCEL_TAB
                                EXCEPTIONS  CLPB_ERROR = 1
                                OTHERS     = 2.
  n_of_rows = rows-high - rows-low + 1.
  loop at EXCEL_TAB assigning <ex_fs>.
    if    sy-tabix > n_of_rows. " CLPB_IMPORT returns one extra line
      delete excel_tab.
      continue.
    endif.
    split <ex_fs> at delimiter into table cells.
    new-line.
    loop at cells.
      write: cells.
    endloop.
    Move 'Data OK.' to <ex_fs>. " or some other diagnostics
  endloop.
ENDFORM.                    " swallow_range
*----------------------------------------------------------------------*
FORM quit_excel.
  if     not ret_code is initial.
    SET PROPERTY OF EXCEL 'DisplayAlerts' = 0. " to be asked about save
  endif.
  CALL METHOD  OF workbook 'Close'.
  CALL METHOD  OF EXCEL 'Quit'.
  FREE OBJECT EXCEL.
ENDFORM.                    " quit_excel
*----------------------------------------------------------------------*
FORM spit_out_diag CHANGING P_RET_CODE.
 check     P_RET_CODE  is initial.
 check not out_diag    is initial.
 add 1 to cols-high. " place diagnostics into column next to inp. range
 CALL METHOD OF SHEET 'Cells' = CELL1
                            EXPORTING #1 = rows-low  #2 = cols-high.
 CALL METHOD OF SHEET 'Cells' = CELL2
                            EXPORTING #1 = rows-high #2 = cols-high.
  CALL METHOD OF SHEET 'RANGE' = RANGE EXPORTING #1 = CELL1 #2 = CELL2.
  CALL METHOD OF RANGE 'SELECT'.
  CALL FUNCTION 'CLPB_EXPORT'   TABLES      DATA_TAB   = EXCEL_TAB
                                EXCEPTIONS  CLPB_ERROR = 1
                                            OTHERS     = 2.
  CALL FUNCTION 'CONTROL_FLUSH' EXCEPTIONS  OTHERS = 3.
  CALL METHOD OF SHEET 'PASTE'.
ENDFORM.                    " spit_out_diag
*----------------------------------------------------------------------*
FORM f4_filename CHANGING P_FILENAME.
data repid type sy-repid.
  move sy-repid to repid.
  call function 'F4_FILENAME'
    exporting  program_name  = repid
               dynpro_number = sy-dynnr
               feld_name     = 'PATH'
    importing  file_name     = P_FILENAME.
ENDFORM.                    " f4_filename