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.
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.
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. Any 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/
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_V02.
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.
What’s New Pussycat?
With each blog now, we will pretend one or more new business requirements have come in. In each case the business will say to you “This ABAP2XLSX thing is no good, because after the spreadsheet has been emailed to me, I have to manually do XYZ in Excel”.
The point is that anything you can do manually in Excel you can do programmatically via ABAP2XLSX
Why is that then?
Some of us older types remember when the suffix for an Excel file was just XLS as opposed to XLSX. That was when dinosaurs walked the Earth, and everything was made of wood. The added “X” at the end is to indicate that “under the hood” as it were an Excel spreadsheet is really an XML document. Same with Word documents, same with PowerPoint and so on.
So, what Ivan Femia thought to himself was thus – since you can generate XML documents programmatically from ABAP, and a spreadsheet is an XML document, therefore you can generate spreadsheets from ABAP.
The reverse is of course true, ABAP can read the contents of an XML document, and can therefore read the contents of a spreadsheet right down to the font and colour and so forth of each cell as well as the contents. In a subsequent blog you will see why that is important as when you send someone a spreadsheet to fill out for later upload into SAP they will “improve” that spreadsheet by re-arranging all the columns and adding ten blank rows at the top and so on and so forth, and then expecting the new layout to upload just fine. With ABAP2XLSX you can say “Ha! Ha! Do your worst! I will be able to upload it anyway!”
Ready Playback One
You may recall the days before SAP created BAPIs and other APIs for all common business objects. You had to use a BDC to record the manual steps needed for a transaction, use the recording to generate a skeleton program, and then change that program to provide dynamic values rather than the hard coded values you had recorded.
Nowadays of course there is an API for every single common SAP business object (HAHAHAHAHAHAHAHA! Of course, there is! Well, maybe one day. A proper one for deliveries (as in LIKP/LIPS) i.e., one of the most widely used SAP business objects, would be nice)
In any event on my first every day of work at my organisation – a summer job whilst still at university – I was given a boring spreadsheet job to do and by the end of that day I had discovered macros i.e., you record the manual steps you are doing and then generate a program to play those steps back, this time using variables. This was 1989 and the spreadsheet was Lotus 123, but it is the same in Excel.
The development in ABAP2XLSX follows the same pattern – both what has gone before and any new additions that need to be made in the future.
As an example, using ABAP2XLSX you can programmatically change the colour of a cell. How did they manage this? Well to demonstrate I will manually create a spreadsheet with one cell, fill that cell with the value BANANA and make the font bold and fill the background in yellow.
Now I save that file but with a funny name ending in .ZIP and with quotation marks around the file name. That might take a few attempts depending on your windows version, you need to take the “more options” path in Office 365.
The result is that you have a saved file that looks like this
When you double click on that file you get a tree structure. In my A2TF book I say that you now must go on a “big game hunt” to find what file your change (e.g., making a cell bold or any of the other ten billion things you can do in Excel) lives in. In this case I noticed that when I saved a one cell banana spreadsheet with no colour formatting compared to a one cell banana spreadsheet with formatting, then the file XL=>STYLES.XML looked different.
Thus, the idea is that if the ABAP code demands a coloured cell (or whatever) then the ABAP code must adjust the generated XML structure accordingly.
Hang On, you said this was going to be EASY! You Foolish Fool!
Well obviously, based on what I just said the replication in ABAP of the XML structure Excel uses is not easy at all – but all the hard yards were done for you – over 10 years ago. I am just trying to explain how ABAP2XLSX works. I would point out that Microsoft publishes the XML structures it uses in its products – this is not a secret at all.
Turn and Face the Business Requested Changes
In any event, when you proudly email your ALV report to a business user, and they want you to change something, you have four options – I am listing them from easiest to most difficult i.e., to state the obvious if an easier option works no need to even think about subsequent options.
You might think I need not state the obvious, but one April Fools’ Day I stated that SAP had bought a brewery and were now making beer, which was obvious nonsense, but I was told (by SAP) to alter my blog to put a disclaimer on the front saying this was an April Fool blog.
Oddly enough I once claimed in another blog that the CEO of SAP had eaten the CEO of Microsoft, and no-one called me up on that one. I also have claimed many times in blogs that Hasso Plattner is a Vampire and Larry Ellison is a Werewolf and that is the real reason they do not like each other, nothing to do with the software companies they founded. No-one called me out about that either, therefore it must be true.
It is April the 1st in a few weeks, and I happen to know that SAP have big plans for that day. I will report this news as and when it occurs.
Stop messing about – what were those four options?
To change an Excel spreadsheet generated from an ALV grid using ABAP2XLSX to meet a new business requirement here are those four options from easiest to hardest:
First Requested Change
At the end of the last blog the demo program was merrily emailing out a spreadsheet of SFLIGHT data. However, the end users were incredibly puzzled by the MANDT column at the start. Obviously, no-one outside of IT was any idea what that means, and it certainly does not provide any business value. Thus, we don’t want this field to be there even as a hidden field. It should be totally suppressed – in ALV terms this a technical field.
What we are going to do is add a new method in the ALV view class called APPLICATION_SPECIFIC_CHANGES. In the last demo program, I had just named this class LCL_VIEW and I am now going to rename this class to LCL_ALV_VIEW just to be crystal clear.
Every time I go back to any program I had written in the past, even a week before, I think something about it is rubbish, that needs to be improved. As long as I have a good reason for thinking so and it is not just changing something back and forth forever then I feel this is a Good Thing and I hope I never stop feeling this way. If tomorrow I look at something I wrote ten years ago and think “wow that is great!” then probably I am no longer fit for purpose.
Later, I am going to move all the public methods of the classes in the demo programs into interfaces. I did not want to do that straight off as that might have melted the brains of non-OO ABAP programmers (which is pretty much everybody). But that is a story for another day, as they say in “tales from the riverbank”.
So, the MAIN method of the application class is changed as follows to call the new view method.
The next is mind-bogglingly simple. We just set the MANDT field to be technical. That is a standard method of CL_SALV_TABLE and its helper classes, nothing to do with ABAP2XLSX.
Hey Presto! The MANDT column is no longer in the ALV grid and hence it is not in the emailed spreadsheet either. In the above code I have ignored error handling for the moment – which is a crime punishable by death – but this matter will be addressed by the end of this series of blogs.
Second Requested Change
The end user says that often the data returned in the emailed spreadsheet has more rows than can fit on one sheet. So, they page down and the header row with the titles vanishes. When creating a spreadsheet manually you would use the “freeze panes” option to always keep the header row visible. How do we do that in ABAP2XLSX?
First off, we need another structure change to the demo program. We already have a method called CREATE_SPREADSHEET. According to the “single responsibility principle” that method should only do what it says on the tin i.e., create the spreadsheet. We need another method to make any alterations needed. Let us call that APPLICATION_SPECIFIC_CHANGES as well. I have no issue having identically named methods in different classes if they perform the exact same task. Indeed, that is why we have interfaces and inheritance the like.
I already had one change to the spreadsheet in the “create” method – naughty – changing the name of the sheet title. That now gets moved into the new method where it belongs.
Now for the new requirement – ABAP2XLSX has standard methods for most things you can do manually, here we just say freeze the panes from the first row. Whilst I am at it, I will freeze the first column as well, because in wide spreadsheets you always want to know the “primary key” of the row you are viewing. Here I have hard coded the values to “1” but naturally you can programmatically alter the values.
Hey Presto! The header row and the very first column are now frozen. I honestly do not understand why I have had literally dozens of people (the ones who write the monthly ABAP2XLSX blogs) telling me this tool is far too difficult to use, because the code you must write is so complicated.
If anything the standard SALV code is more complicated and no-one complains about that.
Third Requested Change
This one does not come from an end user – it comes from one of the comments on a previous blog in this series. The comment was – why do I have to do a “dirty trick” to get the convertor class to change an ALV grid to an Excel object when running in the foreground? That is a very good question, I debugged the code and looked at the provided examples and as far as I can see the presumption is that the ALV grid must be displayed first before it can be converted into an Excel object (at least online). I cannot fix this myself much as I would like to, I am just not smart enough, so I will raise an issue on the ABAP2XLSX GitHub site and see how I go. I will keep you all posted. It is issue number 1097 if you want to track it.
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.
In this blog I have described the ways you can improve on the basic bog-standard code for just sending a spreadsheet as an email containing the data of an ALV grid.
Going Forward
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.
Here in Australia on the 11 May 2023 is the Australian SAP User Group (SAUG – pronounced “Sausage” ) conference in Brisbane. 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.
Cheersy Cheers
Paul
Hundreds of ABAP to 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.
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. Any 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/
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_V02.
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.
- Some people got confused that I did not really explain what ABAP2XLSX was and just started using classes like ZCL_EXCEL out of the blue. So, I have updated the blurb at the start of these blogs.
- It was pointed out I had used Hungarian Notation in the example program (e.g., LD_TITLE instead of just TITLE). That is a Bad Thing and so in the second version of the demo program the prefixes have vanished from the local variables. I keep them for member variables and parameters – in this case the prefix does not denote the variable type because a good name can denote that, but rather the scope. I suppose in theory a name could also denote the scope, but I cannot think of a good way to do this. Any suggestions welcome.
- I was submitting the SOST program for no good reason in the example. The suggested improvement was to set the “send immediately” flag which is now done in the second version.
What’s New Pussycat?
With each blog now, we will pretend one or more new business requirements have come in. In each case the business will say to you “This ABAP2XLSX thing is no good, because after the spreadsheet has been emailed to me, I have to manually do XYZ in Excel”.
The point is that anything you can do manually in Excel you can do programmatically via ABAP2XLSX
Why is that then?
Some of us older types remember when the suffix for an Excel file was just XLS as opposed to XLSX. That was when dinosaurs walked the Earth, and everything was made of wood. The added “X” at the end is to indicate that “under the hood” as it were an Excel spreadsheet is really an XML document. Same with Word documents, same with PowerPoint and so on.
So, what Ivan Femia thought to himself was thus – since you can generate XML documents programmatically from ABAP, and a spreadsheet is an XML document, therefore you can generate spreadsheets from ABAP.
The reverse is of course true, ABAP can read the contents of an XML document, and can therefore read the contents of a spreadsheet right down to the font and colour and so forth of each cell as well as the contents. In a subsequent blog you will see why that is important as when you send someone a spreadsheet to fill out for later upload into SAP they will “improve” that spreadsheet by re-arranging all the columns and adding ten blank rows at the top and so on and so forth, and then expecting the new layout to upload just fine. With ABAP2XLSX you can say “Ha! Ha! Do your worst! I will be able to upload it anyway!”
Ready Playback One
You may recall the days before SAP created BAPIs and other APIs for all common business objects. You had to use a BDC to record the manual steps needed for a transaction, use the recording to generate a skeleton program, and then change that program to provide dynamic values rather than the hard coded values you had recorded.
Nowadays of course there is an API for every single common SAP business object (HAHAHAHAHAHAHAHA! Of course, there is! Well, maybe one day. A proper one for deliveries (as in LIKP/LIPS) i.e., one of the most widely used SAP business objects, would be nice)
In any event on my first every day of work at my organisation – a summer job whilst still at university – I was given a boring spreadsheet job to do and by the end of that day I had discovered macros i.e., you record the manual steps you are doing and then generate a program to play those steps back, this time using variables. This was 1989 and the spreadsheet was Lotus 123, but it is the same in Excel.
The development in ABAP2XLSX follows the same pattern – both what has gone before and any new additions that need to be made in the future.
As an example, using ABAP2XLSX you can programmatically change the colour of a cell. How did they manage this? Well to demonstrate I will manually create a spreadsheet with one cell, fill that cell with the value BANANA and make the font bold and fill the background in yellow.
Banana Spreadsheet
Now I save that file but with a funny name ending in .ZIP and with quotation marks around the file name. That might take a few attempts depending on your windows version, you need to take the “more options” path in Office 365.
Save file with ZIP as suffix
The result is that you have a saved file that looks like this
Saved ZIP File
When you double click on that file you get a tree structure. In my A2TF book I say that you now must go on a “big game hunt” to find what file your change (e.g., making a cell bold or any of the other ten billion things you can do in Excel) lives in. In this case I noticed that when I saved a one cell banana spreadsheet with no colour formatting compared to a one cell banana spreadsheet with formatting, then the file XL=>STYLES.XML looked different.
Thus, the idea is that if the ABAP code demands a coloured cell (or whatever) then the ABAP code must adjust the generated XML structure accordingly.
Hang On, you said this was going to be EASY! You Foolish Fool!
Well obviously, based on what I just said the replication in ABAP of the XML structure Excel uses is not easy at all – but all the hard yards were done for you – over 10 years ago. I am just trying to explain how ABAP2XLSX works. I would point out that Microsoft publishes the XML structures it uses in its products – this is not a secret at all.
Turn and Face the Business Requested Changes
In any event, when you proudly email your ALV report to a business user, and they want you to change something, you have four options – I am listing them from easiest to most difficult i.e., to state the obvious if an easier option works no need to even think about subsequent options.
You might think I need not state the obvious, but one April Fools’ Day I stated that SAP had bought a brewery and were now making beer, which was obvious nonsense, but I was told (by SAP) to alter my blog to put a disclaimer on the front saying this was an April Fool blog.
Oddly enough I once claimed in another blog that the CEO of SAP had eaten the CEO of Microsoft, and no-one called me up on that one. I also have claimed many times in blogs that Hasso Plattner is a Vampire and Larry Ellison is a Werewolf and that is the real reason they do not like each other, nothing to do with the software companies they founded. No-one called me out about that either, therefore it must be true.
It is April the 1st in a few weeks, and I happen to know that SAP have big plans for that day. I will report this news as and when it occurs.
Stop messing about – what were those four options?
To change an Excel spreadsheet generated from an ALV grid using ABAP2XLSX to meet a new business requirement here are those four options from easiest to hardest:
- Often you just need to change the ALV definition – it will automatically get picked up by the “converter” class
- If that does not work, use a standard ABAP2XLSX class/method
- If there is no such standard method, be lazy and complain to the open-source project that what you want does not work. It will get fixed and in an amazingly short space of time.
- An alternative to (3) would being pro-active, do not just take take take, try and give back to the community, fix the code yourself, and submit the fix to the open-source project.
First Requested Change
At the end of the last blog the demo program was merrily emailing out a spreadsheet of SFLIGHT data. However, the end users were incredibly puzzled by the MANDT column at the start. Obviously, no-one outside of IT was any idea what that means, and it certainly does not provide any business value. Thus, we don’t want this field to be there even as a hidden field. It should be totally suppressed – in ALV terms this a technical field.
What we are going to do is add a new method in the ALV view class called APPLICATION_SPECIFIC_CHANGES. In the last demo program, I had just named this class LCL_VIEW and I am now going to rename this class to LCL_ALV_VIEW just to be crystal clear.
Every time I go back to any program I had written in the past, even a week before, I think something about it is rubbish, that needs to be improved. As long as I have a good reason for thinking so and it is not just changing something back and forth forever then I feel this is a Good Thing and I hope I never stop feeling this way. If tomorrow I look at something I wrote ten years ago and think “wow that is great!” then probably I am no longer fit for purpose.
Later, I am going to move all the public methods of the classes in the demo programs into interfaces. I did not want to do that straight off as that might have melted the brains of non-OO ABAP programmers (which is pretty much everybody). But that is a story for another day, as they say in “tales from the riverbank”.
So, the MAIN method of the application class is changed as follows to call the new view method.
mo_model->derive_data( ).
mo_alv_view->initialise( CHANGING ct_output_data = mo_model->mt_output_data[] ).
mo_alv_view->application_specific_changes( ).
The next is mind-bogglingly simple. We just set the MANDT field to be technical. That is a standard method of CL_SALV_TABLE and its helper classes, nothing to do with ABAP2XLSX.
METHOD application_specific_changes.
DATA: lo_column TYPE REF TO cl_salv_column_table.
DATA(lo_columns) = mo_alv->get_columns( ).
TRY.
lo_column ?= lo_columns->get_column( 'MANDT' ).
lo_column->set_technical( abap_true ).
CATCH cx_salv_not_found INTO DATA(not_found).
"Raise a Fatal Exception
ENDTRY.
ENDMETHOD.Hey Presto! The MANDT column is no longer in the ALV grid and hence it is not in the emailed spreadsheet either. In the above code I have ignored error handling for the moment – which is a crime punishable by death – but this matter will be addressed by the end of this series of blogs.
Second Requested Change
The end user says that often the data returned in the emailed spreadsheet has more rows than can fit on one sheet. So, they page down and the header row with the titles vanishes. When creating a spreadsheet manually you would use the “freeze panes” option to always keep the header row visible. How do we do that in ABAP2XLSX?
First off, we need another structure change to the demo program. We already have a method called CREATE_SPREADSHEET. According to the “single responsibility principle” that method should only do what it says on the tin i.e., create the spreadsheet. We need another method to make any alterations needed. Let us call that APPLICATION_SPECIFIC_CHANGES as well. I have no issue having identically named methods in different classes if they perform the exact same task. Indeed, that is why we have interfaces and inheritance the like.
I already had one change to the spreadsheet in the “create” method – naughty – changing the name of the sheet title. That now gets moved into the new method where it belongs.
Now for the new requirement – ABAP2XLSX has standard methods for most things you can do manually, here we just say freeze the panes from the first row. Whilst I am at it, I will freeze the first column as well, because in wide spreadsheets you always want to know the “primary key” of the row you are viewing. Here I have hard coded the values to “1” but naturally you can programmatically alter the values.
METHOD application_specific_changes.
DATA(worksheet) = mo_excel->get_active_worksheet( ).
DATA(sheet_title) = VALUE zexcel_sheet_title( ).
TRY.
"Every Excel spreadsheet has a title at the bottom left which defaults to "Sheet1"
"Here I am hardcoding the value but you can set the value using whatever logic you want
sheet_title = 'SFLIGHT'.
worksheet->set_title( sheet_title ).
"Make sure vital values are always visibnle when user scrolls in spreadsheet
worksheet->freeze_panes( ip_num_columns = 1
ip_num_rows = 1 ).
CATCH zcx_excel INTO DATA(exception).
DATA(message) = exception->get_text( ).
MESSAGE message TYPE 'I'.
ENDTRY.
ENDMETHOD.
Hey Presto! The header row and the very first column are now frozen. I honestly do not understand why I have had literally dozens of people (the ones who write the monthly ABAP2XLSX blogs) telling me this tool is far too difficult to use, because the code you must write is so complicated.
If anything the standard SALV code is more complicated and no-one complains about that.
Third Requested Change
This one does not come from an end user – it comes from one of the comments on a previous blog in this series. The comment was – why do I have to do a “dirty trick” to get the convertor class to change an ALV grid to an Excel object when running in the foreground? That is a very good question, I debugged the code and looked at the provided examples and as far as I can see the presumption is that the ALV grid must be displayed first before it can be converted into an Excel object (at least online). I cannot fix this myself much as I would like to, I am just not smart enough, so I will raise an issue on the ABAP2XLSX GitHub site and see how I go. I will keep you all posted. It is issue number 1097 if you want to track it.
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.
In this blog I have described the ways you can improve on the basic bog-standard code for just sending a spreadsheet as an email containing the data of an ALV grid.
Going Forward
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.
Here in Australia on the 11 May 2023 is the Australian SAP User Group (SAUG – pronounced “Sausage” ) conference in Brisbane. 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.
Cheersy Cheers
Paul
- SAP Managed Tags:
14 Comments
