16 Sep 2017: this file (Excel Exports, v7d) fixes a bug where empty fields would cause values to appear in the wrong columns.
29 Mar 2017: this file (Excel Exports, v6d) fixes a formatting bug when currency values < 1000 are not preceded by a “$”. Explanation forthcoming in next week’s “part 7” article.
Recently I was asked to implement user-friendly Excel exports on a WAN-based solution, utilizing some of the techniques explored earlier in this series (part 1, part 1.1, part 2, part 3, part 4 and part 5 — all from five years ago). There was just one problem: while performance was great locally, and okay on a LAN, it was decidedly less-than-satisfactory across a WAN.
Well today we’re going to look at three demos showcasing a brilliant, outside-the-box technique I picked up from Beverly Voth, which is not only significantly faster on a WAN (4x or 5x faster), but also remedies some other shortcomings associated with my earlier approaches.
Demo files:
User Friendliness
If you read any of the earlier articles in this series, you may recall that the user friendliness consists of a) the ability to define virtual fields, and associate them with human-readable pseudo field names…
b) an interface to facilitate the export of the data (including a dwindling value list as described in part 4)…
…and c) displaying those human-friendly column names in the first row of the resulting spreadsheet.
And this hasn’t changed in today’s demos, except, if you’re familiar with any of the earlier articles, you’ll note that column headings in the spreadsheet are now bolded and centered… a welcome improvement over my previous efforts.
Those Darn Column Headings
It would be nice if FileMaker provided a built-in mechanism to customize the “field names” (i.e., the column headings) when generating Excel output, but since this feature does not currently exist in the product, we end up having to fake it one way or another. Previous articles in this series did so by inserting the user-friendly field names into a special header row in a dedicated helper table, appending the records from the source found set to that table, navigating to a dedicated layout, and then doing a “Save As Excel”.
The overhead to accomplish this (importing into a temp table in parts 1-4; loading up a bunch of variables and then rendering them via virtual list in part 5) accounted for much of the WAN slowness mentioned to at the outset.
Is it possible to avoid the above convolutions, and generate the custom column headings directly from the source table (in this case Contacts or Donations)? As a matter of fact, it is… and, contrary to my initial expectations, today’s approach does not involve exporting as XML and transforming the output via XSLT.
Demo #1 (excel exports, v6a)
To generate a spreadsheet, start from either the Contact Export or the Donation Export layout, and click the Export to Excel button.
As we saw in earlier parts of this series, the Contacts and Donations tables have two sets of helper fields: a) ten global fields representing the column headings…
…and b) ten calculated fields (explained in detail in part 2):
These have not changed, but what has changed is the addition of a new helper field, zz_xl_HTML, as well as a global variable, $$headerExcel. The variable is populated at startup…
…and zz_xl_HTML is defined as follows:
The user is prompted for a file name (an xls extension will be appended to the file name if the user does not include it)…
…which is then exported like so:
That’s right… a single field containing artfully-assembled HTML is exported in tab-separated format to a file with an xls extension. [Bev, you are a genius!]
Since it’s not a standard spreadsheet, the user may see a dialog similar to this…
…and assuming they click Yes, the spreadsheet will open like so:
Demo #2 (excel exports, v6b)
This is the same as demo #1, except it occurred to me that I could eliminate these fields entirely…
…by expanding the highlighted section of zz_xl_HTML from this…
…to this:
Note: The preceding utilizes a custom function which you can read about in ValuePosition: The Function FileMaker Forgot.
Demo #3 (excel exports, v6c)
Excel does a great job of detecting text and numbers, and aligning them appropriately, but take a look at column C… shouldn’t that column be left aligned?
Fortunately we can specify the alignment for dates by changing this portion of zz_xl_HTML…
…to this…
…and, voila, problem solved.
Closing Remarks
A huge thank you to Beverly Voth for sharing the technique and answering my many questions.
Update 4 Apr 2017: Make sure to check out part 7 for a numeric formatting bug fix and further refinements to the technique.