この記事は セゾンテクノロジー Advent Calendar 2024 6日目の記事です。
シリーズ2は HULFT10 のエンジニアによる投稿をお届けします。
はじめに
初めまして、セゾンテクノロジーの齋藤です。
今回はHULFT10リリース記念アドベントカレンダーとのことで、新人の身ですが記事を書こうと思います。
昨年10月に別業種から転職をしたばかりで、エンジニア歴は2年目になります。技術的な間違いや指摘事項などあるかもしれませんが、どうかご容赦ください。
どんな記事を書くのか
PythonでExcel作業の自動化について書こうと思います。
今回のプロジェクトの中で、膨大な量のExcel作業のタスクがありました。手作業で行うには気が遠くなるほどのボリュームで、なおかつ単調な作業だったので自動化するプログラムを作成してみました。
備忘録的な魂胆もありつつ、記事を書けたらと思います。
"HULFT10リリース記念アドベントカレンダー"とか言いながらHULFTとあまり関係のない記事を書こうとしてますが、新人なので大目に見てください。
必要なもの
まずはPCを用意してください。
それから用意したPCにテキストエディターとPythonをインストールしてください。
テキストエディターはVSCodeがおすすめです。無料で使えて拡張性も高く、便利な拡張機能も豊富です。Pythonは公式サイトからインストーラーをダウンロードして実行すれば完了です。
上記が準備できたら、次にライブラリのインストールです。
今回はエクセルを操作するライブラリとしてpandasを使用しました。
先ほどのPythonのインストールと同時にpipコマンドもインストールされているはずなので、
pip install pandas
を実行すれば、インストール可能です。
実際にプログラムを書いてみる
エクセルファイルの読み込み
# エクセルファイルの読み込み、withとセットで使うかclose処理を行う
excel = pandas.ExcelFile('failname')
# シート名のリスト取得
sheet_list = excel.sheet_names
# 先頭のシートのデータを取得
dataframe = excel.parse(sheet_list[0])
ファイル名はパスを指定してください。
また、取得したデータはDataframeというデータ型で返されます。
データを加工する
DataFrameはエクセルのシートをそっくりそのまま格納しているイメージです。
DataFrame.ilocとDataFrame.locで参照できます。
# DataFrameイメージ
ID Name Age City Gender job Height
1 101 Alice 25 Tokyo Female Engineer 165
2 102 Bob 30 New York Male Doctor 180
3 103 Charlie 35 San Francisco Male Artist 170
# 行と列をインデックスで指定
dataframe.iloc[0, 1] # Alice が取得できる
# ラベルで指定できる
dataframe.loc[1, 'Name'] # Alice が取得できる
# locは条件検索なども可能
dataframe.loc[DataFrame['Age'] >= 30]
# 指定した列の削除
dataframe.drop(columns='Height')
dataframe.drop(index=4)
# 指定した順番に列を並び替え
dataframe[['ID', 'Name', 'City', 'job', 'Age']]
# シート同士の結合
combined_dataframe = pandas.concat([dataframe1, dataframe2]):
# データの欠損をチェック
pandas.isnull(dataframe)
# 反復処理、for文と組み合わせる
dataframe.iterrows()
for index, row in dataframe.iterrows()
この他にもDataFrameはいろいろな操作が可能です。
ググるなりAIに聞いてみるなりするといいことがあるかもしれません。
ちなみにAIに聞いてみたら、以下の答えが返ってきました。
データの作成
データの表示
列の選択
行の選択
条件によるフィルタリング
新しい列の追加
列の削除
行の削除
欠損値の処理
集約と統計量の計算
データのソート
インデックスの設定とリセット
データの結合とマージ
データのエクスポート
データの変形
時系列データの操作
文字列操作
データのグルーピング（groupby）
ピボットテーブルの作成
データの結合（concatやmerge）
割となんでもできそうです。
データの出力
DataFrameで操作したデータをエクセルに保存します。
# DataFrameをExcelファイルのシートに書き出す。
dataframe.to_excel('filename', sheet_name='Results', index=False)
引数のindexはエクセルファイルにインデックスを出力するかどうかを選べます。Falseの場合は出力されません。
また、エクセルのシート上で出力する場所なんかも指定できます。startrowとstartcolで開始する行と列を指定できます。
基本的な操作程度でしたが、使い方次第で柔軟な処理ができるかと思います。
実際に作成したプログラム
ソースの一部を削除、または変更しています。
import os
import pandas as pd
from openpyxl import load_workbook
from wcwidth import wcswidth
def make_importfile(input_folder, output_file):
sheet_index_end = int(input("Enter max_sheet_index: "))
output_wb_sheet = output_wb = load_workbook(output_file)
# inputファイルの各セルの内容を保存するための変数
input_cells = {"C":"", "D":"", "E":"", "H":"", "K":"", "L":"", "M":"", "N":"", "O":"", "P":"", "Q":""}
columns = ['C', 'D', 'E', 'K', 'L', 'M', 'N', 'O', 'P', 'Q']
output_line = 2
i = 0
j= 0
# 指定されたディレクトリ内のすべてのExcelファイルをリスト化（入力ファイル）
excel_file_list = [f for f in os.listdir(input_folder) if f.endswith('.xlsx') or f.endswith('.xls')]
for excel_file in excel_file_list:
# ファイルパスの作成
file_path = os.path.join(input_folder, excel_file)
excel = pd.ExcelFile(file_path)
sheet_name_list = excel.sheet_names
# エクセルのシートごとに読み込む
for i in range(sheet_index_end):
sheet_data = excel.parse(file_path, sheet_name=i, usecols="A:L")
sheet_data = sheet_data.iloc[8:1001]
# 一行ずつ取得
for index, row_of_sheet_data in sheet_data.iterrows():
if not pd.isnull(row_of_sheet_data.iloc[11]):
output_wb_sheet[f"A{output_line}"] = test_suite
output_wb_sheet[f"B{output_line}"] = cell_b_number + str(output_line - 1).zfill(4)
output_wb_sheet[f"I{output_line}"] = row_of_sheet_data.iloc[11].replace("\n", "")
output_wb_sheet[f"J{output_line}"] = 1
# inputファイルの各セルの内容が存在すれば格納変数を更新する
for j, clmun in enumerate(columns, start=1):
if not pd.isnull(row_of_sheet_data.iloc[j]):
output_cells[clmun] = row_of_sheet_data.iloc[j].replace("\n", "")
if not pd.isnull(row_of_sheet_data.iloc[10]):
output_wb_sheet[f"C{output_line}"] = output_cells["C"]
output_wb_sheet[f"D{output_line}"] = output_cells["D"]
output_wb_sheet[f"E{output_line}"] = output_cells["E"]
output_wb_sheet[f"H{output_line}"] = '\n'.join([str(output_cells[col]) for col in ["K", "L", "M", "N", "O", "P", "Q"] if output_cells[col]])
elif not pd.isnull(row_of_sheet_data.iloc[9]):
output_wb_sheet[f"C{output_line}"] = output_cells["C"]
output_wb_sheet[f"D{output_line}"] = output_cells["D"]
output_wb_sheet[f"E{output_line}"] = output_cells["E"]
output_wb_sheet[f"H{output_line}"] = '\n'.join([str(output_cells[col]) for col in ["K", "L", "M", "N", "O", "P"] if output_cells[col]])
elif not pd.isnull(row_of_sheet_data.iloc[8]):
output_wb_sheet[f"C{output_line}"] = output_cells["C"]
output_wb_sheet[f"D{output_line}"] = output_cells["D"]
output_wb_sheet[f"E{output_line}"] = output_cells["E"]
output_wb_sheet[f"H{output_line}"] = '\n'.join([str(output_cells[col]) for col in ["K", "L", "M", "N", "O"] if output_cells[col]])
elif not pd.isnull(row_of_sheet_data.iloc[7]):
output_wb_sheet[f"C{output_line}"] = output_cells["C"]
output_wb_sheet[f"D{output_line}"] = output_cells["D"]
output_wb_sheet[f"E{output_line}"] = output_cells["E"]
output_wb_sheet[f"H{output_line}"] = '\n'.join([str(output_cells[col]) for col in ["K", "L", "M", "N"] if output_cells[col]])
elif not pd.isnull(row_of_sheet_data.iloc[6]):
output_wb_sheet[f"C{output_line}"] = output_cells["C"]
output_wb_sheet[f"D{output_line}"] = output_cells["D"]
output_wb_sheet[f"E{output_line}"] = output_cells["E"]
output_wb_sheet[f"H{output_line}"] = '\n'.join([str(output_cells[col]) for col in ["K", "L", "M"] if output_cells[col]])
elif not pd.isnull(row_of_sheet_data.iloc[5]):
output_wb_sheet[f"C{output_line}"] = output_cells["C"]
output_wb_sheet[f"D{output_line}"] = output_cells["D"]
output_wb_sheet[f"E{output_line}"] = output_cells["E"]
output_wb_sheet[f"H{output_line}"] = '\n'.join([str(output_cells[col]) for col in ["K", "L"] if output_cells[col]])
elif not pd.isnull(row_of_sheet_data.iloc[4]):
output_wb_sheet[f"C{output_line}"] = output_cells["C"]
output_wb_sheet[f"D{output_line}"] = output_cells["D"]
output_wb_sheet[f"E{output_line}"] = output_cells["E"]
output_wb_sheet[f"H{output_line}"] = output_cells["K"]
elif not pd.isnull(row_of_sheet_data.iloc[3]):
output_wb_sheet[f"C{output_line}"] = output_cells["C"]
output_wb_sheet[f"D{output_line}"] = output_cells["D"]
output_wb_sheet[f"E{output_line}"] = output_cells["E"]
elif not pd.isnull(row_of_sheet_data.iloc[2]):
output_wb_sheet[f"C{output_line}"] = output_cells["C"]
output_wb_sheet[f"D{output_line}"] = output_cells["D"]
elif not pd.isnull(row_of_sheet_data.iloc[1]):
output_wb_sheet[f"C{output_line}"] = output_cells["C"]
else:
output_wb_sheet[f"C{output_line}"] = output_cells["C"]
output_wb_sheet[f"D{output_line}"] = output_cells["D"]
output_wb_sheet[f"E{output_line}"] = output_cells["E"]
output_line += 1
else:
break
# 出力ファイルを保存
output_excel.to_excel(output_file)
# シート名の出力（確認用）
sheet_name_width = wcswidth(sheet_name_list[i])
print(f"{str(i + 1).zfill(2)}p / {sheet_name_list[i] + ' ' * (40 - sheet_name_width)} OK")
実際に書いたソースコードです。
ネスト深＆汚い＆見づらいで申し訳ありません。
まとめ
以上、エクセル作業の自動化でした。
初めてPythonを書きましたが、普段学習しているC言語と比べると、だいぶ書きやすかったです。
これからは単調で退屈な作業はコンピュータに任せてしまおうと思います。