Skip to Content
Technical Articles
Author's profile photo Otto Gold

Happy reporting with Excel II. (XML simple transformations to generate Excel)


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
     default_extension    = 'xls'
     default_file_name    = lv_def_filename
     file_filter          = 'Text Files (*.xls)|*.xls|All Files (*.*)|*.*|' "#EC NOTEXT
     filename             = lv_filename
     path                 = lv_path
     fullpath             = lv_fullpath
     user_action          = lv_user_action
     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.
 IF lv_user_action EQ '0'.
   CALL METHOD cl_gui_frontend_services=>gui_download
       filename = lv_fullpath
       filetype = 'BIN'
       data_tab = lt_itab
       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.

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

Assigned tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Clinton Jones
      Clinton Jones
      Thanks for posting this programatic approach to integrating EXCEL and SAP
      Author's profile photo Former Member
      Former Member
      good one...
      Author's profile photo Former Member
      Former Member
      Thanks for this very usefull blog!
      Author's profile photo Former Member
      Former Member
      I was looking for a way to generate Excell in background, and I didn´t kew how to do...
      Thank you for the good job...
      Author's profile photo Former Member
      Former Member

      Thanks for this post.

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



      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.

      Author's profile photo Otto Gold
      Otto Gold
      Blog 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