bBox おまけ

2022/06/30 丸山

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

みなさま、｢元気とユーモアのない社会に明るい未来はやって来ない｣を信じるエンジニア
丸山です。

前回、Pythonのライブラリ「XlsxWriter」を使ったグラフの作成を説明しました。

今回は、そんな難しそうな説明はやめて当社のマスコット「さぽたん」を作ってみたいと思います。

1 Python、XlsxWriterをインストールする
2 FileMakerからPythonプログラムを実行する方法
3 ３. プログラムソース
4 まとめ

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

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

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

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

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

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

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

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

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

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

④スクリプトを実行させると「C:\PythonWork/sapotan.xlsx」に、エクセルファイルが作成されています。
エクセルファイルを開くと、「さぽたん」が作成されています。

３. プログラムソース

import xlsxwriter 

#データ用配列にデータを指定
data = [
["","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","","","","","","","","","","","","","",""],
["","","","","","","","","","","","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","","","","","","","","","","","","",""],
["","","","","","","","","","","","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","","","","","","","","","","","",""],
["","","","","","","","","","","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","","","","","","","","","","","",""],
["","","","","","","","","","","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","","","","","","","","","","",""],
["","","","","","","","","","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","","","","","","","","","","",""],
["","","","","","","","","","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","","","","","","","","","",""],
["","","","","","","","","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","","","","","","","","","",""],
["","","","","","","","","1","1","1","1","1","1","1","1","","","","","","","","","2","2","","","","","","","","","","","","","","","","","","","2","2","","","","","","","","1","1","1","1","1","1","1","","","","","","","","","",""],
["","","","","","","","","1","1","1","1","1","1","1","","","","","","","","","2","2","2","2","","","","","","","","","","","","","","","","","2","2","2","2","","","","","","","1","1","1","1","1","1","1","1","","","","","","","","",""],
["","","","","","","","1","1","1","1","1","1","1","1","","","","","","","","","2","2","2","2","","","","","","","","","","","","","","","","","2","2","2","2","","","","","","","1","1","1","1","1","1","1","1","","","","","","","","",""],
["","","","","","","","1","1","1","1","1","1","1","1","","","","","","","","","2","2","2","2","","","","","","","","","","","","","","","","","2","2","2","2","","","","","","","1","1","1","1","1","1","1","1","","","","","","","","",""],
["","","","","","","","1","1","1","1","1","1","1","1","","","","","","","","","2","2","2","2","","","","","","","","","","","","","","","","","2","2","2","2","","","","","","","1","1","1","1","1","1","1","1","1","","","","","","","",""],
["","","","","","","","1","1","1","1","1","1","1","1","","","","","","","","","2","2","2","2","","","","","","","","","","","","","","","","","2","2","2","2","","","","","","","1","1","1","1","1","1","1","1","1","","","","","","","",""],
["","","","","","","","1","1","1","1","1","1","1","1","","","","","","","","","2","2","2","2","","","","","","","","2","2","","","","","","","","2","2","2","2","","","","","","","1","1","1","1","1","1","1","1","1","","","","","","","",""],
["","","","","","","1","1","1","1","1","1","1","1","1","","","","","","","","","2","2","2","2","","","","","","","2","2","2","2","","","","","","","2","2","2","2","","","","","","","1","1","1","1","1","1","1","1","1","","","","","","","",""],
["","","","","","","1","1","1","1","1","1","1","1","1","","","","","","","","","","2","2","","","","","","","","","","","2","","","","","","","","2","2","","","","","","","","1","1","1","1","1","1","1","1","1","","","","","","","",""],
["","","","","","","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","2","2","2","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","","","","","","","",""],
["","","","","","","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","2","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","","","","","","","",""],
["","","","","","","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","2","2","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","","","","","","","",""],
["","","","","","","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","2","2","2","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","","","","","","","",""],
["","","","","","","","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","","","","","","","",""],
["","","","","","","","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","","","","","","","",""],
["","","","","","","","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","",""],
["","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","",""],
["","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","",""],
["","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","",""],
["","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","",""],
["","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","",""],
["","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","",""],
["","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","",""],
["","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","",""],
["","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1",""],
["1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","1","1","1","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1"],
["1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","1","1","1","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1"],
["","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","",""],
["","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","",""],
["","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","",""],
["","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","1","1","1","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","",""],
["","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","1","1","1","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","1","1","1","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","1","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","1","1","1","1","1","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""],
["","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""]
]

#配列の長さを取得
len_row = len(data)
len_col = len(data[0])

workbook = xlsxwriter.Workbook("C:\PythonWork/sapotan.xlsx")

#黒設定
fmt_black = workbook.add_format({'border': 0})
fmt_black.set_font_color('#000000')
fmt_black.set_bg_color('#000000')

#白設定
fmt_white = workbook.add_format({'border': 0})
fmt_white.set_font_color('#FFFFFF')
fmt_white.set_bg_color('#FFFFFF')

#青設定
fmt_blue = workbook.add_format({'border': 0})
fmt_blue.set_font_color('#00B0F0')
fmt_blue.set_bg_color('#00B0F0')


worksheet = workbook.add_worksheet("test02")


for row_i in range(len_row):
     for col_i in range(len_col):
          if data[row_i][col_i] == '':
               worksheet.write(row_i, col_i,  data[row_i][col_i], fmt_white)
          elif data[row_i][col_i] == "2":
               worksheet.write_number(row_i, col_i,  int(data[row_i][col_i]), fmt_black)
          elif data[row_i][col_i] == "1":
               worksheet.write_number(row_i, col_i,  int(data[row_i][col_i]), fmt_blue)
          else:
               worksheet.write(row_i, col_i,  data[row_i][col_i], fmt_white)

#列幅設定        
for col_i in range(len_col):
     worksheet.set_column(col_i, col_i, 0.3)

for row_i in range(len_row):
     worksheet.set_row(row_i, 3.7)

workbook.close()

まとめ

プログラムは長いのですが、データを記述している部分が多く処理はそんなに長くありません。

少しずつプログラムに変更を加えて動かしみると、動きの理解が深まると思いますので
まずは、動かす事から始めてみてください。

彼は、パソコンの前で自分の仕事ぶりを見守っているミクロンマンです。厳しい顔で見てきます！
銀色のリーゼント風の頭を見ていると勇気が出てきます。
ミクロマンみたいに髪の毛はフサフサにはなれないけれど、周りを笑顔にさせられるよう頑張っていきます。それではまた。

参考

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

■環境情報

Windows 10 Pro

丸山

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

bBox おまけ

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

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

３. プログラムソース

まとめ

前の記事

次の記事