
FireMaker から Excel の帳票へ出力
みなさん暑いですね、そんな時に限って愛車のエアコンが壊れます!根性でエアコンガスを注入したFileMakerエンジニアの丸山です。
今回は、Excel で帳票を作成されている方向けの内容です。定型のエクセルに FileMaker からエクスポートしたCSVのデータを張り付けていきます。
そんな時に使用するのは、Phytonとopenpyxlライブラリです。ひな型の Excel ファイルにデータを書き込む作業に向いているライブラリです。
目次
Excelの帳票にデータを設定するなら、openpyxl
ひな形の帳票にデータを設定する方法や各種設定を説明していきます。

■値を設定する

①セルの行番号3、列番号13に文字列「売上」を設定する例です。
tmp_wb = openpyxl.load_workbook("c:\PythonWork\損益一覧テンプレート.xlsx")
tmp_ws = tmp_wb.active
tmp_ws.cell(3,13).value = "売上"
1行目:テンプレート用のエクセルを開いています。
2行目:開いているシートを取得しています。
3行目:対象のセルに文字列「売上」を設定しています。
②セルの行番号5、列番号13に数字「88」を設定する例です。
tmp_ws.cell(5,13).value = 88
1行目:対象のセルに数字「88」を設定しています。
■計算式を設定する

①セルの行番号3、列番Nに計算式を設定する例です。
tmp_ws.cell(4,13).value = "=M4/SUM(M4:M7)"
tmp_ws.cell(4,13).number_format = '0%'
1行目:対象のセルにセル「M4」の値÷「M4」~「M7」の値の合計を計算する式を設定しています。エクセルでの設定をそのまま記述すれば計算式も設定できますね。
2行目:対象のセルに%表示の設定をしています。
■セルを結合する

①セルの行番号2、列番M~Pを結合する例です。
tmp_ws.merge_cells(start_row=2, start_column=13, end_row=2, end_column=16)
1行目:結合するセル範囲に「start_row=開始行, start_column=開始列, end_row=終了行, end_column=終了列」を設定しています。
■セルの罫線、色を設定する

①セルの行番号5、列番号3に罫線と、背景色を設定する例です。
from openpyxl.styles.borders import Border, Side
side = Side(style='thin', color='000000')
border_rect = Border(top=side, bottom=side, left=side, right=side)
tmp_ws.cell(5, 3).border = border_rect
tmp_ws.cell(5, 3).fill = PatternFill(patternType='solid', fgColor='ccffff')
1行目:openpyxlモジュールの中から罫線に必要な所を取込んでいます。
2行目:罫線の種類と色を設定しています。
罫線の種類の例(thin:細い線、medium:普通の線、double:二重線、dotted:点線)
色の種類の例(黒:000000、白:FFFFFF、赤:FF0000、青:0000FF)
3行目:枠の上、下、左、右の罫線の設定をしています。
4行目:対象のセルに枠の罫線を設定しています。
5行目:対象のセルに塗りの設定をしています。
Python、XlsxWriterをインストールする
前々回のブログ「bBox 調査編 2」を参考にインストールしてみてください。
(https://fmsolution.supportas.co.jp/blog/20220613/)
openpyxlのインストール
① Windows のコマンドプロンプトから以下を入力しEnterキーを押します。
pip install openpyxl
FileMaker から Python プログラムを実行する方法
①プログラムが入るテキストフィールドと変換用のオブジェクトフィールドを作成してください。

②プログラムを変換して実行させるスクリプトを作成します。

4行目:店舗のデータをCSVに書き出しています。

※:店舗ごとの売上や経費などの科目ごとの集計をCSVに出力しています。
8行目:プログラムの出力先を指定しています。
12行目:Pythonプログラムが実行できる文字コードに変換しています。
14行目:変換したプログラムをエクスポートしています。
18行目:エクスポートしたプログラムを実行しています。
③「4.プログラムソース」に書かれてあるプログラムを「プログラムソース」フィールドに入れます。
④スクリプトを実行させると「C:\PythonWork\売上一覧NEW.xlsx」に、エクセルファイルが作成されています。
※エクセルのテンプレートが”c:\PythonWork\損益一覧テンプレート.xlsx”にある事が前提です。
プログラムソース
実際に作成プログラムソースです。コメントで処理内容を記載しています、参考にしてみてください。

# openpyxl ライブラリ読込
import openpyxl
import csv
from openpyxl import utils
from openpyxl.styles.alignment import Alignment
from openpyxl.styles import PatternFill
from openpyxl.styles.borders import Border, Side
# 罫線スタイル設定
side = Side(style='thin', color='000000')
sided = Side(style='double', color='000000')
#枠罫線設定
border_rect = Border(top=side, bottom=side, left=side, right=side)
border_rectd = Border(top=side, bottom=sided, left=side, right=side)
# エクセルの位置(オフセット)指定
out_col = 11
out_row = 3
# テンプレートを開く
tmp_wb = openpyxl.load_workbook("c:\PythonWork\損益一覧テンプレート.xlsx")
tmp_ws = tmp_wb.active
# CSVデータをdata変数へ格納
with open("c:\PythonWork\データ.csv") as csvfile:
data = list(csv.reader(csvfile))
# data変数の縦横の大きさ取得
len_row = len(data)
len_col = len(data[0])
# エクセルへデータを展開
for row_i in range(len_row):
if data[row_i][1] == "":
break
if out_col > 12:
# 列幅の指定
tmp_ws.column_dimensions[utils.get_column_letter(out_col)].width = 9
tmp_ws.column_dimensions[utils.get_column_letter(out_col+1)].width = 6
tmp_ws.column_dimensions[utils.get_column_letter(out_col+2)].width = 9
tmp_ws.column_dimensions[utils.get_column_letter(out_col+3)].width = 6
out_col = out_col + 2
out_row = 3
for col_i in range(len_col):
# 列指定文字の取得
colchr = utils.get_column_letter(out_col)
if col_i == 2:
# 見出し項目の設定
tmp_ws.cell(out_row-1, out_col).value = data[row_i][col_i]
tmp_ws.cell(out_row, out_col).value = "売上"
tmp_ws.cell(out_row, out_col+1).value = "比率"
tmp_ws.cell(out_row, out_col+2).value = "売上"
tmp_ws.cell(out_row, out_col+3).value = "比率"
elif 2 < col_i < 9 or 58< col_i < 65:
if data[row_i][col_i] != "":
# 値の設定
tmp_ws.cell(out_row, out_col).value = int(data[row_i][col_i])
# 計算式の設定
tmp_ws.cell(out_row, out_col+1).value = "=" + colchr + str(out_row) + "/SUM(" + colchr + "4:" + colchr + "9)"
elif 8 < col_i < 23 or 64 < col_i < 79:
if data[row_i][col_i] != "":
tmp_ws.cell(out_row, out_col).value = int(data[row_i][col_i])
tmp_ws.cell(out_row, out_col+1).value = "=" + colchr + str(out_row) + "/SUM(" + colchr + "10:" + colchr + "23)"
if col_i == 22 or col_i == 78:
tmp_ws.cell(out_row+1, out_col).value = "=SUM(" + colchr + "10:" + colchr + "23)"
tmp_ws.cell(out_row+1, out_col+1).value = "=SUM(" + colchr + "4:" + colchr + "9)/SUM(" + colchr + "10:" + colchr + "23)"
tmp_ws.cell(out_row+2, out_col).value = "=SUM(" + colchr + "4:" + colchr + "9)-" + colchr + "24"
tmp_ws.cell(out_row+2, out_col+1).value = "=SUM(" + colchr + "24)/SUM(" + colchr + "4:" + colchr + "23)"
elif 22 < col_i < 43 or 77 < col_i < 99:
if data[row_i][col_i] != "":
tmp_ws.cell(out_row, out_col).value = int(data[row_i][col_i])
tmp_ws.cell(out_row, out_col+1).value = "=" + colchr + "" + str(out_row) + "/SUM(" + colchr + "10:" + colchr + "23)"
if col_i == 42 or col_i == 98:
tmp_ws.cell(out_row+1, out_col).value = "=SUM(" + colchr + "26:" + colchr + "45)"
tmp_ws.cell(out_row+1, out_col+1).value = "=SUM(" + colchr + "46)/(SUM(" + colchr + "4:" + colchr + "23)+" + colchr + "46)"
tmp_ws.cell(out_row+2, out_col).value = "=SUM(" + colchr + "4:" + colchr + "9)-SUM(" + colchr + "10:" + colchr + "23)-SUM(" + colchr + "26:" + colchr + "46)"
tmp_ws.cell(out_row+2, out_col+1).value = "=SUM(" + colchr + "46)/(SUM(" + colchr + "4:" + colchr + "23)+" + colchr + "46)"
elif 42 < col_i < 59 or 98 < col_i < 115:
if data[row_i][col_i] != "":
tmp_ws.cell(out_row, out_col).value = int(data[row_i][col_i])
tmp_ws.cell(out_row, out_col+1).value = "=" + colchr + "" + str(out_row) + "/SUM(" + colchr + "48:" + colchr + "65)"
if col_i == 58 or col_i == 114:
tmp_ws.cell(out_row+1, out_col).value = 0
tmp_ws.cell(out_row, out_col+1).value = "=" + colchr + "" + str(out_row+1) + "/SUM(" + colchr + "48:" + colchr + "65)"
tmp_ws.cell(out_row+2, out_col).value = 0
tmp_ws.cell(out_row, out_col+1).value = "=" + colchr + "" + str(out_row+2) + "/SUM(" + colchr + "48:" + colchr + "65)"
tmp_ws.cell(out_row+3, out_col).value = "=SUM(" + colchr + "48:" + colchr + "65)"
tmp_ws.cell(out_row+3, out_col+1).value = "=" + colchr + "66/(SUM(" + colchr + "4:" + colchr + "9)+SUM(" + colchr + "10:" + colchr + "23)+SUM(" + colchr + "26:" + colchr + "45)+SUM(" + colchr + "48:" + colchr + "65))"
tmp_ws.cell(out_row+4, out_col).value = "=SUM(" + colchr + "4:" + colchr + "9)-(SUM(" + colchr + "10:" + colchr + "23)+SUM(" + colchr + "26:" + colchr + "45)+SUM(" + colchr + "48:" + colchr + "65))"
tmp_ws.cell(out_row+4, out_col+1).value = "=" + colchr + "67/(SUM(" + colchr + "4:" + colchr + "9)+SUM(" + colchr + "10:" + colchr + "23)+SUM(" + colchr + "26:" + colchr + "45)+SUM(" + colchr + "48:" + colchr + "65))"
# 表示形式の設定
if col_i != 2:
tmp_ws.cell(out_row, out_col).number_format = '#,##0'
tmp_ws.cell(out_row, out_col+1).number_format = '0%'
if col_i == 22 or col_i == 42 or col_i == 78 or col_i == 98:
tmp_ws.cell(out_row, out_col).number_format = '#,##0'
tmp_ws.cell(out_row, out_col+1).number_format = '0%'
tmp_ws.cell(out_row+1, out_col).number_format = '#,##0'
tmp_ws.cell(out_row+1, out_col+1).number_format = '0%'
tmp_ws.cell(out_row+2, out_col).number_format = '#,##0'
tmp_ws.cell(out_row+2, out_col+1).number_format = '0%'
if col_i == 58 or col_i == 114:
tmp_ws.cell(out_row+1, out_col).number_format = '#,##0'
tmp_ws.cell(out_row+1, out_col+1).number_format = '0%'
tmp_ws.cell(out_row+2, out_col).number_format = '#,##0'
tmp_ws.cell(out_row+2, out_col+1).number_format = '0%'
tmp_ws.cell(out_row+3, out_col).number_format = '#,##0'
tmp_ws.cell(out_row+3, out_col+1).number_format = '0%'
tmp_ws.cell(out_row+4, out_col).number_format = '#,##0'
tmp_ws.cell(out_row+4, out_col+1).number_format = '0%'
if col_i == 2:
# 罫線の設定
tmp_ws.cell(out_row-1, out_col).border = border_rect
tmp_ws.cell(out_row, out_col).border = border_rect
tmp_ws.cell(out_row, out_col+1).border = border_rect
tmp_ws.cell(out_row, out_col+2).border = border_rect
tmp_ws.cell(out_row, out_col+3).border = border_rect
# セルの結合設定
tmp_ws.merge_cells(start_row=out_row-1, start_column=out_col, end_row=out_row-1, end_column=out_col+3)
# セルの配置設定
tmp_ws.cell(out_row-1, out_col).alignment = Alignment(horizontal="centerContinuous", vertical="center")
tmp_ws.cell(out_row, out_col).alignment = Alignment(horizontal="centerContinuous", vertical="center")
tmp_ws.cell(out_row, out_col+1).alignment = Alignment(horizontal="centerContinuous", vertical="center")
tmp_ws.cell(out_row, out_col+2).alignment = Alignment(horizontal="centerContinuous", vertical="center")
tmp_ws.cell(out_row, out_col+3).alignment = Alignment(horizontal="centerContinuous", vertical="center")
# セルの塗り設定
tmp_ws.cell(out_row, out_col).fill = PatternFill(patternType='solid', fgColor='ccffff')
tmp_ws.cell(out_row, out_col+1).fill = PatternFill(patternType='solid', fgColor='ccffff')
tmp_ws.cell(out_row, out_col+2).fill = PatternFill(patternType='solid', fgColor='ccffff')
tmp_ws.cell(out_row, out_col+3).fill = PatternFill(patternType='solid', fgColor='ccffff')
elif col_i == 22 or col_i == 42 or col_i == 78 or col_i == 98:
tmp_ws.cell(out_row, out_col).border = border_rectd
tmp_ws.cell(out_row, out_col+1).border = border_rectd
tmp_ws.cell(out_row+1, out_col).border = border_rect
tmp_ws.cell(out_row+1, out_col+1).border = border_rect
tmp_ws.cell(out_row+2, out_col).border = border_rect
tmp_ws.cell(out_row+2, out_col+1).border = border_rect
elif col_i == 58 or col_i == 114:
tmp_ws.cell(out_row, out_col).border = border_rect
tmp_ws.cell(out_row, out_col+1).border = border_rect
tmp_ws.cell(out_row+1, out_col).border = border_rect
tmp_ws.cell(out_row+1, out_col+1).border = border_rect
tmp_ws.cell(out_row+2, out_col).border = border_rectd
tmp_ws.cell(out_row+2, out_col+1).border = border_rectd
tmp_ws.cell(out_row+3, out_col).border = border_rect
tmp_ws.cell(out_row+3, out_col+1).border = border_rect
tmp_ws.cell(out_row+4, out_col).border = border_rect
tmp_ws.cell(out_row+4, out_col+1).border = border_rect
elif col_i != 2:
tmp_ws.cell(out_row, out_col).border = border_rect
tmp_ws.cell(out_row, out_col+1).border = border_rect
if col_i == 58:
out_col = out_col + 2
out_row = 1
if col_i == 22 or col_i == 42 or col_i == 58 or col_i == 78 or col_i == 98:
out_row = out_row + 3
elif col_i > 1:
out_row = out_row + 1
# エクセルへデータを新しい名前で保存
tmp_wb.save("c:\PythonWork\損益一覧NEW.xlsx")
まとめ
Phytonのopenpyxlライブラリを使用すると、CSVファイルを読み込めて、エクセルファイルに編集を加えて新しいエクセルファイルが作成できる事がわかったかと思います。この方法でいろんな種類のエクセルファイルを作成してみては、いかがでしょうか。
Pythonのロゴをみていると、ムーミン谷のニョロニョロを想像してしまいます。
せっかくなのでムーミンキャラクター診断をしてみました。
https://www.moomin.co.jp/which_moomin_character_are_you
で結果は「ムーミンパパ!」と診断されました。なんでパパ、パパよりは父ちゃんと呼ばれたい!

(画像引用元:https://www.moomin.co.jp/characters/hattifatteners)
そんな、父ちゃんと呼ばれたいエンジニアの道具を紹介させてください。
イワタニプリムスのバーナーで、登山でラーメンを煮たりキャンプでコメを炊いたり寒い時にストーブのかわりにと
便利なバーナーで、コンパクトに収納できるので便利です。

そんな道具たちに新たに、鉄板プレートが加わりました!
豊前工業所さまからいただいた物です。丁度良いサイズで絶妙な曲げ加工と鉄板の厚みにより
お肉をいい感じに焼いてくれます。

そんな鉄板で鹿児島黒牛を焼いてみました。外はこんがり中ジューシーに仕上がります。

夕日とともに、美味しくいただきました。
鹿児島黒牛とこのプレートの組み合わせサイコーです!

夕焼けに照らされた白い機体が、ゴーという音をたてて通り過ぎて行きます。
自宅の真上が飛行機の航路になっていて、よく見る光景なのですが最近その飛行機を芝生に
ねころがって見るのが好きになりました。芝生の臭いを感じながら夢と希望をのせて
飛ぶ飛行のようになりたいと思うエンジニアでした。それではまた。
参考
1. Pythonインストーラ
https://www.python.org/downloads/
2. openpyxl
https://openpyxl.readthedocs.io/en/stable/
3. ムーミンキャラクター診断
https://www.moomin.co.jp/which_moomin_character_are_you
4. 豊前工業所
https://www.buzen-k.com/
■環境情報
Windows 10 Pro