from openpyxl import load_workbook wb = load_workbook(filename = 'TEST.xlsx') ws = wb.active ws1 = ws['B3':'B7'] for i in ws1: for cell in i: print (max(cell))

There is a specific range with dates in the Excel file. I need to choose an early / late date. Tried in various ways, give different errors. How to do it?

    3 answers 3

    You have errors because you do not receive data from cells, but a tuple link. In addition, alphanumeric indexes are already trying not to use, since openpyxl now tries to work with a table in the canonical addressing system (column number: row number).

    Here I have for the test.xls file

      A 1 12.01.1976 2 05.02.1980 3 30.11.2018 4 03.07.2007

    this solution is obtained:

     from openpyxl import load_workbook wb = load_workbook('test.xlsx', data_only=True) sheet = wb.active mycol=1 #номер столбца. A=1, B=2 и т.д myrow = list(range(1,5)) # диапазон строк (1:4) print(max(sheet.cell(row=x,column=mycol).value for x in myrow)) print(min(sheet.cell(row=x,column=mycol).value for x in myrow))

    At the exit:

     2018-11-30 00:00:00 1976-01-12 00:00:00

    In general, everything works, although, of course, it would be better to translate the lines in the timestamp and then compare them.

    • Cool, it works. Can you explain why data_only = True ? - BenGoodman
    • The @BenGoodman directive data_only=True says that you need to load cell values, not reference tuples. To avoid an error builtins.AttributeError: 'tuple' object has no attribute 'value' - strawdog

    Pandas module greatly simplifies and speeds up work with Excel files:

     import pandas as pd # pip install pandas s = pd.read_excel(r'C:\Temp\files.xlsx', skiprows=2, header=None, usecols=[1], squeeze=True) print(s.min()) #2000-12-31 00:00:00 print(s.max()) #2018-11-30 00:00:00

    You can read the entire Excel file in the pandas DataFrame:

     df = pd.read_excel(r'C:\Temp\files.xlsx')
    • I thought Pandas only reads data. doing the analysis and so on. It turns out he can create, adjust Excel files? - BenGoodman
    • Pandas allows you to read, write, process, analyze and visualize data from a variety of sources - MaxU

    Instead

     for i in ws1: for cell in i: print (max(cell))

    use

     print(max([c.value for c in ws1]))
    • tried, gives error builtins.TypeError: '>' not supported between instances of 'Cell' and 'Cell' - BenGoodman
    • You are right - I corrected my answer. - MarianD
    • now another error, builtins.AttributeError: 'tuple' object has no attribute 'value' - BenGoodman