How to Dynamically Update Excel Cells with Pandas: A Flexible Python Solution

Introduction

In a Python community a member asked how to write a specific string into cell M4 of an Excel sheet, but the script kept writing to M2.

Implementation

The discussion highlighted that using pandas directly is simpler than repeatedly opening and closing the workbook with openpyxl. The core logic is to build a mask that selects rows where Execute == "Y", ClaimNo == "DB2482742" and 医院编码 == "P000000491", then assign the desired value to the TestResult column.

from os.path import dirname, abspath
import pandas as pd


def writeExcel_v2(filename, sheet_name, save_column_name, save_data, **kwargs):
    df = pd.read_excel(io=filename, sheet_name=sheet_name)
    res = df[df.reindex(columns=kwargs.keys()).isin(kwargs).all(axis=1)]
    row_number = res.index + 2  # Excel row number offset
    col_number = df.columns.get_indexer([save_column_name])[0] + 1
    with pd.ExcelWriter(filename, engine='openpyxl', mode='a') as excel:
        worksheet = excel.book[sheet_name]
        for row in row_number:
            worksheet.cell(row=row, column=col_number, value=save_data)
            print(f"第{row}行 第{col_number}列的值会保存为：{save_data}")

filename = dirname(dirname(abspath(__file__))) + r'\ExcelTest.xlsx'
sheetname = "DB"
kwargs = {'Execute': ['Y']}
writeExcel_v2(filename, sheetname, 'TestResult', 'test3awertwtrwelbert', **kwargs)

By passing the filtering criteria through **kwargs, the function can be reused for any combination of columns without changing the code.

Conclusion

The provided function solves the original automation problem and offers a flexible pattern for future Excel updates based on arbitrary column conditions.