Excel formatting and charts from FileMaker using XlsxWriter

If you’ve developed solutions with FileMaker for any length of time, you’ve almost certainly created files using FileMaker’s native Excel exports. Although quite useful, this feature gives almost no control over the formatting, and definitely no way to set formulas, charts, or even text formatting.

However, the Python based XlsxWriter library and bBox can provide an easy way to add this to your solution.

Before we can start, there is a one-time step we must perform. Although quite a few Python libraries are standard in Mac OS, the XlsxWriter module is not, so we must first install the library via a Terminal command:

sudo easy_install XlsxWriter

With that done, we’re now ready to go. The first example shows how we can create a worksheet within a workbook file, then add some text formatting for the header. Since the default column widths are fairly narrow (about 8 characters wide), we set the column width to be wide enough to display all the category and example names. Then, we query the FileMaker EXAMPLE table for a list of these, and pump that in to the worksheet. Finally, we add a couple of formulas to summarize the data we’ve added.

FYI, versions of this and the next example are ready to go in the bBox Plug-in download file.

After opening the document in Excel and skipping to the end of the rows we created, you can see that both the formatted text and our calculated values displayed.

Spreadsheet table with formulas & formatting
Spreadsheet table with formulas & formatting

We can take things up a notch further by creating a graph.

And here’s an example of the results:

Spreadsheet table with Chart
Excel table with chart

Although installing the XlsxWriter module is dead easy, for a large deployment it could be enough of a barrier that you’d only want to do this server-side for a solution. If the reports were going to be emailed out, you can simply attach them to an email by first writing your Excel files out to /Library/FileMaker Server/Data/Documents or the script session’s Temp folder. Alternately, if not emailing them, import the file into a container field and save the file locally. A more speculative option, which I haven’t tried yet, could be bundling the XlsxWrite module up by storing its folder in a container field, and exporting the module to the local file system whenever needed, foregoing the need to first install the module.

One thing I’m still playing around with is the color palette. I’d like something where each neighboring color is similar but distinct, and where there are at least 25 different colors. Even better if I can easily generate the list of RGB values programmatically.

Simon

Download the bBox software including this example.

Leave a Reply