data transfer to excel sheet

[Former Member]

I am trying to transfer data from 3 different tables based on some condition into an excel sheet.my requirement is need to leave first line as blank or put the headers.and also i want to know in which order it will be transferred to excel sheet.,i am not able to distinguish the fileds in excel sheet.

[Former Member]

Are you passing a table of data to a function module to download to excel? If that is the case, then the fields will be in whatever order they are in the table.

- April King

[Former Member]

Hi Prasad,

Use function module "Gui_download"

refer the code written below :

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

• BIN_FILESIZE =

filename = v_file " file name

filetype = 'ASC'

• APPEND = ' '

write_field_separator = 'X'

• HEADER = '00'

• TRUNC_TRAILING_BLANKS = ' '

• WRITE_LF = 'X'

• COL_SELECT = ' '

• COL_SELECT_MASK = ' '

• DAT_MODE = ' '

• CONFIRM_OVERWRITE = ' '

• NO_AUTH_CHECK = ' '

• CODEPAGE = ' '

• IGNORE_CERR = ABAP_TRUE

• REPLACEMENT = '#'

• WRITE_BOM = ' '

• TRUNC_TRAILING_BLANKS_EOL = 'X'

• WK1_N_FORMAT = ' '

• WK1_N_SIZE = ' '

• WK1_T_FORMAT = ' '

wk1_t_size = '30'

TABLES

data_tab = i_tab

IF sy-subrc <> 0.

• MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

• WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

Reward points if helpful.

Regards,

Hemant

[Former Member]

Are you passing a table of data to a function module to download to excel? If that is the case, then the fields will be in whatever order they are in the table.

- April King

[Former Member]

I need to leave the first line as blank,what should be done for that

[Former Member]

You could move all of the data from your first table to a second table that has the first record blank. Define the second table just like the first one. Move the data like this:

CLEAR itab2.

APPEND itab2.

APPEND LINES OF itab1 TO itab2.

Then export this second table to the Excel sheet.

- April

[Former Member]

Hi

You can use the GUI_DOWNLOAD fun module for transfer data to EXCEL sheet

with headers

see the sample code

ALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

FILENAME = <fullpath>

FILETYPE = 'BIN'

TABLES

DATA_TAB = <your internal table>

EXCEPTIONS

FILE_WRITE_ERROR = 1

NO_BATCH = 2

GUI_REFUSE_FILETRANSFER = 3

INVALID_TYPE = 4

NO_AUTHORITY = 5

UNKNOWN_ERROR = 6

HEADER_NOT_ALLOWED = 7

SEPARATOR_NOT_ALLOWED = 8

FILESIZE_NOT_ALLOWED = 9

HEADER_TOO_LONG = 10

DP_ERROR_CREATE = 11

DP_ERROR_SEND = 12

DP_ERROR_WRITE = 13

UNKNOWN_DP_ERROR = 14

ACCESS_DENIED = 15

DP_OUT_OF_MEMORY = 16

DISK_FULL = 17

DP_TIMEOUT = 18

FILE_NOT_FOUND = 19

DATAPROVIDER_EXCEPTION = 20

CONTROL_FLUSH_ERROR = 21

OTHERS = 22.

or you can use the fun module

EXCEL_OLE_STANDARD_DAT

Reward points if useful

Regards

Anji

[Former Member]

Hi..

Siva..

For the Internal table that u r passing to EXCEL,

make the necessary modifications in the itab itself as if it is header....insert one blank row...like that..

then..

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

• BIN_FILESIZE =

FILENAME = 'C:\ram\file.xls'

FILETYPE = 'ASC'

• APPEND = ' '

WRITE_FIELD_SEPARATOR = ' X'

TABLES

DATA_TAB = itab.

[Former Member]

Hi prasad,

refer this code........

----

---

• T A B L E S D E C L E R A T I O N

----

---

Tables : MARA , MAKT , MARD .

----

---

• T Y P E - P O O L S D E C L E R A T I O N

----

---

TYPE-POOLS OLE2 .

----

---

• D A T A D E C L E R A T I O N

----

---

*---handles for OLE objects--

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_FORMAT2 TYPE OLE2_OBJECT,

H_FORMAT3 TYPE OLE2_OBJECT,

H_FORMAT4 TYPE OLE2_OBJECT,

H_FORMAT1 TYPE OLE2_OBJECT.

----

---

• I N T E R N A L T A B L E

----

---

Data : Begin of it_1 occurs 0 ,

matnr like mara-matnr ,

maktx like makt-maktx ,

werks like mard-werks ,

labst like mard-labst ,

End of it_1.

----

---

• S E L E C T I O N S C R E E N

----

---

SELECT-OPTIONS : S_MATNR FOR MARA-MATNR.

----

---

• S T A R T - O F - S E L E C T I O N

----

---

START-OF-SELECTION.

SELECT

A~MATNR

B~MAKTX

C~WERKS

SUM( C~LABST )

INTO TABLE IT_1

FROM ( ( mara as a inner join makt as b on

a_{matnr = b}matnr and b~spras = 'EN' )

INNER JOIN MARD AS C ON A_{MATNR = C}MATNR )

WHERE A~MATNR IN S_MATNR

GROUP BY A_{MATNR B}MAKTX C~WERKS.

SORT IT_1 BY MATNR WERKS.

*CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

• EXPORTING

• PERCENTAGE = 100

• TEXT = TEXT-I08

• EXCEPTIONS

• OTHERS = 1.

*---REATE AN EXCEL DOCUMENT--

---

CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'.

SET PROPERTY OF H_EXCEL 'Visible' = 1.

*---SET AN OBJECT FOR WORKBOOK---

CALL METHOD OF H_EXCEL 'Workbooks' = H_MAPL.

*----CREATE A WORKBOOK-----

CALL METHOD OF H_MAPL 'ADD' = h_map.

CALL METHOD OF H_EXCEL 'Cells' = H_ZL EXPORTING #1 = 1 #2 = 1.

*---CHANGE THE PROPERTY OF THE CELL--

GET PROPERTY OF H_ZL 'INTERIOR' = H_FORMAT4.

SET PROPERTY OF H_FORMAT4 'COLORINDEX' = 22.

GET PROPERTY OF H_ZL 'COLUMNS' = H_FORMAT4.

CALL METHOD OF H_FORMAT4 'AUTOFIT'.

*--FOR MATERIAL NUMBER---

SET PROPERTY OF H_ZL 'VALUE' = 'Material Number'.

call method of h_excel 'Cells' = h_zl exporting #1 = 1 #2 = 2 .

CALL METHOD OF H_EXCEL 'COLUMNS' = H_FORMAT4.

*--FOR MATERIAL DESCRIPTION---

SET PROPERTY OF H_ZL 'VALUE' = 'Material Description'.

call method of h_excel 'Cells' = h_zl exporting #1 = 1 #2 = 3 .

CALL METHOD OF H_EXCEL 'COLUMNS' = H_FORMAT4.

*--FOR PLANT---

SET PROPERTY OF H_ZL 'VALUE' = 'Plant'.

call method of h_excel 'Cells' = h_zl exporting #1 = 1 #2 = 4 .

*--FOR STOCK----

SET PROPERTY OF H_ZL 'VALUE' = 'Stock'.

*----DECLEARING A VARIABLE----

data : v_t type i.

clear v_t.

----

---

• P R I N T T H E O U T P U T

----

---

LOOP AT IT_1.

v_t = sy-tabix + 1.

*--for columns format---

CALL METHOD OF H_EXCEL 'COLUMNS' = H_FORMAT1.

SET PROPERTY OF H_FORMAT1 'ColumnWidth' = 20.

call method of h_excel 'Cells' = h_zl exporting #1 = v_t #2 = 1.

*--for material number---

SET PROPERTY OF H_ZL 'VALUE' = it_1-matnr.

CALL METHOD OF H_EXCEL 'COLUMNS' = H_FORMAT1.

call method of h_excel 'Cells' = h_zl exporting #1 = v_t #2 = 2 .

*--for material description----

SET PROPERTY OF H_ZL 'VALUE' = it_1-maktx.

call method of h_excel 'Cells' = h_zl exporting #1 = v_t #2 = 3 .

*--for plants---

SET PROPERTY OF H_ZL 'VALUE' = it_1-werks.

call method of h_excel 'Cells' = h_zl exporting #1 = v_t #2 = 4 .

*--for stock--

SET PROPERTY OF H_ZL 'VALUE' = it_1-labst.

ENDLOOP.

*-------METHOD FOR SAVE THE BOOK -

---

call method of h_excel 'SAVE' = H_FORMAT4.

*----DECLARE WHERE ITS SAVE--

---

SET PROPERTY OF H_FORMAT4 'FILENAME' = 'C:\TESTS.XLS'..

do reward if usefull

vijay