CODEWITHSUNDEEP
pythonexcelopenpyxl
Egelbets
Egelbets

Reputation: 336

openpyxl formula blank in output excel

I want to use the Excel AVERAGE formula to get the average of columns in a file. I am doing this like so:

font = Font(name="Calibri", bold=True)

ws.cell(100, 1).value = "Mean"  # row 100 is the average row
ws.cell(100, 1).font = font

col_range = [2, 3, 4]  # the columns to average
for col_index in col_range:
    cell = ws.cell(100, col_index)  
*   cell.value = f"=AVERAGE({cell.column_letter}2:{cell.column_letter}98)"
    cell.font = font
wb.save(out_file)

However, when I open the Excel file, the average cells are empty:

excel_file

As you can see, the cells in row 100 are empty apart from the "Mean" cell. There is no formula in the other cells whatsoever, but the markup of the cell did work (bold text).

When printing the formulas indicated with * in the above code, I see this:

=AVERAGE(B2:B98)
=AVERAGE(C2:C98)
=AVERAGE(D2:D98)

This is how I want it to be.

When I do something like cell.value = "test" instead of the formula, then this does work:

working_excel_file

I thought this might be a language issue since my Excel is in Dutch, and AVERAGE=GEMIDDELDE in my language, but even if I use GEMIDDELDE, then this will still result in empty cells.

Any ideas on why this isn't working? I should probably mention that the Excel file I am doing this in is created right before with a script, but I do properly save it and then open it again to enter the formulas.

EDIT: I did just find this in the docs:

>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> # add a simple formula
>>> ws["A1"] = "=SUM(1, 1)"
>>> wb.save("formula.xlsx")

NB you must use the English name for a function and function arguments must be separated by commas and not other punctuation such as semi-colons.

If I follow this step by step, then this works. However, this code uses Workbook(), but I use load_workbook(), and then it still doesn't work.

I tried using , instead of ;, but this also didn't work. I think it's weird that you are not supposed to use ;, because isn't the value of a cell just a string? I feel like openpyxl doesn't do anything to it, but yet you're not supposed to use ;.

I also downloaded the English version of Excel to rule out the language. I also tested using , and . as the decimal separators in the cells that have to be averaged, but none of this works either. I feel like this is more an openpyxl issue now; I'm using version 3.0.5. If you use Workbook(), formulas work, but load_workbook() just doesn't work. Any tips would still be appreciated though.

Upvotes: 1

Views: 655

Answers (1)

Egelbets
Egelbets

Reputation: 336

Okay... So, I have something that works now. When using load_workbook(), there is a data_only flag you can use (from here):

There are several flags that can be used in load_workbook.

  • data_only controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet.

I do more operations on the same Excel file than just creating the average row in the question, and when doing these operations, I open the workbook like so:

    wb = load_workbook(filename, data_only=True)

Turns out that this makes the formulas not work anymore😀! When I removed this flag everywhere where I open the workbook, the AVERAGE formula works. Make sure that if you select a range of columns, then to still use :, but if you use a formula with multiple arguments, then separate them with , and not ;.

Oh and btw, you can load the file in Excel in a different language; as long as you put the formula in English in your code, it will translate appropriately.

Upvotes: 1

Related Questions