Groups keyboard shortcuts have been updated
Dismiss
See shortcuts

openpyxl returns None for value actually present and updated in the created sheet.

8,826 views
Skip to first unread message

Aman Verma

unread,
May 24, 2017, 6:01:06 AM5/24/17
to openpyxl-users
Hi all,
I have been using Openpyxl for a while and it fitted very well to my usecases. However, i found an issue regarding the data read by openpyxl upon saving the file with formulas. After updating the cell value and saving , python returns None instead of the value actually present in the sheet.

 So here, are my steps to recreate the problem at your workstation. For this i created another file to check if the problem persist.

  1. Created a sample .XLSM file with one column as raw values. (Kindly find the File attached : input.xlsm)
  2. Added another column which simply copies the value from the previous cell of that specific row (=(PreviousCell)).
  3. Wrote a script to alter the value of cell present in the raw_column, which ideally should reflect back as per the formula in the next cell. (kindly find the script attached , openpyxl.py)
  4. Saved the file with another name(file name : output.xlsm)
  5. Opening the file to check the contents back manually.(The formula works here and can see the column updated as per the raw column value).
  6. I can see the formula still there when reading back the new file from python. However the result value of the cell stays None.

SCRIPT:
def ex_temp():
    wb
= openpyxl.load_workbook("input.xlsm",data_only=False, keep_vba=True)
    wb2
= openpyxl.load_workbook("input.xlsm",data_only=True, keep_vba=True)

    sheet
= wb.get_sheet_by_name('Sheet1')
    sheet2
=wb2.get_sheet_by_name('Sheet1')

   
print "With   Formula: ",sheet.cell("C2").value, sheet.cell("D2").value
   
print "With Only Data: ",sheet2.cell("C2").value, sheet2.cell("D2").value
    sheet
.cell("C2").value=100

    wb
.save("output.xlsm")
   
# wb2.save("output2.xlsm")

   
print "After Writing-------------------------------------------------------"

    wb
= openpyxl.load_workbook("output.xlsm",data_only=False, keep_vba=True)
    wb2
= openpyxl.load_workbook("output.xlsm",data_only=True, keep_vba=True)
   
print wb.get_sheet_names()
    sheet
= wb.get_sheet_by_name('Sheet1')
    sheet2
= wb2.get_sheet_by_name('Sheet1')

   
print "With   Formula: ",sheet.cell("C2").value,sheet.cell("D2").value
   
print "With Only Data: ",sheet2.cell("C2").value, sheet2.cell("D2").value

ex_temp
()


OUTPUT:

With   Formula:  5 =C2
With Only Data:  5 5
After Writing-------------------------------------------------------
[u'Sheet1']
With   Formula:  100 =C2
With Only Data:  100 None




So, The issue is when i am reading back the values present in the cells from the newly created file from python, openpyxl returns None instead of the value actually present in the excel sheet.
I dont know what wrong I am doing here, as i can straightaway see the updated value in the newly created excel sheet along with the existing formula. Is this a limitation of openpyxl or I am doing something very naive.
Can somebody brief be out about the issue and what are possible walkarounds.

My OS: Ubuntu 16.10
Python : 2.7.4
Openpyxl: 2.4.7
Reader : LibreOffice calc

Thanks and regards
Aman Verma
input.xlsm
output.xlsm
openpyxl.py

Charlie Clark

unread,
May 24, 2017, 6:05:12 AM5/24/17
to openpyx...@googlegroups.com
Am .05.2017, 12:01 Uhr, schrieb Aman Verma <amnve...@gmail.com>:

> Hi all,
> I have been using Openpyxl for a while and it fitted very well to my
> usecases. However, i found an issue regarding the data read by openpyxl
> upon saving the file with formulas. After updating the cell value and
> saving , python returns *None* instead of the value actually present in
> the
> sheet.

This is clearly covered in the documentation: you can either have the
formula or the cached value of the last evaluation. If you alter a file
with formulae then you must pass it through some kind of application such
as Excel if you want to get the value of the formulae.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226
Message has been deleted

agow...@gmail.com

unread,
Jul 7, 2020, 1:41:40 AM7/7/20
to openpyxl-users
Hi Aman ,

While at this point, opening with data_only=True or default data_only=False will result in two different results, each independent, ie open in data_only=True state, you will find the result of the formula as None (null value) or a calculated constant, I won't see how the original formula is. Data_only=False only shows the formula. Therefore, the data_only=True state is turned on. If it is saved with the save() function, the formula will be replaced with a constant result or a null value in the original xlsx file. The data_only=False state is opened, and finally saved by the save() function, the original xlsx file will only have the value of data_only=False (that is, the formula), and the other set (data_only=True) will be lost. If you want to regain two sets of values, you still need to open the file and save it with an Excel program .

Solution:

Reopen it once and save it.

from win32com.client import Dispatch

def just_open(filename):
xlApp = Dispatch(“Excel.Application”)
xlApp.Visible = False
xlBook = xlApp.Workbooks.Open(filename)
xlBook.Save()
xlBook.Close()


Hope this solution works for you!!

Amin Babaei

unread,
Jul 7, 2022, 1:57:09 PM7/7/22
to openpyxl-users
you mast write (filename =xxxxxxx)
wb = openpyxl.load_workbook(filename="input.xlsm",data_only=False, keep_vba=True)
Message has been deleted

Y. Taborda

unread,
Nov 27, 2023, 7:19:24 PM11/27/23
to openpyxl-users
How can I solve it if I have a linux machine?

Akshara Ramasubbu

unread,
Aug 16, 2024, 12:51:17 AM8/16/24
to openpyxl-users
I am trying to open-save-close-open the excel sheet using openpyxl (as below):

wb = openpyxl.load_workbook("TRIAL.xlsx",data_only=True)
wb.save( "TRIAL.xlsx" )
wb.close()
       
wb = openpyxl.load_workbook( "TRIAL.xlsx" ,data_only=True)

The result is still None for me. What am I missing?

Thanks in advance,
Akshara

Charlie Clark

unread,
Aug 16, 2024, 4:04:19 AM8/16/24
to openpyxl-users
On 16 Aug 2024, at 6:51, Akshara Ramasubbu wrote:

> The result is still *None *for me. What am I missing?

What do you mean "still"? I don't see any lookups-

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226
Message has been deleted
Message has been deleted

Marcelo Lopes

unread,
Oct 8, 2024, 4:53:01 PM10/8/24
to openpyxl-users
  Hi everyone.

Recently I faced the same issue presented on this thread, which is: after saving an excel file and reading this file again using openpyxl all cells containing formulas return 'None'

This occours because openpyxl is not able to force excel to recalculate the formulas.

An alternative to openpyxl is the library xlwings. So the Aman's code could be written like this:

import xlwings as xw
from openpyxl import load_workbook

def ex_temp():
    # Writing and saving using openpyxl
    reading_data(False, 'input.xlsm')

    # Recalculating formulas and saving using xlwings
    saving_file('output.xlsm')

    # Only reading data AFTER saved by xlwings
    reading_data(True, 'output.xlsm')

def reading_data(only_reading, file):
    wb = load_workbook(file, data_only=False, keep_vba=True)
    wb2 = load_workbook(file, data_only=True, keep_vba=True)
   
    sheet = wb.worksheets[0]
    sheet2 = wb2.worksheets[0]

    print ("With Formula: ",sheet["C2"].value,sheet["D2"].value)
    print ("With Only Data: ",sheet2["C2"].value, sheet2["D2"].value)

    if not only_reading:
        sheet["C2"].value = 100
        wb.save("output.xlsm")
        print('After writing-------------')

def saving_file(file):
    # Saving data using xlwings
    with xw.App(visible=False) as app:
        app.display_alerts = False  # Disable Excel alerts
        wb = app.books.open(file) # Loads workbook
        sheet = wb.sheets[0] # Turns Sheet1 active

        wb.app.calculate() # Recalculating formulas

        wb.save()
        wb.close()

ex_temp()

Output:

With Formula:  100 =C2
With Only Data:  100 100
After writing-------------
With Formula:  100 =C2
With Only Data:  100 100

Basically the ideia is using xlwings library to recalculate formulas and save the file.
Reading can be done either by xlwings or openpyxl.

Hope this solutions helps someone.

Regards!

Marcelo Lopes

Charlie Clark

unread,
Oct 9, 2024, 6:31:44 AM10/9/24
to openpyxl-users

On 8 Oct 2024, at 22:53, Marcelo Lopes wrote:

Hi everyone.

Hi Marcelo,

Recently I faced the same issue presented on this thread, which is: after
saving an excel file and reading this file again using openpyxl all cells
containing formulas return 'None'

This occours because openpyxl is not able to force excel to recalculate the
formulas.

This is not quite true: Openpyxl itself never evaluates formulae, which is why you're forced to choose between the formulae or the most recently cached result, if available. This makes sense if you think about it: if ws["A5"] is the sum of cells A1:A4 and you change any of the values, then the previous calculation is invalid.

An alternative to openpyxl is the library xlwings. So the Aman's code could
be written like this:

This is great if you're on a platform with Microsoft Excels such as Windows or MacOS, because XLWings itself relies on Excel for data processing. There are other alternatives, such as using PyCel to do the calculations; or OpenOffice or LibreOffice in headless mode; or OnlyOffice Docs.

import xlwings as xw
from openpyxl import load_workbook

def ex_temp():
# Writing and saving using openpyxl
reading_data(False, 'input.xlsm')

# Recalculating formulas and saving using xlwings
saving_file('output.xlsm')

# Only reading data AFTER saved by xlwings
reading_data(True, 'output.xlsm')

def reading_data(only_reading, file):
wb = load_workbook(file, data_only=False, keep_vba=True)
wb2 = load_workbook(file, data_only=True, keep_vba=True)

I'd suggest using combining read-only mode with data-only mode.

sheet = wb.worksheets[0]
sheet2 = wb2.worksheets[0]

It's almost always advisable to use sheet titles.

print ("With Formula: ",sheet["C2"].value,sheet["D2"].value)
print ("With Only Data: ",sheet2["C2"].value, sheet2["D2"].value)

if not only_reading:
sheet["C2"].value = 100
wb.save("output.xlsm")
print('After writing-------------')

def saving_file(file):
# Saving data using xlwings
with xw.App(visible=False) as app:
app.display_alerts = False # Disable Excel alerts
wb = app.books.open(file) # Loads workbook
sheet = wb.sheets[0] # Turns Sheet1 active

wb.app.calculate() # Recalculating formulas

wb.save()
wb.close()

ex_temp()

Output:

With Formula: 100 =C2
With Only Data: 100 100
After writing-------------
With Formula: 100 =C2

With Only Data: 100 *100*


Basically the ideia is using xlwings library Excel to recalculate formulas and

save the file.
Reading can be done either by xlwings or openpyxl.

Hope this solutions helps someone.

Regards!

Marcelo Lopes

--

Marcelo Lopes

unread,
Oct 11, 2024, 5:27:50 PM10/11/24
to openpyxl-users
Hi Charlie,

"Openpyxl itself never evaluates formulae"...I didn't said that...I said just the opposite ("openpyxl is not able to force excel to recalculate the formulas") and because of this I presented a solution using xlwings that worked for me pretty well.

By the way...how about you to show a solution instead of keep explaining the problem?

Could you post an example of code to solve this issue? Would be more helpfull than keep telling what doesn't work, isn't it?

Best wishes

Marcelo

Charlie Clark

unread,
Oct 12, 2024, 11:47:05 AM10/12/24
to openpyxl-users

On 11 Oct 2024, at 23:27, Marcelo Lopes wrote:

Hi Charlie,

"Openpyxl itself never evaluates formulae"...I didn't said that...I said
just the opposite ("openpyxl is not able to force excel to recalculate
the formulas
") and because of this I presented a solution using xlwings
that worked for me pretty well.

What you said was misleading because Openpyxl never interacts with Excel. If you want to evaluate the formulae you could build your own engine using the tokeniser, but this

By the way...how about you to show a solution instead of keep explaining
the problem?

Why? I provided some additional information which could be useful. I've never need to do this myself, but I wanted to explain why the library behaves as it does and what alternatives are available. The issue has come up before and I'm pretty certain I've provided and example.

Could you post an example of code to solve this issue? Would be more
helpfull than keep telling what doesn't work, isn't it?

I could, or I could continue to work on the library…

Charlie

Reply all
Reply to author
Forward
0 new messages