You have many ways to do this. some approach that i used:
1. Brute force
Assuming "sheet" and "workbook" are defined.
header = [cell for cell in sheet['A1:XFD1'][0] if cell.value is not None and cell.value.strip() != ''] #you get all non-null columns
target_values = ['NameOfField', 'NameOfField1', 'NameOfField2'] #filter list
target_header = [cell.column for cell in header if cell.value in target_values] #get column index
data = {'OldValue1': 1, 'OldValue2': 2}
for row in sheet.iter_rows(max_row=sheet.max_row, max_col=sheet.max_column):
for cell in row:
if cell.column in target_header and cell.value in data :
cell.value = data[cell.value]
In this case, the brute force is in "sheet['A1:XFD1']". we have to check for all columns the first time. But you'll get all cells references for columns. After that, we create target_values (our columns names...) and we create a list with column index (target_header). Finally we iterated over sheet. We check if the cell's column is in the column index and check if the cell's value is in data, so we're able to change the value.
Downside:if exists cell with random whitespace outside "data area". max_row and max_column will consider that cells (iterate over blank cells).
2. Check for bundaries
You can use your own max row and max column if the data has table form(no empty space between columns, a column with "id"-> not null, not whitespace).
from openpyxl.utils import get_column_letter
def find_limit_sheet(direction):
max_limit_value = 1
while (direction(max_limit_value).value is not None) and (direction(max_limit_value).value.strip() != ''):
max_limit_value = max_limit_value + 1
return (max_limit_value - 1) if max_limit_value != 1 else 1
max_qrow = find_limit_sheet(direction=lambda increment: sheet.cell(row=increment, column=1))
max_qcolumn = find_limit_sheet(direction=lambda increment: sheet.cell(column=increment, row=1))
header = [cell for cell in sheet[f'A1:{get_column_letter(max_qcolumn)}1']] #you get all non-null columns
target_values = ['NameOfField', 'NameOfField1', 'NameOfField2'] #filter list
target_header = [cell.column for cell in header[0] if cell.value in target_values] #get column names
data = {'OldValue1': 1, 'OldValue2': 2}
for row in sheet.iter_rows(max_row=max_qrow, max_col=max_qcolumn):
for cell in row:
if cell.column in target_header and cell.value in data :
cell.value = data[cell.value]
In this case we are inside "data area" only.
3. Optional: Using Pandas
If you need more complex operation on excel data(i have to read a lots of excel in my work :( as data source). I prefer convert to pandas dataframe-> make operation -> save result .
In this case we use all the data.
from openpyxl.utils import get_column_letter
import pandas as pd
def find_limit_sheet(direction):
max_limit_value = 1
while (direction(max_limit_value).value is not None) and (direction(max_limit_value).value.strip() != ''):
max_limit_value = max_limit_value + 1
return (max_limit_value - 1) if max_limit_value != 1 else 1
max_qrow = find_limit_sheet(direction=lambda increment: sheet.cell(row=increment, column=1))
max_qcolumn = find_limit_sheet(direction=lambda increment: sheet.cell(column=increment, row=1))
header = [cell.value for cell in sheet[f'A1:{get_column_letter(max_qcolumn)}1'][0]] #you get all non-null columns
raw_data = []
for row in sheet.iter_rows(max_row=max_qrow, max_col=max_qcolumn):
row_data = [cell.value for cell in row]
raw_data.append(dict(zip(header, row_data)))
df = pandas.DataFrame(raw_data)
df.columns = df.iloc[0]
df = df[1:]
You can also use a sub-set of columns using target_data for example 2.
...
target_header = [cell.column for cell in header[0] if cell.value in target_values] #get column names
...
raw_data = []
for row in sheet.iter_rows(max_row=max_qrow, max_col=max_qcolumn):
row_data = [cell.value for cell in row if cell.column in target_header]
raw_data.append(dict(zip(header, row_data)))
df = pd.DataFrame(raw_data)
df.columns = df.iloc[0]
df = df[1:]
...
INFO
