Python - Openpyxl - Adding Cell Value To Column P If Match Between Column A (list) and Tuple (list)-CodePudding

Background

I have a list of values in column A (which differ from worksheet to worksheet). I wanted to identify a subset of values to be matched and if a match was made between lists then the script will add the value from the tuple list to column P and moves downwards per value (see picture Script output).

Problem

The script kind of works, accept if the value in tuple is not found the Column A list, then it creates an empty cell in Excel and moves down to the next cell (see picture - Excel output).

Questions

What is the best way to avoid empty cells if the value from the tuple is not in the list?

Script

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.styles import colors
from openpyxl.cell import Cell
import colorama
from colorama import init
init()
from colorama import Fore, Style, Back
import os
import shutil
import math
from decimal import Decimal
from openpyxl.styles import Alignment
from inspect import currentframe
import time

########################################
#########################################

src_path ="/Users/data/"
xlfileName = "test.xlsx"
xlfile = src_path   xlfileName
wb = Workbook()
wb = load_workbook(xlfile)

#########################################
#########################################

def get_linenumber():
    cf = currentframe()
    return cf.f_back.f_lineno

#########################################
#########################################
non_cash_tup = (
"Depreciation & amortisation", "Deferred income taxes", "Stock-based compensation", "Change in working capital",
"Accounts receivable", "Inventory", "Accounts payable", "Other non-cash items")

for xws in wb.sheetnames:
    print(Style.RESET_ALL)
    print(Fore.BLUE, "Looping Through Worksheet Name -- ", worksheet, "Script line is -- ", get_linenumber())
    print(Style.RESET_ALL)
    worksheet = wb[xws]
    cell_list = []

    for cell in worksheet['A']:
        cell_list.append(cell.value)
    for i, name in enumerate(non_cash_tup):
        if name in cell_list:
            print(Fore.LIGHTGREEN_EX, name, "is in the list")
            column_cell = 16
            worksheet.cell(column=column_cell, row=i 2, value=name)
        else:
            print(Fore.LIGHTRED_EX, name, "is not in the list")
    wb.save(xlfile)

CodePudding user response：

Use the set() function.

The set() function in Python uses to take an argument and convert it into a set object. It can take arguments like lists, tuples and dictionaries. The argument is called iterable. The output of elements might not be in the same order because items passed as list were not in order.

non_cash_tup = (
"Depreciation & amortisation", "Deferred income taxes", "Stock-based compensation", "Change in working capital",
"Accounts receivable", "Inventory", "Accounts payable", "Other non-cash items")

for xws in wb.sheetnames:
    print(Style.RESET_ALL)
    print(Fore.BLUE, "Looping Through Worksheet Name -- ", worksheet, "Script line is -- ", get_linenumber())
    print(Style.RESET_ALL)
    worksheet = wb[xws]
    cell_list = []

    for cell in worksheet['A']:
        cell_list.append(cell.value)

    matching = set(non_cash_tup) & set(cell_list)
    #print(matching)
    for i, name in enumerate(matching):
        print(Fore.LIGHTGREEN_EX, name, "is in the list")
        column_cell = 16
        worksheet.cell(column=column_cell, row=i 2, value=name)
        wb.save(xlfile)