Openpyxl find and replace values-CodePudding

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"}

CodePudding user response：

# 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


###################################################################################
from Python.Packages.custom_xl_openpyxl_package_master import CustomOpenpyxl

class TestOpenPyxlPackage(CustomOpenpyxl):
    def __init__(self, _my_filename):
        CustomOpenpyxl.__init__(self, _my_filename)


_my_filename = 'Financial Sample.xlsx'
_my_src_obj = TestOpenPyxlPackage(_my_filename)

orig_val = [987888, 987889, 987890]
new_val = ['F3', 'F4', 'F5']
cleaner_map = dict(zip(orig_val, new_val))

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 _cell in _row:
            for _ in cleaner_map.keys():
                if _ == _cell.value:
                    _cell.value = cleaner_map[_]

_my_src_obj.save_wb()
print('All Done')

CodePudding user response：

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.