Technical Articles
Monthly ABAP to EXCEL Blog – January 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.
Blogs from Last ten Years
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.
Then come one (or both) of the following two arguments: –
- I cannot use ABAP2XLSX because my organization has a “no open source” policy
- Open-source ABAP projects are not supported by SAP
- I cannot use ABAP2XLSX because it is far too complicated, a sledgehammer trying to crack a nut,
- ABAP2XLSX is not documented at all
Let us take these one at a time
No Open-Source Policy
Some years back the organisation I work for had a global “NO CLOUD” policy. So, when we in Australia wanted to start using SAP Ariba (which is of course a cloud product) we had to get a special exemption, which was not easy.
Nowadays of course there is no such global policy for some reason. What could possibly have changed everyone’s minds?
In the same way when someone says their organisation has a no open-source policy they are posting that statement either from a work supplied smartphone which is crawling with open-source software, or a work supplied PC which is crawling with open source software, using a browser which is crawling with open source software, and then get in their company car (which is crawling with open source software) to drive to or from work.
If an organisation really had a no open-source policy, then no employees would be allowed to use any modern technology and the company would have to work like the Flintstones where at the end of the day you leave your office by sliding down the neck of a Brontosaurus. Which would probably be quite fun. But in real life most offices do not have such slides – apart from maybe Google offices.
In addition, I cannot really see the difference between copying an open-source program from a GitHub repository into your development system and cutting and pasting the code from whatever blog it is into your development system which is of course what the blog poster is encouraging you to do.
No Support
If you are using an official framework supplied by SAP, then of course if something does not work you can raise what I still call an OSS message and SAP are supposed to investigate and fix the problem. With an open-source project like ABAP2XLSX however you cannot go running to SAP if anything does not work. Therefore – the argument goes – do not use open-source ABAP projects.
This is more of a mindset change than anything else. You must mentally move from support model A to support model B whereby: –
- In support model A you pay millions of dollars a year, and when you have a problem raise an OSS request, go backwards and forwards with first level support for several weeks, who keeps asking you for things you have already sent them. Eventually you get through to a higher level of support who tell you this is consulting, and it cannot be fixed unless you are willing to pay extra. Even if you have debugged the faulty code yourself and supply the solution (the needed code changes) it often makes no difference. If by some miracle the problem is fixed you get an OSS note which you can apply at once, and all the other SAP customers get the fix the next time they apply a support stack in three months’ time, or a year, or whatever.
- In support model B you do not pay anything and when you have a problem you are strongly encouraged to fix it yourself and then present the solution to the GitHub site whereby once it has been approved the central code base is updated for everyone at once, so the next time they check the repository using abapGit they will get all the latest fixes. If you cannot fix the problem yourself, you log an issue on the GitHub site and in a very short space of time someone out in Internet world will have fixed it without constantly fighting back, or telling you that this is working as designed or whatever.
People are used to support model A and have come to quite like it, sort of a Stockholm Syndrome thing I suppose, and the very idea of support model B makes them break out in a cold sweat.
To give some real-life examples of Model B
- Whilst writing the latest edition of my ABAP book I found a way ABAP2XLSX could be improved and described how to submit such an improvement request. Within a matter of days assorted people from internet world had jumped on this and proposed even better solutions than mine. When you raise an OSS note do you get different developers who work for SAP fighting each other to see who can solve your problem the best\ way?
- Several times now a colleague of mine has made suggestions/requests to the author of the ADT plug-in framework (Lukasz Pegiel) and the next day the fix/new feature was there.
- Several times I have raised an issue on the abapGit GitHub repository and an hour later I get a message saying the fix is ready please download it and respond back saying what you think (this works even better than standard SAP support because you download the new version of the code from a different branch, so if it does not work no-one else has been affected, and the fix is only generally released once you and the project owner are happy all is working correctly)
Far Too Complicated
When you download the ABAP2XLSX code from the GitHub repository (which is https://github.com/abap2xlsx/abap2xlsx by the way) to your development t system you will note it is not just one huge program but rather about 70 or 80 different classes.
That seems to get people squawking that everything is far too complicated, but I would counter this with two points:
- I don’t think 70 odd classes in one repository is that excessive. Some standard SAP packages have far more objects.
- This is nothing less than good OO design. That is the design follows the Single Responsibility Principle (SRP – the “S” in SOLID) whereby each class does one thing only and therefore has only one possible reason to change.
The CL_SALV_TABLE follows the same basic principle. Instead of one huge function module like REUSE_ALV_DISPLAY_GRID you have an array of small classes each doing one thing.
Coding using CL_SALV_TABLE to create an ALV report, and coding using ABAP2XLSX to download that report to Excel – in both cases you would need to create instances of assorted low-level objects and make them work together. Moreover, in both cases you can create re-usable templates, so you do not have to write half a million tons of boiler plate code each time.
So, the question becomes – why is the former deemed as no problem at all, but the latter deemed as so complicated as to be impossible?
The usual reason given is that CL_SALV_TABLE is sort of documented, whereas ABAP2XLSX is not documented at all. Back in 2000 when REUSE_ALV_DISPLAY_GRID came out that wasn’t documented very well either, but that did not strop the ALV taking off like a rocket and replacing reports that used WRITE statements.
Classified Documentation in my Office
Of all the arguments against ABAP2XLSX the only one I have any time for is the lack of documentation. Programmers generally aren’t much for writing documentation, they love writing code so much they don’t want to do anything else.
However, in the same way Thomas Jung wrote tons of documentation about CL_SALV_TABLE in his SAP Press book “Next Generation ABAP Development” I wrote tons of documentation about ABAP2XLSX in my book “ABAP to the Future”.
It has been pointed out to me on the internet that if I really want thousands of ABAP programmers to start using ABP2XLSX then forcing them to buy my book is probably not the correct way to proceed, and I see that point. On the other hand, if I just cut and pasted everything from the book chapter into an SCN blog I imagine SAP Press would not be too happy. So, can I find a happy medium?
ABAP2XLSX comes with a set of demo programs. Each one demonstrates one or more things ABAP2XLSX is capable of and gives an example of how to code it. They seem pretty much self-explanatory to me, but obviously they are not.
The Way Forward
Gateway to Excel Goodies
As mentioned at the start I am going to be trying to do my own monthly ABAP2XLSX blog, to counteract all the ones which re-invent the concept. I will write my own demo programs.
By the time the next blog is ready I will have created a new GitHub repository so people can download my example programs. I will try to explain the code in as much detail as I can.
We will start with the most basic use case which is taking an existing ALV report and adding the option to turn it into a spreadsheet which you can then email to yourself (or anyone) even if the program is run in the background.
In subsequent blogs we will move on to adding assorted extra ABAP2XLSX features e.g., setting print parameters, conditional formatting, pie charts and so on.
No doubt by the time I am ready for my next blog there will be one or more blogs published by someone who has just invented a way to upload/download Excel to/from ABAP and when that happens, I will point them to this blog, which I will keep updated as and when I write the next blogs.
Cheersy Cheers
Paul
Nice blog. Thanks.
Very good summary. These are very valid points.
I'm looking forward this series.
I really like the hidden joke in the Assigned Tags 🙂
Great idea with the monthly blog! If something is important/good, it's nice to repeat it!
I am completely with you !
I have been the guy that launched Ivan Femia ,the abap2xlsx creator, into the adventure. He told me that, casually, unzipping an .xlsx file lot of interesting .xml appeared. ABAP is well structured to create XML and after a couple of weeks and many nights, Ivan presented the first results.
He did a terrific job! But the better happened after posting the first blog. The SAP community received it enthusiastically. You then did so much as well as Gregor Wolf and several other gurus like you are. I believe that abap2xlsx is still one of the most successful community project ever (probably with SAPLINK and AbapGit).
In ABAP Cloud something is coming also as SAP standard SAP BTP Help
Thanks a lot,
Sergio
Thanks for being that guy, then 🙂
I just had a look in the BTP Trial environment and the XCO_CP_XLSX apparently isn't there yet, I was curious to see what was already supported.
Strange, I confirm I can find the CLASS in the SAP BTP ABAP environment instance. I cannot image why you don't, I am sorry.
I did not yet worked with the XCO_CP_XLSX and I cannot provide more hints
No idea, though I am using TRL_EN
Thanks for checking, though.
EDIT: nevermind, today it appeared, nice!
Though I'm looking forward to reading these blogs, I'm not sure that more demo programs are gonna be useful to those that don't look at the existing ones.
Wouldn't it be more relevant to create content into the doc folder of the official repo?
100% agree!
As much as I like abab2xlsx I always felt that one of the reasons it is not used more widely is the lack of any proper documentation.
Demo programs are a great add-on to a documentation but no full replacement.
This is not to say that I don't support the approach of Paul Hardy, of course abap2xlsx should be the natural way of choice, many thanks for this push!
Well, I guess you cannot really blame the bloggers for re-inventing the wheel.
Even SAP have recently developed and released their own "new" solution (a final official API, I hope): The XCO XLSX module (XCO_CP_XLSX).
You do have a point: they certainly know about it and helping the project become BTP-compatible would have been a smart move, imnsho.
This issue is broadly symptomatic of another one: the limited understanding of git by the majority of ABAP developers.
Yes - git is used for open source work. But git - and abapGit no less - has very extraordinary use for improving internal development delivery. I don't think this gets talked about enough. CTS does a great job of moving changes to production, however it doesn't easily help you create multiple timestamped snapshots of all your objects. Git can do that! and it's very useful in the case object contention, rework, or dodging the cost of standing up N+1 environments.
Open source isn't the boogeyman some would make it out to be. I know the "no open source" policy is likely sadly a thing, but it's terrible one to have for the reasons you mentioned. How does SAP blog copy pasta instead serve anyone any better? It doesn't. At least in git, you have some guarantees on the authorship and vintage of what new code you're bringing in.
The culture has changed on OSS. Even US Government agencies now in fact mandate the promotion and preference for open source software. Here's the reason to this: OSS that's well crafted from standards body is secure, cost effective, and comes with greater odds of continuity of service based on community involvement.
Oh I don't know Paul. Some people get confused by rational arguments and facts... 😉
As far as documentation goes, I installed abap2xlsx eight years ago for a project. I didn't need any documentation - it was kind of obvious how to use it. Now, there's a sign of a well written application!
I think how to use ABAP2XLSX is obvious as well, but that is not really the point.
In the same way in the comments above someone asked if people will not look at the existing example programs, why would they look at new ones? Obviously, they will not but that is not the point either.
The analogy I would use is the Sydney Harbour Bridge climb. You can pay to go up to the top and walk along the very top in a carefully supervised tour group. You could not always do that.
When the idea was mooted there were about a hundred arguments raised against it e.g., it will distract the traffic on the bridge, people will commit suicide by jumping off, and so on. So, the person proposing the idea came up with a list of cast-iron arguments against each objection and in the end they won.
Put another way there are people in this world who come up with new things, and a great many more who want to stop any sort of new thing whatsoever e.g. "That mobile phone / flat screen television / electric car / OO programming thing will never catch on, but just in case it might I am going to do everything in my power to stop it"
You would not think that people who work in the IT industry would think like that given the very nature off the industry, but there are an enormous amount of people who do think just like that. In this case the horrific new things are OO programming, Open-Source software of any sort, and GIT.
So, I am just trying to knock down the "there is no documentation" barrier.
I have heard a great new anti-ABAP2XLSX argument lately. This one runs "SAP now have come out with their own version of ABAP2XLSX and that is standard so I will wait to use that one rather than some sort of open-source equivalent. Of course, my organization will not have access to a version of ABAP with that new SAP XLSX thing in ti for about 5-10 years, which means I can hold off ABAP2XLSX until then, and then when we do have access to the SAP version, I will make some sort of excuse as to why we cannot use that one either"
Cheersy Cheers
Paul
I understand what you are trying to do. And yes, I know for a fact that there are huge numbers of people working in our industry - even highly technical people - who resist change to the utmost.
I find it weird, but there's a lot of it about. There's always a reason why I, my company, my country is a special snowflake that can't possible use this ubiquitous solution.
At worst, your efforts will nudge a few people away from their obdurate obstinancy. But you'll never convince everyone.
Best argument is always the No-Open-Source policy. Ask them, on which OS their ERP system runs. Good chance it's Linux 😁