Highlighting an excel cell using conditional formatting in python

Question

I'm trying to create a program that reads an excel file containing some data, asks the user for an input, and if the input entered is equal to data within a cell, the cell gets highlighted. This code currently works fine till the user's input is compared with the data.

However I cannot find the correct way to highlight a cell if there is a match with the entered input. What can I do about this? I tried conditional formatting with xlsxwriter but realized while going through documentation that it can only be used to create new files and not change or modify existing files and tried using openpyxl here but to no avail. Sorry I'm just a bit new at this and would really appreciate some help fixing this code

import pandas as pd
import xlsxwriter
from openpyxl import Workbook
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule,
FormulaRule

    def checkIfValuesExists1(dfObj, listOfValues):
    ''' Check if given elements exists in dictionary or not'''
    resultDict = {}
    # Iterate over the list of elements one by one
    for elem in listOfValues:
        # Check if the element exists in dataframe values
        if elem in dfObj.values:
            resultDict[elem] = True
        else:
            resultDict[elem] = False
    # Returns a dictionary of values & their existence flag        
    return resultDict

def main():
    import pandas as pd 
    df=pd.read_excel("filename.xlsx") 
    df

    # Create a DataFrame object
    empDfObj = pd.DataFrame(df, columns=['Name', 'LastName', 
    'MiddleName'])

    print('Contents of the dataframe :')
    print(empDfObj)

    print('**** Check if an element exists in DataFrame using in & not in 
   operators ****')

    x= str(input("Enter your text: "))

    print('** Use in operator to check if an element exists in dataframe 
**')

    if x in empDfObj.values:

    df.style.apply(lambda y:['background:red' if x == df.Category else 
    'background:green' for x in df],axis=0)

        print('Element exists in Dataframe')
    else:
        print('Element does not exist in Dataframe')

    # Check if 'Hello' doesn't exist in DataFrame
   # if 'Hello' not in empDfObj.values:
         #print('Element does not exist in Dataframe')

    # Get the xlsxwriter workbook and worksheet objects.
    df.to_excel('output1.xlsx', engine='xlsxwriter')

if __name__ == '__main__':
    main()

***"highlight a cell if there is a match with the entered input."***: That's not possible, the `Rule` refers the `.cell.value`, e.g. `10` => `Rule .cell value in range(5, 20)`. Your closest solution can be a helper cell value, e.g. `.cell.value == '*'` => `Rule .helper_cell.value == '*'` — stovfl, Sep 16 '19 at 15:13

score 0 · Answer 1 · answered Sep 16 '19 at 14:39

0

To get the value of a cell you just need to use .cell and selecting the row and column as answered here - Reading particular cell value from excelsheet in python

answered Sep 16 '19 at 14:39

Barb

427
3
14

Thanks for that! However what I am looking for is a way to highlight the cell after the input is matched with the cell value – icyfroze Sep 16 '19 at 14:44
I see, possibly implement `mask = np.column_stack([df[col].str.contains(r"\^", na=False) for col in df]) df.loc[mask.any(axis=1)]` to your code to check every column and then use `str.contains` on each column. Source - https://stackoverflow.com/questions/26640129/search-for-string-in-all-pandas-dataframe-columns-and-filter/26641085#26641085 – Barb Sep 16 '19 at 14:51
Thank you! The process of taking input and comparing/matching with cell values seems to be working just fine! The thing I was looking for was the correct syntax to apply conditional formatting/highlighting to the cell though – icyfroze Sep 16 '19 at 15:02

Highlighting an excel cell using conditional formatting in python

1 Answers1