Requirement : 1.create a gui using Tkinter 2.Update the excel by fetching values from Tkinter entry widget 3.Read another sheet of the same workbook 4.plot graph using inside the Tkinter window.

Problem: All the functionality is working fine, except when modifying and reading one after another at same time.

Loaded the work book with data_only=False to preserve formulas. I have modified the excel successfully in "INPUT" sheet.Then when I am reading the cells from "SIMULATION" sheets which are linked to the "Input" sheets with formulas , no data is coming.

Opening the excel file with Excel and closing it ,and now if i run the python program again without the modify functionality, program is able to read cell value and plot graph.

During read functionality of the program workbook is loaded 2nd time using data_only = True to get cell values .

Any suggestions will be very helpful for me.

from tkinter import *
from tkinter import ttk
from openpyxl import load_workbook
import datetime
import matplotlib
matplotlib.use("TkAgg")
from matplotlib.backends.backend_tkagg import FigureCanvasTkAgg
from matplotlib.figure import Figureclass Root(Tk):f = Figure(figsize=(20,8))a = f.add_subplot(111)  entryString1=0entryString2=0entryString3=0entryString4=0entryString5=0entryString6=0def __init__(self):super(Root, self).__init__()self.title("Python Tkinter Dialog Widget")self.state("zoomed")self.frame = ttk.Frame(self,borderwidth=2, relief="solid")self.frame.pack(side='top',fill=X,padx=10,pady=20)self.entry()self.button()self.canvas = FigureCanvasTkAgg(self.f, self.frame)self.canvas.get_tk_widget().pack(fill="both",padx=10,pady=20)self.canvas.draw()def entry(self):self.frame2 = ttk.Frame(self)self.frame2.pack(fill=X,padx=10,pady=20)self.labelentry1 = ttk.Label(self.frame2,text = "V Past Main begin period:   ")self.labelentry1.pack(fill=X,side='left',anchor=W)self.entry1 = ttk.Entry(self.frame2)self.entry1.pack(fill=X,side='left')self.labelentry2 = ttk.Label(self.frame2,text = "  V Past PS begin period:   ")self.labelentry2.pack(fill=X,side='left')self.entry2 = ttk.Entry(self.frame2)self.entry2.pack(fill=X,side='left')self.labelentry3 = ttk.Label(self.frame2,text = "  Adv 0% Main:   ")self.labelentry3.pack(fill=X,side='left')self.entry3 = ttk.Entry(self.frame2)self.entry3.pack(fill=X,side='left')self.labelentry4 = ttk.Label(self.frame2,text = "  Adv 0% PS:   ")self.labelentry4.pack(fill=X,side='left')self.entry4 = ttk.Entry(self.frame2)self.entry4.pack(fill=X,side='left')self.labelentry5 = ttk.Label(self.frame2,text = "  Single premium already paid:   ")self.labelentry5.pack(fill=X,side='left')self.entry5 = ttk.Entry(self.frame2)self.entry5.pack(fill=X,side='left')self.labelentry6 = ttk.Label(self.frame2,text = "  Yearly premium already  paid:   ")self.labelentry6.pack(fill=X,side='left')self.entry6 = ttk.Entry(self.frame2)self.entry6.pack(fill=X,side='left')def button(self):self.frame3 = ttk.Frame(self)self.frame3.pack(side='bottom')self.labelFrame = ttk.LabelFrame(self.frame3,text = "Regenrate Graph")self.labelFrame.pack(padx=10)self.button = ttk.Button(self.labelFrame, text = "Click",command = self.fileDialog)self.button.pack(padx=10)def fileDialog(self):self.entryString1 = self.entry1.get()self.entryString2 = self.entry2.get()self.entryString3 = self.entry3.get()self.entryString4 = self.entry4.get()self.entryString5 = self.entry5.get()self.entryString6 = self.entry6.get()#Load excel file self.filename = "C:\\Users\\ramit\\Desktop\\Projection V0.3_Shankha .xlsx"#load excel file to modifyself.work_book = load_workbook (self.filename)self.sheet = self.work_book['Inputs']if len(self.entryString1) != 0:self.sheet.cell(row=4,column=5).value=int(self.entryString1)if len(self.entryString2) != 0:self.sheet.cell(row=5,column=5).value=int(self.entryString2)if len(self.entryString3) != 0:self.sheet.cell(row=6,column=5).value=int(self.entryString3)if len(self.entryString4) != 0:self.sheet.cell(row=7,column=5).value=int(self.entryString4)if len(self.entryString5) != 0:self.sheet.cell(row=9,column=5).value=int(self.entryString5)if len(self.entryString6) != 0:self.sheet.cell(row=10,column=5).value=int(self.entryString6)self.work_book.save(self.filename)self.work_book = None#load excel file to read self.work_book = load_workbook (self.filename,data_only=True)self.sheet_1 = self.work_book['Simulation']self.x = []self.y = []for i in range(10, 17):self.x.append (self.sheet_1.cell(row=i + 1,column=1).value)self.y.append (self.sheet_1.cell(row=i + 1,column= 77).value)self.a.clear()print(self.x)print(self.y)self.a.set_xlabel('Simulation date')self.a.set_ylabel('Reserve')self.a.plot(self.x, self.y, color='cyan', label='Projection')  self.canvas.draw()root = Root()root.mainloop()

The issue is that openpyxl doesn't evaluate the formula in excel. it will only return the last value saved by excel or 'None' (with data_only=True). The latter is what is happening when you change an input cell and use [cell].value to call the value of the cell with the formula. When you don't change the sheet, you get the value set in excel, which is why your code works when you disable/don't do the input to excel functionality.

Easiest way to around the issue is to use xlwings, this should work the way you intend it to. There are also a few other options. such as directly using windows excel commands to update the sheet then using openpyxl to read but I feel swapping modules is the simpler solution. You may also want to consider bringing the function to the python side of the process and just writing the result to the excel sheet.

how to get formula result in excel using xlwings

import xlwings as xw
sheet = xw.Book(r'C:/path/to/file.xlsx').sheets['sheetname']
result = sheet['X2'].value