Technical Articles
Easy way to download internal table data to an Excel file in the Presentation Server
Introduction
In certain cases, you need to download internal table data in Excel in the Presentation Server. I have tried different FMs but they were not fulfilling my requirements properly. Some of them are complex to implement also. So, I am writing this blog post describing an easy way.
The code mentioned below can be simply copied & changing the internal table & the download path will help to achieve the output in an Excel file in the Presentation Server.
Solution
Please follow the below steps
- Create a type for the Column names of the internal table.
TYPES : BEGIN OF ty_struct, col_name(30), END OF ty_struct, ty_columns TYPE STANDARD TABLE OF ty_struct WITH EMPTY KEY. - Declare the necessary internal tables.
DATA : lt_columns TYPE ty_struct, lt_temp TYPE dfies_tab. - Fetch the column names of the DDIC structure using the FM: ‘DDIF_FIELDINFO_GET’. If you are not using a DDIC structure then you will have to populate the headers explicitly. For populating headers explicitly please check this Link.
TRY. CALL FUNCTION 'DDIF_FIELDINFO_GET' EXPORTING tabname = 'ZSTRUCTURE_NAME' langu = sy-langu TABLES dfies_tab = lt_temp EXCEPTIONS not_found = 1 internal_error = 2 OTHERS = 3. IF sy-subrc = 0. lt_columns = CORRESPONDING #( lt_temp MAPPING col_name = fieldtext ). ENDIF. CATCH cx_root. "Please write your own exception handling code here ENDTRY. - Call the FM: ‘MS_EXCEL_OLE_STANDARD_DAT’. Pass both the tables namely DATA table COLUMN NAME (lt_columns) table & then the file download path.
IF gt_data_table IS NOT INITIAL. CALL FUNCTION 'MS_EXCEL_OLE_STANDARD_DAT' EXPORTING file_name = p_download TABLES data_tab = gt_data_table fieldnames = lt_columns EXCEPTIONS file_not_exist = 1 filename_expected = 2 communication_error = 3 ole_object_method_error = 4 ole_object_property_error = 5 invalid_pivot_fields = 7 download_problem = 8 OTHERS = 9. IF sy-subrc <> 0. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ELSE. MESSAGE |File downloaded successfully| TYPE 'S'. ENDIF. ENDIF. - That’s it. 🙂
N.B: MS_EXCEL_OLE_STANDARD_DAT is an internal function module that is not released.
Ah finally another excel thread, i am going to grab my popcorn ! 🙂
You are using a non released SAP function module.
No one can confirm it will be in your system tomorrow. Or maybe it will be there, but it will be used to create popcorn for Sercan.
I will not press the "like" button, nor I will share it. Nothing LinkedIn related too, sorry.
But I commented it. And I will suggest an improvement to the entry: put a warning on it about the non-released status of the function module.
https://github.com/sapmentors/abap2xlsx is the way to go (in my not-so-humble opinion), sorry.
Yes, MS_EXCEL_OLE_STANDARD_DAT is an internal function module that is not released but it serves the purpose(basic output) if you have it in your system that's why is post is written.
The blog post is updated now. Thanks.
I would like the second the motion that ABAP2XLSX is the way to go here. The OLE technique takes a very long time.
As background the reason for the first comment on this blog is that every single month for the last ten years someone has posted a blog about how to upload/download from Excel to ABAP and most of the authors have not heard of ABAP2XLSX.
A common argument against ABAP2XLSX is "my company does not allow open source ABAP products" despite allowing the use of the internet, and smartphones, and cars, all of which are crawling with open source software.
The other common argument is that ABAP2XLSX is far too complicated to use, and I don't agree with that either.
A common argument against ABAP2XLSX is "my company does not allow open source ABAP products" despite allowing the use of the internet, and smartphones, and cars, all of which are crawling with open source software.
The other common argument is that ABAP2XLSX is far too complicated to use, and I don't agree with that either.
Try this Google query:
"what percentage of used software is open source"
You may be surprised at the results, especially as many of the surveys are up to ten years old and the figure only goes up over time.
So even companies like the Ford Motor Company, which is over a hundred years old, uses open source software in its' products i.e. cars.
The point I am making is that at your company they allow people to use mobile phones at work, or use the internet at work, or indeed use any sort of software at all at work, or to drive, then they are tacitly endorsing open source software, even if their official position is that it is banned.
It would not be very sensible of me to do a series of blogs saying how to program in ABAP2XLSX given that I give very detailed instructions on how to do just that in my "ABAP to the Future" books... the first of which came out about seven years ago.
There are also about twenty or thirty demo programs that come with the ABAP2XLSX package, each demonstrating one aspect of what is possible, just like the standard SAP demo programs. My suspicion has always been that people get scared off those demo programs, and indeed ABAP2XLSX as a whole because it is all methods and classes as opposed to FORM routines.
Cheersy Cheers
Paul
There are also about twenty or thirty demo programs that come with the ABAP2XLSX package, each demonstrating one aspect of what is possible, just like the standard SAP demo programs. My suspicion has always been that people get scared off those demo programs, and indeed ABAP2XLSX as a whole because it is all methods and classes as opposed to FORM routines
I don't think so classes and methods are around for years even before I started 15 years back. And OOP is no big deal that people will be afraid may be a handful old generation who never upgraded as they never needed to but the new generation that knows and has upgraded even they are not aware of this. There is a thing called documentation that we do in the software industry if programs were so readable then no one would have cared about documentation.
You may be surprised at the results, especially as many of the surveys are up to ten years old and the figure only goes up over time.
So even companies like the Ford Motor Company, which is over a hundred years old, uses open source software in its' products i.e. cars.
Many companies are using open source that is not the point in my company a leading SAP partner we have a clear rule, we just can not pick anything available for free and use it there could be IP issues, rights issues, etc. So, we have to move carefully. Also using this piece of code that is unofficial is almost like using an unreleased function module. At least you know whom to blame here no one will even take the blame because they will claim it is for free. in short you are making a meaningless argument
It would not be very sensible of me to do a series of blogs saying how to program in ABAP2XLSX given that I give very detailed instructions on how to do just that in my "ABAP to the Future" books... the first of which came out about seven years ago.
Thanks to SAP Press for the help. Not many people buy SAP press books so saying you wrote that in a book that not many have access to does not makes sense. There are a series of 5/6 articles on BOPF I love to call it the most definitive material to start by James Wood and he authored it on SCN despite all that being present in the book by him on ABAP Objects published by the SAP Press.
So, the point is accessibility and just because the people behind it never tried to make that accessible people do not use it they do not know about it. So, the choice is yours you want to make the masses aware and make it available in the same spirit of free software or you want to make it hard and make the only avenue available is buying your book.
Note: I bought the book "ABAP to the Future", a few years back whatever version was there and returned because it did not met my expectations of a typical technical book I would love to read.
Kind Regards,
Prasenjit Singh Bist
Why do people even bother cloning abap2xlsx? 115 unique cloners these last 2 weeks: Traffic · abap2xlsx/abap2xlsx (github.com)
How are you sure people don't use abap2xlsx, just because you see few people saying it's unusable or whatever, and that nobody says the tool helped? (if there were statistics, maybe we could count more people saying they used it than people rejecting it)
Nobody can tell how much abap2xlsx is used, so it's not possible to argue whether you're right or wrong.
Why do people even bother cloning abap2xlsx? 115 unique cloners these last 2 weeks: Traffic · abap2xlsx/abap2xlsx (github.com)
There are 4 million ABAP developers around 15,000 SAP projects at this moment and only 115 unique clones that too does not guarantees they were deployed in real project. The number sound not too encouraging right. Assuming 50% needed some excel related custom solution the adoption is not that great and the question is still how many were deployed in customer solutions that got delivered and billed and how many were for exploration and people gave up seeing the complexity.
How are you sure people don't use abap2xlsx, just because you see few people saying it's unusable or whatever, and that nobody says the tool helped? (if there were statistics, maybe we could count more people saying they used it than people rejecting it)
It's good to love what you do but never fall in love with your own creation. Anyways the only matter that so many questions on excel are put to this day and they never used the product in reference plus they are trying multiple approaches other than this assumed to be favored solution raises a deep question that it does not have the acceptability. My comment started from a post by an SAP developer and it drew so far so are even 50% SAP developers working on ABAP platform, aware of this solution?
One more point just cloning does not means people found it useful or used it they could have deleted outright rejected it as well. I know 3 developers top class sitting next to me and they download so many things but that does not means they use it.
ABAPGit is an awesome tool and I love it I have on my machine for a Scandinavian client and I don't use it not because it is not useful but because client's Architect, in written asked not to use it.
Finally, I have used it and many times found other ways to get the work done. So, the point is not whether it is right or wrong the point is what has been done to make it popular. Blog series more promotion so that the community knows it and when I refer to community it is not a coveted few of an inner circle I mean the wider community out there doing work for the SAP customers.
I only gave an advice to make it more easily accessible to the wider community and let more people use it and for that there needs to be materials available with quality documentations. It can not be go and read my book or just go and scan through the 20 demo programs. I have no personal interest any one uses it or not.
Thank You!!!
Prasenjit Singh Bist
It's not "my own creation", the tool was created by Ivan Femia in 2010, I've just been around since 2021, like many other people, to support the tool (bug fixes, etc.)
Off I go again. I was reading your argument with Matthew Billingham about ADT yesterday. Very interesting. Anyway, back to ABAP2XLSX.
First off - have you ever talked to Shaun Snapp from Brightworks? His blogs have a lot to say about SAP generally and people like me, and in fact me specifically. I think you two would get on like a house on fire.
Anyway you ask if even 50% of developers are aware of the ABAP2XLSX solution. The answer is NO. That is crystal clear. Every month a new blog comes out re-inventing the wheel and then me and Sandra and whoever else point the poster at ABAP2XLSX and they admit they have never heard of it.
So the entire point I (and the so-called inner circle) make every time is that clearly no-one has heard of it so when someone tries to re-invent the wheel we point them at ABAP2XLSX. Then, as I have said, sometimes the poster fights back.
Also I would just like to make a distinction here between two sorts of ABAP applications:-
(1) the ones you create that are only used by your own organisation
(2) ABAP applications created with the express intent of being on-sold to external customers
The type of abapGit projects to be found on
dotabap.org
are aimed at the former. That is to try and avoid ten million companies all spending time and effort creating the exact same thing - what you might call "boiler-plate" code - when they could be spending that time and effort on company specific value-add.
Anyway you ask if even 50% of developers are aware of the ABAP2XLSX solution. The answer is NO. That is crystal clear. Every month a new blog comes out re-inventing the wheel and then me and Sandra and whoever else point the poster at ABAP2XLSX and they admit they have never heard of it.
Thanks for following ADT and Shaun Snapp and whoever. My point is simple- there is lack of awareness of the solutions that you spoke about.
The exact blog where there was someone who said they have issues using open source, you replied everyone uses it and I commented no not everyone every organization has norms.
Now back to the actual subject- The points is simply providing a GitHub link or it's in my book does not helps. It's a decade old project and still people do not know that simply means enough has not been done to create awareness. How can it be done- some blogs and posts to stir up some interest.
Creating a boiler plate and assuming we did great help for others and people will pick it up- No it does not works that way. You have to go out to masses and help them understand and read those things. Now, I don't understand what crime have I done by suggesting that 🙂 You are trying to corner me this way or that way. This project has powerful features and it's sad people are not aware.
Finally it's up to the people who own this project to do whatever they want. I hope it helps you save your house from fire and I don't know what Mr Snapp talks about you not my area of interest.
First off - have you ever talked to Shaun Snapp from Brightworks? His blogs have a lot to say about SAP generally and people like me, and in fact me specifically. I think you two would get on like a house on fire.
Did you try that with him putting the house on fire? Thanks for your suggestion but I don't have the time for that. His blogs provide deep insight and I follow them. Very reasonable man indeed. I had my share of doubts but then when I analysed his blogs mostly on HANA and Fiori- my doubts turned into deep admiration for his courage to tell the truth.
Recently I read - https://www.brightworkresearch.com/how-jelena-perfiljeva-strongly-agrees-with-brightworks-analysis-of-sap-fiori/ ; a vey strong endorsement right ?
https://www.brightworkresearch.com/how-sap-resources-make-false-statements-about-brightwork-research/
One more point: I have no argument with Mr. Matthew, I have my opinions and share of problems as a developer with ADT and he commented on that so, I replied back. There is no argument because that would waste my time not solve anything he is not the VP in charge of ADT tools.
I don't understand how you are drawing parallel between me and Mr. Snapp and I find it neither disturbing nor disgusting just ridiculous it's like a Monday afternoon dark comedy. What more to expect here on SCN sadly huh.
Thanks,
Prasenjit Singh Bist
I certainly found it easy enough to use when I implemented it for the first time at a client site (a pharmaceutical company, so very hot on CSV, SOPs and Quality Control. At the time about 120 years old).
It must be those extra 30 years which do all the damage to the mindset 😉
maybe I missed it but I think most important apart from the non-released warning, is that this only works if the table contains character fields only. no Weight, Quantity etc. it will just simply cause a dump.