How To Extract Formatted SALV Output As An Excel Document in the background
Recently, have come across a requirement to send output of report developed based on SALV as an excel attachment. To achieve the same we can use the method TO_XML of class CL_SALV_TABLE. This method will have the formatted output content as XSTRING value.
Here are the steps.
a. Variable Declaration.
b.Data Selection/ALV Customizing calls.
c.Call to Convert ALV Output as internal XML Format.
d.E-Mail Data Declaration.
e.EMail – Content Conversion/Body/Attachment Creation
Send E-Mail
Excel Output
we can also use TO_XML method to download the content as an XLS or XML documents in Abap Web dynpro based applications .
Here is the sample source code for this approach.
Hello,
Firstly well done, an dnot trying to take anything away from your solution, but as an alternative, if you were to search for ABAP2XLS on the SCN website you might be surprised to find that it's purpose is to create spreadsheets from SAP data, including SALV reports.
I have to conclude that the knowledge sharing aspect of SCN is not working as well as it might. Literally every week a new person posts a blog like the one above, describing how they have worked out how to export SAP data to excel in the background.
In some ways that's good, as it means there are lots of clever people out there, but it also means hundreds of people at any given time are busily re-inventing the wheel, unaware that the answer to their problem is a mouse click away.
Cheersy Cheers
Paul
Hi Paul,
Thanks for the comment. This solution is mainly for those can't install ABAP2XLS solution in their environment due to approval from the client.
Regards
Srini S
Strangely enough I have encountered IT departments where installing things like ABAP2XLS or SAPLINK were viewed with loathing and disgust.
I am lucky - in Australia in general and in my company in particular the focus is solely on getting the job done and we are not too fussed with hamstringing ourselves with illogical rules.
The famous quote on this matter runs thus "you will never understand bueracrats until you realise that for them process is everything and results count for nothing". We are the opposite in Australia.
Just to add to the irony, I had just finished the first paragraph in this email when my boss came up to me, saying he was in a huge hurry and needed a way to edit a particular Z table, and search for cell contents, and scroll properly, none of which SM30 was letting him do.
So I pointed him to a generic Z table editing program which let you edit Z tables in an ALV grid. He went away happy. And where did I get this program? I found it on the SCN in 2005, thought that would be useful and copied it to my SAP system.
So the fact my boss lets me install such things in our system rewarded him nine years later, sort of a Karma type thing. Conversly development managers who forbid such things on general policy grounds are shooting themselves in the foot.
Hi Paul,
I'm not sure that bureaucracy is really the main reason why SAPLINK is not allowed. At my project we don't currently allow SAPLINK - and this is really from a quality control perspective. My manager, who helped reach this decision actually worked on a subset of the original SAPLINK development. Our concerns are that SAPLINK allows the mass uploading (and downloading) of code into (and out of) our environment, and that code by definition will not meet with our own coding quality standards - or SAP's for that matter. So a lot of extra time and effort has to be applied to monitor code uploaded via SAPLINK.
Then when you consider that we have a team of offshore developers - all of whom could make use of SAPLINK, we are concerned that code quality could lapse. The custom code that has been developed for us can be removed from us very easily, and this is also a concern, especially from the intellectual property perspective (some of our custom code was developed by SAP custom development). I suppose you could argue that this is just bureaucracy, but I think these concerns are quite genuine.
This excel email utility though looks like something we can use, and has the benefit of being simple to implement, and not being tied to SAPLINK is a bonus from my perspective!
Regards,
Julian
I suppose it is a question of what you want to use SAPLINK for.
I had to migrate a very large number of custom objects - the programs, function modules, classes, database tables, data elements, domains, from our Australian SAP system to our German SAP system. I also wanted the documentation for each object to be copied.
This was in 2010. With SAPLINK it took a day. Doing it manually, I'd still not be finished. It still took me six months to convince the powers that be, and then only on condition that I deleted SAPLINK from the target system after I was done.
I have got some wonderful things from the SCN via SAPLINK. ABAP2XLS is a case in point. It does what it does really well, and if it doesn't meet high coding standards, then I just don't care. The amount of business benefit it gave us, instantly, was all that seemed to matter to our managers.
I also don't see the distinction between cutting and pasting a piece of code from an SCN blog into your system, and then correcting it if needed to meet your standards, from uploading the same thing via SAPLINK and then correcting it if needed to meet your standards.
The fact that you are worried that your offshore developers can't be trusted says all that needs to be said about outsourcing development. Even without SAPLINK in standard SE80 most ABAP objects - programs, screens, whatever - have a "download" option from the menu to store the code on your local hard drive. SAPLINK could speed this up, but really, if I wanted to steal vast amounts of code from a system I had access to, it would take no time at all even without SAPLINK.
That all sounds very negative, maybe I am bitter because I spent the last year dealing with a USA company, who I will not name, who had sacked all their American developers and outsourced development to another country, which I also will not name, and not trained the poor old outsourced developers first. I am sure that saved a lot of money, but it didn't work that well for actually getting the vast amount of flaws in their software fixed.
That sounds even more bitter, I'd better break off now before I throw myself off the nearest bridge.
Cheersy Cheers
Paul
P.S. Arrrrgggghhh!!! Splash!
No please don't jump off a bridge! - then where would I turn for the most entertaining and information rich blogs on SDN?
Usually when we want to import a lot of code like that we use transports.
I can see your point of view on the offshoring perspective. At my site though we don't have a legacy team of programmers - as we are a fairly new install - and so we are trying to build up a team, and these days its actually next to impossible to find onshore ABAP resources (particularly here in Scandinavia), they have mostly moved onto more senior things than coding abap it seems. I'm thinking of writing a blog on where we will get our future ABAP code architects from, as the well seems to be drying up fast. The offshorer's that I speak to almost all seem to have long term goals of becoming team leads. If they all succeed there will be a lot of team leads!
Its not that I don't trust our offshore developer's,, and its not necessarily the offshorer's we should worry about - but its just a matter that if you leave a door open long enough then someone somewhere will use it, so I prefer to put a good lock on the door...
Let us just say I have a sandbox system, and I have spent nine months developing this killer application. This has generated a vast amount of Z objects.
All is good, I now want to move all this from system ABC to my real development system DEF. I put everything in a big transport, get my BASIS people to put this in the transport queue, into DEF it goes, everybody happy bunny.
Then the users want some changes. I go to change my lovely program, which i wrote myself, and "only modify foriegn objects in an emergency". The system thinks because the program was written in ABC it is a foreign object. I have to change the original system via SE03 for all ten million objects one at a time (if anyone knows a faster way to do this I am all ears) or use the modification assistant on my own code.....
With SAPLINK all the imported objects think they arenative to the system at hand.
I don't know how lucky I am, living in Australia. SAP is massive here, virtually every single large company, and all government departments use SAP. So there is no real shortage of SAP types.
Where I do run into people who have offshored their ABAP development they are not happy bunny!
As to locking the door, as I said, if the lock on the door is not having SAPLINK, then I would see the gaping ten foor wide radius hole in the wall right next to the door is the fact it is so easy to download vast chunks of ABAP code onto your local machine just using the standard SE80.
Cheersy Cheers
Paul
I am yet to write a killer app.
Below reply seems suggest a faster way by selecting objects and entering MASS in command field.
That does the job. That is the textbook definition of an "easter egg" i.e. a little feature the programmer left in for themselves to make their life easier, but made sure no-one else knew....
It's the same with field exits - we are not supposed to use them any more but the transaction code to maintain them still works... it's just not on any menu option.
As an alternative to changing the original system of the objects (above), there is a classic badi (WB_MODASS) where you can disable the workbench check on original system (so that the modification assistant is not called if the original system is different). We use this on my project as we have a dual track development (one for AM and one for project developments). Perhaps there are other issues with original system though (outside of disabling the modification assistant); I'm not sure.
Hi..Great blog..Is there a way to download in the same format without emailing?
Thanks
Hakim
Yes there is:
It worked 🙂 ..Thanks
This functionality is already available as standard via Menu - List - Export-Spreadsheet in fore-ground. And CL_GUI_FRONTEND_SERVICES will not work in background . So, we need to use the table lt_bintab to generate a file in app.server directory for later use.
Yes. But this way you can download file on foreground without calling gr_table->display( ), or not? And If you need it on background then it is job for DATASET TRANSFER to app server, as you suggested 🙂
Hi Tomas - Just realized that only header part is coming up in the output and not the data..
I have followed the process that you have provided..
Here is the source code snippet:
SELECT * FROM sflight INTO CORRESPONDING FIELDS OF TABLE gt_outtab UP TO 100 ROWS.
TRY.
cl_salv_table=>factory(
IMPORTING
r_salv_table = gr_table
CHANGING
t_table = gt_outtab ).
CATCH cx_salv_msg. "#EC NO_HANDLER
ENDTRY.
DATA: lr_functions TYPE REF TO cl_salv_functions_list,
lr_layout TYPE REF TO cl_salv_layout,
ls_key TYPE salv_s_layout_key.
lr_functions = gr_table->get_functions( ).
*... §3.1 activate ALV generic Functions
lr_functions->set_all( abap_true ).
lr_layout = gr_table->get_layout( ).
* ... §4.1 set the Layout Key
ls_key-report = sy-repid.
lr_layout->set_key( ls_key ).
* ... §4.3 set Layout save restriction
lr_layout->set_save_restriction( if_salv_c_layout=>restrict_user_independant ).
*... Top of List, End of List
DATA: lr_content TYPE REF TO cl_salv_form_element.
PERFORM create_alv_form_content_tol USING space CHANGING lr_content.
gr_table->set_top_of_list( lr_content ).
PERFORM create_alv_form_content_eol USING space CHANGING lr_content.
gr_table->set_end_of_list( lr_content ).
**-- Call to display output.
gr_table->display( ).
***-- Convert the output to interal XML format
lv_xml_type = if_salv_bs_xml=>c_type_mhtml.
lv_xml = gr_table->to_xml( xml_type = lv_xml_type ).
call function 'SCMS_XSTRING_TO_BINARY'
exporting
buffer = LV_XML
* APPEND_TO_TABLE = ' '
IMPORTING
OUTPUT_LENGTH = GT_LEN
tables
binary_tab = GT_SRCTAB
.
call method cl_gui_frontend_services=>gui_download
exporting
bin_filesize = GT_LEN
filename = filename
filetype = 'BIN'
* append = SPACE
* write_field_separator = SPACE
* header = '00'
* trunc_trailing_blanks = SPACE
* write_lf = 'X'
* col_select = SPACE
* col_select_mask = SPACE
* dat_mode = SPACE
* confirm_overwrite = SPACE
* no_auth_check = SPACE
* codepage = SPACE
* ignore_cerr = ABAP_TRUE
* replacement = '#'
* write_bom = SPACE
* trunc_trailing_blanks_eol = 'X'
* wk1_n_format = SPACE
* wk1_n_size = SPACE
* wk1_t_format = SPACE
* wk1_t_size = SPACE
* show_transfer_status = 'X'
* fieldnames =
* write_lf_after_last_line = 'X'
* importing
* filelength =
changing
data_tab = GT_SRCTAB
* exceptions
* file_write_error = 1
* no_batch = 2
* gui_refuse_filetransfer = 3
* invalid_type = 4
* no_authority = 5
* unknown_error = 6
* header_not_allowed = 7
* separator_not_allowed = 8
* filesize_not_allowed = 9
* header_too_long = 10
* dp_error_create = 11
* dp_error_send = 12
* dp_error_write = 13
* unknown_dp_error = 14
* access_denied = 15
* dp_out_of_memory = 16
* disk_full = 17
* dp_timeout = 18
* file_not_found = 19
* dataprovider_exception = 20
* control_flush_error = 21
* not_supported_by_gui = 22
* error_no_gui = 23
* others = 24
.
if sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
endif.
Hi Abdul, I have copied your code and it works for me (i get MHTML excel file with all data). I only skipped "*... Top of List, End of List" part because it is not needed and I dont know what you have in subroutines... I dont know, maybe try also skip this part. And ALV display is returning data?
Still i am getting a blank output after removing tol / eol..
Hi Abdul,
was trying the code in this blog and indeed I'm facing the same issue as you, only the header part is coming up in the output and not the data...
did you solve it?
Thank you for any help
Flavio
For your info:
It's also possible to use xlsx type.
I actually prefer method explained in this blog over the abap2xlsx solution, because the format of the output is the same as in other standard export to excel functionalities in SAP.
And with abap2xlsx it seems you need to actually display SALV first and then convert it to excel. This is not the case with xml solution.
This works really well with .XLS but Is it possible to create attachment with .txt? I tried writing to the file in Binary format and then converting to string but it did not work.