Site icon FileMakerHacks

Excel Exports using XML and XSLT

beverlyvoth

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

FMPXMLRESULT Minimal Outline

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:

Export Order

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:

Use XSL style sheet Option added

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:

Snippet of the xml2CSV XSLT

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):

Enter static text for headers

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:

Use XSL style sheet with headers option

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.

Setting up a global repeating field

And include this field in your export order:

Field export order with new field

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.

Enter custom headers in the repeating field in the popover

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:

Simple Outline of Excel XML

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

(snippet of the full document)

The most notable changes:

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 in Excel Markup Language

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.

XSLT “Case()” as CHOOSE, WHEN, & OTHERWISE

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.

Excel Worksheet Options

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.

Additions to Original Demo

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

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!

Exit mobile version