9 Replies Latest reply on Apr 25, 2017 4:09 PM by beverly

    Sending Variable through Script and XML Excel Spreadsheet?

    mprythero

      I was curious as to whether or not it was possible to create a variable (say for this example, titled Weekending), could it somehow be sent along through the script and XML (I'm working with an .XSLT file that I retrived from the forums) to a final destination in the one of the cells? It only needs to occur once in the spreadsheet, which is why I'm thinking of a Variable being the appropriate option, but I'm not entirely sure.

       

      Thanks for any help ahead of time!

       

      Matt

        • 1. Re: Sending Variable through Script and XML Excel Spreadsheet?
          beverly

          XML and XSLT only uses data (text, numbers, dates, times). if your variable is passed as a field (even a global field) in the XML export, then it can be used in the XSLT. To use the value only once, the XPath would be the same, but make sure to narrow to once:

          xsl:value-of select="field[1]"

          & don't place inside a for-each loop.

          beverly

           

          p.s. can you post the XSLT here?

          • 2. Re: Sending Variable through Script and XML Excel Spreadsheet?
            mprythero

            Hi beverly - You can find my template that I've been working on here: http://www.arvadahistory.org/public/FMP2XLS.xslt

             

            In it you should be able to find a section with the below (commented out because otherwise I run into issues...):

            <!--Here is where my Weekending Result should come in...   <Cell ss:Index="1" ss:MergeAcross="3" ss:StyleID="MyTitleStyle">
               <Data ss:Type="String">
               <xsl:value-of select="Weekending[1]" />
               </Data>
               </Cell>-->

             

            So I do get an error if I add this code, but I know it's primarily at the basis of myself being completely new to this form of art I'll call it.

             

            I do have a field called Weekending in the same table as that I am exporting, as well as I know I am exporting it. Below are the images of what I'd like, versus what I have now...

             

            The first image shows that the date (and the only one in the last image) should be moved up next to Roadrunner Week Ending:, rather than in the table (and I know that you mentioned having it only appear once, but with the code above, that doesn't seem to want to work. Thanks ahead of time!

            old.jpg

            new.jpg

            • 3. Re: Sending Variable through Script and XML Excel Spreadsheet?
              beverly

              ok, what column (in export order) has this date in your export? copy and paste the METADATA part of your export (as raw XML).

               

              you don't need to add another Cell, you can just append the correct field:

              <Data ss:Type="String">

              >     <xsl:text>ROADRUNNER WEEK ENDING: </xsl:text><!-- here is where your field would go with the xsl:value-of -->

              </Data>

              beverly

              • 4. Re: Sending Variable through Script and XML Excel Spreadsheet?
                mprythero

                Hi Beverly -

                 

                I'm posting the entire file, mainly because like I had mentioned before, the base of it isn't my creation (I pulled it from here and have been parsing down the code to only the necessities while learning XML at the same time... : [ANN] Export custom spreadsheets with FMP2XLS v1.01 )

                 

                I'm just attaching the image of the export screen from filemaker as well, just so it's posted:

                export.jpg

                 

                But anyways, like I said, here below is the code from my file:

                 

                <?xml version="1.0" encoding="utf-16"?>
                <?mso-application progid="Excel.Sheet"?>
                <xsl:stylesheet version="1.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:html="http://www.w3.org/TR/REC-html40"
                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:xalan="http://xml.apache.org/xalan"
                xmlns:fmp="http://www.filemaker.com/fmpxmlresult" exclude-result-prefixes="fmp">
                <!--
                This file is v1.03 of FMP2XLS - Current as of April 8, 2016 - Copyright (c) 2016 by Doug Staubach
                
                This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License. 
                Official License is available at the following URL: http://creativecommons.org/licenses/by-nc/4.0/
                
                You are free to copy, use, distribute, modify or display the contents of this file in any format
                and for any purpose that you wish, with only 2 conditions: (1) That you attribute the original work
                to me, Doug Staubach, (in other words, put my full name in the credits as the original author), and
                (2) you may not sell my work or create a commercial software program based on my work, without my
                permission. (My name and contact information is listed at the bottom of this comment section). 
                
                The purpose of this file is to assist FileMaker users by giving them the ability to create fully
                customized (pretty) Excel Spreadsheets from within a FileMaker Database (or WebDirect). Although
                FileMaker does provide a native ability to export data using Excel format, the files that they
                create are rather simple (they do not include formatting for numeric or date-time cells, they do
                not include options for renaming field headers, they do not include options for adding calculated
                forumlas for certain fields, they do not include options for adding summary fields like count, 
                sum, min or max functions, etc.) - ALL of those options are available through the use of this
                tempalte, but it might take some self-education, programming, and learning by you, to make your
                particular implementation of this example work correctly fro your situation.
                
                This template will only work when the FileMaker user chooses the correct XML format (FMPXMLRESULT)
                during the record export process. However, this template is designed to work equally whether the
                user chooses to 'apply current layout's formatting to exported data' or not. This template includes
                a section for 'WorkSheet Options' that contains a lot of Excel-specific data (like print settings).
                The best way to set these sorts of values if to create your own Excel spreadsheet, then save-as
                an XML file, then open that file with a text editor, copy the 'WorkSheet Options' section from
                your file, and replace the 'WorkSheet Options' section in this file.
                
                Although this template was designed to work with a specific sample XML file (and the field names
                from that sample file have been hard-coded into this template to show how to rename the header, or
                add a cell formula, or choose a different display format for those cells), it will work just fine
                when applied to a different database and different fields (it can be used as a generic template
                without having to make any changes).
                
                Note: This template represents a LOT of frustrating hours of trial and error to makes sure that
                all of the functions and formats worked correctly. If you change something in this template and
                you get a "Table" error afterward, it will be up to you to figure it out. I am providing this
                template as an example of how to do most of the things that a person might want to do with a
                spreadsheet, but I did not exhaust all of the possibilites. Furthermore, I am not an employee
                of FileMaker, Apple, or Microsoft (they own their own copyrights and trademarks), and I am not
                interested in providing free technical support to the user community at large. - I have blazed
                a trial, and I'm hoping that other people will find it useful and build on my work.
                
                If you find this template useful, please tell me so. You can do so by adding a comment to my user 
                profile in the FileMaker forums, or by sending me a message on those forums. It is always nice
                to hear positive feedback. - If you have some code improvements (whether for speed or for added
                functionality), please send me copies of your work so I can adopt those techniques as well.
                
                Thanks,
                Doug Staubach
                https://community.filemaker.com/people/DougStaubach
                -->
                
                <!-- Note: the next section has been disabled, because I don't know how to make it work -->
                <!-- If you are able to fix this, I would be grateful (contact me at the FileMaker forums) -->
                <!--
                <xalan:script language="JavaScript" implements-prefix="myscript">
                    <![CDATA[
                    function getOS(){
                        var OSName="Unknown OS";
                        if (navigator.appVersion.indexOf("Win")!=-1) OSName="Windows";
                        if (navigator.appVersion.indexOf("Mac")!=-1) OSName="MacOS";
                        if (navigator.appVersion.indexOf("X11")!=-1) OSName="UNIX";
                        if (navigator.appVersion.indexOf("Linux")!=-1) OSName="Linux";
                        return OSName;
                        }
                    ]]>
                </xalan:script>
                -->
                
                <xsl:template match="/">
                <!-- <xsl:variable name="zCurrentOS" select="myscript:getOS()" /> -->
                    <Workbook>
                        <!-- Define styles in advance (YOU must define these styles and give them unique IDs) -->
                        <Styles>
                            <Style ss:ID="Default" ss:Name="Normal">
                                <Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1" />
                                <Borders />
                                <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="12" ss:Color="#000000" ss:Bold="0"/>
                                <Interior />
                                <NumberFormat/>
                                <Protection ss:Protected="0"/>
                            </Style>
                            <Style ss:ID="MyTitleStyle">
                                <Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="0" />
                                <Font ss:Size="12" ss:Color="#000000" ss:Bold="1"/>
                            </Style>
                            <Style ss:ID="MyHeaderStyle">
                                <Alignment ss:Horizontal="Center" ss:Vertical="Bottom" ss:WrapText="0"/>
                                <Font ss:Color="#FFFFFF" ss:Bold="1"/>
                                <Interior ss:Color="#808080" ss:Pattern="Solid"/>
                                <Borders>
                                    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
                                    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#FFFFFF"/>
                                    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#FFFFFF"/>
                                    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
                                </Borders>
                            </Style>
                            <Style ss:ID="MyCellBorders">
                                <Borders>
                                    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
                                    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
                                    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
                                    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
                                </Borders>
                            </Style>
                            <Style ss:ID="MyTextStyle1" ss:Parent="MyCellBorders">
                                <Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/>
                            </Style>
                            <Style ss:ID="MyNumberStyle1" ss:Parent="MyCellBorders">
                                <Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="0" />
                                <NumberFormat ss:Format="#;-#;0;@"/>
                            </Style>
                            <Style ss:ID="MyNumberStyle2" ss:Parent="MyCellBorders">
                                <Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="0" />
                                <NumberFormat ss:Format="#,##0.00;-#,##0.00;0;@"/>
                            </Style>
                            <Style ss:ID="MyDateStyle1" ss:Parent="MyCellBorders">
                                <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="0" />
                                <!-- NUMBER FORMATS: Each space, dash, or literal text character must be escaped with '\' -->
                                <!-- text strings can be enclosed with &quote;TEXT-HERE&quote; and it is best to end with ';@' -->
                                <!-- to ensure portability, I intentionally avoid the use of named formates like 'Short Date' -->
                                <!-- For help with creating custom number formats, check the following Microsoft article -->
                                <!-- https://support.office.com/en-us/Search/results?query=Create+or+delete+a+custom+number+format -->
                                <NumberFormat ss:Format="d\ mmm\ yyyy;@"/>
                            </Style>
                            <Style ss:ID="MyDateStyle2" ss:Parent="MyCellBorders">
                                <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="0" />
                                <NumberFormat ss:Format="mmm\ d\,\ yyyy;@"/>
                                <!-- excel displays a nagging pop-up (exclamation point) on any cell that contains an unprotected formula -->
                                <!-- to avoid this, you should should turn cell protection 'on' for any style used with a calculated field -->
                                <!-- the line below will turn cell protection on for any field that uses this particular Style -->
                               <Protection/>
                            </Style>
                            <Style ss:ID="MyTimeStyle1" ss:Parent="MyCellBorders">
                                <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="0" />
                                <NumberFormat ss:Format="hh:mm;@"/>
                            </Style>
                            <Style ss:ID="MyStampStyle1" ss:Parent="MyCellBorders">
                                <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1" />
                                <NumberFormat ss:Format="d\ mmm\ yyyy\ &quot;at&quot;\ h:mm\ AM/PM;@"/>
                            </Style>
                            <Style ss:ID="MyCountStyle1" >
                                <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1" />
                                <!-- I like to put the word "COUNT:" here, instead of typing it in a different cell -->
                                <NumberFormat ss:Format="&quot;COUNT: &quot;#"/>
                               <Protection/>
                            </Style>
                            <Style ss:ID="MySumStyle1" >
                                <Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1" />
                                <NumberFormat ss:Format="0.0#"/>
                                <Font ss:Color="#000000" ss:Bold="1"/>
                                <Protection/>
                            </Style>
                            <Style ss:ID="MyMinStyle1" >
                                <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1" />
                                <NumberFormat ss:Format="&quot;MIN: &quot;#;&quot;MIN: &quot;\-#;&quot;MIN: &quot;0;@"/>
                                   <Protection/>
                            </Style>
                        </Styles>
                        <!-- Create the worksheet (and set the tab name to match the FM database name) -->
                        <!--{fmp:FMPXMLRESULT/fmp:DATABASE/@NAME}-->
                        <Worksheet ss:Name="Billing Statement">
                            <Table x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="150" ss:TopCell="2">
                                <!-- Specific column widths for the following columns (in order) - all others use default width -->
                                <Column ss:AutoFitWidth="0" ss:Width="150"/><!-- first column is 25 pixels wide -->
                                <Column ss:Hidden="0" ss:AutoFitWidth="1" ss:Width="150"/>
                                <Column ss:Hidden="0" ss:AutoFitWidth="1" ss:Width="150"/>
                                <!-- Now, get all of the data for the sheet itself (the cell values and formats) -->
                                <xsl:call-template name="FMP12toExcel2004" />
                                <!-- After we have written all data, go back and resize autowidth columns  -->
                                <!-- Unfortunately, this only works for numeric and date values; not text  -->
                                <xsl:for-each select="Column">
                                    <xsl:attribute name="ss:AutoFitWidth">1</xsl:attribute>
                                </xsl:for-each>
                            </Table>
                            <!-- Excel worksheet options section (freeze panes, print landscape, add footer, etc) -->
                            <!-- The best way to set these valus is to create a spreadsheet that has all of the -->
                            <!-- settings the way you like them, then save that sheet as XML, then edit that file -->
                            <!-- using a text editor, then copy the 'WorkSheet Options section from that file -->
                            <!-- and paste it into this file (replacing the values in this section) -->
                            <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                                <PageSetup>
                                    <Layout x:Orientation="Portrait"/>
                                    <!--<Footer x:Data="&amp;L&amp;A&amp;C &amp;P / &amp;N&amp;R&amp;D &amp;T"/>-->
                                    <PageMargins x:Bottom="0.75" x:Top="0.75" x:Left="0.7" x:Right="0.7"/>
                                </PageSetup>
                                <FitToPage/>
                                <Print>
                                    <FitHeight>0</FitHeight>
                                    <ValidPrinterInfo/>
                                    <PaperSizeIndex>1</PaperSizeIndex>
                                    <Scale>100</Scale>
                                    <HorizontalResolution>300</HorizontalResolution>
                                    <VerticalResolution>300</VerticalResolution>
                                </Print>
                                <PageLayoutZoom>0</PageLayoutZoom>
                                <Selected/>
                                <DoNotDisplayGridlines/>
                                <SplitHorizontal>0</SplitHorizontal>
                                <TopRowBottomPane>0</TopRowBottomPane>
                                <SplitVertical>0</SplitVertical>
                                <LeftColumnRightPane>0</LeftColumnRightPane>
                                <ActivePane>0</ActivePane>
                                <Panes>
                                    <!--<Pane><Number>3</Number></Pane>
                                    <Pane><Number>2</Number></Pane>
                                    <Pane><Number>1</Number></Pane>
                                    <Pane><Number>0</Number></Pane>-->
                                </Panes>
                                <FreezePanes/>
                                <ProtectObjects>False</ProtectObjects>
                                <ProtectScenarios>False</ProtectScenarios>
                            </WorksheetOptions>
                        </Worksheet>
                    </Workbook>
                </xsl:template>
                
                <xsl:template name="FMP12toExcel2004">
                    <!-- PART1 - Create a worksheet title (layout or database name and count of records selected) -->
                    <Row ss:AutoFitHeight="1">
                        <Cell ss:Index="1" ss:MergeAcross="3" ss:StyleID="MyTitleStyle">
                            <Data ss:Type="String">
                                <xsl:text>ROADRUNNER TRANSPORTATION SERVICES</xsl:text>
                            </Data>
                        </Cell>
                    </Row>
                <!--    <Row ss:AutoFitHeight="1">
                        <Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="MyTitleStyle">
                            <Data ss:Type="String">
                <!-\-                <xsl:choose>
                                    <xsl:when test="fmp:FMPXMLRESULT/fmp:DATABASE/@LAYOUT!=''">
                                        <xsl:value-of select="fmp:FMPXMLRESULT/fmp:DATABASE/@LAYOUT" />
                                    </xsl:when>
                                    <xsl:otherwise>
                                        <xsl:value-of select="fmp:FMPXMLRESULT/fmp:DATABASE/@NAME" />
                                    </xsl:otherwise>
                                </xsl:choose>-\->
                                <xsl:text>ROADRUNNER TRANSPORTATION SERVICES</xsl:text>
                <!-\-                <xsl:value-of select="fmp:FMPXMLRESULT/fmp:RESULTSET/@FOUND"/>
                                <xsl:text> out of </xsl:text>
                                <xsl:value-of select="fmp:FMPXMLRESULT/fmp:DATABASE/@RECORDS"/>
                                <xsl:text> records)</xsl:text>-\->
                            </Data>
                        </Cell>
                    </Row>-->
                    
                    <Row ss:AutoFitHeight="1">
                        <Cell ss:Index="1" ss:MergeAcross="3" ss:StyleID="MyTitleStyle">
                            <Data ss:Type="String">
                                <xsl:text>4900 S Pennsylavania Ave</xsl:text>
                            </Data>
                        </Cell>
                    </Row>
                    <Row ss:AutoFitHeight="1">
                        <Cell ss:Index="1" ss:MergeAcross="3" ss:StyleID="MyTitleStyle">
                            <Data ss:Type="String">
                                <xsl:text>Cudahy, WI 53110</xsl:text>
                            </Data>
                        </Cell>
                    </Row>
                    <Row ss:AutoFitHeight="1">
                        <Cell ss:Index="1" ss:MergeAcross="3" ss:StyleID="MyTitleStyle">
                            <Data ss:Type="String">
                                <xsl:text></xsl:text>
                            </Data>
                        </Cell>
                    </Row>
                    <Row ss:AutoFitHeight="1">
                        <Cell ss:Index="1" ss:MergeAcross="3" ss:StyleID="MyTitleStyle">
                            <Data ss:Type="String">
                                <xsl:text></xsl:text>
                            </Data>
                        </Cell>
                    </Row>
                    <Row ss:AutoFitHeight="1">
                        <Cell ss:Index="1" ss:MergeAcross="1" ss:StyleID="MyTitleStyle">
                            <Data ss:Type="String">
                                <xsl:text>ROADRUNNER WEEK ENDING:</xsl:text><!-- here is where your field would go with the xsl:value-of -->
                            </Data>
                        </Cell>
                    </Row>
                    <!-- Next line inserts an empty row between worksheet title and header row -->
                    <Row ss:AutoFitHeight="0"/>
                    <Row ss:AutoFitHeight="0"/>
                    <!-- PART2 - Fill in the header row with field names -->
                    <Row>
                        <!-- Next line inserts an empty column on the left side of the sheet -->
                        <!-- This is simply a personal preference of mine :) -->
                        <!--<Cell />-->
                        <!-- this section gets field names directly from the FM database export file -->
                        <xsl:for-each select="fmp:FMPXMLRESULT/fmp:METADATA/fmp:FIELD">
                            <Cell ss:StyleID="MyHeaderStyle">
                                <Data ss:Type="String">
                                    <xsl:choose>
                                        <!-- replace ugly field names with pretty names if you like -->
                                        <xsl:when test="@NAME='MyParagraphWithWrapping'">
                                            <xsl:text>PRO#</xsl:text>
                                        </xsl:when>
                                        <xsl:otherwise>
                                            <!-- otherwise, the FileMaker fieldname will be used -->
                                            <xsl:value-of select="@NAME"/>
                                        </xsl:otherwise>
                                    </xsl:choose>
                                </Data>
                            </Cell>
                        </xsl:for-each>
                    </Row>
                    <!-- PART3 - Fill in the actual data (copy FM fields into Excel cells) -->
                    <xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW">
                        <Row ss:AutoFitHeight="1">
                            <!-- Next line inserts an empty column on the left side of the sheet -->
                            <!-- This is simply a personal preference of mine :) -->
                            <!--<Cell />-->
                            <xsl:for-each select="fmp:COL">
                                <xsl:variable name="i" select="position()" />
                                <xsl:variable name="zFieldName" select="/fmp:FMPXMLRESULT/fmp:METADATA/fmp:FIELD[$i]/@NAME" />
                                <xsl:variable name="zFieldType" select="/fmp:FMPXMLRESULT/fmp:METADATA/fmp:FIELD[$i]/@TYPE" />
                                <!-- We are looping through rows and columns, and now we can examine the cell -->
                                <Cell>
                                    <xsl:choose>
                                        <!-- Add FORMULAS for specific fields chosen by name (YOU have to manually create formulas) -->
                                        <xsl:when test="$zFieldName='MyCalcUpperText'">
                                            <xsl:attribute name="ss:StyleID">MyTextStyle1</xsl:attribute>
                                            <xsl:attribute name="ss:Formula">=UPPER(RC[-8])</xsl:attribute>
                                        </xsl:when>
                                        <xsl:when test="$zFieldName='MyCalcDatePlus30'">
                                            <xsl:attribute name="ss:StyleID">MyDateStyle2</xsl:attribute>
                                            <xsl:attribute name="ss:Formula">=RC[-4]+30</xsl:attribute>
                                        </xsl:when>
                                        <!-- All of the remaining fields do not have cell formulas -->
                                        <!-- So we define the style, data type, and data contents based on the fieldtype -->
                                        <!-- is the current field a NUMBER field? -->
                                        <xsl:when test="$zFieldType='NUMBER'">
                                            <xsl:variable name="zNewNum">
                                                <xsl:call-template name="FixNumber">
                                                    <xsl:with-param name="zOldNum" select="fmp:DATA" />
                                                </xsl:call-template>
                                            </xsl:variable>
                                            <xsl:choose>
                                                <!-- if a valid number was not returned, choose a text style and type -->
                                                <!-- (we need to do this to avoid 'Table' errors in the XML spreadsheet) -->
                                                <xsl:when test="string(number($zNewNum))='NaN'">
                                                    <xsl:attribute name="ss:StyleID">MyTextStyle1</xsl:attribute>
                                                    <Data>
                                                        <xsl:attribute name="ss:Type">String</xsl:attribute>
                                                        <xsl:value-of select="." />
                                                    </Data>
                                                </xsl:when>
                                                <!-- ok, the number was valid, but we have two different number formats, so -->
                                                <!-- we must make a choice. In this case we will decide based on field name  -->
                                                <xsl:when test="$zFieldName='Amount Due'">
                                                    <xsl:attribute name="ss:StyleID">MyNumberStyle2</xsl:attribute>
                                                    <Data>
                                                        <xsl:attribute name="ss:Type">Number</xsl:attribute>
                                                        <xsl:value-of select="$zNewNum" />
                                                    </Data>
                                                </xsl:when>
                                                <xsl:otherwise>
                                                    <xsl:attribute name="ss:StyleID">MyNumberStyle1</xsl:attribute>
                                                    <Data>
                                                        <xsl:attribute name="ss:Type">Number</xsl:attribute>
                                                        <xsl:value-of select="$zNewNum" />
                                                    </Data>
                                                </xsl:otherwise>
                                            </xsl:choose>
                                        </xsl:when>
                                        <!-- is the current field a DATE field? -->
                                        <xsl:when test="$zFieldType='DATE'">
                                            <xsl:variable name="zNewDate">
                                                <xsl:call-template name="DateToISO">
                                                    <xsl:with-param name="zOldDate" select="fmp:DATA" />
                                                </xsl:call-template>
                                            </xsl:variable>
                                            <xsl:choose>
                                                <!-- if a valid date was returned, choose a date style and type -->
                                                <xsl:when test="substring($zNewDate,1,4)='ISO:'">
                                                    <xsl:attribute name="ss:StyleID">MyDateStyle1</xsl:attribute>
                                                    <Data>
                                                        <xsl:attribute name="ss:Type">DateTime</xsl:attribute>
                                                        <xsl:value-of select="substring($zNewDate,5)" />
                                                    </Data>
                                                </xsl:when>
                                                <!-- otherwise, choose a text style and type (to avoid 'Table' errors) -->
                                                <xsl:otherwise>
                                                    <xsl:attribute name="ss:StyleID">MyTextStyle1</xsl:attribute>
                                                    <Data>
                                                        <xsl:attribute name="ss:Type">String</xsl:attribute>
                                                        <xsl:value-of select="." />
                                                    </Data>
                                                </xsl:otherwise>
                                            </xsl:choose>
                                        </xsl:when>
                                        <!-- is the current field a TIME field? -->
                                        <xsl:when test="$zFieldType='TIME'">
                                            <xsl:variable name="zNewTime">
                                                <xsl:call-template name="TimeToISO">
                                                    <xsl:with-param name="zOldTime" select="fmp:DATA" />
                                                </xsl:call-template>
                                            </xsl:variable>
                                            <xsl:choose>
                                                <!-- if a valid time was returned, choose a time style and type -->
                                                <xsl:when test="substring($zNewTime,1,11)='1899-12-31T'">
                                                    <xsl:attribute name="ss:StyleID">MyTimeStyle1</xsl:attribute>
                                                    <Data>
                                                        <xsl:attribute name="ss:Type">DateTime</xsl:attribute>
                                                        <xsl:value-of select="$zNewTime" />
                                                    </Data>
                                                </xsl:when>
                                                <!-- otherwise, choose a text style and type (to avoid 'Table' errors) -->
                                                <xsl:otherwise>
                                                    <xsl:attribute name="ss:StyleID">MyTextStyle1</xsl:attribute>
                                                    <Data>
                                                        <xsl:attribute name="ss:Type">String</xsl:attribute>
                                                        <xsl:value-of select="." />
                                                    </Data>
                                                </xsl:otherwise>
                                            </xsl:choose>
                                        </xsl:when>
                                        <!-- is the current field a TIMESTAMP field? -->
                                        <xsl:when test="$zFieldType='TIMESTAMP'">
                                            <xsl:variable name="zLeftHalf" select="substring-before(fmp:DATA,':')" />
                                            <xsl:variable name="zBreakAt">
                                                <xsl:choose>
                                                    <xsl:when test="string(number(substring($zLeftHalf,string-length($zLeftHalf)-2,1)))='NaN'">
                                                        <xsl:value-of select="string-length($zLeftHalf)-2" />    
                                                    </xsl:when>
                                                    <xsl:otherwise>
                                                        <xsl:value-of select="string-length($zLeftHalf)-1" />    
                                                    </xsl:otherwise>
                                                </xsl:choose>
                                            </xsl:variable>
                                            <xsl:variable name="zNewDate">
                                                <!-- First, test for FileMaker timestamp problem (only occurs when using timestamps combined with system formats) -->
                                                <xsl:choose>
                                                    <xsl:when test="/fmp:FMPXMLRESULT/fmp:DATABASE/@LAYOUT=''">
                                                        <xsl:call-template name="DateToISO">
                                                            <!-- if the user made the choice to use system date formatting, the timestamp bug will occur. -->
                                                            <!-- to correct this in our date template, we will prefix the info with 'TSK' = Time Stamp Kludge) -->
                                                            <xsl:with-param name="zOldDate" select="concat('TSK',substring(fmp:DATA,1,number($zBreakAt)-1))" />
                                                        </xsl:call-template>
                                                    </xsl:when>
                                                    <xsl:otherwise>
                                                        <xsl:call-template name="DateToISO">
                                                            <!-- if the user made the choice to apply layout formatting to data, the kludge is not needed -->
                                                            <xsl:with-param name="zOldDate" select="substring(fmp:DATA,1,number($zBreakAt)-1)" />
                                                        </xsl:call-template>
                                                    </xsl:otherwise>
                                                </xsl:choose>
                                            </xsl:variable>
                                            <xsl:variable name="zNewTime">
                                                <xsl:call-template name="TimeToISO">
                                                    <xsl:with-param name="zOldTime" select="substring(fmp:DATA,number($zBreakAt)+1)" />
                                                </xsl:call-template>
                                            </xsl:variable>
                                            <xsl:choose>
                                                <!-- if a valid date and time were returned, choose a date-time style and type -->
                                                <xsl:when test="substring($zNewDate,1,4)='ISO:' and substring($zNewTime,1,11)='1899-12-31T'">
                                                    <xsl:attribute name="ss:StyleID">MyStampStyle1</xsl:attribute>
                                                    <Data>
                                                        <xsl:attribute name="ss:Type">DateTime</xsl:attribute>
                                                        <xsl:value-of select="concat(substring($zNewDate,5,10),substring($zNewTime,11))" />
                                                    </Data>
                                                </xsl:when>
                                                <!-- otherwise, choose a text style and type (to avoid 'Table' errors) -->
                                                <xsl:otherwise>
                                                    <xsl:attribute name="ss:StyleID">MyTextStyle1</xsl:attribute>
                                                    <Data>
                                                        <xsl:attribute name="ss:Type">String</xsl:attribute>
                                                        <xsl:value-of select="." />
                                                    </Data>
                                                </xsl:otherwise>
                                            </xsl:choose>
                                          </xsl:when>
                                        <!-- if it was none of the above, just make it a TEXT field -->
                                        <xsl:otherwise>
                                            <xsl:attribute name="ss:StyleID">MyTextStyle1</xsl:attribute>
                                            <Data>
                                                <xsl:attribute name="ss:Type">String</xsl:attribute>
                                                <!-- Next line translates line breaks for Mac -->
                                                <!-- <xsl:value-of select="translate(.,'&#10;','&#13;')" /> -->
                                                <!-- Next line translates line breaks for Windows -->
                                                <!-- <xsl:value-of select="translate(.,'&#13;','&#10;')" /> -->
                                                <!-- without the translation, it looks like the following -->
                                                <!-- <xsl:value-of select="." /> -->
                                                <xsl:value-of select="." />
                                            </Data>
                                        </xsl:otherwise>
                                    </xsl:choose>
                                </Cell>
                            </xsl:for-each><!-- next column -->
                        </Row>
                    </xsl:for-each><!-- next row -->
                    <!-- PART4 - Insert a final row for 'summary formulas' at the bottom of certain columns -->
                    <!-- Summary formulas are used to calculate a SUM, COUNTA, MIN or MAX of all data rows -->
                    <!-- Next line inserts an empty row between data rows and summary row -->
                    <Row ss:AutoFitHeight="0"/>
                    <Row>
                        <!-- Next line inserts an empty column on the left side of the sheet -->
                        <!-- This is simply a personal preference of mine :) -->
                        <!--<Cell />-->
                        <xsl:for-each select="fmp:FMPXMLRESULT/fmp:METADATA/fmp:FIELD">
                            <xsl:variable name="i" select="position()" />
                            <xsl:variable name="zFieldName" select="/fmp:FMPXMLRESULT/fmp:METADATA/fmp:FIELD[$i]/@NAME" />
                            <xsl:choose>
                                <!-- Choose which columns will have summary values based on field name or column sequence -->
                                <xsl:when test="$i=1">
                                    <Cell ss:StyleID="MySumStyle1">
                                        <Data ss:Type="String">
                                            <Font ss:Color="#000000" ss:Bold="1"/>
                                            <xsl:text>Statement Total:</xsl:text>
                                        </Data>
                                    </Cell>
                                    <Cell ss:StyleID="MySumStyle1">
                                        <xsl:attribute name="ss:Formula">=SUM(R4C:R[-2]C)</xsl:attribute>
                                    <Data ss:Type="Number"></Data>
                                    </Cell>
                                </xsl:when>
                <!--                <xsl:when test="$zFieldName='Amount Due'">
                                    <Cell ss:StyleID="MySumStyle1">
                                    <xsl:attribute name="ss:Formula">=SUM(R4C:R[-2]C)</xsl:attribute>
                                    <Data ss:Type="Number"></Data>
                                    </Cell>
                                </xsl:when>
                                <xsl:when test="$zFieldName='MyNumberComplicated'">
                                    <Cell ss:StyleID="MyMinStyle1">
                                    <xsl:attribute name="ss:Formula">=MIN(R4C:R[-2]C)</xsl:attribute>
                                    <Data ss:Type="Number"></Data>
                                    </Cell>
                                </xsl:when>-->
                                <xsl:otherwise>
                                    <!-- move to the next column without doing anyting -->
                                    <Cell />
                                </xsl:otherwise>
                            </xsl:choose>
                        </xsl:for-each>
                    </Row>
                </xsl:template>
                
                <xsl:template name="DateToISO">
                    <xsl:param name="zOldDate" />
                    <!-- To make sure that date conversions always work 100% of the time without errors, when you are selecting FileMaker -->
                    <!-- export fields, you should NOT enable the checkbox that says "apply current layout's formatting to exported data". -->
                    <!-- Having said that, this sub-template will attempt to recognize other (common) date formats within the data itself. -->
                    <!-- Numeric date formats for USA, Ireland and Germany were tested. Note that spelled out month like "February" and "Feb" -->
                    <!-- are listed below in Engligh only, but other languages could be supported as well, using the same techniques. -->
                    <!-- If the date format cannot be detected, then this function returns nothing (an empty value). -->
                    <!-- The next line will grab the date format from the XML file, which will be be used to help decipher the date field -->
                    <xsl:variable name="zSystemDateFormat" select="translate(normalize-space(/fmp:FMPXMLRESULT/fmp:DATABASE/@DATEFORMAT),'DMY. /,','dmy---')" />
                    <!-- From this point forward, we will be testing and manipulating the contents of the actual date in each cell value -->
                    <xsl:variable name="zKludgeTest">
                        <xsl:choose>
                            <xsl:when test="substring($zOldDate,1,3)='TSK'">
                                <xsl:text>Yes</xsl:text>
                            </xsl:when>
                            <xsl:otherwise>
                                <xsl:text>No</xsl:text>
                            </xsl:otherwise>
                        </xsl:choose>
                    </xsl:variable>
                    <xsl:variable name="zRemoveTest">
                        <xsl:choose>
                            <xsl:when test="$zKludgeTest='Yes'">
                                <xsl:value-of select="substring($zOldDate,4)" />
                            </xsl:when>
                            <xsl:otherwise>
                                <xsl:value-of select="$zOldDate" />
                            </xsl:otherwise>
                        </xsl:choose>
                    </xsl:variable>
                    <!-- next line changes all uppercase characters to lowercase characters, and changes quote character to a space -->
                    <xsl:variable name="zStep1" select="translate(normalize-space($zRemoveTest),'ABCDEFGHIJKLMNOPQRSTUVWXYZ&quot;','abcdefghijklmnopqrstuvwxyz ')" />
                    <!-- next line changes all other date delimiters into space characters (to do this we had to switch ' and " characters) -->
                    <xsl:variable name="zStep2" select='normalize-space(translate($zStep1,"-,./\()|&apos;","              "))' />
                    <xsl:variable name="zStep3">
                        <xsl:choose>
                            <!-- completely remove ordinal suffixes: will change 1st, 2nd, 3rd, 11th, 30th to 1, 2, 3, 11, 30 -->
                            <xsl:when test="contains($zStep2,'1st')">
                                <xsl:value-of select="concat(substring-before($zStep2,'1st'),'1',substring-after($zStep2,'1st'))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep2,'2nd')">
                                <xsl:value-of select="concat(substring-before($zStep2,'2nd'),'2',substring-after($zStep2,'2nd'))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep2,'3rd')">
                                <xsl:value-of select="concat(substring-before($zStep2,'3rd'),'3',substring-after($zStep2,'3rd'))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep2,'1th')">
                                <xsl:value-of select="concat(substring-before($zStep2,'1th'),'1',substring-after($zStep2,'1th'))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep2,'2th')">
                                <xsl:value-of select="concat(substring-before($zStep2,'2th'),'2',substring-after($zStep2,'2th'))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep2,'3th')">
                                <xsl:value-of select="concat(substring-before($zStep2,'3th'),'3',substring-after($zStep2,'3th'))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep2,'4th')">
                                <xsl:value-of select="concat(substring-before($zStep2,'4th'),'4',substring-after($zStep2,'4th'))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep2,'5th')">
                                <xsl:value-of select="concat(substring-before($zStep2,'5th'),'5',substring-after($zStep2,'5th'))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep2,'6th')">
                                <xsl:value-of select="concat(substring-before($zStep2,'6th'),'6',substring-after($zStep2,'6th'))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep2,'7th')">
                                <xsl:value-of select="concat(substring-before($zStep2,'7th'),'7',substring-after($zStep2,'7th'))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep2,'8th')">
                                <xsl:value-of select="concat(substring-before($zStep2,'8th'),'8',substring-after($zStep2,'8th'))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep2,'9th')">
                                <xsl:value-of select="concat(substring-before($zStep2,'9th'),'9',substring-after($zStep2,'9th'))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep2,'0th')">
                                <xsl:value-of select="concat(substring-before($zStep2,'0th'),'0',substring-after($zStep2,'0th'))" />
                            </xsl:when>
                            <xsl:otherwise>
                                <xsl:value-of select="$zStep2" />
                            </xsl:otherwise>
                        </xsl:choose>
                    </xsl:variable>
                    <!-- next step removes the final 'T' character (in the case we received the left-half of an ISO formatted timestamp -->
                    <xsl:variable name="zStep4">
                        <xsl:choose>
                            <xsl:when test="substring($zStep3,string-length($zStep3),1)='t'">
                                <xsl:value-of select="substring($zStep3,1,string-length($zStep3)-1)" />
                            </xsl:when>
                            <xsl:otherwise>
                                <xsl:value-of select="$zStep3" />
                            </xsl:otherwise>
                        </xsl:choose>
                    </xsl:variable>
                    <xsl:variable name="zStep5">
                        <xsl:choose>
                            <!-- completely remove days of the week (this works for abbreviated days like 'Mon' and spelled out days like 'Monday') -->
                            <xsl:when test="contains($zStep4,'mon')">
                                <xsl:value-of select="concat(substring-before($zStep4,'mon'),substring-after(substring-after(concat($zStep4,' '),'mon'),' '))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep4,'tue')">
                                <xsl:value-of select="concat(substring-before($zStep4,'tue'),substring-after(substring-after(concat($zStep4,' '),'tue'),' '))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep4,'wed')">
                                <xsl:value-of select="concat(substring-before($zStep4,'wed'),substring-after(substring-after(concat($zStep4,' '),'wed'),' '))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep4,'thu')">
                                <xsl:value-of select="concat(substring-before($zStep4,'thu'),substring-after(substring-after(concat($zStep4,' '),'thu'),' '))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep4,'fri')">
                                <xsl:value-of select="concat(substring-before($zStep4,'fri'),substring-after(substring-after(concat($zStep4,' '),'fri'),' '))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep4,'sat')">
                                <xsl:value-of select="concat(substring-before($zStep4,'sat'),substring-after(substring-after(concat($zStep4,' '),'sat'),' '))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep4,'sun')">
                                <xsl:value-of select="concat(substring-before($zStep4,'sun'),substring-after(substring-after(concat($zStep4,' '),'sun'),' '))" />
                            </xsl:when>
                            <xsl:otherwise>
                                <xsl:value-of select="$zStep4" />
                            </xsl:otherwise>
                        </xsl:choose>
                    </xsl:variable>
                    <!-- next step normalizes all space characters (eliminates multiples), then converts space characters to '-' characters -->
                    <xsl:variable name="zStep6" select="translate(normalize-space($zStep5),' ','-')" />
                    <!-- before we continue cleaning up the data, we should make note of the position of certain elements (like month and year) -->
                    <xsl:variable name="zFieldDateFormat">
                        <!-- (this section was added in v1.02) This section ATTEMPTS to determine the field's date format based on the actual contents of the cell -->
                        <!-- if the field's date format cannot be determined by examining the data, then the system date format will be used. (This is NOT a great -->
                        <!-- way to determine field formatting, but we have no choice until/unless FileMaker starts exporting data in date fields using ISO date -->
                        <!-- format, or alternately, starts exporting field format codes like 'mmm d, yy' with the metadata of each FMPMXMLEXPORT field/column.) -->
                        <xsl:choose>
                            <xsl:when test="$zKludgeTest='Yes'">
                                <!-- A bug exists in FileMaker timestamp fields (the date portion always gets formatted as m/d/yyyy, regardless of system formats) -->
                                <xsl:text>mdy</xsl:text>
                            </xsl:when>
                            <!-- first six tests look for a spelled out textual month and a 4 digit numeric year (format detection is pretty accurate for these) -->
                            <!-- test for mmm-d-yyyy -->
                            <xsl:when test="string(number(substring-before($zStep6,'-'))) = 'NaN'
                                and string-length(substring-before($zStep6,'-')) &gt; 2
                                and string(number(substring-before(substring-after($zStep6,'-'),'-'))) != 'NaN'
                                and string(number(substring-after(substring-after($zStep6,'-'),'-'))) != 'NaN'
                                and string-length(substring-after(substring-after($zStep6,'-'),'-')) = 4">
                                <xsl:text>mdy</xsl:text>
                            </xsl:when>
                            <!-- test for d-mmm-yyyy -->
                            <xsl:when test="string(number(substring-before($zStep6,'-'))) != 'NaN'
                                and string(number(substring-before(substring-after($zStep6,'-'),'-'))) = 'NaN'
                                and string-length(substring-before(substring-after($zStep6,'-'),'-')) &gt; 2
                                and string(number(substring-after(substring-after($zStep6,'-'),'-'))) != 'NaN'
                                and string-length(substring-after(substring-after($zStep6,'-'),'-')) = 4">
                                <xsl:text>dmy</xsl:text>
                            </xsl:when>
                            <!-- test for yyyy-mmm-d -->
                            <xsl:when test="string-length(substring-before($zStep6,'-')) = 4
                                and string(number(substring($zStep6,1,4))) != 'NaN'
                                and string(number(substring-before(substring-after($zStep6,'-'),'-'))) = 'NaN'
                                and string-length(substring-before(substring-after($zStep6,'-'),'-')) &gt; 2
                                and string(number(substring-after(substring-after($zStep6,'-'),'-'))) != 'NaN'">
                                <xsl:text>ymd</xsl:text>
                            </xsl:when>
                            <!-- test for yyyy-d-mmm -->
                            <xsl:when test="string-length(substring-before($zStep6,'-')) = 4
                                and string(number(substring($zStep6,1,4))) != 'NaN'
                                and string(number(substring-before(substring-after($zStep6,'-'),'-'))) != 'NaN'
                                and string(number(substring-after(substring-after($zStep6,'-'),'-'))) = 'NaN'
                                and string-length(substring-after(substring-after($zStep6,'-'),'-')) &gt; 2">
                                <xsl:text>ydm</xsl:text>
                            </xsl:when>
                            <!-- test for mmm-yyyy-d (unusual format, but we can detect it and rule it out) -->
                            <xsl:when test="string(number(substring-before($zStep6,'-'))) = 'NaN'
                                and string-length(substring-before($zStep6,'-')) &gt; 2
                                and string(number(substring-before(substring-after($zStep6,'-'),'-'))) != 'NaN'
                                and string-length(substring-before(substring-after($zStep6,'-'),'-')) = 4
                                and string(number(substring-after(substring-after($zStep6,'-'),'-'))) != 'NaN'">
                                <xsl:text>myd</xsl:text>
                            </xsl:when>
                            <!-- test for d-yyyy-mmm (unusual format, but we can detect it and rule it out) -->
                            <xsl:when test="string(number(substring-before($zStep6,'-'))) != 'NaN'
                                and string(number(substring-before(substring-after($zStep6,'-'),'-'))) != 'NaN'
                                and string-length(substring-before(substring-after($zStep6,'-'),'-')) = 4
                                and string(number(substring-after(substring-after($zStep6,'-'),'-'))) = 'NaN'
                                and string-length(substring-after(substring-after($zStep6,'-'),'-')) &gt; 2">
                                <xsl:text>dym</xsl:text>
                            </xsl:when>
                            <!-- next four tests look for a spelled out textual month but year and month are 2 digit numbers -->
                            <!-- we are going to assume in this case that year is almost never placed between month and day  -->
                            <!-- test for mmm-d-y -->
                            <xsl:when test="string(number(substring-before($zStep6,'-'))) = 'NaN'
                                and string-length(substring-before($zStep6,'-')) &gt; 2
                                and string(number(substring-before(substring-after($zStep6,'-'),'-'))) != 'NaN'
                                and string(number(substring-after(substring-after($zStep6,'-'),'-'))) != 'NaN'
                                and contains($zSystemDateFormat,'m-d')">
                                <xsl:text>mdy</xsl:text>
                            </xsl:when>
                            <!-- test for d-mmm-y (a lot more common than y-mmm-d) -->
                            <xsl:when test="string(number(substring-before($zStep6,'-'))) != 'NaN'
                                and string(number(substring-before(substring-after($zStep6,'-'),'-'))) = 'NaN'
                                and string-length(substring-before(substring-after($zStep6,'-'),'-')) &gt; 2
                                and string(number(substring-after(substring-after($zStep6,'-'),'-'))) != 'NaN'
                                and contains($zSystemDateFormat,'d-m')">
                                <xsl:text>dmy</xsl:text>
                            </xsl:when>
                            <!-- test for y-mmm-d (quite a bit less common than d-mmm-y) -->
                            <xsl:when test="string(number(substring-before($zStep6,'-'))) != 'NaN'
                                and substring($zSystemDateFormat,1,1)='y'
                                and string(number(substring-before(substring-after($zStep6,'-'),'-'))) = 'NaN'
                                and string-length(substring-before(substring-after($zStep6,'-'),'-')) &gt; 2
                                and string(number(substring-after(substring-after($zStep6,'-'),'-'))) != 'NaN'
                                and contains($zSystemDateFormat,'m-d')">
                                <xsl:text>ymd</xsl:text>
                            </xsl:when>
                            <!-- test for y-d-mmm (unusual format, but we can detect it and rule it out) -->
                            <xsl:when test="string(number(substring-before($zStep6,'-'))) != 'NaN'
                                and string(number(substring-before(substring-after($zStep6,'-'),'-'))) != 'NaN'
                                and string(number(substring-after(substring-after($zStep6,'-'),'-'))) = 'NaN'
                                and string-length(substring-after(substring-after($zStep6,'-'),'-')) &gt; 2
                                and contains($zSystemDateFormat,'d-m')">
                                <xsl:text>ydm</xsl:text>
                            </xsl:when>
                            <!-- next four tests look for a 4 digit numeric year but month and day are 2 digit numbers -->
                            <!-- again, we will assume that year is almost never placed between month and day  -->
                            <!-- test for yyyy-m-d -->
                            <xsl:when test="string-length(substring-before($zStep6,'-')) = 4
                                and string(number(substring-before($zStep6,'-'))) != 'NaN'
                                and string(number(substring-before(substring-after($zStep6,'-'),'-'))) != 'NaN'
                                and string(number(substring-after(substring-after($zStep6,'-'),'-'))) != 'NaN'
                                and contains($zSystemDateFormat,'m-d')">
                                <xsl:text>ymd</xsl:text>
                            </xsl:when>
                            <!-- test for yyyy-d-m (unusual format, but we will test for it anyway if above failed) -->
                            <xsl:when test="string-length(substring-before($zStep6,'-')) = 4
                                and string(number(substring-before($zStep6,'-'))) != 'NaN'
                                and string(number(substring-before(substring-after($zStep6,'-'),'-'))) != 'NaN'
                                and string(number(substring-after(substring-after($zStep6,'-'),'-'))) != 'NaN'
                                and contains($zSystemDateFormat,'d-m')">
                                <xsl:text>ydm</xsl:text>
                            </xsl:when>
                            <!-- test for d-m-yyyy -->
                            <xsl:when test="string(number(substring-before($zStep6,'-'))) != 'NaN'
                                and string(number(substring-before(substring-after($zStep6,'-'),'-'))) != 'NaN'
                                and string(number(substring-after(substring-after($zStep6,'-'),'-'))) != 'NaN'
                                and string-length(substring-after(substring-after($zStep6,'-'),'-')) = 4
                                and contains($zSystemDateFormat,'d-m')">
                                <xsl:text>dmy</xsl:text>
                            </xsl:when>
                            <!-- test for m-d-yyyy -->
                            <xsl:when test="string(number(substring-before($zStep6,'-'))) != 'NaN'
                                and string(number(substring-before(substring-after($zStep6,'-'),'-'))) != 'NaN'
                                and string(number(substring-after(substring-after($zStep6,'-'),'-'))) != 'NaN'
                                and string-length(substring-after(substring-after($zStep6,'-'),'-')) = 4
                                and contains($zSystemDateFormat,'m-d')">
                                <xsl:text>mdy</xsl:text>
                            </xsl:when>
                            <xsl:otherwise>
                                <!-- if the field format is too ambiguous to predict, we will just use the system format -->
                                <xsl:value-of select="$zSystemDateFormat" />
                            </xsl:otherwise>
                        </xsl:choose>
                    </xsl:variable>
                    <!-- next step converts '-' characters back to space characters (and adds a trailing ' ' so we can replace partial or full words, then trim -->
                    <xsl:variable name="zStep7" select="concat(translate($zStep6,'-',' '),' ')" />
                    <xsl:variable name="zStep8">
                        <xsl:choose>
                            <!-- change month names to month numbers (this works for abbreviated months like 'jan' and spelled out months like 'january') -->
                            <xsl:when test="contains($zStep7,'jan')">
                                <xsl:value-of select="concat(substring-before($zStep7,'jan'),'01 ',substring-after(substring-after($zStep7,'jan'),' '))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep7,'feb')">
                                <xsl:value-of select="concat(substring-before($zStep7,'feb'),'02 ',substring-after(substring-after($zStep7,'feb'),' '))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep7,'mar')">
                                <xsl:value-of select="concat(substring-before($zStep7,'mar'),'03 ',substring-after(substring-after($zStep7,'mar'),' '))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep7,'apr')">
                                <xsl:value-of select="concat(substring-before($zStep7,'apr'),'04 ',substring-after(substring-after($zStep7,'apr'),' '))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep7,'may')">
                                <xsl:value-of select="concat(substring-before($zStep7,'may'),'05 ',substring-after(substring-after($zStep7,'may'),' '))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep7,'jun')">
                                <xsl:value-of select="concat(substring-before($zStep7,'jun'),'06 ',substring-after(substring-after($zStep7,'jun'),' '))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep7,'jul')">
                                <xsl:value-of select="concat(substring-before($zStep7,'jul'),'07 ',substring-after(substring-after($zStep7,'jul'),' '))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep7,'aug')">
                                <xsl:value-of select="concat(substring-before($zStep7,'aug'),'08 ',substring-after(substring-after($zStep7,'aug'),' '))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep7,'sep')">
                                <xsl:value-of select="concat(substring-before($zStep7,'sep'),'09 ',substring-after(substring-after($zStep7,'sep'),' '))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep7,'oct')">
                                <xsl:value-of select="concat(substring-before($zStep7,'oct'),'10 ',substring-after(substring-after($zStep7,'oct'),' '))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep7,'nov')">
                                <xsl:value-of select="concat(substring-before($zStep7,'nov'),'11 ',substring-after(substring-after($zStep7,'nov'),' '))" />
                            </xsl:when>
                            <xsl:when test="contains($zStep7,'dec')">
                                <xsl:value-of select="concat(substring-before($zStep7,'dec'),'12 ',substring-after(substring-after($zStep7,'dec'),' '))" />
                            </xsl:when>
                            <xsl:otherwise>
                                <xsl:value-of select="$zStep7" />
                            </xsl:otherwise>
                        </xsl:choose>
                    </xsl:variable>
                    <!-- last time - normalize all space characters (eliminate multiples), and convert space characters back to '-' characters -->
                    <xsl:variable name="zStep9" select="translate(normalize-space($zStep8),' ','-')" />
                    <xsl:variable name="zMonth">
                        <xsl:choose>
                            <xsl:when test="substring($zFieldDateFormat,1,1)='m'">
                                <xsl:value-of select="substring(string(100+number(substring-before($zStep9,'-'))),2)" />
                            </xsl:when>        
                            <xsl:when test="substring($zFieldDateFormat,string-length($zFieldDateFormat),1)='m'">
                                <xsl:value-of select="substring(string(100+number(substring-after(substring-after($zStep9,'-'),'-'))),2)" />
                            </xsl:when>        
                            <xsl:otherwise>
                                <xsl:value-of select="substring(string(100+number(substring-before(substring-after($zStep9,'-'),'-'))),2)" />
                            </xsl:otherwise>
                        </xsl:choose>
                    </xsl:variable>
                    <xsl:variable name="zDay">
                        <xsl:choose>
                            <xsl:when test="substring($zFieldDateFormat,1,1)='d'">
                                <xsl:value-of select="substring(string(100+number(substring-before($zStep9,'-'))),2)" />
                            </xsl:when>        
                            <xsl:when test="substring($zFieldDateFormat,string-length($zFieldDateFormat),1)='d'">
                                <xsl:value-of select="substring(string(100+number(substring-after(substring-after($zStep9,'-'),'-'))),2)" />
                            </xsl:when>        
                            <xsl:otherwise>
                                <xsl:value-of select="substring(string(100+number(substring-before(substring-after($zStep9,'-'),'-'))),2)" />
                            </xsl:otherwise>
                        </xsl:choose>
                    </xsl:variable>
                    <xsl:variable name="zYrPt">
                        <xsl:choose>
                            <xsl:when test="substring($zFieldDateFormat,1,1)='y'">
                                <xsl:value-of select="string(number(substring-before($zStep9,'-')))" />
                            </xsl:when>        
                            <xsl:when test="substring($zFieldDateFormat,string-length($zFieldDateFormat),1)='y'">
                                <xsl:value-of select="string(number(substring-after(substring-after($zStep9,'-'),'-')))" />
                            </xsl:when>        
                            <xsl:otherwise>
                                <xsl:value-of select="string(number(substring-before(substring-after($zStep9,'-'),'-')))" />
                            </xsl:otherwise>
                        </xsl:choose>
                    </xsl:variable>
                    <xsl:variable name="zYear">
                        <xsl:choose>
                            <!-- determine the century using MS-Excel rules. Especially important when the year only has 2 digits -->
                            <xsl:when test="number($zYrPt) &lt; 30">
                                <xsl:value-of select="string(2000+number($zYrPt))" />
                            </xsl:when>
                            <xsl:when test="number($zYrPt) &lt; 100">
                                <xsl:value-of select="string(1900+number($zYrPt))" />
                            </xsl:when>
                            <xsl:otherwise>
                                <xsl:value-of select="substring(string(60000+number($zYrPt)),2)" />
                            </xsl:otherwise>
                        </xsl:choose>
                    </xsl:variable>
                    <xsl:choose>
                        <xsl:when test="string(number($zDay))='NaN' or string(number($zMonth))='NaN'
                                or string(number($zYear))='NaN' or string-length($zYear) != 4
                                or number($zDay) &lt; 1 or number($zDay) &gt; 31
                                or number($zMonth) &lt; 1 or number($zMonth) &gt; 12">
                            <!-- if the input data cannot be formatted as a valid date, then return nothing -->
                            <xsl:text />
                        </xsl:when>
                        <!-- otherwise, return the ISO date with a prefix to show that we were successful -->
                        <xsl:otherwise>
                                <xsl:value-of select="concat('ISO:',$zYear,'-',$zMonth,'-',$zDay,'T00:00:00.000')" />
                        </xsl:otherwise>
                    </xsl:choose>
                </xsl:template>
                
                <xsl:template name="TimeToISO">
                    <!-- This sub-template will attempt to recognize common time formats by recognizing patterns within the data itself. -->
                    <!-- If the time format cannot be detected, then this function returns nothing (an empty value). -->
                    <xsl:param name="zOldTime" />
                    <xsl:variable name="zFixTime1" select="normalize-space(translate($zOldTime,'APBCDEFGHIJKLMNOQRSTUVWXYZbcdefghijklmnoqrstuvwxyz','ap'))" />
                    <xsl:variable name="zFixTime2">
                        <xsl:choose>
                            <!-- if there is an a.m. or p.m., change it to 'a' or 'p' -->
                            <xsl:when test="contains($zFixTime1,'a.m.')">
                                <xsl:value-of select="concat(substring-before($zFixTime1,'a.m.'),'a',substring-after($zFixTime1,'a.m.'))" />
                            </xsl:when>
                            <xsl:when test="contains($zFixTime1,'p.m.')">
                                <xsl:value-of select="concat(substring-before($zFixTime1,'p.m.'),'p',substring-after($zFixTime1,'p.m.'))" />
                            </xsl:when>
                            <xsl:otherwise>
                                <xsl:value-of select="$zFixTime1" />
                            </xsl:otherwise>
                        </xsl:choose>
                    </xsl:variable>
                    <xsl:variable name="TimePart1" select="substring-before($zFixTime2,':')" />
                    <xsl:variable name="TimePart2" select="substring-after($zFixTime2,':')" />
                    <xsl:variable name="TimePart3" select="normalize-space(substring($TimePart2,1,string-length($TimePart2)-1))" />
                    <xsl:variable name="TimePart4" select="substring($TimePart2,string-length($TimePart2),1)" />
                    <xsl:choose>
                        <xsl:when test="$TimePart4='a' and number($TimePart1)=12">
                                <xsl:value-of select="concat('1899-12-31T00:',$TimePart3)" />
                        </xsl:when>
                        <xsl:when test="$TimePart4='a' and number($TimePart1) &lt; 12">
                                <xsl:value-of select="concat('1899-12-31T',substring(string(100+number($TimePart1)),2),':',$TimePart3)" />
                        </xsl:when>
                        <xsl:when test="$TimePart4='p' and number($TimePart1)=12">
                                <xsl:value-of select="concat('1899-12-31T12:',$TimePart3)" />
                        </xsl:when>
                        <xsl:when test="$TimePart4='p' and number($TimePart1) &lt; 12">
                                <xsl:value-of select="concat('1899-12-31T',substring(string(112+number($TimePart1)),2),':',$TimePart3)" />
                        </xsl:when>
                        <xsl:when test="string(number($TimePart4))='NaN' and number($TimePart1) &gt; 12">
                            <xsl:value-of select="concat('1899-12-31T',substring(string(100+number($TimePart1)),2),':',$TimePart3)" />
                        </xsl:when>
                        <xsl:when test="string(number($TimePart1))!='NaN' and string-length($TimePart2) &gt; 1">
                                <xsl:value-of select="concat('1899-12-31T',substring(string(100+number($TimePart1)),2),':',$TimePart2)" />
                        </xsl:when>
                        <xsl:otherwise>
                            <!-- if the input data cannot be formatted as a valid time, then return nothing -->
                            <xsl:text />
                        </xsl:otherwise>
                    </xsl:choose>
                </xsl:template>
                
                <xsl:template name="FixNumber">
                    <!-- This sub-template will attempt to clean-up number formats so they can be recognized as numbers. -->
                    <!-- If the data cannot be converted to a number, then this function returns nothing (an empty value). -->
                    <xsl:param name="zOldNum" />
                    <xsl:choose>
                        <!-- does the number work without any modifications? -->
                        <xsl:when test="string(number($zOldNum))!='Nan'
                            and not(contains($zOldNum,','))
                            and not(contains($zOldNum,'-'))">
                            <xsl:value-of select="number($zOldNum)" />
                        </xsl:when>
                        <xsl:otherwise>
                            <!-- next line removes 4 math operators and 5 currency symbols from the input data -->
                            <xsl:variable name="zNumFix1" select="normalize-space(translate($zOldNum,'-(+)=$€¥¢£','--'))" />
                            <xsl:variable name="zNumFix2">
                                <!-- if we have a number with a trailing '-' character, move it to the front -->
                                <xsl:choose>
                                    <xsl:when test="substring($zNumFix1,string-length($zNumFix1),1)='-'">
                                        <xsl:value-of select="concat('-',substring($zNumFix1,1,string-length($zNumFix1)-1))" />
                                    </xsl:when>
                                    <xsl:otherwise>
                                        <xsl:value-of select="$zNumFix1" />
                                    </xsl:otherwise>
                                </xsl:choose>
                            </xsl:variable>
                            <xsl:choose>
                                <!-- see if the number works now (with the minor modifications we just made) -->
                                <xsl:when test="string(number($zNumFix2))!='NaN'
                                    and not(contains($zNumFix2,','))">
                                    <xsl:value-of select="number($zNumFix2)" />
                                </xsl:when>
                                <!-- first European test: if the user made the choice to use system number formats instead of choosing -->
                                <!-- to copy number formatting from the layout, then any and all separators would have to be considered -->
                                <!-- decimal separators, (because group separators are not included in system formats by default) -->
                                <xsl:when test="contains($zNumFix2,',')
                                    and /fmp:FMPXMLRESULT/fmp:DATABASE/@LAYOUT=''
                                    and string(number(translate($zNumFix2,',','.')))!='NaN'">
                                    <xsl:value-of select="number(translate($zNumFix2,',','.'))" />
                                </xsl:when>
                                <!-- second European test: if ',' appears after '.' like '1.234,50' then convert to XSLT '1234.50' -->
                                <xsl:when test="contains(substring-after($zNumFix2,'.'),',')
                                    and string(number(translate($zNumFix2,',.','.')))!='NaN'">
                                    <xsl:value-of select="number(translate($zNumFix2,',.','.'))" />
                                </xsl:when>
                                <!-- third European test: if '.' appears after '.' like '1.234.567' then convert to XSLT '1234567' -->
                                <xsl:when test="contains(substring-after($zNumFix2,'.'),'.')
                                    and string(number(translate($zNumFix2,'.','')))!='NaN'">
                                    <xsl:value-of select="number(translate($zNumFix2,'.',''))" />
                                </xsl:when>
                                <!-- first Non-European test: if ',' appears before '.' like '1,234.50' then convert to XSLT '1234.50' -->
                                <xsl:when test="contains(substring-after($zNumFix2,','),'.')
                                    and string(number(translate($zNumFix2,',','')))!='NaN'">
                                    <xsl:value-of select="number(translate($zNumFix2,',',''))" />
                                </xsl:when>
                                <!-- second Non-European test: if ',' appears after ',' like '1,234,567' then convert to XSLT '1234567' -->
                                <xsl:when test="contains(substring-after($zNumFix2,','),',')
                                    and string(number(translate($zNumFix2,',','')))!='NaN'">
                                    <xsl:value-of select="number(translate($zNumFix2,',',''))" />
                                </xsl:when>
                                <!-- final test for ',' characters: just get rid of them and see if the number works -->
                                <xsl:when test="contains($zNumFix2,',')
                                    and string(number(translate($zNumFix2,',','')))!='NaN'">
                                    <xsl:value-of select="number(translate($zNumFix2,',',''))" />
                                </xsl:when>
                                <xsl:otherwise>
                                    <!-- if the input data cannot be formatted as a valid number, then return nothing -->
                                    <xsl:text />
                                </xsl:otherwise>
                            </xsl:choose>
                        </xsl:otherwise>
                    </xsl:choose>
                </xsl:template>
                    
                <xsl:template match="fmp:FMPXMLRESULT">
                </xsl:template>
                </xsl:stylesheet>
                
                • 5. Re: Sending Variable through Script and XML Excel Spreadsheet?
                  beverly

                  Thank you, but the XSLT was not needed. The snippet showed where you needed the value.

                  Now I need to know what would export without the XSLT. I know the file has this all scripted. Once you run the script, you should be able to immediately Export (as XML) manually and get the order the same. Try that and do NOT specify the XSLT. Raw xml is what is needed. Open the file in a text editor and copy the parts between the "METADATA" & "/METADATA". This will be a list of the fields (in the order they are exported). Then tell me which field is your global (date) by name.

                  beverly

                  • 6. Re: Sending Variable through Script and XML Excel Spreadsheet?
                    mprythero

                    Ah! I see, sorry about that... again, getting used to what I need to provide and what is what. Anyways, below is the information you had asked for:

                     

                    <METADATA><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="PRO Number" TYPE="NUMBER"/><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Amount Due" TYPE="NUMBER"/><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Notes" TYPE="TEXT"/><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Weekending" TYPE="TEXT"/></METADATA>
                    

                     

                    And my global field is "Weekending". Thank you again so very much! Matt

                    • 7. Re: Sending Variable through Script and XML Excel Spreadsheet?
                      beverly

                      Thank you!

                      OK, I see that all fields are being pushed into columns, but let's address using your global field, first.

                       

                      Line 292 is:

                      <xsl:text>ROADRUNNER WEEK ENDING: </xsl:text><!-- here is where your field would go with the xsl:value-of -->

                      and should be (hard to test, but try this):

                      <xsl:text>ROADRUNNER WEEK ENDING: </xsl:text><xsl:value-of select="//fmp:ROW[1]/fmp:COL[4]/fmp:DATA[1]" />

                      Basically it says to grab every first row/record (there will be only one!) with the fourth column/field, which is the "Weekending" value. See if that works, before adding the next change.

                       

                      Next, we'll change the loop on the Line 328 (for-each).

                      this is what it is:

                      <xsl:for-each select="fmp:COL">

                      I *think* this will work to eliminate the final column:

                      <xsl:for-each select="fmp:COL[position()!= 4]">

                      a way to say for every column except the one that is the fourth one you exported

                      beverly

                      1 of 1 people found this helpful
                      • 8. Re: Sending Variable through Script and XML Excel Spreadsheet?
                        mprythero

                        Thank you Thank you Thank you Thank you!!! I owe you so very much. Thank you so so very much, you have been so incredibly helpful and I am so very grateful for your time and effort to help me!

                        • 9. Re: Sending Variable through Script and XML Excel Spreadsheet?
                          beverly

                          It's the XPath stuff for which they pay me the 'big bucks'. LOL

                          I could have made it more complex, but wanted you to understand it.

                          beverly