Openpyxl: Find string in Row then add data to column 45, 46, 47 of the same row

Question

My columns are fixed and should not move. The rows are ever changing. I need to find a string 'Bond Fund' and each row this is found needs to add data to columns 45, 46, 47.

I have tried the following code and was not provided syntax errors but when opening the excel sheet no value is added to columns noted.

import openpyxl
from openpyxl.utils.cell import get_column_letter
from datetime import datetime

fname = input("Name of workbook:")
wb = openpyxl.load_workbook('Import ' + fname + '.xlsx', data_only=True)
ws1 = wb.worksheets\['Import'\]

for row in ws1.iter_rows():
    for cell in row:
        if cell.value == "Bond Fund":
            ws1.cell(row=cell.Row, column=45).value = 'SubClass'
            ws1.cell(row=cell.Row, column=46).value = 'Bond Fund'
            ws1.cell(row=cell.Row, column=47).value = 'Research'

I expected the code to work like find and replace which I have successfully used such as

for col in ws1.iter_cols(min_col=1, min_row=1, max_col=100):
    for cell in col:
        if cell.value == 'Bond':
            cell.value = 'Bond Fund'

Solution to my problem above:

intermuni= 'Muni California Intermediate'
for _idx, _ival in enumerate(ws1.values, start=1):
        if str(intermuni) in str(_ival):
            ws1.cell(row=_idx, column=45).value = 'SubClass'
            ws1.cell(row=_idx, column=46).value = 'Muni Single State Interm'
            ws1.cell(row=_idx, column=47).value = 'Priority Buy'
            ws1.cell(row=_idx, column=48).value = 'Can Sell'
            ws1.cell(row=_idx, column=49).value = 'SubClass'
            ws1.cell(row=_idx, column=50).value = 'Muni National Interm'
            ws1.cell(row=_idx, column=51).value = 'Priority Buy'
            ws1.cell(row=_idx, column=52).value = 'Can Sell'

New problem: #I want to use a dictionary in the above if statement to avoid having multiple if elif statements. Is this possible?

#First thought I created the dictionary. Then use assetClass in if str(assetClass) in str(_ival):

assetClass = {'Large Value':'QVAL', 'Large Blend':'VONE','Large Growth':'SPY','Small Blend':'VB','Foreign Large Blend':'EFA','Diversified Emerging Mkts':'EEM','Health':'XHE','US Tech':'QQQ','Intermediate Core Bond':'BND','Inflation-Protected Bond':'AGG','Multisector Bond':'BND','High Yield Bond':'SJNK','Bank Loan':'AGG','Muni California Intermediate':'MUB','Muni California Long':'MUB','Preferred Stock':'JPS', 'Nontraditional Bond':''BND'} 
for _idx, _ival in enumerate(ws1.values, start=1):
        if str(assetClass) in str(_ival):
            ws1.cell(row=_idx, column=32).value = '' #what goes here?
            ws1.cell(row=_idx, column=33).value = 'ALL'
            ws1.cell(row=_idx, column=34).value = '1'

First will this work? if so what is the code that goes "ws1.cell(row=_idx, column=32).value = '' #what goes here?" which will save the dictionary value in column 32 that matches the key?

Thanks.

Answer 1

I believe this should help in the scenario you posted: Please take a look

######
import os
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):
        assert _my_file_name.split('.')[-1] in ['xls', 'xlsx'], 'Input file does not have valid excel extension'
        self.my_filename = _my_file_name
        self.my_base_wb = openpyxl.load_workbook(self.my_filename, read_only=False)
        # following line will get the names of worksheets in the workbook
        self.ws_names_in_my_base_wb = self.my_base_wb.sheetnames
        # following line will get the number of worksheets in the workbook
        self.num_ws_in_my_base_wb = len(self.my_base_wb.sheetnames)
        # following line will set the last worksheet in the workbook as active
        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_file_name = 'Financial Sample.xlsx'
os.chdir('/Users/devops/Documents')

_stk_oflw_obj = CustomOpenpyxl(_my_file_name)
_my_search_string = 'Small Business'

# Iterate over the values in the active worksheet
for _idx, _ival in enumerate(_stk_oflw_obj.my_base_active_ws.values, start=1):
    # Search the string in the entire row
    # (_ival is tuple values)
    if str(_my_search_string) in str(_ival):
        # If the string is found in the row
        # add 'StackOverflow-Subclass' text on 17th column on the matched row index
        _stk_oflw_obj.my_base_active_ws.cell(row=_idx, column=17).value = 'StackOverflow-Subclass'
        # add 'StackOverflow-Subclass' text on 18th column on the matched row index
        _stk_oflw_obj.my_base_active_ws.cell(row=_idx, column=18).value = 'StackOverflow-Subclass'
        # add 'StackOverflow-Subclass' text on 19th column on the matched row index
        _stk_oflw_obj.my_base_active_ws.cell(row=_idx, column=19).value = 'StackOverflow-Subclass'

# Save the workbook
_stk_oflw_obj.save_wb()
######