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.
Have used this Excel export feature with great success for several years, this is indeed a clever expansion on the idea.
Rather than the warning message displayed during the export script you could capture the user’s requested file name, set it to a variable and attempt to import the file into a global container. If successful then the file already exists, if so append “copy” to the requested file name before saving. Depending on your level of FileMaker paranoia you can loop this step one or more times to make sure there’s no file with the file name appended with “copy”, or “copy copy” etc that already exists.
Thanks as ever for stretching our minds!
Hi Simon,
Excellent suggestion.
Regards,
Kevin
Since you already mentioned and used the MBS-Plugin in the story of embedded links in PDFs, this is the perfect tool to create excel-sheets in combination with the Lib.XL (yes also needs licensing). If you need oftenexcel-export which are not only carring the data but is also directly usable for users, it is perfect.
You have to practice to understand the logic of all the possibilities, but then it works smooth.
The whole excel-file is created in memory and in the end put into container or saved as file.
It is very, very fast, can be done on Server to create the file in container which then can be processed from client over WAN.
The possibilities are not restricted, so everything excel can do, can be done with the plugin (szies of rows, columns, fonts, fontsize, color of text, backgroun, frame etc), setting print area, setting headers, footers for printing, auto-optimized coloumn-width, even formulas can be set, so the sheet is able co calculate when user changes cells etc etc.
I am using it to create user-ready und usable sheets, reday for printing, editing or entering data which will be processed with calculations and so on.
And there is XSLT or PHP – so many ways to get Excel from FileMaker. :)
Not sure I like the idea of having the user see that warning. If we wanted to send this file to a client which would be the most frequent use, that would be a deal breaker.
Hi Dan,
I did some research while writing the article and that warning is not easy to suppress. A work around would be to save the spreadsheet in proper Excel format after opening it, but I agree the technique would be smoother if one could generate a true spreadsheet, not HTML masquerading as a spreadsheet.
Regards,
Kevin
On the Macintosh, when the (strangely worded) dialog appears, one has the option to disable future warnings.
I would not send the file to a client. I would open in Excel and save “Properly”. The article/example is to make an “easy” export to html which Excel can open.
If you only want to customise column headings in the exported file only you can do so with a few script lines. On Mac OS you can fire Apple scripts directly from within a FM script. On Windows you can use MBS plugin for scripting (never tested this myself though).
Yep, there are many ways to skin the cat… the techniques that have been explored in this series (so far, at any rate) don’t require any outside technologies and work cross platform.