Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Making Letters bold in FILE transported to EXCEL

former_member209920
Active Participant
0 Kudos
2,285

Hi,

I have a requirement to export data from internal table in form of file to excel.

I moved the internal table to another table as string and then using FM GUI_DOWNLOAD into excel, moved it to excel.

I have done is successfully.

But the problem is that

I need the Header First row) of the table in EXCEL should display in BOLD Letters.

How can I achieve this ?

Regards,

ManuB

5 REPLIES 5

raymond_giuseppi
Active Contributor
0 Kudos
306

Use OLE2 to do want you want in Excel. Read document as Copying data from Microsoft Excel to ABAP using OLE or OLE.

You can find many samples in source of FMs like XXL_FULL_API or multiple threads already on scn.    

Regards,

Raymond

lijisusan_mathews
Active Contributor
0 Kudos
306

i don't think you can transport the Formatting while using GUI_DOWNLOAD.

For adding formatting, You have to use OLE.

Former Member
0 Kudos
306

Hi Manu,

The above requirement can be achieved by using ABAP OLE Automation Controller.

Below is the code on how to use it:

REPORT  ZTEST.

* this report demonstrates how to send some ABAP data to an
* * EXCEL sheet using OLE automation.

INCLUDE OLE2INCL.

* * handles for OLE objects
DATA: H_EXCEL TYPE OLE2_OBJECT,        " Excel object
            H_MAPL TYPE OLE2_OBJECT,        " list of workbooks
            H_MAP TYPE OLE2_OBJECT,          " workbook
            H_ZL TYPE OLE2_OBJECT,          " cell
            H_F TYPE OLE2_OBJECT.            " font

TABLES: SPFLI.

DATA  H TYPE I.

* * table of flights
DATA: IT_SPFLI LIKE SPFLI OCCURS 10 WITH HEADER LINE.



**&---------------------------------------------------------------------*
* *&  Event START-OF-SELECTION
* *&---------------------------------------------------------------------*

START-OF-SELECTION.

* * read flights
   SELECT * FROM SPFLI INTO TABLE IT_SPFLI UP TO 10 ROWS.





* * start Excel
   CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'.
   PERFORM ERR_HDL.
   SET PROPERTY OF H_EXCEL  'Visible' = 1.
   PERFORM ERR_HDL.



* * get list of workbooks, initially empty
   CALL METHOD OF H_EXCEL 'Workbooks' = H_MAPL.
   PERFORM ERR_HDL.

* * add a new workbook
   CALL METHOD OF H_MAPL 'Add' = H_MAP.
   PERFORM ERR_HDL.



* * output column headings to active Excel sheet
   PERFORM FILL_CELL USING 1 1 1 'Airline Code'.
   PERFORM FILL_CELL USING 1 2 1 'Flight Number'.
   PERFORM FILL_CELL USING 1 3 1 'From'.
   PERFORM FILL_CELL USING 1 4 1 'To'.
   PERFORM FILL_CELL USING 1 5 1 'Departure Time'.

   LOOP AT IT_SPFLI.

* * copy flights to active EXCEL sheet
     H = SY-TABIX + 1.

     PERFORM FILL_CELL USING H 1 1 IT_SPFLI-CARRID.
     PERFORM FILL_CELL USING H 2 1 IT_SPFLI-CONNID.
     PERFORM FILL_CELL USING H 3 0 IT_SPFLI-CITYFROM.
     PERFORM FILL_CELL USING H 4 0 IT_SPFLI-CITYTO.
     PERFORM FILL_CELL USING H 5 0 IT_SPFLI-DEPTIME.

   ENDLOOP.

* * disconnect from Excel
   FREE OBJECT H_EXCEL.
   PERFORM ERR_HDL.



**---------------------------------------------------------------------*
* *      FORM FILL_CELL                                                *
* *---------------------------------------------------------------------*
* *      sets cell at coordinates i,j to value val boldtype bold      *
* *---------------------------------------------------------------------*

FORM FILL_CELL USING I J BOLD VAL.

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

   PERFORM ERR_HDL.
   SET PROPERTY OF H_ZL 'Value' = VAL .
   PERFORM ERR_HDL.
   GET PROPERTY OF H_ZL 'Font' = H_F.
   PERFORM ERR_HDL.
   SET PROPERTY OF H_F 'Bold' = BOLD .
   PERFORM ERR_HDL.

ENDFORM.

*&---------------------------------------------------------------------*

*&      Form  ERR_HDL
*&--------------------------------------------------------------------*
*      outputs OLE error if any                                      *
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*

FORM ERR_HDL.


IF SY-SUBRC <> 0.
   WRITE: / 'Error Using OLE Automation', SY-SUBRC.
   STOP.
ENDIF.

ENDFORM.

Furthermore, you can also find more details on blog:

http://scn.sap.com/people/sakshi.dogra/blog/2012/03/19/abap--drawing-x-y-scatter-graph-in-ms-excel-u...

And documentation is avalable on: http://help.sap.com/printdocu/core/print46c/en/data/pdf/bcfesde6/bcfesde6.pdf

bastinvinoth
Contributor
0 Kudos
306

Hi Manu Bhatnagar,

Paste the below code,then see the result,

Hope it helpful for you to get bold letters in Excel Header File from SAP.

* OBJECT ID             :

* PROGRAM TITLE         : Customer Master Details Upload Through XD01

* MODULE                : SD

* PROGRAM TYPE          : Data Conversion

* Functional Consultant : Madhu

* Technical Consultant  : Bastin Vinoth NG

* CREATION DATE         : 17-7-2012

*-----------------------------------------------------------------------------------------------------------

* DESCRIPTION           : Customer Master Details Upload Through XD01

*

*

*

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

* Modification history:

* ----------------------------------------------------------------------------------------------------------

* DATE       |User ID     |TS Ver   | Transport Request  | Description

* ----------------------------------------------------------------------------------------------------------

* 17-7-2012   bvinoth        1          D10K900198         Customer Master Upload

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

REPORT zsctp006

        NO STANDARD PAGE HEADING LINE-SIZE 255.

TYPE-POOLS: slis.  " Tables for Display *

DATA: it_fcat TYPE slis_t_fieldcat_alv,           " Field catalog Internal table

       wa_fcat TYPE slis_fieldcat_alv,             " Field catalog Work area

       it_stab TYPE slis_t_sortinfo_alv,           " Sort info Internal table

       wa_stab TYPE slis_sortinfo_alv,             " Sort info Work area

       it_etab TYPE slis_t_event WITH HEADER LINE, " Event Internal table

       wa_etab TYPE slis_alv_event,                " Event Work area

       it_ltab TYPE slis_layout_alv.

DATA: BEGIN OF itab OCCURS 0,

    id(4),

   count TYPE i,

    msg(100),

    para(100),

   kunnr TYPE kna1-kunnr,

   name1 TYPE kna1-name1,

     END OF itab.

DATA: wa LIKE LINE OF itab.

TABLES: t100.

*** Generated data section with specific formatting - DO NOT CHANGE  ***

DATA: BEGIN OF record OCCURS 0,

*  * data element: BUKRS

         bukrs_001(004),

* data element: VKORG

         vkorg_002(004),

* data element: VTWEG

         vtweg_003(002),

* data element: SPART

         spart_004(002),

* data element: KTOKD

         ktokd_005(004),

* data element:

         use_zav_006(001),

* data element: AD_TITLETX

         title_medi_007(030),

* data element: AD_NAME1

         name1_008(040),

* data element: AD_NAME2

         name2_009(040),

* data element: AD_SORT1UL

         sort1_010(020),

* data element: AD_SORT2UL

         sort2_011(020),

* data element: AD_NAME_CO

         name_co_012(040),

* data element: AD_STRSPP1

         str_suppl1_013(040),

* data element: AD_STRSPP2

         str_suppl2_014(040),

* data element: AD_STREET

         street_015(060),

* data element: AD_HSNM1

         house_num1_016(010),

* data element: AD_CITY2

         city2_017(040),

* data element: AD_PSTCD1

         post_code1_018(010),

* data element: AD_CITY1

         city1_019(040),

* data element: LAND1

         country_020(003),

* data element: REGIO

         region_021(003),

* data element: SPRAS

         langu_022(002),

* data element: AD_TLNMBR1

         tel_number_023(030),

* data element: AD_MBNMBR1

         mob_number_024(030),

* data element: AD_FXNMBR1

         fax_number_025(030),

* data element: AD_SMTPADR

         smtp_addr_026(132),

* data element: CIVVE

*        CIVVE_027(001),

* data element: AKONT

         akont_028(010),

* data element: DZTERM

         zterm_029(004),

* data element: AWAHR

         awahr_030(003),

* data element: VKBUR

         vkbur_031(004),

* data element: VKGRP

         vkgrp_032(003),

* data element: KDGRP

         kdgrp_033(002),

* data element: WAERS_V02D

         waers_034(005),

* data element: KALKS

         kalks_035(001),

* data element: LPRIO

         lprio_036(002),

* data element: KZAZU_D

         kzazu_037(001),

* data element: VSBED

         vsbed_038(002),

* data element: ANTLF

         antlf_039(001),

* data element: INCO1

         inco1_040(003),

* data element: INCO2

         inco2_041(028),

* data element: DZTERM

         zterm_042(004),

* data element: TAKLD

         taxkd_01_043(001),

         vwerk_041(004),

         ktgrd_046(002),

** data element: TAKLD

*        taxkd_01_047(001),

         er_message(100),           "error message

       END OF record.

*** End generated data section ***

DATA:   bdcdata LIKE bdcdata    OCCURS 0 WITH HEADER LINE,

         messtab LIKE bdcmsgcoll OCCURS 0 WITH HEADER LINE,

         messtab1 LIKE bdcmsgcoll OCCURS 0 WITH HEADER LINE.

DATA : it_error LIKE record OCCURS 0 WITH HEADER LINE.

*Internal table to store batch input field and screen details

*Table for Messages

DATA : i_msg LIKE bdcmsgcoll OCCURS 0 WITH HEADER LINE.

* Internal table to find the error from the legacy data*

DATA : BEGIN OF it_erfind OCCURS 0,

               er_message(100),         "For Error Message

        END OF it_erfind.

**************--variable--******************************************

DATA : g_mode(1) TYPE c VALUE 'E',

        g_ftext(200).

DATA : g_message(200),

        time(10)   ,

        date(10)  ,

        v_error_filename LIKE rlgrap-filename.

date      = sy-datum.

time      = sy-uzeit.

DATA : it_excel LIKE TABLE OF alsmex_tabline WITH HEADER LINE.

DATA : v_flag.

DATA: wa_ctu_params TYPE ctu_params.

* selection-screen

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

* selection-screen

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

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

PARAMETER: p_file LIKE ibipparms-path OBLIGATORY.

PARAMETER : p_mode TYPE c OBLIGATORY .

PARAMETERS: start TYPE i OBLIGATORY.

PARAMETERS: end TYPE i OBLIGATORY.

SELECTION-SCREEN SKIP.

SELECTION-SCREEN:END OF BLOCK b1.

* logic section

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

INITIALIZATION.

   wa_ctu_params-dismode = p_mode.

   wa_ctu_params-updmode = 'S'. "synchronus update

   wa_ctu_params-defsize = 'X'. "Default size

*generating error file name with date and time.

   PERFORM make_file_name.

* at selection-screen

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file .

   PERFORM get_filename.

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

* start-of-selection

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

START-OF-SELECTION.

   PERFORM upload.

   IF it_excel IS NOT INITIAL.

     PERFORM create_index.

     PERFORM fill_bdc_data.

   ENDIF.

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

* end-of-selection

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

END-OF-SELECTION.

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

*  PERFORM excel_download.

   PERFORM excel.

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

* subroutines

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

FORM upload.

   REFRESH it_excel.CLEAR it_excel.

   CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

     EXPORTING

       filename                = p_file

       i_begin_col             = 2

       i_begin_row             = start

       i_end_col               = 41

       i_end_row               = end

     TABLES

       intern                  = it_excel

     EXCEPTIONS

       inconsistent_parameters = 1

       upload_ole              = 2

       OTHERS                  = 3.

   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.                    "UPLOAD

*&---------------------------------------------------------------------*

*&      Form  CREATE_INDEX

*&---------------------------------------------------------------------*

FORM create_index.

   LOOP AT it_excel.

     CASE it_excel-col.

       WHEN '0001'.

         record-bukrs_001(004) = it_excel-value.

       WHEN '0002'.

         record-vkorg_002(004) = it_excel-value.

       WHEN '0003'.

         record-vtweg_003(002) = it_excel-value.

       WHEN '0004'.

         record-spart_004(002) = it_excel-value.

       WHEN '0005'.

         record-ktokd_005(004) = it_excel-value.

       WHEN '0006'.

         record-title_medi_007(030) = it_excel-value.

       WHEN '0007'.

         record-name1_008(040) = it_excel-value.

       WHEN '0008'.

         record-name2_009(040) = it_excel-value.

       WHEN '0009'.

         record-sort1_010(020) = it_excel-value.

       WHEN '0010'.

         record-sort2_011(020) = it_excel-value.

       WHEN '0011'.

         record-name_co_012(020) = it_excel-value.

       WHEN '0012'.

         record-str_suppl1_013(040) = it_excel-value.

       WHEN '0013'.

         record-str_suppl2_014(040) = it_excel-value.

       WHEN '0014'.

         record-street_015(060) = it_excel-value.

       WHEN '0015'.

         record-house_num1_016(008) = it_excel-value.

       WHEN '0016'.

         record-city2_017(040) = it_excel-value.

       WHEN '0017'.

         record-post_code1_018(010) = it_excel-value.

       WHEN '0018'.

         record-city1_019(040) = it_excel-value.

       WHEN '0019'.

         record-country_020(003) = it_excel-value.

       WHEN '0020'.

         record-region_021(003) = it_excel-value.

       WHEN '0021'.

         record-langu_022(002) = it_excel-value.

       WHEN '0022'.

         record-tel_number_023(030) = it_excel-value.

       WHEN '0023'.

         record-mob_number_024(030) = it_excel-value.

       WHEN '0024'.

         record-fax_number_025(030) = it_excel-value.

       WHEN '0025'.

         record-smtp_addr_026(132) = it_excel-value.

       WHEN '0026'.

         record-akont_028(010) = it_excel-value.

       WHEN '0027'.

         record-zterm_029(004) = it_excel-value.

       WHEN '0028'.

         record-vkbur_031(004) = it_excel-value.

       WHEN '0029'.

         record-vkgrp_032(003) = it_excel-value.

       WHEN '0030'.

         record-kdgrp_033(002) = it_excel-value.

       WHEN '0031'.

         record-waers_034(005) = it_excel-value.

       WHEN '0032'.

         record-kalks_035(001) = it_excel-value.

       WHEN '0033'.

         record-lprio_036(002) = it_excel-value.

       WHEN '0034'.

         record-vsbed_038(002) = it_excel-value.

       WHEN '0035'.

         record-vwerk_041(004) = it_excel-value.

       WHEN '0036'.

         record-inco1_040(003) = it_excel-value.

       WHEN '0037'.

         record-inco2_041(028) = it_excel-value.

       WHEN '0038'.

         record-zterm_042(004) = it_excel-value.

       WHEN '0039'.

         record-ktgrd_046(002) = it_excel-value.

       WHEN '0040'.

         record-taxkd_01_043(001) = it_excel-value.

     ENDCASE.

     AT END OF row.

       APPEND record.

       CLEAR record.

     ENDAT.

   ENDLOOP.

ENDFORM.                    " CREATE_INDEX

*------------------------------------------------------------------*

*&      Form FILL_BDC_DATA

*&---------------------------------------------------------------------*

*       text

*----------------------------------------------------------------------*

*  -->  p1        text

*  <--  p2        text

*----------------------------------------------------------------------*

FORM fill_bdc_data.

   LOOP AT record.

     PERFORM bdc_dynpro      USING 'SAPMF02D' '0100'.

     PERFORM bdc_field       USING 'BDC_CURSOR'

                                   'USE_ZAV'.

     PERFORM bdc_field       USING 'BDC_OKCODE'

                                   '/00'.

     PERFORM bdc_field       USING 'RF02D-BUKRS'

                                   record-bukrs_001.

     PERFORM bdc_field       USING 'RF02D-VKORG'

                                   record-vkorg_002.

     PERFORM bdc_field       USING 'RF02D-VTWEG'

                                   record-vtweg_003.

     PERFORM bdc_field       USING 'RF02D-SPART'

                                   record-spart_004.

     PERFORM bdc_field       USING 'RF02D-KTOKD'

                                   record-ktokd_005.

     PERFORM bdc_field       USING 'USE_ZAV'

                                   'X'.

     PERFORM bdc_dynpro      USING 'SAPMF02D' '0111'.

     PERFORM bdc_field       USING 'BDC_OKCODE'

                                     '/00' " 'ENTR'.

*    PERFORM bdc_field       USING 'BDC_CURSOR'

*                                  'ADDR1_DATA-CITY1'.

     PERFORM bdc_field       USING 'SZA1_D0100-TITLE_MEDI'

                                   record-title_medi_007.

     PERFORM bdc_field       USING 'ADDR1_DATA-NAME1'

                                   record-name1_008.

     PERFORM bdc_field       USING 'ADDR1_DATA-NAME2'

                                   record-name2_009.

     PERFORM bdc_field       USING 'ADDR1_DATA-SORT1'

                                   record-sort1_010.

     PERFORM bdc_field       USING 'ADDR1_DATA-SORT2'

                                   record-sort2_011.

     PERFORM bdc_field       USING 'ADDR1_DATA-NAME_CO'

                                   record-name_co_012.

     PERFORM bdc_field       USING 'ADDR1_DATA-STR_SUPPL1'

                                   record-str_suppl1_013.

     PERFORM bdc_field       USING 'ADDR1_DATA-STR_SUPPL2'

                                   record-str_suppl2_014.

     PERFORM bdc_field       USING 'ADDR1_DATA-STREET'

                                   record-street_015.

     PERFORM bdc_field       USING 'ADDR1_DATA-HOUSE_NUM1'

                                   record-house_num1_016.

     PERFORM bdc_field       USING 'ADDR1_DATA-CITY2'

                                   record-city2_017.

     PERFORM bdc_field       USING 'ADDR1_DATA-POST_CODE1'

                                   record-post_code1_018.

     PERFORM bdc_field       USING 'ADDR1_DATA-CITY1'

                                   record-city1_019.

     PERFORM bdc_field       USING 'ADDR1_DATA-COUNTRY'

                                   record-country_020.

     PERFORM bdc_field       USING 'ADDR1_DATA-REGION'

                                   record-region_021.

     PERFORM bdc_field       USING 'ADDR1_DATA-LANGU'

                                   record-langu_022.

     PERFORM bdc_field       USING 'SZA1_D0100-TEL_NUMBER'

                                   record-tel_number_023.

     PERFORM bdc_field       USING 'SZA1_D0100-MOB_NUMBER'

                                   record-mob_number_024.

     PERFORM bdc_field       USING 'SZA1_D0100-FAX_NUMBER'

                                   record-fax_number_025.

     PERFORM bdc_field       USING 'SZA1_D0100-SMTP_ADDR'

                                   record-smtp_addr_026.

     PERFORM bdc_dynpro      USING 'SAPMF02D' '0120'.

     PERFORM bdc_field       USING 'BDC_CURSOR'

                                   'KNA1-LIFNR'.

     PERFORM bdc_field       USING 'BDC_OKCODE'

                                   '/00'.

     PERFORM bdc_dynpro      USING 'SAPMF02D' '0125'.

     PERFORM bdc_field       USING 'BDC_CURSOR'

                                   'KNA1-NIELS'.

     PERFORM bdc_field       USING 'BDC_OKCODE'

                                   '/00'.

     PERFORM bdc_dynpro      USING 'SAPMF02D' '0130'.

     PERFORM bdc_field       USING 'BDC_CURSOR'

                                   'KNBK-BANKS(01)'.

     PERFORM bdc_field       USING 'BDC_OKCODE'

                                   '=ENTR'.

     PERFORM bdc_dynpro      USING 'SAPMF02D' '0340'.

     PERFORM bdc_field       USING 'BDC_CURSOR'

                                   'RF02D-KUNNR'.

     PERFORM bdc_field       USING 'BDC_OKCODE'

                                   '=ENTR'.

     PERFORM bdc_dynpro      USING 'SAPMF02D' '0370'.

     PERFORM bdc_field       USING 'BDC_CURSOR'

                                   'RF02D-KUNNR'.

     PERFORM bdc_field       USING 'BDC_OKCODE'

                                   '=ENTR'.

*perform bdc_field       using 'KNA1-CIVVE'

*                              record-CIVVE_027.

     PERFORM bdc_dynpro      USING 'SAPMF02D' '0360'.

     PERFORM bdc_field       USING 'BDC_CURSOR'

                                   'KNVK-NAMEV(01)'.

     PERFORM bdc_field       USING 'BDC_OKCODE'

                                   '=ENTR'.

     PERFORM bdc_dynpro      USING 'SAPMF02D' '0210'.

     PERFORM bdc_field       USING 'BDC_CURSOR'

                                   'KNB1-KNRZE'.

     PERFORM bdc_field       USING 'BDC_OKCODE'

                                   '/00'.

     PERFORM bdc_field       USING 'KNB1-AKONT'

                                   record-akont_028.

     PERFORM bdc_dynpro      USING 'SAPMF02D' '0215'.

     PERFORM bdc_field       USING 'BDC_CURSOR'

                                   'KNB1-ZTERM'.

     PERFORM bdc_field       USING 'BDC_OKCODE'

                                   '/00'.

     PERFORM bdc_field       USING 'KNB1-ZTERM'

                                   record-zterm_029.

     PERFORM bdc_dynpro      USING 'SAPMF02D' '0220'.

     PERFORM bdc_field       USING 'BDC_CURSOR'

                                   'KNB5-MAHNA'.

     PERFORM bdc_field       USING 'BDC_OKCODE'

                                   '/00'.

     PERFORM bdc_dynpro      USING 'SAPMF02D' '0230'.

     PERFORM bdc_field       USING 'BDC_CURSOR'

                                   'KNB1-VRSNR'.

     PERFORM bdc_field       USING 'BDC_OKCODE'

                                   '/00'.

     PERFORM bdc_dynpro      USING 'SAPMF02D' '0310'.

     PERFORM bdc_field       USING 'BDC_CURSOR'

                                   'KNVV-KALKS'.

     PERFORM bdc_field       USING 'BDC_OKCODE'

                                   '/00'.

     PERFORM bdc_field       USING 'KNVV-AWAHR'

                                   record-awahr_030.

     PERFORM bdc_field       USING 'KNVV-VKBUR'

                                   record-vkbur_031.

     PERFORM bdc_field       USING 'KNVV-VKGRP'

                                   record-vkgrp_032.

     PERFORM bdc_field       USING 'KNVV-KDGRP'

                                   record-kdgrp_033.

     PERFORM bdc_field       USING 'KNVV-WAERS'

                                   record-waers_034.

     PERFORM bdc_field       USING 'KNVV-KALKS'

                                   record-kalks_035.

     PERFORM bdc_dynpro      USING 'SAPMF02D' '0315'.

     PERFORM bdc_field       USING 'BDC_CURSOR'

                                   'KNVV-VSBED'.

     PERFORM bdc_field       USING 'BDC_OKCODE'

                                   '/00'.

     PERFORM bdc_field       USING 'KNVV-LPRIO'

                                   record-lprio_036.

     PERFORM bdc_field       USING 'KNVV-KZAZU'

                                   record-kzazu_037.

     PERFORM bdc_field       USING 'KNVV-VSBED'

                                   record-vsbed_038.

     PERFORM bdc_field       USING 'KNVV-VWERK'

                                   record-vwerk_041.

     PERFORM bdc_field       USING 'KNVV-ANTLF'

                                   record-antlf_039.

     PERFORM bdc_dynpro      USING 'SAPMF02D' '0320'.

     PERFORM bdc_field       USING 'BDC_CURSOR'

                                   'KNVV-INCO2'.

     PERFORM bdc_field       USING 'BDC_OKCODE'

                                   '/00'.

     PERFORM bdc_field       USING 'KNVV-INCO1'

                                   record-inco1_040.

     PERFORM bdc_field       USING 'KNVV-INCO2'

                                   record-inco2_041.

     PERFORM bdc_field       USING 'KNVV-ZTERM'

                                   record-zterm_042.

     PERFORM bdc_dynpro      USING 'SAPMF02D' '1350'.

     PERFORM bdc_field       USING 'BDC_CURSOR'

                                   'KNVI-TAXKD(01)'.

     PERFORM bdc_field       USING 'BDC_OKCODE'

                                   '=ENTR'.

     PERFORM bdc_field       USING 'KNVI-TAXKD(01)'

                                   record-taxkd_01_043.

     PERFORM bdc_dynpro      USING 'SAPMF02D' '1350'.

     PERFORM bdc_field       USING 'BDC_CURSOR'

                                   'KNVI-TAXKD(01)'.

     PERFORM bdc_field       USING 'BDC_OKCODE'

                                   '=ENTR'.

     PERFORM bdc_dynpro      USING 'SAPMF02D' '0324'.

     PERFORM bdc_field       USING 'BDC_CURSOR'

                                   'KNVP-PARVW(01)'.

     PERFORM bdc_field       USING 'BDC_OKCODE'

                                   '=ENTR'.

     CALL TRANSACTION 'XD01' USING bdcdata MODE p_mode

                           MESSAGES INTO messtab.

     REFRESH bdcdata.

     CLEAR bdcdata.

*    to fetch the error message from the standard error table

     SELECT SINGLE * FROM t100 WHERE sprsl = 'E'

                              AND arbgb = sy-msgid

                              AND msgnr = sy-msgno.

     g_message = t100-text.

*subroutine to change the error message for every document number

     PERFORM replace_parameters  USING sy-msgv1

                                       sy-msgv2

                                       sy-msgv3

                                       sy-msgv4

                             CHANGING g_message.

*to find out the error in the legacy data if there is anything and pass

*the document no with error message to the seperate internal table

*called it_erfind

     IF sy-msgty IS NOT INITIAL.  "= 'E' OR sy-msgty = 'A'.

       it_erfind-er_message = g_message.

       APPEND it_erfind.

       CLEAR  it_erfind.

     ENDIF.

*Finally we are segregating the error and downloading the error data.

     AT LAST.

       PERFORM display_message.

*To segregate the error

*      PERFORM segregate_error.

*To download the error from it_error internal table with err mesg

*--------------------------------------------------------------------

       PERFORM error_download.

*To download the error from it_error internal table with err mesg

     ENDAT.

   ENDLOOP.

   CLEAR record.

ENDFORM.   " FILL_BDC_DATA

*Subrotine to replace the parameters

*--------------------------------------------------------------------

FORM replace_parameters USING p_par_1 p_par_2 p_par_3

                         p_par_4 CHANGING p_message.

*erst mal pruefen, ob numerierte Parameter verwendet wurden

*--------------------------------------------------------------------

   DO.

     REPLACE '&1' WITH p_par_1 INTO p_message.

     IF sy-subrc <> 0.

       EXIT.

     ENDIF.

   ENDDO.

   DO.

     REPLACE '&2' WITH p_par_2 INTO p_message.

     IF sy-subrc <> 0.

       EXIT.

     ENDIF.

   ENDDO.

   DO.

     REPLACE '&3' WITH p_par_3 INTO p_message.

     IF sy-subrc <> 0.

       EXIT.

     ENDIF.

   ENDDO.

   DO.

     REPLACE '&4' WITH p_par_4 INTO p_message.

     IF sy-subrc <> 0.

       EXIT.

     ENDIF.

   ENDDO.

*falls keine numerierten Parameter vorh., ersetzen wie gehabt

*-------------------------------------------------------------------

   REPLACE '&' WITH p_par_1 INTO p_message.

   CONDENSE p_message.

   IF sy-subrc EQ 0.

     REPLACE '&' WITH p_par_2 INTO p_message.

     CONDENSE p_message.

     IF sy-subrc EQ 0.

       REPLACE '&' WITH p_par_3 INTO p_message.

       CONDENSE p_message.

       IF sy-subrc EQ 0.

         REPLACE '&' WITH p_par_4 INTO p_message.

         CONDENSE p_message.

       ENDIF.

     ENDIF.

   ENDIF.

   PERFORM jump.

ENDFORM.                               "replace_parameters

*Subroutine to download the error data from the it_error table.

*--------------------------------------------------------------------

FORM error_download.

*  IF NOT it_erfind[] IS INITIAL.

*    CALL FUNCTION 'WS_DOWNLOAD'

*      EXPORTING

*        codepage = 'IBM'(001)

*        filename = v_error_filename

*        filetype = 'DAT'                    "DAT

*      TABLES

*        data_tab = itab.

*  ENDIF.

ENDFORM.                               "error_download

*----------------------------------------------------------------------*

FORM bdc_dynpro  USING  program dynpro.

   CLEAR bdcdata.

   bdcdata-program  = program.

   bdcdata-dynpro   = dynpro.

   bdcdata-dynbegin = 'X'.

   APPEND bdcdata.

ENDFORM.                    " bdc_dynpro

*&---------------------------------------------------------------------*

*&      Form  bdc_field

*&---------------------------------------------------------------------*

*       text

*----------------------------------------------------------------------*

*      -->P_0184   text

*      -->P_0185   text

*----------------------------------------------------------------------*

FORM bdc_field  USING   fnam fval.

   CLEAR bdcdata.

   bdcdata-fnam = fnam.

   bdcdata-fval = fval.

   APPEND bdcdata.

ENDFORM.                    " bdc_field

*&---------------------------------------------------------------------*

*&      Form  get_filename

*&---------------------------------------------------------------------*

*       text

*----------------------------------------------------------------------*

FORM get_filename.

   CALL FUNCTION 'F4_FILENAME'

     EXPORTING

       program_name  = syst-repid

       dynpro_number = syst-dynnr

*     FIELD_NAME    = ' '

     IMPORTING

       file_name     = p_file

     EXCEPTIONS

       OTHERS        = 1.

   IF sy-subrc NE .

     WRITE : / 'Enter File Name'.

   ENDIF.

ENDFORM.                    " GET_FILENAME

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

**FORM FOR GENERATE FILE NAME

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

FORM make_file_name .

*  WRITE sy-datum TO date  MM/DD/YYYY.

*  WRITE sy-uzeit TO time  USING EDIT MASK ' __ __ __'.

*  CONCATENATE text-007 date time '.txt'

*  INTO v_error_filename.

ENDFORM.                               " make_file_name

*&-------------------------------------------------------------------

*&      Form  display_message

*&-------------------------------------------------------------------

FORM display_message .

   DATA:event    TYPE slis_t_event.

   DATA: levent TYPE slis_alv_event.

   CLEAR levent.

   levent-name = 'TOP_OF_PAGE'.

   levent-form = 'F_REPORT_HEADER_ALV'.

   APPEND levent TO event.

   it_ltab-window_titlebar    = 'BDC Data Status Report'.

   it_ltab-zebra = 'X'.

   it_ltab-colwidth_optimize = 'X'.

   wa_fcat-fieldname = 'COUNT'.

   wa_fcat-tabname   = 'ITAB'.

   wa_fcat-seltext_l = 'S.NO'.

   APPEND wa_fcat TO it_fcat.

   CLEAR wa_fcat.

   wa_fcat-fieldname = 'ID'.

   wa_fcat-tabname   = 'ITAB'.

   wa_fcat-seltext_l = 'ID'.

   APPEND wa_fcat TO it_fcat.

   CLEAR wa_fcat.

*  wa_fcat-fieldname = 'CCODE'.

*  wa_fcat-tabname   = 'ITAB'.

*  wa_fcat-seltext_l = 'Company Code'.

*  APPEND wa_fcat TO it_fcat.

*  CLEAR wa_fcat.

   wa_fcat-fieldname = 'NAME1'.

   wa_fcat-tabname   = 'ITAB'.

   wa_fcat-seltext_l = 'Customer Name'.

   APPEND wa_fcat TO it_fcat.

   CLEAR wa_fcat.

   wa_fcat-fieldname = 'MSG'.

   wa_fcat-tabname   = 'ITAB'.

   wa_fcat-seltext_l = 'Message'.

   APPEND wa_fcat TO it_fcat.

   CLEAR wa_fcat.

   wa_fcat-fieldname = 'PARA'.

   wa_fcat-tabname   = 'ITAB'.

   wa_fcat-seltext_l = 'Success or Error'.

   APPEND wa_fcat TO it_fcat.

   CLEAR wa_fcat.

*  DATA repid TYPE sy-repid.

*  repid = sy-repid.

   CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

    EXPORTING

*                          I_INTERFACE_CHECK                 = ' '

*                          I_BYPASSING_BUFFER                = ' '

*                          I_BUFFER_ACTIVE                   = ' '

                           i_callback_program                = sy-cprog

*                          I_CALLBACK_PF_STATUS_SET          = ' '

*                          I_CALLBACK_USER_COMMAND           = ' '

*                          I_CALLBACK_TOP_OF_PAGE            = 'BASTIN'

*                          I_CALLBACK_HTML_TOP_OF_PAGE       = ' '

*                          I_CALLBACK_HTML_END_OF_LIST       = ' '

*                          I_STRUCTURE_NAME                  =

                           i_background_id                   = 'ALV_BACKGROUND'

                           i_grid_title                      = 'List of ALV outputs after upload whether Data has been uploaded or not'

*                          I_GRID_SETTINGS                   =

                           is_layout                         = it_ltab

                           it_fieldcat                       = it_fcat

*                          IT_EXCLUDING                      =

*                          IT_SPECIAL_GROUPS                 =

*                          IT_SORT                           =

*                          IT_FILTER                         =

*                          IS_SEL_HIDE                       =

                           i_default                         = 'X'

                           i_save                            = 'X'

*                          IS_VARIANT                        =

                           it_events                         = event[]

*                          IT_EVENT_EXIT                     =

*                          IS_PRINT                          =

*                          IS_REPREP_ID                      =

*                          I_SCREEN_START_COLUMN             = 0

*                          I_SCREEN_START_LINE               = 0

*                          I_SCREEN_END_COLUMN               = 0

*                          I_SCREEN_END_LINE                 = 0

*                          I_HTML_HEIGHT_TOP                 = 0

*                          I_HTML_HEIGHT_END                 = 0

*                          IT_ALV_GRAPHICS                   =

*                          IT_HYPERLINK                      =

*                          IT_ADD_FIELDCAT                   =

*                          IT_EXCEPT_QINFO                   =

*                          IR_SALV_FULLSCREEN_ADAPTER        =

*                        IMPORTING

*                          E_EXIT_CAUSED_BY_CALLER           =

*                          ES_EXIT_CAUSED_BY_USER            =

     TABLES

       t_outtab                          = itab

                         EXCEPTIONS

                           program_error                     = 1

                           OTHERS                            = 2

             .

   IF sy-subrc <> 0.

* Implement suitable error handling here

   ENDIF.

ENDFORM.                    "display_message

*&---------------------------------------------------------------------*

*&

*&---------------------------------------------------------------------*

*       text

*----------------------------------------------------------------------*

*  -->  p1        text

*  <--  p2        text

*----------------------------------------------------------------------*

FORM jump .

   IF sy-msgty =  'I' OR sy-msgty =  'S' .

     SELECT SINGLE id FROM icon INTO itab-id WHERE id = '@08@'.

     itab-para = 'Successfully Created'.

     itab-name1 = record-name1_008.

   ELSEIF sy-msgty = 'E' OR sy-msgty =  'A'.

     SELECT SINGLE id FROM icon INTO itab-id WHERE id = '@0A@'.

     itab-para = 'Error Occured'.

     itab-name1 = record-name1_008.

   ELSEIF sy-msgty = 'W'.

     SELECT SINGLE id FROM icon INTO itab-id WHERE id = '@09@'.

   ENDIF.

   DATA: count TYPE i.

   itab-count = itab-count + 1.

*  itab-ccode = sy-msgv2.

   itab-msg = g_message.

   APPEND itab.

ENDFORM.                    "BASTIN

*&---------------------------------------------------------------------*

*&      Form  ****

*&---------------------------------------------------------------------*

*       text

*----------------------------------------------------------------------*

*  -->  p1        text

*  <--  p2        text

*----------------------------------------------------------------------*

*FORM excel_download.

*

*

*  WRITE sy-datum TO date  MM/DD/YYYY.

*  WRITE sy-uzeit TO time  USING EDIT MASK ' __ __ __'.

*  CONCATENATE text-007 date time '.xls'

*  INTO v_error_filename.

*

*  CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'

*    EXPORTING

**   I_FIELD_SEPERATOR          =

**   I_LINE_HEADER              =

*      i_filename                 = v_error_filename   "'C:\Users\sony\Desktop\BASTINBHARAT.xls'

**   I_APPL_KEEP                = ' '

*    TABLES

*      i_tab_sap_data             = itab[]

** CHANGING

**   I_TAB_CONVERTED_DATA       =

*   EXCEPTIONS

*     conversion_failed          = 1

*     OTHERS                     = 2

*            .

*  IF sy-subrc <> 0.

** Implement suitable error handling here

*  ENDIF.

*

*

*ENDFORM.                    "****

**CALL FUNCTION 'GUI_DOWNLOAD'.

*REPORT  zexcel_code.

" GET_DATA

*&---------------------------------------------------------------------*

*&      Form  EXCEL

*&---------------------------------------------------------------------*

*       text

*----------------------------------------------------------------------*

*  -->  p1        text

*  <--  p2        text

*----------------------------------------------------------------------*

FORM excel .

******************************Tables***********************************

   TABLES: objk, aufk.

   TYPE-POOLS: ole2.

******************************Types*************************************

   TYPES: BEGIN OF ty_ser,

             kunnr TYPE kna1-kunnr,

             name1 TYPE kna1-name1,

             sortl TYPE kna1-sortl,

             ort01 TYPE kna1-ort01,

             land1 TYPE kna1-land1,

             pstlz TYPE kna1-pstlz,

             telf1 TYPE kna1-telf1,

             telf2 TYPE kna1-telf2,

             error(100),

*            err_name type kna1-name1,

*          mcod3

           END OF ty_ser.

   TYPES: BEGIN OF ty_titles,

           title(20) TYPE c,

           field(20) TYPE c,

           END OF ty_titles.

***************************Data Declaration*****************************

   DATA: it_ser   TYPE TABLE OF ty_ser,

          wa_ser   TYPE ty_ser,

          v_lines  TYPE i,

          it_title TYPE STANDARD TABLE OF ty_titles.

*         wa_ser like line of it_Ser.

   DATA: fm_name  TYPE rs38l_fnam,

          v_line   TYPE sy-tabix,

          v_tabix  TYPE sy-tabix,

          v_titles TYPE sy-tabix,

          v_field  TYPE string,

          filename TYPE string,

          path     TYPE string,

          fullpath TYPE string,

          v_text   TYPE string,

          v_file   TYPE rlgrap-filename.

   DATA: e_sheet TYPE ole2_object,

          e_activesheet TYPE ole2_object,

          e_newsheet TYPE ole2_object,

          e_appl TYPE ole2_object,

          e_work TYPE ole2_object,

          e_cell TYPE ole2_object,

          e_color TYPE ole2_object,

          e_bold TYPE ole2_object.

   DATA: data_titles TYPE REF TO data.

   FIELD-SYMBOLS: <fs_spfli> LIKE LINE OF it_ser,

                   <fs_titles> LIKE LINE OF it_title,

                   <fs> TYPE any.

   LOOP AT itab INTO wa .

     wa-kunnr = wa-msg+09(11) .

     itab-kunnr = wa-kunnr.

     SELECT SINGLE  * FROM kna1 INTO CORRESPONDING FIELDS OF wa_ser WHERE kunnr = itab-kunnr.

     wa_ser-error = wa-para.

     IF wa-id EQ '@0A@'.

       wa_ser-error    = g_message.

*     if  wa_ser-error eq 'Error Occured'.

*       wa_Ser-error = wa-msg.

*       endif.

       wa_ser-name1 = wa-name1.

     ENDIF.

     MODIFY itab.

     APPEND wa_ser TO it_ser.

     CLEAR: wa,wa_ser.

   ENDLOOP.

   CREATE DATA data_titles TYPE ty_titles.

   ASSIGN data_titles->* TO <fs_titles>.

   <fs_titles>-title = 'Customer Number'.

   <fs_titles>-field = 'KUNNR'.

   APPEND <fs_titles> TO it_title.

   <fs_titles>-title = 'Name'.

   <fs_titles>-field = 'NAME1'.

   APPEND <fs_titles> TO it_title.

   <fs_titles>-title = 'Search Term'.

   <fs_titles>-field = 'SORTL'.

   APPEND <fs_titles> TO it_title.

   <fs_titles>-title = 'State'.

   <fs_titles>-field = 'ORT01'.

   APPEND <fs_titles> TO it_title.

   <fs_titles>-title = 'COUNTRY'.

   <fs_titles>-field = 'LAND1'.

   APPEND <fs_titles> TO it_title.

   <fs_titles>-title = 'PIN CODE'.

   <fs_titles>-field = 'PSTLZ'.

   APPEND <fs_titles> TO it_title.

   <fs_titles>-title = 'TEL_NO'.

   <fs_titles>-field = 'TELF1'.

   APPEND <fs_titles> TO it_title.

   <fs_titles>-title = 'MOB_NO'.

   <fs_titles>-field = 'TELF2'.

   APPEND <fs_titles> TO it_title.

   <fs_titles>-title = 'Error Message'.

   <fs_titles>-field = 'ERROR'.

   APPEND <fs_titles> TO it_title.

*      <fs_titles>-title = 'Error Customer Name'.

*    <fs_titles>-field = 'ERR_NAME'.

*    APPEND <fs_titles> TO it_title.

   CALL METHOD cl_gui_frontend_services=>file_save_dialog

     EXPORTING

       window_title      = 'Select archive'

       default_extension = 'xlsx'

       file_filter       = '*.xlsx'

     CHANGING

       filename          = filename

       path              = path

       fullpath          = fullpath.

   v_line = 1.

   CREATE OBJECT e_appl 'EXCEL.APPLICATION'.

   SET PROPERTY OF e_appl 'VISIBLE' = 1.

   CALL METHOD OF

       e_appl

       'WORKBOOKS' = e_work.

   CALL METHOD OF

       e_work

       'Add'  = e_work.

   GET PROPERTY OF e_appl 'ActiveSheet' = e_activesheet.

   SET PROPERTY OF e_activesheet 'Name' = sy-datum.

   LOOP AT it_ser ASSIGNING <fs_spfli>.

     v_tabix = sy-tabix.

     v_line = v_line + 1.

     LOOP AT it_title ASSIGNING <fs_titles>.

       v_titles = sy-tabix.

       CALL METHOD OF

           e_appl

           'Cells' = e_cell

         EXPORTING

           #1      = 1

           #2      = v_titles.

       SET PROPERTY OF e_cell 'Value' =  <fs_titles>-title.

       GET PROPERTY OF e_cell 'Interior' = e_color.

       SET PROPERTY OF e_color 'ColorIndex' = 35.

       GET PROPERTY OF e_cell 'Font' = e_bold.

       SET PROPERTY OF e_bold 'Bold' = 1.

       CALL METHOD OF

           e_appl

           'Cells' = e_cell

         EXPORTING

           #1      = v_line

           #2      = v_titles.

       CONCATENATE '<fs_spfli>-' <fs_titles>-field

       INTO v_field.

       ASSIGN (v_field) TO <fs>.

       IF v_titles = 2.

         SET PROPERTY OF e_cell 'NumberFormat' = '@'.

       ENDIF.

       SET PROPERTY OF e_cell 'Value' = <fs>.

       GET PROPERTY OF e_cell 'Interior' = e_color.

       SET PROPERTY OF e_cell 'ColumnWidth' = 25.

       SET PROPERTY OF e_color 'ColorIndex' = 0.

       GET PROPERTY OF e_cell 'Font' = e_bold.

       SET PROPERTY OF e_bold 'Bold' = 0.

     ENDLOOP.

   ENDLOOP.

   CALL METHOD OF

       e_work

       'SAVEAS'

     EXPORTING

       #1       = fullpath.

   FREE OBJECT e_appl.

*  ENDLOOP.

*  IF it_ser[] IS INITIAL.

*

*    MESSAGE 'ERROR OCCURED PLS CHECK THE DATA' TYPE 'I'.

*

*  ENDIF.

*ENDFORM.                    "get_data

ENDFORM.                    " EXCEL

Regards,

Bastin Vinoth NG

kesavadas_thekkillath
Active Contributor
0 Kudos
306

Moderator Message: Please try to search for the available information before posting.