ABAP WebDynpro – Download Formatted Excel using XML

By | November 11, 2013 | WebDynpro ABAP | 7,924 | 7

Formatted Excel can be downloaded from GUI using the OLE but OLE wont work with WebDynpro application. Let’s see how we can download formatted excels using XML.

Requirement & Solution search

Download excel file so that user can be guided to fill file easily.

Approach available: Excel download from WebDynpro Application can be done by simply concatenating fields and then adding row below it. But when formatted cells are required this approach fails due to Excel property. If a context is created using dictionary structure it will be good only for displaying names.

so the question is What other solution is available to get the formatted excel downloaded? Below are few references which made it possible.

Approach – XML using Transformations

Approach is to create and XML document and download it. It can be achieved via:

  • Transformation - Create Transformation, pass the text and receive the XML results
  • Concatenating the tags and appending them to text – This is time consuming and error prone

I started with the second approach, like:

APPEND INITIAL LINE TO P_T_XML_DATA  ASSIGNING  <FS_XML>. 
MOVE:   '<?xml version="1.0"?>'  TO  <FS_XML>-TDLINE.
CONCATENATE TEXT <FS_XML>-TDLINE CL_ABAP_CHAR_UTILITIES=>NEWLINE INTO TEXT.
"------ rest of the XML code
"-------
"
"
"-below is end of appended code.
APPEND INITIAL LINE TO P_T_XML_DATA ASSIGNING <FS_XML>.
MOVE:  '</WORKBOOK>' TO <FS_XML>-TDLINE.
CONCATENATE TEXT <FS_XML>-TDLINE CL_ABAP_CHAR_UTILITIES=>NEWLINE INTO TEXT.
 

Though this is workable solution, but is error prone and slight miss of tag is head banging. I got Script Parse error and when checked few tags were open.

Blog ABAP and Excel – Create Formatted Excel using XML by Naimesh on SDN explains how to use transformation for use, bingo! Matches the requirement, since GUI_FRONTEND cannot be used, dummy data being passed to XML as a source as table and rest of the XML generated is pasted in ‘Z’ transformation and in result is XML.

Tutorial

Here are the steps to create formatted Excel.

Step 1: Setting up WebDynpro Application
Creating WebDynpro component

Activate all the related WD objects

Put a button in the View for download

Assign an Action to the button. This action would be triggered when button is pressed

Method ONEXCEL_DOWNLOAD looks like:

Step 2: Setup Transformation

Create Transformation in Tcode STRANS

Provide Short Description and select type XSLT

Generated default XML looks like

Replace the generated default XML code with this XML

 
<?sap.transform simple?>
<?mso-application progid="Excel.Sheet"?>
<tt:transform xmlns:tt="http://www.sap.com/transformation-templates">
  <?mso-application progid="Excel.Sheet"?>
 
  <tt:root name="table"/>
  <tt:template>
 
 
->>>>XML code here-<<<< 
 
 
 
</tt:template>
 
</tt:transform>
 

Like this:

Step 3: Excel Workbook as XML
Create a sample excel with Drop down, created without using references all hard coded Values. Note here is reference wouldn’t work if placed in dropdown here.

Save the file as XML

Opening XML into Notepad++ to check XML. Copy the generated XML code to transformation

Step 4 - Putting Everything together
Copy the XML into Transformation at the point (shown in one of the screenshots above)

Excel Download Action Mehtod
Call Transformation, Convert to XML, Download the File as Excel

Method ONACTIONEXCEL_DOWNLOAD

 
  DATA: ITAB TYPE STANDARD TABLE OF CHAR1,
        XTEXT TYPE XSTRING,
        XMLSTR TYPE STRING.
 
  CALL TRANSFORMATION ZG_XML_FOR_EXCEL_XSLT
                    SOURCE TABLE = ITAB
                    RESULT XML XMLSTR.
 
  CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
    EXPORTING
      TEXT   = XMLSTR
    IMPORTING
      BUFFER = XTEXT.
 
 
  WDR_TASK=>CLIENT_WINDOW->CLIENT->ATTACH_FILE_TO_RESPONSE(
           I_FILENAME   = 'SAMPLE_DOWNLOAD_FILE.XLS'
           I_CONTENT    =  XTEXT
           I_MIME_TYPE   = 'EXCEL'
           I_IN_NEW_WINDOW = ABAP_FALSE
           I_INPLACE       = ABAP_FALSE ).
 

Output

When you run the WD application, you would see something similar to this

Pressing the Button will display the prompt to ask for Downloading the file

File is same as what we have created in Sample:

Like It? Share!!

Don't miss an Update

Get notified of the new post, right into your inbox

Mohinder Chouhan{2 articles}

I have been working in SAP-ABAP for last 7 years, Infosys LTd. My main interest areas are Forms(SmartForm, Adobe), ALVs( Container based, SALV). Many times, I Debug Standard transactions to understand their framework and to learn from them. Recently, I have started exploring to Webdynpro ABAP.
Follow :

Explore all of his 2 articles.

Load comments

7 Comments

  • tempx

    I took me a while also to find it but here you can find abap2xlsx sources (SAPLink format)

    https://sap.assembla.com/code/abap2xlsx/subversion/nodes/438/build

    I use the following code to export to excel from WD, it is reusing the SALV_WD_TABLE logic:

     
     
    METHOD EXPORT_TO_EXCEL.
     
      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.
     
     
      lo_nd_table = ir_node.
     
    * 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_false ).
     
    ENDMETHOD.                    "export_to_excel
     
     
  • Steve Oldner

    Thanks Mohinder!

    Good article and very useful! Funny how that Naimesh guy always shows up!! LOL!!
    I really appreciate the links to SDN also. This gives the other plagiarizer – or developer, the opportuninty to develop their ‘best practice’.

    Al always, a real joy reading these posts.

  • Hello tempx,

    Thanks for sharing your approach for simple native ALV “type” formatted excels.

    I helped Mohinder to find the source for abap2xlsx on SCN Code exchange via this link. I guess, the source is deep down buried somewhere on that code exchange page …

    Thanks,
    Naimesh Patel

  • Hello Steve,

    You know me – I’m everywhere at least on ABAP space. hehehehe … Just kidding.
    Appreciate your inputs.

    Thanks,
    Naimesh Patel

  • Mohinder

    Hi Tempx,

    Thanks for input on ABAP2XLS, Naimesh and I tried to find source code, while going through the Install guidelines for .nuggs, there are references to change classes to Z , I was bit concerned. So chose ‘ ticket for the long way round…’ . Will try you approach as well.

    Between I found t-code DWDM also beneficial.

    Thanks
    Mohinder

  • Naveen chowdary Kommineni

    Hi Mohinder Bhai, This is Naveen, I find this very useful, great job.

  • Mohinder

    Hi Tempx,

    I tried your code approach, it works well if you have an ALV on screen and is data filled. But by creating a context and using as node , result is not expected as desired.

    Requirement I had was to generate a formatted excel, for user to fill in again and upload back. There is not data availabe at run time and ALV is not filled.

    Though Upload excel is still challenge working on it, approach in your posts help to understand reuse existing components SALV_WD_TABLE and modulate it.
    Thanks it was good learning.

    Thanks
    Mohinder

Comments on this Post are now closed. If you have something important to share, you can always contact me.

You seem to be new here. Subscribe to stay connected.