Excel Exports using XML and XSLT
All FileMaker developers spend time figuring out how to export FileMaker data in a usable format for their clients. Kevin Frank had a series of posts on just that subject (link in the next paragraph below). There is way to export data from FileMaker using XML and XSLT, but it’s not easy nor intuitive. It is powerful, though.
Kudos to Beverly Voth for tackling – and taming – such a difficult subject, and to Kevin Frank for letting Beverly post on his great site, FileMakerHacks.com. So what does this post by Beverly talk about? Exporting data from FileMaker to Excel using XML and XSLT:
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”
It’s a bit of a long read, but worth the effort if you need (or want) to export Excel data in a much more usable format for your clients. If you don’t have time now, scan it quickly, download the example file, and bookmark it for later. When that call comes wanting better exports from FileMaker to Excel, you’ll be ready to handle it.
Here’s what you’ll find
Among other things, it includes a free downloadable example file and instructions on using Microsoft’s Excel Markup Language (XML) to format the .xlsx spreadsheet while exporting. So why would you want to do that? Let’s hear what Beverly has to say:
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.
Add multiple worksheets, grouped data with summaries, styling formulas, and more – depending upon how deep you want to dive into the XML code, of course.
And there’s a section on using a repeating field in FileMaker to make custom headers in your Excel report!