【Python】EXCELのセルにある文字列と同じ名前のJSONファイルを探してその内容と置き換える方法

概要

• EXCELファイルの指定列にある文字列を取得し、同じ名前のJSONファイルを探してその内容を読み込み、セルの値をそのJSONの内容で置き換える、という方法を紹介します。
• 以下のようなエクセルがあり、また記載されているJSONファイルが存在している、という前提です。

サンプルコード解説

import os
import openpyxl
import json

def replace_content_in_excel(excel_path, column_index, json_files_dir):
    """
    EXCELファイルの指定列にある文字列を、同名のJSONファイルの内容で置き換える関数。

    Args:
        excel_path (str): EXCELファイルのパス。
        column_index (int): 置き換える列のインデックス。
        json_files_dir (str): JSONファイルが格納されているディレクトリのパス。
    """
    wb = openpyxl.load_workbook(excel_path)
    ws = wb.active

    for row in ws.iter_rows(min_row=2):
        # 指定した列のセルの値を取得
        cell_value = row[column_index - 1].value

        # JSONファイルのパスを作成
        json_file_path = os.path.join(json_files_dir, cell_value)

        # JSONファイルの内容を読み込んで、セルの値を置き換える
        if os.path.exists(json_file_path):
            with open(json_file_path, "r", encoding="utf-8") as json_file:
                json_content = json.load(json_file)
                json_string = json.dumps(json_content, ensure_ascii=False)
                row[column_index - 1].value = json_string

    wb.save(excel_path)

# 置き換えを行うExcelファイルのパスとJSONファイルが格納されているディレクトリのパスを指定
excel_file_path = "./output_excel.xlsx"
json_files_dir = "./sample_json_files"

# EXCELファイルの指定列にある文字列を、同名のJSONファイルの内容で置き換える
replace_content_in_excel(excel_file_path, 1, json_files_dir)

openpyxlライブラリについて

• 上記コードでは、openpyxlライブラリを使用しています。Excelファイルを操作するために必要です。
• まだインストールしていない場合は、以下のコマンドを使用してインストールが必要です。

pip install openpyxl 

replace_content_in_excel関数について

• ここでは、指定されたExcelファイルの特定の列にある文字列を、同じ名前のJSONファイルの内容で置き換える処理を行っています。引数としてExcelファイルのパス、置き換える列のインデックス、JSONファイルが格納されているディレクトリのパスを受け取る。順序は以下の通り。
• openpyxl.load_workbook関数を使用して、指定したExcelファイルを読み込む。読み込んだワークブックオブジェクトをwbという変数に格納。その後、アクティブなシートを取得し、wsという変数に格納。
  • ちなみに、wb.sheetnamesで複数のシート名をリストで取得できたりもします。
• ws.iter_rows(min_row=2)は、シートの2行目(1行目はヘッダーがある前提のため)から最終行までの範囲を反復処理するためのイテレータを返します。
• row[column_index - 1].valueは、指定した列のセルにアクセスするための記法(今回の場合、代入された変数cell_valueには、001_output.jsonなどが入ります)。プログラムでExcelの列にアクセスする際には、A列が1、B列が2、C列が3...というように、列のインデックスは通常1から始まります。したがって、column_indexが1の場合はrow[0]となり、その行の最初のセル（A列）を表します。ちなみに、ここに-1で行わないと以下のようにインデックスエラーになります。

IndexError: tuple index out of range

• os.path.join関数を使用して、JSONファイルが格納されているディレクトリのパスとセルの値を結合。
• os.path.exists関数を使用して、指定したJSONファイルが存在するかどうかを確認し、存在する場合は、open関数を使用してJSONファイルを読み込み、json.load関数を使用してJSONデータを読み込みます。その後、json.dumps関数を使用してJSONデータを文字列に変換し、セルの値を置き換えます（ここは補足で後述）。
• 最後に、save()メソッドで変更を加えたワークブックを指定したExcelファイルのパスに保存します(既に存在する場合は上書き)。

補足1：JSONオブジェクトは、セルの値として直接設定できない

with open(json_file_path, "r", encoding="utf-8") as json_file:
    json_content = json.load(json_file)
    row[column_index - 1].value = json_content

• 上記だと以下のValueErrorになりました。

line 23, in replace_content_in_excel
    row[column_index - 1].value = json_content
  File "/opt/homebrew/lib/python3.10/site-packages/openpyxl/cell/cell.py", line 218, in value
    self._bind_value(value)
  File "/opt/homebrew/lib/python3.10/site-packages/openpyxl/cell/cell.py", line 187, in _bind_value
    raise ValueError("Cannot convert {0!r} to Excel".format(value))

• これはopenpyxlパッケージでは、直接JSONオブジェクトをセルの値として設定することはできないためらしい。JSONオブジェクトを文字列に変換してセルの値として設定すると無事に実行できました。

json_content = json.load(json_file)
json_string = json.dumps(json_content, ensure_ascii=False) # JSONオブジェクトを文字列に変換
row[column_index - 1].value = json_string

補足2：エスケープシーケンス（例："\uXXXX"）で表現された場合

• 上記で加えたjson.dumpsについて、ensure_ascii=Falseがない場合は、エクセル上でエンコードエラーになる。以下を正しくデコードして表示すると、「神奈川県」になります。

\u795e\u5948\u5ddd\u770c

• json.dumps()関数のensure_asciiパラメータはデフォルトではTrueです。これをFalseに設定することで、JSONのエンコード時にASCIIエンコーディングを適用しないようになります。これにより、日本語文字をエスケープせずにJSON文字列に変換できるというわけです。これでエクセルにも日本語が正常に表示されました。