16 Sep 2017: this file (Excel Exports, v7d) fixes a bug where empty fields would cause values to appear in the wrong columns.
This is a follow up to last week’s part 6, with three refinements.
- Demo 1 (Excel Exports, v7a) – fixes a numeric formatting bug
- Demo 2 (Excel Exports, v7b) – offers a more flexible Excel export “save” dialog
- Demo 3 (Excel Exports, v7c) – uses ExecuteSQL to reduce the # of TOs on the RG
My goal last week was to produce a spreadsheet that would be well-formatted and require no post-processing… an interesting challenge given that the multiple columns of text, date and number data were being generated from a single text field.
I wasn’t worried about date formatting, because Excel does a good job of rendering the dates correctly (except a bit of inline CSS was necessary to left-justify them).
Additionally, all seemed well with donation amounts formatted as currency thanks to a custom function called CurrencyFormat. Why did I use a custom function? Because it appeared to ensure that Excel would render numbers in this format: #,###.##
Here’s the relevant entry in the Aliases table (note the leading “$” in Output)…
…and I had no complaint about the donation amount formatting…
…until I decided to get rid of the leading “$”…
…and was nonplussed to discover that Excel was now ignoring my formatting instructions in cases where the number was less than 1000 (i.e., small enough to not require a thousands separator).
I opened the XLS file in a text editor and confirmed I hadn’t somehow neglected to include the trailing “.00” on some of the exported values. Nope, they were all there…
…but Excel was making its own decisions re: whether or not to render them.
Well, surely I wasn’t the only person who had encountered this annoyance. A bit of online searching led to this blog posting, with some helpful advice re: defining a CSS class to remedy the problem.
Actually I defined two classes: the first on line 8 above for currency formatting, and the second on line 9 to force a column to be left-aligned (to address the date-alignment issue mentioned above), and added them to the $$excelHeader declaration in my startup script.
The final step was to get rid of the inline CSS in zz_xl_HTML and use classes for both currency formatting and date alignment.
Before (fixes date alignment only):
After (fixes both date alignment and currency formatting):
Does it work? Indeed it does.
This “save” dialog isn’t particularly user-friendly, is it?
As you may recall we are using smoke and mirrors with this export, because we’re not really exporting in Excel format. Instead we assemble HTML in a single field, export it in TAB format, but give the output file an XLS extension (yes, XLS, not XSLX).
We emphatically do not want the user to be able to change the output file type, so we aren’t going to display a standard “export” dialog, but it would be nice if they could enter the name, choose the destination, see a warning if the file already exists, and either overwrite it, or instead give the new file a different name.
Here’s what they see in this demo (with the file name pre-entered)…
…and here’s how it’s done.
- Export a file named donations.xls or contacts.xls to the FileMaker temporary folder
- Insert the freshly-exported file from step 1 into a global container field
- Export field contents from the global container
In demo 3, we use ExecuteSQL to eliminate these two highlighted TOs from the Relationships Graph.
In demos 1 & 2, the highlighted TOs serve a single purpose only: to facilitate the translation of alias “input” to the corresponding alias “output” in this calculation (DTN shown here, but the same, of course, applies to CON).
So let’s remove con_ALI and dtn_ALI from the graph…
…and change the Let declaration in zz_xl_HTML to…
…and, finally, populate the $inputXX and $outputXX variables by adding some new code to the “export to excel” script.
(If you’re curious about the GFN, it’s a custom function that was introduced in ExecuteSQL: Robust Coding, part 1 to prevent SQL code from breaking when a field is renamed, or if the field name contains potentially-troublesome characters such as spaces.)
And I think that’s about enough for today.