11 Replies Latest reply on Mar 31, 2015 1:00 AM by ManjitSandhu

    Help with XSLT Transformation and Formatting

    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

        • 1. Re: Help with XSLT Transformation and Formatting
          Korry

          AFAIK FileMaker only supports XPath 1.0, so here are the string functions you can use:

          http://www.w3.org/TR/xpath/#section-String-Functions

           

          Regarding format: in this case, your transform made a tab-delimited text file, not an Excel/XLS file.

          • 2. Re: Help with XSLT Transformation and Formatting
            ManjitSandhu

            Many thanks for your response. I'll look at the string functions in the link you provided [EDIT]Useful list but there are no examples on how to apply the functions to XSL transformations?[/EDIT]

            Is there a way to switch to an Excel file instead of a tab-delimited text file, using the transformation file as a starting point?

            Thanks

            Martin

            • 3. Re: Help with XSLT Transformation and Formatting
              ManjitSandhu

              Ignoring the useless comment above, I've had a bit of a play and made some changes based on some examples I found online, but whilst I can get the headings to appear in bold, no data is coming through. Is there any way to validate the XSLT file?

              <?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"
                xmlns:fmp="http://www.filemaker.com/fmpxmlresult
                exclude-result-prefixes="fmp">
                 
              <xsl:template match="/">

              <Workbook>
                <Styles>
                  <Style ss:ID="Heading">
                    <Font ss:Size="10" ss:Bold="1"/>
                  </Style>
                </Styles>

                <Worksheet ss:Name="RFP">

                  <Table>
                  
                    <Row>
                      <Cell ss:StyleID="Heading">
                        <Data ss:Type="String">Client Name</Data>
                      </Cell>
                      <Cell ss:StyleID="Heading">
                        <Data ss:Type="String">Stock Exchange</Data>
                      </Cell>
                      <Cell ss:StyleID="Heading">
                        <Data ss:Type="String">Industry Sector</Data>
                      </Cell>
                        <Cell ss:StyleID="Heading">
                      <Data ss:Type="String">Company/Trustee</Data>
                      </Cell>
                      <Cell ss:StyleID="Heading">
                        <Data ss:Type="String">Lead Consultant</Data>
                      </Cell>
                      <Cell ss:StyleID="Heading">
                        <Data ss:Type="String">Scheme Name</Data>
                      </Cell>
                      <Cell ss:StyleID="Heading">
                        <Data ss:Type="String">Number of Active Members</Data>
                      </Cell>
                      <Cell ss:StyleID="Heading">
                        <Data ss:Type="String">Deferred Members</Data>
                      </Cell>
                      <Cell ss:StyleID="Heading">
                        <Data ss:Type="String">Total Funds Managed</Data>
                      </Cell>
                      <Cell ss:StyleID="Heading">
                        <Data ss:Type="String">Open/Closed</Data>
                      </Cell>
                    </Row>

                    <Row>
                      <xsl:for-each select="fmp:RESULTSET/fmp:ROW">
                        <Cell>
                          <Data ss:Type="String">
                            <xsl:value-of select="fmp:COL[1]"/>
                          </Data>
                        </Cell>
                        <Cell>
                          <Data ss:Type="String">
                            <xsl:value-of select="fmp:COL[2]"/>
                          </Data>
                        </Cell>                
                        <Cell>
                          <Data ss:Type="String">
                            <xsl:value-of select="fmp:COL[3]"/>
                          </Data>
                        </Cell>
                        <Cell>
                          <Data ss:Type="String">
                            <xsl:value-of select="fmp:COL[4]"/>
                          </Data>
                        </Cell>
                        <Cell>
                          <Data ss:Type="String">
                            <xsl:value-of select="fmp:COL[5]"/>
                          </Data>
                        </Cell>
                        <Cell>
                          <Data ss:Type="String">
                            <xsl:value-of select="fmp:COL[6]"/>
                          </Data>
                        </Cell>
                        <Cell>
                          <Data ss:Type="String">
                            <xsl:value-of select="fmp:COL[7]"/>
                          </Data>
                        </Cell>
                        <Cell>
                          <Data ss:Type="String">
                            <xsl:value-of select="fmp:COL[8]"/>
                          </Data>
                        </Cell>
                        <Cell>
                          <Data ss:Type="String">
                            <xsl:value-of select="fmp:COL[9]"/>
                          </Data>
                        </Cell>                
                        <Cell>
                          <Data ss:Type="String">
                            <xsl:value-of select="fmp:COL[10]"/>
                          </Data>
                        </Cell>                
                      </xsl:for-each>
                    </Row>
                  </Table>
                </Worksheet>
              </Workbook>
              </xsl:template>
              <xsl:template match="fmp:FMPXMLRESULT"/>
              </xsl:stylesheet>

              Thanks again

              Martin

              • 4. Re: Help with XSLT Transformation and Formatting
                Korry

                In your first XSLT, your root level was "FMPXMLRESULT". Selecting nodes from "fmp:RESULTSET/fmp:ROW" was valid from that point.

                In the second example your root is "/". Now you'll need to select nodes from "fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW".

                So:

                      <Row>
                        <xsl:for-each select="fmp:RESULTSET/fmp:ROW">
                          ...

                needs to change to:

                      <Row>
                        <xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW">
                          ...

                • 5. Re: Help with XSLT Transformation and Formatting
                  ManjitSandhu

                  Thanks. I'd already tried that and am still getting titles but no data.

                  If I could get this working, is there a better way to populate the cells than repeating the Cell, Data, Select steps for columns 1 to 10?

                  And even with the updated header section, I am still getting the same 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?"

                  Should the file be saved as xlsx or xls?

                  Thanks for your help

                  Martin

                  • 6. Re: Help with XSLT Transformation and Formatting
                    Korry

                    Try this:

                    <?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"
                      xmlns:fmp="http://www.filemaker.com/fmpxmlresult"
                      exclude-result-prefixes="fmp">
                     
                      <xsl:output indent="yes" />
                     
                      <xsl:template match="fmp:FMPXMLRESULT/fmp:RESULTSET">
                        <Workbook>
                          <Styles>
                            <Style ss:ID="Heading">
                              <Font ss:Size="10" ss:Bold="1" />
                            </Style>
                          </Styles>
                          <Worksheet ss:Name="RFP">
                            <Table>
                              <Row>
                                <Cell ss:StyleID="Heading"><Data ss:Type="String">Client Name</Data></Cell>
                                <Cell ss:StyleID="Heading"><Data ss:Type="String">Stock Exchange</Data></Cell>
                                <Cell ss:StyleID="Heading"><Data ss:Type="String">Industry Sector</Data></Cell>
                                <Cell ss:StyleID="Heading"><Data ss:Type="String">Company/Trustee</Data></Cell>
                                <Cell ss:StyleID="Heading"><Data ss:Type="String">Lead Consultant</Data></Cell>
                                <Cell ss:StyleID="Heading"><Data ss:Type="String">Scheme Name</Data></Cell>
                                <Cell ss:StyleID="Heading"><Data ss:Type="String">Number of Active Members</Data></Cell>
                                <Cell ss:StyleID="Heading"><Data ss:Type="String">Deferred Members</Data></Cell>
                                <Cell ss:StyleID="Heading"><Data ss:Type="String">Total Funds Managed</Data></Cell>
                                <Cell ss:StyleID="Heading"><Data ss:Type="String">Open/Closed</Data></Cell>
                              </Row>
                              <xsl:for-each select="fmp:ROW">
                                <Row>
                                  <xsl:apply-templates />
                                </Row>
                              </xsl:for-each>
                            </Table>
                          </Worksheet>
                        </Workbook>
                      </xsl:template>
                     
                      <!--called for every "COL" node in a "ROW"-->
                      <xsl:template match="fmp:COL">
                        <Cell>
                          <Data ss:Type="String">
                            <xsl:value-of select="fmp:DATA" />
                          </Data>
                        </Cell>
                      </xsl:template>
                     
                    </xsl:stylesheet>

                    The resulting file is definitely XLSX, the xml-based Excel format, not the old binary XLS format.

                    • 7. Re: Help with XSLT Transformation and Formatting
                      ManjitSandhu

                      Thanks, much neater code...but it won't open at all if I set the filename to be .xlsx, and then when I revert back to .xls, I get the same message which if I choose yes, gives me a subsequent error 'Problems during load', which states the details are in a log file...which doesn't exist!

                      I've attached an image of the export process (last part of a bigger script), and below is the function which creates the filename (there are about 6 reports, so have tried to re-use code rather than repeat):

                      Let( [
                      // get the current date...
                      cDate = Get( CurrentDate ); 
                      // get the current time...
                      cTime = Get( CurrentTime ) ];
                      // get the documents path of the server...
                      Get ( DocumentsPath ) & 
                      // now build up the filename name...
                      ReportName & 
                      SpaceDelimiter &
                      Year ( cDate ) & 
                      Right( "0" & Month( cDate ); 2 ) & 
                      Right( "0" & Day( cDate); 2 ) & 
                      Right( "0" & Hour( cTime ); 2 ) & 
                      Right( "0" & Minute( cTime ); 2 ) & 
                      // and add the file extension...
                      ".xls")

                      for reference, the output filename is 'RFP Report YYYYMMDDHHMM.xls'

                      Is there a way to simply output the XML file, then I can mess about with the XML and XSLT in one of the online tools to see what's happening?

                      Thanks again - much appreciated.

                      Martin

                      • 8. Re: Help with XSLT Transformation and Formatting
                        ManjitSandhu

                        Am unable to upload a sample XML file to run through the XSLT transformation, so here it is as text:

                        <?xml version="1.0" encoding="UTF-8" ?><FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"><ERRORCODE>0</ERRORCODE><PRODUCT BUILD="11-06-2014" NAME="FileMaker" VERSION="Server 13.0v5"/><DATABASE DATEFORMAT="D/m/yyyy" LAYOUT="" NAME="DC_Client_Manager.fmp12" RECORDS="100" TIMEFORMAT="k:mm:ss "/><METADATA><FIELD EMPTYOK="NO" MAXREPEAT="1" NAME="CLIENT::d_ClientName" TYPE="TEXT"/><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="CLIENT::d_Stock_Exchange" TYPE="TEXT"/><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="CLIENT::d_IndustrySector" TYPE="TEXT"/><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="CLIENT::d_Company_Trustee" TYPE="TEXT"/><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="CLIENT::d_Lead_Consultant" TYPE="TEXT"/><FIELD EMPTYOK="NO" MAXREPEAT="1" NAME="d_SchemeName" TYPE="TEXT"/><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="d_NumberOfMembers" TYPE="NUMBER"/><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="d_NumberOfMembersDeferred" TYPE="NUMBER"/><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="d_Total_Fund_Values" TYPE="NUMBER"/><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="d_Closed_Flag_Display" TYPE="TEXT"/></METADATA><RESULTSET FOUND="1"><ROW MODID="10" RECORDID="274"><COL><DATA>Dummy Client 1</DATA></COL><COL><DATA></DATA></COL><COL><DATA></DATA></COL><COL><DATA>Trustee</DATA></COL><COL><DATA>Surname, Forename</DATA></COL><COL><DATA>Dummy Scheme</DATA></COL><COL><DATA>500</DATA></COL><COL><DATA>100</DATA></COL><COL><DATA>722416280</DATA></COL><COL><DATA>Open</DATA></COL></ROW></RESULTSET></FMPXMLRESULT>

                        • 9. Re: Help with XSLT Transformation and Formatting
                          Korry

                          Revised XSLT:

                          -> Added a <xsl:processing-instruction> element to get <?mso-application progid="Excel.Sheet"?> in the output.
                          -> Changed the root path to "/".
                           

                          <?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"
                            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:output indent="yes" />
                           
                            <xsl:template match="/">
                              <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
                              <Workbook>
                                <Styles>
                                  <Style ss:ID="Heading">
                                    <Font ss:Size="10" ss:Bold="1" />
                                  </Style>
                                </Styles>
                                <Worksheet ss:Name="RFP">
                                  <Table>
                                    <Row>
                                      <Cell ss:StyleID="Heading"><Data ss:Type="String">Client Name</Data></Cell>
                                      <Cell ss:StyleID="Heading"><Data ss:Type="String">Stock Exchange</Data></Cell>
                                      <Cell ss:StyleID="Heading"><Data ss:Type="String">Industry Sector</Data></Cell>
                                      <Cell ss:StyleID="Heading"><Data ss:Type="String">Company/Trustee</Data></Cell>
                                      <Cell ss:StyleID="Heading"><Data ss:Type="String">Lead Consultant</Data></Cell>
                                      <Cell ss:StyleID="Heading"><Data ss:Type="String">Scheme Name</Data></Cell>
                                      <Cell ss:StyleID="Heading"><Data ss:Type="String">Number of Active Members</Data></Cell>
                                      <Cell ss:StyleID="Heading"><Data ss:Type="String">Deferred Members</Data></Cell>
                                      <Cell ss:StyleID="Heading"><Data ss:Type="String">Total Funds Managed</Data></Cell>
                                      <Cell ss:StyleID="Heading"><Data ss:Type="String">Open/Closed</Data></Cell>
                                    </Row>
                                    <xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW">
                                      <Row>
                                        <xsl:apply-templates />
                                      </Row>
                                    </xsl:for-each>
                                  </Table>
                                </Worksheet>
                              </Workbook>
                            </xsl:template>
                           
                            <!--called for every "COL" node in a "ROW"-->
                            <xsl:template match="fmp:COL">
                              <Cell>
                                <Data ss:Type="String">
                                  <xsl:value-of select="fmp:DATA" />
                                </Data>
                              </Cell>
                            </xsl:template>
                           
                          </xsl:stylesheet>

                           

                          Looks like the output isn't XLSX, but rather "XML Spreadsheet" with proper extension *.xml. Of course your users' default XML handler probably isn't Excel, but that's another topic.

                           

                          • 10. Re: Help with XSLT Transformation and Formatting
                            ManjitSandhu

                            Thanks @Korry, that's resolved the issue and am now getting output, and whilst I still get the error about file extension, the file does open, so think they will have to just accept that limitation. All our PCs are managed, so to get the default XML handler changed would mean a change at policy level, so not going to happen as this application is only used by <100 users out of >16,000 staff.

                            The only issue I have left to resolve is part of my original question in that I wanted certain fields to be output as strings, but currently, every column is a string - it would make better sense now for me to set certain fields to numbers leaving the rest as strings rather than the other way around. I'm sure I've seen some examples so will continue looking. 

                            Thanks so much for your help - very much appreciated

                            Martin

                            • 11. Re: Help with XSLT Transformation and Formatting
                              ManjitSandhu

                              Sorted the issue with numbers/text - might be a bit too generic as I know I will have issues with a field that could be a 12 digit number (which is an identifier so should remain a string) but this gives me numbers where I expect them in this example:

                              <xsl:template match="fmp:COL">
                                <xsl:variable name="type">
                                  <xsl:choose>
                                    <xsl:when test="number(.) = .">Number</xsl:when>
                                    <xsl:otherwise>String</xsl:otherwise>
                                  </xsl:choose>
                                </xsl:variable>
                                <Cell><Data ss:Type="{$type}"><xsl:value-of select="fmp:DATA"/></Data></Cell>
                              </xsl:template>

                              In my head, I'd like to do a 'choose except if field = CIS Identifier', but think that is another post/question!

                              Thanks again

                              Martin