Skip to content
Advertisement

How can I automatically calculate the formulas of an Excel file made with OpenPyxl?

I’m working on an program which makes an excel file, then it gets the info into JSON and does more things. I’m struggling with Openpyxl. I found out today that if you don’t open an Excel file made with Openpyxl with Excel, the formulas won’t be computer.

So when I write:

        excel = load_workbook(self.path_excel, read_only=True, data_only=True)

I don’t get the formulas result, but only a “None” result. If I instead write data_only=False I will get my original formula. I very well know why this happens and I’m trying to find an automatic solution to open the excel file, compute all the formulas inside my excel file and close it. So when I open it up again in Openpyxl in the code after this “phase” I will have my results.

I’m using Python btw.

Here is the result I get and what I want to get:

1: data_only=True Not what I wanted :/

  1. data_only=False NOT WHAT I WANTED PART 2

  2. What I really want with data_only=True

'delta_1': '12345' and more answers with numbers like when I open it in excel…

Thanks for the eventual help :)

Advertisement

Answer

I’m answering my own post for future reference and for others having my same problem…

Basically what worked was:

  1. Stop using Pycharm as for some reason it was limiting my Python code;
  2. Download Visual Studio Code;
  3. Use the xlwings library as suggested by jezza_99 in the second comment.

Thanks for the help everyone :)

Advertisement