FileMakerのデータから、フォーマットされたExcelファイルを大量に生成したいというニーズがあり、調べていました。いくつか方法はあるようですが、Macの場合、これが比較的簡単かと思いました。bBoxはmacOS専用のFileMakerプラグインで、Python・Ruby・PHPなどを使ってFileMakerのスクリプトを拡張し、ワークフローの高度な自動化を支援してくれます。

---

Excel formatting and charts from FileMaker using XlsxWriter

(元記事はこちら)

Simon Brown
2016/4/9

FileMakerである程度の期間にわたってソリューションを開発していれば、FileMakerの標準機能のExcelエクスポートを使用してファイルを作成したことがあるはずです。この機能はとても便利ではありますが、フォーマット関連はほとんど制御できず、計算式、グラフ、テキスト書式を設定する方法がありません。

しかし、PythonベースのXlsxWriterライブラリとbBoxを使えば、これをあなたのソリューションで簡単に実現できます。

作業を始める前に、一度だけ行わなくてはいけないステップがあります。MacOSではほとんどのPythonライブラリが標準でインストールされていますが、XlsxWriterモジュールは含まれていません。そこでこのライブラリをターミナルからインストールします：

sudo easy_install XlsxWriter

これで作業を進めることができます。最初の例では、ワークブックファイル内にワークシートを作成し、ヘッダ部分のテキストに書式を設定しています。デフォルトの列幅はかなり狭いため(約8文字)、カテゴリおよびサンプルの名前がすべて表示できる幅に設定します。次に、FileMakerのEXAMPLEテーブルを検索し、その結果をワークシートに流し込みます。最後に、追加したデータを集計する計算式を追加します。

なお、以下の2つのサンプルスクリプトはbBoxプラグインのダウンロードファイルに含まれています 。

import fm,os,xlsxwriter
 
# Excelファイルを書き出す場所を設定
os.chdir ('/tmp')
workbook = xlsxwriter.Workbook('_Examples.xlsx')
worksheet = workbook.add_worksheet()
 
# ボールドの書式設定
bold14 = workbook.add_format({'bold': True, 'font_size': 14})
 
# ヘッダを設定
worksheet.write('A1', 'Category', bold14)
worksheet.write('B1', 'Name', bold14)
 
# デフォルトの列幅はせまいので
worksheet.set_column(0, 0, 20)
worksheet.set_column(1, 1, 40)
 
# FileMakerのEXAMPLEテーブルからサンプルデータを取得
examples = fm.executesql ("SELECT category,name FROM EXAMPLE ORDER BY category,name")
 
# ヘッダの次の行から開始
row = 1
col = 0
 
# データをループしてExcel行に書き出し
for category, name in (examples):
    worksheet.write(row, col, category)
    worksheet.write(row, col + 1, name)
    row += 1
 
# 計算式
rangeTxt = 'A2:A' + str(row)
worksheet.write(row, 0, 'Categories', bold14)
worksheet.write(row, 1, '=SUMPRODUCT(1/COUNTIF(' + rangeTxt + ',' + rangeTxt + '))', bold14)
 
row += 1
worksheet.write(row, 0, 'Examples', bold14)
worksheet.write(row, 1, row-3, bold14)
 
workbook.close()

 

Excelでドキュメントを開き、作成した行の最後までスキップすると、書式設定されたテキストと計算された値が表示されています。

さらに進めて、次はグラフを作成します。

import fm,os,xlsxwriter

# /tmpフォルダにワークブックファイルを作成しシートを追加
os.chdir ('/tmp')
workbook = xlsxwriter.Workbook('_Examples.xlsx')
worksheet = workbook.add_worksheet()

bold14 = workbook.add_format({'bold': True, 'font_size': 14})
worksheet.write('A1', 'Category', bold14)
worksheet.write('B1', 'Count', bold14)

worksheet.set_column(0, 0, 30)

# FileMakerのEXAMPLEテーブルからサンプルデータを取得
examples = fm.executesql ('SELECT DISTINCT category, COUNT (DISTINCT name) FROM EXAMPLE GROUP BY category')

row = 1
col = 0
exampleLastRowStr = str (len (examples)+row)

for category, count in (examples):
worksheet.write(row, col, category)
worksheet.write(row, col + 1, count)
row += 1# Create a new Chart object.

chart = workbook.add_chart({'type': 'doughnut'})
chart.set_size({'width': 700, 'height': 680})

# データ系列に色を設定
chart.add_series({
'name': 'Example',
'categories': '=Sheet1!$A$2:$A$' + exampleLastRowStr,
'values': '=Sheet1!$B$2:$B$' + exampleLastRowStr,
'points': [
{'fill': {'color': '#000000'}},
{'fill': {'color': '#00FF00'}},
{'fill': {'color': '#0000FF'}},
{'fill': {'color': '#FF0000'}},
{'fill': {'color': '#01FFFE'}},
{'fill': {'color': '#FFA6FE'}},
{'fill': {'color': '#FFDB66'}},
{'fill': {'color': '#006401'}},
{'fill': {'color': '#010067'}},
{'fill': {'color': '#95003A'}},
{'fill': {'color': '#007DB5'}},
{'fill': {'color': '#FF00F6'}},
{'fill': {'color': '#FFEEE8'}},
{'fill': {'color': '#774D00'}},
{'fill': {'color': '#90FB92'}},
{'fill': {'color': '#0076FF'}},
{'fill': {'color': '#D5FF00'}},
{'fill': {'color': '#FF937E'}},
{'fill': {'color': '#6A826C'}},
{'fill': {'color': '#FF029D'}},
{'fill': {'color': '#FE8900'}},
{'fill': {'color': '#7A4782'}},
{'fill': {'color': '#7E2DD2'}},
{'fill': {'color': '#85A900'}},
{'fill': {'color': '#FF0056'}},
{'fill': {'color': '#A42400'}},
],
})

# タイトル、グラフ形式を設定(白枠と影)
chart.set_title({'name': 'Category Counts'})
chart.set_style(10)

# グラフをシートに挿入
worksheet.insert_chart('C2', chart, {'x_offset': 10, 'y_offset': 10})

workbook.close()

 

結果の例を以下に示します。

XlsxWriterモジュールのインストールは極めて簡単ですが、端末数が多く個別のインストールが手間という場合は、サーバーサイドで実行する方法もあります。レポートを電子メールで送信する場合は、一度Excelファイルをサーバ上の /Library/FileMaker Server/Data/Documents あるいはスクリプトセッションのTempフォルダに書き込んでから電子メールに添付します。メールを使わない方法として、ファイルをオブジェクトフィールドにインポートして、ユーザにローカルに保存させることもできます。もう一つの方法(試してはいないのですが)としては、XlsxWriterモジュールをフォルダごとまとめてオブジェクトフィールドに格納し、必要に応じてローカルファイルシステムにエクスポートさせます。それによって事前のインストールの手間を省きます。

私がまだ苦労しているのはカラーパレットです。グラフの隣り合う色を似ていながら区別できるようにしたいのですが、色は少なくとも25色あります。プログラムでRGB値のリストを簡単に生成できればいいのですが。

このサンプルを含むbBoxソフトウェアをダウンロード