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

 

http://searchsap.techtarget.com/tip/1,289483,sid21_gci868246,00.html?FromTaxonomy=/pr/283958

 

Vladimir Vyukov

09 Dec 2002, Rating 2.17 (out of 5)

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.)

 

Code

 

 

 

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