PythonでExcelファイルを複数シートに跨って、特定のセルのみ集計を行うコード

概要

あるExcelのシートを全て読み込み、特定の行を抜き出し、
そこだけシート毎に集計をとるコードを作成します。

作成経緯

現在のプロジェクトでは、Excelファイルで各作業者の進捗を管理しており、
シート毎にそれを毎日集計する必要があります。
ただマクロを作るとファイルの更新に支障が出てしまい、
集計用のExcelを作成した場合、ファイルで行挿入などに対応ができなくなります。
そのためPythonで集計システムを作成して、毎日工数を使わずに集計ができるように、システムを作成する。

コード全文

Integration_masterdata01=pd.DataFrame(index= [], columns=[])
path = '読み込ませるファイルの指定'
files = os.listdir(path)
files_range = sum(os.path.isfile(os.path.join(path,name)) for name in os.listdir(path))
file_path=path+"/"+files[0]

master_data10=pd.DataFrame(index= [], columns=[])

for i in range(6,21):
    transcript_table01 =  pd.read_excel(file_path,sheet_name=i)

    input_file = pd.ExcelFile(file_path)
    sheet_names = input_file.sheet_names
    print(sheet_names[i])
    
  
    
    transcript_table01_column_list=transcript_table01.iloc[0].to_list()
    transcript_table01.columns = transcript_table01_column_list
    transcript_table01 = transcript_table01.drop(transcript_table01.index[0])
    
    transcript_table01["シート名"] = sheet_names[i]

    
    master_data01= pd.DataFrame(index= [], columns=[])
    master_data01["ADOID"] = transcript_table01["Azure Dev Ops\nID"]
    master_data01["シート名"] = transcript_table01["シート名"]

    master_data10=master_data10.append(master_data01)
 
master_data10=master_data10.dropna(subset=['ADOID'])


# 最終結果を書き込み
file_name01="ファイル名.xlsx"
rows = dataframe_to_rows(master_data10, index=False, header=True) # openpyxlのユーティリティを使用

wb = openpyxl.load_workbook(file_name01)
ws = wb.worksheets[0]

# ファイルを初期化
for row in ws:
    for cell in row:
        cell.value = None

#別名で保存
wb.save(file_name01)

【解説】

●インポート

ああああ

必要なパッケージを読み込む

●ファイルの読み込みまで

Integration_masterdata01=pd.DataFrame(index= [], columns=[])
path = '読み込ませるファイルの指定'
files = os.listdir(path)
files_range = sum(os.path.isfile(os.path.join(path,name)) for name in os.listdir(path))
file_path=path+"/"+files[0]

master_data10=pd.DataFrame(index= [], columns=[])

最初にからのテーブルを作成する。

その後に読み込むExcelファイルがあるフォルダを指定する。なおここでは絶対パスで指定をする。

その後フォルダから任意の番号のファイルを読み込むようコード記載する。
理由としてはフォルダ内のファイルを入れ替えるだけで、即座に集計ができるようにするため、この形式をとります。

最後に再度空のテーブルを作成する

●FOR文の最上段（シートの読み込み）

for i in range(6,21):
    transcript_table01 =  pd.read_excel(file_path,sheet_name=i)

    input_file = pd.ExcelFile(file_path)
    sheet_names = input_file.sheet_names
    print(sheet_names[i])

ファイル内で読み込むシートの範囲(枚数）を指定する。
その後ファイル内のシートの名称を全て取得する。
最後に現在読み込んだシートをプリント文で表示する。

●FOR文の下段（データクレンジングなど）

    transcript_table01_column_list=transcript_table01.iloc[0].to_list()
    transcript_table01.columns = transcript_table01_column_list
    transcript_table01 = transcript_table01.drop(transcript_table01.index[0])
    
    
    master_data01= pd.DataFrame(index= [], columns=[])
    master_data01["ID"] = transcript_table01["Azure Dev Ops\nID"]
    master_data01["シート名"] = sheet_names[i]

   #ここに追加したい行の処理を入れる
    
    master_data10=master_data10.append(master_data01)

Excel内の２行目をPandasの行にします。
この処理はExcel内で最初の行が空白である場合、
セルが全て空白になってしまいPandasでセルの指定が出来なくなるので、
ベースにしたい行を一度リストに入れ、そのリストで上書きする処理をいれます。
その後ベースにした行を消します。

一度からのテーブルを作り、そこに読み込んだシートの中から、
抽出したい列を指定して、空のテーブルに追加します。
ここでは「ID」という項目を作成し、中身は「Azure Dev Ops\nID」という項目を抽出します。
また読み込んだシート名も記載したいので、「シート名」という項目を作成し、そこは関数「sheet_names[i]」で指定したテキストを入力します・

また余談ですがセル内に改行がある場合「\n」でテキスト区切らないと、
セルを読み込んでくれません。

最後にFOR文に入る前に作成したテーブルに、
抽出したテーブルを追加します。

●データ保存

master_data10=master_data10.dropna(subset=['ADOID'])

# 最終結果を書き込み
file_name01="ファイル名.xlsx"
rows = dataframe_to_rows(master_data10, index=False, header=True) # openpyxlのユーティリティを使用

wb = openpyxl.load_workbook(file_name01)
ws = wb.worksheets[0]

# ファイルを初期化
for row in ws:
    for cell in row:
        cell.value = None

#別名で保存
wb.save(file_name01)

シートを全て追加したテーブルに対して、IDが重複している物を削除する。
その後ipynbと同じフォルダにある保存したいExcelファイルの名称をしてして、そこの最初のシートに保存します。

また保存する際以前の書き込みなどがあるとトラブルの元になるので、
一度保存先のシートを初期化します。

●最後に

以上がExcelファイルから複数シートに跨り、任意の列を抽出するやり方です。
後は全てのシートを纏めたテーブルで合計を取ってもいいですし、
ピポットテーブルに変換して、日付毎の値をとることもできます。

このやり方でしたらファイルを入れ替えるだけで、集計が可能となりますので、作業時間はかなり削れると思います。

是非お試しを！