Read an area from an Excel sheet and write out a diagnostic
Here's how to open an Excel file, activate a sheet, read in a rectangular area from it and write out a diagnostic.
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