generating excel files using XSLT mapping

[Former Member]

Hi All,

I am working on a proxy to file scenario and we have a requirement of generating .xls file in a spreadsheet format at FTP server.I have done xslt mappping for creating the excel file in spreadsheet.But getting following error in channel monitoring.

Message processing failed. Cause: com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error occurred while connecting to the FTP server "phmysa-s3006.ap.novartis.net:21": java.lang.Exception: Exception in XML Parser (format problem?):'java.lang.Exception: Message processing failed in XML parser: 'Conversion configuration error: Unknown structure 'Workbook' found in document', probably configuration error in file adapter (XML parser error)'

XSLT Code Used:

<?xml version='1.0' ?>

<?mso-application progid="Excel.Sheet"?>

<xsl:stylesheet version="1.0" 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"

xmlns:html="http://www.w3.org/TR/REC-html40">

<xsl:template match="DT_StockReports/*">

<Row>

<Cell>

<Data ss:Type="String">

<xsl:apply-templates/>

</Data>

</Cell>

</Row>

</xsl:template>

<xsl:template match="/">

<Workbook>

<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

</DocumentProperties>

<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">

<WindowHeight>10365</WindowHeight>

<WindowWidth>13260</WindowWidth>

<WindowTopX>120</WindowTopX>

<WindowTopY>60</WindowTopY>

<ProtectStructure>False</ProtectStructure>

<ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

<Styles>

<Style ss:ID="Default" ss:Name="Normal">

<Alignment ss:Vertical="Bottom"/>

<Borders/>

<Font x:CharSet="512"/>

<Interior/>

<NumberFormat/>

<Protection/>

</Style>

</Styles>

<Worksheet ss:Name="">

<Table ss:ExpandedColumnCount="40" ss:ExpandedRowCount="15000" x:FullColumns="40"

x:FullRows="15000">

<xsl:apply-templates/>

</Table>

<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">

<PageSetup>

<PageMargins x:Bottom="0.984251969" x:Left="0.78740157499999996"

x:Right="0.78740157499999996" x:Top="0.984251969"/>

</PageSetup>

<Selected/>

<Panes>

<Pane>

<Number>40</Number>

<ActiveRow>40</ActiveRow>

<ActiveCol>15000</ActiveCol>

</Pane>

</Panes>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

</Workbook>

</xsl:template>

</xsl:stylesheet>

Thanks

Ayura

[Former Member · Accepted solution]

Ayura,

Can you provide the input payload you have supplied?

Regards

Ramesh

[Former Member · Accepted solution]

The input structure looks like this

DT_StockReports

-Header

-- Field1

-- Field2

-- Field3

- Records

-- Field1

-- Field2

-- Field3

Edited by: Ayura Kanungo on Dec 28, 2011 6:46 AM

[Former Member · Accepted solution]

Can you provide the xml payload? Moreover i tested the code with external XSLT test tool, it seems that the excel sheet is not getting created properly.

If you provide test data in xml payload we can corner the problem.

If you want you can also test the XSLT with external tools and save the output data in .xls file and try to open in Excel.

Regards

Ramesh

[Former Member · Accepted solution]

Input xml:

<?xml version="1.0" encoding="utf-8"?><n0:MT_StockReports xmlns:n0="urn:novartis.com:pi:ph:sp:LM_DD_501_LSP_L0X:StockReports" xmlns:prx="urn:sap.com:proxy:Q75:/1SAI/TASEF7C5F005BC07ECC0997:700:2008/06/25"><Header><Material></Material><MaterialDescription></MaterialDescription><MType></MType><Bin></Bin><Unit></Unit><MS></MS><LS></LS><Type></Type><PGr></PGr><ABC></ABC><Crcy></Crcy><Prc></Prc><Vendor></Vendor><VendorName></VendorName><VendMat></VendMat><ManufacturingPartN></ManufacturingPartN><Batch></Batch><BatchExpiryDate></BatchExpiryDate><Unrestr></Unrestr><SafetyStk></SafetyStk><MinSafStck></MinSafStck><MinLotSize></MinLotSize><MaxLotSize></MaxLotSize><MaxLevel></MaxLevel><ReorderPt></ReorderPt><TotalStock></TotalStock><TotalVal></TotalVal></Header><Records><Material>20001</Material><MaterialDescription>SIEVED NATEGLINIDE / DS.01 / DR</MaterialDescription><MType>ZPI</MType><Bin></Bin><Unit>KG</Unit><MS>40</MS><LS>HB</LS><Type>M0</Type><PGr>S01</PGr><ABC></ABC><Crcy>SGD</Crcy><Prc>S</Prc><Vendor> </Vendor><VendorName></VendorName><VendMat></VendMat><ManufacturingPartN></ManufacturingPartN><Batch>SD0026</Batch><BatchExpiryDate>01.12.2011</BatchExpiryDate><Unrestr>49.803 </Unrestr><SafetyStk>0.000 </SafetyStk><MinSafStck>0.000 </MinSafStck><MinLotSize>110.000 </MinLotSize><MaxLotSize>550.000 </MaxLotSize><MaxLevel>1.000 </MaxLevel><ReorderPt>0.000 </ReorderPt><TotalStock>49.803 </TotalStock><TotalVal>1723.18 </TotalVal></Records></n0:MT_StockReports>

Edited by: Ayura Kanungo on Dec 28, 2011 7:11 AM

[Former Member · Accepted solution]

Ayura,

Use the following XSLT code, it will work!

<xsl:stylesheet version="1.0"
    xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
 xmlns:msxsl="urn:schemas-microsoft-com:xslt"
 xmlns:user="urn:my-scripts"
 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 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"
    xmlns:html="http://www.w3.org/TR/REC-html40">
    <xsl:apply-templates/>
  </Workbook>
</xsl:template>


<xsl:template match="/*">
  <Worksheet>
  <xsl:attribute name="ss:Name">
  <xsl:value-of select="local-name(/*/*)"/>
  </xsl:attribute>
    <Table x:FullColumns="1" x:FullRows="1">
      <Row>
        <xsl:for-each select="*[position() = 1]/*">
          <Cell><Data ss:Type="String">
          <xsl:value-of select="local-name()"/>
          </Data></Cell>
        </xsl:for-each>
      </Row>
      <xsl:apply-templates/>
    </Table>
  </Worksheet>
</xsl:template>


<xsl:template match="/*/*">
  <Row>
    <xsl:apply-templates/>
  </Row>
</xsl:template>


<xsl:template match="/*/*/*">
  <Cell><Data ss:Type="String">
    <xsl:value-of select="."/>
  </Data></Cell>
</xsl:template>


</xsl:stylesheet>

Regards

Ramesh

[Former Member · Accepted solution]

Hi Ramesh,

Thanks a lot for your help.It is working now.

Thanks

Ayura

[Former Member · Accepted solution]

Hi Ramesh,

Please help me with below thread.

https://scn.sap.com/message/16075007#16075007

[Former Member · Accepted solution]

Hi Ayura,

Please help me with below thread.

https://scn.sap.com/message/16075007#16075007

[Former Member · Accepted solution]

Dear Ramesh/Ayura,

I'm unable to import the below source payload in Imported Archive:

~mt_store

   ~RECORD

      ~WEEK

     ~STORE_CODE

     ~.....

      ~DISC_FLAG

After this should we map the source and target in Altova Map force?

______________________________

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

<xsl:stylesheet version="1.0"

xmlns:html="http://www.w3.org/2001/XMLSchema" xmlns:xsl="http://xlxstocsv/xi/StoreConversion"

    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 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"

    xmlns:html="http://www.w3.org/TR/REC-html40">

    <xsl:apply-templates/>

  </Workbook>

</xsl:template>

    <xsl:template match="/*">

        <Workbook>

            <Worksheet ss:Name="Page1">

                <Table>

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

                </Table>

            </Worksheet>

        </Workbook>

    </xsl:template>

    <xsl:template name="StoreXXL">

        <Row>

                        <Cell>

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

                        </Cell>

                        <Cell>

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

                        </Cell>

<Cell>

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

                        </Cell>

<Cell>

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

                        </Cell>

<Cell>

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

                        </Cell>

<Cell>

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

                        </Cell>

<Cell>

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

                        </Cell>

<Cell>

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

                        </Cell>

<Cell>

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

                        </Cell>

                    </Row>

        <xsl:for-each

            select="//RECORD">

            <Row>

                <Cell>

                    <Data ss:Type="String">

                        <xsl:value-of select="WEEK" />

                    </Data>

                </Cell>

                <Cell>

                    <Data ss:Type="String">

                        <xsl:value-of select="STORE_CODE" />

                    </Data>

<Cell>

                    <Data ss:Type="String">

                        <xsl:value-of select="BARCODE" />

                    </Data>

<Cell>

                    <Data ss:Type="String">

                        <xsl:value-of select="SIZE" />

                    </Data>

<Cell>

                    <Data ss:Type="String">

                        <xsl:value-of select="MRP" />

                    </Data>

<Cell>

                    <Data ss:Type="String">

                        <xsl:value-of select="BASIC_AMT" />

                    </Data>

<Cell>

                    <Data ss:Type="String">

                        <xsl:value-of select="SALES_QTY" />

                    </Data>

<Cell>

                    <Data ss:Type="String">

                        <xsl:value-of select="SOH" />

                    </Data>

<Cell>

                    <Data ss:Type="String">

                        <xsl:value-of select="DISC_FLAG" />

                    </Data>

                </Cell>

            </Row>

        </xsl:for-each>

    </xsl:template>

<xsl:template match="mt_store">

</xsl:template>

</xsl:stylesheet>

[Former Member]

Hi Ramesh

The excel fiel is getting created but in this case header is taking values of column names specified in Data type.and after that there is another row which is containing the value passed for those fields.

Kinldy help me with this.

Thanks

Ayura

Edited by: Ayura Kanungo on Dec 28, 2011 9:23 AM

Edited by: Ayura Kanungo on Dec 28, 2011 9:53 AM

[Former Member]

Ayura,

In that case dont send the Header at all. Only send rows where the row names will be your column name and the value will be part of record.

Are you able to skip header from proxy?

Regards

Ramesh

Edited by: Ramesh P on Dec 28, 2011 3:14 PM

[Former Member]

Hi Ramesh

The excel fiel is getting created but in this case header i.e first row is getting duplicated twice.

Kinldy help me with this.

Thanks

Ayura

Edited by: Ayura Kanungo on Dec 28, 2011 9:15 AM