topic Re: OLE2 issue in converting excel macro to abap code in Application Development and Automation Discussions

OLE2 issue in converting excel macro to abap code

Former Member — Mon, 06 May 2013 05:50:58 GMT

Hello Folks,

Could you please assist me converting below Marcro to ABAP code.

Here's my VBA Code (Excel macro code):

Sub Macro1()

' Macro1 Macro

'
Cells.Select
Cells.EntireColumn.AutoFit
End Sub

Regards

Aradhya.J.M.

Re: OLE2 issue in converting excel macro to abap code

Former Member — Mon, 06 May 2013 06:31:18 GMT

Hi Aradhya,

DATA: H_EXCEL TYPE OLE2_OBJECT,

GS_CELL1 TYPE OLE2_OBJECT,

GS_CELL2 TYPE OLE2_OBJECT,

GS_CELLS TYPE OLE2_OBJECT.

CALL METHOD OF H_EXCEL 'Cells' = GS_CELL1

EXPORTING

#1 = 1 " 1st Row

#2 = 1. " 1st Column

==============================================================

CALL METHOD OF H_EXCEL 'Cells' = GS_CELL2

EXPORTING

#1 = 5 " 5th Row

#2 = 4. " 4th Column

==========================================================

CALL METHOD OF H_EXCEL 'Range' = GS_CELLS " Select Range Area using above row and column

EXPORTING

#1 = GS_CELL1

#2 = GS_CELL2.

CALL METHOD OF GS_CELLS 'Select' . " Select

========================================================

CALL METHOD OF H_EXCEL 'AutoFit'.

Regard's

Smruti

Re: OLE2 issue in converting excel macro to abap code

Former Member — Mon, 06 May 2013 12:02:32 GMT

Hello Smruti,

Thanks for your reply,It worked for me.

I have one more query on the same,How can i send OLE generated object as attachement through an email.I am checking various posts insisting use converting it to SOLIX.

Re: OLE2 issue in converting excel macro to abap code

Former Member — Tue, 07 May 2013 04:48:12 GMT

Hi Aradhya,

First download your OLE generated object into Presentation Server(i.e. particular location) , then with the help of this FMs ""SO_DOCUMENT_SEND_API1" or "SO_NEW_DOCUMENT_SEND_API1" you can send Excel Sheet attached to your respective mail id . Search in SCN.SAP.COM lot of Sample code you can find code and discussion on this FMs .

Regard's

Smruti

Re: OLE2 issue in converting excel macro to abap code

krishna_k19 — Tue, 07 May 2013 04:57:14 GMT

Hi smruti,

is there any way to send a mail , in body can we attach text boxes.(attachment i know,but i'm asking in body).

Please let me know if you know..

Regards,

Krishna

Re: OLE2 issue in converting excel macro to abap code

Former Member — Tue, 07 May 2013 06:17:15 GMT

Hello smruti,

Thanks for your reply,

I tried using FM SO_DOCUMENT_SEND_API1,problem is i am losing the format which was establisted using OLE. I am still searching for some FM which sends email without disturbing the format of excel.

Regrads

Aradhya.J.M.

Re: OLE2 issue in converting excel macro to abap code

Former Member — Tue, 07 May 2013 07:03:09 GMT

Hi Aradhya,

is it ur Unicode System ? if "Yes" then Check this SAP Sample Standard Program using OOABAP ,

BCS_EXAMPLE_7 . If this Program getting any error , Please check this Notes:

Note 1151258 - Error when sending Excel attachments

Note 1844477 - "Error occurred during transmission - return code: <XERR_BCS>" with report BCS_EXAMPLE_7

Regard's

Smruti

Re: OLE2 issue in converting excel macro to abap code

Former Member — Mon, 27 May 2013 10:26:51 GMT

Refer this,

*&---------------------------------------------------------------------*
*& Report ZBC_SHA_SALES_INFO_IN_EXCEL
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT zbc_sha_sales_info_in_excel.
INCLUDE <icon>.
* EXCEL sheet using OLE automation.
INCLUDE ole2incl.
INCLUDE excel__c.
*----------------------------------------------------------------------*
* Customer Database Tables
*----------------------------------------------------------------------*
TABLES: vbak,   "Sales Header
         vbap,   "Sales Item
         likp,   "Delivery Header
         lips,   "SD document: Delivery: Item data
         vttk,   "Shipment Header
         vttp,   "Shipment Item
         vbrk,   "Billing Document: Header Data
         vbrp,   "Billing Document: Item Data
         kna1.   "General Data in Customer Master
*----------------------------------------------------------------------*
* Internal Tables
*----------------------------------------------------------------------*
DATA: BEGIN OF it_output OCCURS 10 ,
          vbeln LIKE vbak-vbeln,   "Sales Document
          audat LIKE vbak-audat,   "Document Date (Date Received/Sent)
          netwr LIKE vbak-netwr,   "Net Value of the Sales Order
          waerk LIKE vbak-waerk,   "SD Document Currency
          kunnr LIKE vbak-kunnr,   "Sold-to party
          name1 LIKE kna1-name1,                            "Name 1
          posnr LIKE vbap-posnr,   "Sales Document Item
          matnr LIKE vbap-matnr,   "material number
          maktx LIKE makt-maktx,   "material desc.
          vbeln1 LIKE likp-vbeln,   "Delivery
          tknum LIKE vttp-tknum,   "Shipment Number
          distz LIKE vttk-distz,   "Distance
          medst LIKE vttk-medst,   "Unit of measure for distance
          route LIKE vttk-route,   "Route
          vbeln2 LIKE vbrk-vbeln,   "Billing Document
          status LIKE icon-name,    "Status
          selkz(1).
DATA: END OF it_output.

DATA: BEGIN OF it_vbak OCCURS 10,
         vbeln LIKE vbak-vbeln,
         kunnr LIKE vbak-kunnr,
         waerk LIKE vbak-waerk,
         audat LIKE vbak-audat,
         netwr LIKE vbak-netwr,
       END OF it_vbak.

DATA: BEGIN OF it_vbap OCCURS 10,
         vbeln LIKE vbap-vbeln,
         posnr LIKE vbap-posnr,
         matnr LIKE vbap-matnr,
       END OF it_vbap.

DATA: BEGIN OF it_makt OCCURS 10,
         matnr LIKE makt-matnr,
         maktx LIKE makt-maktx,
       END OF it_makt.

DATA: BEGIN OF it_kna1 OCCURS 10,
         kunnr LIKE kna1-kunnr,
         name1 LIKE kna1-name1,
       END OF it_kna1.

DATA: BEGIN OF it_lips OCCURS 10,
         vbeln LIKE lips-vbeln,
         vgbel LIKE lips-vgbel,
         vgpos LIKE lips-vgpos,
         matnr LIKE lips-matnr,
       END OF it_lips.

DATA: BEGIN OF it_likp OCCURS 10,
         vbeln LIKE likp-vbeln,
       END OF it_likp.

DATA: BEGIN OF it_vttp OCCURS 10,
         vbeln LIKE vttp-vbeln,
         tknum LIKE vttp-tknum,

       END OF it_vttp.

DATA: BEGIN OF it_vbrp OCCURS 10,
         vbeln LIKE vbrp-vbeln,
         vgbel LIKE vbrp-vgbel,
       END OF it_vbrp.

DATA: BEGIN OF it_vbrk OCCURS 10,
         vbeln LIKE vbrk-vbeln,
       END OF it_vbrk.

DATA: BEGIN OF it_vttk OCCURS 10,
         tknum LIKE vttk-tknum,
         route LIKE vttk-route,
         distz LIKE vttk-distz,
         medst LIKE vttk-medst,
       END OF it_vttk.

DATA: it_output1 LIKE TABLE OF it_output WITH HEADER LINE.
*&---------------------------------------------------------------------*
*& Variables
*&---------------------------------------------------------------------*
DATA: w_excel   TYPE ole2_object, "Holds the excel application
       w_wbooks TYPE ole2_object, "Holds Work Books
       w_wbook   TYPE ole2_object, "Holds Work Book
       w_cell    TYPE ole2_object, "Holds Cell
       w_cell1   TYPE ole2_object, "Holds Cell
       w_cell2   TYPE ole2_object, "Holds Cell
       w_format TYPE ole2_object, "Object for format
       w_font    TYPE ole2_object,
       w_wsheets TYPE ole2_object, "Holds Active Sheet
       w_wsheet TYPE ole2_object, "Holds Active Sheet
       w_range   TYPE ole2_object, "To select a range
       w_interior TYPE ole2_object,
       w_formatconditions TYPE ole2_object,
       w_border TYPE ole2_object,
       w_row TYPE i,
       w_col TYPE i,
       w_size TYPE i.
*----------------------------------------------------------------------*
* ALV GRID Variables
*----------------------------------------------------------------------*
TYPE-POOLS: slis.

DATA: gd_title      TYPE lvc_title,
       gd_fieldcat   TYPE slis_t_fieldcat_alv ,
       wa_fieldcat   TYPE slis_fieldcat_alv,
       gd_layout     TYPE slis_layout_alv,
       gd_sort       TYPE slis_t_sortinfo_alv,
       wa_sort       LIKE LINE OF gd_sort,
       gd_events     TYPE slis_t_event,
       gd_extab      TYPE slis_t_extab WITH HEADER LINE,
       wa_extab      TYPE slis_t_extab WITH HEADER LINE,
       gd_event_exit TYPE slis_t_event_exit WITH HEADER LINE,
       wa_event      TYPE slis_alv_event,
       wa_event_exit TYPE slis_t_event_exit,
       gd_save(1),
       gd_repid      LIKE sy-repid,
       gd_dynnr      LIKE sy-dynnr,
       gd_param1     LIKE sy-ucomm,
       gd_top        TYPE slis_t_listheader,
       wa_top        TYPE slis_listheader.
*----------------------------------------------------------------------*
* Selection screen
*----------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECT-OPTIONS: s_vbeln FOR vbak-vbeln,      " Sales Document
                 s_vkorg FOR vbak-vkorg.      " Sales Organization
SELECTION-SCREEN END OF BLOCK b1.
*----------------------------------------------------------------------*
* MAIN PROGRAM
*----------------------------------------------------------------------*
gd_repid = sy-repid.

PERFORM fetch_assign_data.

IF it_output[] IS NOT INITIAL.

   PERFORM display_data.
ENDIF.
*&---------------------------------------------------------------------*
*&      Form fetch_assign_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM fetch_assign_data .
   SELECT vbeln
          kunnr
          waerk
          audat
          netwr FROM vbak
                INTO TABLE it_vbak
               WHERE vbeln IN s_vbeln
                 AND vkorg IN s_vkorg.

   IF it_vbak[] IS NOT INITIAL.

     SELECT vbeln
            posnr
            matnr FROM vbap
                  INTO TABLE it_vbap
                   FOR ALL ENTRIES IN it_vbak
                 WHERE vbeln EQ it_vbak-vbeln.

     SELECT kunnr
            name1 FROM kna1
                  INTO TABLE it_kna1
                   FOR ALL ENTRIES IN it_vbak
                 WHERE kunnr EQ it_vbak-kunnr.

     IF it_vbap[] IS NOT INITIAL .

       SELECT matnr
              maktx FROM makt
                    INTO TABLE it_makt
                     FOR ALL ENTRIES IN it_vbap
                   WHERE matnr EQ it_vbap-matnr.

       SELECT vbeln
              vgbel
              vgpos
              matnr FROM lips
                    INTO TABLE it_lips
                     FOR ALL ENTRIES IN it_vbap
                   WHERE vgbel EQ it_vbap-vbeln
                     AND vgpos EQ it_vbap-posnr.

       IF it_lips[] IS NOT INITIAL.

         SELECT vbeln FROM likp
                      INTO TABLE it_likp
                       FOR ALL ENTRIES IN it_lips
                     WHERE vbeln EQ it_lips-vbeln.

         SELECT vbeln
                tknum FROM vttp
                      INTO TABLE it_vttp
                       FOR ALL ENTRIES IN it_lips
                     WHERE vbeln EQ it_lips-vbeln.

         IF it_vttp[] IS NOT INITIAL .

           SELECT tknum
                  route
                  distz
                  medst FROM vttk
                        INTO TABLE it_vttk
                         FOR ALL ENTRIES IN it_vttp
                       WHERE tknum EQ it_vttp-tknum.

         ENDIF.

         SELECT vbeln
                vgbel FROM vbrp
                      INTO TABLE it_vbrp
                       FOR ALL ENTRIES IN it_lips
                     WHERE vgbel EQ it_lips-vbeln.

         IF it_vbrp[] IS NOT INITIAL.

           SELECT vbeln FROM vbrk
                        INTO TABLE it_vbrk
                         FOR ALL ENTRIES IN it_vbrp
                       WHERE vbeln EQ it_vbrp-vbeln
                         AND vbtyp EQ 'M'.
         ENDIF.    " IF it_vbrp[] IS NOT INITIAL.

       ENDIF.    " IF it_lips[] IS NOT INITIAL.

     ENDIF.    " IF it_vbap[] IS NOT INITIAL .

   ENDIF .   " IF it_vbak[] IS NOT INITIAL.

   CLEAR it_output[].

   LOOP AT it_vbap.

     CLEAR it_output.
     it_output-status = icon_red_light.
     it_output-posnr = it_vbap-posnr.                "Sales Doc. Item
     it_output-matnr = it_vbap-matnr.                "Material No.

     READ TABLE it_makt WITH KEY matnr = it_vbap-matnr.
     IF sy-subrc EQ 0.
       it_output-maktx = it_makt-maktx.              "Material Description
     ENDIF.

     READ TABLE it_vbak WITH KEY vbeln = it_vbap-vbeln.

     IF sy-subrc EQ 0.
       it_output-vbeln = it_vbak-vbeln.              "Sales doc. no.
       it_output-kunnr = it_vbak-kunnr.              "Sold To Party
       it_output-waerk = it_vbak-waerk.              "SD Doc. Currency
       it_output-audat = it_vbak-audat.              "Document Date
       it_output-netwr = it_vbak-netwr.              "Net Value

       READ TABLE it_kna1 WITH KEY kunnr = it_vbak-kunnr.
       IF sy-subrc EQ 0.
         it_output-name1 = it_kna1-name1.              "Customer
       ENDIF.

       READ TABLE it_lips WITH KEY vgbel = it_vbap-vbeln
                                   vgpos = it_vbap-posnr
                                   matnr = it_vbap-matnr.
       IF sy-subrc EQ 0.
         LOOP AT it_lips WHERE vgbel EQ it_vbap-vbeln AND
                               vgpos EQ it_vbap-posnr AND
                               matnr EQ it_vbap-matnr.

*      READ TABLE it_lips WITH KEY vgbel = it_vbap-vbeln
*                                  vgpos = it_vbap-posnr
*                                  matnr = it_vbap-matnr.

*        IF sy-subrc EQ 0.
           it_output-status = icon_yellow_light.
*       Read Delivery Header
           READ TABLE it_likp WITH KEY vbeln = it_lips-vbeln.

           IF sy-subrc EQ 0.
             it_output-vbeln1 = it_likp-vbeln.            "Delivery

**        loop at shipment item
             LOOP AT it_vttp WHERE vbeln EQ it_likp-vbeln.

*          READ TABLE it_vttp WITH KEY vbeln = it_likp-vbeln.

*          IF sy-subrc EQ 0.

*         Read Shipment Header
               READ TABLE it_vttk WITH KEY tknum = it_vttp-tknum.
               IF sy-subrc EQ 0.
                 it_output-tknum = it_vttk-tknum.           "Shipment
                 it_output-route = it_vttk-route.           "Route
                 it_output-distz = it_vttk-distz.           "Distance
                 it_output-medst = it_vttk-medst.      "Unit Of Distance
               ENDIF.

             ENDLOOP.
*          ENDIF.
**            Loop At Billing Item
             LOOP AT it_vbrp WHERE vgbel EQ it_lips-vbeln.

*          READ TABLE it_vbrp WITH KEY vgbel = it_lips-vbeln.
*
*          IF sy-subrc EQ 0.

*             Read Billing Header
               READ TABLE it_vbrk WITH KEY vbeln = it_vbrp-vbeln.

               IF sy-subrc EQ 0.
                 it_output-vbeln2 = it_vbrk-vbeln.          "Billing
               ENDIF.

               IF it_output-vbeln1 IS NOT INITIAL AND
                  it_output-vbeln2 IS NOT INITIAL.
                 it_output-status = icon_green_light.
               ENDIF.

*          ENDIF.
             ENDLOOP."Loop it_vbrp

           ENDIF.     "sy-subrc EQ 0.

           APPEND it_output.
         ENDLOOP.

       ELSE.
         APPEND it_output.
       ENDIF.
     ENDIF.
   ENDLOOP.

ENDFORM.                    " fetch_assign_data
*&---------------------------------------------------------------------*
*&      Form display_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM display_data .
   CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE'
     EXPORTING
       i_program_name     = gd_repid
       i_internal_tabname = 'IT_OUTPUT'
       i_inclname         = gd_repid
     CHANGING
       ct_fieldcat        = gd_fieldcat.
   IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
   ENDIF.

   PERFORM manipulate_events.
   PERFORM manipulate_fieldcatlog.
   PERFORM manipulate_layout.

   CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
     EXPORTING
       i_callback_program       = gd_repid
       i_callback_pf_status_set = 'PF_STATUS'
       i_callback_user_command = 'USER_COMMAND'
       is_layout                = gd_layout
       it_fieldcat              = gd_fieldcat
     TABLES
       t_outtab                 = it_output.
   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.                    " display_data
*&---------------------------------------------------------------------*
*&      Form manipulate_events
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM manipulate_events .
   CALL FUNCTION 'REUSE_ALV_EVENTS_GET'
     EXPORTING
       i_list_type     = 0
     IMPORTING
       et_events       = gd_events
     EXCEPTIONS
       list_type_wrong = 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.

   ELSE.

     READ TABLE gd_events WITH KEY name = 'USER_COMMAND' INTO wa_event.
     wa_event-form = 'USER_COMMAND1'.
     MODIFY gd_events FROM wa_event INDEX sy-tabix.

   ENDIF. "sy-subrc <> 0
ENDFORM.                    " manipulate_events
*&---------------------------------------------------------------------*
*&      Form manipulate_fieldcatlog
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM manipulate_fieldcatlog .

   LOOP AT gd_fieldcat INTO wa_fieldcat.

     CASE wa_fieldcat-fieldname.

       WHEN 'VBELN' OR                         "Sales Doc. No.
            'VBELN1' OR                         "Delivery
            'VBELN2' OR                         "Billing
            'KUNNR' OR                         "Sold To Party
            'TKNUM' OR                         "Shipment
            'MATNR'.                            "Material No.

         wa_fieldcat-hotspot = 'X'.

       WHEN 'SELKZ'.
         wa_fieldcat-no_out    = 'X'.

       WHEN 'NETWR'.
         IF sy-ucomm EQ 'EDIT'.
           wa_fieldcat-edit = 'X'.
         ENDIF.
     ENDCASE.

     MODIFY gd_fieldcat FROM wa_fieldcat.

   ENDLOOP.
ENDFORM.                    " manipulate_fieldcatlog
*&---------------------------------------------------------------------*
*&      Form manipulate_layout
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM manipulate_layout .
   gd_layout-box_fieldname     = 'SELKZ'.
   gd_layout-box_tabname       = 'IT_OUTPUT'.
   gd_layout-colwidth_optimize = 'X'.
ENDFORM.                    " manipulate_layout
*      Form user_command
*&---------------------------------------------------------------------
*
*       text
*----------------------------------------------------------------------
*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------
FORM user_command USING param1 TYPE sy-ucomm
                         param2 TYPE slis_selfield.

   CASE param1.

     WHEN '&IC1'.
       IF param2-fieldname = 'VBELN'.
         SET PARAMETER ID 'AUN' FIELD param2-value.
         CALL TRANSACTION 'VA03' AND SKIP FIRST SCREEN.
       ENDIF. "param2-fieldname = 'VBELN'.

       IF param2-fieldname = 'KUNNR'.
         SET PARAMETER ID 'KUN' FIELD param2-value.
         CALL TRANSACTION 'XD03' AND SKIP FIRST SCREEN.
       ENDIF. "param2-fieldname = 'KUNNR'.

       IF param2-fieldname = 'VBELN1'.
         SET PARAMETER ID 'VL' FIELD param2-value.
         CALL TRANSACTION 'VL03N' AND SKIP FIRST SCREEN.
       ENDIF. "param2-fieldname = 'VBELN1'.

       IF param2-fieldname = 'TKNUM'.
         SET PARAMETER ID 'TNR' FIELD param2-value.
         CALL TRANSACTION 'VT03N' AND SKIP FIRST SCREEN.
       ENDIF. "param2-fieldname = 'TKNUM'.

       IF param2-fieldname = 'VBELN2'.
         SET PARAMETER ID 'VF' FIELD param2-value.
         CALL TRANSACTION 'VF03' AND SKIP FIRST SCREEN.
       ENDIF. "param2-fieldname = 'VBELN2'.

       IF param2-fieldname = 'MATNR'.
         SET PARAMETER ID 'MAT' FIELD param2-value.
         CALL TRANSACTION 'MM03' AND SKIP FIRST SCREEN.
       ENDIF. "param2-fieldname = 'MATNR'.

     WHEN 'EDIT'.
       READ TABLE it_output WITH KEY selkz = 'X'.
       DELETE it_output[] WHERE selkz NE 'X'.
       it_output1[] = it_output[].
       PERFORM display_data.

     WHEN 'SAVE'.

       LOOP AT it_output.

         UPDATE vbak SET netwr = it_output-netwr
                    WHERE vbeln EQ it_output-vbeln
                      AND kunnr EQ it_output-kunnr.
         IF sy-subrc EQ 0.
           MESSAGE 'Value Updated Sucessfully' TYPE 'S'.
         ELSE.
           MESSAGE 'Double click on the changed row' TYPE 'I'.
         ENDIF.
       ENDLOOP.

     WHEN 'EXCEL'.

*      READ TABLE it_output WITH KEY selkz = 'X'.
*      IF sy-subrc EQ 0.
         PERFORM open_excel.
         PERFORM add_worksheet.
*      ENDIF.

     WHEN 'BACK'.
       LEAVE TO SCREEN 1000.

   ENDCASE.                                                  "param1

   param2-refresh = 'X'.
ENDFORM.                    "user_command
*&---------------------------------------------------------------------*
*&      Form pf_status
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->RT_EXTAB   text
*----------------------------------------------------------------------*
FORM pf_status USING rt_extab TYPE slis_t_extab.

   DATA: gd_index TYPE i.

   wa_extab-fcode = 'EDIT'.
   APPEND wa_extab TO gd_extab.
   CLEAR wa_extab.

   wa_extab-fcode = 'EXCEL'.
   APPEND wa_extab TO gd_extab.
   CLEAR wa_extab.

   IF sy-ucomm EQ 'EDIT' .

     SET PF-STATUS 'ZSTATUS' EXCLUDING gd_extab.

   ELSEIF sy-index EQ 0 AND sy-ucomm EQ ' '.

     SET PF-STATUS 'ZSTATUS'.

   ELSEIF sy-ucomm EQ 'BACK'.
     SET PF-STATUS 'ZSTATUS'.

   ELSEIF sy-index EQ 1 OR sy-ucomm EQ 'SAVE'.

     SET PF-STATUS 'ZSTATUS' EXCLUDING gd_extab.

   ENDIF. " IF sy-ucomm EQ 'EDIT' .
ENDFORM.                    "pf_status
*&---------------------------------------------------------------------*
*&      Form open_excel
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM open_excel .
   DATA: l_cnt TYPE i.

   CREATE OBJECT w_excel 'EXCEL.APPLICATION'.
   IF sy-subrc NE 0.
     WRITE : / 'Error CREATE OBJECT'.
   ENDIF. " IF sy-subrc NE 0.

   SET PROPERTY OF w_excel 'Visible' = 1.
   IF sy-subrc NE 0.
     WRITE : / 'Error MAKE EXCEL VISIBLE'.
   ENDIF. " IF sy-subrc NE 0.

   CALL METHOD OF w_excel 'WORKBOOKS' = w_wbooks.
   IF sy-subrc NE 0.
     WRITE : / 'Error GET WORKBOOKS'.
   ENDIF. " IF sy-subrc NE 0.

   CALL METHOD OF w_wbooks 'ADD' = w_wbook.
   IF sy-subrc NE 0.
     WRITE : / 'Error ADD TEMPLATE'.
   ENDIF. " IF sy-subrc NE 0.

   GET PROPERTY OF w_wbook 'Worksheets' = w_wsheets.
   GET PROPERTY OF w_excel 'ACTIVESHEET' = w_wsheet.
   SET PROPERTY OF w_excel 'DISPLAYALERTS' = 0.

   GET PROPERTY OF w_wsheets 'Count' = l_cnt.

   l_cnt = l_cnt - 1.

* Delete unwanted worksheets
   DO l_cnt TIMES.
     GET PROPERTY OF w_excel 'ACTIVESHEET' = w_wsheet.
     CALL METHOD OF w_wsheet 'DELETE'.
   ENDDO. " DO l_cnt TIMES.
ENDFORM.                    " open_excel
*&---------------------------------------------------------------------*
*&      Form add_worksheet
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM add_worksheet .
   GET PROPERTY OF w_excel 'ACTIVESHEET' = w_wsheet.
   CALL METHOD OF w_wsheets 'Add' = w_wsheet.

   w_size = 15.

   PERFORM fill_ccell USING 1 8 'Sales Details' 'X' '' '' '' '' 200.

   PERFORM merge_cell USING 1 8 1 9.

   PERFORM set_border USING 1 8 1 9 10 'X'.

   PERFORM set_color USING 1 8 1 9 'LV'.

   "Distance
   "Unit of measure for distance
   "Route
   "Billing Document
   "Status

   PERFORM fill_ccell
   USING 2 1 'Sales Document' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 2 'Document Date' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 3 'Net Value ' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 4 'SD Document Currency' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 5 'Sold-to party' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 6 'Name 1' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 7 'Sales Document Item' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 8 'Material number' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 9 'Material Desc.' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 10 'Delivery' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 11 'Shipment Number' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 12 'Distance' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 13 'Unit of measure for distance' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 14 'Route' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 15 'Billing Document' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 16 'Status' 'X' '' '' '' '' 000255000.

   w_row = 2.
   w_size = 10.

   LOOP AT it_output WHERE selkz EQ 'X'.
     w_row = w_row + 1.

     PERFORM fill_ccell
     USING w_row 1 it_output-vbeln 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 2 it_output-audat 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 3 it_output-netwr 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 4 it_output-waerk 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 5 it_output-kunnr 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 6 it_output-name1 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 7 it_output-posnr 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 8 it_output-matnr 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 9 it_output-maktx 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 10 it_output-vbeln1 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 11 it_output-tknum 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 12 it_output-distz 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 13 it_output-medst 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 14 it_output-route 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 15 it_output-vbeln2 'X' '' '' '' '' 111111111.

*    IF it_output-status EQ icon_red_light.
*      it_output-status = 0.
*    ELSEIF it_output-status EQ icon_yellow_light.
*      it_output-status = 1.
*    ELSEIF it_output-status EQ icon_green_light.
*      it_output-status = 2.
*    ENDIF.

*    PERFORM fill_ccell
*    USING w_row 16 it_output-status '' '' '' '' '' 111111111.

     CALL METHOD OF w_excel 'WORKBOOKS' = w_wbooks.
**
     IF it_output-status EQ icon_red_light.
       PERFORM set_color USING w_row 16 w_row 16 'RD'.
       PERFORM set_border USING w_row 16 w_row 16 10 'X'.
     ELSEIF it_output-status EQ icon_green_light.
       PERFORM set_color USING w_row 16 w_row 16 'GR'.
       PERFORM set_border USING w_row 16 w_row 16 10 'X'.
     ELSEIF it_output-status EQ icon_yellow_light.
       PERFORM set_color USING w_row 16 w_row 16 'YL'.
       PERFORM set_border USING w_row 16 w_row 16 10 'X'.
     ENDIF.

*    PERFORM fill_ccell
*    USING w_row 16 it_output-status 'X' '' '' '' '' 111111111.

   ENDLOOP.

ENDFORM.                    " add_worksheet
*&---------------------------------------------------------------------*
*&      Form fill_ccell
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_1      text
*      -->P_6      text
*      -->P_1481   text
*      -->P_1482   text
*      -->P_1483   text
*      -->P_1484   text
*      -->P_1485   text
*      -->P_1486   text
*      -->P_200    text
*----------------------------------------------------------------------*
FORM fill_ccell USING i_row TYPE i
                         i_col TYPE i
                         i_value
                         i_fontbold
                         i_digit
                         i_wraptext
                         i_horizon_align
                         i_vertical_align
                         i_color.

   DATA: l_str TYPE string.

   CALL METHOD OF w_excel 'Cells' = w_cell
     EXPORTING
     #1 = i_row
     #2 = i_col.

   PERFORM err_hdl.

   SET PROPERTY OF w_cell 'VALUE' = i_value.
   PERFORM err_hdl.

   IF i_fontbold = 'X'.
     GET PROPERTY OF w_cell 'Font' = w_font.
     SET PROPERTY OF w_font 'Bold' = 1.
     SET PROPERTY OF w_font 'Size' = w_size.
     SET PROPERTY OF w_font 'Color' = i_color.
   ENDIF. " IF i_fontbold = 'X'.

   IF NOT i_wraptext IS INITIAL.
     SET PROPERTY OF w_cell 'WrapText' = 1.
   ENDIF. " IF NOT i_wraptext IS INITIAL.

   IF NOT i_horizon_align IS INITIAL.

     IF i_horizon_align = 'L'.
       SET PROPERTY OF w_cell 'HorizontalAlignment' = xlleft.
     ELSEIF i_horizon_align = 'R'.
       SET PROPERTY OF w_cell 'HorizontalAlignment' = xlright.
     ELSEIF i_horizon_align = 'C'.
       SET PROPERTY OF w_cell 'HorizontalAlignment' = xlcenter.
     ENDIF. " IF i_horizon_align = 'L'.

   ENDIF.   " IF NOT i_horizon_align IS INITIAL.

   IF NOT i_vertical_align IS INITIAL.

     IF i_vertical_align = 'T'.
       SET PROPERTY OF w_cell 'VerticalAlignment' = xltop.
     ELSEIF i_vertical_align = 'B'.
       SET PROPERTY OF w_cell 'VerticalAlignment' = xlbottom.
     ELSEIF i_vertical_align = 'C'.
       SET PROPERTY OF w_cell 'VerticalAlignment' = xlcenter.
     ENDIF. " IF i_vertical_align = 'T'.

   ENDIF. " IF NOT i_vertical_align IS INITIAL.

* To set number format for cell
   IF i_digit <> ''.

     IF i_value IS INITIAL AND i_digit <> '%'.
       SET PROPERTY OF w_cell 'VALUE' = ''.
     ELSE.

       IF i_digit = '1'.
         SET PROPERTY OF w_cell 'NumberFormat' = '#,###.0 '.
       ELSEIF i_digit = '2'.
         SET PROPERTY OF w_cell 'NumberFormat' = '#,##0.00 '.
       ELSEIF i_digit = '%'.
         SET PROPERTY OF w_cell 'NumberFormat' = '#,##0.00% '.
       ELSE.
         SET PROPERTY OF w_cell 'NumberFormat' = '#,### '.
       ENDIF.

     ENDIF. " IF i_value IS INITIAL AND i_digit <> '%'.

   ENDIF. " IF i_digit <> ''.

ENDFORM.                    " fill_ccell
*&---------------------------------------------------------------------*
*&      Form merge_cell
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_1      text
*      -->P_6      text
*      -->P_1      text
*      -->P_7      text
*----------------------------------------------------------------------*
FORM merge_cell USING   i_row1 i_col1
                          i_row2 i_col2.
   CALL METHOD OF w_excel 'Cells' = w_cell1
     EXPORTING #1 = i_row1
     #2 = i_col1.

   CALL METHOD OF w_excel 'Cells' = w_cell2
     EXPORTING #1 = i_row2
     #2 = i_col2.

   CALL METHOD OF w_excel 'Range' = w_range
     EXPORTING #1 = w_cell1
     #2 = w_cell2.

   CALL METHOD OF w_range 'Merge'.
ENDFORM.                    " merge_cell
*&---------------------------------------------------------------------*
*&      Form set_border
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_1      text
*      -->P_6      text
*      -->P_1      text
*      -->P_7      text
*      -->P_8      text
*      -->P_1503   text
*----------------------------------------------------------------------*
FORM set_border USING i_row1
                        i_col1
                        i_row2
                        i_col2
                        i_thickness
                        i_allborders.

   CALL METHOD OF w_excel 'Cells' = w_cell1
     EXPORTING
     #1 = i_row1
     #2 = i_col1.

   CALL METHOD OF w_excel 'Cells' = w_cell2
     EXPORTING #1 = i_row2
     #2 = i_col2.

   CALL METHOD OF w_excel 'Range' = w_range
     EXPORTING #1 = w_cell1
     #2 = w_cell2.

   IF i_allborders IS INITIAL.

     CALL METHOD OF w_range 'BorderAround'
     EXPORTING #1 = 1 "Continuous line
               #2 = i_thickness. "Thickness: 1 - Normal, 4 - Thick
   ELSE.

     GET PROPERTY OF w_range 'Borders' = w_border.
     SET PROPERTY OF w_border 'LineStyle' = '1'.
     SET PROPERTY OF w_border 'Weight' = i_thickness.

   ENDIF. " IF i_allborders IS INITIAL.
ENDFORM.                    " set_border
*&---------------------------------------------------------------------*
*&      Form set_color
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_1      text
*      -->P_6      text
*      -->P_1      text
*      -->P_7      text
*      -->P_1511   text
*----------------------------------------------------------------------*
FORM set_color USING i_row1
                       i_col1
                       i_row2
                       i_col2
                       i_color.

   DATA: l_colorindex TYPE i.

   CASE i_color.

     WHEN 'BK'.
       l_colorindex = 1. "Black

     WHEN 'BR'.
       l_colorindex = 53. "Brown

     WHEN 'OG'.
       l_colorindex = 52. "Olive Green

     WHEN 'DG'.
       l_colorindex = 51. "Dark Green

     WHEN 'DT'.
       l_colorindex = 49. "Dark Teal

     WHEN 'DB'.
       l_colorindex = 11. "Dark Blue

     WHEN 'ID'.
       l_colorindex = 55. "Indigo

     WHEN 'G4'.
       l_colorindex = 56.                                    "Gray 80%

     WHEN 'DR'.
       l_colorindex = 9. "Dark Red

     WHEN 'OR'.
       l_colorindex = 46. "Orange

     WHEN 'DY'.
       l_colorindex = 12. "Dark Yellow

     WHEN 'GR'.
       l_colorindex = 10. "Green

     WHEN 'TL'.
       l_colorindex = 14. "Teal

     WHEN 'BL'.
       l_colorindex = 5. "Blue

     WHEN 'BY'.
       l_colorindex = 47. "Blue Gray

     WHEN 'G3'.
       l_colorindex = 16.                                    "Gray 50%

     WHEN 'RD'.
       l_colorindex = 3. "Red

     WHEN 'LO'.
       l_colorindex = 45. "Light Orange

     WHEN 'LI'.
       l_colorindex = 43. "Lime

     WHEN 'SG'.
       l_colorindex = 50. "Sea Green

     WHEN 'AQ'.
       l_colorindex = 42. "Aqua

     WHEN 'LB'.
       l_colorindex = 41. "Light Blue

     WHEN 'VL'.
       l_colorindex = 13. "Violet

     WHEN 'G2'.
       l_colorindex = 48.                                    "Gray 40%

     WHEN 'PK'.
       l_colorindex = 7. "Pink

     WHEN 'GD'.
       l_colorindex = 44. "Gold

     WHEN 'YL'.
       l_colorindex = 6. "Yellow

     WHEN 'BG'.
       l_colorindex = 4. "Bright Green

     WHEN 'TQ'.
       l_colorindex = 8. "Turquoise

     WHEN 'SB'.
       l_colorindex = 33. "Sky Blue

     WHEN 'PL'.
       l_colorindex = 54. "Plum

     WHEN 'G1'.
       l_colorindex = 15.                                    "Gray 25%

     WHEN 'RS'.
       l_colorindex = 38. "Rose

     WHEN 'TN'.
       l_colorindex = 40. "Tan

     WHEN 'LY'.
       l_colorindex = 36. "Light Yellow

     WHEN 'LG'.
       l_colorindex = 35. "Light Green

     WHEN 'LT'.
       l_colorindex = 34. "Light Turquoise

     WHEN 'PB'.
       l_colorindex = 37. "Pale Blue

     WHEN 'LV'.
       l_colorindex = 39. "Lavender

     WHEN 'WH'.
       l_colorindex = 2. "White

     WHEN OTHERS.
       l_colorindex = 2. "White

   ENDCASE. " CASE i_color.

   CALL METHOD OF w_excel 'Cells' = w_cell1
     EXPORTING #1 = i_row1
     #2 = i_col1.

   CALL METHOD OF w_excel 'Cells' = w_cell2
     EXPORTING #1 = i_row2
     #2 = i_col2.

   CALL METHOD OF w_excel 'Range' = w_range
     EXPORTING #1 = w_cell1
     #2 = w_cell2.

* GET PROPERTY OF w_excel 'FormatConditions' = w_formatconditions.

*    SET PROPERTY OF w_formatconditions 'ReverseOrder' = 0.
*    SET PROPERTY OF w_formatconditions 'ShowIconOnly' = 1.
*    SET PROPERTY OF w_formatconditions 'IconSet' =
* 'xl3trafficlights1'.
*
*    SET PROPERTY OF w_formatconditions 'iconcriteria' = 2.
*    SET PROPERTY OF w_formatconditions 'Type' =
*'xlconditionvaluenumber'.
*    SET PROPERTY OF w_formatconditions 'Value' = 2.
*    SET PROPERTY OF w_formatconditions 'Operator' = 5.

   GET PROPERTY OF w_range 'Interior' = w_interior.

   SET PROPERTY OF w_interior 'ColorIndex' = l_colorindex.

ENDFORM.                    " set_color
*&---------------------------------------------------------------------*
*&      Form err_hdl
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM err_hdl .
   IF sy-subrc <> 0.
     WRITE: / 'OLE-Automation Error:'(010), sy-subrc.
     STOP.
   ENDIF. " IF sy-subrc <> 0.
ENDFORM.                    " err_hdl*&---------------------------------------------------------------------*
*& Report ZBC_SHA_SALES_INFO_IN_EXCEL
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT zbc_sha_sales_info_in_excel.
INCLUDE <icon>.
* EXCEL sheet using OLE automation.
INCLUDE ole2incl.
INCLUDE excel__c.
*----------------------------------------------------------------------*
* Customer Database Tables
*----------------------------------------------------------------------*
TABLES: vbak,   "Sales Header
         vbap,   "Sales Item
         likp,   "Delivery Header
         lips,   "SD document: Delivery: Item data
         vttk,   "Shipment Header
         vttp,   "Shipment Item
         vbrk,   "Billing Document: Header Data
         vbrp,   "Billing Document: Item Data
         kna1.   "General Data in Customer Master
*----------------------------------------------------------------------*
* Internal Tables
*----------------------------------------------------------------------*
DATA: BEGIN OF it_output OCCURS 10 ,
          vbeln LIKE vbak-vbeln,   "Sales Document
          audat LIKE vbak-audat,   "Document Date (Date Received/Sent)
          netwr LIKE vbak-netwr,   "Net Value of the Sales Order
          waerk LIKE vbak-waerk,   "SD Document Currency
          kunnr LIKE vbak-kunnr,   "Sold-to party
          name1 LIKE kna1-name1,                            "Name 1
          posnr LIKE vbap-posnr,   "Sales Document Item
          matnr LIKE vbap-matnr,   "material number
          maktx LIKE makt-maktx,   "material desc.
          vbeln1 LIKE likp-vbeln,   "Delivery
          tknum LIKE vttp-tknum,   "Shipment Number
          distz LIKE vttk-distz,   "Distance
          medst LIKE vttk-medst,   "Unit of measure for distance
          route LIKE vttk-route,   "Route
          vbeln2 LIKE vbrk-vbeln,   "Billing Document
          status LIKE icon-name,    "Status
          selkz(1).
DATA: END OF it_output.

DATA: BEGIN OF it_vbak OCCURS 10,
         vbeln LIKE vbak-vbeln,
         kunnr LIKE vbak-kunnr,
         waerk LIKE vbak-waerk,
         audat LIKE vbak-audat,
         netwr LIKE vbak-netwr,
       END OF it_vbak.

DATA: BEGIN OF it_vbap OCCURS 10,
         vbeln LIKE vbap-vbeln,
         posnr LIKE vbap-posnr,
         matnr LIKE vbap-matnr,
       END OF it_vbap.

DATA: BEGIN OF it_makt OCCURS 10,
         matnr LIKE makt-matnr,
         maktx LIKE makt-maktx,
       END OF it_makt.

DATA: BEGIN OF it_kna1 OCCURS 10,
         kunnr LIKE kna1-kunnr,
         name1 LIKE kna1-name1,
       END OF it_kna1.

DATA: BEGIN OF it_lips OCCURS 10,
         vbeln LIKE lips-vbeln,
         vgbel LIKE lips-vgbel,
         vgpos LIKE lips-vgpos,
         matnr LIKE lips-matnr,
       END OF it_lips.

DATA: BEGIN OF it_likp OCCURS 10,
         vbeln LIKE likp-vbeln,
       END OF it_likp.

DATA: BEGIN OF it_vttp OCCURS 10,
         vbeln LIKE vttp-vbeln,
         tknum LIKE vttp-tknum,

       END OF it_vttp.

DATA: BEGIN OF it_vbrp OCCURS 10,
         vbeln LIKE vbrp-vbeln,
         vgbel LIKE vbrp-vgbel,
       END OF it_vbrp.

DATA: BEGIN OF it_vbrk OCCURS 10,
         vbeln LIKE vbrk-vbeln,
       END OF it_vbrk.

DATA: BEGIN OF it_vttk OCCURS 10,
         tknum LIKE vttk-tknum,
         route LIKE vttk-route,
         distz LIKE vttk-distz,
         medst LIKE vttk-medst,
       END OF it_vttk.

DATA: it_output1 LIKE TABLE OF it_output WITH HEADER LINE.
*&---------------------------------------------------------------------*
*& Variables
*&---------------------------------------------------------------------*
DATA: w_excel   TYPE ole2_object, "Holds the excel application
       w_wbooks TYPE ole2_object, "Holds Work Books
       w_wbook   TYPE ole2_object, "Holds Work Book
       w_cell    TYPE ole2_object, "Holds Cell
       w_cell1   TYPE ole2_object, "Holds Cell
       w_cell2   TYPE ole2_object, "Holds Cell
       w_format TYPE ole2_object, "Object for format
       w_font    TYPE ole2_object,
       w_wsheets TYPE ole2_object, "Holds Active Sheet
       w_wsheet TYPE ole2_object, "Holds Active Sheet
       w_range   TYPE ole2_object, "To select a range
       w_interior TYPE ole2_object,
       w_formatconditions TYPE ole2_object,
       w_border TYPE ole2_object,
       w_row TYPE i,
       w_col TYPE i,
       w_size TYPE i.
*----------------------------------------------------------------------*
* ALV GRID Variables
*----------------------------------------------------------------------*
TYPE-POOLS: slis.

DATA: gd_title      TYPE lvc_title,
       gd_fieldcat   TYPE slis_t_fieldcat_alv ,
       wa_fieldcat   TYPE slis_fieldcat_alv,
       gd_layout     TYPE slis_layout_alv,
       gd_sort       TYPE slis_t_sortinfo_alv,
       wa_sort       LIKE LINE OF gd_sort,
       gd_events     TYPE slis_t_event,
       gd_extab      TYPE slis_t_extab WITH HEADER LINE,
       wa_extab      TYPE slis_t_extab WITH HEADER LINE,
       gd_event_exit TYPE slis_t_event_exit WITH HEADER LINE,
       wa_event      TYPE slis_alv_event,
       wa_event_exit TYPE slis_t_event_exit,
       gd_save(1),
       gd_repid      LIKE sy-repid,
       gd_dynnr      LIKE sy-dynnr,
       gd_param1     LIKE sy-ucomm,
       gd_top        TYPE slis_t_listheader,
       wa_top        TYPE slis_listheader.
*----------------------------------------------------------------------*
* Selection screen
*----------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECT-OPTIONS: s_vbeln FOR vbak-vbeln,      " Sales Document
                 s_vkorg FOR vbak-vkorg.      " Sales Organization
SELECTION-SCREEN END OF BLOCK b1.
*----------------------------------------------------------------------*
* MAIN PROGRAM
*----------------------------------------------------------------------*
gd_repid = sy-repid.

PERFORM fetch_assign_data.

IF it_output[] IS NOT INITIAL.

   PERFORM display_data.
ENDIF.
*&---------------------------------------------------------------------*
*&      Form fetch_assign_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM fetch_assign_data .
   SELECT vbeln
          kunnr
          waerk
          audat
          netwr FROM vbak
                INTO TABLE it_vbak
               WHERE vbeln IN s_vbeln
                 AND vkorg IN s_vkorg.

   IF it_vbak[] IS NOT INITIAL.

     SELECT vbeln
            posnr
            matnr FROM vbap
                  INTO TABLE it_vbap
                   FOR ALL ENTRIES IN it_vbak
                 WHERE vbeln EQ it_vbak-vbeln.

     SELECT kunnr
            name1 FROM kna1
                  INTO TABLE it_kna1
                   FOR ALL ENTRIES IN it_vbak
                 WHERE kunnr EQ it_vbak-kunnr.

     IF it_vbap[] IS NOT INITIAL .

       SELECT matnr
              maktx FROM makt
                    INTO TABLE it_makt
                     FOR ALL ENTRIES IN it_vbap
                   WHERE matnr EQ it_vbap-matnr.

       SELECT vbeln
              vgbel
              vgpos
              matnr FROM lips
                    INTO TABLE it_lips
                     FOR ALL ENTRIES IN it_vbap
                   WHERE vgbel EQ it_vbap-vbeln
                     AND vgpos EQ it_vbap-posnr.

       IF it_lips[] IS NOT INITIAL.

         SELECT vbeln FROM likp
                      INTO TABLE it_likp
                       FOR ALL ENTRIES IN it_lips
                     WHERE vbeln EQ it_lips-vbeln.

         SELECT vbeln
                tknum FROM vttp
                      INTO TABLE it_vttp
                       FOR ALL ENTRIES IN it_lips
                     WHERE vbeln EQ it_lips-vbeln.

         IF it_vttp[] IS NOT INITIAL .

           SELECT tknum
                  route
                  distz
                  medst FROM vttk
                        INTO TABLE it_vttk
                         FOR ALL ENTRIES IN it_vttp
                       WHERE tknum EQ it_vttp-tknum.

         ENDIF.

         SELECT vbeln
                vgbel FROM vbrp
                      INTO TABLE it_vbrp
                       FOR ALL ENTRIES IN it_lips
                     WHERE vgbel EQ it_lips-vbeln.

         IF it_vbrp[] IS NOT INITIAL.

           SELECT vbeln FROM vbrk
                        INTO TABLE it_vbrk
                         FOR ALL ENTRIES IN it_vbrp
                       WHERE vbeln EQ it_vbrp-vbeln
                         AND vbtyp EQ 'M'.
         ENDIF.    " IF it_vbrp[] IS NOT INITIAL.

       ENDIF.    " IF it_lips[] IS NOT INITIAL.

     ENDIF.    " IF it_vbap[] IS NOT INITIAL .

   ENDIF .   " IF it_vbak[] IS NOT INITIAL.

   CLEAR it_output[].

   LOOP AT it_vbap.

     CLEAR it_output.
     it_output-status = icon_red_light.
     it_output-posnr = it_vbap-posnr.                "Sales Doc. Item
     it_output-matnr = it_vbap-matnr.                "Material No.

     READ TABLE it_makt WITH KEY matnr = it_vbap-matnr.
     IF sy-subrc EQ 0.
       it_output-maktx = it_makt-maktx.              "Material Description
     ENDIF.

     READ TABLE it_vbak WITH KEY vbeln = it_vbap-vbeln.

     IF sy-subrc EQ 0.
       it_output-vbeln = it_vbak-vbeln.              "Sales doc. no.
       it_output-kunnr = it_vbak-kunnr.              "Sold To Party
       it_output-waerk = it_vbak-waerk.              "SD Doc. Currency
       it_output-audat = it_vbak-audat.              "Document Date
       it_output-netwr = it_vbak-netwr.              "Net Value

       READ TABLE it_kna1 WITH KEY kunnr = it_vbak-kunnr.
       IF sy-subrc EQ 0.
         it_output-name1 = it_kna1-name1.              "Customer
       ENDIF.

       READ TABLE it_lips WITH KEY vgbel = it_vbap-vbeln
                                   vgpos = it_vbap-posnr
                                   matnr = it_vbap-matnr.
       IF sy-subrc EQ 0.
         LOOP AT it_lips WHERE vgbel EQ it_vbap-vbeln AND
                               vgpos EQ it_vbap-posnr AND
                               matnr EQ it_vbap-matnr.

*      READ TABLE it_lips WITH KEY vgbel = it_vbap-vbeln
*                                  vgpos = it_vbap-posnr
*                                  matnr = it_vbap-matnr.

*        IF sy-subrc EQ 0.
           it_output-status = icon_yellow_light.
*       Read Delivery Header
           READ TABLE it_likp WITH KEY vbeln = it_lips-vbeln.

           IF sy-subrc EQ 0.
             it_output-vbeln1 = it_likp-vbeln.            "Delivery

**        loop at shipment item
             LOOP AT it_vttp WHERE vbeln EQ it_likp-vbeln.

*          READ TABLE it_vttp WITH KEY vbeln = it_likp-vbeln.

*          IF sy-subrc EQ 0.

*         Read Shipment Header
               READ TABLE it_vttk WITH KEY tknum = it_vttp-tknum.
               IF sy-subrc EQ 0.
                 it_output-tknum = it_vttk-tknum.           "Shipment
                 it_output-route = it_vttk-route.           "Route
                 it_output-distz = it_vttk-distz.           "Distance
                 it_output-medst = it_vttk-medst.      "Unit Of Distance
               ENDIF.

             ENDLOOP.
*          ENDIF.
**            Loop At Billing Item
             LOOP AT it_vbrp WHERE vgbel EQ it_lips-vbeln.

*          READ TABLE it_vbrp WITH KEY vgbel = it_lips-vbeln.
*
*          IF sy-subrc EQ 0.

*             Read Billing Header
               READ TABLE it_vbrk WITH KEY vbeln = it_vbrp-vbeln.

               IF sy-subrc EQ 0.
                 it_output-vbeln2 = it_vbrk-vbeln.          "Billing
               ENDIF.

               IF it_output-vbeln1 IS NOT INITIAL AND
                  it_output-vbeln2 IS NOT INITIAL.
                 it_output-status = icon_green_light.
               ENDIF.

*          ENDIF.
             ENDLOOP."Loop it_vbrp

           ENDIF.     "sy-subrc EQ 0.

           APPEND it_output.
         ENDLOOP.

       ELSE.
         APPEND it_output.
       ENDIF.
     ENDIF.
   ENDLOOP.

ENDFORM.                    " fetch_assign_data
*&---------------------------------------------------------------------*
*&      Form display_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM display_data .
   CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE'
     EXPORTING
       i_program_name     = gd_repid
       i_internal_tabname = 'IT_OUTPUT'
       i_inclname         = gd_repid
     CHANGING
       ct_fieldcat        = gd_fieldcat.
   IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
   ENDIF.

   PERFORM manipulate_events.
   PERFORM manipulate_fieldcatlog.
   PERFORM manipulate_layout.

   CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
     EXPORTING
       i_callback_program       = gd_repid
       i_callback_pf_status_set = 'PF_STATUS'
       i_callback_user_command = 'USER_COMMAND'
       is_layout                = gd_layout
       it_fieldcat              = gd_fieldcat
     TABLES
       t_outtab                 = it_output.
   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.                    " display_data
*&---------------------------------------------------------------------*
*&      Form manipulate_events
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM manipulate_events .
   CALL FUNCTION 'REUSE_ALV_EVENTS_GET'
     EXPORTING
       i_list_type     = 0
     IMPORTING
       et_events       = gd_events
     EXCEPTIONS
       list_type_wrong = 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.

   ELSE.

     READ TABLE gd_events WITH KEY name = 'USER_COMMAND' INTO wa_event.
     wa_event-form = 'USER_COMMAND1'.
     MODIFY gd_events FROM wa_event INDEX sy-tabix.

   ENDIF. "sy-subrc <> 0
ENDFORM.                    " manipulate_events
*&---------------------------------------------------------------------*
*&      Form manipulate_fieldcatlog
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM manipulate_fieldcatlog .

   LOOP AT gd_fieldcat INTO wa_fieldcat.

     CASE wa_fieldcat-fieldname.

       WHEN 'VBELN' OR                         "Sales Doc. No.
            'VBELN1' OR                         "Delivery
            'VBELN2' OR                         "Billing
            'KUNNR' OR                         "Sold To Party
            'TKNUM' OR                         "Shipment
            'MATNR'.                            "Material No.

         wa_fieldcat-hotspot = 'X'.

       WHEN 'SELKZ'.
         wa_fieldcat-no_out    = 'X'.

       WHEN 'NETWR'.
         IF sy-ucomm EQ 'EDIT'.
           wa_fieldcat-edit = 'X'.
         ENDIF.
     ENDCASE.

     MODIFY gd_fieldcat FROM wa_fieldcat.

   ENDLOOP.
ENDFORM.                    " manipulate_fieldcatlog
*&---------------------------------------------------------------------*
*&      Form manipulate_layout
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM manipulate_layout .
   gd_layout-box_fieldname     = 'SELKZ'.
   gd_layout-box_tabname       = 'IT_OUTPUT'.
   gd_layout-colwidth_optimize = 'X'.
ENDFORM.                    " manipulate_layout
*      Form user_command
*&---------------------------------------------------------------------
*
*       text
*----------------------------------------------------------------------
*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------
FORM user_command USING param1 TYPE sy-ucomm
                         param2 TYPE slis_selfield.

   CASE param1.

     WHEN '&IC1'.
       IF param2-fieldname = 'VBELN'.
         SET PARAMETER ID 'AUN' FIELD param2-value.
         CALL TRANSACTION 'VA03' AND SKIP FIRST SCREEN.
       ENDIF. "param2-fieldname = 'VBELN'.

       IF param2-fieldname = 'KUNNR'.
         SET PARAMETER ID 'KUN' FIELD param2-value.
         CALL TRANSACTION 'XD03' AND SKIP FIRST SCREEN.
       ENDIF. "param2-fieldname = 'KUNNR'.

       IF param2-fieldname = 'VBELN1'.
         SET PARAMETER ID 'VL' FIELD param2-value.
         CALL TRANSACTION 'VL03N' AND SKIP FIRST SCREEN.
       ENDIF. "param2-fieldname = 'VBELN1'.

       IF param2-fieldname = 'TKNUM'.
         SET PARAMETER ID 'TNR' FIELD param2-value.
         CALL TRANSACTION 'VT03N' AND SKIP FIRST SCREEN.
       ENDIF. "param2-fieldname = 'TKNUM'.

       IF param2-fieldname = 'VBELN2'.
         SET PARAMETER ID 'VF' FIELD param2-value.
         CALL TRANSACTION 'VF03' AND SKIP FIRST SCREEN.
       ENDIF. "param2-fieldname = 'VBELN2'.

       IF param2-fieldname = 'MATNR'.
         SET PARAMETER ID 'MAT' FIELD param2-value.
         CALL TRANSACTION 'MM03' AND SKIP FIRST SCREEN.
       ENDIF. "param2-fieldname = 'MATNR'.

     WHEN 'EDIT'.
       READ TABLE it_output WITH KEY selkz = 'X'.
       DELETE it_output[] WHERE selkz NE 'X'.
       it_output1[] = it_output[].
       PERFORM display_data.

     WHEN 'SAVE'.

       LOOP AT it_output.

         UPDATE vbak SET netwr = it_output-netwr
                    WHERE vbeln EQ it_output-vbeln
                      AND kunnr EQ it_output-kunnr.
         IF sy-subrc EQ 0.
           MESSAGE 'Value Updated Sucessfully' TYPE 'S'.
         ELSE.
           MESSAGE 'Double click on the changed row' TYPE 'I'.
         ENDIF.
       ENDLOOP.

     WHEN 'EXCEL'.

*      READ TABLE it_output WITH KEY selkz = 'X'.
*      IF sy-subrc EQ 0.
         PERFORM open_excel.
         PERFORM add_worksheet.
*      ENDIF.

     WHEN 'BACK'.
       LEAVE TO SCREEN 1000.

   ENDCASE.                                                  "param1

   param2-refresh = 'X'.
ENDFORM.                    "user_command
*&---------------------------------------------------------------------*
*&      Form pf_status
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->RT_EXTAB   text
*----------------------------------------------------------------------*
FORM pf_status USING rt_extab TYPE slis_t_extab.

   DATA: gd_index TYPE i.

   wa_extab-fcode = 'EDIT'.
   APPEND wa_extab TO gd_extab.
   CLEAR wa_extab.

   wa_extab-fcode = 'EXCEL'.
   APPEND wa_extab TO gd_extab.
   CLEAR wa_extab.

   IF sy-ucomm EQ 'EDIT' .

     SET PF-STATUS 'ZSTATUS' EXCLUDING gd_extab.

   ELSEIF sy-index EQ 0 AND sy-ucomm EQ ' '.

     SET PF-STATUS 'ZSTATUS'.

   ELSEIF sy-ucomm EQ 'BACK'.
     SET PF-STATUS 'ZSTATUS'.

   ELSEIF sy-index EQ 1 OR sy-ucomm EQ 'SAVE'.

     SET PF-STATUS 'ZSTATUS' EXCLUDING gd_extab.

   ENDIF. " IF sy-ucomm EQ 'EDIT' .
ENDFORM.                    "pf_status
*&---------------------------------------------------------------------*
*&      Form open_excel
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM open_excel .
   DATA: l_cnt TYPE i.

   CREATE OBJECT w_excel 'EXCEL.APPLICATION'.
   IF sy-subrc NE 0.
     WRITE : / 'Error CREATE OBJECT'.
   ENDIF. " IF sy-subrc NE 0.

   SET PROPERTY OF w_excel 'Visible' = 1.
   IF sy-subrc NE 0.
     WRITE : / 'Error MAKE EXCEL VISIBLE'.
   ENDIF. " IF sy-subrc NE 0.

   CALL METHOD OF w_excel 'WORKBOOKS' = w_wbooks.
   IF sy-subrc NE 0.
     WRITE : / 'Error GET WORKBOOKS'.
   ENDIF. " IF sy-subrc NE 0.

   CALL METHOD OF w_wbooks 'ADD' = w_wbook.
   IF sy-subrc NE 0.
     WRITE : / 'Error ADD TEMPLATE'.
   ENDIF. " IF sy-subrc NE 0.

   GET PROPERTY OF w_wbook 'Worksheets' = w_wsheets.
   GET PROPERTY OF w_excel 'ACTIVESHEET' = w_wsheet.
   SET PROPERTY OF w_excel 'DISPLAYALERTS' = 0.

   GET PROPERTY OF w_wsheets 'Count' = l_cnt.

   l_cnt = l_cnt - 1.

* Delete unwanted worksheets
   DO l_cnt TIMES.
     GET PROPERTY OF w_excel 'ACTIVESHEET' = w_wsheet.
     CALL METHOD OF w_wsheet 'DELETE'.
   ENDDO. " DO l_cnt TIMES.
ENDFORM.                    " open_excel
*&---------------------------------------------------------------------*
*&      Form add_worksheet
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM add_worksheet .
   GET PROPERTY OF w_excel 'ACTIVESHEET' = w_wsheet.
   CALL METHOD OF w_wsheets 'Add' = w_wsheet.

   w_size = 15.

   PERFORM fill_ccell USING 1 8 'Sales Details' 'X' '' '' '' '' 200.

   PERFORM merge_cell USING 1 8 1 9.

   PERFORM set_border USING 1 8 1 9 10 'X'.

   PERFORM set_color USING 1 8 1 9 'LV'.

   "Distance
   "Unit of measure for distance
   "Route
   "Billing Document
   "Status

   PERFORM fill_ccell
   USING 2 1 'Sales Document' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 2 'Document Date' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 3 'Net Value ' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 4 'SD Document Currency' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 5 'Sold-to party' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 6 'Name 1' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 7 'Sales Document Item' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 8 'Material number' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 9 'Material Desc.' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 10 'Delivery' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 11 'Shipment Number' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 12 'Distance' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 13 'Unit of measure for distance' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 14 'Route' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 15 'Billing Document' 'X' '' '' '' '' 000255000.

   PERFORM fill_ccell
   USING 2 16 'Status' 'X' '' '' '' '' 000255000.

   w_row = 2.
   w_size = 10.

   LOOP AT it_output WHERE selkz EQ 'X'.
     w_row = w_row + 1.

     PERFORM fill_ccell
     USING w_row 1 it_output-vbeln 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 2 it_output-audat 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 3 it_output-netwr 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 4 it_output-waerk 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 5 it_output-kunnr 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 6 it_output-name1 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 7 it_output-posnr 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 8 it_output-matnr 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 9 it_output-maktx 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 10 it_output-vbeln1 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 11 it_output-tknum 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 12 it_output-distz 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 13 it_output-medst 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 14 it_output-route 'X' '' '' '' '' 111111111.

     PERFORM fill_ccell
     USING w_row 15 it_output-vbeln2 'X' '' '' '' '' 111111111.

*    IF it_output-status EQ icon_red_light.
*      it_output-status = 0.
*    ELSEIF it_output-status EQ icon_yellow_light.
*      it_output-status = 1.
*    ELSEIF it_output-status EQ icon_green_light.
*      it_output-status = 2.
*    ENDIF.

*    PERFORM fill_ccell
*    USING w_row 16 it_output-status '' '' '' '' '' 111111111.

     CALL METHOD OF w_excel 'WORKBOOKS' = w_wbooks.
**
     IF it_output-status EQ icon_red_light.
       PERFORM set_color USING w_row 16 w_row 16 'RD'.
       PERFORM set_border USING w_row 16 w_row 16 10 'X'.
     ELSEIF it_output-status EQ icon_green_light.
       PERFORM set_color USING w_row 16 w_row 16 'GR'.
       PERFORM set_border USING w_row 16 w_row 16 10 'X'.
     ELSEIF it_output-status EQ icon_yellow_light.
       PERFORM set_color USING w_row 16 w_row 16 'YL'.
       PERFORM set_border USING w_row 16 w_row 16 10 'X'.
     ENDIF.

*    PERFORM fill_ccell
*    USING w_row 16 it_output-status 'X' '' '' '' '' 111111111.

   ENDLOOP.

ENDFORM.                    " add_worksheet
*&---------------------------------------------------------------------*
*&      Form fill_ccell
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_1      text
*      -->P_6      text
*      -->P_1481   text
*      -->P_1482   text
*      -->P_1483   text
*      -->P_1484   text
*      -->P_1485   text
*      -->P_1486   text
*      -->P_200    text
*----------------------------------------------------------------------*
FORM fill_ccell USING i_row TYPE i
                         i_col TYPE i
                         i_value
                         i_fontbold
                         i_digit
                         i_wraptext
                         i_horizon_align
                         i_vertical_align
                         i_color.

   DATA: l_str TYPE string.

   CALL METHOD OF w_excel 'Cells' = w_cell
     EXPORTING
     #1 = i_row
     #2 = i_col.

   PERFORM err_hdl.

   SET PROPERTY OF w_cell 'VALUE' = i_value.
   PERFORM err_hdl.

   IF i_fontbold = 'X'.
     GET PROPERTY OF w_cell 'Font' = w_font.
     SET PROPERTY OF w_font 'Bold' = 1.
     SET PROPERTY OF w_font 'Size' = w_size.
     SET PROPERTY OF w_font 'Color' = i_color.
   ENDIF. " IF i_fontbold = 'X'.

   IF NOT i_wraptext IS INITIAL.
     SET PROPERTY OF w_cell 'WrapText' = 1.
   ENDIF. " IF NOT i_wraptext IS INITIAL.

   IF NOT i_horizon_align IS INITIAL.

     IF i_horizon_align = 'L'.
       SET PROPERTY OF w_cell 'HorizontalAlignment' = xlleft.
     ELSEIF i_horizon_align = 'R'.
       SET PROPERTY OF w_cell 'HorizontalAlignment' = xlright.
     ELSEIF i_horizon_align = 'C'.
       SET PROPERTY OF w_cell 'HorizontalAlignment' = xlcenter.
     ENDIF. " IF i_horizon_align = 'L'.

   ENDIF.   " IF NOT i_horizon_align IS INITIAL.

   IF NOT i_vertical_align IS INITIAL.

     IF i_vertical_align = 'T'.
       SET PROPERTY OF w_cell 'VerticalAlignment' = xltop.
     ELSEIF i_vertical_align = 'B'.
       SET PROPERTY OF w_cell 'VerticalAlignment' = xlbottom.
     ELSEIF i_vertical_align = 'C'.
       SET PROPERTY OF w_cell 'VerticalAlignment' = xlcenter.
     ENDIF. " IF i_vertical_align = 'T'.

   ENDIF. " IF NOT i_vertical_align IS INITIAL.

* To set number format for cell
   IF i_digit <> ''.

     IF i_value IS INITIAL AND i_digit <> '%'.
       SET PROPERTY OF w_cell 'VALUE' = ''.
     ELSE.

       IF i_digit = '1'.
         SET PROPERTY OF w_cell 'NumberFormat' = '#,###.0 '.
       ELSEIF i_digit = '2'.
         SET PROPERTY OF w_cell 'NumberFormat' = '#,##0.00 '.
       ELSEIF i_digit = '%'.
         SET PROPERTY OF w_cell 'NumberFormat' = '#,##0.00% '.
       ELSE.
         SET PROPERTY OF w_cell 'NumberFormat' = '#,### '.
       ENDIF.

     ENDIF. " IF i_value IS INITIAL AND i_digit <> '%'.

   ENDIF. " IF i_digit <> ''.

ENDFORM.                    " fill_ccell
*&---------------------------------------------------------------------*
*&      Form merge_cell
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_1      text
*      -->P_6      text
*      -->P_1      text
*      -->P_7      text
*----------------------------------------------------------------------*
FORM merge_cell USING   i_row1 i_col1
                          i_row2 i_col2.
   CALL METHOD OF w_excel 'Cells' = w_cell1
     EXPORTING #1 = i_row1
     #2 = i_col1.

   CALL METHOD OF w_excel 'Cells' = w_cell2
     EXPORTING #1 = i_row2
     #2 = i_col2.

   CALL METHOD OF w_excel 'Range' = w_range
     EXPORTING #1 = w_cell1
     #2 = w_cell2.

   CALL METHOD OF w_range 'Merge'.
ENDFORM.                    " merge_cell
*&---------------------------------------------------------------------*
*&      Form set_border
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_1      text
*      -->P_6      text
*      -->P_1      text
*      -->P_7      text
*      -->P_8      text
*      -->P_1503   text
*----------------------------------------------------------------------*
FORM set_border USING i_row1
                        i_col1
                        i_row2
                        i_col2
                        i_thickness
                        i_allborders.

   CALL METHOD OF w_excel 'Cells' = w_cell1
     EXPORTING
     #1 = i_row1
     #2 = i_col1.

   CALL METHOD OF w_excel 'Cells' = w_cell2
     EXPORTING #1 = i_row2
     #2 = i_col2.

   CALL METHOD OF w_excel 'Range' = w_range
     EXPORTING #1 = w_cell1
     #2 = w_cell2.

   IF i_allborders IS INITIAL.

     CALL METHOD OF w_range 'BorderAround'
     EXPORTING #1 = 1 "Continuous line
               #2 = i_thickness. "Thickness: 1 - Normal, 4 - Thick
   ELSE.

     GET PROPERTY OF w_range 'Borders' = w_border.
     SET PROPERTY OF w_border 'LineStyle' = '1'.
     SET PROPERTY OF w_border 'Weight' = i_thickness.

   ENDIF. " IF i_allborders IS INITIAL.
ENDFORM.                    " set_border
*&---------------------------------------------------------------------*
*&      Form set_color
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_1      text
*      -->P_6      text
*      -->P_1      text
*      -->P_7      text
*      -->P_1511   text
*----------------------------------------------------------------------*
FORM set_color USING i_row1
                       i_col1
                       i_row2
                       i_col2
                       i_color.

   DATA: l_colorindex TYPE i.

   CASE i_color.

     WHEN 'BK'.
       l_colorindex = 1. "Black

     WHEN 'BR'.
       l_colorindex = 53. "Brown

     WHEN 'OG'.
       l_colorindex = 52. "Olive Green

     WHEN 'DG'.
       l_colorindex = 51. "Dark Green

     WHEN 'DT'.
       l_colorindex = 49. "Dark Teal

     WHEN 'DB'.
       l_colorindex = 11. "Dark Blue

     WHEN 'ID'.
       l_colorindex = 55. "Indigo

     WHEN 'G4'.
       l_colorindex = 56.                                    "Gray 80%

     WHEN 'DR'.
       l_colorindex = 9. "Dark Red

     WHEN 'OR'.
       l_colorindex = 46. "Orange

     WHEN 'DY'.
       l_colorindex = 12. "Dark Yellow

     WHEN 'GR'.
       l_colorindex = 10. "Green

     WHEN 'TL'.
       l_colorindex = 14. "Teal

     WHEN 'BL'.
       l_colorindex = 5. "Blue

     WHEN 'BY'.
       l_colorindex = 47. "Blue Gray

     WHEN 'G3'.
       l_colorindex = 16.                                    "Gray 50%

     WHEN 'RD'.
       l_colorindex = 3. "Red

     WHEN 'LO'.
       l_colorindex = 45. "Light Orange

     WHEN 'LI'.
       l_colorindex = 43. "Lime

     WHEN 'SG'.
       l_colorindex = 50. "Sea Green

     WHEN 'AQ'.
       l_colorindex = 42. "Aqua

     WHEN 'LB'.
       l_colorindex = 41. "Light Blue

     WHEN 'VL'.
       l_colorindex = 13. "Violet

     WHEN 'G2'.
       l_colorindex = 48.                                    "Gray 40%

     WHEN 'PK'.
       l_colorindex = 7. "Pink

     WHEN 'GD'.
       l_colorindex = 44. "Gold

     WHEN 'YL'.
       l_colorindex = 6. "Yellow

     WHEN 'BG'.
       l_colorindex = 4. "Bright Green

     WHEN 'TQ'.
       l_colorindex = 8. "Turquoise

     WHEN 'SB'.
       l_colorindex = 33. "Sky Blue

     WHEN 'PL'.
       l_colorindex = 54. "Plum

     WHEN 'G1'.
       l_colorindex = 15.                                    "Gray 25%

     WHEN 'RS'.
       l_colorindex = 38. "Rose

     WHEN 'TN'.
       l_colorindex = 40. "Tan

     WHEN 'LY'.
       l_colorindex = 36. "Light Yellow

     WHEN 'LG'.
       l_colorindex = 35. "Light Green

     WHEN 'LT'.
       l_colorindex = 34. "Light Turquoise

     WHEN 'PB'.
       l_colorindex = 37. "Pale Blue

     WHEN 'LV'.
       l_colorindex = 39. "Lavender

     WHEN 'WH'.
       l_colorindex = 2. "White

     WHEN OTHERS.
       l_colorindex = 2. "White

   ENDCASE. " CASE i_color.

   CALL METHOD OF w_excel 'Cells' = w_cell1
     EXPORTING #1 = i_row1
     #2 = i_col1.

   CALL METHOD OF w_excel 'Cells' = w_cell2
     EXPORTING #1 = i_row2
     #2 = i_col2.

   CALL METHOD OF w_excel 'Range' = w_range
     EXPORTING #1 = w_cell1
     #2 = w_cell2.

* GET PROPERTY OF w_excel 'FormatConditions' = w_formatconditions.

*    SET PROPERTY OF w_formatconditions 'ReverseOrder' = 0.
*    SET PROPERTY OF w_formatconditions 'ShowIconOnly' = 1.
*    SET PROPERTY OF w_formatconditions 'IconSet' =
* 'xl3trafficlights1'.
*
*    SET PROPERTY OF w_formatconditions 'iconcriteria' = 2.
*    SET PROPERTY OF w_formatconditions 'Type' =
*'xlconditionvaluenumber'.
*    SET PROPERTY OF w_formatconditions 'Value' = 2.
*    SET PROPERTY OF w_formatconditions 'Operator' = 5.

   GET PROPERTY OF w_range 'Interior' = w_interior.

   SET PROPERTY OF w_interior 'ColorIndex' = l_colorindex.

ENDFORM.                    " set_color
*&---------------------------------------------------------------------*
*&      Form err_hdl
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM err_hdl .
   IF sy-subrc <> 0.
     WRITE: / 'OLE-Automation Error:'(010), sy-subrc.
     STOP.
   ENDIF. " IF sy-subrc <> 0.
ENDFORM.                    " err_hdl