In: Computer Science
Python 3
In the field where it says failure day indicate current date if i run the program today it show todays date and if i run the program tomorrow it show me tomorrows date and so on .
The program is rewriting the product everytime it gets executed i want to add new products as a list in excel
# required library
import tkinter as tk
from tkcalendar import DateEntry
import xlsxwriter
# frame
window = tk.Tk()
window.title("daily logs")
#window.resizable(0,0)
# labels
tk.Label(window, text="Bar code").grid(row=0, sticky="W", pady=20,
padx=20)
tk.Label(window, text="Products failed").grid(row=1, sticky="W",
pady=20, padx=20)
tk.Label(window, text="Money Lost").grid(row=2, sticky="W",
pady=20, padx=20)
tk.Label(window, text="Failed date").grid(row=3, sticky="W",
pady=20, padx=20)
# entries
barcode = tk.Entry(window)
product = tk.Entry(window)
money = tk.Entry(window)
# arraging
barcode.grid(row=0, column=1)
product.grid(row=1, column=1)
money.grid(row=2, column=1)
cal = DateEntry(window, width=12, year=2019, month=6, day=22,
background='darkblue', foreground='white', borderwidth=2)
cal.grid(row=3, column=1)
# callback function
def cleardate():
barcode.delete(0, 'end')
product.delete(0, 'end')
money.delete(0, 'end')
# callback function
def readValue():
excel_barcode = barcode.get()
excel_product = product.get()
excel_money = money.get()
date = cal._date
print(date)
workbook =
xlsxwriter.Workbook("dailylog.xlsx")
worksheet = workbook.add_worksheet()
worksheet.write("A1", "BARCODE")
worksheet.write("B1", "MONEY")
worksheet.write("C1", "PRODUCT")
worksheet.write("D1", "date")
worksheet.write("A2", excel_barcode)
worksheet.write("B2", excel_money)
worksheet.write("C2", excel_product)
worksheet.write("D2", str(date))
workbook.close()
cleardate() #calls cleardate() function
# button to trigger actions
button = tk.Button(text="SUBMIT", command=readValue).grid(row=4,
pady=20, padx=20)
button = tk.Button(text="CLEAR", command=cleardate).grid(row=4,
column=1, pady=20, padx=20)
window.geometry("500x400")
window.mainloop()
The python module xlsxwriter doesn't allow or have functionality to edit/modify existing excel files.
To add the new product in existing excel file, another python library openpyxl is required. openpyxl provides all the functionality of xlsxwriter and functionality of loading and updating existing excel files.
Updated program imports the openpyxl library (removed xlsxwriter library). if file doesn't exist it creates the file and insert header and record in the excel file. Otherwise it loads the excel file and append a new record in the file.
Complete Program:
# required library
import tkinter as tk
from tkcalendar import DateEntry
# xlsxwriter library doesn't work when update is required in
existing excel file
# openpyxl is another library which provide xlswriter functionality
and allow to modify existing files
# install openpyxl before executing the program
import openpyxl
import os.path
# frame
window = tk.Tk()
window.title("daily logs")
#window.resizable(0,0)
# labels
tk.Label(window, text="Bar code").grid(row=0, sticky="W", pady=20,
padx=20)
tk.Label(window, text="Products failed").grid(row=1, sticky="W",
pady=20, padx=20)
tk.Label(window, text="Money Lost").grid(row=2, sticky="W",
pady=20, padx=20)
tk.Label(window, text="Failed date").grid(row=3, sticky="W",
pady=20, padx=20)
# entries
barcode = tk.Entry(window)
product = tk.Entry(window)
money = tk.Entry(window)
# arraging
barcode.grid(row=0, column=1)
product.grid(row=1, column=1)
money.grid(row=2, column=1)
#use current date instead of hardcoded date
cal = DateEntry(window, width=12, background='darkblue',
foreground='white', borderwidth=2)
cal.grid(row=3, column=1)
# callback function
def cleardate():
barcode.delete(0, 'end')
product.delete(0, 'end')
money.delete(0, 'end')
# callback function
def readValue():
excel_barcode = barcode.get()
excel_product = product.get()
excel_money = money.get()
date = cal._date
# if file exist then append the new record
if os.path.exists("dailylog.xlsx"):
# load the existing excel file
workbook = openpyxl.load_workbook("dailylog.xlsx")
# get the active worksheet from the loaded workbook
worksheet = workbook.active
# append the record in the excel file and save it
worksheet.append([excel_barcode, excel_money, excel_product,
str(date)])
# Save the file
workbook.save("dailylog.xlsx")
else:
# file doesn't exist, create it with header and insert the
record
workbook = openpyxl.Workbook()
# grab the active worksheet
worksheet = workbook.active
# insert header
worksheet["A1"] = "BARCODE"
worksheet["B1"] = "MONEY"
worksheet["C1"] = "PRODUCT"
worksheet["D1"] = "date"
# insert record
worksheet["A2"] = excel_barcode
worksheet["B2"] = excel_money
worksheet["C2"] = excel_product
worksheet["D2"] = str(date)
# Save the file
workbook.save("dailylog.xlsx")
cleardate() #calls cleardate() function
# button to trigger actions
button = tk.Button(text="SUBMIT", command=readValue).grid(row=4,
pady=20, padx=20)
button = tk.Button(text="CLEAR", command=cleardate).grid(row=4,
column=1, pady=20, padx=20)
window.geometry("500x400")
window.mainloop()
Updated UI:
Updated Excel sheet: