Openpyxl: backfill a row that has a cell with a partial string match?-CodePudding

I'm working on a program that filters a large .xlsx file and then splits those filtered sets out onto new sheets in a new workbook.

One problem I'm trying to fix, is recreating the old format on the new sheets using openpyxl. I can't seem to figure out how to make a partial string match not result in a TypeError.

Relevant code snippet:

def set_style(sheet, type_row, group1, group2):
    for row in sheet.iter_rows():
        for cell in row:
            if row[type_row].value in group1:
                if 'START:' in cell.value:
                    cell.fill = PatternFill(start_color='00FFOO', end_color='00FF00', fill_type='solid')
                    cell.font = Font(bold=True)
                if 'END:' in cell.value:
                    cell.fill = PatternFill(start_color='99FF99', end_color='99FF99', fill_type='solid')
                    cell.font = Font(bold=True)
            if row[type_row].value in group2:
                cell.font = Font(bold=True)
                cell.fill = PatternFill(start_color='FFA500', end_color='FFA500', fill_type='solid')

The if-statement related to group2 works fine on it's own, it's only when I try to check if "START:" or "END:" that ends up resulting in my error.

Any help would be appreciated!

CodePudding user response：

This is how i solved it for a near match for one of my use cases. Please see if it helps:

# Method to get row indexes of searched values in a specific column with near match condition
# Arguments to this method are:- Row number for header row, Column name & Search value
def get_row_idx_lst_based_on_search_val_specific_col_near_match(self, _header_row_num, _col_name, _search_val):
    # Fetch the column index from column name using 'ref_col_name_letter_map' method
    _col_idx = column_index_from_string(self.ref_col_name_letter_map(_header_row_num)[_col_name])
    # Get the list of indexes where near match of searched value is found excluding the header row
    # The Excel columns start with 1, however when iterating, the tuples start with index 0
    _row_idx_list = [_xl_row_idx for _xl_row_idx, _row_val in
                     enumerate(self.my_base_active_ws.iter_rows(values_only=True), start=1) if
                     str(_search_val) in str(_row_val[_col_idx - 1]) if _xl_row_idx != _header_row_num]
    # Return type is list
    return _row_idx_list

CodePudding user response：

I managed to find out what the problem was and sort of worked around it:

if row[5].value is not None:
  if row[5].value != int:
     if 'START:' in row[5].value:
#<rest of code here>