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

[OttoGold]

 

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