report in excel

[vijy_mukunthan]

hi friends

how to get the report program in excel sheet. i have done alv report program in ABAP am getting exact result. But my requirement is to get that in excel sheet. how to do that. help me its urgent.

Regards

vijay

[Former Member]

J Are

Posts: 69

Registered: 10/2/07

Forum Points: 0

ALV to excel download

Posted: Jan 19, 2008 12:39 AM E-mail this message Reply

Hello all,

I have a scenario, in my program everything is working fine. Foreground printing, background printing, alv display all is good. The problem arrives when I try to download my alv to excel sheet, than pick table format and than msexcel program to open the excel sheet.

What is happening is that, all the columns with text format are coming on left side and all the numeric format columns are showing up on the right side in that excel table.

Is there any way I can fix this issue. Since columns are getting misplaced due to this strange behaviour.

I tried converting all the numeric or currency columns in my alv to type c, but than I cannot perform the sum on them, since sap does not allow that.

Any suggesation would be great. Thanks in advanced.

Sravan Prakash.V

Posts: 2,033

Registered: 9/21/07

Forum Points: 2,568

Re: ALV to excel download

Posted: Jan 19, 2008 5:10 AM in response to: J Are E-mail this message Reply

Open Excel -> Tools -> Options -> Security -> Macro Security -> Make it low.

Now on the same path open tab -> Trusted Publishers and Check the checkbox for Trust Access to VB Project.

Use the FM - ALV_XXL_CALL. here is the sample -

REPORT ZSKC_ALV_XXL.

TYPE-POOLS : KKBLO.

DATA : ITAB LIKE T100 OCCURS 0,

T_FCAT_LVC TYPE LVC_S_FCAT OCCURS 0 WITH HEADER LINE,

T_FCAT_KKB TYPE KKBLO_T_FIELDCAT.

START-OF-SELECTION.

Get data.

SELECT * UP TO 20 ROWS

FROM T100

INTO TABLE ITAB

WHERE SPRSL = SY-LANGU.

CHECK SY-SUBRC EQ 0.

Create the field catalog.

CALL FUNCTION 'LVC_FIELDCATALOG_MERGE'

EXPORTING

I_STRUCTURE_NAME = 'T100'

CHANGING

CT_FIELDCAT = T_FCAT_LVC[]

EXCEPTIONS

INCONSISTENT_INTERFACE = 1

PROGRAM_ERROR = 2

OTHERS = 3.

CHECK SY-SUBRC EQ 0.

make sure you pass the correct internal table name in the field catalog.

t_fcat_lvC-tabname = 'ITAB'.

MODIFY T_FCAT_LVC TRANSPORTING TABNAME WHERE TABNAME NE SPACE.

Transfer to KKBLO format.

CALL FUNCTION 'LVC_TRANSFER_TO_KKBLO'

EXPORTING

IT_FIELDCAT_LVC = T_FCAT_LVC[]

IMPORTING

ET_FIELDCAT_KKBLO = T_FCAT_KKB

EXCEPTIONS

IT_DATA_MISSING = 1

IT_FIELDCAT_LVC_MISSING = 2

OTHERS = 3.

CHECK SY-SUBRC EQ 0.

Call XXL.

CALL FUNCTION 'ALV_XXL_CALL'

EXPORTING

I_TABNAME = 'ITAB'

IT_FIELDCAT = T_FCAT_KKB

TABLES

IT_OUTTAB = ITAB[]

EXCEPTIONS

FATAL_ERROR = 1

NO_DISPLAY_POSSIBLE = 2

OTHERS = 3.

IF SY-SUBRC 0.

ENDIF.

Regards.

Hema Sundar Mun...

Posts: 823

Registered: 12/24/07

Forum Points: 796

Re: ALV to excel download

Posted: Jan 19, 2008 5:23 AM in response to: J Are E-mail this message Reply

Hi Are.

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

filename = 'c:\test_xls.xls'

filetype = 'ASC'

DAT_MODE = 'X'

TABLES

data_tab = itab.

try like this.

cheers,

Hema.

KIRAN KUMAR

Posts: 280

Registered: 11/17/07

Forum Points: 432

Re: ALV to excel download

Posted: Jan 19, 2008 5:36 AM in response to: J Are E-mail this message Reply

Hi J,

After u populate the data into excel sheet.First open the excel sheet ok and in between ROW(1) COLUMN(A) u have some space place cursor on that and right click select FORMAT CELL option and select TEXT and click ok.Then ur excel sheet data can be converted ok..

Reward points if helpful.

Kiran Kumar.G.A

Have a Nice Day..

J Are

Posts: 69

Registered: 10/2/07

Forum Points: 0

Re: ALV to excel download

Posted: Jan 19, 2008 10:33 PM in response to: J Are E-mail this message Reply

Hello All,

thanks for replying to my question. Well there is no solution to it, pls see note 358644 (this note number I got from different web site, since none of the solution worked).

To get around this problem what I did, is give the user option of selecting one of the two options: 1) ALV with sum capabilities (with numeric fields) 2) ALV with excel sheet download (in this case, the table is prepared only with Type C field types) Hope this is helpful for other folks who have the same issue.

Another option (if you do not want to put a check box or radio button on the selection screen) would be to use user command , so when the user presses the excel sheet btn, you catch its function code and than download the table with all the char fields to the excel.

[Former Member]

hi,

There are different ways to do this. There is the ALV option that comes with the ability to download into spreadsheet format, and you can also use OLE to run Excel & create a spreadsheet. Since it is a report you asked about, ALV is the more logical way to go.

Hope this helps, Do reward.

[Former Member]

I m using OLE to create an excel report.

Can someone help me with the syntax of column width and merging cells and text alignment in Excel.

Regards,

Tarun Bahal

[Former Member]

Hi,

If u want to print out put of the report on spread sheet then,

1. Execute the program,

2. Go to LIST in menu bar,

3. select save/ select,

4. In this go to file, there u get options on which u can save data select spread sheet and save then ur file....

Regards,

kavitha.

[Former Member]

sample program

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.

Check the following link:

http://sap-img.com/abap/download-to-excel-with-format-border-color-cell-etc.htm

[Former Member]

Hi

This feature you can have like, after Displayig your alv you will have a option of export to Spreadsheet, in the Menubar and in the application toolbar..

Else

If you want to have from execution of program u can use OLE to get it to spreadsheet.

Regards

GP

Rewards Point for useful answer.