topic Re: data transfer to excel sheet in Application Development and Automation Discussions

data transfer to excel sheet

Former Member — Thu, 03 May 2007 12:14:39 GMT

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.

Re: data transfer to excel sheet

Former Member — Thu, 03 May 2007 12:18:35 GMT

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

Re: data transfer to excel sheet

Former Member — Thu, 03 May 2007 12:18:45 GMT

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

Re: data transfer to excel sheet

Former Member — Thu, 03 May 2007 12:19:16 GMT

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

Re: data transfer to excel sheet

Former Member — Thu, 03 May 2007 12:23:24 GMT

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

Re: data transfer to excel sheet

Former Member — Thu, 03 May 2007 12:26:46 GMT

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.

Re: data transfer to excel sheet

Former Member — Thu, 03 May 2007 12:26:58 GMT

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

Re: data transfer to excel sheet

Former Member — Thu, 03 May 2007 12:50:00 GMT

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