Skip to Content

Introduction

There are multiple ways to exchange data between ABAP and Excel, varying from csv files to OLE automation.

With the introduction of Simple Transformations in ABAP and an open XML standard in Excel, a new approach is possible.

As of Excel 2002 excel workbooks can be saved as XML files. This can be done, just by choosing the XML format in the save dialogue.

image

 

When the Save as type option “XML Spreadsheet” is chosen, an XML file is created. Documentation about this XML file can be found at these locations:

XML Spreadsheet Reference

XML in Excel and the Spreadsheet Component

Once the excel is saved as XML, it does not only save the data, but also all the formatting, properties, filters, …

As of this moment the Excel sheet is just an XML which we can process with a Simple Transformation in ABAP. The SAP documentation can be found here.

When we combine thee 2 elements, we have a quite simple and powerful toolset to create rich formatted excel files from ABAP and to read in ABAP any excel sheets that a user saves as XML format just from the save as dialog box.

 

In this Blog I will show how to create and read XML file. I have limited the XML tags processed to keep the process clear. However, you can add and process as many tags as needed.

Step 1: create the Simple Transformation

Go to transaction STRANS and create a new Simple Transformation called Z_XML_XLS.

image

In the source code part, paste in this code for the transformation. Then save and activate the transformation.

image

 The coding for the simple transformation is available in the WIKI code gallery. https://wiki.sdn.sap.com/wiki/x/oADDB

 

 

 Step 2: create a simple ABAP program which calls this transformation

The functionality of this coding is to create a new excel sheet with some data and some basic formatting from ABAP on the one side, and also to read an excel sheet saved as an XML and output some content.

To create this program go to transaction SE38 and create a new program called Z_XLS_XML and copy in this source code. Then save and activate the program.

 

 The coding for program is available in the WIKI code gallery. https://wiki.sdn.sap.com/wiki/x/oADDB

  Test the program

 

When you execute the program to create an excel file, you fill in the selection screen like this.

image

After executing the program and opening that file, you should see this excel sheet.

 

image

 

When you want to read an excel file, just create a new file from excel and save it as XML.

image

 

Then process it with the read functionality.

image

 

image

To report this post you need to login first.

5 Comments

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

    1. Koen Van Loocke Post author
      Hi, I changed the coding in the WIKI to refer to a standard type. It is now
      li_upload                  type table of RCF_FILE_CONTENT,

      hope it helps.

      Koen

      (0) 
  1. Josin George
    Hello Koen,

    I am new to XML, can you please tell me answers for the query below?.

    How can we use simple transformatios to create excels with multiple sheets? Suppose if i have got 2 sheets do i’ll have to write tags twice?

    Thanks a lot,

    Josin

    (0) 

Leave a Reply