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.

Millions of 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, and then give assorted bogus reasons why ABAP2XLSX should not be used.

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

First off ABAP2XLSX is an open-source project which can be found on the GitHub repository mentioned below.

abap2xlsx/abap2xlsx: Generate your professional Excel spreadsheet from ABAP (github.com)

Its purpose is to move data between Excel and ABAP in both directions. Any global classes you see in my demo program with “EXCEL” in their name come from the ABAP2XLSX repository.

The last blogs in this new series I posted can be found at: –

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

https://blogs.sap.com/2023/02/11/monthly-abap-to-excel-blog-february-2023/

https://blogs.sap.com/2023/03/25/monthly-abap-to-excel-blog-march-2023/

The GitHub repository for my evolving demonstration program is

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. The version I talk about in this blog is Z_ABAP2XLSX_PDH_DEMO_V03 plus an associated global class which I have called ZCL_ABAP2XLSX_PDH_DEMO_V03 even though it is the first version of such a class, just to keep the suffix for the executable program and related global class the same.

Halley’s Comment

The good thing about posting blogs on SCN is the comments you get. Some suggest positive improvements to the demo code, and even the negative comments can be valuable if they are written in a constructive manner rather than just “you are an idiot”. I already knew that last fact.

This blog has three sections. Only section 02 is something I had planned. The other two sections are responses to comments on the previous three blogs. This is as it should be – I make some suggestions in my blogs, in the comments people suggest better ways to do something, I experiment with those ideas and if I get them working everybody wins, and by everybody, I mean potentially every single organisation that runs SAP.

Moreover, if someone posts a comment saying feature XYZ is missing or sub-optimal, and then in the next blog I suggest a proposed solution, chances are someone will tell me my proposed solution is not the best and tell me a better way to do it. Which is fine by me. This is what the community is all about.

Section 01 – Short Dump Problem!

In the sample code in my first blog there was some really strange code exporting a value to memory with a comment to the effect that doing this strange thing is the only way to stop a short dump. Now that was true. In online mode you had to show the ALV grid on the screen before sending that same data as an email. If you did that the other way around, you got a dump. In background mode all was fine so I did some jiggery pokery to make ABAP2XLSX think it was in background mode.

That code was as follows

    "Convert SALV object into excel
    DATA: 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.

In the comments section of my first ABAP2XLSX blog was a question to the effect that instead of doing something bizarre and not intuitive at all, how about I fix the actual ABAP2XLSX code? Well, I could fix it in my development system, but I wanted to fix it for everybody. So, I raised an issue on GitHub (Issue 1097, closed now, but on GitHub you can look at the history of closed issues, and I this case I encourage you to have a look just to see what the process is) where I suggested a possible fix but wanted to make sure it did not stuff anything else up and after much discussion with various parties where I explained what the problem was and how to simulate it (I am an 7.50, in higher releases of ABAP there was no problem) a code change was made to the core project by Sandra Rossi, and now if you download the latest version of ABAP2XLSX there is no longer any need for any sort of strange workaround.

Therefore, in V03 of my demonstration program on GitHub you will no longer see that strange code as there is no longer any need for it.

That all happened in an amazingly short space of time. As a thought experiment imagine you found a bug in standard SAP code and proposed a solution via what I still call OSS. I don’t even have to imagine this situation; I have had colleagues in both Australia and Germany propose obvious solutions to obvious bugs in standard SAP code and guess how far they got? Ten years on both bugs are still there.

I raise this point because one of the arguments against using an open-source product like ABAP2XLSX is that because it is not a standard SAP product you will not get the standard SAP support. Well, that is true. You get a level of support that is a million times better. I don’t see that as an argument against ABAP2XLSX.

Section 02 – New Business Requirement

Here is a new business requirement which is somewhat trivial, but I have found that the IT department can get tons of Brownie Points if they can solve a low-level problem that, whilst not fatal, is really annoying for the end users. Usually something that makes the end users press half a hundred buttons on their computer to get a task done when they just want to press one button.

In this case thus far the business users love having their SFLIGHT report emailed to them in the form of a spreadsheet. The first thing they do is to print the spreadsheet out so that they can scribble all over it. I remember in the early nineties people kept telling me soon we will have a PAPERLESS OFFICE and now it is 2023 and people still print things out all the time, so that’s just the way it is. As I have said before, don’t try and stop the tide coming in, it’s going to come in whether you like it or not, instead make it easier for people to deal with the tide coming in.

The problem is that an ALV report generally has lots of columns, more than can fit on one screen no matter how big your monitor is. Excel defaults to “portrait mode” so if you have, say, 30 rows of data and print the spreadsheet as-is then you will likely get 90 pages printed out, the first 30 with the first 33% of columns, the next thirty with the middle 33% and the last 30 with the remainder. If you had a really big physical desk, you could try and line up the printed pieces of paper I suppose and stick them together with tape but in real life what people tend to do is manually adjust the print settings to choose “landscape” mode and fit all the columns on one page. Often that makes the printed output quite small but clearly people are willing to live with that as such printouts are what I see abandoned on the printer or on people’s desks as I am walking past.

Put another way, when someone gets a spreadsheet, and they want to print it out they change the print settings manually 100% of the time. As the Beach Boys might say “Wouldn’t it be nice if we could automate that step?”

With ABAP2XLSX you can do just that. Any setting you can make manually, you can make programmatically in ABAP whilst generating the spreadsheet to be emailed.

In the demonstration program the ALV view has an “application specific changes” method where you can re-arrange the column order and change the headings and so on. These changes apply to the screen output, but also get copied into the generated Excel sheet.

The XLSX view class also has an “application specific changes” method, this time for the sort of changes which only apply to spreadsheets. Thus far we have used this to freeze the first column plus the headings row and change the name of the worksheet from SHEET1 to SFLIGHT.

Now we are going to add in some code to alter the print settings. The first and most important bit is to change the orientation from portrait to landscape and make all the columns fit onto one page.

      "Requirement is landscape mode plus fit all columns on one sheet
      worksheet->sheet_setup->orientation         = zcl_excel_sheet_setup=>c_orientation_landscape.
      worksheet->sheet_setup->fit_to_page         = 'X'.
      worksheet->sheet_setup->fit_to_width        = 1.  " used only if ip_fit_to_page = 'X'
      worksheet->sheet_setup->page_order          = zcl_excel_sheet_setup=>c_ord_downthenover.
      worksheet->sheet_setup->paper_size          = zcl_excel_sheet_setup=>c_papersize_a4.
      worksheet->sheet_setup->scale               = 80. " used only if ip_fit_to_page = SPACE
      worksheet->sheet_setup->horizontal_centered = abap_true.

I copied all of this from the supplied example programs, but even had those not existed, it would not have been too difficult to figure out.

When you look at the example program on GitHub you will see lots of other printer related code as well. Without going into every single line of code here basically I drew up a list in my head of all the things I might do if I was printing out a spreadsheet manually, which would be

• Set the orientation to landscape.
• Set the “fit to one sheet” option.
• Change the margins to “narrow” so the result is just that little bit bigger.
• Set the “black and white” indicator so as not to waste money.
• Have a header with the tab name, and centre this.
• Have a footer with the current date on the left and page X of Y on the right.
• Say I want the header row repeated on every printed sheet.

You can do all of this programmatically in ABAP and the example program will show you how.

I now re-run the program, sending myself an email of SFLIGHT data. When I get the email, I open up the spreadsheet, and go into the print preview you see the following.

Print Preview

The date column does not look too good, but we can address that later. The important thing is that all the required print settings have been applied automatically.

I hope this demonstrates that anything you can do manually in Excel you can do programmatically in ABAP whilst generating the spreadsheet.

Section 03 – Complete Re-Write of Demo Program

In one of the comments on one of my earlier ABAP2XLSX blogs there was a bit of an esoteric discussion on the best way to implement the MVC pattern. Since to this day 99% of ABAP programmers would not touch object-oriented programming with a ten-foot bargepole, let alone know that MVC means “Model / View / Controller” and understand what that implies, that discussion must seem meaningless.

Nonetheless if I am going to do something I want to do it right / do it right / do it RIGGGGGHHHT / Bend Over Shake a Tail Feather Baby!

In my example program there were, in effect, two controllers looking after the same view. That’s not good, a person cannot serve two masters as someone somewhere once said. You could say this is somewhat like arguing about how many Angels can dance on the head of a pin, it does not matter, the program works fine, but too many people take second best, I can’t take anything less, it’s got to BEEEEEEEE PERFECT!

So, the very next time I had to write a real program for my real work I though let us see if I cannot improve on my template program.

So, it is time for the “Single Responsibility Principle” to come into play i.e., a unit of software programming (routine) should do one thing only and do it well. If you are asked “What does this routine do?” and your answer contains the word AND something is amiss.

I thought to myself “What do I want the Executable program to do?”. First off that is the entry point linked to a transaction code. You can in theory have a transaction code that executes a method of a global class but that does not work properly and never has – specifically if the called program does a COMMIT WORK then the universe explodes. In ABAP in the cloud there are no executable programs (or SAP GUI) but you can add a standard interface / class so you can click on a Z class and auto-execute it.

Thus, the “one thing” the executable program is going to do is to collect information from the selection screen for onward transmission to the main program, which is going to be a global class with lots of local classes for the Model / View / Controller / Test Classes etc. This is pretty much how the RAP works – an empty global class with all the work being done in embedded local classes.

In my new template design the global class has a MAIN method called from the executable program which does a nothing except create the MVC class instances needed by the controller, pass them into the controller and then tell the controller to get cracking. That sentence contains the word AND which is bad so I will rephrase that as the single thing that method does is to invoke the MAIN method of the controller. To achieve that goal the class instances have to be created first most especially the controller itself.

So first up I removed virtually everything from V03 of the executable program. All that is left is the selection screen and a single FORM routine called just after START-OF-SELECTION also called MAIN. The code is as follows:

START-OF-SELECTION.
  PERFORM main.

FORM main.

  zcl_abap2xlsx_pdh_demo_v03=>get_instance(
                             VALUE #( variant       = p_vari
                                      send_email    = p_send
                                      email_address = p_email
                                      r_carrid      = s_carrid[]
                                      r_connid      = s_connid[]
                                      r_fldate      = s_fldate[] ) )->main( ).

ENDFORM.

As mentioned, the MAIN method of my local class does nothing except forward the data to a local controller class which will then process the application by calling assorted methods of the model and the two views (ALV and XLSX). As an aside if a method only has one line of code you can be fairly sure it is only doing one thing.

  METHOD zif_abap2xlsx_pdh_demo_v03~main.

     lcl_controller=>get_instance(
       io_model     = NEW lcl_model( ms_selections )
       io_alv_view  = NEW lcl_alv_view( )
       io_xlsx_view = NEW lcl_xlsx_view( ms_selections ) )->main( ).

  ENDMETHOD.

Instead of two controllers looking after one view, we now have one controller looking after two views Someone is probably going to tell me that is wrong as well, it should be a one-to-one relationship, but I don’t care. I have lots of programs with a single business logic model and the controller shows different views with different slices of the underlying data depending on what command the end user chooses.

Anyway, in the controller’s MAIN method are a series of calls to the model and views, the same as the ones that used to sit in my amorphous “application” method.

CLASS lcl_controller IMPLEMENTATION.

  METHOD get_instance.

    ro_controller = NEW lcl_controller( io_model     = io_model
                                        io_alv_view  = io_alv_view
                                        io_xlsx_view = io_xlsx_view ).

  ENDMETHOD.

  METHOD constructor.
    mo_model     = io_model.
    mo_alv_view  = io_alv_view.
    mo_xlsx_view = io_xlsx_view.
  ENDMETHOD.

  METHOD main.

    mo_model->derive_data( ).

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

    "Have to send the email before showing the ALV on screen.
    "Sending the mail only when the user exits the screen
    "means you are snding it at a random time in some senses,
    "and that might confuse people
    IF mo_model->ms_selections-send_email EQ abap_true.
      mo_xlsx_view->set_alv( mo_alv_view->mo_alv ).
      mo_xlsx_view->create_spreadsheet( mo_model->mt_output_data[] ).
      mo_xlsx_view->application_specific_changes( ).
      mo_xlsx_view->email_spreadsheet( ).
    ENDIF.

    "This will bring up the screen and then wait till the user chooses a command
    mo_alv_view->display( ).

  ENDMETHOD.

ENDCLASS.

I will be constantly fiddling with the demo program/class as the blogs go by, especially when we start to add business logic and unit tests are needed. People are already going to say it is far too complicated and that sense of horror is only going to grow. I have always said that for a small simple program of course the procedural version is going to be smaller and easier to understand than the OO equivalent, but in real life no program ever stays small and simple, they just get bigger and more complex over time, and once it grows beyond a certain level of complexity then the OO version becomes smaller / easier to understand / easier to maintain than the procedural equivalent. The break-even point of complexity between the two (i.e., the point the OO version becomes easier to maintain than the procedural one) gets hit sooner than you might think.

Oh, By the Way, Just One More Thing

One last comment I got (via email) was that last time I forgot to upload the updated demo program to GitHub. I did this as soon as I was aware I had forgotten to do this basic thing, and I hope I do not forget again. If I do, I am sure someone will tell me.

Conclusion

Just to re-iterate the main point of these blogs is to (a) let people know ABAP2XLSX exists in the first place and (b) dispute the argument that ABAP2XLSX is too impossibly difficult to use.

As an extra point I am trying to disprove the myth (that seems to only exist in ABAP world) that somehow open-source software is a bad thing and not fit for productive use.

In this blog I have described

• How amazingly good the online support for open-source projects can be.
• An example of how anything you can do manually in Excel you can do programmatically in ABAP using ABAP2XLSX.
• A sort of rambling off-topic discussion about how to best structures OO programs using the MVC pattern.

Going Forward

Heart of the Intelligent Enterprise

I will be trying to do a blog each month hereafter explaining how to add extra bells and whistles to the generated spreadsheet in the example.

I had a comment in a previous blog asking if I was going to talk about reading from Excel files using ABAP2XLSX. To be honest I had not intended to, but I react to comments and so thought I should really address this matter. As it transpires the gentleman in question has already solved his problem – with help from the SAP community – but that is no reason why I should not talk about the way to do this anyway, so that will be in the next blog.

Here in Australia on the 21 August 2023 is the Australian SAP User Group (SAUG – pronounced “Sausage”) conference in Sydney. At that event I will be giving a talk about ABAP2XLSX as a sort of supplement to these blogs. I want to make sure at least in Australia everyone knows this tool exists. Maybe they will let me do the same speech later this year in ASIG TECH Connect in New Orleans,