Report excel

[Former Member]

Hi,

In reports how to put the output data into excel file ?

[Former Member]

hi

u can use OLE.

[Former Member]

Please explain clealy and is it possible to give any sample report

[rahul2000]

check this code

REPORT zmm_basic_data .

************************************************************************

Report to View Detail Of Material Master BASIC DATA.

Start Date 07.05.2007

Request No.-GR3K931783

************************************************************************

TABLES: mara,marc,makt.

DATA: gi_mara LIKE mara OCCURS 1 WITH HEADER LINE,

gi_marc LIKE marc OCCURS 1 WITH HEADER LINE,

gi_makt LIKE makt OCCURS 1 WITH HEADER LINE.

DATA: BEGIN OF gi_download OCCURS 1,

matnr LIKE mara-matnr,"material no

maktx LIKE makt-maktx,"material description

matkl LIKE mara-matkl,"material group

werks LIKE marc-werks,"plant

ekgrp LIKE marc-ekgrp,"purchasing group

spart LIKE mara-spart,"division

meins LIKE mara-meins,"base uit of measure

bismt LIKE mara-bismt,"old material no.

prdha LIKE mara-prdha,"product hierarchy

brgew LIKE mara-brgew,"gross weight

ntgew LIKE mara-ntgew,"net weight

gewei LIKE mara-gewei,"weight unit

volum LIKE mara-volum,"volume

voleh LIKE mara-voleh,"volume unit

zeinr LIKE mara-zeinr,"document no.

zeiar LIKE mara-zeiar,"document type

zeivr LIKE mara-zeivr,"document version

zeifo LIKE mara-zeifo,"page format of document

blanz LIKE mara-blanz,"number of sheets

spras LIKE makt-spras,"language key

END OF gi_download.

DATA: BEGIN OF gi_fieldnames OCCURS 1,

mandt(50),

END OF gi_fieldnames.

*file path and file name data declaration.

DATA: stripped_name LIKE rlgrap-filename,

file_path LIKE rlgrap-filename.

DATA: inpath LIKE ltran-path01,

file LIKE ltran-file01,

outpath LIKE ltran-path02.

Field Symbols ************

FIELD-SYMBOLS <mara> LIKE gi_mara.

FIELD-SYMBOLS <marc> LIKE gi_marc.

FIELD-SYMBOLS <makt> LIKE gi_makt.

FIELD-SYMBOLS <download> LIKE gi_download.

SELECTION-SCREEN: BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

SELECTION-SCREEN: SKIP.

SELECT-OPTIONS: s_werks FOR marc-werks.

SELECT-OPTIONS: s_ekgrp FOR marc-ekgrp.

SELECT-OPTIONS: s_matnr FOR mara-matnr.

SELECT-OPTIONS: s_matkl FOR mara-matkl.

SELECT-OPTIONS: s_spart FOR mara-spart.

SELECTION-SCREEN: SKIP.

PARAMETER fnm TYPE rlgrap-filename OBLIGATORY.

SELECTION-SCREEN: SKIP.

SELECTION-SCREEN: END OF BLOCK b1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR fnm.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

static = 'X'

CHANGING

file_name = fnm

EXCEPTIONS

mask_too_long = 1

OTHERS = 2.

IF sy-subrc 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

*For fetching the baisc data.

START-OF-SELECTION.

*******************************************************************

*Getting the general data based on material no,division and material

*group.

*******************************************************************

SELECT * FROM mara INTO TABLE gi_mara

WHERE matnr IN s_matnr

AND spart IN s_spart

AND matkl IN s_matkl.

*******************************************************************

*Getting the plant data based on material no,plant and purchasing

*group.

*******************************************************************

IF NOT gi_mara[] IS INITIAL.

SELECT * FROM marc INTO TABLE gi_marc

FOR ALL ENTRIES IN gi_mara

WHERE matnr EQ gi_mara-matnr

AND werks IN s_werks

AND ekgrp IN s_ekgrp.

ENDIF.

***********************************************************

*Getting the material description based on material no

***********************************************************

IF NOT gi_mara[] IS INITIAL.

SELECT * FROM makt INTO TABLE gi_makt

FOR ALL ENTRIES IN gi_mara

WHERE matnr = gi_mara-matnr.

ENDIF.

Fetching all data into single internal table ********

SORT gi_mara BY matnr.

SORT gi_makt BY matnr.

SORT gi_marc BY matnr.

*****Transfering the data into gi_download*******

IF s_werks] IS INITIAL and s_ekgrp[ IS INITIAL.

LOOP AT gi_mara ASSIGNING <mara>.

MOVE-CORRESPONDING <mara> TO gi_download.

READ TABLE gi_marc ASSIGNING <marc>

WITH KEY matnr = <mara>-matnr

BINARY SEARCH.

IF sy-subrc = 0.

MOVE <marc>-werks TO gi_download-werks.

MOVE <marc>-ekgrp TO gi_download-ekgrp.

ENDIF.

READ TABLE gi_makt ASSIGNING <makt>

WITH KEY matnr = <mara>-matnr

BINARY SEARCH.

IF sy-subrc = 0.

MOVE <makt>-maktx TO gi_download-maktx.

MOVE <makt>-spras TO gi_download-spras.

ENDIF.

APPEND gi_download.

CLEAR gi_download.

ENDLOOP.

ELSE.

LOOP AT gi_marc ASSIGNING <marc>.

READ TABLE gi_mara ASSIGNING <mara>

WITH KEY matnr = <marc>-matnr

BINARY SEARCH.

IF sy-subrc = 0.

MOVE-CORRESPONDING <mara> TO gi_download.

ENDIF.

READ TABLE gi_makt ASSIGNING <makt>

WITH KEY matnr = <marc>-matnr

BINARY SEARCH.

IF sy-subrc = 0.

MOVE <makt>-maktx TO gi_download-maktx.

MOVE <makt>-spras TO gi_download-spras.

ENDIF.

MOVE <marc>-werks TO gi_download-werks.

MOVE <marc>-ekgrp TO gi_download-ekgrp.

APPEND gi_download.

CLEAR gi_download.

ENDLOOP.

ENDIF.

IF gi_download[] IS INITIAL.

MESSAGE i001(sa) WITH 'Data not found'.

LEAVE LIST-PROCESSING.

ENDIF.

*******Downloading the basic data********

gi_fieldnames-mandt = 'Material no'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Material description'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Material group'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Plant'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Purchasing group'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Division'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Base uit of measure'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Old material no.'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Product hierarchy'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Gross weight'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Net weight'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Weight unit'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Volume'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Volume unit'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Document no.'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Document type'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Document version'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Page format of document'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Number of sheets'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Language key'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames. CALL FUNCTION 'SO_SPLIT_FILE_AND_PATH'

EXPORTING

full_name = fnm

IMPORTING

stripped_name = stripped_name

file_path = file_path

EXCEPTIONS

x_error = 1

OTHERS = 2.

IF sy-subrc 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

CLEAR fnm.

CONCATENATE file_path stripped_name INTO fnm.

CLEAR: inpath,file,stripped_name,file_path,outpath.

CALL FUNCTION 'WS_DOWNLOAD'

EXPORTING

filename = fnm

filetype = 'DAT'

TABLES

data_tab = gi_download

fieldnames = gi_fieldnames

EXCEPTIONS

file_open_error = 1

file_write_error = 2

invalid_filesize = 3

invalid_type = 4

no_batch = 5

unknown_error = 6

invalid_table_width = 7

gui_refuse_filetransfer = 8

customer_error = 9

OTHERS = 10.

IF sy-subrc 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ELSE.

MESSAGE i003(sa) WITH 'File downloaded successfuly' fnm.

ENDIF.

[Former Member]

U can use the FM

SAP_CONVERT_TO_XLS_FORMAT

to convert itab data to excel

use this module to send an internal table in excel format

for more solutions check thread

[ thread|;

[Former Member]

Hi

OLE is Object Link Enabling.

Here u can play with MS word or Excel at the Frontend using SAP.

You can uplaod data from Excel and Download it into another or same excel.

Sample Code:

parameters: xlsfile(64) type c default

'C:ptmatrix.xls'.

tables usr03.

data: users like usr03 occurs 100 with header line

,items type i.

constants: ok type i value 0.

include ole2incl.

data: excel type ole2_object,

workbook type ole2_object,

sheet type ole2_object,

cell type ole2_object,

row type ole2_object.

• Create an Excel object and start Excel.

create object excel 'EXCEL.APPLICATION'.

if sy-subrc ne ok.

message i000 with sy-msgli.

endif.

• Create an Excel workbook Object.

call method of excel 'WORKBOOKS' = workbook .

• Put Excel in background

set property of excel 'VISIBLE' = 0 .

• Collect the data to the transfer.

select * from usr03 into table users.

describe table users lines items.

check items > 0.

• Put Excel in front.

set property of excel 'VISIBLE' = 1 .

• Transfer the header line to Excel.

call method of workbook

• 'NEW' EXPORTING #1 = XLSFILE.

'OPEN' exporting #1 = xlsfile.

call method of excel 'RANGE' = cell exporting #1 = 'A1'.

set property of cell 'VALUE' = 'BNAME' .

call method of excel 'RANGE' = cell exporting #1 = 'B1'.

set property of cell 'VALUE' = 'NAME1' .

• Transfer the internal table values to Excel

loop at users.

call method of excel 'ROWS' = row exporting #1 = '2' .

call method of row 'INSERT' no flush.

call method of excel 'RANGE' = cell no flush exporting #1 = 'A2' .

set property of cell 'VALUE' = users-bname no flush.

call method of excel 'RANGE' = cell no flush exporting #1 = 'B2' .

set property of cell 'VALUE' = users-name1 no flush.

endloop.

• release and exit Excel.

call method of excel 'SAVE'.

call method of excel 'QUIT'.

• Free all objects

free object cell.

free object workbook.

free object excel.

excel-handle = -1.

free object row.

Pls reward if helpful

Regards

Preeti

[Former Member]

hi,

You have many options.

1 There are some function modults.

2. using menu mainter also u can send the report output to excel file.

REPORT Excel.

TABLES:

sflight.

• header data................................

DATA :

header1 LIKE gxxlt_p-text VALUE 'Suresh',

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 = 'KOTHUR SREEKANTH REDDY'.

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.