AnsweredAssumed Answered

Help with XSLT Transformation and Formatting

Question asked by ManjitSandhu on Mar 24, 2015
Latest reply on Mar 31, 2015 by ManjitSandhu

Title

Help with XSLT Transformation and Formatting

Post

Hi

So on the continuing road of supporting an application written by a third party, I've got an issue with the reports that are generated by the application that I've tried to resolve myself but hit a bit of a brick wall. The users wanted to be able to export a set of fields based on a specific criteria to Excel. Initially this was set up as simple Excel exports, and we created a template for them to drag/drop data into but then they wanted to export the data 'ready to go' so the developers switched to using XSLT files to supply them with the correct headings instead of the FM field names. It all works, except some of the columns need formatting, ie there is an ID field which is either a long number of a mixed case string,but must always be displayed as a string. Looking online and lots of google searching, I can't find a way to format specific columns in the XSLT file.

Here's a sample XSLT file:

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

<xsl:stylesheet 
version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform
xmlns:fmp="http://www.filemaker.com/fmpxmlresult
exclude-result-prefixes="fmp">

<xsl:output method="text" version="1.0" encoding="UTF-8" indent="yes"/>
<!--
Template: match="fmp:FMPXMLRESULT"
The main driver for building the table. Calls the header template then iterates through the RESULTSET nodeset to build the table rows.
-->

    <xsl:template match="fmp:FMPXMLRESULT">
        <xsl:call-template name="myobOutput"/>
    </xsl:template>

<xsl:template name="myobOutput" match="fmp:FMPXMLRESULT">

<!--
write out the first line as labels to make it easier for the user to match up when importing
-->

    <xsl:text>Client Name&#9;</xsl:text>
    <xsl:text>Status&#9;</xsl:text>
    <xsl:text>Lead Consultant&#9;</xsl:text>
    <xsl:text>Lead Consultant Office&#9;</xsl:text>
    <xsl:text>Activity Type&#9;</xsl:text>
    <xsl:text>Issue Method&#9;</xsl:text>
    <xsl:text>Description&#9;</xsl:text>
    <xsl:text>Scheme&#9;</xsl:text>
    <xsl:text>Date Added&#9;</xsl:text>
    <xsl:text>Status&#9;</xsl:text>
    <xsl:text>Date Completed&#9;</xsl:text>
    <xsl:text>Fixed Fee&#9;</xsl:text>
    <xsl:text>Oracle Code&#9;</xsl:text>
    <xsl:text>Activity Note&#9;</xsl:text>
    <xsl:text>Open/Closed&#9;</xsl:text>
    <xsl:text>&#13;</xsl:text>

<xsl:for-each select="fmp:RESULTSET/fmp:ROW">
    <xsl:value-of select="fmp:COL[1]"/><xsl:text>&#9;</xsl:text>
    <xsl:value-of select="fmp:COL[2]"/><xsl:text>&#9;</xsl:text>
    <xsl:value-of select="fmp:COL[3]"/><xsl:text>&#9;</xsl:text>
    <xsl:value-of select="fmp:COL[4]"/><xsl:text>&#9;</xsl:text>
    <xsl:value-of select="fmp:COL[5]"/><xsl:text>&#9;</xsl:text>
    <xsl:value-of select="fmp:COL[6]"/><xsl:text>&#9;</xsl:text>
    <xsl:value-of select="fmp:COL[7]"/><xsl:text>&#9;</xsl:text>
    <xsl:value-of select="fmp:COL[8]"/><xsl:text>&#9;</xsl:text>
    <xsl:value-of select="fmp:COL[9]"/><xsl:text>&#9;</xsl:text>
    <xsl:value-of select="fmp:COL[10]"/><xsl:text>&#9;</xsl:text>
    <xsl:value-of select="fmp:COL[11]"/><xsl:text>&#9;</xsl:text>
    <xsl:value-of select="fmp:COL[12]"/><xsl:text>&#9;</xsl:text>
    <xsl:value-of select="fmp:COL[13]"/><xsl:text>&#9;</xsl:text>
    <xsl:value-of select="fmp:COL[14]"/><xsl:text>&#9;</xsl:text>
    <xsl:value-of select="fmp:COL[15]"/><xsl:text>&#9;</xsl:text>
    <xsl:text>&#13;</xsl:text>
</xsl:for-each>

</xsl:template>
</xsl:stylesheet>

The users access the system through WebDirect, so the export happens on the server. Wheat we are also seeing is that when the file is opened by a user, they get a message "The file you are trying to open, 'report.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before openeing the file. Do you want to open the file now?". If you click Yes, it opens fine, but is there a way to stop this message when opening the output files?

Thanks

Martin

Outcomes