Skip to Content

The program developed uses OLE (Object link Enabling). The standard ABAP download module does not accommodate the format options to Excel. Also it downloads data into only one worksheet. Unlike the standard module, this function module has two features.

1) If a file already exists, it adds a new Tab(Worksheet) in the Excel.

2) If the file doesn’t exist, then it creates an excel sheet and downloads into it.

Also, you can name the worksheet(tabname in excel) created. Further following formatting options can be provided.

1) Giving a colour index to a cell: This is particularly useful while you upload a file, and you would like to show the errors in data of the file by changing the background colour of the cell.

2) Bold

3) Vertical Orientation: This will provide you an option to change the vertical orientation of text in the cell. For example, downloading text vertically(90 degrees). This orientation corresponds to the degree at which you want to rotate the data in the cell.

4) Comments: Adding comments to a cell. This is very useful while you use the colour index to show that it is an error and add a comment to say what exactly the error is.

You need to create one structure(named as ZFORMATOPTIONS in the example) with following fields.

Note: The text is in the following format

fieldname(Dataelement)- Description.

ROW(KCD_EX_ROW_N)- Number of the row to be formatted

COL(KCD_EX_COL_N)- Number of the col to be formatted

VERT(NUMC2)- Degree of rotation(0-90)

BOLD(BOOLE_D)- ‘X’ if you want to make text bold

COLOR(CHAR1)- Values 1 to 9

COMMENTS(CHAR256)- Free text that is added as a comment to the cell

You can also provide headings to the excel sheet through the table parameter ‘T_HEADING’ to the function module.

The function group’s TOP include, function module, Form include(F01) are placed below. Further a test program is also given to show how this function module could be used.

********************TOP INCLUDE Starts********************

FUNCTION-POOL ZTEST_OLE.                    “MESSAGE-ID ..

TYPE-POOLS: abap.

  • EXCEL sheet using OLE automation.

INCLUDE OLE2INCL.

DEFINE ole_error.

  IF NOT &1 IS INITIAL.

    MESSAGE e899(v1) WITH ‘OLE Error ='(002) &1

                     RAISING ole_error.

  ENDIF.

END-OF-DEFINITION.

TYPES:

  BEGIN OF ty_line,

    line(4096) TYPE c,

  END OF ty_line.

  CONSTANTS:

    c_tab   TYPE x VALUE 9,

    c_bgrw  TYPE i VALUE 1,

    c_bgcl  TYPE i VALUE 1.

*For EXCEL operations through ABAP

  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_format  TYPE ole2_object, “Object for format

    w_font    TYPE ole2_object,

    w_sheets  TYPE ole2_object, “Holds Active Sheet

    w_range   TYPE ole2_object, “To select a range

*For data processing

    it_line TYPE STANDARD TABLE OF ty_line,

    wa_line TYPE ty_line,

    w_field TYPE ty_line-line,

    w_tab   TYPE c.

  FIELD-SYMBOLS:

       TYPE ANY.

*******************TOP Include Ends***********************

*****************Function Module starts*******************

FUNCTION ztest_ole_single_table.

“—-


“*”Local interface:

*”  IMPORTING

*”     REFERENCE(FILENAME) TYPE  RLGRAP-FILENAME

*”     REFERENCE(TABNAME) TYPE  CHAR16 OPTIONAL

*”  TABLES

*”      T_DATA

*”      T_HEADING STRUCTURE  LINE OPTIONAL

*”      T_FORMATOPT STRUCTURE  ZFORMATOPTIONS OPTIONAL

*”  EXCEPTIONS

*”      OLE_ERROR

*”      DATA_EMPTY

*”      CLIPBOARD_EXPORT_ERROR

*”—-


  DATA:

    file_already_exists TYPE c.

  IF t_data[] IS INITIAL.

    MESSAGE e899(v1) WITH    ‘No Data in the internal table'(001)

                     RAISING data_empty.

  ENDIF.

  ASSIGN w_tab TO  = c_tab.

  REFRESH it_line.

  PERFORM prepare_int_tab TABLES t_data

                                 t_heading.

  PERFORM create_excel_sheet USING     filename

                                       tabname

                                       t_data

                             CHANGING  file_already_exists.

  CHECK NOT t_formatopt[] IS INITIAL.

  PERFORM format_cells TABLES t_formatopt

                       USING  filename

                              file_already_exists.

ENDFUNCTION.

****************Function Module Ends**********************

****************F01(Form Include) starts******************

*—-


***INCLUDE LZTEST_OLEF01 .

*—-


*&—-


*&      Form  prepare_int_tab

*&—-


  •       text

*—-


  • –>  p1        text

  • <–  p2        text

*—-


FORM prepare_int_tab TABLES it_data

                            it_heading STRUCTURE line.

  CLEAR wa_line.

  IF NOT it_heading[] IS INITIAL.

    LOOP AT it_heading.

      CONCATENATE wa_line-line

                  it_heading-line

                  w_tab

             INTO wa_line-line.

      CONDENSE wa_line.

    ENDLOOP.

    APPEND wa_line TO it_line.

  ENDIF.

  LOOP AT it_data.

    CLEAR wa_line.

    DO.

      ASSIGN COMPONENT sy-index OF STRUCTURE it_data TO .

      CONDENSE w_field.

      CONCATENATE wa_line-line

                  w_field

                  w_tab

             INTO wa_line-line.

      CONDENSE wa_line.

    ENDDO.

    APPEND wa_line TO it_line.

  ENDLOOP.

ENDFORM.                    ” prepare_int_tab

*&—-


*&      Form  create_excel_sheet

*&—-


  •       text

*—-


  • –>  p1        text

  • <–  p2        text

*—-


FORM create_excel_sheet USING    p_filename

                                 p_tabname

                                 w_data

                        CHANGING p_file_already_exists.

  DATA:

   l_cols TYPE i,

   l_rows TYPE i,

   l_name TYPE char16,

   l_rc   TYPE sy-subrc,

   l_res  TYPE abap_bool,

   l_type TYPE c,

   l_file TYPE string,

   l_from TYPE ole2_object,

   l_to   TYPE ole2_object,

   l_entcol TYPE ole2_object.

  CREATE OBJECT w_excel ‘Excel.Application’.

  ole_error sy-subrc.

  CALL METHOD OF w_excel ‘Workbooks’ = w_wbooks.

  ole_error sy-subrc.

  • SET PROPERTY OF w_excel ‘Visible’ = 1.

  ole_error sy-subrc.

  l_file = p_filename.

  CLEAR l_res.

  CALL METHOD cl_gui_frontend_services=>file_exist

    EXPORTING

      file            = l_file

    RECEIVING

      result          = l_res

    EXCEPTIONS

      cntl_error      = 1

      error_no_gui    = 2

      wrong_parameter = 3

      OTHERS          = 4

          .

  IF sy-subrc <> 0.

    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

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

  ENDIF.

  IF l_res IS INITIAL.

    CLEAR p_file_already_exists.

  ELSE.

    p_file_already_exists = ‘X’.

  ENDIF.

  IF NOT p_file_already_exists IS INITIAL.

  •   Open the existing file in case if it exists

    CALL METHOD  OF w_wbooks ‘Open’

      EXPORTING

        #1 = p_filename.

    ole_error sy-subrc.

    CALL METHOD OF w_excel ‘Sheets’ = w_sheets.

    ole_error sy-subrc.

    CALL METHOD OF w_sheets ‘Add’.

    ole_error sy-subrc.

    GET PROPERTY OF w_excel ‘ActiveSheet’ = w_wbook.

    ole_error sy-subrc.

  ELSE.

    CALL METHOD OF w_wbooks ‘Add’. ” = w_wbook.

    ole_error sy-subrc.

    GET PROPERTY OF w_excel ‘ActiveSheet’ = w_wbook.

    ole_error sy-subrc.

  ENDIF.

  IF NOT p_tabname IS INITIAL.

    SET PROPERTY OF w_wbook ‘Name’ = p_tabname.

    ole_error sy-subrc.

  ENDIF.

  CALL METHOD OF w_wbook ‘Cells’ = l_from

    EXPORTING

      #1 = c_bgrw

      #2 = c_bgcl.

  ole_error sy-subrc.

  DESCRIBE FIELD w_data TYPE l_type COMPONENTS l_cols.

  DESCRIBE TABLE it_line LINES l_rows.

  CALL METHOD OF w_wbook ‘Cells’ = l_to

    EXPORTING

      #1 = l_rows

      #2 = l_cols.

  ole_error sy-subrc.

  CALL METHOD OF w_wbook ‘Range’ = w_range

    EXPORTING

      #1 = l_from

      #2 = l_to.

  ole_error sy-subrc.

  CALL METHOD cl_gui_frontend_services=>clipboard_export

    IMPORTING

      data         = it_line

    CHANGING

      rc           = l_rc

    EXCEPTIONS

      cntl_error   = 1

      error_no_gui = 2

      OTHERS       = 3

          .

  IF sy-subrc <> 0

     OR NOT l_rc IS INITIAL.

    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

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

               RAISING clipboard_export_error.

  ENDIF.

  CALL METHOD OF w_range ‘Select’.

  ole_error sy-subrc.

  CALL METHOD OF w_wbook ‘Paste’.

  ole_error sy-subrc.

  WHILE l_cols GT 0.

    l_rows = 1.

    CALL METHOD OF w_excel ‘Columns’ = w_cell

      EXPORTING

        #1 = l_cols.

    ole_error sy-subrc.

    CALL METHOD OF w_cell ‘EntireColumn’ = l_entcol.

    ole_error sy-subrc.

    l_cols = l_cols – 1.

    CALL METHOD OF l_entcol ‘Autofit’.

    ole_error sy-subrc.

  ENDWHILE.

ENDFORM.                    ” create_excel_sheet

*&—-


*&      Form  format_cells

*&—-


  •       text

*—-


  •      –>P_FILENAME  text

  •      –>P_FILE_ALREADY_EXISTS  text

*—-


FORM format_cells TABLES   it_formatopt STRUCTURE zformatoptions

                  USING    p_filename TYPE rlgrap-filename

                           p_file_already_exists TYPE c.

  DATA:

    l_row       TYPE i,

    l_col       TYPE i,

    l_entcol    TYPE ole2_object,

    l_cols      TYPE ole2_object,

    l_comment   TYPE ole2_object.

  LOOP AT it_formatopt.

    CLEAR: l_row, l_col.

    l_row = it_formatopt-row.

    l_col = it_formatopt-col.

    CALL METHOD OF w_wbook ‘Cells’ = w_cell

        EXPORTING

          #1 = l_row

          #2 = l_col.

    ole_error sy-subrc.

    IF NOT it_formatopt-bold IS INITIAL.

      CALL METHOD OF w_cell ‘Font’ = w_font.

      ole_error sy-subrc.

      SET PROPERTY OF w_font ‘Bold’ = 1.

      ole_error sy-subrc.

      CALL METHOD OF w_excel ‘Columns’ = l_cols

       EXPORTING

         #1 = l_col.

      ole_error sy-subrc.

      CALL METHOD OF l_cols ‘EntireColumn’ = l_entcol.

      ole_error sy-subrc.

      CALL METHOD OF l_entcol ‘Autofit’.

      ole_error sy-subrc.

    ENDIF.

    IF NOT it_formatopt-color IS INITIAL.

      CALL METHOD OF w_cell ‘Interior’ = w_format.

      ole_error sy-subrc.

      SET PROPERTY OF w_format ‘ColorIndex’ = it_formatopt-color.

      ole_error sy-subrc.

      CALL METHOD OF w_excel ‘Columns’ = l_cols

       EXPORTING

         #1 = l_col.

      ole_error sy-subrc.

      CALL METHOD OF l_cols ‘EntireColumn’ = l_entcol.

      ole_error sy-subrc.

      CALL METHOD OF l_entcol ‘Autofit’.

      ole_error sy-subrc.

    ENDIF.

    IF NOT it_formatopt-vert IS INITIAL.

      SET PROPERTY OF w_cell ‘Orientation’ = it_formatopt-vert.

      ole_error sy-subrc.

      CALL METHOD OF w_excel ‘Columns’ = l_cols

       EXPORTING

         #1 = l_col.

      ole_error sy-subrc.

      CALL METHOD OF l_cols ‘EntireColumn’ = l_entcol.

      ole_error sy-subrc.

      CALL METHOD OF l_entcol ‘Autofit’.

      ole_error sy-subrc.

    ENDIF.

    IF NOT it_formatopt-comments IS INITIAL.

  •      CALL METHOD OF w_excel ‘Range’ = w_range

  •        EXPORTING

  •          #1 = l_row

  •          #2 = l_col.

  •      ole_error sy-subrc.

  •      CALL METHOD OF w_range ‘Select’.

  •      ole_error sy-subrc.

      CALL METHOD OF w_cell ‘AddComment’ = l_comment.

      ole_error sy-subrc.

      CALL METHOD OF l_comment ‘Text’

        EXPORTING

          #1 = it_formatopt-comments.

      ole_error sy-subrc.

    ENDIF.

  ENDLOOP.

  PERFORM save_and_close USING p_filename

                               p_file_already_exists.

ENDFORM.                    ” format_cells

*&—-


*&      Form  save_and_close

*&—-


  •       text

*—-


  •      –>P_P_FILENAME  text

  •      –>P_P_FILE_ALREADY_EXISTS  text

*—-


FORM save_and_close USING    p_filename

                             p_file_already_exists.

  IF p_file_already_exists IS INITIAL.

    CALL METHOD OF w_wbook ‘Saveas’

      EXPORTING

        #1 = p_filename.

    ole_error sy-subrc.

  ELSE.

    CALL METHOD OF w_excel ‘ActiveWorkbook’ = w_wbooks.

    ole_error sy-subrc.

    CALL METHOD OF w_wbooks ‘Save’.

    ole_error sy-subrc.

  ENDIF.

  CALL METHOD OF w_wbooks ‘Close’.

  ole_error sy-subrc.

ENDFORM.                    ” save_and_close

**************Form Include Ends***************************

*********************Test Progam**************************

REPORT ztest_ole.

TABLES mara.

SELECT-OPTIONS:

  s_matnr FOR mara-matnr.

PARAMETERS:

  p_file   TYPE rlgrap-filename,

  p_tabnm  TYPE char16.

DATA:

  BEGIN OF it_mara OCCURS 1,

    matnr TYPE mara-matnr, “Material No.

    mtart TYPE mara-mtart, “Material Type

    matkl TYPE mara-matkl, “Material Group

    groes TYPE mara-groes, “Size/Dimension

  END OF it_mara,

  it_heading TYPE STANDARD TABLE OF line,

  wa_heading TYPE line,

  it_formatopt TYPE STANDARD TABLE OF zformatoptions,

  wa_format  TYPE zformatoptions,

  l_col TYPE zformatoptions-col.

START-OF-SELECTION.

  SELECT matnr

         mtart

         matkl

         groes

    FROM mara

   UP TO 100 ROWS

    INTO TABLE it_mara.

  wa_heading-line = ‘Material No.’.   APPEND wa_heading TO it_heading.

  wa_heading-line = ‘Material Type’.  APPEND wa_heading TO it_heading.

  wa_heading-line = ‘Material Group’. APPEND wa_heading TO it_heading.

  wa_heading-line = ‘Size/Dimension’. APPEND wa_heading TO it_heading.

  DO 4 TIMES.

    CLEAR wa_format.

    wa_format-row = 1.

    wa_format-col = l_col + 1.

    l_col = l_col + 1.

    wa_format-bold = ‘X’.

    wa_format-color = ‘6’.

    wa_format-vert = 45.

    wa_format-comments = ‘This is a heading’.

    APPEND wa_format TO it_formatopt.

  ENDDO.

  CALL FUNCTION ‘ZTEST_OLE_SINGLE_TABLE’

       EXPORTING

            filename               = p_file

            tabname                = p_tabnm

       TABLES

            t_data                 = it_mara

            t_heading              = it_heading

            t_formatopt            = it_formatopt

       EXCEPTIONS

            ole_error              = 1

            data_empty             = 2

            clipboard_export_error = 3

            OTHERS                 = 4.

  IF sy-subrc <> 0.

    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

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

  ENDIF.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

  PERFORM get_file CHANGING p_file.

*&—-


*&      Form  get_file

*&—-


  •       text

*—-


  •      <–P_P_FILE  text

*—-


FORM get_file CHANGING p_file.

  DATA:

    l_file  TYPE string,

    l_path  TYPE string,

    l_fpath TYPE string.

  CALL METHOD cl_gui_frontend_services=>file_save_dialog

    CHANGING

      filename          = l_file

      path              = l_path

      fullpath          = l_fpath

    EXCEPTIONS

      cntl_error        = 1

      error_no_gui      = 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.

  p_file = l_fpath.

ENDFORM.                    ” get_file

*******************Test Program Ends**********************

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Former Member
    hi,
    its a really good solution.
    one thing i am facing is that i m not able to hide commnets in excel.
    Is it possible through code?
    thanks again for a good blong.

    jigs.

    (0) 
    1. Former Member
      Sorry had been a long time after could have revisited. Use the following code immediately after adding the comment:

      CALL METHOD OF l_comment ‘Text’
      EXPORTING
      #1 = it_formatopt-comments.
      ole_error sy-subrc.
      CALL METHOD OF l_comment ‘Visible’ = 0.

      (0) 
    1. Former Member
      Hi

        Sorry didnt visit this blog for long now. In background none of the GUI operations work as it is mandatory that the GUI functions are not available in background..

      (0) 
    1. Former Member
      Did you get any solution for formating an excel attachment?

      I have a requirement to generate an email with attachment and I am looking for some formating options.

      Any inputs will be helpful.

      Thanks,
      Amol

      (0) 

Leave a Reply