on 04-24-2008 7:17 PM
Hi all!
I'm trying to fill the header of a table from SAP to Excel. Does anyone now
the correct steps of set get and properties needed to do this?
Thanks!
u have to append the header into one Itab
and in the DOWNLOAD function in the table option
"fieldnames" should be that itabname...
i think this will help u to download the header.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Example Standard Reports
XXLFTEST
XXLTTEST
Often we face situations where we need to download internal table contents onto an Excel sheet. We are familiar with the function module WS_DOWNLOAD. Though this function module downloads the contents onto the Excel sheet, there cannot be any column headings or we cannot differentiate the primary keys just by seeing the Excel sheet. For this purpose, we can use the function module XXL_FULL_API. The Excel sheet which is generated by this function module contains the column headings and the key columns are highlighted with a different color. Other options that are available with this function module are we can swap two columns or suppress a field from displaying on the Excel sheet. The simple code for the usage of this function module is given below.
Program code :
-
REPORT Excel.
TABLES:
sflight.
header data................................
DATA :
header1 LIKE gxxlt_p-text VALUE 'Raj',
header2 LIKE gxxlt_p-text VALUE 'Excel sheet'.
Internal table for holding the SFLIGHT data
DATA BEGIN OF t_sflight OCCURS 0.
INCLUDE STRUCTURE sflight.
DATA END OF t_sflight.
Internal table for holding the horizontal key.
DATA BEGIN OF t_hkey OCCURS 0.
INCLUDE STRUCTURE gxxlt_h.
DATA END OF t_hkey .
Internal table for holding the vertical key.
DATA BEGIN OF t_vkey OCCURS 0.
INCLUDE STRUCTURE gxxlt_v.
DATA END OF t_vkey .
Internal table for holding the online text....
DATA BEGIN OF t_online OCCURS 0.
INCLUDE STRUCTURE gxxlt_o.
DATA END OF t_online.
Internal table to hold print text.............
DATA BEGIN OF t_print OCCURS 0.
INCLUDE STRUCTURE gxxlt_p.
DATA END OF t_print.
Internal table to hold SEMA data..............
DATA BEGIN OF t_sema OCCURS 0.
INCLUDE STRUCTURE gxxlt_s.
DATA END OF t_sema.
Retreiving data from sflight.
SELECT * FROM sflight
INTO TABLE t_sflight.
Text which will be displayed online is declared here....
t_online-line_no = '1'.
t_online-info_name = 'Created by'.
t_online-info_value = 'Raj'.
APPEND t_online.
Text which will be printed out..........................
t_print-hf = 'H'.
t_print-lcr = 'L'.
t_print-line_no = '1'.
t_print-text = 'This is the header'.
APPEND t_print.
t_print-hf = 'F'.
t_print-lcr = 'C'.
t_print-line_no = '1'.
t_print-text = 'This is the footer'.
APPEND t_print.
Defining the vertical key columns.......
t_vkey-col_no = '1'.
t_vkey-col_name = 'MANDT'.
APPEND t_vkey.
t_vkey-col_no = '2'.
t_vkey-col_name = 'CARRID'.
APPEND t_vkey.
t_vkey-col_no = '3'.
t_vkey-col_name = 'CONNID'.
APPEND t_vkey.
t_vkey-col_no = '4'.
t_vkey-col_name = 'FLDATE'.
APPEND t_vkey.
Header text for the data columns................
t_hkey-row_no = '1'.
t_hkey-col_no = 1.
t_hkey-col_name = 'PRICE'.
APPEND t_hkey.
t_hkey-col_no = 2.
t_hkey-col_name = 'CURRENCY'.
APPEND t_hkey.
t_hkey-col_no = 3.
t_hkey-col_name = 'PLANETYPE'.
APPEND t_hkey.
t_hkey-col_no = 4.
t_hkey-col_name = 'SEATSMAX'.
APPEND t_hkey.
t_hkey-col_no = 5.
t_hkey-col_name = 'SEATSOCC'.
APPEND t_hkey.
t_hkey-col_no = 6.
t_hkey-col_name = 'PAYMENTSUM'.
APPEND t_hkey.
populating the SEMA data.......................... t_sema-col_no = 1. t_sema-col_typ = 'STR'. t_sema-col_ops = 'DFT'. APPEND t_sema.
t_sema-col_no = 2.
APPEND t_sema.
t_sema-col_no = 3.
APPEND t_sema.
t_sema-col_no = 4.
APPEND t_sema.
t_sema-col_no = 5.
APPEND t_sema.
t_sema-col_no = 6.
APPEND t_sema.
t_sema-col_no = 7.
APPEND t_sema.
t_sema-col_no = 8.
APPEND t_sema.
t_sema-col_no = 9.
APPEND t_sema.
t_sema-col_no = 10.
t_sema-col_typ = 'NUM'.
t_sema-col_ops = 'ADD'.
APPEND t_sema.
CALL FUNCTION 'XXL_FULL_API'
EXPORTING
DATA_ENDING_AT = 54
DATA_STARTING_AT = 5
filename = 'TESTFILE'
header_1 = header1
header_2 = header2
no_dialog = 'X'
no_start = ' '
n_att_cols = 6
n_hrz_keys = 1
n_vrt_keys = 4
sema_type = 'X'
SO_TITLE = ' '
TABLES
data = t_sflight
hkey = t_hkey
online_text = t_online
print_text = t_print
sema = t_sema
vkey = t_vkey
EXCEPTIONS
cancelled_by_user = 1
data_too_big = 2
dim_mismatch_data = 3
dim_mismatch_sema = 4
dim_mismatch_vkey = 5
error_in_hkey = 6
error_in_sema = 7
file_open_error = 8
file_write_error = 9
inv_data_range = 10
inv_winsys = 11
inv_xxl = 12
OTHERS = 13
.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
I hope it helps.
Thanks,
Vibha
Please mark all the useful answers
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Ashutosh...Use the below code i think it will be helpful
This program demonstrates how to send abap data to excel sheet
using OLE automation
include ole2incl.
*handles for OLE object
data: h_excel type ole2_object, " Excel object
h_mapl type ole2_object, " list of workbooks
h_map type ole2_object, " workbook
h_zl type ole2_object, " cell
h_f type ole2_object. " font
data: h type i.
types: begin of t_bkpf,
bukrs type bkpf-bukrs,
belnr type bkpf-belnr,
gjahr type bkpf-gjahr,
blart type bkpf-blart,
budat type bkpf-budat,
end of t_bkpf.
data: it_bkpf type standard table of t_bkpf,
wa_bkpf type t_bkpf.
********start-of-selection*******************
start-of-selection.
select bukrs
belnr
gjahr
blart
budat into table it_bkpf
from bkpf
up to 10 rows.
call function 'SAPGUI_PROGRESS_INDICATOR'
exporting
PERCENTAGE = 0
text = text-001
exceptions
others = 1.
Start excel
create object h_excel 'EXCEL.APPLICATION'.
perform err_hdl.
set property of h_excel 'Visible' = 1.
perform err_hdl.
call function 'SAPGUI_PROGRESS_INDICATOR'
exporting
PERCENTAGE = 0
text = text-002
exceptions
others = 1.
call method of h_excel 'workbooks' = h_mapl.
perform err_hdl.
Add a new workbook
call method of h_mapl 'Add' = h_map.
perform err_hdl.
call function 'SAPGUI_PROGRESS_INDICATOR'
exporting
PERCENTAGE = 0
text = text-003
exceptions
others = 1.
Output Column Headings to active excel sheet
perform fill_cell using 1 1 1 text-004.
perform fill_cell using 1 2 1 text-005.
perform fill_cell using 1 3 1 text-006.
perform fill_cell using 1 4 1 text-007.
perform fill_cell using 1 5 1 text-008.
Copy internal table to excel sheet
loop at it_bkpf into wa_bkpf.
h = sy-tabix + 1.
perform fill_cell1 using h 1 0 wa_bkpf-bukrs.
perform fill_cell2 using h 2 0 wa_bkpf-belnr.
perform fill_cell1 using h 3 0 wa_bkpf-gjahr.
perform fill_cell1 using h 4 0 wa_bkpf-blart.
perform fill_cell1 using h 5 0 wa_bkpf-budat.
endloop.
disconnect from Excel
free object h_excel.
perform err_hdl.
&----
*& Form err_hdl
&----
text
----
--> p1 text
<-- p2 text
----
form err_hdl .
if sy-subrc <> 0.
write: / 'Error in OLE Automation'.
stop.
endif.
endform. " err_hdl
&----
*& Form fill_cell
&----
text
----
-->P_1 text
-->P_1 text
-->P_1 text
-->P_TEXT_004 text
----
form fill_cell using i j bold val.
call method of h_excel 'Cells' = h_zl exporting #1 = i #2 = j.
perform err_hdl.
set property of h_zl 'Value' = val .
perform err_hdl.
get property of h_zl 'Font' = h_f.
perform err_hdl.
set property of h_f 'Bold' = bold .
perform err_hdl.
set property of h_f 'colorindex' = 10.
endform. " fill_cell
&----
*& Form fill_cell1
&----
text
----
-->P_H text
-->P_1 text
-->P_0 text
-->P_WA_BKPF_BUKRS text
----
form fill_cell1 using i j bold val.
call method of h_excel 'Cells' = h_zl exporting #1 = i #2 = j.
perform err_hdl.
set property of h_zl 'Value' = val .
perform err_hdl.
get property of h_zl 'Font' = h_f.
perform err_hdl.
set property of h_f 'Bold' = bold .
perform err_hdl.
endform. " fill_cell1
&----
*& Form fill_cell2
&----
text
----
-->P_H text
-->P_2 text
-->P_0 text
-->P_WA_BKPF_BELNR text
----
form fill_cell2 using i j bold val.
call method of h_excel 'Cells' = h_zl exporting #1 = i #2 = j.
perform err_hdl.
set property of h_zl 'Value' = val .
perform err_hdl.
get property of h_zl 'Font' = h_f.
perform err_hdl.
set property of h_f 'Bold' = bold .
perform err_hdl.
set property of h_f 'colorindex' = 13.
endform. " fill_cell2
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Here is some example code for this, Reward Points if usefull
Pls go through this link
http://www.sap-img.com/abap/download-to-excel-with-format-border-color-cell-etc.htm
Regards
Fareedas
Edited by: Fareeda Tabassum S on Apr 25, 2008 10:39 AM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
98 | |
11 | |
11 | |
10 | |
10 | |
8 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.