こんにちは。
金融系業務で毎日データと奮闘している北見です。

Excelファイルの操作は、VBAで行うことが多いと思います。
今回は、人工知能(AI)やIoTの分野でも使われることが多く、人気の高まっているPythonで
Excelファイル内のデータを一括置換してみたいと思います。

【やりたい事】

  • 特定フォルダ配下にある、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では、他に多くのパッケージを使用した連携処理が行えますので
    次回は、色々と試してみたいと思います。

    最後まで、お読み頂きありがとうございました。

    北見
    CSVIT事業部 北見
    Pythonについて、色々と見る機会がありExcelの操作ができるのは面白いなと思い記事を作成しました。