ABAP & Excel, why can't we just have something nice?

[larshp]

Exporting data to Excel in ABAP is a common topic, but there are many different options to choose,

• abap2xlsx


• CL_FDT_XL_SPREADSHEET


• XCO_CP_XLSX


• CL_SALV_BS_TT_UTIL


• SAP_CONVERT_TO_XLS_FORMAT


• XXL_FULL_API


• and more

But, I just want one, one that works, one that is nice. But what is nice?

1. Works everywhere

Something that works everywhere, I don't want to learn different APIs for different platforms. Ideally this means 702 and up to latest Steampunk.

Something like abapGit has worked for on-prem 702 and up for almost 10 years, so it is possible to build software that works on multiple versions.

abap2ui5 does an automatic downport in order to write the software in high syntax, while still supporting 702.

2. Public issue list

All software has bugs, some will be fixed, some not. But most important to me is easy access to check the issues, what are the known bugs, has someone had the same problem as me? It helps a lot to be able to search open and closed issues to find bugs in my code.

3. Suggest fixes

If I discovered a bug, I might have a fix for it, let me suggest that fix instead of having someone else spend the time to get to the same conclusion/fix.

4. Upgrade at any time

Let me upgrade the software at my convenience, not along with 1000s of other things. If I choose to consider it all working, there is no need to upgrade.

Reduce the impact by having multiple copies inside the same system, managed by different teams. That way each team can decide how/when to upgrade.

5. Easy testing

Easy ad-hoc testing, why require code to be installed? Something like codesandbox, but with the ABAP code in the left editor. This exists, see abap-openapi as an example, it runs the ABAP code but in the browser, on the fly.

This also goes for unit tests, just edit a file in notepad and push it to github, and it will trigger the unit tests running in seconds. See ajson example

6. Public versioning

If something breaks I want to be able to pull an old version, and or easily see the differences between the old and installed version. This makes it much easier to decide when to upgrade, or do debugging.

 

We keep getting more and more options, with fewer features, and a cluttered core plus ecosystem. The direction that SAP and the community is heading is just overhead for everyone.

Its the same story for JSON handling, HTTP clients and more in ABAP.

 

What's nice to you? And how do we get there?

[Jocelyn_Dart]

Hi Lars

One of those options should really be ... "Moving or already moved to SAP S/4HANA? Good news, spreadsheet download is a standard feature of many SAP Fiori apps. No development required".

[gregorw]

But this Excel download is only available in the Fiori app. That doesn't cover the usecases where you want to provide a nicely formatted, some fields write protected Excel that should be used to edit data and upload it afterwards back to the ABAP based system.

[larshp]

Next logical question would be if you are on SAP S/4 2022 or SAP S/4 2023(is 2023 released yet?)? If I want to write an Excel file using feature something, it should just be possible, no yearly S/4 upgrade to do build an Excel file

[Jocelyn_Dart]

Hi Lars

Downloads for most apps started with S/4HANA 1909 when it became a standard feature of SAP Fiori elements apps - first list reports, then analytical list pages, and then Smart Business and Web Dynpro Grid reports.

So if you are on S/4HANA you would most likely already be on one of those releases as the older releases are now out-of-maintenance.

These releases all evolve as you upgrade so there's nothing you need to do to reactivate the feature. For instance, as of 2022 we can also download  to PDF.  Plus MS Teams integration is coming soon.

I can agree that sometimes you have specific use cases for formatting... just suggesting you at least consider the standard built-in features first before considering  jumping to code...

There's always something more interesting for developers to work on.  Building a custom app, or implementing an AI scenario for example.

[Jocelyn_Dart]

Hi Gregor,

Sure -  if you want special formatting on your download that's definitely a valid use case.

In which case ABAP Cloud (the official name for what was previously called Embedded Steampunk) is a good clean core option.

Of course if it's very complex and the rules change frequently you might also want to consider a process automation bot for that - and avoid adding code to your system at all.

Mostly I'm just suggesting that whatever you are thinking about coding... it's good governance to make sure you aren't reinventing something that may already be available out of the box.

In SAP S/4HANA we now have almost 3K Fiori apps -  that includes at least 40 Import / Upload apps that I could find with a very quick check of the Fiori apps library.

 

 

[larshp]

Part of my argument is that Steampunk is not a clean core, a clean core should be without Excel(XCO_CP_XLSX), just let the users update that at will. Email templates is a feature delivered recently by SAP, I dont think it goes into a clean core, it should just be an add-on.

Going with process automation bot, well, I think that breaks almost everything in my wishes for something nice.

[larshp]

As an ABAP developer, we get work because the SAP standard functionality is not enough, which sometimes is building a specifically formatted Excel file.

Multiple SAP customers are still on 702, why should they be left out, from a technical perspective?

[SergioFerrariIt]

Let me add that there are requirements to generate "nice" Excel files as the response to an API (Request/Response) or when a certain event occurs and even in batch/background. In those cases the powerful standard feature of Fiori Elements cannot be leverage, isn't it?

 

[sh4il3sh]

you missed ole objects to create excel lol

[larshp]

Yea, and probably there is more, just OLE is really bad 🙂

[Sandra_Rossi]

Nice is also well programmed, intuitive (easy to use) and well established.

I guess abap2xlsx is well positioned but I have no idea for others:

                           1. Works    2. Public   3. Suggest  4. Upgrade  5. Easy  6. Public

Solution                   everywhere  issue list  fixes       any time    testing  versioning

-------------------------  ----------  ----------  ----------  ----------  -------  ----------

abap2xlsx                  almost?     yes         yes         yes         almost?  yes

CL_FDT_XL_SPREADSHEET

XCO_CP_XLSX

CL_SALV_BS_TT_UTIL

SAP_CONVERT_TO_XLS_FORMAT

XXL_FULL_API

Is there a nice opensource library in another programming language which we could do source-to-source translation to ABAP? (and is it a good idea?)

I also guess that it would be smarter and simpler to use an existing nice opensource library whatever its programming language is. Possibly a Webservice, but performance, availability and scaling also to be checked...

[larshp]

Thanks I actually wanted to create a table, but its not really possible in the blogging platform. All the SAP solutions/approaches are: No, No, No, No, No, and No

abap2xlsx does run a few unit tests, see https://github.com/abap2xlsx/abap2xlsx/pull/872, and with more work its also possible to get it running everywhere

Adding more servers to a solution is always more work, ABAP is a platform for business applications, so it does need to handle Excel, preferably in nice way

[olegbash599]

I think you mismatch the concept.

accroding to you description you need not excel, but file with mime-type application/vnd.openxmlformats-officedocument.spreadsheetml.sheet. (it is differ).

as for works everywhere: xslt-transformation works mostly everywhere. but I do not think that existing solution should be replaced. They are quite good ))

why do you need a spreadsheet-file "everywhere" especially where there are no MS Excel or Libre Office?)

UPD: any table in csv != spreadsheet.

[oblomov]

The definition of "nice" from (1) to (6) is just great and with abapgit, abaplint etc. this is really possible!

[Juwin]

How can someone even start to compare the 'standard' option XCO_CP_XLSX to ABAP2XLSX?!! XCO_CP_XLSX can't even do simple things (e.g column width optimization, write to different worksheets, update an existing worksheet etc)

If XCO_CP_XLSX is SAP's answer to ABAP2XLSX, then these kind of blogs about Excel in ABAP will continue to appear on discussion forums.

Thanks

Waiting to hear more from:
bfeeb8ed7fa64a7d95efc21f74a8c135

[matt]

Is	a table	really
not possible	on the	blogging platform?

[hardyp180]

A long long time ago, in a galaxy far, far, away the ABAP2XLSX code lived in the "SAP Code Exchange"

SAP reserved the right to take any code in that space and turn it into the core product and then charge customers for it without crediting the original code authors. Everyone was fine with that, SAP did in fact take some code from the code exchange, primarily to do with JSON transformations.

That was a long time ago but SAP could have just shifted ABAP2XLSX and renamed all the classes and then it would have been an SAP product. it would not have come anywhere near as far as it has being an open source project, but it would have avoided the need for SAP to try and re-invent the wheel fifty four times, and presumably when things like Fiori or the RAP came along things would not have been a problem....

Anyway we have to live in the actual world. In the SAP GUI ALV the Excel download was a standard feature from about 2000 so when SAP trumpet that Fiori can do the same thing you will forgive me if I do not jump out of my chair and sing and dance,

What I actually want is what I get from ABAP2XLSX - the ability to programmatically format how the download from my "list report" looks like. I agree if people are happy with the standard option that is just fine but in real life they never are.

[larshp]

oh, nice, there is a hidden toolbar

hello

world

[matt]

Yes, it allows a few more fun bits

---

Oh I made a mistake

♣

[larshp]

Juwin: Yea, and thats partly why we cant have nice things

[larshp]

Paul: Putting abap2xlsx into the core would make it worse for customers, potentially requiring upgrading to latest S/4 HANA, just to fix a bug in the source code.

[hardyp180]

I don't want ABAP2XLSX in the core. I was just making the point SAP could have taken it ten years ago and put in the core. instead, they have tried to re-invent the wheel with multiple different classes trying to do the same thing, only not as well.

The USA people have the saying "get off the pot" i.e., either do something yourself or let the community do it for you. At the moment ABAP2XLSX is obviously the best solution, and has the best support, but while there are all the other standard SAP classes every month someone "discovers" them and posts a blog on SCN about ABAP to Excel integration. Or, even worse talks about OLE. Or invents the whole concept from scratch with Z code.

[shais]

TBH, That sounds great, but in 90% of the business use cases, users who want to download the data to Excel would like to do it in background (automatically) and not in foreground, so the built-in functionality of the Fiori apps won't help much.

[Jelena]

I'm a bit late to the conversation, wanted to give this some more thought. It seems like ABAPers can definitely have nice things but only when we do it ourselves. Your own work have proven already that all of this niceness is possible.

I find it especially impressive and important that, as you say, abapGit does not require "latest and greatest", it works on early versions too. Sadly, SAP's general direction so far is to claim "we have awesome feature, all you need to do is upgrade". But this is not well received by the customers who don't like their ERP systems taken "upgrade hostage" just to get basic functionality. (I'd put everything you've mentioned here as "basic" in 2023.) SAP user groups have already spoken out about it recently.

I know you probably meant it rhetorically but this also kind of answers the "why" here. It doesn't make it right though.

Obligatory pimping of newsletter. 🙂

Thank you for posting this and putting these thoughts in a nice (no pun intended 🙂 ) form.

[christian_grail]

Hi Lars,

nice article and I agree that abap2xlsx should have just been integrated into SAP, but for some reason it didn't happen. It looks really nice and offers a lot of functionalities.

I'm currently working on enabling the old OLE based Excel integration to work in non SAP GUI for Windows environments like in Fiori environments where SAP GUI apps run in SAP GUI for HTML. The integration is based on XCO for export and an own XLSX importer. XCO now supports also multiple sheets.

https://blogs.sap.com/2023/03/08/how-to-enable-the-sap-desktop-office-integration-for-sap-gui-for-ht...

We've also updated SAP_CONVERT_TO_XLS_FORMAT to transparently fallback from OLE to an in-memory solution where OLE is not available.

Best regards,
Christian

[larshp]

heh, always another scenario/approach

Integrating it into SAP would make it difficult to "upgrade at any time", sometimes its difficult to have SAP fix bugs by providing notes. Having it outside would allow to upgrade at any time, install multiple versions to reduce regression testing across teams.