Openpyxl 1.8.5: Reading the Result of a Formula Typed in a Cell Using Openpyxl

Openpyxl 1.8.5: Reading the result of a formula typed in a cell using openpyxl

openpyxl support either the formula or the value of the formula. You can select which using the data_only flag when opening a workbook. However, openpyxl does not and will not calculate the result of a formula. There are libraries out there like pycel which purport to do this.

How to get value of cell instead formula value?

Edit: In latest version (3.0.6+) the api was changed to disable the user of the first option (internal_value property) and thus only the second option now works. (Thanks Akshat)

You can either

1. Use row[0].internal_value.

Or

2. Use data_only=True when you load your workbook.
   (For example, workbook = openpyxl.load_workbook("yourxlsx.xlsx", data_only=True))

The second option is good when you are sure you don't wish to ever get the original formula from the workbook.

openpyxl formula blank in output excel

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.

---

Related Topics