How to write from multiple Tkinter GUI windows to an Excel file at a time?

Question

I'm new to Tkinter. Trying to write the user inputs from my multiple tkinter GUI to an Excel file using the below, still the latest value is getting overridden. I'm not able to keep the previous input values from the user to the Excel. Requesting your help here. I have used numofvm() to create number of tkinter windows, accordingly GUI's will get created and then after User will enter the values through GUI.

from tkinter import *
import xlwt


# designing window for vmdetails

def Vmdetails():
    global vmdetails_screen
    for i in range(1,N+1):
        t=i
        numb=str(t)
        vmdetails_screen = Toplevel(numofvm_screen)
        vmdetails_screen.title("VM_Details_"+ numb)
        vmdetails_screen.geometry("400x950")

        global cluster_name
        global vm_template
        global clustername_entry



        # Set text variables
        cluster_name = StringVar()
        vm_template = StringVar()


        # Set label for user's instruction
        Label(vmdetails_screen, text="Please enter details below", bg="blue").pack()
        Label(vmdetails_screen, text="").pack()

        Cluster_Name_lable = Label(vmdetails_screen, text="Cluster_Name "+ numb +"* ")
        Cluster_Name_lable.pack()

        # Set textvariables entry

        clustername_entry = Entry(vmdetails_screen, textvariable=cluster_name)
        clustername_entry.pack()

        Button(vmdetails_screen, text="Submit", width=10, height=1, command=check).pack()


def check():
    # Retrieve the value from the entry and store it to a variable
    global var
    if cluster_name.get() == '':
        invalid_value()
    else:
     write_to_xls()

def invalid_value():
    global invalid_value_screen
    invalid_value_screen = Toplevel(vmdetails_screen)
    invalid_value_screen.title("Invalid Entry")
    invalid_value_screen.geometry("250x100")
    Label(invalid_value_screen, text="Enter valid values for all the required fields").pack()
    Button(invalid_value_screen, text="OK", command=delete_invalid_value).pack()


def delete_invalid_value():
    invalid_value_screen.destroy()


# designing window to provide the number of vm's to be created

def numofvm():

    global numofvm_screen
    numofvm_screen = Toplevel(main_screen)
    numofvm_screen.title("VM_NUM")
    numofvm_screen.geometry("300x250")

    global vmnumber
    global vmnumber_entry
    vmnumber = StringVar()

    Label(numofvm_screen, text="Please enter the Number of VM's you wishes to create ", bg="blue").pack()
    Label(numofvm_screen, text="").pack()

    vmnumber_lable = Label(numofvm_screen, text="Number of VM's to be created * ")
    vmnumber_lable.pack()

    vmnumber_entry = Entry(numofvm_screen, textvariable=vmnumber)
    vmnumber_entry.pack()

    Button(numofvm_screen, text="Submit", width=10, height=1, command=screen_duplicate).pack()


def screen_duplicate():
    global N
    N = int(vmnumber.get())
    if N > 0  :
      Vmdetails()

#exporting the user inputs into an excel sheet

def write_to_xls():
    # create new workbook
    wb = xlwt.Workbook()

    for i in range(1,N+1):
        t=i
        numb=str(t)
    # add sheet using given name
        ws = wb.add_sheet("VM_"+numb+"_DETAILS")

    # write text to cell
        ws.write(0, 0, "Cluster_Name")
        ws.write(1, 0,cluster_name.get())


    # save to given file name
    wb.save('my_file.xls')


def main_account_screen():
    global main_screen
    main_screen = Tk()
    main_screen.geometry("300x250")
    main_screen.title("Welcome")
    Label(text="Select Your Choice", bg="blue", width="300", height="2", font=("Calibri", 13)).pack()
    Label(text="").pack()
    Button(text="Enter", height="2", width="30", command=numofvm).pack()
    Label(text="").pack()
    main_screen.mainloop()


main_account_screen()

I believe the problem stems from the `for` loop in the `Vmdetails()` function does: namely it creates and assigns `N` different `StringVar()`s to the `cluster_name` and `vm_template` global variables, which means that afterward, only the last one is remains. This is affecting the `check()` function which always retrieves the value of the _last_ one created, not necessarily the one the user may have just entered a value into. Because of this problem, I have not been able to get the part that writes and saves the `.xls` file. Anyhow I think the file writing issue has the same cause. — martineau, Mar 04 '19 at 08:43
Thanks,Yes, that's what I think too, still any clue on how to resolve this? — SUBHENDU PANDA, Mar 04 '19 at 09:38
Yes, you need to store them each in some variable-sized container data-structure like a `list` and reference them only via indices after they're created. — martineau, Mar 04 '19 at 12:07

score 1 · Accepted Answer · answered Mar 04 '19 at 22:34

As I explained in a comment, the data writing problem is because you need to store the StringVars and related information is lists instead of overwriting the previous values by only using a single-value variables.

While fixing that would be possible without doing so, your use of so many global variables makes the code hard to understand, debug, and modify—which are all reason the common wisdom is to avoid them as much as possible.

Here's a list the ones the code in your question uses:

cluster_name, clustername_entry, vm_template, vmdetails_screen, invalid_value_screen, numofvm_screen, vmnumber, vmnumber_entry, N, main_screen

Since that's a quite a few, I decided to also eliminate them as well in the code below to make the design "cleaner" besides only showing how to store the data in lists as suggested. Hopefully this will provide you with a better foundation for the continued development of your application.

To avoid the globals, most of them have been turned into attributes of an class representing your entire application—which is a design based on this answer to anther tkinter question by tkinter guru @Bryan Oakley.

from tkinter import *
import tkinter.messagebox as tkMessageBox
import os
import xlwt


class MyApp(Tk):
    LABEL_BG = "light blue"

    def __init__(self):
        Tk.__init__(self)
        self.geometry("300x250")
        self.title("Welcome")
        Label(text="Select Your Choice", bg=self.LABEL_BG, width="300", height="2",
              font=("Calibri", 13)).pack()
        Label(text="").pack()
        Button(text="Enter", height="2", width="30", command=self.numofvm).pack()
        Label(text="").pack()

    def numofvm(self):
        """ Designing window to provide the number of VMs to be created. """
        self.numofvm_screen = Toplevel(self)
        self.numofvm_screen.title("VM_NUM")
        self.numofvm_screen.geometry("300x250")

        self.vmnumber = StringVar()

        Label(self.numofvm_screen,
              text="Please enter the Number of VMs you wishes to create",
              bg=self.LABEL_BG).pack()
        Label(self.numofvm_screen, text="").pack()

        vmnumber_lable = Label(self.numofvm_screen,
                               text="Number of VMs to be created:")
        vmnumber_lable.pack()

        self.vmnumber_entry = Entry(self.numofvm_screen, textvariable=self.vmnumber)
        self.vmnumber_entry.pack()

        Button(self.numofvm_screen, text="Submit", width=10, height=1,
               command=self.screen_duplicate).pack()

        self.numofvm_screen.focus_set()

    def screen_duplicate(self):
        try:
            self.N = int(self.vmnumber.get())
        except ValueError:
            self.N = 0

        if self.N > 0:
            self.vm_details()

    def vm_details(self):
        """ Create designing windows for VM details.
        """
        # Preallocate and then create VM detail screens and data.
        self.cluster_names = [None for _ in range(self.N)]
        self.clustername_entries = [None for _ in range(self.N)]
        self.vm_templates = [None for _ in range(self.N)]
        self.vmdetails_screen = [None for _ in range(self.N)]

        for i in range(self.N):
            numb = str(i+1)
            self.vmdetails_screen[i] = Toplevel()
            self.vmdetails_screen[i].title("VM Details " + numb)
            self.vmdetails_screen[i].geometry("400x950")

            # Set text variables
            self.cluster_names[i] = StringVar()
            self.vm_templates[i] = StringVar()

            # Set label for user's instruction
            Label(self.vmdetails_screen[i], text="Please enter details below:",
                  bg=self.LABEL_BG).pack()
            Label(self.vmdetails_screen[i], text="").pack()

            Cluster_Name_lable = Label(self.vmdetails_screen[i],
                                       text="Cluster Name "+ numb + ":")
            Cluster_Name_lable.pack()

            # Set textvariables entry
            self.clustername_entries[i] = Entry(self.vmdetails_screen[i],
                                                textvariable=self.cluster_names[i])
            self.clustername_entries[i].pack()

            Button(self.vmdetails_screen[i], text="Submit", width=10, height=1,
                   command=self.validate).pack()

    def validate(self):
        """ Check values of ALL cluster name entries and save them to excel
            file if they're all valid.
        """
        if not all(cluster_name.get() for cluster_name in self.cluster_names):
            self.invalid_value()
        else:
            self.write_to_xls()
            tkMessageBox.showinfo("Info", '"%s" file written' % self.xls_filepath)

            # Get rid of all data entry screens.
            for i in range(self.N):
                self.vmdetails_screen[i].destroy()

            self.numofvm_screen.destroy()

    def invalid_value(self):
        """ Display error message screen.
        """
        self.invalid_value_screen = Toplevel()
        self.invalid_value_screen.title("Invalid Entry")
        self.invalid_value_screen.geometry("400x100")
        Label(self.invalid_value_screen,
              text="Please enter valid values for the fields in ALL\n"
                   "VM Detail windows before clicking Submit button").pack()
        Button(self.invalid_value_screen, text="OK",
               command=lambda: self.invalid_value_screen.destroy()).pack()

    def write_to_xls(self):
        """ Export all user's inputs to an excel sheet. """

        # Create new workbook.
        wb = xlwt.Workbook()

        for i in range(self.N):
            numb = str(i+1)
            # Add sheet using given name.
            ws = wb.add_sheet("VM_" + numb + "_DETAILS")

            # Write text to cell.
            ws.write(0, 0, "Cluster_Name")
            ws.write(1, 0, self.cluster_names[i].get())

        # Save excel file in same directory as script.
        self.xls_filepath = os.path.join(os.path.dirname(__file__), 'my_file.xls')
        wb.save(self.xls_filepath)


if __name__ == '__main__':
    root = MyApp()
    root.mainloop()

SUBHENDU: You're welcome. Suggest you read the accepted answer to the quesiton [Best way to structure a tkinter application](https://stackoverflow.com/a/17470842/355230). — martineau, Mar 08 '19 at 16:46

How to write from multiple Tkinter GUI windows to an Excel file at a time?

1 Answers1