Skip to Content

Using/Abusing standard WD ABAP ALV functionality to export to Excel

What a lot of cool stuff

I’ve recently been impressed by the level of functionality that SAP is delivering for me as a developer. Some of it is really good stuff! Exporting a table to Excel in Web Dynpro used to be a absolute pain – but now it’s really simple – just use the ALV component – and it’s all there!

Which is great… but!

You can’t export from the ALV grid if you use cell variants and you can’t use the ALV grid if you start doing some very complex things with your tables. So does that mean that you are now back to square one when it comes to developing an export to Excel?

Hack time – A Bad Bad Thing?

Are we back to square one? Well, obviously no – if the ALV grid can do it so can we, the question is how? And here is my conundrum – I’ll share some code here which I’ve used to “trick” the ALV export utility into exporting the values from a context node that I’ve specially created for export. Now these classes etc don’t seem to have big “do not use” “SAP Only” flags on them – but then again, they are not very well documented either.

So am I doing a bad bad thing here? Or should all this code be able to be used like this?

 

 data: lo_nd_table type ref to if_wd_context_node,
        lo_component type ref to cl_salv_wd_c_table,
        lo_interface type ref to if_salv_wd_comp_table_if,
        lo_cm type ref to cl_salv_wd_config_table,
        lo_data_table type ref to cl_salv_wd_data_table,
        lo_model_table type ref to cl_salv_wd_model_table,
        lo_result_data_table type ref to cl_salv_wd_result_data_table,
        l_xml_content    type xstring,
        l_mimetype type string,
        l_filename type string.


* create a reference to the ALV component logic
  create object lo_component.

* get a reference to the interface for updating details of the ALV table
  lo_interface = lo_component->if_salv_wd_component_table~get_interface( ).

* set the source of the data that we want to export to excel for use in
* building the ALV table representation of the node - not for data, just for
* columns and that sort of thing
  lo_nd_table = wd_context->get_child_node( name = wd_this->wdctx_table ).

  lo_interface->set_data(
    exporting
      only_if_new_descr = abap_false
    changing
      r_data_source     = lo_nd_table ).

* get a reference to the configuration model - where we can set column names etc.
  lo_cm = lo_interface->get_model( ).

* create the ALV data table
  create object lo_data_table.
* set the source of the data
  lo_data_table->set_data( changing value = lo_nd_table ).

* create a model table - where we merge the data and the column setup
  create object lo_model_table
    exporting
      r_data = lo_data_table.

* set the table configuration to the configuration model we built from our node
  lo_model_table->set_cm( lo_cm ).

* now create a "result" table to allow us to do things with this model
  create object lo_result_data_table
    exporting
      r_model = lo_model_table.

* update the result table with the current context values
  lo_result_data_table->if_salv_bs_result_data~refresh( ).


* now convert the result table into an XML representation that we can send to the user
* there are various types that could be used - but I'll used the MHTML format - it does
* exactly what I want - which is a simple formatted MS Excel output
  call method cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform
    exporting
      xml_type      = if_salv_bs_xml=>c_type_mhtml
      r_result_data = lo_result_data_table
      xml_flavour   = if_salv_bs_c_tt=>c_tt_xml_flavour_export
      gui_type      = if_salv_bs_xml=>c_gui_type_wd
    importing
      xml           = l_xml_content
      filename      = l_filename
      mimetype      = l_mimetype.


* and finally send this to the user
  cl_wd_runtime_services=>attach_file_to_response(
      exporting
        i_filename      = l_filename
        i_content       = l_xml_content
        i_mime_type     = l_mimetype
        i_in_new_window = abap_true
        i_inplace       = abap_true ).
To report this post you need to login first.

8 Comments

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

    1. Chris Paine
      You are most welcome – the for the feedback – the MHTML formatting is pretty cool isn’t it!
      (0) 
      1. Sergio Ferrari
        Yes Chris,
          after years of experience I’m sure that for the end-users everything begins and ends with an Excel.

          The process usually begins with:
          “I have all the data within an Excel file and I want to upload it into SAP to update …”

          And the process always ends with:
          “…is it possible to download this in Excel so that I can apply new formatting, sort, filter, create a pivot table a graphic and so on?”

          As lot of people says “Excel is the financial lingua franca”

        Sergio

        (0) 
  1. Benjamin Williams
    I’m new to the whole SAP ABAP ecosystem, but having come from MS C#/SQL environment, if you use “system” and undocumented features you run the risk of that functionality not existing (or changing without notice) in the next version or service pack.

    I love your code, even if I don’t understand it all.  But I’ll play around with it and see what I can learn.

    Thanks for the morning inspiration.

    (0) 
    1. Chris Paine
      See – the thing is that these aren’t marked as unreleased – and the functionality and interfaces need to be maintained to allow the SAP code to function. So I’m not so much worried about it breaking from release to release…

      But it’s more I don’t think that anyone invisaged the code being used this way – and perhaps it goes against the design model. And perhaps there is a good reason for that model.

      Good luck in using it!

      Chris

      (0) 
    1. Chris Paine
      Hello Stefan,

      I think you mistake the gist of my post – it is all about the code – not the functionality. The question posed is should you be able to re-use/abuse the ALV code in this manner or should use like this not be supported?
      A functional demo in this context would be quite inappropriate.
      I also believe that people should be free to post (within reason and the SCN guidelines) anything that they wish within blogs, this should extend to and include purely technical questions such as covered in this blog.
      I hope you understand better now my purpose in this blog.
      Kind regards,
      Chris

      (0) 
  2. Volker Weckbach
    This blog is really helpful! I don’t want to know how long it took to figure out which classes have to be called to make this work. I guess you analysed the coding of the WD-ALV Component. Thank you for this blog, I used the coding to implement a own Export-to-Excel-Button outside the ALV-Component. The standard function has a dropdown-box which doesn’t look nice at all.

    Greetz,

    Volker

    (0) 

Leave a Reply