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

[Aman Verma]

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

[Charlie Clark]

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

[agow...@gmail.com]

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]

you mast write (filename =xxxxxxx)
wb = openpyxl.load_workbook(filename="input.xlsm",data_only=False, keep_vba=True)

[Y. Taborda]

How can I solve it if I have a linux machine?