Upload or Download in XLSX
Download in XLSX format:
My Problem Statement:
Whenever I have tried to attach XLS document to email or download XLS document from any ABAP report to presentation server. I have faced a warning message while opening the XLS file, mentioned below.
[ We can run SAP Program ‘BCS_EXAMPLE_7’ to simulate the scenario ]
My Client was pretty uncomfortable with the pop-up message and asked me to change some code in ABAP such that he doesn’t want to see this pop-up message in Excel. Initially, I thought to myself that “How can I possible remove a pop-up message in Excel from ABAP ?”
Solution found:
Moving from format XLS to XLSX has solved this issue for me.
Transform internal table into XML (XSTRING) and convert XML into Binary. Use this Binary file for attachments or downloading.
Include – TEST_GHO_DATA_UPLD_DWLD_FILF01
Form – EXTRACT_TO_EXCEL
Above mentioned Form in the include has the code on how to transform an internal table to XML and then convert XML into Binary.
[ I came to know about this information from a blog, unfortunately I couldn’t recollect that URL of the blog ]
Upload in XLSX Format:
I was breaking my head on how to upload a XLSX file into SAP. Then I have found a document written by Benu Mariantony on how to upload a XLSX file into SAP.
https://wiki.scn.sap.com/wiki/display/ABAP/XLSX+Upload+-+Unified+Approach
I have followed the blog and simulated the code and it worked for me. On other day, when I tried with different XLSX file, the program gave dump. Then I remembered the possible issue mentioned by Matthew Billingham in the wiki page. I have exactly ran into the same issue. The sequence of folders in “XL” folder has changed.
I have used similar logic to differentiate the folders. Code snippet mentioned below.
**--After getting all Parts/Folders available in Folder 'XL' into LO_PART2
**--Loop on no of folders and check the relationship type
DO lv_count1 TIMES. "No of Sub folders in Folder 'Xl'
DATA(lo_part2) = lo_parts1->get_part( sy-index - 1 ). "Get the each and every folder
IF lo_part2 IS BOUND.
DATA(lv_rel_type) = lo_part2->get_relationship_type( ). "Check relationship type
IF lv_rel_type = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet'. "Worksheet XML
lv_index_sheet = sy-index - 1.
ELSEIF lv_rel_type = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings'. "Shared XML
lv_index_shared = sy-index - 1.
ENDIF.
ENDIF.
ENDDO.
Thanks for sharing!
There is also a famous ABAP2XLSX project. I believe it handles at least the download part (as the name suggests 🙂 ).
ABAP2XLSX is a great tool and has served me well. It handles converting both to and from Excel. It is also very powerful and has many advanced features that you can play with after you've gotten familiar with the product.
Hallo Siva,
you will get the "corrupted" popup if you irgnore the length of the xstring when downloading the binary table.
I don't think it is this case. It is more likely that he is creating CSV (or tab delimited) file and calling it .XLS.
Jelena beat me to it, but as soon as I saw the title of this blog I double clicked on it so I could mention ABAP2XLSX in the comments.
That project must not be doing a good job of selling itself, as every month for the last five years at least, regular as clockwork, there is a blog like this one on SCN, talking about the problem of uploading and downloading Excel files from SAP and coming up with a different way to do it.
The usual argument against using ABAP2XLSX is that "open source is banned at my company" though I am 99.99% sure that in those cases the rule only applies to ABAP in those organisations - the "special snowflake" once again.
The other common argumnet, amazingly, is that it is too comphrensive, we only want a little bit of fucntionality for our tiny use case. My argument back againts that, is that tiny use cases invariably grow into huge use cases...
Cheersy Cheers
Paul
I think abap2xlsx is amazing, and I've got it implemented in some instance streams of my client. I considered it for my requirements on a new stream, and decided it was rather overkill. Literally, it was load data from various xlsx files and process it, coupled with being able to write sample template xlsx files to get people started.
The point of https://wiki.scn.sap.com/wiki/display/ABAP/XLSX+Upload+-+Unified+Approach is that it's quick and neat with a small footprint. I enhanced it so that the transformation is built dynamically.
IF
a multiple number of reports are using ABAP2XLSX in your productive system and, one day, for a reason we ignore, ABAP2XLSX is no more working
THEN
who will be responsible ? How will you explain: “we decided to use this tool but SAP is not responsible to maintain and to correct this code.”
ENDIF.
It is of course very easy for every SAP site to re-invent the wheel because then it is THEIR wheel. Is this the most effective way foward however>
In my case, there are indeed a multiple number of productive reports in my productive system using ABAP2XLSX. Indeed if I imported a new version they could break. It would not be SAPs fault. It would be my fault - because (a) I did not test the change I made and (b) did not fix it. Once it is my system it beocmes my code just like any other Z code I write.
However, just like my own Z code, I would not whinge and complain to the sky, I would test and I would fix and (because this is ope source) I would fix the code on GitHub so everyone else benefits because I CARE.
That is the point with open source projects. You get the benefit but you have to give something back. Nothing is for free.
To stress this again - and I cannot stress this enough - with standard ERP software you get a new version every five years and if it breaks you go through OSS and get it fixed eventually if you are lucky. With open source you get a new version every day and if it breaks you fix it yourself. This is a huge mind set change,
Once again, it beggars belief that people are willing to use open source things in every aspect of their life except for ABAP ... read that "ABAP is a special snowflake" blog. Hopefully it will open your mind as to what we are trying to achieve.
The question is - can you say with 100% certainty that your current orgaisation is not using open source code in any form at all? If you have a smart phone then you have already failed that test - and it could be said that smart phones are just as important to business as ERP systems these days, if not more...
Cheersy Cheers
Paul
Just to stress this again - because so many people miss the point - of you write a Z program or download a Z progam then thereafter it is YOUR problem not SAPs or anyon elses.
Taking responsibilty for one actions is sadlly lacking in the world as a whole, not just SAP.....