Openpyxl find and replace values

Question

I would like to find and convert values in a column with the corresponding text. For example, at the value 987888, I would like to overwrite the text"F3". Does someone have a solution to this? I would like to say first that I'm a beginner with Python and programming in general. Down below you can find the "translation" of every code in the text

FlacDic = {987888:"F3",987887:"F4",665609:"F5",3007649:"F7",3003447:"F8",967923:"F10",967924:"F11",992892:"F21"}

Answer 1

The following should help in your scenario:

#######################################################################################
# Import openpyxl
# Note: openpyxl package provides both read and write capabilities to excel
import openpyxl


# Class definitions should use CamelCase convention based on pep-8 guidelines
class CustomOpenpyxl:

    # Initialize the class with filename as only argument
    def __init__(self, _my_file_name):
        self.my_filename = _my_file_name
        self.my_base_wb = openpyxl.load_workbook(self.my_filename, read_only=False)
        self.my_base_active_ws = self.my_base_wb.active

    # Method to save the workbook
    # No arguments to this method
    def save_wb(self):
        return self.my_base_wb.save(self.my_filename)
        
_my_filename = 'Financial Sample.xlsx'
_my_src_obj = CustomOpenpyxl(_my_filename)

# create two lists of original and new values
orig_val = [987888, 987889, 987890]
new_val = ['F3', 'F4', 'F5']
# Zip these two lists as a new dictionary to create the mapping
cleaner_map = dict(zip(orig_val, new_val))

# Iterate through the columns and rows in the worksheet
for _col in _my_src_obj.my_base_active_ws.iter_cols():
    for _row in _my_src_obj.my_base_active_ws.iter_rows():
        # for each cell in the row, if the original value (dictionary key) is found
        # then replace it with the new value (dictionary value)
        for _cell in _row:
            for _ in cleaner_map.keys():
                if _ == _cell.value:
                    _cell.value = cleaner_map[_]

# save your workbook
_my_src_obj.save_wb()
print('All Done')
#######################################################################################

Answer 2

Do you absolutely need to do it with openpyxl ? It could be done easily with pandas if you convert your data into a pandas dataframe. Or do you have formatting constrains with excel ?

As a pandas df you can do it like this

import pandas as pd

df = pd.read_excel(r'location\excelfile.xlsx')
df.loc[:,column_name] = df.replace(to_replace=['987888','987888'.....],value=['F3','F4'])
df.to_excel(r'save loc\filename.xlsx')

This should give you an idea I hope. Note that you will loose all the formatting in you original excel file.