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.
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.
In the source code part, paste in this code for the transformation. Then save and activate the transformation.
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.
After executing the program and opening that file, you should see this excel sheet.
When you want to read an excel file, just create a new file from excel and save it as XML.
Then process it with the read functionality.