サンプルの概要
Excelとの連携部分にフォーカスするため、極力シンプルに設計します。
テーブル「売上DB」
・売上日（日付）
・本支店（テキスト）
・売上金額（数字）
以上、フィールド３つのみ。
小売店を想定し、１レコードがレシート１枚、「本支店」は売った店名、「売上金額」はそのレシートの合計金額です。
実際は売上票コードをキーにしてぶら下がった「細目」テーブルに個々の商品の情報があったり、「商品マスタ」や「顧客マスタ」と連携しているなど、各自想像してください。
これに乱数を使ったスクリプトを回し、2017/1/1〜2017/1/31を対象期間として３万レコードぐらいのダミーデータを作成しました。
今回はここから２種類の月報シートをExcelファイルとして作ることを目標とします。
作戦会議
まず、FileMakerからどうやってExcelのファイルを出力するのか。そこから考えます。
FileMakerが直接.xlsxファイルを吐き出すとか、FileMakerがエクセルを直接操作するような仕組みは、ちょっとハードル高そうです。
Excelの処理を自動化といえば、やっぱマクロ（Excel VBA）ですよね。
FileMakerでVBA言語のコードをテキスト形式で書いたり、FileMakerからExcelへ出力したいデータを渡して、作表整形する部分をExcel VBAで書くとか、いくつか方法があると思います。それもありっちゃありですが、作るソリューションごとにその都度専用の設計開発をしなければなりません。
そこで、FileMakerからはもっとシンプルな形でワークシートの作成手順を記述することにして、Excel VBA側でそれを解釈して動作するようなものを作ったら、いろいろ使い回しも効きそうだし、これはいけるんじゃないかと。
つまりは
Excelの処理を自動化といえば、やっぱマクロ（Excel VBA）ですよね。
FileMakerでVBA言語のコードをテキスト形式で書いたり、FileMakerからExcelへ出力したいデータを渡して、作表整形する部分をExcel VBAで書くとか、いくつか方法があると思います。それもありっちゃありですが、作るソリューションごとにその都度専用の設計開発をしなければなりません。
そこで、FileMakerからはもっとシンプルな形でワークシートの作成手順を記述することにして、Excel VBA側でそれを解釈して動作するようなものを作ったら、いろいろ使い回しも効きそうだし、これはいけるんじゃないかと。
つまりは
Excelマークアップ言語を開発じゃ！
大風呂敷を広げてしまいましたが、そんな大層なことはありません。いま自分に必要な最低限の機能だけ実現すればいいのです。
今回作るサンプルでは、FileMakerで作成した命令一覧をExcelのマクロで解析し、ワークシートを作成します。
（FileMakerからはタブ区切りテキストとして生成）
・１行で１命令
・左端（A列）が命令、B列以降右方向にパラメータを並べる
・パラメータは水平位置固定（コマンドによりどの列が何の値か決まっている）
・変数、条件分岐、ループなどもちろん一切ナシ
・エラー処理もナシ
どうせExcelだし、ワークシート上にスクリプトを書いちゃえという素敵仕様です。このブログのサンプルとしても、開発や処理の過程を見える化できていいですね。さらに、セルで区切る形で情報が並ぶとExcel VBAからの解析もラクに書けそうです。
コマンド一覧
※1 指定した範囲のセル数だけデータを並べる。複数行の場合は水平方向優先。
※2 サイズ・位置・スタイルはxl定数名で指定（詳しくは（その３）参照）
※3 （#,###などセル書式で指定する形式）
言語と呼ぶにはあまりに少ない命令数ですが、ワークシート生成に必要な手順を一つずつコマンドにしていったらこんな具合になりました。これぐらいでもちゃんと表が作れます。
次回はExcel VBAで実際にこの力技EMLを実行できるようプログラムしていきます。
つづく。
力技EML 言語仕様
・EML(Excel Markup Language)スクリプトはExcelのワークシート上に展開される（FileMakerからはタブ区切りテキストとして生成）
・１行で１命令
・左端（A列）が命令、B列以降右方向にパラメータを並べる
・パラメータは水平位置固定（コマンドによりどの列が何の値か決まっている）
・変数、条件分岐、ループなどもちろん一切ナシ
・エラー処理もナシ
どうせExcelだし、ワークシート上にスクリプトを書いちゃえという素敵仕様です。このブログのサンプルとしても、開発や処理の過程を見える化できていいですね。さらに、セルで区切る形で情報が並ぶとExcel VBAからの解析もラクに書けそうです。
コマンド一覧
| コマンド | パラメータ （スラッシュはセル区切り） | 説明 |
| // | 注釈(NOP) | |
| newBook | ブック名/シート数 | 新規ワークブック |
| width | 開始列番号/幅1/[幅2]/... | セル幅指定、開始位置から右方向へ任意の数設定可能 |
| height | 開始行番号/高さ1/[高さ2]/... | セル高さ指定、開始位置から下方向へ任意の数設定可能 |
| selectSheet | シート番号 | シートを選択 |
| nameSheet | シート名 | シート名を設定 |
| merge | SY/SX/EY/EX | セルを結合 |
| write | SY/SX/EY/EX/値1/[値2]... | セルに値を書き込む※1 |
| fontSize | SY/SX/EY/EX/サイズ | フォントサイズ設定 |
| hAlign | SY/SX/EY/EX/位置 | セル内水平位置設定※2 |
| vAlign | SY/SX/EY/EX/位置 | セル内垂直位置設定※2 |
| borders | SY/SX/EY/EX/線スタイル | 枠線を引く（外枠・縦・横すべて）※2 |
| numberFormat | SY/SX/EY/EX/スタイル | データの書式設定※3 |
| shrinkToFit | SY/SX/EY/EX/TRUE or FALSE | セルに合わせて文字を縮小 |
| wrapText | SY/SX/EY/EX/TRUE or FALSE | セル内テキスト折り返し |
| addIndent | SY/SX/EY/EX/TRUE or FALSE | セル内文字の前後にスペース |
※2 サイズ・位置・スタイルはxl定数名で指定（詳しくは（その３）参照）
※3 （#,###などセル書式で指定する形式）
言語と呼ぶにはあまりに少ない命令数ですが、ワークシート生成に必要な手順を一つずつコマンドにしていったらこんな具合になりました。これぐらいでもちゃんと表が作れます。
次回はExcel VBAで実際にこの力技EMLを実行できるようプログラムしていきます。
つづく。
0 件のコメント:
コメントを投稿