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

6,710 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?
Reply all
Reply to author
Forward
0 new messages