pythonpython-3.xexcelmaxcell

Is there a way to return the highest value in Excel?


The Problem

I'm working directly on a Excel sheet with the Python extension for Excel and I'm trying to find out which is the highest number of a list of cells.

This is the code for the function that I wrote:

def getMax(celle_voti_lista) -> int:
    valori = [v for v in celle_voti_lista if isinstance(v, (int, float))]
    return max(valori) if valori else 0

And this is the function call:

max(xl("D11:D23"))

But everytime I run this code, It gives me 0 instead of the actual highest number. What should I do?


Solution

  • try something like this:

    from openpyxl import load_workbook
    
    def xl(range_str, file_path, sheet_name):
        wb = load_workbook(file_path)
        sheet = wb[sheet_name]
        start_cell, end_cell = range_str.split(":")
        start_row, start_col = int(start_cell[1:]), start_cell[0].upper()
        end_row, end_col = int(end_cell[1:]), end_cell[0].upper()
        cells = []
    
        for row in sheet[f"{start_col}{start_row}":f"{end_col}{end_row}"]:
            for cell in row:
                cells.append(cell.value)
        return cells
    
    def getMax(celle_voti_lista) -> int:
        valori = [v for v in celle_voti_lista if isinstance(v, (int, float))]
        return max(valori) if valori else 0
    

    and then:

    file_path = "data.xlsx"
    sheet_name = "Sheet1"
    max_value = getMax(xl("D11:D23", file_path, sheet_name))
    print(max_value)