In this recipe, we will see how we can use simple transformations in order to download the contents of an internal table into Excel format. The advantage of this method is that the Excel file will contain the desired font size and colors without any programming effort required.
We will first create a sample Excel file in XML format. Then, we will upload it via the transformation editor XSLT_TOOL and generate a transformation accordingly. (The Excel XML and the transformation language may be mixed, thus the Excel XML is converted into a transformation.) The generated transformation will serve as a template representing our Excel file format. We will make slight changes in it and call it in our ABAP program in order to generate the Excel file.
The primary emphasis of this recipe is to generate an XML string containing the content of the Excel data in XML format. The download part of the XML will not be shown.
We will now see in detail the required steps:
- We first create an Excel file with the appropriate required column headings and some sample data. We will consider an example of employee number, name, and grade.
- Next, we will save the Excel file in the XML Spreadsheet 2003 format.
- Next, we will create a new simple transformation using transaction
XSLT_TOOL. -
Then, from the Source Code tab, click on the Import (
) button. On the File dialog box that appears, select the ExcelXML file created earlier. This will create a transformation program within the editor.
- Next, we need to make some minor changes within the transformation. First, we will remove
ss:ExpandedRowCount="6"from theTableelement. The data rows pertaining to the sample data uploaded must be removed as well. These are contained withinRowandCellelements. (Our uploaded Excel XML file generated a transformation for our sample data. We need to delete the hard-coded lines pertaining to the sample data within theRowandCellelements. We then make the necessary changes so that the transformation may be used for dynamic data passed in an internal table from our program.)
- We will now add a root element table at the beginning of the transformation, before the
tt:templatecommand.
tt:loopis then added in place of the removed employee data. The loop addresses each line of the internal table"table". We print the contents of the fieldsPERNR,NAME, andGRADEas texts within the elementCellfor everyRow.
- We will save and activate the transformation.
- We will then make a simple program to populate an internal table based on the root element table. We define an internal table,
itab, and work area,wa, based on the table typezst6_tt_perand line typezst6_st_per, respectively. We then populate the table with suitable values.
- Then, the
CALL TRANSFORMATIONstatement is called and the internal tableitabis passed. The resultingxml_stringstring contains the Excel data in XML format.
We uploaded the transformation using the XSLT_TOOL transaction's
Import button. This transformation is based on the file that we uploaded. If we call the same transformation, the result will be the same file that was uploaded. Since we want the Excel output to be based on the content of the internal table of the ABAP program, we made the necessary changes to the transformation in order to incorporate a loop on a root element table. During serialization, the internal table itab was passed in place of the root element table defined within the transformation. The loop was run and the content of the table was included in the XML string. The xml_string may then be written as saved on the desktop or emailed to another user. When opened in Excel, it will show the user the data in Excel format along with the column heading colors and the fonts of the original sample file.