the xlsxwriter format class

In [1]:

import re
import string
import pandas as pd
import numpy as np
from xlsxwriter.utility import xl_col_to_name

In [2]:

def randStr(length):
    rand_str = "".join(np.random.choice(np.array(list(string.ascii_uppercase)),length))
    return rand_str

In [3]:

num = 7
column_names = [randStr(5) for i in range(num)]

my_df = pd.DataFrame(np.random.rand(num,num))
my_df.columns = column_names

In [4]:

my_df

Out[4]:

	QNHTR	ENTJP	YPGCH	REMXE	ASROI	XWPAK	WWGKJ
0	0.757278	0.855409	0.743965	0.278558	0.907111	0.233872	0.070314
1	0.822849	0.395048	0.577086	0.692197	0.701750	0.172315	0.218093
2	0.865188	0.799359	0.003658	0.799052	0.997357	0.854079	0.660088
3	0.013974	0.452349	0.426939	0.280872	0.461789	0.800806	0.874574
4	0.988762	0.389006	0.613281	0.980424	0.808690	0.716430	0.409934
5	0.728441	0.723092	0.774407	0.117822	0.109778	0.040558	0.874075
6	0.987464	0.571264	0.572463	0.058041	0.168523	0.268443	0.425605

In [5]:

def spanXL(dataframe, start_index=0, row = 1):
    first = chr(65+start_index)
    last = xl_col_to_name(dataframe.shape[1]-1)
    span = [first+str(row),last+str(dataframe.shape[1]+1)]
    span = ":".join(span)
    return span
spanXL(my_df)

Out[5]:

'A1:G8'

In [6]:

def colSpanXL(dataframe, start_index=0, row = 1):
    span_str = spanXL(dataframe, start_index, row).replace(":","&")
#     span_str = re.sub("\d+$","1",span_str)
    rx = re.compile("(\d.+\D)")
    col_span = "$".join(rx.split(span_str))
    return col_span

colSpanXL(my_df)

Out[6]:

'A$1&G$8'

In [7]:

def makeSearch(term,df):
    search_str = '=SEARCH("{0}",{1})'.format(term,colSpanXL(df))
    return search_str

In [8]:

makeSearch("T",my_df)

Out[8]:

'=SEARCH("T",A$1&G$8)'

In [9]:

def criteriaXL(term,df,xl_fun="SEARCH"):
    
    xl_fun = xl_fun.upper()
    
    if not xl_fun.startswith("="):
        xl_fun = "="+xl_fun
        
    
    fun_str = xl_fun+'("{0}",{1})'.format(term,colSpanXL(df))
    return fun_str

In [10]:

criteriaXL("acetyl",my_df)

Out[10]:

'=SEARCH("acetyl",A$1&G$8)'

In [11]:

def dynFormat(format_set,workbook):
    return workbook.add_format(format_set)

In [12]:

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_conditional.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
my_df.to_excel(writer, sheet_name='Sheet1',index=False)

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']


worksheet.conditional_format(spanXL(my_df), {'type':'formula',
                                             'criteria': makeSearch(column_names[3],my_df),
                                             'format': dynFormat({"bg_color":"PeachPuff"},workbook)})

worksheet.conditional_format(spanXL(my_df),{'type':'formula',
                                             'criteria': makeSearch(column_names[-1],my_df),
                                             'format':  dynFormat({"bg_color":"orange"},workbook)})

# Close the Pandas Excel writer and output the Excel file.
writer.save()

In [ ]: