Excel to Python

[AntonioCruz]

Good evening all

I'm brand new to Python and trying to use the following excel array formula
'=SUM(IF(SUMIF($A3:$H3,K1:M1),1))' and I dont know how to do it.
It compares two sets of numbers and returns how many common numbers exist.
Can anyone help?

Thanks in advance
António

[Pedroski55]

I don't know what your formula does. I heard openpyxl can do things with XL formulas, but I never tried.
Read in the values you want, then do whatever you want with them.

import openpyxl
myfolder = '/path/to/myfolder/'
myfile = 'myXLfile.xlsx'
sourceFile = openpyxl.load_workbook(myfolder + myfile)
# sourceSheetNames is a list
sourceSheetNames = sourceFile.sheetnames
for sheet in sourceSheetNames:
    print('The sheet names are', sheet)
# assume you only have 1 sheet in your XL, then your sheet is sourceSheetNames[0]
# otherwise choose the sheet you want
sheet = sourceSheetNames[0]
nums_row3 = []
nums_row1 = []
# get A3 to H3
# A = column 1, H = column 8
# get the number in cells A3 to H3
for colNum in range(1, 9):
    num = sourceFile[sheet].cell(row=3, column=colNum).value
    print('row 3 column', colNum, '=', num)
    nums_row3.append(num)
# K = column 11 M = column 13
for colNum in range(11, 14):
    num = sourceFile[sheet].cell(row=1, column=colNum).value
    print('row 1 column', colNum, '=', num)
    nums_row1.append(num)

# now you have 2 lists with the values from A3:H3 and K1:M1
# not sure what you want to do with them but you can do many things with Python lists
# if you want to write values to cells (of course, the XL must be open with openpyxl)
# anyXLFile[sheet].cell(row=X, column=Y, value=myvalue)

[ibreeden]

It compares two sets of numbers and returns how many common numbers exist.

>>> set1 = {1, 2, 3, 4, 5}
>>> set2 = {4, 5, 6, 7, 8}
>>> set3 = set1 & set2
>>> print(len(set3))
2