Python Tkinter and openpyxl return to new row on button push-CodePudding

I am trying to write to an excel file (LibreOffice Calc not actually excel). I am trying to save historical data and then graph for status updates at our quarterly meetings (I can get the information to the graph when formatted correctly so that isn't an issue).

However, I'm running into an issue when I press a button on my Tkinter window the information I want to be written is recorded in my excel file but every time I push the button it overwrites the first row. Below is an oversimplified example of my code. I have no idea what else to add to my def function to return to a new line so I don't overwrite my information. Thoughts?

from tkinter import *
from openpyxl import Workbook

def excel ():
     wb=Workbook()
     ws=wb.active
     cellx='A1'
     ws[cellx]=E1.get()
     wb.save("sample.xlsx")
     #Assumeing I need something similar to ws.cell(row=cellx 1) but it doesn't work.

root=Tk()

L1=Label(root,text="Tester")
E1=Entry(root,width=10)
b1=Button(root,text="Save",command=excel)

L1.pack()
E1.pack()
b1.pack()

root.mainloop()

In the actual code, there will be some entry boxes that contain words and some that will only contain numbers.

CodePudding user response：

You use all time the same cellx = "A1" so you alwasy get in the same row. You have to increase number in "A1" and use "A2", "A3", etc. But "A1" is a string and you can't do "A1" 1

You have to keep number as integer, increase it, and late convert it to string with char "A"

# outside function
row = 0

# inside function
global row

row  = 1
cellx = f"A{row}"

You have to set row = 0 outside function (to create global variable) and inside function you have to use global to inform function that it has to keep new value in external/global variable.

import tkinter as tk  # PEP8: `import *` is not preferred

from openpyxl import Workbook

def excel():
    global row   # inform function that it has to assing new value to global variable
    
    row  = 1
    
    cellx = f"A{row}"
    print('cellx:', cellx)
    
    wb = Workbook()  # PEP8: spaces around `=`
    ws = wb.active
     
    ws[cellx] = e1.get()
    wb.save("sample.xlsx")

# ---

row = 0  # global variable (it doesn't need word `global`)

root = tk.Tk()  # PEP8: spaces around `=`

l1 = tk.Label(root, text="Tester")  # PEP8: space after `,`
e1 = tk.Entry(root, width=10)  # PEP8: `lower_case_names` for variables
b1 = tk.Button(root, text="Save", command=excel)

l1.pack()
e1.pack()
b1.pack()

root.mainloop()

PEP 8 -- Style Guide for Python Code

BTW:

If you run again script then it starts again from A1 and replace old content. If you want to add to new row(s) then you would have to keep value row in file and read it at start, and save it when you close program (or when you save Excel). OR you would have to find method in openpyxl to detect which row is empty.

CodePudding user response：

You can simply use ws.append(...) to append the input text into next empty row:

from openpyxl import Workbook, load_workbook

FILENAME = "sample.xlsx"

def excel():
    try:
        # load workbook if it exists already
        wb = load_workbook(filename=FILENAME)
    except FileNotFoundError:
        # create new workbook
        wb = Workbook()
    ws = wb.active
    ws.append([E1.get()]) # append to next empty row
    wb.save(FILENAME)