1

I have a multi sheet excel file like the one pictured below. I want to highlight with the condition: if value 'app' in column 'Kind' matches with value 'v6.0' in column 'NetFrameworkVersion' then highlight it yellow if value 'functionapp' in column 'Kind' matches with value 'v4.0' in column 'NetFrameworkVersion' then highlight it green else highlight it blue

import pandas as pd
import pathlib
import xlsxwriter


with pd.ExcelWriter('*/environment.xlsx' , engine='xlsxwriter') as writer:
    for filename in pathlib.Path('*/FD').glob('*.csv'):
        df = pd.read_csv(filename) 
        
        
        df_custom = df.filter(['Kind', 'NetFrameworkVersion', 'Use32BitWorkerProcess', 'AlwaysOn' ]) #Select column and arrange custom
        
        sheetname = filename.stem.split('-')[3] #Set sheet name save as short name
        
        df_custom.style.to_excel(writer, sheet_name=sheetname,index=True, header = True) #write out file excel after read all data from csv files
        #set border#
        workbook = writer.book
        worksheet = writer.sheets[sheetname]
        border_fmt = workbook.add_format({'bottom':1, 'top':1, 'left':1, 'right':1})
        worksheet.conditional_format(xlsxwriter.utility.xl_range(0, 0, len(df), len(df_custom.columns)), {'type': 'no_errors', 'format': border_fmt})
        
        worksheet.set_column('C1:Z200', 25)   #set range column width
        worksheet.set_column('B:B', 35) #set 1 column width
        
        red_format = workbook.add_format({'bg_color':   '#FFC7CE',
                               'font_color': '#9C0006'})
        worksheet.conditional_format('F1:F1000', {'type':     'text',
                                        'criteria': 'containing',
                                        'value':    'True',
                                        'format':   red_format})
        worksheet.conditional_format('G1:G100', {'type':     'text',
                                        'criteria': 'containing',
                                        'value':    'False',
                                        'format':   red_format})     
    writer.save()

Example Picture: enter image description here

ATY
  • 41
  • 5
  • What is stopping you? How exactly can we help? If you don't know how to color the cells, [this](https://stackoverflow.com/questions/28075699/coloring-cells-in-pandas) might help. Then you can export it to a format of your choice, and it should keep the colored cells: [`df.to_excel`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html?highlight=to_excel#pandas.DataFrame.to_excel) – Confused Learner Aug 02 '22 at 11:09
  • That's right, I don't know how to color the cells like the picture example – ATY Aug 02 '22 at 12:54

1 Answers1

0

Let df be the DataFrame from your picture.

Something like this should work:

import pandas as pd # Jinja2 must be installed


def styler(series):
    color_attr = ''
    
    if series['Kind'] == 'app' and series['NetFrameworkVersion'] == 'v6.0':
        color_attr = 'background-color: yello'
    
    elif series['Kind'] == 'functionapp' and series['NetFrameworkVersion'] == 'v4.0':
        color_attr = 'background-color: green'
    
    else:
        color_attr = 'background-color: blue'

    return pd.Series(color_attr, series.index)


left_df_p = df[['Kind', 'NetFrameworkVersion']]
left_df_p.style.apply(styler, axis=1) # iterates over every row

colored_df = left_df_p.join(df[['Use32BitWorkerProcess', 'AlwaysOn']])

Next time, please provide a Minimal, Reproducible Example, so that people can actually test the code. Instead of posting a picture, it is better to call and post the output of df.to_dict.

Confused Learner
  • 386
  • 2
  • 9