Skip to Content

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.

/wp-content/uploads/2012/09/image1_xslt_528477.jpg

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.

/wp-content/uploads/2012/09/image2_xslt_528478.jpg

Output of the Scenario

/wp-content/uploads/2012/09/image3_xslt_528485.jpg

/wp-content/uploads/2012/09/image4_xslt_528486.jpg

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

To report this post you need to login first.

11 Comments

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

  1. Praveen Vagicharla

    Hi,

    Thanks for the blog.

    My scenario is RFC to Mail. I followed your code and addedd some fields and created the rest as same.

    When I triggered the data I am getting below error in RWB.

    Please help.

    Message processing failed. Cause:
    com.sap.aii.af.sdk.xi.srt.BubbleException: Failed to call the endpoint  [null
    “null”]; nested exception caused by:
    com.sap.aii.af.sdk.xi.util.XMLScanException: expecting start tag:
    {http://sap.com/xi/XI/Mail/30}Mail, but found
    {urn:schemas-microsoft-com:office:spreadsheet}Workbook at state 1

    Thanks & regards,

    Praveen Kumar

    (0) 

Leave a Reply