こんにちは。
金融系業務で毎日データと奮闘している北見です。
Excelファイルの操作は、VBAで行うことが多いと思います。
今回は、人工知能(AI)やIoTの分野でも使われることが多く、人気の高まっているPythonで
Excelファイル内のデータを一括置換してみたいと思います。
【やりたい事】
最近の業務で、DBの制約が変わったため
・JUnitで使用するテストデータ
・比較データ(Excelファイル)
を、修正しなければならない事がありました。
初期段階なら手作業もありですが、ある程度工程が進んでいると、量も多く時間がとてもかかります。
そんな時には、Pythonで一括置換できると、作業効率も格段にアップすると思います。
【イメージ】
カラム:test_flgに「not null」制約が追加されたため、特定テーブルの「test_flg」に値を入れたい
環境使用言語等
さて、今回の環境は以下となります。
・Windows10
・Python
Ver 3.7.5
・ライブラリ/モジュール
openpyxl :Excelの読み書きなどを行うライブラリ
glob(標準):指定されたパターンにマッチするファイルパス名を取得するモジュール
os(標準) :OSに依存しているさまざまな機能を利用するためのモジュール
環境構築
「Python」のインストールについて
過去記事に記載の「2.Pythonのインストール」部をご参考ください。
PL/Pythonを利用して統計処理用のデータを取得する
「openpyxl」のインストールについて
windowsコマンドプロンプトで、下記のコマンドを実行するとインストールできます。
pip install openpyxl
Excelファイルの一括置換(ソース)
ライブラリのインポート
まずは、import文を使用して、今回使用するライブラリ/モジュールをインポートします。
import glob import os import openpyxl
対象パス、対象シート、対象項目、置換文字の指定
置換対象とする各項目について、今回はソース内で指定します。
#①対象ファイルのパス path = 'C:\python' #②対象ファイル種別 fileType = '*.xlsx' #③置換対象としたいシート名 sheetName = ['table_bbb','table_ccc'] #④置換対象項目名 tgtItem = ['test_flg'] #⑤置換後データ changDate = 'aabbcc'
ディレクトリ配下のパス取得
globモジュールは、第1引数にパスの文字列を指定することで、ファイルパスを文字列のリスト形式で取得ができます。
※第1引数へ、osモジュール(os.path.join)で、パス(①対象ファイルのパス)とファイル種別(②対象ファイル種別)を結合した「C:\python\*.xlsx」を指定。
#「①対象ファイルのパス」配下にあるExcelファイルのパスを出力 print("■検索対象ファイル") print(glob.glob(os.path.join(path,fileType ))) #「①対象ファイルのパス」配下にある「xlsx」ファイル数分ループ for book in glob.glob(os.path.join(path, fileType)):
該当セルを置換
「openpyxl」を使用し、以下の流れで置換します。
ブックの取得⇒シートの取得⇒該当セルの特定・置換⇒ブックの保存
print("■対象ファイル") print(book) bookFlg=0 #ブックの取得 #openpyxl.load_workbook('Excelファイルのパス') actBook = openpyxl.load_workbook(book) #シート数分ループ for actSheetName in actBook.sheetnames: print("■対象シート") print(actSheetName) count = 0 #シート名の判定(「③置換対象としたいシート名」との比較) if actSheetName in sheetName: #該当シートの最大行を取得 maxRow = actBook[actSheetName].max_row #アクティブシートを取得 #ブック変数[シート名] actSheet = actBook[actSheetName] #置換対象項目行のループ #for 行変数 in シート変数.iter_rows(min_row = 2(開始行) ,max_row=2(終了行)) for row in actSheet.iter_rows(min_row=2,max_row=2): #for セル変数 in 行変数 for cellRow in row: #置換対象項目の判定(「④置換対象項目名」との比較) if cellRow.value in tgtItem: #置換対象項目列のループ #for 列変数 in シート変数.iter_col(min_row = 3(開始行),max_row=対象シートで値が入っているMAX行数(終了行) #,min_row=置換対象の列(開始列),max_row=置換対象の列(終了列)) for col in actSheet.iter_cols(min_row=3, max_row=maxRow, min_col=cellRow.column, max_col=cellRow.column): #for セル変数 in 列変数 for cellCol in col: #対象セルに「⑤置換後データ」を設定 #セル変数.value=置換文字 cellCol.value=changDate count+=1 bookFlg=1 print(str(count) + "件置換しました。") #ブックを保存 if bookFlg == 1: #ブック変数.save(Excelファイルのパス) actBook.save(book) else: actBook.close
完成系(ソース)
以下、完成系のソースです。ここでは「test.py」というファイル名としました。
import glob import os import openpyxl #①対象ファイルのパス path = 'C:\python' #②対象ファイル種別 fileType = '*.xlsx' #③置換対象としたいシート名 sheetName = ['table_bbb','table_ccc'] #④置換対象項目名 tgtItem = ['test_flg'] #⑤置換後データ changDate = 'aabbcc' #「①対象ファイルのパス」配下にあるExcelファイルのパスを出力 print("■検索対象ファイル") print(glob.glob(os.path.join(path,fileType ))) #「①対象ファイルのパス」配下にある「xlsx」ファイル数分ループ for book in glob.glob(os.path.join(path, fileType)): print("■対象ファイル") print(book) bookFlg=0 #ブックの取得 #openpyxl.load_workbook('Excelファイルのパス') actBook = openpyxl.load_workbook(book) #シート数分ループ for actSheetName in actBook.sheetnames: print("■対象シート") print(actSheetName) count = 0 #シート名の判定(「③置換対象としたいシート名」との比較) if actSheetName in sheetName: #該当シートの最大行を取得 maxRow = actBook[actSheetName].max_row #アクティブシートを取得 #ブック変数[シート名] actSheet = actBook[actSheetName] #置換対象項目行のループ #for 行変数 in シート変数.iter_rows(min_row = 2(開始行) ,max_row=2(終了行)) for row in actSheet.iter_rows(min_row=2,max_row=2): #for セル変数 in 行変数 for cellRow in row: #置換対象項目の判定(「④置換対象項目名」との比較) if cellRow.value in tgtItem: #置換対象項目列のループ #for 列変数 in シート変数.iter_col(min_row = 3(開始行),max_row=対象シートで値が入っているMAX行数(終了行) #,min_row=置換対象の列(開始列),max_row=置換対象の列(終了列)) for col in actSheet.iter_cols(min_row=3, max_row=maxRow, min_col=cellRow.column, max_col=cellRow.column): #for セル変数 in 列変数 for cellCol in col: #対象セルに「⑤置換後データ」を設定 #セル変数.value=置換文字 cellCol.value=changDate count+=1 bookFlg=1 print(str(count) + "件置換しました。") #ブックを保存 if bookFlg == 1: #ブック変数.save(Excelファイルのパス) actBook.save(book) else: actBook.close
一括置換の実施
・「①対象ファイルのパス」ディレクトリへ下記ファイルを配置しています。
各ファイルのシートは下記(データの内容は【イメージ】と同様)
test1.xls:table_aaa、table_bbb ⇒置換対象あり
test2.xls:table_ccc、table_ddd ⇒置換対象あり
test3.xls:table_eee ⇒置換対象なし
プログラムの実行
ソースファイルのダブルクリックまたは、コマンドプロンプトへソースファイルをドラッグ&ドロップして実行できます。
置換対象のシート、項目のあるファイルのみが更新されました。
最後に
openpyxlを使用してエクセル操作を行いましたが、とても簡単に置換ができました。
VBAと比べ、インストール等の手間がかかりますが、
個人的にはVBAよりPythonの方がソースが理解しやすいと思いました。
ぜひ試してみてください!
また、Pythonでは、他に多くのパッケージを使用した連携処理が行えますので
次回は、色々と試してみたいと思います。
最後まで、お読み頂きありがとうございました。