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

  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.