comparing one column in two spreadsheets in openpyxl

Question

I am making an excel comparing program but I seem to be stuck. I want to compare two excel files in a spreadsheet. Here is my code:

import openpyxl

wb = openpyxl.load_workbook('C:\\Users\\Bill\\Desktop\\CK_Server_list_0.1.xlsx')
ws = wb.active

wb1 = 
openpyxl.load_workbook('C:\\Users\\Bill\\Desktop\\CK_Server_list_0.2.xlsx')
ws1 = wb1.active

for x in ws.iter_cols(max_col=1):
        for cell in x:
                print(cell.value, cell.coordinate)


for row1 in ws1.iter_cols(min_col=1):
        if row1[0].value != ws.cell(row=x, column=1).value:
                print(str(row1[0].value) + ' is not equal to ' + str(ws.cell(row=x, column=1).value + ' ' + str(ws.cell(row=x, column=1).coordinate)))

And every time I run this it gives me an error saying that tuple() < int(). Can anyone fix this problem? Any help would be appreciated.

Are you attempting to compare a column from the first table (0.1.xlsx) to a row in the second (0.2.xlsx)? — njoosse, May 25 '17 at 18:41

Xukrao · Accepted Answer · 2017-05-26T14:06:36.123

3

This error pops up because your variable x contains a tuple of cell objects at the time when the line if row1[0].value != ws.cell(row=x, column=1).value: gets executed. The input argument row requires an int value instead.

I think that a good approach for your problem would be to use for loops in combination with zip statements (more on zip here):

import openpyxl

wb = openpyxl.load_workbook('C:\\Users\\Bill\\Desktop\\CK_Server_list_0.1.xlsx')
ws = wb.active

wb1 = openpyxl.load_workbook('C:\\Users\\Bill\\Desktop\\CK_Server_list_0.2.xlsx')
ws1 = wb1.active

for (col, col_1) in zip(ws.iter_cols(), ws1.iter_cols()):
    for (cell, cell_1) in zip(col, col_1):
       if cell.value != cell_1.value:
           print(str(cell.value) + ' is not equal to ' + str(cell_1.value) + ' ' + str(cell.coordinate))

edited May 26 '17 at 14:06

answered May 25 '17 at 18:45

Xukrao

8,003
5
26
52

what if size of rows and columns is not same? – bhushan pardhi Apr 16 '21 at 14:16
@bhushanpardhi The `zip` statement stops once the smallest input iterable is exhausted. Therefore this code will only compare the rows and columns that are present in both excel sheets. – Xukrao Apr 17 '21 at 20:51

comparing one column in two spreadsheets in openpyxl

1 Answers1