topic Re: Report excel in Application Development and Automation Discussions

Report excel

Former Member — Mon, 07 Jan 2008 07:43:04 GMT

Hi,

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

Re: Report excel

Former Member — Mon, 07 Jan 2008 07:48:51 GMT

u can use OLE.

Re: Report excel

Former Member — Mon, 07 Jan 2008 07:51:31 GMT

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

Re: Report excel

rahul2000 — Mon, 07 Jan 2008 07:52:41 GMT

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.

Re: Report excel

Former Member — Mon, 07 Jan 2008 07:53:48 GMT

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|;

Re: Report excel

Former Member — Mon, 07 Jan 2008 07:57:45 GMT

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

Re: Report excel

Former Member — Mon, 07 Jan 2008 08:50:29 GMT

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.