Monthly ABAP to EXCEL Blog – February 2023

[hardyp180]

Monthly ABAP to EXCEL Blog – February 2023

For over ten years now every month on the SAP Community Site someone publishes a blog about how to upload/download data from EXCEL to ABAP. So, I am going to start doing this as well - only I will always be talking about ABAP2XLSX as the preferred mechanism to do this.

Excel Blogs

The 120 blogs posted over the last ten years usually never mention ABAP2XLSX at all. They either talk about the archaic OLE technology that can be used to communicate with Microsoft products or re-invent the ABAP2XLSX concept.

Then myself and about three or four other people (the “usual suspects” as I call them) will post in the comments section talking about ABAP2XLSX and the original poster will either admit they had never heard of ABAP2XLSX or sometimes get all offended and say of course they had heard of it, just forgot to mention in their blog.

The most wonderful thing that happened this week was that as an experiment someone got CHAT GPT to write a blog on how to download from ABAP to Excel. It did a better job than some of the hundreds I have read.

Anyway, to fight back I am going to try an explain ABAP2XLSX as best I can and why it is a Good Thing.

The last blog in this new series I posted can be found at: -

https://blogs.sap.com/2023/01/12/monthly-abap-to-excel-blog-january-2023/

Footfall

Now in some ways I am going to start off by shooting myself in the foot. As well as putting example code I am going to also create a public GitHub repository so people can download the examples via abapGit.

This immediately begs the question – if the reason assorted people do not want to use ABAP2XLSX is because of a hatred/fear of anything remotely open-source plus a fictitious “no open-source” policy then why in the world would they go anywhere near GitHub in a billion years? The answer is of course they will not, but as it turns out the no open-source brigade have no problem at all copying code off an SCN blog into their development systems – after all if they had a problem with that concept then why in the world would they post their own code on SCN every month and expect other people to download that code to their development systems?

Monsieur Example-Mousse

A lot of examples on the internet – and indeed the standard ABAP2XLSX demo programs – are divorced from any actual use cases and consist of an executable program which does whatever is being demonstrated in as few lines as possible so as not to distract from the point being made.

That makes sense does it not? But I cannot do it. I cannot write a program, even as an example, which just has everything after START-OF-SELECTION in a big, coagulated mass without even any FORM routines, let alone methods.

So, I am going to shoot myself in the foot again and have an example OO program with methods. The reason that is such a bad decision of my part is that many of the opponents of ABAP2XLSX dislike it because it is all classes and methods and new-fangled nonsense like that.

However, if after 23 years every new ABAP code example on the internet is still made up of all FORM routines, then new people will still think FORM routines are the go.

The B-side is that for incredibly simple example OO programs take one billion times more code than the procedural code – leading the observation that whilst reading a blog about transforming a procedural program to the OO equivalent someone said “the <better> the code gets the longer and more complicated it is”. Meaning it is not better at all.

I would of course disagree on the grounds that in the real-world simple programs never stay simple – they expand at a geometric rate and once a program reaches a certain (quite small really) size then it is easier to maintain it going forward if it is written in an OO manner.

I will however make a slight compromise and use – for the example data - SFLIGHT as opposed to Monsters.

Use Casey Jones, Working on the Railroad

So as a starting point we are going to be displaying the good old SFLIGHT table in class CL_SALV_TABLE (which is not editable by the way, did I ever mention that?)

Much as the IT world hates it the truth is that 99.99% of data generated out of SAP reports ends up getting downloaded to Excel and then having all sorts of further processing done on that data at the Excel level. This is so called “Spreadsheet Hell” – so called by software vendors who are trying to convince you to stop using Excel altogether and buy their alternative instead.

As another alternative perhaps another way forward is to stop trying to be King Canute and ordering the tide to go back out but instead working with reality and improving the bi-directional integration between SAP and Excel.

With ABAP2XLSX you have three options once you have turned your ALV grid into a spreadsheet: -

• Show that spreadsheet “in place” embedded inside the SAP GUI


• Download the spreadsheet to your local PC


• Email the spreadsheet to yourself or someone else (who will then open the XLSX attachment and then download it to their local PC if they feel like it)

The standard ABAP2XLSX demo programs let you do all three, but in my example, I am going to focus on the emailing aspect. I have found in real life this is the most widely used scenario. Often the amount of data is too large to run the program online and then download it, and I have not used the “in-place” option enough to be able to talk with any authority about it.

So, the end user is going to input some SFLIGHT selection criteria, an ALV grid will appear if run in the foreground and if a box is ticked an email will be sent to the selected email address (even if the program is run in the background) and that email will contain an XLSX version of the ALV grid.

Over-Complicated, Overpaid, and Over Here

As mentioned before the initial version of the example program will seem like a violently over-complicated program, all full of classes that do one thing only (the so-called single responsibility principle or SRP which is the S in SOLID). Given that the program at this stage is doing hardly anything at all some people will throw their hands up in the air and walk away screaming “this OO nonsense arrived in ABAP in the year 2000 – no wonder no-one ever used it!”. The program is even going to have a unit test section – empty initially – leading to some other people screaming “this unit testing nonsense was developed in 1944 – no wonder no-one ever uses it!”.

Max Bygraves – I Wanna Tell You a GitHub Repository

The GitHub repository for the evolving demonstration program is going to be

hardyp/ABAP2XLSX_EXAMPLES: ABAP2XLSX Examples (github.com)

using local package $ABAP2XLSX_PDH_DEMO. There will be several versions of the same program (Z_ABAP2XLSX_PDH_DEMO) one per blog, and you can download them to your development system using abapGit (which again many people will hate the very idea of, even though SAP itself is starting to supply software via this medium e.g., the “Code Pal”)

Class Warfare

This is going to be an executable program comprised of a big bunch of local classes, with the entry point coming straight after START-OF-SELECTION.

• Model – responsible for all business logic (there will be none initially)


• Test Class – to do unit tests on the business logic in the model


• Persistency Layer – responsible for database access


• View – responsible for displaying the data – here we use CL_SALV_TABLE


• Controller – responsible for linking the model and the view. This class can handle user commands received from the view. If no business logic involved it can handle them itself, if business logic is involved it can call upon the model.


• Selection-Options – this (as might be expected) is responsible for storing the selection-options entered by the user. We do this because we do not like global variables and SELECT-OPTIONS are global variables. There have been half a zillion attempts on the SCN to do this in the best possible OO way, none of them optimal. My way is not optimal either, but I have yet to find a better way.


• Application – a class that acts like a conductor and makes all the other classes play their musical instruments properly in the correct order. In could be argued this is not strictly needed, I could have the controller do this, but what if one day the program gets so complex there are multiple MVC constructs, some of which are global?

This is nothing AT ALL do with ABAP2XLSX – You Fiend!

In my mind (such as it is) everything is interlinked – OO programming, Test Driven Development, ABAP2XLSX and so on. No-one is using any of it, and everybody needs to. Thus, I keep on about every aspect, all at once, forever, like a broken record.

You will have noted that in the above list of classes I have not even mentioned ABAP2XLSX at all yet, thus proving my madness once again. At first glance it is difficult to see where ABAP2XLSX fits into the MVC equation. We are going to be sending the data to the end user in the form of a spreadsheet – so that is like a view i.e., a representation of the data viewable by a human just like the GUI representation provided by CL_SALV_TABLE (which is not editable by the way). Yet the exact appearance of the spreadsheet e.g., conditional formatting and what have you is 100% based on business logic which is the job of the model. Put another way only the model knows if a value in a cell is invalid, but only the view knows how to colour that cell red.

Put like that the answer is obvious – the spreadsheet is another view i.e., another way of displaying the same data. Another view could be a Gateway link to a ten-foot-tall robot frog which tells you jokes about the data in the model and so on. The controller can use some sort of logic to decide which type of view is most appropriate to send the data to the end user. You could have multiple models and only one view, you could have only one model and lots of views. See how complicated this is? That might seem like madness at first glance, but it is a complicated world, and thus you need as much flexibility as you can.

View to a Kill

In “The Robots of Death” there was something like the MVC pattern. There was one “Super-Voc” which had all the business logic of the mining craft and controlled all the other robots. Then you have the “Vocs” (controllers) which obeyed the Super-Voc (model) but could also respond to user commands from the human crew. Lastly you had the “dums” (views) which actually did all the work but had no intelligence whatsoever they just did whatever the controllers told them, orders which could have come from the model or from user-commands received by the controller. In that story the robots killed all the humans but let’s not go there apart from that it is a good analogy.

Therefore, when I am designing an ALV report I put all the “smarts” in the model e.g., what columns are hidden, sort order, what cells are invalid and should be highlighted and so on, and then the controller can pass them into whatever view is going to present the information to the end user. That way you do not have to duplicate that business logic in different views. As soon as you start duplicating code in different places it starts to drift apart the very next day. So, if I want to highlight a cell because the value is above a threshold then the model indicates this and an ALV view gives that cell a red background and the generated spreadsheet does the exact same but the underlying technology is very different.

Now, I know what you are going to say next – in this example the XLSX view is going to not only create an XML representation of the data (that can be viewed as a spreadsheet) but also send that data as an attachment in an email. Is not only creating such data but also emailing such data - two different things and therefore should you not have two different classes?

I say back to you – are you crazy? Why do you always have to over-complicate everything? This is just a simple example for goodness’ sake! Let us just stick with one local XLSX view class for now thank you very much and then maybe we can split it up later in a subsequent version.

Code in the Hole

Now you want to see some actual code, do you? OK, here we go. You can see all the code on the GitHub repository and there is not actually all that much of it (50% of the code is class declarations) so I will just highlight the important bits.

We will have a selection screen with the three primary keys of SFLIGHT as the selection criteria plus a box where you can put the target email address. Note the little icons to make it blindingly obvious that an email is being sent with a spreadsheet attached.

Selection Screen

This is a type 1 executable program, so processing kicks off at START-OF-SELECTION. The aim of the game here is to have no global variables, so instead we use static classes (which are global variables really but let’s not go there).

START-OF-SELECTION.

  lcl_selections=>set_data(

    is_carrid = s_carrid[]

    is_connid = s_connid[]

    is_fldate = s_fldate[]

    ip_vari   = p_vari

    ip_send   = p_send

    ip_email  = p_email ).



  lcl_application=>main( ).



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

* Class Implementations

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

CLASS lcl_selections IMPLEMENTATION.



  METHOD set_data.



    s_carrid[] = is_carrid[].

    s_connid[] = is_connid[].

    s_fldate[] = is_fldate[].

    p_vari     = ip_vari.

    p_send     = ip_send.

    p_email    = ip_email.



  ENDMETHOD.



ENDCLASS.

 

So, the first step is to change the real global variables (the selection criteria) into static attributes of a class. This way we make all the OO diehard people happy and at one and the same time puzzle everyone who never uses OO, which is most ABAP people. Next comes the MAIN method – in Java and many other languages that is the name of the first method called which in turn calls all the others.

At this stage we just want to do four things

1. Read the Database


2. Prepare an ALV object


3. Email that ALV object to the target email address


4. Show the ALV object on the screen (just so you can make sure the data in the spreadsheet is correct)

There are no user commands in this initial version, there is no business logic and hence no need for unit tests. Showing the screen and emailing are “dependencies” and therefore cannot be unit tested. We only need unit tests once the business logic starts to come along, as it will in later blogs. Due to the “over-complicated” design the MAIN method consists of a series of calls to assorted specialised classes which do one thing only. In this case we have the model for the data retrieval (which it outsources to a specialised data access class) the controller which does nothing at all yet, and two views – one to email out the data, one to show the data on the screen.

CLASS lcl_application IMPLEMENTATION.



  METHOD main.



    mo_model      = NEW #( ).

    mo_alv_view   = NEW #( ).

    mo_controller = NEW #( io_model = mo_model

                           io_view  = mo_alv_view ) ##NEEDED."For User Commands



    mo_model->derive_data( ).

    mo_alv_view->initialise( CHANGING ct_output_data = mo_model->mt_output_data[] ).



    IF lcl_selections=>p_send EQ abap_true.

      mo_xlsx_view = NEW #( mo_alv_view->mo_alv ).

      mo_xlsx_view->create_spreadsheet( mo_model->mt_output_data[] ).

      mo_xlsx_view->email_spreadsheet( ).

    ENDIF.



    mo_alv_view->display( ).



  ENDMETHOD.



ENDCLASS.

There is nothing shocking about the data retrieval (unless you are shocked by using methods instead of just doing a SELECT statement directly after STARt-OF-SELECTION).

CLASS lcl_model IMPLEMENTATION.



  METHOD derive_data.



    mo_pers = NEW #( ).



    mt_output_data = mo_pers->derive_data( ).



  ENDMETHOD.



ENDCLASS.

CLASS lcl_pers_layer IMPLEMENTATION.



  METHOD derive_data.



    SELECT *

      FROM sflight

      INTO CORRESPONDING FIELDS OF TABLE rt_output_data

      WHERE carrid IN lcl_selections=>s_carrid[]

      AND   connid IN lcl_selections=>s_connid[]

      AND   fldate IN lcl_selections=>s_fldate[]

      ORDER BY PRIMARY KEY.



    IF sy-subrc NE 0.

      RETURN.

    ENDIF.



  ENDMETHOD.



ENDCLASS.

There is also nothing shocking about the ALV view methods.

CLASS lcl_view IMPLEMENTATION.



  METHOD initialise.



    TRY.

        cl_salv_table=>factory(

          IMPORTING r_salv_table = mo_alv

          CHANGING  t_table      = ct_output_data[]  ).

      CATCH cx_salv_msg INTO DATA(lx_salv_msg).

        MESSAGE lx_salv_msg->get_text( ) TYPE 'I'.

    ENDTRY.



  ENDMETHOD.



  METHOD display.



    mo_alv->display( ).



  ENDMETHOD.



ENDCLASS.

 

Thus far everything is 100% bog standard. Now we come to the XLSX view and the method for turning the ALV object into a spreadsheet, which will be stored in class attribute MO_EXCEL. The prefix MO is a reference to the barman in “The Simpsons” no hang on that is not right, it is short for “Member Object”. Leaving aside some surrounding code needed to get the basics working this is in essence such a single call to a “converter” class.

  METHOD create_spreadsheet.

    "Convert SALV object into excel

    DATA: ld_sheet_title TYPE zexcel_sheet_title,

          l_ws           TYPE c LENGTH 10 VALUE 'ITS'.



    "If we do this (call the convertor) in the foreground we get a dump

    "unless we do a dirty trick

    IF sy-batch EQ abap_false.

      EXPORT l_ws = l_ws TO MEMORY ID 'WWW_ALV_ITS'.

    ENDIF.



    DATA(lo_converter) = NEW zcl_excel_converter( ).



    TRY.

        IF mo_alv IS BOUND.

          lo_converter->convert(

            EXPORTING

              io_alv        = mo_alv

              it_table      = it_table[]

              i_table       = abap_true "Create as Table

              i_style_table = zcl_excel_table=>builtinstyle_medium2

            CHANGING

              co_excel      = mo_excel ).

        ELSE.

          RETURN.

        ENDIF.



        FREE MEMORY ID 'WWW_ALV_ITS'.



        DATA(lo_worksheet) = mo_excel->get_active_worksheet( ).



        ld_sheet_title = 'SFLIGHT'.

        lo_worksheet->set_title( ld_sheet_title ).



      CATCH zcx_excel INTO DATA(lo_exception).

        DATA(ld_message) = lo_exception->get_text( ).

        MESSAGE ld_message TYPE 'I'.

    ENDTRY.



  ENDMETHOD.

Now we have an EXCEL object the last stage is to email it out. In the next method there is a call to an ABAP2XLSX message to convert that EXCEL object to an XML string and the rest of the code is 100% bog standard code to email things out of SAP, code you will have seen a million times before.

  METHOD email_spreadsheet.

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

* Some of the code here may look VERY familiar, because instead of re-inventing the wheel I copied it straight off

* the internet about 10+ years ago. It worked straightaway so I never bothered changing it aprt from adding inline

* declarations

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

    DATA: lo_excel_writer TYPE REF TO zif_excel_writer,

          lo_recipient    TYPE REF TO if_recipient_bcs,

          ld_bytecount    TYPE i,

          ld_maxbytecount TYPE i,

          ld_filelen      TYPE so_obj_len.



    "Preconditions

    CHECK lcl_selections=>p_send  EQ abap_true.

    CHECK lcl_selections=>p_email IS NOT INITIAL.



    TRY.

        lo_excel_writer   = NEW zcl_excel_writer_2007( ).

        DATA(ld_xml_file) = lo_excel_writer->write_file( mo_excel ).



      CATCH zcx_excel INTO DATA(lo_exception).

        DATA(ld_message) = lo_exception->get_text( ).

        MESSAGE ld_message TYPE 'I'.

    ENDTRY.



    TRY.

        "Convert to binary

        DATA(lt_file_tab) = cl_bcs_convert=>xstring_to_solix( iv_xstring  = ld_xml_file ).

        ld_bytecount      = xstrlen( ld_xml_file ).

        ld_maxbytecount   = 10000000 ##NUMBER_OK.



        "Create persistent send request

        DATA(lo_send_request) = cl_bcs=>create_persistent( ).



        "Create document object from internal table with text

        DATA(lt_main_text) = VALUE bcsy_text( ( line = 'This is the text in the body of the email'(009) ) ).

        IF ld_bytecount >= ld_maxbytecount.

          APPEND INITIAL LINE TO lt_main_text ASSIGNING FIELD-SYMBOL(<ls_main_text>).

          <ls_main_text>-line =

          |{ 'The excel extract of the report cannot be sent because the resulting file is beyond the'(008) } | &&

          |{ ld_maxbytecount / 1000000 DECIMALS = 0 }{ 'MB limit'(007) }|.

        ENDIF.



        DATA(lo_document) = cl_document_bcs=>create_document( i_type    = 'RAW'

                                                              i_text    = lt_main_text

                                                              i_subject = 'Email Subject'(006) ).



        IF ld_bytecount < ld_maxbytecount.



          "Add the spreadsheet as attachment to document object

          ld_filelen = ld_bytecount.



          DATA: lt_att_head  TYPE soli_tab,

                lv_text_line TYPE soli.

          CONCATENATE '&SO_FILENAME=' 'Attachment Subject.XLSX'(005) INTO lv_text_line."Must end in XLSX or XLSM

          APPEND lv_text_line TO lt_att_head.



          lo_document->add_attachment( i_attachment_type    = 'EXT'

                                       i_attachment_subject = 'Attachment Subject.XLSX'(005)

                                       i_attachment_size    = ld_filelen

                                       i_att_content_hex    = lt_file_tab

                                       i_attachment_header  = lt_att_head ) ##NO_TEXT.



        ENDIF.



        "Add document object to send request

        lo_send_request->set_document( lo_document ).



        "Create recipient object

        lo_recipient = cl_cam_address_bcs=>create_internet_address( lcl_selections=>p_email ).



        "Add recipient object to send request

        lo_send_request->add_recipient( lo_recipient ).



        lo_send_request->set_status_attributes( 'N' )."Never



        "Send Document

        DATA(lf_sent_to_all) = lo_send_request->send( 'X' )."With Error Screen



        COMMIT WORK.



        IF lf_sent_to_all IS INITIAL.

          MESSAGE i500(sbcoms) WITH lcl_selections=>p_email."Document not sent to &1

        ELSE.

          MESSAGE s022(so)."Document sent

          "Kick off the send job so the email goes out immediately

          WAIT UP TO 2 SECONDS.     "ensure the mail has been queued

          SUBMIT rsconn01

            WITH mode   = '*'       "process everything you find.

            WITH output = ' '

          AND RETURN.                                    "#EC CI_SUBMIT

        ENDIF.



      CATCH cx_bcs INTO DATA(lo_bcs_exception).

        "Error occurred during transmission - return code: <&>

        MESSAGE i865(so) WITH lo_bcs_exception->error_type.

    ENDTRY.



  ENDMETHOD.

And that is that. 99% bog standard code you will already have seen dozens of times plus two calls to ABAP2XLSX class methods. For the life of me I cannot see why people keep saying ABAP2XLSX is far too difficult to use. The result is the data shown on screen plus a spreadsheet in your inbox.

ALV Output

Spreadsheet Output

Moreover, as we will see going forward, since most of the code to create an EXCEL object and to email it out is “boilerplate” you would want to encapsulate it into a global class with importing parameters for the small portion of values that change e.g., the name of the spreadsheet, contents of the email body and so on.

Conclusion

Just to re-iterate the main point of these blogs is to dispute the argument that ABAP2XLSX is too impossibly difficult to use.

In this blog I have started with the absolute basics i.e., the main use case which is emailing out a spreadsheet which looks like an ALV report, filled with SAP data. In subsequent blogs we will start to look at all the fancy things you can pro programmatically do with that spreadsheet e.g., print settings and the like, all of which are pretty much obvious how to do as well.

Cheersy Cheers

Paul