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.

/wp-content/uploads/2014/02/scn_blog_alv_decl02_388605.png

b.Data Selection/ALV Customizing calls.

/wp-content/uploads/2014/02/scn_blog_alv_call02_388606.png

/wp-content/uploads/2014/02/scn_blog_alv_call03_388613.png

/wp-content/uploads/2014/02/scn_blog_alv_call04_388614.png

c.Call to Convert ALV Output as internal XML Format.

/wp-content/uploads/2014/02/scn_blog_alv_call05_388615.png

d.E-Mail Data Declaration.

/wp-content/uploads/2014/02/scn_blog_alv_call06_388616.png

e.EMail – Content Conversion/Body/Attachment Creation

/wp-content/uploads/2014/02/scn_blog_alv_call07_388617.png

Send E-Mail

/wp-content/uploads/2014/02/scn_blog_alv_call08_388618.png

Excel Output

/wp-content/uploads/2014/02/scn_blog_alv_call09_388622.png

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.

To report this post you need to login first.

18 Comments

You must be Logged on to comment or reply to a post.

  1. Paul Hardy

    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

    (0) 
  2. Paul Hardy

    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.

    (0) 
    1. Julian Phillips

      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

      (0) 
      1. Paul Hardy

        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!

        (0) 
        1. Julian Phillips

          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…

          (0) 
      2. Paul Hardy

        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

        (0) 
          1. Paul Hardy

            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.

            (0) 
            1. Julian Phillips

              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.

              (0) 
    1. Tomas Buryanek

      Yes there is:

      CALL FUNCTION ‘SCMS_XSTRING_TO_BINARY’

           EXPORTING

             buffer        = l_xstring

           IMPORTING

             output_length = l_size

           TABLES

             binary_tab    = lt_bintab.

         cl_gui_frontend_services=>gui_download(

           EXPORTING

             bin_filesize              = l_size    ” File length for binary files

             filename                  = ‘C:\TEMP\test.xlsx’    ” Name of file

             filetype                  = ‘BIN’    ” File type (ASCII, binary …)

           CHANGING

             data_tab                  = lt_bintab    ” Transfer table

      (0) 
      1. Srinivasasubbu Balasubramanian Post author

        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.

        (0) 
        1. Tomas Buryanek

          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 🙂

          (0) 
      2. Abdul Hakim

        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_keyreport = syrepid.

        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 sysubrc <> 0.

        * MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

        *            WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

        endif.

        (0) 
        1. Tomas Buryanek

          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?

          (0) 

Leave a Reply