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?

[Akshara Ramasubbu]

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]

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

[Marcelo Lopes]

Hi, Charlie.

"still" mencioned by Akshara means that the issue reported by Aman in the beggining of this thread still happens even applying the suggested solutions.

I'm facing the same issue: 

1. Openpyxl save the .xlsx file; 
2. Openpyxl read the file again; 
3. All cells containing formulas return 'None' instead of the formulas results.

If the excel file is manually opened, saved and closed, every formula cells return their values normally.

I've tried to "reopen and save" with win32com.client import Dispatch as suggested but it didn't work.

Is ther any other suggestion?

Regards

Marcelo Lopes