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.
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>
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
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 ).
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: