Python ツール #2 ― 簡単な Excel ファイル統計（ひな形）
「複数の Excel ファイルを読み込んで、1つの Excel ファイルに書き出す」という処理の Python スクリプトひな形を作成しました。
例えば、複数の Excel ファイルにアンケート結果が入っていて、そのアンケート結果を 1つの Excel にまとめ上げる必要があるという要件が出てきたときに、この Python スクリプトひな形を修正して、その要件に合わせた Python スクリプトを開発できるようになります。
例えば、Excel ファイルに書かれたデータベース表定義を読み込んで、DDL 文を生成するとか（ただ、これはどなたかが VB Script で作成しているハズですが）。
実行イメージ
入力 Excel ファイル
テスト用に 2つの Excel ファイルを用意しています。
DataTable-1.xlsx
DataTable-2.xlsx
出力 Excel ファイル
複数の Excel ファイルを読み込んで、1つの Excel ファイルに出力しています。
開発するに至った動機
私の仕事において、複数の Excel ファイルを分析するという状況がよくあります。手で出来る量ではない場合、“怠惰” な私は、時間を掛けてプログラムを作成して、プログラムに分析させることを選択することがあります。
毎回、Excel 分析の要件が全く違うので、スクリプトの共用はできず、スクリプトをコピーして作り直していました。そのとき、以下のようなことが発生して非効率的でした。
- 「これまでに作成した数々のスクリプトのうち、どのスクリプトを使うのか」「そのスクリプトの作りを理解して、削除箇所を特定し、追加箇所・内容を特定して、...」
- 理解ミス、削除ミス、追加ミス、...。
- 他の方に Excel ファイル分析のイメージを共有できない。
今後は、ひな形スクリプトがあることによって、以下のように効率的になるであろうと考えています。
- 「ひな形スクリプトに対し」「追加箇所・内容の特定」だけに注力できる。
- ひな形スクリプトと作成スクリプトの diff を取ることで、追加箇所・内容を明確にできる。
- 他の方と Excel ファイル分析のイメージを共有できる。ひょっとしたら、他の方にも Excel ファイル分析のプログラムを作成してもらえるかもしれない。
なお、今回の Python スクリプトは、ゼロバイトからのフルスクラッチで作成しました（会社で開発した成果物を公開するわけにはいきませんので）。
Excel VB マクロでの実装を選択しなかった理由
私の主観や知識不足も含まれます。
- Excel VB マクロの有効化の設定が面倒くさい。
- Excel VB マクロは、ステップ実行テストしにくいのでは？
- Excel VB マクロだけで実現できなくなるものあり。例えば、分析において、データベースの読み込みもやりたいとか。
ソースコードの簡単な説明
- ヘッダー。
excel_summarizer.py
#!/usr/bin/env python3 ・・・ # Import Libraries import os - 各定数値。必要に応じて修正してください。例えば、Excel ファイルのパス “IN_SRC_ROOT” は、D: ドライブからの絶対パスでもよいですし、“excel_summarizer.py” からの相対パスでもよいです。
excel_summarizer.py
# Input, Output IN_DIR = '.\\input' OUT_DIR = '.\\output' # IN_SRC_ROOT = 'D:\\Developments\\PyCharmProjects\\excel_summarize\\input' # noqa IN_SRC_ROOT = '.\\input' IN_SRC_RELATIVE = '\\excel' IN_EXCEL = IN_DIR + '\\excel_summary_template.xlsx' OUT_EXCEL = OUT_DIR + '\\excel_summary.xlsx' IN_SHEET = 'Data Table' OUT_SHEET = 'Summary of Data Tables' EXTEND = '.xlsx' OUT_DEBUG = OUT_DIR + '\\debug.txt' - Excel ファイルの読み込み位置、Excel ファイルの書き込み位置、および Excel ファイルの書き込みのセル書式です。Excel ファイルのセル構成に合わせて値を書き換えてください。
excel_summarizer.py
# Input Excel Cell Position (1 Origin) IN_CELL_ROW_OFFSET = 4 IN_CELL_COL_OFFSET = 2 # Output Excel Cell Position (1 Origin) OUT_CELL_ROW_OFFSET = 4 OUT_CELL_COL_OFFSET = 2 # Output Cell Formats CELL_FORMATS = [ {'font': FONT_MEIRYO, 'alignment': None, 'number_format': NUMBER_FORMAT_NO_CUBE_INT}, {'font': FONT_MEIRYO, 'alignment': ALIGN_LEFT, 'number_format': None}, {'font': FONT_MEIRYO, 'alignment': None, 'number_format': NUMBER_FORMAT_CUBE_CENT}, {'font': FONT_MEIRYO, 'alignment': None, 'number_format': NUMBER_FORMAT_CUBE_INT}, ] - Excel ファイルを読み込んで Excel ファイルに書き出す処理。分析の要件に応じて処理を書き換えてください。
excel_summarizer.py
# Scan Excel File def scan_excel_file(write_excel: WriteExcel, full_path_file: str, fp) -> int: read_excel = ReadExcel(full_path_file, IN_SHEET) row_min, row_max, col_min, col_max = read_excel.range() num_lines = 0 # Read Rows for row_current in range(row_min, row_max): num_lines += 1 sep = '' csv = '' idx = 0 # Read And Write Cells for col_current in range(col_min, col_max): # Write Cell from_cell = read_excel.cell(col_current) to_cell = write_excel.cell(col_current) to_cell.value = from_cell.value to_cell.border = BORDER_ALL to_cell.font = CELL_FORMATS[idx]['font'] if CELL_FORMATS[idx]['alignment'] is not None: to_cell.alignment = CELL_FORMATS[idx]['alignment'] if CELL_FORMATS[idx]['number_format'] is not None: to_cell.number_format = CELL_FORMATS[idx]['number_format'] csv += sep + ('%s' % to_cell.value) sep = ', ' idx += 1 read_excel.next_row() write_excel.next_row() if fp is not None: fp.write('%5d: %s\n' % (num_lines, csv)) read_excel.close() return num_lines - 再帰を使ってフォルダーをシークし、当該フォルダー内の各 Excel ファイルを分析させる処理。
excel_summarizer.py
# Seek Directories def seek_directories(excel: WriteExcel, level: int, dir_root: str, dir_relative: str, fp) -> None: dirs = [] files = [] for path in os.listdir(dir_root): if os.path.isfile(os.path.join(dir_root, path)): files.append(path) else: dirs.append(path) files.sort(key=str.lower) for file in files: full_path_file = os.path.join(dir_root, file) if fp is not None: fp.write('%s\n' % full_path_file) base, ext = os.path.splitext(file) if ext == EXTEND and not base.startswith('~'): lines = scan_excel_file(excel, full_path_file, fp) print('%s %s %d' % (dir_relative, file, lines)) dirs.sort(key=str.lower) for dir_nest in dirs: seek_directories(excel, level + 1, os.path.join(dir_root, dir_nest), os.path.join(dir_relative, dir_nest), fp) return - メイン処理。デバッグログを出力したくない場合は、“fp = None” を設定し、“fp = open( ... )” をコメントアウトしてください。
excel_summarizer.py
# Get Current Time def get_current_time() -> str: now = datetime.datetime.now() dt = now.strftime("%Y-%m-%d %H:%M:%S") return dt # Main def main() -> None: try: options, arguments = getopt.getopt(sys.argv[1:], shortopts="h", longopts=["help"]) except getopt.error as message: print(message) print(__doc__) sys.exit(1) for option, argument in options: if option in ("-h", "--help"): print(__doc__) sys.exit(0) print('Excel Summarizer - start [%s]' % get_current_time()) # fp = None fp = open(OUT_DEBUG, 'w', encoding='utf-8') write_excel = WriteExcel(IN_EXCEL, OUT_EXCEL, OUT_SHEET) seek_directories(write_excel, 0, IN_SRC_ROOT + IN_SRC_RELATIVE, IN_SRC_RELATIVE, fp) write_excel.close() if fp is not None: fp.close() print('Excel Summarizer - end [%s]' % get_current_time()) sys.exit(0) # Goto Main if __name__ == '__main__': main()
プログラマーの三大美徳
怠惰、短気、傲慢