Posted: Fri Sep 14, 2007 5:42 pm Post subject: OLE, Excel
TEXT_CONVERT_XLS_TO_SAP
EXCEL_OLE_STANDARD_DAT - Just calls MS_EXCEL_OLE_STANDARD_DAT
MS_EXCEL_OLE_STANDARD_DAT - Downloads internal table and opens it in MS Excel.
Code:
report z_it2excel.
data: it_t001 type t001 occurs 0,
it_fields type dfies occurs 0 with header line,
begin of it_fnames occurs 0,
reptext like dfies-reptext,
end of it_fnames.
* The structure definition required for this internal table.
* The following definition is not working:
* it_fnames like dfies-reptext occurs 0 with header line.
* because Function Module will use the following:
* ASSIGN COMPONENT 1 OF STRUCTURE FIELDNAMES TO .
select * from t001 into table it_t001.
CALL FUNCTION 'GET_FIELDTAB'
EXPORTING
* LANGU = SY-LANGU
* ONLY = ' '
TABNAME = 'T001'
* WITHTEXT = 'X'
* IMPORTING
* HEADER =
* RC =
TABLES
FIELDTAB = it_fields
EXCEPTIONS
INTERNAL_ERROR = 1
NO_TEXTS_FOUND = 2
TABLE_HAS_NO_FIELDS = 3
TABLE_NOT_ACTIV = 4
OTHERS = 5
.
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
loop at it_fields.
it_fnames-reptext = it_fields-reptext.
append it_fnames.
endloop.
KCD_EXCEL_OLE_TO_INT_CONVERT - Uploads an *.xls file to internal table (max cell length = 32).
This function uses a range selection and copy-paste technique, therefore it quite fast.
Code:
report z.
parameter p_file like rlgrap-filename.
data: w_text type natxt, "message
* this internal table should have the same structure
* as *.xls file to be uploaded. E.g.:
begin of it_data occurs 0,
s1(10), "text field 1
s2(20), "text field 2
n1 type i, "integer number field
end of it_data.
end-of-selection.
loop at it_data.
write: / it_data-s1, it_data-s2, it_data-n1.
endloop.
form f_excel_upload tables p_download
using value(p_filename) like rlgrap-filename
changing p_text type natxt.
data: it_intern type kcde_cells occurs 0 with header line,
w_index type i,
w_start_col type i value '1',
w_start_row type i value '1',
w_end_col type i value '256',
w_end_row type i value '65536',
w_rows like sy-fdpos.
field-symbols: .
call function 'KCD_EXCEL_OLE_TO_INT_CONVERT'
EXPORTING
filename = p_filename
i_begin_col = w_start_col
i_begin_row = w_start_row
i_end_col = w_end_col
i_end_row = w_end_row
TABLES
intern = it_intern
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
others = 3.
if sy-subrc <> 0.
p_text = 'Upload Error:'.
write sy-subrc to p_text+14 left-justified.
else.
if it_intern[] is initial.
p_text = 'No Data Uploaded.'.
else.
sort it_intern by row col.
loop at it_intern.
move : it_intern-col to w_index.
assign component w_index of structure p_download to .
if sy-subrc = 0.
move : it_intern-value to .
unassign .
endif.
at end of row.
append p_download.
clear p_download.
endat.
endloop.
describe table p_download lines w_rows.
p_text = 'Uploaded Rows:'.
write w_rows to p_text+15 left-justified.
endif.
endif.
endform.
* end of source code *
There is another Example that reads data from Excel file cell by cell.
Code:
report z_read_excel.
parameter p_file like rlgrap-filename.
data: w_text type natxt, "message
* this internal table should have the same structure
* as *.xls file to be uploaded. E.g.:
begin of it_data occurs 0,
s1(10), "text field 1
s2(20), "text field 2
n1 type i, "integer number field
end of it_data.
end-of-selection.
loop at it_data.
write: / it_data-s1, it_data-s2, it_data-n1.
endloop.
form f_excel_upload tables p_download
using value(p_filename) like rlgrap-filename
changing p_text type natxt.
data: w_start_col type i value '1',
w_start_row type i value '1',
w_end_col type i value '3',
w_end_row type i value '5',
w_rows like sy-fdpos,
w_cols like sy-fdpos,
w_curr_row like sy-fdpos,
w_curr_col like sy-fdpos,
w_index type i,
w_value(99) type c.
data: h_appl like obj_record,
h_work like obj_record,
h_cell like obj_record.
field-symbols: .
* start excel
if h_appl-header = space or h_appl-handle = -1.
create object h_appl 'excel.application'.
if sy-subrc <> 0.
p_text = 'Start Excel Error:'.
write sy-subrc to p_text+19 left-justified.
message i002(sy) with sy-msgli.
endif.
set property of h_appl 'visible' = 0.
endif.
* open file
call method of h_appl 'workbooks' = h_work.
call method of h_work 'open' exporting #1 = p_filename.
* get data cell by cell
w_rows = w_end_row - w_start_row + 1.
w_cols = w_end_col - w_start_col + 1.
w_curr_row = w_start_row.
do w_rows times.
w_curr_col = w_start_col.
w_index = 1.
do w_cols times.
call method of h_appl 'cells' = h_cell
exporting #1 = w_curr_row
#2 = w_curr_col.
get property of h_cell 'value' = w_value.
assign component w_index of structure p_download to .
if sy-subrc = 0.
move : w_value to .
unassign .
endif.
add 1 to: w_curr_col, w_index.
enddo.
add 1 to w_curr_row.
append p_download.
clear p_download.
enddo.
if p_download[] is initial.
p_text = 'No Data Uploaded.'.
else.
describe table p_download lines w_rows.
p_text = 'Uploaded Rows:'.
write w_rows to p_text+15 left-justified.
endif.
endform.
* end of source code *
ALSM_EXCEL_TO_INTERNAL_TABLE - the same as KCD_EXCEL_OLE_TO_INT_CONVERT but max cell length = 50
FTBU_START_EXCEL - just [download internal table to file and] start Excel (w/o OLE).
XXL_FULL_API - Output Excel list viewer list
- XXLFTEST (XXL Test Environment / XXL_FULL_API Example)
- XXLTTEST (XXL Interface: Test Program for Formats and Types (also XXL_FULL_API))
- XXLSTEST (XXL Test Environment / XXL_SIMPLE_API Example)
XXL_SIMPLE_API - Output list viewer list with reduced functions
XXL_CHECK_API
Examble of use in customer reports:
if question = 'STARTABLE'
and return_code <> 0
then "Download to Excel" button has to be excluded from a pf-status
In fact, the return_code is always set to 0 in the FM source code.
The modules of the TXXL function group uses the DLLs and other files installed with SAPGUI in directory:
...\Program Files\SAP\FrontEnd\SAPgui\xxl\
Can work both with Excel and Lotus 1-2-3:
SAP_CONVERT_TO_XLS_FORMAT - Convert data to Microsoft Excel format.
MS_EXCEL_OLE_STANDARD_OLE - will build a file, and automatically start Excel
RH_START_EXCEL_WITH_DATA - starts Excel with the contents of an internal table. This function finds Excel in the desktop registry. It also uses a local PC working directory to save the file (that's what the 'W' value for data path flag does). Very transparent to user!
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
