Supportas Developers Blog

FireMaker から Excel の帳票へ出力

丸山

FileMakerサポータス開発者ブログ

みなさん暑いですね、そんな時に限って愛車のエアコンが壊れます！根性でエアコンガスを注入したFileMakerエンジニアの丸山です。

今回は、Excel で帳票を作成されている方向けの内容です。定型のエクセルに FileMaker からエクスポートしたCSVのデータを張り付けていきます。
そんな時に使用するのは、Phytonとopenpyxlライブラリです。ひな型の Excel ファイルにデータを書き込む作業に向いているライブラリです。

Excelの帳票にデータを設定するなら、openpyxl

ひな形の帳票にデータを設定する方法や各種設定を説明していきます。

■値を設定する

①セルの行番号３、列番号１３に文字列「売上」を設定する例です。

tmp_wb = openpyxl.load_workbook("c:\PythonWork\損益一覧テンプレート.xlsx")
tmp_ws = tmp_wb.active
tmp_ws.cell(3,13).value = "売上"

　　１行目：テンプレート用のエクセルを開いています。

　　２行目：開いているシートを取得しています。

　　３行目：対象のセルに文字列「売上」を設定しています。

②セルの行番号5、列番号１３に数字「88」を設定する例です。

tmp_ws.cell(5,13).value = 88

　　１行目：対象のセルに数字「88」を設定しています。

■計算式を設定する

①セルの行番号３、列番Nに計算式を設定する例です。

tmp_ws.cell(4,13).value = "=M4/SUM(M4:M7)"
tmp_ws.cell(4,13).number_format = '0%'

　　１行目：対象のセルにセル「M4」の値÷「M4」～「M7」の値の合計を計算する式を設定しています。エクセルでの設定をそのまま記述すれば計算式も設定できますね。

　　２行目：対象のセルに％表示の設定をしています。

■セルを結合する

①セルの行番号２、列番M～Pを結合する例です。

tmp_ws.merge_cells(start_row=2, start_column=13, end_row=2, end_column=16)

　　１行目：結合するセル範囲に「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')

　　１行目：openpyxlモジュールの中から罫線に必要な所を取込んでいます。

　　２行目：罫線の種類と色を設定しています。

　　　　罫線の種類の例（thin：細い線、medium：普通の線、double：二重線、dotted：点線）

　　　　色の種類の例（黒：000000、白：FFFFFF、赤：FF0000、青：0000FF）

　　３行目：枠の上、下、左、右の罫線の設定をしています。

　　４行目：対象のセルに枠の罫線を設定しています。
　　５行目：対象のセルに塗りの設定をしています。

Python、XlsxWriterをインストールする

前々回のブログ「bBox 調査編 2」を参考にインストールしてみてください。
（https://fmsolution.supportas.co.jp/blog/20220613/）

openpyxlのインストール

① Windows のコマンドプロンプトから以下を入力しEnterキーを押します。

pip install openpyxl

　

FileMaker から Python プログラムを実行する方法

①プログラムが入るテキストフィールドと変換用のオブジェクトフィールドを作成してください。

　　

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

　

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

　

　　※：店舗ごとの売上や経費などの科目ごとの集計をCSVに出力しています。

　　８行目：プログラムの出力先を指定しています。

　　１２行目：Pythonプログラムが実行できる文字コードに変換しています。

　　１４行目：変換したプログラムをエクスポートしています。

　　１８行目：エクスポートしたプログラムを実行しています。

③「４．プログラムソース」に書かれてあるプログラムを「プログラムソース」フィールドに入れます。

④スクリプトを実行させると「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）

そんな、父ちゃんと呼ばれたいエンジニアの道具を紹介させてください。
イワタニプリムスのバーナーで、登山でラーメンを煮たりキャンプでコメを炊いたり寒い時にストーブのかわりにと
便利なバーナーで、コンパクトに収納できるので便利です。

そんな道具たちに新たに、鉄板プレートが加わりました！

豊前工業所さまからいただいた物です。丁度良いサイズで絶妙な曲げ加工と鉄板の厚みにより
お肉をいい感じに焼いてくれます。

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

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

夕焼けに照らされた白い機体が、ゴーという音をたてて通り過ぎて行きます。

自宅の真上が飛行機の航路になっていて、よく見る光景なのですが最近その飛行機を芝生に
ねころがって見るのが好きになりました。芝生の臭いを感じながら夢と希望をのせて
飛ぶ飛行のようになりたいと思うエンジニアでした。それではまた。

参考

１. Pythonインストーラ 
https://www.python.org/downloads/

２. openpyxl 
https://openpyxl.readthedocs.io/en/stable/　

３. ムーミンキャラクター診断 
https://www.moomin.co.jp/which_moomin_character_are_you

４. 豊前工業所
https://www.buzen-k.com/

■環境情報

Windows 10 Pro

丸山

ひょんな事から FrameMaker の使い手から FileMaker の使い手へ。なによりも嫁が怖い、すきなYouTubeは「もちまる日記」とはっきり言える。いまどき九州男

前の記事

Supportas Developers Blog

FileMaker 19.5 のテキスト認識を試してみた件

次の記事

Supportas Developers Blog

繰り返しフィールドを使ったCount関数の挙動

ロゴ：株式会社サポータス
ロゴ：Claris Partner PLATINUM
Claris パートナーの中でも最上位のPLATINUMレベルメンバーとして、Claris FileMaker を活用したソリューションを提供いたします。
FileMaker 、ファイルメーカー、 FileMaker Cloud 、 FileMaker Go およびファイルフォルダロゴは、
Claris International Inc. （旧 FileMaker, Inc.）の米国および／またはその他の国における登録商標です。
アイコン：ページトップへ

Copyright © 2022 Supportas Inc. All rights reserved.