Export to Excel with XSL

[fmdataweb]

I'm doing an export of a table with 5 fields to an Excel file with an XSL stylesheet to control the formatting of the Excel data. So far it's working well - I just need to add some additional formatting for a few of the cells. I'm looking for help with examples/syntax for setting the following attributes:

- making a cell bold

- changing the font size of a cell

- making all columns set to auto resize so they fit the contents of each column (same as double clicking the divider between 2 columns)

All fields I'm exporting are text fields.

Thanks,

Steve

[comment]

IIRC, you need to define Styles at the top level of the Workbook and then assign them (by their ID) to individual cells (or rows or columns or tables). WRT column auto-resize, I believe that's a Column attribute. And I am not sure it works with text cells.

These are not really Filemaker-related questions. You can see a very simple example here but you really ought to refer to the official documentation from Microsoft: https://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx 

[beverly]

comment is correct that this is a spreadsheet markup question. However the Export from FileMaker can be done very well with XML and XSLT that maps the fields exported into the elements needed for markup. I do it all the time. There will NOT be a template that one can use to make it any easier, however.

Part of the issue comes from FileMaker where the data exported is not formatted text - the XML is plain text. There IS a work-around using calculated HTML (styled) which Excel can open:

https://filemakerhacks.com/2017/04/04/user-friendly-excel-exports-part-7/

(and previous parts of this article may help as well).

But if you want to export as XML and have the XSLT set the styles, you also have to export a field with the "flag" that says this data should be styled bold or this data should be Verdana 36, for example. Unless it's something like a header row that will always be a particular format - that can be "hard-coded" into the XSLT. It's the dynamic parts that may not be able to caluclate-on-the-fly and require a specific field of data to say what the formatting should be. That can be read in the XML by the XSLT and used in the <ss:Style> for particular Rows, Columns, or Cells.

@fmdataweb what determines a data point to be a particular style?

Beverly

[fmdataweb]

Hi @beverly I was wondering what the best way to specify which data should be bolded - at the moment I just know which parts of the XML I would like to have bolded. For example here's a particular row I would like to be bolded:

<ROW MODID="1" RECORDID="902">
    <COL>
        <DATA>Name</DATA>
    </COL>
    <COL>
        <DATA>ID</DATA>
    </COL>
    <COL>
        <DATA>Category</DATA>
    </COL>
    <COL>
        <DATA>Number</DATA>
    </COL>
    <COL>
        <DATA>Amount</DATA>
    </COL>
</ROW>

I'm open to ideas if there's an easier way to dynamically specify which row/col/data elements should be bold however as I have no real experience with XSLT and XML exports.

[fmdataweb]

Forgot to mention that I can easily flag certain fields/records in the table I'm exporting from if needed to indicate that they should be bolded - just not sure how to incorporate that into this block that processes the rows/fields:

<xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW">
        <Row>
            <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" />

                <Cell ss:StyleID="MyTextStyle1">
                                    <Data ss:Type="String">
                                        <xsl:value-of select="." />
                                    </Data>
                </Cell>
            </xsl:for-each><!-- next column -->
        </Row>
    </xsl:for-each><!-- next row -->

[comment]

35 minutes ago, fmdataweb said:

at the moment I just know which parts of the XML I would like to have bolded.

You need to point your stylesheet to the row you want to be bold (you can style an entire row at once). I am not sure what's the best way to do that, because I don't know which row that will be. An example is not enough: we need a rule that the stylesheet can follow.

 

20 minutes ago, fmdataweb said:

Forgot to mention that I can easily flag certain fields/records in the table I'm exporting from if needed to indicate that they should be bolded

How exactly can you do that?

 

 

Edited August 15, 2017 by comment

[fmdataweb]

5 minutes ago, comment said:

You need to point your stylesheet to the row you want to be bold (you can style an entire row at once). I am not sure what's the best way to do that, because I don't know which row that will be. An example is not enough: we need a rule that the stylesheet can follow.

 

How exactly can you do that?

 

 

I could add a field called 'FlagBold' to the table and if this field has a value of 1 it indicates that the record/row should be bold, otherwise if empty it would be left alone, e.g.

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="FlagBold" TYPE="TEXT"/>

Is that what you're asking here?

(I wouldn't want this FlagBold field to appear in the final XLS spreadsheet file).

[comment]

24 minutes ago, fmdataweb said:

could add a field called 'FlagBold' to the table and if this field has a value of 1 it indicates that the record/row should be bold

Okay, that could work. So suppose this would the 6th field in your field export order, then you could do something like (in words):

• for each Filemaker ROW, create an Excel row;
• if the DATA in COL #6 is 1, add a style attribute.

I don't have time to test this now, but in code it should look roughly like this:

<xsl:for-each select="fmp:RESULTSET/fmp:ROW">
    <Row>
        <xsl:if test="fmp:COL[6]/fmp:DATA = 1">
            <xsl:attribute name="ss:StyleID">MyBoldStyle</xsl:attribute>
        </xsl:if>
        <!-- create cells for the other fields here -->
    </Row>
</xsl:for-each>                    

 

 

 

Edited August 15, 2017 by comment

[fmdataweb]

23 hours ago, comment said:

Okay, that could work. So suppose this would the 6th field in your field export order, then you could do something like (in words):

• for each Filemaker ROW, create an Excel row;
• if the DATA in COL #6 is 1, add a style attribute.

I don't have time to test this now, but in code it should look roughly like this:

<xsl:for-each select="fmp:RESULTSET/fmp:ROW">
    <Row>
        <xsl:if test="fmp:COL[6]/fmp:DATA = 1">
            <xsl:attribute name="ss:StyleID">MyBoldStyle</xsl:attribute>
        </xsl:if>
        <!-- create cells for the other fields here -->
    </Row>
</xsl:for-each>                    

 

 

 

Thanks @beverly I think I'm getting close, but my limited understanding of the XSL syntax is probably holding me back. Here's what I've got so far:

I've got a style as follows:

           <Style ss:ID="MyTitleStyle">
                <Alignment ss:WrapText="0" />
                <Font  ss:Bold="1"/>
            </Style>

and here's the bit that exports the records:

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

            <xsl:if test="fmp:COL[6]/fmp:DATA = 1">
                <xsl:attribute name="ss:StyleID">MyTitleStyle</xsl:attribute>
            </xsl:if>

            <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 ss:StyleID="MyTextStyle1">
                                    <Data ss:Type="String">
                                        <xsl:value-of select="." />
                                    </Data>
                </Cell>
            </xsl:for-each><!-- next column -->
        </Row>
    </xsl:for-each><!-- next row -->

I'm not getting any bold when there is a 1 in the FlagBold field, and this field is also getting exported. Can you please see if you can spot my syntax error and also how to prevent the FlagBold from appearning the in the final spreadsheet file.

Thanks

Edited August 16, 2017 by fmdataweb

[comment]

First of all, my name is not Beverly. I will try and answer this because you are quoting my post and trying to implement my suggestion - but my name is not Beverly.

Next, please check your result in a text editor and verify that the row in question does have a ss:StyleID attribute. If it does, then I guess it's being overridden by the individual cell styles. It's all very nice to apply a style to the entire row at once - but you also need to suppress the cell styles in that row, otherwise it's all in vain.

Finally, in order to remove the flag field from appearing in the output, change this:

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

to:

<xsl:for-each select="fmp:COL[position() &lt; 6]">

 

 

 

Edited August 16, 2017 by comment

[fmdataweb]

23 hours ago, comment said:

First of all, my name is not Beverly. I will try and answer this because you are quoting my post and trying to implement my suggestion - but my name is not Beverly.

Next, please check your result in a text editor and verify that the row in question does have a ss:StyleID attribute. If it does, then I guess it's being overridden by the individual cell styles. It's all very nice to apply a style to the entire row at once - but you also need to suppress the cell styles in that row, otherwise it's all in vain.

Finally, in order to remove the flag field from appearing in the output, change this:

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

to:

<xsl:for-each select="fmp:COL[position() &lt; 6]">

 

 

 

Hi 'comment' - so sorry for getting mixed up here. Thank you very much for your advice - I'll have a crack and see if I can get this working.

[beverly]

You can call me comment if that helps. LOL

You are LOOPing the ROW/records in your XML. That works for 'normal rows' used where needed. However, you are NOT testing to see if the COL/field is your flag field. You don't want it in the "columns", but used as a trigger to make the style as desired.

@comment Got you started with the xsl:If. I might use the xsl:choose, xsl:when, & xsl:otherwise is some places where the "if" is not sufficient.

You are correct that XSLT is a different 'language' and getting it to work can be a challenge sometimes. This can give you a few pointers:

https://www.w3schools.com/xml/xsl_intro.asp

Using XSLT (version 1.0) with XML export (or import) and FileMaker can be narrower than you might find if you just search for how to do XSLT in other applications.