-1

I have a big excel with a lot of formulas and some input data. I am trying to modify this input data with openpyxl and then read the results. For some reason the values of the cells with formulas can't be properly read by openpyxl or pandas, but I can see the calculation values if I open it in excel. If I create a similar file with excel then the result of the formulas can be read without problems. Am I doing something wrong? I reproduce my problem with an example here:

from openpyxl import Workbook,load_workbook
import pandas as pd

wb=Workbook()
ws1=wb['Sheet']
ws1['A1']='a'
ws1['A2']='b'
ws1['A3']='c'

ws1['B1']=1
ws1['B2']=2
ws1['B3']='=B1+B2'
wb.save('to_erase.xlsx')

wb2 = load_workbook(filename='to_erase.xlsx')
wb['Sheet']['B3'].value
#this displays the formula, ok

wb3 = load_workbook(filename='to_erase.xlsx',data_only=True)
wb3['Sheet']['B3'].value

this does not display anything, which is strange, same is I use pandas

pd.read_excel('to_erase.xlsx',header=None)

The cell B3 is a NaN. This does not happen if I create the excel manually, Why the result of the formula is not being displayed?

Nabla
  • 1,509
  • 3
  • 20
  • 35

1 Answers1

0

When you write '=B1 + B2' in cell B3 of your excel file, this is a macro/command which will be played by Excel when the file will be opened in excel, so read_excel or openxls can't play with it.

Frenchy
  • 16,386
  • 3
  • 16
  • 39
  • so the workflow is not possible?, even with other libraries? – Nabla Mar 08 '19 at 14:33
  • No sorry, either you have a tool (not free..) which simulates some functions of excel, either you have a tool which can communicate with file excel opened by excel. (com or dom tool). Excel can execute python program, but its not you want – Frenchy Mar 08 '19 at 14:36
  • Please dont Forget To validate the answer – Frenchy Mar 08 '19 at 20:22