Convert incoming XML to Excel or Excel XML – Part 1 - XSLT Way

[aashish_sinha]

Recently, in one of my project a requirement came to generate the output/target file in excel or .xls format. It was challenge how to generate these files because we cannot directly generate a file in .xls file by just giving a name in receiver adapter like “Output.xls”. So it should be something like custom code needs to be inserted in receiver adapter to convert incoming source XML to Excel file.

  I think about 2 approaches:

1. Insert a XSLT code for the conversion of source XML to Excel XML – which later on can be opened as excel file.
2. Create a custom adapter module to convert incoming XML Payload to Excel sheet.

In this part, I will only talk about XSLT way to convert incoming XML to Excel XML later opened as excel sheet.

Below is the source XML need to be converted to MS Excel XML.

Source XML

<?xml version="1.0"?>

<MT_TEST_IN>

                <RECORD>

                                <NAME>AASHISH</NAME>

                                <AGE>25</AGE>

                </RECORD>

                <RECORD>

                                <NAME>Robert</NAME>

                                <AGE>24</AGE>

                </RECORD>

                <RECORD>

                                <NAME>Sare</NAME>

                                <AGE>21</AGE>

                </RECORD>

</MT_TEST_IN>

XSLT Code – It’s a sample code and can be changed as per requirement.

<?xml version="1.0" encoding="utf-8"?>
<?mso-application progid="Excel.Sheet"?>
<xsl:stylesheet version="1.0"
xmlns:html="http://www.w3.org/TR/REC-html40"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

    <xsl:template match="/">

        <Workbook>
            <Styles>
                <Style ss:ID="Default" ss:Name="Normal">
                    <Alignment ss:Vertical="Bottom" />
                    <Borders />
                    <Font />
                    <Interior />
                    <NumberFormat />
                    <Protection />
                </Style>
                <Style ss:ID="s21">
                    <Font ss:Size="22" ss:Bold="1" />
                </Style>
                <Style ss:ID="s22">
                    <Font ss:Size="14" ss:Bold="1" />
                </Style>
                <Style ss:ID="s23">
                    <Font ss:Size="12" ss:Bold="1" />
                </Style>
                <Style ss:ID="s24">
                    <Font ss:Size="10" ss:Bold="1" />
                </Style>
            </Styles>

            <Worksheet ss:Name="Page1">
                <Table>
                    <xsl:call-template name="XMLToXSL" />
                </Table>
            </Worksheet>
        </Workbook>

    </xsl:template>

    <xsl:template name="XMLToXSL">

        <Row>
                        <Cell>
                            <Data ss:Type="String">Name</Data>
                        </Cell>
                        <Cell>
                            <Data ss:Type="String">Age</Data>
                        </Cell>
                    </Row>
        <xsl:for-each
            select="//RECORD">

            <Row>
                <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="NAME" />
                    </Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="AGE" />
                    </Data>
                </Cell>
            </Row>
        </xsl:for-each>
    </xsl:template>
<xsl:template match="MT_TEST_IN">
</xsl:template>
</xsl:stylesheet>

Above XSLT code will convert the incoming source file into Excel XML which later can be opened as an Excel file.

Now to use this XSLT in our scenario we need to zip with XSLT file and upload it to SAP PI. We can upload this zip file to PI in imported archive to use this mapping in Operations Mapping for our conversion process.

Import XSL mapping to SAP PI using Imported Archive

Import your XSL zip file to imported archive.

After importing xslt file to SAP PI, create Operations Mapping and provide XSL mapping.

Communication Channel

Configure your Communication Channel as per below screen shot.

Output of the Scenario

Second part will contain Adapter module for conversion of incoming XML file to Excel using Apache POI Java api’s.