Skip to Content

 

In the first part of this blog (Happy reporting with (ABAP OLE) Excel) I have introduced how you can enhance your Excel reporting using Perl source codes for Excel OLE. The resulting codes in ABAP are very easy to maintain and if your connection is fast, then it is a fast solution. But there are also some disadvantages. To name the three most annoying:

    Sometimes you don´t have a fast connection (or computer etc.), then the performance is not very good and you have to wait quite a while

    You cannot use this approach on background, what is a problem for example when you need to send the result as an email attachment or it is a nightly export for example.

    If you would like to have the resulting Excel precisely formatted and this <u>formatting is needed to be complicated</u> (not only the mentioned column width and color or value pattern), you would grow grey to do that manually.

    So I am back to present another approach which solves all the mentioned problems of ABAP/ Excel OLE. Hope you can forgive me, this blog will be less ABAP and more about a creative approach and MS Excel with a little use of XSL transformation in ABAP.

    To give you a complete overview I must tell you the secret here and not at the very end of the blog. There is a well documented XML standard how to create Office (Excel in this blog) documents. To get a basic overview check the Wikipedia article: Microsoft Office XML formats .

    As you can see in the attached documents, the whole Excel document is a simple XML where you only (at the beginning) tell the system to open it in Excel:

    Now you ask yourself how can you create such a document, I didn´t tell you anything about the tags, right? The best thing about this approach is you don´t care about most of the tags. Open your MS Excel, do not change the document, pick Save as… and save it to your desktop – the magic is to pick the “XML Spreadsheet (*.xml)” (I am sorry here, but I don´t have English Excel so I cannot write the proper file type description).

    Now open your document in the Notepad and note you need to (for a most simple version) read only two following sections and you can ignore all the rest:

    <Styles> = where there are all Styles used in the document (all coloring, bolding, underlining, size changes, alignments etc.)

    <Table> with <Row> … </Row> = where you can find all the data

    Based on the named facts we will do the following:

      1. Create the needed styles/ cells formatting
      2. Create a shot and reusable ABAP report
      3. Create a XSL transformation
      4. Create a data filling XSLT section

    When I asked you to save the blank spreadsheet and open it in the Notepad you could have noticed that in the Styles section there was only one style:

    To get more styles just edit that Spreadsheet (change fields alignments, colors etc.) and save it again. Now you can see some more styles in the Styles section. You will only need to copy all the XML “code” into the XSL transformation and in the data filling section you will only use the names of the styles as needed. Yes, it is that simple, just format the Spreadsheet the way you want (without the data). Create styles – checked!

    Create the ABAP report. In the report you need to have these sections:

      1. Call transformation (we didn´t create one yet!)
      2. Save (lets save it to the client PC): the best approach is to use cl_gui_frontend_services=>file_save_dialog and gui_download (remember to use filetype = ‘BIN’.
    REPORT  zexcel.
    
     DATA lt_test TYPE TABLE OF /xiting/rp_alv_grid_agr_tcd.
     DATA lt_itab TYPE STANDARD TABLE OF text2048.
    
     CALL TRANSFORMATION zxiting_dev_martin1
     SOURCE data = lt_test[]
     RESULT XML lt_itab.
    
     DATA lv_def_filename TYPE string.
     DATA lv_filename TYPE string.
     DATA lv_path TYPE string.
     DATA lv_fullpath TYPE string.
     DATA lv_user_action  TYPE i.
    
     *** ask for real filename and location to save there
     CALL METHOD cl_gui_frontend_services=>file_save_dialog
       EXPORTING
         default_extension    = 'xls'
         default_file_name    = lv_def_filename
         file_filter          = 'Text Files (*.xls)|*.xls|All Files (*.*)|*.*|' "#EC NOTEXT
       CHANGING
         filename             = lv_filename
         path                 = lv_path
         fullpath             = lv_fullpath
         user_action          = lv_user_action
       EXCEPTIONS
         cntl_error           = 1
         error_no_gui         = 2
         not_supported_by_gui = 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 lv_user_action EQ '0'.
       CALL METHOD cl_gui_frontend_services=>gui_download
         EXPORTING
           filename = lv_fullpath
           filetype = 'BIN'
         CHANGING
           data_tab = lt_itab
         EXCEPTIONS
           OTHERS   = 24.
       IF sy-subrc <> 0.
         MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
       ELSE.
       ENDIF.
     ENDIF.

    Again, it is that simple. ABAP report – checked!

    Now we need to create the XSL transformation the report will call. If you have never done that, find a tutorial, there are quite many on the Internet. You need to copy the XML “code” of the Spreadsheet (opened in the Notepad) and paste it to the transformation and make few enhancements. Add the necessary tags to make that valid transformation (like The last part is to find the right places in the transformation where the data are to be filled in

    To report this post you need to login first.

    6 Comments

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

    1. Rosen Petrov

      Thanks for this post.

      A few months ago i have been using SAP Document interfaces:

      i_oi_document_proxy

      i_oi_spreadsheet

      which gives you quite easy access to MS Office documents. Problem with the “Spreadsheet Interface” i_oi_spreadsheet is the limitation of rows number – it is restricted to 4 digit integer, i.e. 9999.

      (0) 
      1. Otto Gold Post author

        The best thing you can get is abap2xslx by Ivan Femia and his squad. Unfortunatelly that`s a lot of Z-code that I am not going to import in my customers` systems. That`s why I prefer my XML way. Learning the abap2xsls way (at least the internal way of doing the job) will be useful though. Check code exchange.

        Cheers Otto

        (0) 

    Leave a Reply