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