Skip to Content
0
Jun 13, 2007 at 09:38 AM

DOWNLOADING DATA TO EXCEL

53 Views

HI,

I HAVE USED ALV DYNAMIC TABLE IN MY REPORT , BY USING "CREAT DYNAMIC TABLE". WHEN I AM DOWNLOADING DATA TO EXCEL IT IS NOT PROPERLY DOWNLOADING DATA TO EXCEL.

FOLLOWING IS THE REPORT.

SO PLEASE HELP ME, AS SOON AS POSSIBLE.

&----


*& Report ZCRPT_SD_GD_62

*&

&----


*&

*&

&----


REPORT zcrpt_sd_gd_62san.

TYPE-POOLS : slis,truxs.

TABLES : s906.

*******************selection-sreen*********************************

SELECTION-SCREEN : BEGIN OF BLOCK b1 WITH FRAME TITLE text-001 .

PARAMETER : p_vari LIKE disvariant-variant .

SELECTION-SCREEN : END OF BLOCK b1 .

SELECTION-SCREEN : BEGIN OF BLOCK b2 WITH FRAME TITLE text-002 .

SELECT-OPTIONS : s_date FOR s906-spmon NO-EXTENSION.

SELECTION-SCREEN : END OF BLOCK b2 .

******************end of selection-screen**************************

********************declaration of data*****************************

DATA: BEGIN OF it_vbrk OCCURS 0,

vbeln LIKE vbrk-vbeln,

fkdat LIKE vbrk-fkdat,

netwr LIKE vbrk-netwr,

land1 LIKE vbrk-land1,

knumv LIKE vbrk-knumv,

kurrf LIKE vbrk-kurrf,

WAERK LIKE VBRK-WAERK,

month(02),

year(04),

END OF it_vbrk.

DATA: BEGIN OF it_final OCCURS 0,

netwr LIKE vbrk-netwr,

land1 LIKE vbrk-land1,

month(02),

year(04),

netwr1 LIKE vbrk-netwr,

END OF it_final.

DATA: BEGIN OF it_total OCCURS 0,

netwr LIKE vbrk-netwr,

land1 LIKE vbrk-land1,

END OF it_total.

DATA: BEGIN OF it_land OCCURS 0,

land1 LIKE t005t-land1,

landx LIKE t005t-landx,

netwr LIKE vbrk-netwr,

END OF it_land.

DATA : BEGIN OF it_konv OCCURS 0,

vbeln LIKE vbrp-vbeln,

posnr LIKE vbrp-posnr,

kschl LIKE konv-kschl,

kbetr LIKE konv-kbetr,

kwert LIKE konv-kwert,

knumv LIKE konv-knumv,

END OF it_konv.

DATA : n TYPE i VALUE 4.

DATA : a TYPE i VALUE 2,

b TYPE i VALUE 2.

DATA : l_vari LIKE disvariant.

DATA : gt_fieldcat TYPE lvc_t_fcat.

DATA : gp_table TYPE REF TO data.

FIELD-SYMBOLS : <gt_table> TYPE table.

DATA: ok_code LIKE sy-ucomm,

g_container TYPE scrfname VALUE 'BCALV_GRID_DEMO_0100_CONT1',

grid1 TYPE REF TO cl_gui_alv_grid,

g_custom_container TYPE REF TO cl_gui_custom_container.

*- Declarations For F4 Help

DATA: BEGIN OF return_tab OCCURS 10.

INCLUDE STRUCTURE ddshretval.

DATA: END OF return_tab.

**************end of declaration of data*****************************

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

  • DECLARATION FOR ALV

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

DATA: fieldtab TYPE slis_t_fieldcat_alv,

heading TYPE slis_t_listheader,

layout TYPE slis_layout_alv,

events TYPE slis_t_event,

repname LIKE sy-repid,

f2code LIKE sy-ucomm VALUE '&ETA',

g_save(1) TYPE c,

g_variant LIKE disvariant,

gx_variant LIKE disvariant,

g_exit(1) TYPE c.

DATA: keyinfo TYPE slis_keyinfo_alv.

CONSTANTS: formname_top_of_page TYPE slis_formname VALUE 'TOP_OF_PAGE'.

DATA: alv_print TYPE slis_print_alv.

DATA: alv_detail_func(30).

DATA: repid LIKE sy-repid.

repid = sy-repid.

DATA: alv_fieldcat TYPE slis_t_fieldcat_alv ,

lt_alv_cat TYPE TABLE OF lvc_s_fcat ,

it_fieldcat LIKE LINE OF lt_alv_cat ,

fieldcat TYPE slis_fieldcat_alv,

l_fieldcat TYPE slis_fieldcat_alv,

gt_events TYPE slis_t_event ,

x_layout TYPE lvc_s_layo,

gt_list_top_of_page TYPE slis_t_listheader.

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

  • INITIALIZATION

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

INITIALIZATION.

repname = sy-repid.

PERFORM initialize_variant.

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

  • AT SELECTION-SCREEN

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

AT SELECTION-SCREEN.

IF s_date-low IS INITIAL OR s_date-high IS INITIAL.

MESSAGE 'Please Enter the Month Range' TYPE 'E'.

ENDIF.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_vari.

PERFORM f4_for_variant.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR s_date-low.

PERFORM f4_spmon_low.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR s_date-high.

PERFORM f4_spmon_high.

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

  • START OF SELECTION

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

START-OF-SELECTION.

repname = sy-repid.

DATA : start_date TYPE datum,

end_date TYPE datum,

month TYPE p,

d_date LIKE sy-datum,

d_date1 LIKE sy-datum,

e_date(12).

DATA : msg TYPE REF TO if_hrpa_message_handler.

CONCATENATE s_date-low '01' INTO d_date1.

start_date = d_date1.

CONCATENATE s_date-high '01' INTO d_date.

CALL FUNCTION 'LAST_DAY_OF_MONTHS'

EXPORTING

day_in = d_date

IMPORTING

last_day_of_month = end_date

  • EXCEPTIONS

  • DAY_IN_NO_DATE = 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.

CALL FUNCTION 'HR_ECM_GET_NUMBER_OF_MONTHS'

EXPORTING

begda = start_date

endda = end_date

days_min = '00'

message_handler = msg

IMPORTING

months = month.

  • IS_OK = .

n = month + 1.

PERFORM fieldcat_build.

CALL METHOD cl_alv_table_create=>create_dynamic_table

EXPORTING

it_fieldcatalog = gt_fieldcat

IMPORTING

ep_table = gp_table.

ASSIGN gp_table->* TO <gt_table>.

PERFORM get_data.

PERFORM eventtab_build USING gt_events .

PERFORM build_layout USING x_layout .

IF g_save EQ space .

PERFORM initialize_variant .

ENDIF .

  • PERFORM format_data .

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

  • END OF SELECTION

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

END-OF-SELECTION.

  • CHECK NOT it_final[] IS INITIAL.

PERFORM alv_display .

&----


*& Form get_data

&----


  • text

----


  • --> p1 text

  • <-- p2 text

----


FORM get_data .

FIELD-SYMBOLS : <ls_table>.

FIELD-SYMBOLS : <l_field> .

FIELD-SYMBOLS : <wa_final> LIKE it_final .

FIELD-SYMBOLS : <f_cnt> .

FIELD-SYMBOLS : <l_country> .

FIELD-SYMBOLS : <l_netwr> .

FIELD-SYMBOLS : <l_netwr1> .

FIELD-SYMBOLS : <l_netwr2> .

FIELD-SYMBOLS : <l_netwr3> .

DATA : frm_date LIKE vbrk-fkdat .

DATA : date2(6). "LIKE sy-datum .

DATA : cdate(12) .

DATA : sel_dat(8) ,

f_date LIKE sy-datum,

temp type p decimals 2,

temp1(15).

RANGES : r_date FOR sy-datum.

CONCATENATE s_date-low '01' INTO d_date.

CONCATENATE s_date-high '01' INTO d_date1.

CALL FUNCTION 'LAST_DAY_OF_MONTHS'

EXPORTING

day_in = d_date1

IMPORTING

last_day_of_month = f_date

  • EXCEPTIONS

  • DAY_IN_NO_DATE = 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.

r_date-sign = 'I'.

r_date-option = 'BT'.

r_date-low = d_date.

r_date-high = f_date.

APPEND r_date.

SELECT vbeln fkdat land1 knumv kurrf waerk FROM vbrk

INTO CORRESPONDING FIELDS OF TABLE it_vbrk

WHERE fkdat IN r_date

AND vtweg = 90.

  • AND LAND1 = 'GB'.

SELECT land1 landx FROM t005t INTO TABLE it_land

FOR ALL ENTRIES IN it_vbrk

WHERE land1 EQ it_vbrk-land1

AND spras EQ sy-langu.

ASSIGN it_final TO <wa_final>.

LOOP AT it_vbrk.

it_vbrk-month = it_vbrk-fkdat+4(2).

it_vbrk-year = it_vbrk-fkdat+0(4).

MODIFY it_vbrk TRANSPORTING month year.

ENDLOOP.

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

LOOP AT it_vbrk.

MOVE : it_vbrk-vbeln TO it_konv-vbeln.

SELECT kschl kbetr kwert knumv INTO (it_konv-kschl,it_konv-kbetr, it_konv-kwert, it_konv-knumv)

FROM konv

WHERE knumv = it_vbrk-knumv

AND kschl IN ('ZFOB', 'ZFBN')

AND kinak NE 'X'.

COLLECT it_konv.

ENDSELECT.

ENDLOOP.

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

******For fob ****************

LOOP AT it_vbrk.

IF IT_VBRK-LAND1 = 'NP'.

READ TABLE it_konv WITH KEY knumv = it_vbrk-knumv

vbeln = it_vbrk-vbeln

kschl = 'ZFBN'.

IT_VBRK-NETWR = IT_KONV-KWERT.

ELSE.

READ TABLE it_konv WITH KEY knumv = it_vbrk-knumv

vbeln = it_vbrk-vbeln

kschl = 'ZFOB'.

IF sy-subrc EQ 0.

IF it_vbrk-waerk = 'INR' OR it_vbrk-waerk = 'GBP'.

ELSE.

it_konv-kwert = it_konv-kwert / 100.

ENDIF.

IT_VBRK-NETWR = IT_KONV-KWERT.

ENDIF.

ENDIF.

it_vbrk-netwr = it_vbrk-netwr * it_vbrk-kurrf.

it_vbrk-netwr = it_vbrk-netwr / 100000.

MODIFY it_vbrk TRANSPORTING netwr.

ENDLOOP.

LOOP AT it_vbrk.

it_final-netwr = it_vbrk-netwr.

it_final-land1 = it_vbrk-land1.

it_final-month = it_vbrk-month.

it_final-year = it_vbrk-year.

COLLECT it_final.

ENDLOOP.

CLEAR it_final.

LOOP AT it_final.

it_total-land1 = it_final-land1.

it_total-netwr = it_final-netwr.

COLLECT it_total.

CLEAR it_total.

ENDLOOP.

LOOP AT it_land.

READ TABLE it_total WITH KEY land1 = it_land-land1.

IF sy-subrc EQ 0 .

it_land-netwr = it_total-netwr.

MODIFY it_land TRANSPORTING netwr.

ENDIF.

CLEAR it_land.

ENDLOOP.

SORT IT_FINAL BY LAND1.

LOOP AT it_final.

ASSIGN LOCAL COPY OF INITIAL LINE OF <gt_table> TO <ls_table>.

READ TABLE it_land WITH KEY land1 = it_final-land1.

IF sy-subrc EQ 0 .

ASSIGN COMPONENT 'LANDX' OF STRUCTURE <ls_table> TO <l_country> .

<l_country> = it_land-landx .

ENDIF .

ASSIGN COMPONENT 'NETWR' OF STRUCTURE <ls_table> TO <l_netwr2>.

<l_netwr2> = it_land-netwr.

CONCATENATE it_final-year it_final-month INTO date2.

MOVE date2 TO sel_dat .

ASSIGN COMPONENT 'NETWR' OF STRUCTURE <wa_final> TO <l_netwr>.

ASSIGN COMPONENT sel_dat OF STRUCTURE <ls_table> TO <l_netwr1>.

<l_netwr1> = <l_netwr>.

  • UNASSIGN : <l_country> ,

  • <f_cnt> ,

  • <ls_table> .

COLLECT <ls_table> INTO <gt_table> .

CLEAR <ls_table>.

*

  • CALL FUNCTION 'GUI_DOWNLOAD'

  • EXPORTING

  • filename = 'C:\san.xls'

  • FILETYPE = 'ASC'

  • WRITE_FIELD_SEPARATOR = 'X'

  • IMPORTING

  • FILELENGTH =

  • TABLES

  • data_tab = <gt_table>

    • FIELDNAMES = gt_fieldcat

  • 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

  • .

  • IF sy-subrc <> 0.

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

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

  • ENDIF.

  • ENDLOOP .

    ENDFORM. "get_data

    &----


    *& Form alv_display

    &----


    • text

    ----


    • --> p1 text

    • <-- p2 text

    ----


    FORM alv_display .

    CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'

    EXPORTING

    i_callback_program = repid

    is_layout_lvc = x_layout

    it_fieldcat_lvc = gt_fieldcat

    i_callback_top_of_page = 'TOP_OF_PAGE'

    i_default = 'X'

    i_save = g_save

    is_variant = g_variant

    it_events = events[]

    TABLES

    t_outtab = <gt_table>

    EXCEPTIONS

    program_error = 1

    OTHERS = 2.

    ENDFORM. " alv_display

    &----


    *& Form initialize_variant

    &----


    • text

    ----


    • --> p1 text

    • <-- p2 text

    ----


    FORM initialize_variant .

    g_save = 'A'.

    CLEAR g_variant.

    g_variant-report = repname.

    gx_variant = g_variant.

    CALL FUNCTION 'REUSE_ALV_VARIANT_DEFAULT_GET'

    EXPORTING

    i_save = g_save

    CHANGING

    cs_variant = gx_variant

    EXCEPTIONS

    not_found = 2.

    IF sy-subrc = 0.

    p_vari = gx_variant-variant.

    ENDIF.

    layout-get_selinfos = 'X'.

    layout-group_change_edit = 'X'.

    alv_print-no_print_selinfos = 'X'.

    alv_print-no_coverpage = 'X'.

    alv_print-no_print_listinfos = 'X'.

    ENDFORM. " initialize_variant

    &----


    *& Form f4_for_variant

    &----


    • text

    ----


    • --> p1 text

    • <-- p2 text

    ----


    FORM f4_for_variant .

    CALL FUNCTION 'REUSE_ALV_VARIANT_F4'

    EXPORTING

    is_variant = g_variant

    i_save = g_save

    IMPORTING

    e_exit = g_exit

    es_variant = gx_variant

    EXCEPTIONS

    not_found = 2.

    IF sy-subrc = 2.

    MESSAGE ID sy-msgid TYPE 'S' NUMBER sy-msgno

    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

    ELSE.

    IF g_exit = space.

    p_vari = gx_variant-variant.

    ENDIF.

    ENDIF.

    ENDFORM. " f4_for_variant

    &----


    *& Form fieldcat_build

    &----


    • text

    ----


    • --> p1 text

    • <-- p2 text

    ----


    FORM fieldcat_build .

    DATA : ls_fieldcat TYPE lvc_s_fcat.

    DATA : coltext TYPE lvc_s_fcat-coltext .

    DATA : mon_no(2).

    DATA : date1(06).

    DATA : dat_disp(12) ,

    dat_field(12) ,

    mm(02),

    mon_char(8).

    date1 = s_date-low.

    DO 3 TIMES.

    • IF sy-index EQ '1' .

    CASE sy-index.

    WHEN '1'.

    ls_fieldcat-tabname = 'TOO5T' .

    ls_fieldcat-fieldname = 'LANDX' .

    ls_fieldcat-coltext = 'COUNTRY' .

    ls_fieldcat-outputlen = 15.

    WHEN '2'.

    ls_fieldcat-tabname = 'VBRK' .

    ls_fieldcat-fieldname = 'NETWR' .

    ls_fieldcat-coltext = 'Total' .

    ls_fieldcat-outputlen = 15.

    WHEN '3'.

    ls_fieldcat-tabname = 'KONV' .

    ls_fieldcat-fieldname = 'KWERT' .

    ls_fieldcat-coltext = 'PREV. Total' .

    ls_fieldcat-outputlen = 15.

    *

    ENDCASE.

    APPEND ls_fieldcat TO gt_fieldcat.

    ENDDO.

    DO n TIMES.

    CLEAR dat_disp .

    CALL FUNCTION 'CONVERSION_EXIT_PERI6_OUTPUT'

    EXPORTING

    input = date1

    IMPORTING

    output = dat_disp.

    CLEAR : ls_fieldcat .

    mm = dat_disp+0(2).

    CASE mm.

    WHEN '01'.

    mon_char = 'JAN 2007'.

    WHEN '02'.

    mon_char = 'FEB 2007'.

    WHEN '03'.

    mon_char = 'MAR 2007'.

    WHEN '04'.

    mon_char = 'APR 2007'.

    WHEN '05'.

    mon_char = 'MAY 2007'.

    WHEN '06'.

    mon_char = 'JUN 2007'.

    WHEN '07'.

    mon_char = 'JUL 2007'.

    WHEN '08'.

    mon_char = 'AUG 2007'.

    WHEN '09'.

    mon_char = 'SEP 2007'.

    WHEN '10'.

    mon_char = 'OCT 2007'.

    WHEN '11'.

    mon_char = 'NOV 2007'.

    WHEN '12'.

    mon_char = 'DEC 2007'.

    ENDCASE.

    ls_fieldcat-fieldname = date1.

    ls_fieldcat-datatype = 'CURR'.

    ls_fieldcat-coltext = mon_char.

    APPEND ls_fieldcat TO gt_fieldcat.

    IF date1+4(2) = 12.

    date10(4) = date10(4) + 1.

    CONCATENATE date1+0(4) '01' INTO date1.

    ELSE.

    date1 = date1 + 1 .

    ENDIF.

    ENDDO.

    ENDFORM. " fieldcat_build

    &----


    *& Form eventtab_build

    &----


    • text

    ----


    • -->LT_EVENTS text

    ----


    FORM eventtab_build USING lt_events TYPE slis_t_event.

    CONSTANTS:

    gc_formname_top_of_page TYPE slis_formname VALUE 'TOP_OF_PAGE'.

    DATA: ls_event TYPE slis_alv_event.

    CALL FUNCTION 'REUSE_ALV_EVENTS_GET'

    EXPORTING

    i_list_type = 0

    IMPORTING

    et_events = lt_events.

    READ TABLE lt_events WITH KEY name = slis_ev_top_of_page

    INTO ls_event.

    IF sy-subrc = 0.

    MOVE gc_formname_top_of_page TO ls_event-form.

    APPEND ls_event TO lt_events.

    ENDIF.

    ENDFORM. "eventtab_build

    &----


    *& Form build_layout

    &----


    • text

    ----


    • -->P_X_LAYOUT text

    ----


    FORM build_layout USING p_layout TYPE lvc_s_layo.

    p_layout-zebra = 'X'.

    ENDFORM. " build_layout

    &----


    *& Form f4_spmon_low

    &----


    • text

    ----


    • -->P_S_SPMON_LOW text

    ----


    FORM f4_spmon_low." using p_s_spmon_low.

    DATA: mf_returncode LIKE sy-subrc,

    mf_monat LIKE isellist-month,

    mf_hlp_repid LIKE sy-repid.

    mf_monat = sy-datum+0(6).

    CALL FUNCTION 'POPUP_TO_SELECT_MONTH'

    EXPORTING

    actual_month = mf_monat

    • FACTORY_CALENDAR = ' '

    • HOLIDAY_CALENDAR = ' '

    • LANGUAGE = SY-LANGU

    • START_COLUMN = 8

    • START_ROW = 5

    IMPORTING

    selected_month = mf_monat

    return_code = mf_returncode

    EXCEPTIONS

    factory_calendar_not_found = 1

    holiday_calendar_not_found = 2

    month_not_found = 3

    OTHERS = 4

    .

    IF sy-subrc = 0 AND mf_returncode = 0.

    s_date-low = mf_monat.

    ENDIF.

    ENDFORM. " f4_spmon_low

    &----


    *& Form f4_spmon_high

    &----


    • text

    ----


    • --> p1 text

    • <-- p2 text

    ----


    FORM f4_spmon_high .

    DATA: mf_returncode1 LIKE sy-subrc,

    mf_monat1 LIKE isellist-month,

    mf_hlp_repid1 LIKE sy-repid.

    mf_monat1 = sy-datum+0(6).

    CALL FUNCTION 'POPUP_TO_SELECT_MONTH'

    EXPORTING

    actual_month = mf_monat1

    • FACTORY_CALENDAR = ' '

    • HOLIDAY_CALENDAR = ' '

    • LANGUAGE = SY-LANGU

    • START_COLUMN = 8

    • START_ROW = 5

    IMPORTING

    selected_month = mf_monat1

    return_code = mf_returncode1

    EXCEPTIONS

    factory_calendar_not_found = 1

    holiday_calendar_not_found = 2

    month_not_found = 3

    OTHERS = 4

    .

    IF sy-subrc = 0 AND mf_returncode1 = 0.

    s_date-high = mf_monat1.

    ENDIF.

    ENDFORM. " f4_spmon_high

    ----


    • FORM TOP_OF_PAGE *

    ----


    FORM top_of_page .

    DATA: c_date(10) ,

    c_time(8) .

    DATA: line TYPE slis_listheader ,

    rs_variant TYPE disvariant .

    DATA : itab TYPE line OCCURS 0 WITH HEADER LINE.

    DATA: hline TYPE slis_listheader,

    heading TYPE slis_t_listheader.

    CLEAR heading[] .

    line-typ = 'S' .

    line-info = ' COUNTRYWISE-MONTHWISE EXPORT SALES REPORT ' .

    APPEND line TO heading .

    line-typ = 'S' .

    WRITE: sy-datum TO line-info .

    CONCATENATE 'Rundate :' line-info INTO line-info

    SEPARATED BY space .

    APPEND line TO heading .

    CLEAR line .

    CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE'

    EXPORTING

    it_list_commentary = heading.

    ENDFORM . "top_of_page