- 自動化
PythonビギナーがExcel操作を自動化してみた。
お疲れ様です。2019年入社のI.Koseiです。
今回は、業務の中でPythonを用いて自動化マクロを作成したので、その内容を共有いたします。
開発に至った経緯
とある案件において、完成図書の提出時に使用した作業手順書に、コマンドの実行時刻を記載して提出する必要がありました。
単純な作業ですが、場合によっては数万行に及ぶログファイルから該当コマンドを目視で探し、Excelに転記するのは煩わしく、この対応を数十回行う必要があったため、自動化してみようと考えました。
Pythonを使った理由
Excelの操作が必要だったため、最初はVBAでの開発を考えましたが、せっかくの機会なので以前から興味のあったPythonでできないか調べたところ、ライブラリを使えば対応できるとわかり、Pythonで開発することにしました。
実現したいこと
手順書に記されたコマンドを実行すると、ログからタイムスタンプを実績列に転記し、転記に成功した場合はチェック列を「■」に更新します。また、タイムスタンプのセル幅も表示に合わせて自動調整します。
実現に必要な処理
上記を実現するために、以下の処理を作成しました。
- 複数のログファイルを統合し、タイムスタンプを時系列ごとに整理すること。
- 手順書のコマンドが記載されている列(E列)を参照、統合ログから一致するコマンドを探し、
そのタイムスタンプを実績列(H列)に転記すること。 - タイムスタンプの幅に合わせてセルの幅を調整すること。
- 実行時に作成した統合ログは終了時に削除すること。
構想段階では単純にExcelのコマンドをキーにログファイルから同じ文字列を探し、該当した行のタイムスタンプを転記できればいいと考えていましたが、実際に開発を進めていく中で新しい課題がでてきました。
開発中の課題
簡単に構想段階で想定していたプログラムを作り、実行したところ、以下のように転記に失敗することがありました。
よくよくログファイルを確認したところ、特定の条件下でコマンドの間にスペースが入ってしまい、
完全一致では取得できない事象を確認しました。
これでは想定していた処理だけではタイムスタンプの転記がうまくできません。
あまり論理的ではありませんが、転記できなかったセルに対しては、
あらかじめ用意した正規表現で部分一致検索を行い、無理やり取得することにしました。
最終的に以下の5つのモジュールに分けて処理を行うことにしました。
開発準備
そもそもPCにPythonが入っていなかったため、Pythonをインストールするところからでした。
ここで自分はAdd Python 3.x to PATHにチェックを入れずにインストールしてしまい、
数日間、パッケージのインストールに使うPIPコマンドがうまく機能せず、悩まされることになりましたので、
必ずチェックを入れてインストールするようにしましょう。
ログファイルの統合
今回の案件では手順の都合上、TeraTermで取得したログも複数のファイルに分かれていました。想定している処理では一つのログファイルにタイムスタンプが時系列順で並んでいる必要がありますので、まずはその処理を作成しました。
すべての処理を解説すると長くなってしまうので、一部抜粋して紹介します。
有効な行の抽出
ログはものによっては4万行ほどのテキスト量がありますが、転記に必要なのはコマンドを実行した時間だけです。
今回の例では機器名の「@test」、特定のコマンド実行時の[yes no]の確認のどちらかを行った行だけ有効な行として抽出します。
連続する重複行の削除
今回の手順書では同じコマンドを連続して二回実行することはありませんでしたが、ログの中では同一のコマンドを二回連続して実行していることがありました。実際の作業時にコマンドの実行の失敗や、念のため確認として二回実行することがありました。完成図書としては必要ないため削除する処理を入れました。
main関数で統合ログファイルの作成
ここまでに用意してきた関数をmain関数にまとめて実行し、処理を完成させます。
これでログファイルを一つのファイルに統合し、
タイムスタンプを時系列順に並び替える処理が完成しました。
タイムスタンプの転記
続いて、統合したログと手順書のコマンドを使ってタイムスタンプの転記を行っていきます。
Pythonを使ってExcelを操作するには、「openpyxl」(オープンパイエクセルと読むそうです)というライブラリが
必要になるため、まずはこちらをインストールしていきます。
ライブラリをインストールできたら忘れずに読み込みましょう。
これでExcelを操作する関数やクラスが使えるようになります。
手順書は拠点によって若干違い、変更も多々あるため、
終了の条件はE列の空白セルが連続で何回続いたかで判定するようにしました。
search_keyは、ExcelのE列にある値を取り出しています。
ここで取り出した値が、後にログファイル内で検索するためのキーとして使われます。
ログファイルから一致した行のタイムスタンプを取得してH列へ、G列へは■を記載します。
rowのカウントを増やしながら処理を繰り返します。
MAX_EMPTY_COUNTは20の定数のため、空白が20回繰り返すまで処理を続けます。
Excelからセルを読み取るwsオブジェクトはworksheetの略で、先ほど読み込んだopenpyxlを利用しています。
プログラムのエントリーポイント作成
main.pyを作成し、これまでに作ったモジュールのエントリーポイントとします。
上記で紹介したログを統合する処理と、ログからタイムスタンプを転記する処理に加え、
例外処理と、最後に統合したログを削除する処理を一つにまとめます。
exeファイルの作成
せっかく作ったので、同じ案件に従事するメンバーも使えるようにexeファイルにして配ることにしました。
ビルドにはpyinstallerというライブラリを使いました。
一つのexeファイルだけで動くように --onefileオプションを付けてビルドします。
何をするファイルなのか想像できる名前を付けて完成です。
終わりに
今回、初めてPythonに触れました。
最初は書き方が分からない部分もありましたが、試行錯誤を重ねながら、
自力で目標としていた動作をするマクロを作成できてよかったです。
また、開発の中でコードを読みやすくリファクタリングしたい場面があり、
その際には社内AIにも手を貸してもらいました。
詳しくは、こちらの記事をご参照ください。
社内AI：AIVolutionX（通称あいちゃん）
AIVolutionXの中身の話 | 記事一覧 | エクシオ・デジタルソリューションズ株式会社
【後継機】Excellia (エクセリア)
【生成 AI】Excellia を自社向けに提供開始 ～AIVolutionX からの進化と業務利用への本格展開～ | 記事一覧 | エクシオ・デジタルソリューションズ株式会社
余談ですが、今回のマクロの作成と併せてPython3エンジニア認定基礎試験の資格を取得しました。
ほとんど開発を終えてから資格勉強に着手してしまったので、後からコードを見返してみると、
もう少し早い段階から並行して学習しておけばよかったと感じました。
特に、PythonにはPEP8と呼ばれるお作法がまとめられたドキュメントがあるのですが、
今回のコーディング時にはこちらは全く意識できていませんでした…
次回、自動化マクロを作成するときは、この点にも注意したいです。
参考：pep8-ja 1.0 ドキュメント
https://pep8-ja.readthedocs.io/ja/latest/
