down load to excel sheet

Former Member — Tue, 18 Sep 2007 14:22:18 GMT

hi experts,

I have downloaded to excel sheet i got in one row the data

my requirement is to get that in column can any one guide me....

thanks in advance

Re: down load to excel sheet

Former Member — Tue, 18 Sep 2007 14:33:40 GMT

You can use FM : EXCEL_OLE_STANDARD_DAT and check the below code :

Tables

tables : zsdcarton,

ztruck,

ztruckstatus.

Constants

constants: c_werks(4) type c value '1000'.

Internal table for Ztruck

data : begin of i_ztruck occurs 0,

werks like ztruck-werks," Plant

vdatu like ztruck-vdatu, " Delivery date

ZZTRKNO like ztruck-ZZTRKNO, " Route #

ZZSHIPTIME like ztruck-ZZSHIPTIME, " Dispatch time

ZCLTIME like ztruck-ZCLTIME, " Truck close time

end of i_ztruck.

Internal table for ZSDCARTON

data : begin of i_zsdcarton occurs 0,

lateflag like zsdcarton-lateflag,

zzslot like zsdcarton-zzslot,

status like zsdcarton-status,

end of i_zsdcarton.

Internal table for Final output

data : begin of i_final occurs 0,

vdatu like ztruck-vdatu, " Delivery Date

ZZTRKNO like ztruck-ZZTRKNO, " Route #

ZZSHIPTIME like ztruck-ZZSHIPTIME, " Dispatch time

ZCLTIME like ztruck-ZCLTIME, " Truck close time

load_compl_c(5) type n , " Load Complted Time

status_s_total(5) type n, " No Scan/Load

status_l_total(5) type n, " Late Scan

end of i_final.

Internal Table XLS File Header

DATA: BEGIN OF i_head OCCURS 0,

field(30) TYPE c,

END OF i_head.

Variables

status couter

data: status_total(5) type n,

no scan / no load

status_s(3) type n,

status_s_total(5) type n,

good

status_g(3) type n,

status_g_total(5) type n,

late

status_l(3) type n,

status_l_total(5) type n,

manual

status_m(3) type n,

status_m_total(5) type n,

alternative

status_a(3) type n,

status_a_total(5) type n.

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

S E L E C T I O N - S C R E E N *************************

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

selection-screen : begin of block blk with frame title text-001.

select-options : p_vdatu for zsdcarton-vdatu obligatory

default sy-datum.

selection-screen : end of block blk .

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

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

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

start-of-selection.

Get the data from ZTRUCK and

perform get_data_tables.

Down load the data into XLS file

perform get_download.

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

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

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

end-of-selection.

&----

*& Form get_data_tables

&----

----

FORM get_data_tables.

data : lv_time like sy-uzeit.

select werks

vdatu

zztrkno

zzshiptime

zcltime from ztruck into table i_ztruck

where werks = c_werks

and vdatu in p_vdatu.

if sy-subrc ne 0.

message e000(zwave) with 'No data found for given current date'.

endif.

loop at i_ztruck.

refresh : i_zsdcarton.

clear : i_zsdcarton,

lv_time.

Get the Scan Status for Every route

select lateflag zzslot status from zsdcarton into table i_zsdcarton

where werks = i_ztruck-werks

and vdatu = i_ztruck-vdatu

and zztrkno = i_ztruck-zztrkno.

if sy-subrc eq 0.

loop at i_zsdcarton.

case i_zsdcarton-lateflag.

late cartons

when 'X'.

late and never loaded

if i_zsdcarton-zzslot = space.

add 1 to status_s.

else.

add 1 to status_l.

endif.

all other exceptions

when space.

check if scanned

case i_zsdcarton-zzslot.

good scan

when 'S'.

add 1 to status_g.

never scanned

when space.

if i_zsdcarton-status = space.

no scan

add 1 to status_s.

else.

no load -> no scan

add 1 to status_s.

endif.

manual scanned

when 'M'.

if i_zsdcarton-status = 'M'.

add 1 to status_g.

elseif i_zsdcarton-status = 'E'.

exceprtion -> manual

add 1 to status_g.

endif.

endcase.

add totals

add status_g to status_g_total.

Late Scan

add status_l to status_l_total.

add status_a to status_a_total.

No Scan and Load

add status_s to status_s_total.

clear : status_g,

status_l,

status_a,

status_s.

endloop.

else.

continue.

endif.

Get the Load Complete Time

select single uzeit from ztruckstatus into lv_time

where werks = i_ztruck-werks

and lgnum = '100'

and vdatu = i_ztruck-vdatu

and zztrkno = i_ztruck-zztrkno

and tstat = 'L'.

if sy-subrc eq 0.

write: lv_time(5)

to i_final-load_compl_c using edit mask '__:__'.

endif.

Delivery Date

i_final-vdatu = i_ztruck-vdatu.

Route #

i_final-zztrkno = i_ztruck-zztrkno.

Dispach time

i_final-zzshiptime = i_ztruck-zzshiptime.

Truck Close Time

i_final-zcltime = i_ztruck-zcltime.

No Scan/ Load

i_final-status_s_total = status_s_total .

Late Scan

i_final-status_l_total = status_l_total.

append i_final.

clear : status_g_total,

status_l_total,

status_a_total,

status_s_total,

lv_time,

i_final.

endloop.

ENDFORM. " get_data_tables

&----

*& Form get_download

&----

Download the data

----

FORM get_download.

data : lv_file like rlgrap-filename,

lv_date(8) type c.

lv_date = sy-datum.

concatenate 'C:/Truckload' lv_date into lv_file.

Fill the Header Values
Delivery Date

i_head-field = 'Delivery Date'.

append i_head.

Route #

i_head-field = 'Route'.

APPEND i_head.

Dispatch Time

i_head-field = 'Dispatch Time'.

append i_head.

Closing Time

i_head-field = 'Closing Time'.

append i_head.

Load Completed Time

i_head-field = 'Load Completed Time'.

append i_head.

No Scan/Load

i_head-field = 'No Scan/Load'.

append i_head.

Late Scan

i_head-field = 'Late Scan'.

append i_head.

CALL FUNCTION 'EXCEL_OLE_STANDARD_DAT'

EXPORTING

FILE_NAME = lv_file

CREATE_PIVOT = 0

DATA_SHEET_NAME = 'TruckLoad'

PIVOT_SHEET_NAME = ' '
PASSWORD = ' '
PASSWORD_OPTION = 0

TABLES

PIVOT_FIELD_TAB =

DATA_TAB = i_final

FIELDNAMES = i_head

EXCEPTIONS

FILE_NOT_EXIST = 1

FILENAME_EXPECTED = 2

COMMUNICATION_ERROR = 3

OLE_OBJECT_METHOD_ERROR = 4

OLE_OBJECT_PROPERTY_ERROR = 5

INVALID_FILENAME = 6

INVALID_PIVOT_FIELDS = 7

DOWNLOAD_PROBLEM = 8

OTHERS = 9

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

ENDFORM. " get_download

Thanks

Seshu

topic Re: down load to excel sheet in Application Development and Automation Discussions

down load to excel sheet

Re: down load to excel sheet

Re: down load to excel sheet

Re: down load to excel sheet

Re: down load to excel sheet

Re: down load to excel sheet