User-Friendly Excel Exports, part 7

This is a follow up to last week’s part 6, with three refinements.

Demo #1

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

2017-04-03_172905

…and I had no complaint about the donation amount formatting…

2017-04-03_180449

…until I decided to get rid of the leading “$”…

2017-04-03_181135

…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).

2017-04-03_181708

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…

2017-04-03_182224

…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.

2017-03-29_032803

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.

Demo #2

This “save” dialog isn’t particularly user-friendly, is it?

2017-03-26_220006

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.

Explanation:

  1. Export a file named donations.xls or contacts.xls to the FileMaker temporary folder
  2. Insert the freshly-exported file from step 1 into a global container field
  3. Export field contents from the global container

Demo #3

In demo 3, we use ExecuteSQL to eliminate these two highlighted TOs from the Relationships Graph.

2017-04-06_094148

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

2017-04-04_094635

So let’s remove con_ALI and dtn_ALI from the graph…

2017-04-04_091955

…and change the Let declaration in zz_xl_HTML to…

2017-04-04_094666

…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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s