Python Tkinter and openpyxl return to new row on button push

Question

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.

What is it that you think `ws[cellx]=E1.get()`, when `cellx = 'A1'` *explicitly in the previous statement*, should be doing? What do you think `cellx` should be to make this work? — Scott Hunter, Oct 11 '21 at 13:26
if you write in `A1` then you write in first row. If you want to save in new row then when you press button then you have to change `A1` into `A2`, next into `A3`, etc. Because `A1` is a text then you can't do `"A1"+1`. YOu have to keep row number in other variable - `row = 1` and later do `row +=1` , `cellx = f"A{row}"` — furas, Oct 11 '21 at 14:11

score 1 · Answer 1 · answered Oct 11 '21 at 16:06

1

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)

answered Oct 11 '21 at 16:06

acw1668

40,144
5
22
34

This worked perfect thank you I was having issues with the append function earlier but I realized my mistake after seeing your example code. Thank you! – Craig Walker Oct 13 '21 at 16:17

furas · Answer 2 · 2021-10-11T14:26:43.687

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.

Python Tkinter and openpyxl return to new row on button push

2 Answers2