I have a table where I would like to sort based on a specific column, and the values in that column result from a formula, but instead of reading the resulting values, python reads the literal string of the formula; therefore, I’m unable sort properly based on the values.
How do I sort based on the values?
”’
This identifies the last column of my table which is a section of the mother table
for col_idx in range(1, sheet.max_column+1):
if sheet.cell(row=1, column=col_idx).value == "Status":
col_status = col_idx
”’
This should supposedly sort my table based on the values which are “Age” values; the values here are computed using the excel formula for NETWORKDAYS()
data = []
for row in sheet.iter_rows(min_row=2, max_col=col_status):
data.append(row)
data.sort(key=lambda x: x[8], reverse=True )
print(data)
”’
So seems like you are just opening the workbook with
load_workbookwithout specifying ‘data_only’ setting. By default load_workbook sets ‘data_only=False’ and reads the formula from the cell if one exists otherwise reads the value. If you set ‘data_only=True’ then all cells will have the value read including those with formulas. Therefore if you want the value a evaluated by formulae you must havedata_only=Trueset in ‘load_workbook’. Also as Openpyxl cannot evaluate formulae the sheet must have been opened in Excel to do this otherwise the value will be 0.[continued from previous comment] Also be careful if you are updating he worksheet and saving, since Openpyxl has only read the values if you save that workbook (over the existing) you lose the formulas and just have a sheet of values.