How to generate an EXCEL file using module parameter??

[Former Member]

Hi,

I ha ve a file to file scenario, where the output file format will be EXCEL. I know this is possible using Module Parameters. But I can't find a helpful blog.

So please help me. Its urgent.

Thanks in advance,

Dwaipayan De

[Former Member · Accepted solution]

Hi Dwaipayan,

Please check this link

You can use XSLT mapping or adapter module to generate excel file in receiver side.

Regards,

Praveen

[Former Member · Accepted solution]

Hi Praveen,

Thanks for your reply. Can you do me a favor? Please post the communication channel configuration part from the following link, the images are not opening from my system. It will be better if you write the communication channel configuration part in your reply.

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

Regards,

Dwaipayan

[Former Member · Accepted solution]

Hi Dwaipayan,

I think assigning FileName scheme as "Filename".xls is enough in channel. Please check by assigning filename with extension ".xls".

Regards,

Praveen

[Former Member]

Hi,

Now excel file is generating. But the file is containing only the header part. I have used the following XSLT code :-

<?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">SupplyOfficeCode</Data>
                        </Cell>
                        <Cell>
                            <Data ss:Type="String">WorkOrderNo</Data>
                        </Cell>
   <Cell>
                            <Data ss:Type="String">ApplicationNo</Data>
                        </Cell>
   <Cell>
                            <Data ss:Type="String">ApplicationType</Data>
                        </Cell>
                        <Cell>
                            <Data ss:Type="String">ApplicantName</Data>
                        </Cell>
                        <Cell>
                            <Data ss:Type="String">DateOfIssueOfWO</Data>
                        </Cell>
                        <Cell>
                            <Data ss:Type="String">ApplCreationDate</Data>
                        </Cell>
                        <Cell>
                            <Data ss:Type="String">WorkOrderStatus</Data>
                        </Cell>
                        <Cell>
                            <Data ss:Type="String">DateOfCanOfWO</Data>
                        </Cell>
                       
        </Row>
        <xsl:for-each
            select="//Record">

            <Row>
                <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="SupplyOfficeCode" />
                    </Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="WorkOrderNo" />
                    </Data>
                </Cell>
  <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="ApplicationNo" />
                    </Data>
                </Cell>
  <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="ApplicationType" />
                    </Data>
                </Cell>
  <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="ApplicantName" />
                    </Data>
                </Cell>
  <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="DateOfIssueOfWO" />
                    </Data>
                </Cell>
  <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="ApplCreationDate" />
                    </Data>
                </Cell>
  <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="WorkOrderStatus" />
                    </Data>
                </Cell>
  <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="DateOfCanOfWO" />
                    </Data>
                </Cell>
            </Row>
        </xsl:for-each>
    </xsl:template>
<xsl:template match="GetWorkOrderDetailsinEXCEL">
</xsl:template>
</xsl:stylesheet>

In the communication channel, full message is passing(including header and body part). But when the excel file is generating, it is containing only the header part. No message body is present there.

Please help me to solve this proble.

Thanks and Regards,

Dwaipayan De

[Former Member]

Hi,

Can you share your xml payload in channel?

Regards,

Praveen

[Former Member]

Payload of Sender Communication Channel :-

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

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"><Styles><Style xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:ID="Default" ss:Name="Normal"><Alignment ss:Vertical="Bottom"/><Borders/><Font/><Interior/><NumberFormat/><Protection/></Style><Style xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:ID="s21"><Font ss:Size="22" ss:Bold="1"/></Style><Style xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:ID="s22"><Font ss:Size="14" ss:Bold="1"/></Style><Style xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:ID="s23"><Font ss:Size="12" ss:Bold="1"/></Style><Style xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:ID="s24"><Font ss:Size="10" ss:Bold="1"/></Style></Styles><Worksheet xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:Name="Page1"><Table><Row><Cell><Data ss:Type="String">SupplyOfficeCode</Data></Cell><Cell><Data ss:Type="String">WorkOrderNo</Data></Cell><Cell><Data ss:Type="String">ApplicationNo</Data></Cell><Cell><Data ss:Type="String">ApplicationType</Data></Cell><Cell><Data ss:Type="String">ApplicantName</Data></Cell><Cell><Data ss:Type="String">DateOfIssueOfWO</Data></Cell><Cell><Data ss:Type="String">ApplCreationDate</Data></Cell><Cell><Data ss:Type="String">WorkOrderStatus</Data></Cell><Cell><Data ss:Type="String">DateOfCanOfWO</Data></Cell></Row><Row><Cell><Data ss:Type="String">115</Data></Cell><Cell><Data ss:Type="String">51561</Data></Cell><Cell><Data ss:Type="String">4512</Data></Cell><Cell><Data ss:Type="String">NEW</Data></Cell><Cell><Data ss:Type="String">AA</Data></Cell><Cell><Data ss:Type="String">05082014</Data></Cell><Cell><Data ss:Type="String">05082014</Data></Cell><Cell><Data ss:Type="String">A</Data></Cell><Cell><Data ss:Type="String">05052014</Data></Cell></Row><Row><Cell><Data ss:Type="String">541</Data></Cell><Cell><Data ss:Type="String">6541</Data></Cell><Cell><Data ss:Type="String">5151</Data></Cell><Cell><Data ss:Type="String">NEW</Data></Cell><Cell><Data ss:Type="String">BB</Data></Cell><Cell><Data ss:Type="String">05052014</Data></Cell><Cell><Data ss:Type="String">05052014</Data></Cell><Cell><Data ss:Type="String">B</Data></Cell><Cell><Data ss:Type="String">05052014</Data></Cell></Row><Row><Cell><Data ss:Type="String">5444</Data></Cell><Cell><Data ss:Type="String">2115451</Data></Cell><Cell><Data ss:Type="String">561561</Data></Cell><Cell><Data ss:Type="String">NEW</Data></Cell><Cell><Data ss:Type="String">CC</Data></Cell><Cell><Data ss:Type="String">05062014</Data></Cell><Cell><Data ss:Type="String">05062014</Data></Cell><Cell><Data ss:Type="String">C</Data></Cell><Cell><Data ss:Type="String">05062014</Data></Cell></Row></Table></Worksheet></Workbook>

Payload of Receiver Communication Channel :-

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

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"><Styles><Style xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:ID="Default" ss:Name="Normal"><Alignment ss:Vertical="Bottom"/><Borders/><Font/><Interior/><NumberFormat/><Protection/></Style><Style xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:ID="s21"><Font ss:Size="22" ss:Bold="1"/></Style><Style xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:ID="s22"><Font ss:Size="14" ss:Bold="1"/></Style><Style xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:ID="s23"><Font ss:Size="12" ss:Bold="1"/></Style><Style xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:ID="s24"><Font ss:Size="10" ss:Bold="1"/></Style></Styles><Worksheet xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:Name="Page1"><Table><Row><Cell><Data ss:Type="String">SupplyOfficeCode</Data></Cell><Cell><Data ss:Type="String">WorkOrderNo</Data></Cell><Cell><Data ss:Type="String">ApplicationNo</Data></Cell><Cell><Data ss:Type="String">ApplicationType</Data></Cell><Cell><Data ss:Type="String">ApplicantName</Data></Cell><Cell><Data ss:Type="String">DateOfIssueOfWO</Data></Cell><Cell><Data ss:Type="String">ApplCreationDate</Data></Cell><Cell><Data ss:Type="String">WorkOrderStatus</Data></Cell><Cell><Data ss:Type="String">DateOfCanOfWO</Data></Cell></Row><Row><Cell><Data ss:Type="String">115</Data></Cell><Cell><Data ss:Type="String">51561</Data></Cell><Cell><Data ss:Type="String">4512</Data></Cell><Cell><Data ss:Type="String">NEW</Data></Cell><Cell><Data ss:Type="String">AA</Data></Cell><Cell><Data ss:Type="String">05082014</Data></Cell><Cell><Data ss:Type="String">05082014</Data></Cell><Cell><Data ss:Type="String">A</Data></Cell><Cell><Data ss:Type="String">05052014</Data></Cell></Row><Row><Cell><Data ss:Type="String">541</Data></Cell><Cell><Data ss:Type="String">6541</Data></Cell><Cell><Data ss:Type="String">5151</Data></Cell><Cell><Data ss:Type="String">NEW</Data></Cell><Cell><Data ss:Type="String">BB</Data></Cell><Cell><Data ss:Type="String">05052014</Data></Cell><Cell><Data ss:Type="String">05052014</Data></Cell><Cell><Data ss:Type="String">B</Data></Cell><Cell><Data ss:Type="String">05052014</Data></Cell></Row><Row><Cell><Data ss:Type="String">5444</Data></Cell><Cell><Data ss:Type="String">2115451</Data></Cell><Cell><Data ss:Type="String">561561</Data></Cell><Cell><Data ss:Type="String">NEW</Data></Cell><Cell><Data ss:Type="String">CC</Data></Cell><Cell><Data ss:Type="String">05062014</Data></Cell><Cell><Data ss:Type="String">05062014</Data></Cell><Cell><Data ss:Type="String">C</Data></Cell><Cell><Data ss:Type="String">05062014</Data></Cell></Row></Table></Worksheet></Workbook>

[Former Member]

Hi Dwaipayan,

Sorry for late reply. Not sure why Header only coming in excel but the xml payload in the receiver file adapter is generated correctly. For me its working fine. I've saved your receiver channel payload xml manually as .xls file and opened in Excel, its showing all records.

Can you try it manually to save your receiver payload as .xls and check it in Excel?

Regards,

Praveen

[Former Member]

Hi Praveen,

Now it is working fine. I don't know why it was not working previously. Anyway, thanks for your reply.

Regards,

Dwaipayan

[Former Member]

Hi Dwaipayan,

Please follow below Blogs Might be its help you

SAPTechnical.COM - XI - Step-by-step guide to develop Adapter Module to read Excel file

http://scn.sap.com/community/pi-and-soa-middleware/blog/2010/12/06/pixi-reading-ms-excels-xlsx-and-x...

Regards,

Rajendar K

[iaki_vila]

Hi Dwaipayan

Have you checked this blog ?

What is your real problem?, do you need to rename the extension to xslx?

Regards,

[Former Member]

Hi Iñaki ,

Thanks for your reply.

No, I don't want to rename the extention. I have a hash(#) separated file and I want to convert it into a normal excel file. I want to convert it using the module parameter in the ID part of SAP PI. Here I don't want to use JAVA.

So please post any link that contains the procedure to convert into excel file using Module Parameter.

Regards,

Dwaipayan