ABAP WebDynpro - Download Formatted Excel using XML

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.

Abap2XLS project, I tried and navigated a lot to find the source in repository but couldn’t navigated to right place 🙂
XML solution
A. Document Export Webdynpro ABAP table Data to Cell Formatted Excel Sheet by Krithuika helped to build solution
B. In on of the comment on the same document by Pavan nailed down approach.
C. Blog ABAP and Excel – Create Formatted Excel using XML by Naimesh helped nailing the solution which elaborates the requirement in same manner, explanation about transformation and benefits.
D. Document ABAP – XML Mapping by Peter McNulty discusses about the structure usage and advantages.

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:

7 Comments

tempx

# November 12th, 2013 at 5:26 am

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

# November 12th, 2013 at 5:48 am

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.

Naimesh Patel

# November 12th, 2013 at 8:52 am

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

Naimesh Patel

# November 12th, 2013 at 8:56 am

Hello Steve,

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

Thanks,
Naimesh Patel

Mohinder

# November 12th, 2013 at 10:39 am

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

# November 12th, 2013 at 10:59 am

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

Mohinder

# November 13th, 2013 at 11:26 am

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

Keep in touch. We would do great working together.

Welcome !

Write Green Programs

Display ALV Grid on List Generated by WRITE

Why NOT to have a wrapper around Exception (RAISE) ?