Python openpyxl data_only=True returning None

python excel openpyxl
Michał Nowosielski picture Michał Nowosielski · Mar 20, 2016 · Viewed 13.7k times · Source

I have a simple excel file:

A1 = 200
A2 = 300
A3 = =SUM(A1:A2)

this file works in excel and shows proper value for SUM, but while using openpyxl module for python I cannot get value in data_only=True mode

Python code from shell:

wb = openpyxl.load_workbook('writeFormula.xlsx', data_only = True)
sheet = wb.active
sheet['A3']
<Cell Sheet.A3> # python response
print(sheet['A3'].value)
None # python response

while:

wb2 = openpyxl.load_workbook('writeFormula.xlsx')
sheet2 = wb2.active
sheet2['A3'].value
'=SUM(A1:A2)'  # python response

Any suggestions what am I doing wrong?

Answer

Charlie Clark picture Charlie Clark · Mar 20, 2016

It depends upon the provenance of the file. data_only=True depends upon the value of the formula being cached by an application like Excel. If, however, the file was created by openpyxl or a similar library, then it's probable that the formula was never evaluated and, thus, no cached value is available and openpyxl will report None as the value.

Related questions

Pandas: Looking up the list of sheets in an excel file

The new version of Pandas uses the following interface to load Excel files: read_excel('path_to_file.xls', 'Sheet1', index_col=None, na_values=['NA']) but what if I don't know the sheets that are available? For example, …

Read More
getting sheet names from openpyxl

I have a moderately large xlsx file (around 14 MB) and OpenOffice hangs trying to open it. I was trying to use openpyxl to read the content, following this tutorial. The code snippet is as follows: from openpyxl import load_workbook …

Read More
how to write to a new cell in python using openpyxl

I wrote code which opens an excel file and iterates through each row and passes the value to another function. import openpyxl wb = load_workbook(filename='C:\Users\xxxxx') for ws in wb.worksheets: for row in ws.rows: print …

Read More