Multiple internal tables are not getting generated properly in .xsl file

[Former Member]

Hi Experts,

I am working on RFC to FTP scenario.EXCEL File is getting generated properly using XSLT Code when I sent one record from RFC.

But,if I sent multiple records from RFC,all records are getting generated in one line in excel file on FTP.

The expected result is each record sent from RFC should generate on separate row.

Input XML file-

<?xml version="1.0" encoding="UTF-8"?>

<rfc:ZCF_numbers xmlns:rfc="urn:sap-com:document:sap:rfc:functions">

<IT_numbers>

<item>

<EXPORT1>

<number1>1</number1>

<number2>2</number2>

</EXPORT1>

<EXPORT2>

<DATE>12-12-2014</DATE>

</EXPORT2>

<EXPORT3>

<EMPLOYEENAME>SARAH</EMPLOYEENAME>

</EXPORT3>

</item>

<item>

<EXPORT1>

<number1>3</number1>

<number2>4</number2>

</EXPORT1>

<EXPORT2>

<DATE>13-12-2014</DATE>

</EXPORT2>

<EXPORT3>

<EMPLOYEENAME>John</EMPLOYEENAME>

</EXPORT3>

</item>

</IT_numbers>

</rfc:ZCF_numbers>

XSLT Code used for conversion-

<?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="EXPORTDATA">

<Table>

<xsl:call-template name="XMLToXSL" />

</Table>

</Worksheet>

</Workbook>

</xsl:template>

<xsl:template name="XMLToXSL">

<Row>

<Cell>

<Data ss:Type="String">number1</Data>

</Cell>

<Cell>

<Data ss:Type="String">number2</Data>

</Cell>

<Cell>

<Data ss:Type="String">DATE</Data>

</Cell>

<Cell>

<Data ss:Type="String">EMPLOYEENAME</Data>

</Cell>

</Row>

<Row>

<xsl:for-each select="//IT_number/item/EXPORT1">

<Cell>

<Data ss:Type="String"><xsl:value-of select="number1" /></Data>

</Cell>

<Cell>

<Data ss:Type="String"><xsl:value-of select="number2" /></Data>

</Cell>

</xsl:for-each>

<xsl:for-each select="//IT_number/item/EXPORT2">

<Cell>

<Data ss:Type="String"><xsl:value-of select="DATE" /></Data>

</Cell>

</xsl:for-each>

<xsl:for-each select="//IT_number/item/EXPORT3">

<Cell>

<Data ss:Type="String"><xsl:value-of select="EMPLOYEENAME" /></Data>

</Cell>

</xsl:for-each>

</Row>

</xsl:template>

<xsl:template match="ZCF_numbers">

</xsl:template>

</xsl:stylesheet>

Expected Result-

number1	number2	DATE	EMPLOYEENAME
1	2	12-12-2014	SARAH
3	4	13-12-2014	John

Result which is getting generated by above code-

number1	number2	DATE	EMPLOYEENAME
1	2	12-12-2014	SARAH	3	4	13-12-2014	John

Please suggest.

Regards,

Aditi

[Snavi]

Hi Aditi,

you need to loop over the items in your xml to generate rows in the excel file.

try with this modified xsl

<?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="EXPORTDATA">

<Table>

<xsl:call-template name="XMLToXSL" />

</Table>

</Worksheet>

</Workbook>

</xsl:template>

<xsl:template name="XMLToXSL">

<Row>

<Cell>

<Data ss:Type="String">number1</Data>

</Cell>

<Cell>

<Data ss:Type="String">number2</Data>

</Cell>

<Cell>

<Data ss:Type="String">DATE</Data>

</Cell>

<Cell>

<Data ss:Type="String">EMPLOYEENAME</Data>

</Cell>

</Row>

<xsl:for-each select="//item">

<Row>

<xsl:for-each select="EXPORT1">

<Cell>

<Data ss:Type="String"><xsl:value-of select="number1" /></Data>

</Cell>

<Cell>

<Data ss:Type="String"><xsl:value-of select="number2" /></Data>

</Cell>

</xsl:for-each>

<xsl:for-each select="EXPORT2">

<Cell>

<Data ss:Type="String"><xsl:value-of select="DATE" /></Data>

</Cell>

</xsl:for-each>

<xsl:for-each select="EXPORT3">

<Cell>

<Data ss:Type="String"><xsl:value-of select="EMPLOYEENAME" /></Data>

</Cell>

</xsl:for-each>

</Row>

</xsl:for-each>

</xsl:template>

<xsl:template match="ZCF_numbers">

</xsl:template>

</xsl:stylesheet>