■ツールの概要
決められたフォーマットのExcelファイルを別名にて複数作成したい場合、手作業でひとつずつコピーしていると、非常に時間と労力がかかるため、自動化するツールを作成する。
以降、本ツールを「フォーマットシートコピー」ツールと呼ぶ。
■制約事項・前提条件
・本処理は、シート別ブックにコピーせず、マスタファイル「mst_sheet.xlsx」を別名保存し、対象のフォーマットシート以外を削除する方式とする。
詳細は「処理イメージ図」参照。
・マスタファイル「mst_sheet.xlsx」は、「mst」フォルダに必ず存在しているものとする。
※ファイルが存在しないエラーハンドリングは考慮しない。
・エラーハンドリングは以下の2つとする。
①「mst_sheet.xlsx」に「本紙」シートが存在しない場合は、エラーをコンソールに出力し、処理を終了する。
②「mst_sheet.xlsx」の「本紙」シートにて指定したシート名が存在しない場合は、エラーをコンソールに出力し、次の処理に進む。
■プログラム設計
「フォーマットシートコピー」ツールのプログラム設計を以下に示す。
▼ディレクトリ構成
「フォーマットシートコピー」のディレクトリ構成は、以下の通り。
├── mst # マスタファイル格納場所
│ └── mst_sheet.xlsx
├── output # フォーマットファイル出力場所
│ └── # ここにExcelファイルを出力する
└── sheetcopy.py # 実行モジュール▼フォーマットシートコピー処理
「本紙」シートの「ファイル名」列の値で「mst_sheer.xlsx」を別名保存し、「シート名」列の値のシート名のみを残す処理。
・処理イメージ図
以下の①〜③の処理を繰り返す。
※（）内は例を示す。
①「本紙」シートの「ファイル名」と「シート名」を1行ずつ読み込む
②「mst_sheet.xlsx」ファイルを読み込んだ「ファイル名：（sample1.xlsx）」で別名保存
③別名保存したExcelファイル（sample1.xsx）を開き、該当の「シート名：（A）」以外を削除
・定数一覧
「フォーマットシートコピー」ツールにて使用する定数を以下の表に示す。
| 定数 | 値 | 概要 |
|---|---|---|
| EXCEL_MST_FILEPATH | ./mst/mst_sheet.xlsx | マスタファイルの相対パス |
| OUTPUT_DIR | ./output/ | 出力先の相対パス |
| MST_SHEET_MAIN | 本紙 | マスタファイルのシート名 |
| MST_SHEET_START_ROW | 3 | 読み込み開始行 ※マスタファイルの「本紙」シート |
| MST_SHEET_FILENAME_COL | 2 | 「ファイル名」列 ※マスタファイルの「本紙」シート |
| MST_SHEET_SHEETNAME_COL | 3 | 「シート名」列 ※マスタファイルの「本紙」シート |
・プログラム処理フロー図
「フォーマットシートコピー」ツールの処理フロー図を以下の図にて示す。
■サンプルコード
・sheetcopy.py
import openpyxl
# ファイルパスの定数
EXCEL_MST_FILEPATH = './mst/mst_sheet.xlsx'
OUTPUT_DIR = './output/'
# シート名の定数
MST_SHEET_MAIN = '本紙'
# 「本紙」シートに関する定数
MST_SHEET_START_ROW = 3
MST_SHEET_FILENAME_COL = 2
MST_SHEET_SHEETNAME_COL = 3
# シートコピー処理
def copy_sheet():
'''
ファイル名と指定したシートのみを残す処理
「本紙」シートの「ファイル名」列の値で「mst_sheer.xlsx」を別名保存し
「シート名」列の値のシート名のみを残す処理
'''
# マスタのExcelファイルを開く
mst_wb = openpyxl.load_workbook(EXCEL_MST_FILEPATH)
# シート名を配列に格納
mst_shtname_list = [ws.title for ws in mst_wb]
# 「本紙」シートがなければ処理終了
if MST_SHEET_MAIN not in mst_shtname_list:
print('「本紙」シートがありません')
# マスタExcelファイル閉じる
mst_wb.close()
return
# 「本紙」シートをセット
mst_ws = mst_wb[MST_SHEET_MAIN]
# 行カウンタの初期値をセット
i_row = MST_SHEET_START_ROW
# ファイル名の値が空になるまで繰り返し
while mst_ws.cell(i_row, MST_SHEET_FILENAME_COL).value != None:
# ファイル名とシート名をセット
filename = OUTPUT_DIR + mst_ws.cell(i_row, MST_SHEET_FILENAME_COL).value + '.xlsx'
sheetname = mst_ws.cell(i_row, MST_SHEET_SHEETNAME_COL).value
# 行カウンタをインクリメント
i_row += 1
# 設定したシート名がなければスキップ
if sheetname not in mst_shtname_list:
print('シート名{}がありません'.format(sheetname))
continue
# 入力したファイル名でマスタExcelファイルを別名保存
mst_wb.save(filename)
# 別名保存したExcelファイルを開く
output_wb = openpyxl.load_workbook(filename)
# シート名分繰り返し
for output_ws in output_wb.worksheets:
# 入力したシート名以外の場合
if output_ws.title != sheetname:
# シートを削除
output_wb.remove(output_ws)
# 別名保存したExcelを保存
output_wb.save(filename)
# 別名保存したExcelを閉じる
output_wb.close()
# マスタExcelファイルを閉じる
mst_wb.close()
# メイン処理
if __name__ == '__main__':
# シートコピー処理呼び出し
copy_sheet()■実行結果
「フォーマットシートコピー」ツールにおける、「正常系」および「異常系」の実行結果を以下に示す。
▼正常系
・「mst_sheet.xlsx」の「本紙」シートにて、「ファイル名」と「シート名」を記入する。
・「sheetcopy.py」モジュールを実行する。
# 中身確認
$ ls
mst output sheetcopy.py
# モジュール実行
$ python3 sheetcopy.py・「output」ディレクトリにExcelファイルが作成されていることを確認。
・それぞれ対応したシートのみが存在することを確認。
▼異常系
・「本紙」シートなし
・「mst_sheet.xlsx」の「本紙」シートの名前を「aaaa」に変更する。
・「sheetcopy.py」モジュールを実行するとエラーメッセージを出力。
# 中身確認
$ ls
mst output sheetcopy.py
# モジュール実行
$ python3 sheetcopy.py
「本紙」シートがありません・存在しないシート名を指定
・「「mst_sheet.xlsx」の本紙」シートにて、「シート名」列に存在しないシート名を指定する。（赤枠部分）
・「sheetcopy.py」モジュールを実行するとエラーメッセージを出力。
# 中身確認
$ ls
mst output sheetcopy.py
# モジュール実行
$ python3 sheetcopy.py
シート名Eがありません
シート名Kがありません・シート名が存在する対象のみ作成されていることを確認。
■参考
「フォーマットシートコピー」ツールを実装するにあたり、参考とさせていただいた外部リンクおよび内部リンクを以下に示す。
▼外部リンク
・openpyxl基本操作
▼内部リンク
・GitHub
コメント