こんにちは。
金融系業務で毎日データと奮闘している北見です。
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では、他に多くのパッケージを使用した連携処理が行えますので
次回は、色々と試してみたいと思います。
最後まで、お読み頂きありがとうございました。
