help with an exercise

[iiiik]

I came to ask about the same exercise as last time, with which you helped me and it was very helpful but I would like to know if it could be done in another way in which the comparison of the two excel tables appears in one of the two sheets already created instead of creating a new sheet. I leave here the code.

import openpyxl
from openpyxl.styles import PatternFill
 
datafile1 = openpyxl.load_workbook("C:\\Users\\ime1s\\Downloads\\MiCalculo.xlsx")
fill_style1 = PatternFill(start_color='FDD835', end_color='FDD835', fill_type='solid')
fill_style2 = PatternFill(start_color='cc0000', end_color='cc0000', fill_type='solid')
data_sheet1 = datafile1['Comfica Calculo']
data_sheet2 = datafile1['Mi Calculo']
 
# Create a new sheet for comparison results
comparison_sheet = datafile1.create_sheet('Comparison Results')
 
# Iterate through rows in both sheets and compare values
for row1, row2 in zip(
    data_sheet1.iter_rows(min_row=1, max_row=data_sheet1.max_row),
    data_sheet2.iter_rows(min_row=1, max_row=data_sheet2.max_row),
):
    for cell1, cell2 in zip(row1, row2):
        if cell1.value == cell2.value:
            comparison_sheet.cell(row=cell1.row, column=cell1.column, value=1).fill = fill_style1
        else:
            comparison_sheet.cell(row=cell1.row, column=cell1.column, value=0).fill = fill_style2
 
datafile1.save('compared_file.xlsx')

[Pedroski55]

Yes, of course you can do that!

Better add 1 to maxRow, or you won't get the last row!

import openpyxl
from openpyxl.styles import PatternFill

XL_file = '/home/pedro/myPython/openpyxl/xlsx_files/compare_cells1.xlsx'
XLF = openpyxl.load_workbook(XL_file)
fill_style1 = PatternFill(start_color='FDD835', end_color='FDD835', fill_type='solid')
fill_style2 = PatternFill(start_color='cc0000', end_color='cc0000', fill_type='solid')
# Create a new sheet for comparison results
comparison_sheet = XLF.create_sheet('Comparison Results')
# get the biggest max row
maxRow1 = XLF['Sheet1'].max_row
maxRow2 = XLF['Sheet2'].max_row
if maxRow1 > maxRow2:
    maxRow = maxRow1
else:
    maxRow = maxRow2
# Iterate through rows in both sheets and compare values
# row 1 is usually headers so start at row 2
for rowNum in range(2, maxRow + 1):
    if XLF['Sheet1'].cell(row=rowNum, column=1).value == XLF['Sheet2'].cell(row=rowNum, column=1).value:
       XLF['Comparison Results'].cell(row=rowNum, column=1).fill=fill_style1
    else:
       XLF['Comparison Results'].cell(row=rowNum, column=1).fill=fill_style2
XLF.save(XL_file)

[iiiik]

Thank you so much. But what I'm looking for is that the solution of the comparison is on the same sheet, instead of creating another comparison sheet, which is in the next column.

[Pedroski55]

Oh! That's not so hard.

Change this part for the sheet you want and the column you want。

But don't change the comparison line, that's line 2 here below.

for rowNum in range(2, maxRow + 1):
    if XLF['Sheet1'].cell(row=rowNum, column=1).value == XLF['Sheet2'].cell(row=rowNum, column=1).value:
       XLF['Sheet1].cell(row=rowNum, column=1).fill=fill_style3
    else:
       XLF['Sheet1'].cell(row=rowNum, column=1).fill=fill_style4
XLF.save(XL_file)

Change 'Sheet1' for whatever sheet you want and change column=1 for any other column number

[iiiik]

Thank youu very very muchhh!!

[Pedroski55]

What I can't get is a gradient fill.

I followed the instructions from the documentation exactly, but it doesn't work.

I think maybe that is because I use Libre Office, not Microsoft Excel.

On the other hand, I really don't need a gradient fill!