Excel Exports using XML and XSLT

Editor’s note: Once again it is an honor and a privilege to present an in-depth guest article written by Beverly Voth.

This is a follow-up to the User-Friendly Excel Exports series by Kevin Frank, but using an XML export along with various XSLT to give you .csv or .xls (for Excel). The first demo is a quick export (to CSV – comma-separated values) using an XSLT to help explain the relationship with FileMaker Pro XML export. How an XSLT style sheet reads that data to transform it into another text format (.csv or .xls) is also explained throughout the article and in comments within the XSLT. The demos will get increasingly more complex, but ultimately more “user-friendly”.

Demo file: Excel-Exports-using-XML-and-XSLT.zip

We’ll use the demo files from Kevin with revisions to use the XSLT from these demos. Open this or any database and select Export Records from the File menu and choose XML as the type. Name it “raw_sample.xml”. In the next dialog, “Specify XML and XSL Options”, use FMPXMLRESULT as the Grammar and uncheck the “Use XSL style sheet”. You can view the file in any text editor or in any browser. Some browsers require you to view the source. Some browsers will “pretty-print” (indent) the XML document with a default style sheet. The XML is valid even if it is not indented for human viewing.

Demo 1 – XML to simple CSV

For our demos, the export as XML from FileMaker Pro, we will use the grammar called FMPXMLRESULT. There is another grammar, FMPDSORESULT, and just for comparison, you might test Export to see what that looks like. Excel cannot directly open either format and auto-magically be a spreadsheet. You may find the FMPXMLRESULT grammar is somewhat similar in structure to the xml that Excel uses. The link to the full grammar is here:

FileMaker Help FMPXMLRESULT Grammar, https://fmhelp.filemaker.com/help/16/fmp/en/#page/FMP_Help/xml-format.html

Using the basic outline above, notice how there is a FIELD that corresponds to every COL in every row (in the same relative position). A ROW will be repeated for every record that you export. The Export order (as in your dialog) will be the same order (or position) that appears in the XML document. Pay close attention to the “Apply current layout’s data formatting to exported data”. This should be checked for best results:

While Excel may have a similar header row & data row(s), it cannot open the FMPXMLRESULT as a raw export. So we apply an XSLT in the export dialog:

When you click the “Specify” button next to the File radio selection, you use the standard OS navigation to find the file. Ignore the HTTP request option for these demos. These style sheets will automatically download with the different types of demos (details on this below). You can also download them separately to your desktop to be used and viewed anywhere.

xml2CSV.xsl

The XSLT is XML with special commands/tags and functions. The full file can be downloaded from the linked demo database and viewed in the browser or text editor:

Notice the xsl:output attribute method=”text”. The XSLT style sheets that will be creating CSV “text” will use this method. When we get to an XLS (Excel) style sheet for use with the export, we will be using method=”xml”. There are actually three methods that can be used with the transformation, including the two just mentioned as well as method=”html”. These are all a text-format but will produce different results. Keep that in mind if you are creating your own XSLT.

This XSLT loops through the METADATA/FIELDs for the column header and for the ROWs/COLs for all the records and columns (in an inner loop). Every column gets double-quoted regardless of field type. We could test for the TYPE in the corresponding METADATA/FIELD and apply double-quotes for all types except numbers. But we are trying to simplify here to move on to the more complex later.

A tip about the XSLT “command/tags” used. You can learn more about XSL/XSLT and XPath functions and commands here: W3Schools XSLT Introduction. Somewhat like FileMaker Pro functions and scripting, you can have logic and loops and text functions act upon the XML source. XPath is used to get the “location” in the source XML. Relative and absolute paths may be used in the demos provided here. The comments inline inside each of the XSLT will also help you understand what command/tag and/or function is used and why. https://www.w3schools.com/

Demo 2 – XML to simple CSV with static headers

Demo 1, of course, is no better than the standard export as Comma-separated values (or .mer with the header row). You do get all the field names (including related table names). This does NOT solve our problem with “custom header”. We can revise the XSLT to contain static values instead of a loop call to the FIELDs in METADATA.

xml2CSV_headers.xsl

(Only this part changes — download the full file from the example database):

Keep in mind that every time there is a new export with different fields or export order, this static text needs to change in the XSLT. There are ways to pass a field with these values, but again we are trying to move to more complex demos. Also remember to choose this new style sheet in the export dialog:

Demo 3 – XML to .csv Using Repeating Field for Custom Header

Are you excited? We will export a new field (text) with repeating values for this demo. Yes! You heard that correctly. Repeating and Related fields will export with a single COL, followed by DATA for every repeat or related child record.

And include this field in your export order:

In the demo on the Donations layout, I’ve included a  Popover Button “Custom Headers”. When this is clicked, the Popover will allow you to set the values for your custom values in this field.

When exported to the raw XML the METADATA/FIELD will show this field as:

Each ROW will show one COL, but many DATA elements:

Why use a repeating field here instead of other field types or related fields? The Repeating fields exported as XML will give you one DATA for every repeat (as shown on the layout), even empty ones:

Related fields exported as XML will give you only as many children DATA as there are records in the portal. Empty portals (or any empty related single field) will return empty:

or Included in the demo download is a file called “related_export_sample.xml”. This shows an example from a simple ORDERS database with related Customers and related Items (for the order). This Sample shows what is returned in the raw XML export if any related field (parent or child) has no DATA.

We can leverage the METADATA/FIELD attribute MAXREPEAT to set some variables and use a test to only process the non-empty repeats. To make it simple we will just use one of those wonderful functions to count the number of fields we exported and presume that one has the MAXREPEAT. And we will presume that you followed the above instructions by placing this global repeating field at the bottom of the export order.

xml2CSV_custom.xsl

Even XSLT has variables that can be used throughout for processing. We set some of these for the first demo as the “constants” for carriage return and double-quote characters.  Those variables were “global” to be used anywhere in the XSLT. The “fld_count” relies on starting to read the XML source, so occurs after the xsl:template match=”/” in the XSLT.  Like FileMaker Pro variables, a little “context” may be needed. Any variable set inside a ROW loop, for example, will change for every record it finds. Notice that variable names in XSLT are case-sensitive (unlike FileMaker variables) and are called with the “$” prefix. This demo has more things in common with the first demo (dynamic loops to get the column header values). The second demo may be less complex, but requires more manual entry. Some notable differences:

select="count(//fmp:FIELD)"

1. A variable is set based on the number of columns we are exporting.
2. This will be used other places to limit the number of columns in the header row and limit the columns in each row. We do not want this “header column” to be used in itself or as a column of data.

1. This actually replaces our less complex for-each loop of the FIELD elements in the first demo.
2. The absolute path to the first ROW (the bracketed “1”) is because we only need these values once. The global field will actually be duplicated in every ROW and we cannot prevent that. But we can limit what we use.
3. The next part of the path: COL[$fld_count], uses that variable we set above. We only want the column that matches the number of fields we exported. The function last() would also have worked here, as it is the last column/field in the export order. For example, this would then be: COL[last()].
4. Now we will limit the number of DATA elements returned to less than the number of columns. Hopefully you entered the same number of values in the repeating field as the columns headers you desired. DATA[position() & lt ; $fld_count] means to give me every DATA element in that COL in the first ROW, but only if its position is less than the number of columns. Because “<” is a reserved character in XML & XSLT, we use the encoded version for the comparison (see the screenshot and XSLT file).
5. All of the above is XPath and understanding it better is what makes it easier to create XSLT.

“User-friendly” for the export means more work for the developer at the beginning. Once the XSLT is created, it should be able to be used with any table export as long as the final field is the repeating field. The rest is all dynamically determined by processing the XML with the XSLT upon export.

Demo 4 – XML to .xls (EXCEL) Using Stylesheet Markup Language

Microsoft Office has a set of schema (grammar) documents that define how an XML can be imported into it. We’ll concentrate on the Spreadsheet ML for this demo.

Wikipedia – Office XML, https://en.wikipedia.org/wiki/Microsoft_Office_XML_formats

Here is a simple example; see the links at the end of this article for more information on this markup language:

Do you see some of the similarities with the FMPXMLRESULT grammar? We have a way to specify the Data Type (such as String, Number, Date), but will only be using String for simplicity. We can use this as a basis in our XSLT to export to this format. The document can only be saved as the text extension “.xls” as the “.xlsx” is a compressed format not possible with this method. There are row limits with .xls:

References: http://www.excel-exercise.com/xls-xlsx-file/ & https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_mac-mso_mac2011/what-is-the-real-difference-between-a-xls-vs-xlsx/c7a9d641-d6cf-485d-81d8-1bee60bb17d0?auth=1

xml2Excel.xsl

The most notable changes:

• The filename has the .xls extension even though this is an XML type of export
  
• The xsl:output method=”xml” and has the media-type attribute
• An xsl:processing-instruction is used to set the file to be of type Excel
• The necessary Excel tags are used, including the namespace declarations
• This export uses two rows for the header to show the difference with the loop and the static values. Choose your preference and comment out or delete the other.
  

This is the most basic of Excel documents and should open directly in Excel. The file may need to be opened by choosing Open with Excel File menu. The file may then be saved as .xslx as desired.

This particular demo is more like Demo 1 & Demo 2. The first header row uses the field names and the second header row use static values. The main purpose of this demo is to show how this Excel markup language in the XSLT differs from using an export that become CSV.

Demo 5 – XML to Excel with Custom Header (on export)

So, why would we use the Excel Markup Language (XML) to make these kinds of exports? One answer has several parts: Styling, Freeze Panes, multiple worksheets, formulas and more. These are all possible with more complex XSLT (and even with values exported from FileMaker). Our export could be grouped (via the XSLT) to show summaries by Organization or Payment Type, for example. This demo will just use the Styling (for the header row) and Freeze Panes (to keep the header row at the top as you scroll the remaining rows). We will also add back the export of the repeating field for our header columns.

xml2Excel_custom.xsl

This XSLT is a revision of Demo 4 (a simple Excel export) and some of the tricks in Demo 3, such as the dynamic header only (using the global field). This demo also has added styles. The Excel ML has some tags we can add, called STYLES and STYLE (one for each style needed including a default). If you ever create an Excel spreadsheet, Save As with an “XML” Format, and open in a text editor, you will see these automatically defined and assigned to rows or cells.

Styles are defined after the “Workbook” root element, but before the “Worksheet” element in the XSLT. Each Style has an “ss:ID” attribute and an “ss:Name” attribute. These should be unique within the Styles group. A Style with ss:ID=”Default” ss:Name=”Normal” is used for every row & cell without a specific ss:Style. Within each Style are other options, such as ss:Color as a Hex color value or Font ss:Size & ss:Bold=”1″. http://htmlcolorcodes.com/

While we did not format the Amount & Date columns for this demo, these styles could be defined and then dynamically applied to cells based on the FileMaker Pro XML source FIELD TYPE attributes. Compare the CSV exports where we had the columns as double-quoted strings, but they still get right justified when opened in Excel. Excel formatting can get quite complex.

These defined styles have been used in the header row:
Row ss:StyleID=”hdr”
and in the cells in the first and last columns (Date and Amount):
Cell ss:StyleID=”colRt”

We used another XSLT/XPath method similar to the FileMaker Pro Case() function. In XSLT this uses: xsl:choose, xsl:when, & xsl:otherwise. Each xsl:when is similar to xsl:if and needs a “true test”. This demo is testing for the first and last columns (Date and amount):
xsl:when test=”position()=1″
xsl:when test=”position()=last()”
…and columns failing either test get the Cell with no ss:StyleID. This is the ‘default’ defined in the xsl:otherwise. Details are in the downloadable XSLT for this demo.

An added bonus is the Freeze Panes option in Excel ML. This is defined in the WorksheetOptions section of the XSLT, occurring just before the close of the Worksheet tag. These options apply to each worksheet in a workbook, so can be different for each one.

The Freeze Panes is set up manually in Excel, by clicking in a row just below the header (or other row you want to “stick”). Then select “Freeze Panes” under the “Windows” menu. If you have a wide spreadsheet and want to freeze a column (or two), that can also be done (with or without the header pane).

Download and study this XSLT. It’s still very simplified showing what can be done with styling and the Freeze Pane feature of Excel! The Donations layout of the demo has a selection drop-down to run the exports (with XSLT) or just download the XSLT or other sample XML.

The “Custom Headers” button is the Popover to add/edit the custom headers. The “Select an XML Report” is a drop down to choose a report or raw assets document export. Once selected, click the “Donation XML Export” button.

Also view the Script used by this final button, “export to xml” to see some tricks with variables to make the export dynamic based on the report selection. Comments in this script and within the XSLT can provide more information that may not be directly noted in this article.

Tips and Troubleshooting

• The FMPDSORESULT grammar can be used for Export (as XML) from FileMaker Pro. XSLT can also be used to transform it into the .csv and .xls needed for Excel. The XPath would be different for getting the desired values. While FMPDSORESULT is still an available grammar for export, you may see this at the top of the document in a comment: This grammar has been deprecated – use FMPXMLRESULT instead 
  The biggest reason to use FMPXMLRESULT grammar, is that it is the only grammar that can be used for Import (XML) into FileMaker Pro. It may be better to learn its format for more generic and dynamic XSLT creation.
• If you get an error dialog (when using XSLT with XML for Export or Import) it may not be large enough to get all the details, but pay attention to text with “Line: xx”. This will give you a clue where the error may be in your XSLT. Make a screenshot of the error message before dismissing for further investigation.
• Use an XML editor that allows you to create and test your XSLT outside FileMaker Pro.
• A method used in this demo for “storing” the downloadable XSLT and other sample XML is to place them as TEXT objects on the Layout. Once they are pasted there (in Layout Mode), they are sized smaller and named in the Inspector. These kinds of objects can be accessed with the FileMaker Pro function, “GetLayoutObjectAttribute()“. Here we are also applying Base64 to encode and decode. This method allows us to store the text into a variable but as a container.
• See the script “export to xml” in the demo file for how these “layout objects” are used.
• If you have FileMaker Pro 16, you can use Export Field Contents script step, the TextEncode() function & Insert Text (into a variable) script step for some of this storage and output.
• In FileMaker Pro 16, you can use a Card Window instead of the popover used for this demo. This would allow you to enter the values for the custom headers.
• Do you like a blank first column? Add a global field and make it’s “header” also blank. This will show in every ROW with the empty value. Test for COL[1] as needed to use the default Style.
• In addition, related field records (one field) would work, as the XSLT & XPath is the same. Just be careful to have the correct (non-blank) number of related records for the header text labels. These need to be in the proper sort order as well to match each header with each column. (This is a use case where the Repeating field is actually good for temporary storage, as global, and a part of this kind of export.)
• Formulas are not defined in any of these demos, but they are possible for row total, column subtotals and totals. More advanced XSLT can even group by any of the columns to make subheaders or even separate worksheets within a workbook. Column and row “spans” are even possible if desired.
• These references on the Excel ML may help with more details:
  XML Spreadsheet Reference, http://msdn.microsoft.com/en-us/library/aa140066(office.10,loband).aspx
  Dive into SpreadsheetML (parts 1 & 2), http://msdn.microsoft.com/en-us/library/bb226687(office.11).aspx,
  http://msdn.microsoft.com/en-us/library/bb226693(office.11).aspx
• See these articles for more on the Export Field Contents script step:
  An In-Depth Look at “Export Field Contents”, https://filemakerhacks.com/2012/09/23/export-field-contents-as-utf-8/
  Export Field Contents (Update for FM 16), https://filemakerhacks.com/2017/05/09/export-field-contents-update-for-fm-16/
• Error dialog when importing raw XML export from FileMaker Pro into Excel:

TIP: use this “feature” to view your TEXT output of XML or XSLT. You will get a row for every “return” in the XML/XSLT document when viewed this way!