1

I am trying to convert a csv file to excel as per below code and i have few columns which need to be compared inside excel and do conditional format

In the below code i have hard coded cells "B2<>C2" , "C2:C1048576". Without providing B2,C2 can i make those cells name to read in generic way and compare the complete data in excel

Wherever i have _SRC & _TGT those columns need to be compared simultaneously. Below link has both the sample excels

for csvfile in glob.glob(os.path.join('.', "file1.csv")):
    workbook = Workbook(csvfile[:-4] + '.xlsx')
    worksheet = workbook.add_worksheet()
    with open(csvfile, 'rt', encoding='utf8') as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader):
            for c, col in enumerate(row):
                worksheet.write(r, c, col)
    orange_format = workbook.add_format({'bg_color':   '#FFEB9C',
                                     'font_color': '#9C6500'})

    worksheet.conditional_format('C2:C1048576', {'type':'formula',
                                       'criteria':'=B2<>C2',
                                       'format':orange_format})

    workbook.close()

I have two types of excels data as below

enter image description here

enter image description here

Arya
  • 528
  • 6
  • 26
  • I'm not sure I understand, but are you asking how you can reuse your conditional format that currently works on column C to work on columns E, K and M as well? Honestly, the simplest solution would be to create a new condition for each column. It's easy to do in python using a loop. – Dan Sep 03 '19 at 13:04
  • @Dan. Thanks a lot for the reply. In each excel i have KeyColumns mentioned and rest of the columns will be having _SRC and _TGT. without providing the hardcoded cell information like to compare B with C , can i run in a loop or any other way so that it will compare automatically all the columns like BwithC , DwithE ... – Arya Sep 03 '19 at 13:13
  • yes, see my answer. If you want, you can create `columns_to_format` like this `string.ascii_letters[2:26:2].upper()` (`import string` first) – Dan Sep 03 '19 at 13:21

1 Answers1

2

If you're asking how to make

worksheet.conditional_format('C2:C1048576', {'type':'formula',
                                   'criteria':'=B2<>C2',
                                   'format':orange_format})

reusable to other columns, then try something like this: 

def get_previous_column(column):
    # if you're expecting to go above column `Z` then you'll need to improve this function
    return chr(ord(column) - 1)

def make_conditional_format(worksheet, col_src, col_tgt):
    column_range = f"{col_tgt}2:{col_tgt}1048576"
    worksheet.conditional_format(
        column_range, 
        {
            'type':'formula',
            'criteria':f'={col_src}2<>{col_tgt}2',
            'format':orange_format
         }
    )

columns_to_format = ['C', 'E', 'K', 'M']

for col_tgt in columns_to_format:
    col_src = get_previous_column(col_tgt)
    make_conditional_format(worksheet, col_src, col_tgt)
Dan
  • 45,079
  • 17
  • 88
  • 157
  • Thanks Dan. Actually my 2 screen shots are 2 different excels. I have different scenarios. But i am trying to find a way wherever i have "_SRC" & _TGT" i want to compare those corresponding columns – Arya Sep 03 '19 at 13:20
  • @Arya I've updated the answer to accept srouce and target columns. Now all you need to do is write the logic to find the "_SRC" & _TGT" pairs – Dan Sep 03 '19 at 13:24
  • 1
    It worked. Thanks a lot. I will try with multiple scenarios and if i have any doubt can i ask in this same page ? – Arya Sep 03 '19 at 13:57
  • Hi Dan. As you mentioned one point # if you're expecting to go above column `Z` then you'll need to improve this function --- how can i handle if it is greater then 'Z' like 'AA', 'AB' .... – Arya Sep 06 '19 at 09:23
  • @Arya A very quick google lead me to this: https://stackoverflow.com/a/23862195/1011724 no idea if it works. – Dan Sep 06 '19 at 10:00