I have two windows in this PyQt5 GUI application.
I need to call a function in the first window from second window on button press event. ( I am using python 3.8.10 )
I just want to load data from excel when I press an insert button in the second window. But loadExcelData() is in the first window class.
I have tried this method :
class second(QtWidgets.QMainWindow, Ui_MainWindow2):
def __init__(self):
super().__init__()
self.setupUi(self)
self.insertDatabtn.clicked.connect(self.getAndInsert)
def getAndInsert(self):
f1 = first()
f1.loadExcelData()
Full code :
import sys
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtWidgets import *
import pandas as pd
from datetime import datetime
from dateutil import relativedelta
import ast,time
class Ui_MainWindow1(object):
def setupUi(self, MainWindow):
MainWindow.setObjectName("MainWindow")
MainWindow.resize(710,500)
MainWindow.setMinimumSize(QtCore.QSize(264, 248))
self.centralwidget = QtWidgets.QWidget(MainWindow)
self.centralwidget.setObjectName("centralwidget")
self.insertWindowbtn = QtWidgets.QPushButton(self.centralwidget)
self.insertWindowbtn.setGeometry(QtCore.QRect(546, 30, 135, 51))
# EDIT
font = QtGui.QFont()
font.setFamily("Calibri")
font.setPointSize(20)
font.setItalic(False)
font.setBold(True)
self.headlabel = QtWidgets.QLabel(self.centralwidget)
self.headlabel.setGeometry(QtCore.QRect(50, 30, 221, 51))
self.headlabel.setFont(font)
#self.headlabel.setText('Expiration Detector')
self.headlabel.setObjectName("headlabel")
# END
font = QtGui.QFont()
font.setFamily("Lucida Sans")
font.setPointSize(12)
font.setItalic(False)
# EDIT 2
self.profilebtn = QtWidgets.QPushButton(self.centralwidget)
self.profilebtn.setGeometry(QtCore.QRect(546, 85, 135, 51))
self.profilebtn.setFont(font)
self.profilebtn.setText('')
self.profilebtn.setIcon(QtGui.QIcon(QtGui.QPixmap(r"user.png")))
self.profilebtn.setIconSize(QtCore.QSize(28, 28))
self.profilebtn.setText(" Profile ")
self.profilebtn.setObjectName("profilebtn")
# END 2
self.insertWindowbtn.setFont(font)
self.insertWindowbtn.setIcon(QtGui.QIcon(QtGui.QPixmap('plus.png')))
self.insertWindowbtn.setIconSize(QtCore.QSize(28, 28))
self.insertWindowbtn.setObjectName("insertWindowbtn")
self.tableWidget = QtWidgets.QTableWidget(self.centralwidget)
self.tableWidget.setGeometry(QtCore.QRect(20, 150, 660, 321))
self.tableWidget.setObjectName("tableWidget")
MainWindow.setCentralWidget(self.centralwidget)
self.statusbar = QtWidgets.QStatusBar(MainWindow)
self.statusbar.setObjectName("statusbar")
MainWindow.setStatusBar(self.statusbar)
self.retranslateUi(MainWindow)
QtCore.QMetaObject.connectSlotsByName(MainWindow)
def retranslateUi(self, MainWindow):
_translate = QtCore.QCoreApplication.translate
MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
self.insertWindowbtn.setText(_translate("MainWindow", " Add Data"))
def show_new_window(self, checked):
self.w = second()
self.w.show()
class Ui_MainWindow2(object):
def setupUi(self, MainWindow):
MainWindow.setObjectName("MainWindow")
MainWindow.resize(484,338)
MainWindow.setMinimumSize(QtCore.QSize(264, 248))
self.centralwidget = QtWidgets.QWidget(MainWindow)
self.centralwidget.setObjectName("centralwidget")
layout = QtWidgets.QVBoxLayout()
#MainWindow.setLayout(layout)
MainWindow.setWindowTitle("Insert Data Window")
self.insertDatabtn = QtWidgets.QPushButton(self)
self.insertDatabtn.setGeometry(QtCore.QRect(330, 250, 121, 41))
font = QtGui.QFont()
font.setFamily("Lucida Sans")
font.setPointSize(12)
self.insertDatabtn.setFont(font)
self.insertDatabtn.setObjectName("insertDatabtn")
self.label = QtWidgets.QLabel(self)
self.label.setGeometry(QtCore.QRect(20, 20, 101, 31))
font = QtGui.QFont()
font.setFamily("Lucida Sans")
font.setPointSize(12)
self.label.setFont(font)
self.label.setObjectName("label")
self.label_2 = QtWidgets.QLabel(self)
self.label_2.setGeometry(QtCore.QRect(20, 80, 101, 31))
font = QtGui.QFont()
font.setFamily("Lucida Sans")
font.setPointSize(12)
self.label_2.setFont(font)
self.label_2.setObjectName("label_2")
self.label_3 = QtWidgets.QLabel(self)
self.label_3.setGeometry(QtCore.QRect(20, 140, 101, 31))
font = QtGui.QFont()
font.setFamily("Lucida Sans")
font.setPointSize(12)
self.label_3.setFont(font)
self.label_3.setObjectName("label_3")
self.label_4 = QtWidgets.QLabel(self)
self.label_4.setGeometry(QtCore.QRect(20, 200, 101, 31))
font = QtGui.QFont()
font.setFamily("Lucida Sans")
font.setPointSize(12)
self.label_4.setFont(font)
self.label_4.setObjectName("label_4")
self.txtDescription = QtWidgets.QLineEdit(self)
self.txtDescription.setGeometry(QtCore.QRect(150, 22, 271, 31))
font = QtGui.QFont()
font.setFamily("Lucida Sans")
font.setPointSize(12)
self.txtDescription.setFont(font)
self.txtDescription.setObjectName("txtDescription")
self.txtSerialno = QtWidgets.QLineEdit(self)
self.txtSerialno.setGeometry(QtCore.QRect(150, 82, 271, 31))
font = QtGui.QFont()
font.setFamily("Lucida Sans")
font.setPointSize(12)
self.txtSerialno.setFont(font)
self.txtSerialno.setObjectName("txtSerialno")
self.dateExpire = QtWidgets.QDateEdit(self)
self.dateExpire.setGeometry(QtCore.QRect(150, 200, 131, 31))
font = QtGui.QFont()
font.setFamily("Lucida Console")
font.setPointSize(12)
self.dateExpire.setFont(font)
self.dateExpire.setDateTime(QtCore.QDateTime(QtCore.QDate(2020, 12, 30), QtCore.QTime(0, 0, 0)))
self.dateExpire.setCurrentSection(QtWidgets.QDateTimeEdit.DaySection)
self.dateExpire.setCalendarPopup(True)
self.dateExpire.setObjectName("dateExpire")
self.dateIssue = QtWidgets.QDateEdit(self)
self.dateIssue.setGeometry(QtCore.QRect(150, 140, 131, 31))
font = QtGui.QFont()
font.setFamily("Lucida Console")
font.setPointSize(12)
self.dateIssue.setFont(font)
self.dateIssue.setDateTime(QtCore.QDateTime(QtCore.QDate(2020, 12, 30), QtCore.QTime(0, 0, 0)))
self.dateIssue.setCurrentSection(QtWidgets.QDateTimeEdit.DaySection)
self.dateIssue.setCalendarPopup(True)
self.dateIssue.setObjectName("dateIssue")
# Set
self.insertDatabtn.setText("Insert Data")
self.label.setText("Description")
self.label_2.setText("Serial No")
self.label_3.setText("Issue Date")
self.label_4.setText("Expire Date")
self.dateExpire.setDisplayFormat("dd/MM/yyyy")
self.dateIssue.setDisplayFormat("dd/MM/yyyy")
class first(QtWidgets.QMainWindow, Ui_MainWindow1):
def __init__(self):
super().__init__()
self.setupUi(self)
self.setWindowTitle("Expiration Monitor")
# Load data from excel file
df = pd.read_excel("CadetData.xlsx", "Sheet1")
if df.size == 0:
return
dfc = str(df.columns) #Extracting list of header labels
HeaderLabels = ast.literal_eval(dfc[dfc.find('['):dfc.find(']')+1])
for i in HeaderLabels :
for i in HeaderLabels :
if 'Unnamed' in i :
HeaderLabels.remove(str(i))
HeaderLabels.append('Remaining Time')
HeaderLabels.append('Remaining %')
df.fillna('', inplace=True)
self.tableWidget.setRowCount(df.shape[0])
self.tableWidget.setColumnCount(df.shape[1]+2)
self.tableWidget.setHorizontalHeaderLabels(HeaderLabels)
ExpireDates = []
IssueDates = []
# returns pandas array object
for row in df.iterrows():
values = row[1]
ExpireDates.append(values['Expire Date'])
IssueDates.append(values['Issue Date'])
for col_index, value in enumerate(values):
#if isinstance(value, (float, int)):
# value = '{0:0,.0f}'.format(value)
tableItem = QtWidgets.QTableWidgetItem(str(value))
self.tableWidget.setItem(row[0], col_index, tableItem)
for i in range(len(ExpireDates)) :
end_date = datetime.strptime(ExpireDates[i], "%d/%m/%Y")
start_date = datetime.strptime(datetime.today().strftime('%d/%m/%Y'),"%d/%m/%Y")
# Get the relativedelta between two dates
delta = relativedelta.relativedelta(end_date, start_date)
# Time to expire
if delta.years < 0 or delta.months < 0 or delta.days <0 :
remtime = '0 Years 0 Months 0 Days'
else :
remtime = str(delta.years)+' Years,'+str(delta.months)+' Months,'+str(delta.days)+' Days'
end_date2 = datetime.strptime(ExpireDates[i], "%d/%m/%Y")
start_date2 = datetime.strptime(IssueDates[i], "%d/%m/%Y")
# Get the relativedelta between two dates
delta2 = relativedelta.relativedelta(end_date2, start_date2)
#total days from issue date to expire date
totday = (delta2.years*365)+(delta2.months*30)+delta2.days
tabItem = QtWidgets.QTableWidgetItem(remtime)
self.tableWidget.setItem(i, 4, tabItem)
# Remaining days to expire
remday = (delta.years*365)+(delta.months*30)+delta.days
# Remaining months to expire
#remmonth = (delta.years*12)+delta.months
if remday >= (12*30) :
Pcolor = "#1ac73a" # Green color
elif remday > (6*30) :
Pcolor = "#db8514" # Orange color
elif remday >= (3*30) or remday < (3*30) :
Pcolor = "#d61515" # Red color
Pbar = QtWidgets.QProgressBar(self)
Pstyle = """
QProgressBar {
border: 2px solid #5c5c5c;
border-radius: 5px;
text-align: center;
}
QProgressBar::chunk {
background-color: """+Pcolor+""";
width: 20px;
}
"""
Pbar.setStyleSheet(Pstyle)
Pbar.setFormat("%p%")
self.tableWidget.setCellWidget(i,5,Pbar)
prec = int((remday/totday)*100)
if prec<0:
prec=0
Pbar.setValue(prec)
self.tableWidget.setColumnWidth(0, 110)
self.tableWidget.setColumnWidth(1, 100)
self.tableWidget.setColumnWidth(2, 70)
self.tableWidget.setColumnWidth(3, 70)
self.tableWidget.setColumnWidth(4, 150)
self.tableWidget.setColumnWidth(5, 140)
for x in range(df.shape[0]):
self.tableWidget.setRowHeight(x,28)
self.tableWidget.setEditTriggers(QtWidgets.QTableWidget.NoEditTriggers)
# Button Connects
self.insertWindowbtn.clicked.connect(self.OpenInsertWindow)
def OpenInsertWindow(self):
self.show_new_window(self)
"""
def InsertData(self):
from openpyxl import load_workbook
myFileName=r'CadetData.xls'
#load the workbook, and put the sheet into a variable
wb = load_workbook(filename=myFileName)
ws = wb['Sheet1']
#max_row is a sheet function that gets the last row in a sheet.
newRowLocation = ws.max_row +1
#write data
ws.cell(column=1,row=newRowLocation, value="aha! a new entry at the end")
wb.save(filename=myFileName)
wb.close()"""
def loadExcelData(self):
print('Fn Call 200')
# Load data from excel file
df = pd.read_excel("CadetData.xlsx", "Sheet1")
if df.size == 0:
return
dfc = str(df.columns) #Extracting list of header labels
HeaderLabels = ast.literal_eval(dfc[dfc.find('['):dfc.find(']')+1])
for i in HeaderLabels :
for i in HeaderLabels :
if 'Unnamed' in i :
HeaderLabels.remove(str(i))
HeaderLabels.append('Remaining Time')
HeaderLabels.append('Remaining %')
df.fillna('', inplace=True)
self.tableWidget.setRowCount(df.shape[0])
self.tableWidget.setColumnCount(df.shape[1]+2)
self.tableWidget.setHorizontalHeaderLabels(HeaderLabels)
ExpireDates = []
IssueDates = []
# returns pandas array object
for row in df.iterrows():
values = row[1]
ExpireDates.append(values['Expire Date'])
IssueDates.append(values['Issue Date'])
for col_index, value in enumerate(values):
#if isinstance(value, (float, int)):
# value = '{0:0,.0f}'.format(value)
tableItem = QtWidgets.QTableWidgetItem(str(value))
self.tableWidget.setItem(row[0], col_index, tableItem)
for i in range(len(ExpireDates)) :
end_date = datetime.strptime(ExpireDates[i], "%d/%m/%Y")
start_date = datetime.strptime(datetime.today().strftime('%d/%m/%Y'),"%d/%m/%Y")
# Get the relativedelta between two dates
delta = relativedelta.relativedelta(end_date, start_date)
# Time to expire
if delta.years < 0 or delta.months < 0 or delta.days <0 :
remtime = '0 Years 0 Months 0 Days'
else :
remtime = str(delta.years)+' Years,'+str(delta.months)+' Months,'+str(delta.days)+' Days'
end_date2 = datetime.strptime(ExpireDates[i], "%d/%m/%Y")
start_date2 = datetime.strptime(IssueDates[i], "%d/%m/%Y")
# Get the relativedelta between two dates
delta2 = relativedelta.relativedelta(end_date2, start_date2)
#total days from issue date to expire date
totday = (delta2.years*365)+(delta2.months*30)+delta2.days
tabItem = QtWidgets.QTableWidgetItem(remtime)
self.tableWidget.setItem(i, 4, tabItem)
# Remaining days to expire
remday = (delta.years*365)+(delta.months*30)+delta.days
# Remaining months to expire
#remmonth = (delta.years*12)+delta.months
if remday >= (12*30) :
Pcolor = "#1ac73a" # Green color
elif remday > (6*30) :
Pcolor = "#db8514" # Orange color
elif remday >= (3*30) or remday < (3*30) :
Pcolor = "#d61515" # Red color
Pbar = QtWidgets.QProgressBar(self)
Pstyle = """
QProgressBar {
border: 2px solid #5c5c5c;
border-radius: 5px;
text-align: center;
}
QProgressBar::chunk {
background-color: """+Pcolor+""";
width: 20px;
}
"""
Pbar.setStyleSheet(Pstyle)
Pbar.setFormat("%p%")
self.tableWidget.setCellWidget(i,5,Pbar)
prec = int((remday/totday)*100)
if prec<0:
prec=0
Pbar.setValue(prec)
self.tableWidget.setColumnWidth(0, 110)
self.tableWidget.setColumnWidth(1, 100)
self.tableWidget.setColumnWidth(2, 70)
self.tableWidget.setColumnWidth(3, 70)
self.tableWidget.setColumnWidth(4, 150)
self.tableWidget.setColumnWidth(5, 140)
for x in range(df.shape[0]):
self.tableWidget.setRowHeight(x,28)
self.tableWidget.setEditTriggers(QtWidgets.QTableWidget.NoEditTriggers)
# Button Connects
self.insertWindowbtn.clicked.connect(self.OpenInsertWindow)
class second(QtWidgets.QMainWindow, Ui_MainWindow2):
def __init__(self):
super().__init__()
self.setupUi(self)
self.insertDatabtn.clicked.connect(self.getAndInsert)
def getAndInsert(self):
dataCol1 = self.txtDescription.text()
dataCol2 = self.txtSerialno.text()
dataCol3 = self.dateIssue.text()
dataCol4 = self.dateExpire.text()
from openpyxl import load_workbook
myFileName=r'CadetData.xlsx'
#load the workbook, and put the sheet into a variable
wb = load_workbook(filename=myFileName)
ws = wb['Sheet1']
#max_row is a sheet function that gets the last row in a sheet.
#newRowLocation = ws.max_row +1
for max_row, row in enumerate(ws, 1):
if all(c.value is None for c in row):
newRowLocation = max_row
break
#write data
try:
ws.cell(column=1,row=newRowLocation, value=dataCol1)
ws.cell(column=2,row=newRowLocation, value=dataCol2)
ws.cell(column=3,row=newRowLocation, value=dataCol3)
ws.cell(column=4,row=newRowLocation, value=dataCol4)
wb.save(filename=myFileName)
wb.close()
except Exception as e:
msg1 = QtWidgets.QMessageBox()
#msg1.setIcon(QtWidgets.QMessageBox.warning)
msg1.setText(str(e))
msg1.setWindowTitle("Error")
msg1.setStandardButtons(QtWidgets.QMessageBox.Ok)
msg1.exec_()
else:
f1 = first() # <<
f1.loadExcelData() # << Is this correct ?
msg2 = QtWidgets.QMessageBox()
#msg2.setIcon(QtWidgets.QMessageBox.information)
msg2.setText("Successfully Inserted Data.")
msg2.setWindowTitle("Success")
msg2.setStandardButtons(QtWidgets.QMessageBox.Ok)
msg2.exec_()
if __name__ == "__main__":
import sys
app = QtWidgets.QApplication(sys.argv)
MainWindow = first()
MainWindow.show()
sys.exit(app.exec_())
Is there any other method? Because this method does not work. I have searched many times and couldn't find a solution for this problem.
To access the first window from the second window, you can pass a reference to the first window in the __init__ for the second window and set it as an attribute. Then you can use self.first_window wherever you need to access the first window within the second window (and vice versa).
I have made all the necessary changes to your example in the code below (search for #-> to find them all):
import sys
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtWidgets import *
import pandas as pd
from datetime import datetime
from dateutil import relativedelta
import ast,time
class Ui_MainWindow1(object):
def setupUi(self, MainWindow):
MainWindow.setObjectName("MainWindow")
MainWindow.resize(710,500)
MainWindow.setMinimumSize(QtCore.QSize(264, 248))
self.centralwidget = QtWidgets.QWidget(MainWindow)
self.centralwidget.setObjectName("centralwidget")
self.insertWindowbtn = QtWidgets.QPushButton(self.centralwidget)
self.insertWindowbtn.setGeometry(QtCore.QRect(546, 30, 135, 51))
# EDIT
font = QtGui.QFont()
font.setFamily("Calibri")
font.setPointSize(20)
font.setItalic(False)
font.setBold(True)
self.headlabel = QtWidgets.QLabel(self.centralwidget)
self.headlabel.setGeometry(QtCore.QRect(50, 30, 221, 51))
self.headlabel.setFont(font)
#self.headlabel.setText('Expiration Detector')
self.headlabel.setObjectName("headlabel")
# END
font = QtGui.QFont()
font.setFamily("Lucida Sans")
font.setPointSize(12)
font.setItalic(False)
# EDIT 2
self.profilebtn = QtWidgets.QPushButton(self.centralwidget)
self.profilebtn.setGeometry(QtCore.QRect(546, 85, 135, 51))
self.profilebtn.setFont(font)
self.profilebtn.setText('')
self.profilebtn.setIcon(QtGui.QIcon(QtGui.QPixmap(r"user.png")))
self.profilebtn.setIconSize(QtCore.QSize(28, 28))
self.profilebtn.setText(" Profile ")
self.profilebtn.setObjectName("profilebtn")
# END 2
self.insertWindowbtn.setFont(font)
self.insertWindowbtn.setIcon(QtGui.QIcon(QtGui.QPixmap('plus.png')))
self.insertWindowbtn.setIconSize(QtCore.QSize(28, 28))
self.insertWindowbtn.setObjectName("insertWindowbtn")
self.tableWidget = QtWidgets.QTableWidget(self.centralwidget)
self.tableWidget.setGeometry(QtCore.QRect(20, 150, 660, 321))
self.tableWidget.setObjectName("tableWidget")
MainWindow.setCentralWidget(self.centralwidget)
self.statusbar = QtWidgets.QStatusBar(MainWindow)
self.statusbar.setObjectName("statusbar")
MainWindow.setStatusBar(self.statusbar)
self.retranslateUi(MainWindow)
QtCore.QMetaObject.connectSlotsByName(MainWindow)
def retranslateUi(self, MainWindow):
_translate = QtCore.QCoreApplication.translate
MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
self.insertWindowbtn.setText(_translate("MainWindow", " Add Data"))
#-> REMOVE THIS METHOD
# def show_new_window(self, checked):
# self.w = second()
# self.w.show()
class Ui_MainWindow2(object):
def setupUi(self, MainWindow):
MainWindow.setObjectName("MainWindow")
MainWindow.resize(484,338)
MainWindow.setMinimumSize(QtCore.QSize(264, 248))
self.centralwidget = QtWidgets.QWidget(MainWindow)
self.centralwidget.setObjectName("centralwidget")
layout = QtWidgets.QVBoxLayout()
#MainWindow.setLayout(layout)
MainWindow.setWindowTitle("Insert Data Window")
self.insertDatabtn = QtWidgets.QPushButton(self)
self.insertDatabtn.setGeometry(QtCore.QRect(330, 250, 121, 41))
font = QtGui.QFont()
font.setFamily("Lucida Sans")
font.setPointSize(12)
self.insertDatabtn.setFont(font)
self.insertDatabtn.setObjectName("insertDatabtn")
self.label = QtWidgets.QLabel(self)
self.label.setGeometry(QtCore.QRect(20, 20, 101, 31))
font = QtGui.QFont()
font.setFamily("Lucida Sans")
font.setPointSize(12)
self.label.setFont(font)
self.label.setObjectName("label")
self.label_2 = QtWidgets.QLabel(self)
self.label_2.setGeometry(QtCore.QRect(20, 80, 101, 31))
font = QtGui.QFont()
font.setFamily("Lucida Sans")
font.setPointSize(12)
self.label_2.setFont(font)
self.label_2.setObjectName("label_2")
self.label_3 = QtWidgets.QLabel(self)
self.label_3.setGeometry(QtCore.QRect(20, 140, 101, 31))
font = QtGui.QFont()
font.setFamily("Lucida Sans")
font.setPointSize(12)
self.label_3.setFont(font)
self.label_3.setObjectName("label_3")
self.label_4 = QtWidgets.QLabel(self)
self.label_4.setGeometry(QtCore.QRect(20, 200, 101, 31))
font = QtGui.QFont()
font.setFamily("Lucida Sans")
font.setPointSize(12)
self.label_4.setFont(font)
self.label_4.setObjectName("label_4")
self.txtDescription = QtWidgets.QLineEdit(self)
self.txtDescription.setGeometry(QtCore.QRect(150, 22, 271, 31))
font = QtGui.QFont()
font.setFamily("Lucida Sans")
font.setPointSize(12)
self.txtDescription.setFont(font)
self.txtDescription.setObjectName("txtDescription")
self.txtSerialno = QtWidgets.QLineEdit(self)
self.txtSerialno.setGeometry(QtCore.QRect(150, 82, 271, 31))
font = QtGui.QFont()
font.setFamily("Lucida Sans")
font.setPointSize(12)
self.txtSerialno.setFont(font)
self.txtSerialno.setObjectName("txtSerialno")
self.dateExpire = QtWidgets.QDateEdit(self)
self.dateExpire.setGeometry(QtCore.QRect(150, 200, 131, 31))
font = QtGui.QFont()
font.setFamily("Lucida Console")
font.setPointSize(12)
self.dateExpire.setFont(font)
self.dateExpire.setDateTime(QtCore.QDateTime(QtCore.QDate(2020, 12, 30), QtCore.QTime(0, 0, 0)))
self.dateExpire.setCurrentSection(QtWidgets.QDateTimeEdit.DaySection)
self.dateExpire.setCalendarPopup(True)
self.dateExpire.setObjectName("dateExpire")
self.dateIssue = QtWidgets.QDateEdit(self)
self.dateIssue.setGeometry(QtCore.QRect(150, 140, 131, 31))
font = QtGui.QFont()
font.setFamily("Lucida Console")
font.setPointSize(12)
self.dateIssue.setFont(font)
self.dateIssue.setDateTime(QtCore.QDateTime(QtCore.QDate(2020, 12, 30), QtCore.QTime(0, 0, 0)))
self.dateIssue.setCurrentSection(QtWidgets.QDateTimeEdit.DaySection)
self.dateIssue.setCalendarPopup(True)
self.dateIssue.setObjectName("dateIssue")
# Set
self.insertDatabtn.setText("Insert Data")
self.label.setText("Description")
self.label_2.setText("Serial No")
self.label_3.setText("Issue Date")
self.label_4.setText("Expire Date")
self.dateExpire.setDisplayFormat("dd/MM/yyyy")
self.dateIssue.setDisplayFormat("dd/MM/yyyy")
class first(QtWidgets.QMainWindow, Ui_MainWindow1):
def __init__(self):
super().__init__()
self.setupUi(self)
self.setWindowTitle("Expiration Monitor")
# Load data from excel file
df = pd.read_excel("CadetData.xlsx", "Sheet1")
if df.size == 0:
return
dfc = str(df.columns) #Extracting list of header labels
HeaderLabels = ast.literal_eval(dfc[dfc.find('['):dfc.find(']')+1])
for i in HeaderLabels :
for i in HeaderLabels :
if 'Unnamed' in i :
HeaderLabels.remove(str(i))
HeaderLabels.append('Remaining Time')
HeaderLabels.append('Remaining %')
df.fillna('', inplace=True)
self.tableWidget.setRowCount(df.shape[0])
self.tableWidget.setColumnCount(df.shape[1]+2)
self.tableWidget.setHorizontalHeaderLabels(HeaderLabels)
ExpireDates = []
IssueDates = []
# returns pandas array object
for row in df.iterrows():
values = row[1]
ExpireDates.append(values['Expire Date'])
IssueDates.append(values['Issue Date'])
for col_index, value in enumerate(values):
#if isinstance(value, (float, int)):
# value = '{0:0,.0f}'.format(value)
tableItem = QtWidgets.QTableWidgetItem(str(value))
self.tableWidget.setItem(row[0], col_index, tableItem)
for i in range(len(ExpireDates)) :
end_date = datetime.strptime(ExpireDates[i], "%d/%m/%Y")
start_date = datetime.strptime(datetime.today().strftime('%d/%m/%Y'),"%d/%m/%Y")
# Get the relativedelta between two dates
delta = relativedelta.relativedelta(end_date, start_date)
# Time to expire
if delta.years < 0 or delta.months < 0 or delta.days <0 :
remtime = '0 Years 0 Months 0 Days'
else :
remtime = str(delta.years)+' Years,'+str(delta.months)+' Months,'+str(delta.days)+' Days'
end_date2 = datetime.strptime(ExpireDates[i], "%d/%m/%Y")
start_date2 = datetime.strptime(IssueDates[i], "%d/%m/%Y")
# Get the relativedelta between two dates
delta2 = relativedelta.relativedelta(end_date2, start_date2)
#total days from issue date to expire date
totday = (delta2.years*365)+(delta2.months*30)+delta2.days
tabItem = QtWidgets.QTableWidgetItem(remtime)
self.tableWidget.setItem(i, 4, tabItem)
# Remaining days to expire
remday = (delta.years*365)+(delta.months*30)+delta.days
# Remaining months to expire
#remmonth = (delta.years*12)+delta.months
if remday >= (12*30) :
Pcolor = "#1ac73a" # Green color
elif remday > (6*30) :
Pcolor = "#db8514" # Orange color
elif remday >= (3*30) or remday < (3*30) :
Pcolor = "#d61515" # Red color
Pbar = QtWidgets.QProgressBar(self)
Pstyle = """
QProgressBar {
border: 2px solid #5c5c5c;
border-radius: 5px;
text-align: center;
}
QProgressBar::chunk {
background-color: """+Pcolor+""";
width: 20px;
}
"""
Pbar.setStyleSheet(Pstyle)
Pbar.setFormat("%p%")
self.tableWidget.setCellWidget(i,5,Pbar)
prec = int((remday/totday)*100)
if prec<0:
prec=0
Pbar.setValue(prec)
self.tableWidget.setColumnWidth(0, 110)
self.tableWidget.setColumnWidth(1, 100)
self.tableWidget.setColumnWidth(2, 70)
self.tableWidget.setColumnWidth(3, 70)
self.tableWidget.setColumnWidth(4, 150)
self.tableWidget.setColumnWidth(5, 140)
for x in range(df.shape[0]):
self.tableWidget.setRowHeight(x,28)
self.tableWidget.setEditTriggers(QtWidgets.QTableWidget.NoEditTriggers)
# Button Connects
self.insertWindowbtn.clicked.connect(self.OpenInsertWindow)
def OpenInsertWindow(self):
#-> PASS REFERENCE TO FIRST WINDOW
self.second_window = second(self)
self.second_window.show()
"""
def InsertData(self):
from openpyxl import load_workbook
myFileName=r'CadetData.xls'
#load the workbook, and put the sheet into a variable
wb = load_workbook(filename=myFileName)
ws = wb['Sheet1']
#max_row is a sheet function that gets the last row in a sheet.
newRowLocation = ws.max_row +1
#write data
ws.cell(column=1,row=newRowLocation, value="aha! a new entry at the end")
wb.save(filename=myFileName)
wb.close()"""
def loadExcelData(self):
print('Fn Call 200')
# Load data from excel file
df = pd.read_excel("CadetData.xlsx", "Sheet1")
if df.size == 0:
return
dfc = str(df.columns) #Extracting list of header labels
HeaderLabels = ast.literal_eval(dfc[dfc.find('['):dfc.find(']')+1])
for i in HeaderLabels :
for i in HeaderLabels :
if 'Unnamed' in i :
HeaderLabels.remove(str(i))
HeaderLabels.append('Remaining Time')
HeaderLabels.append('Remaining %')
df.fillna('', inplace=True)
self.tableWidget.setRowCount(df.shape[0])
self.tableWidget.setColumnCount(df.shape[1]+2)
self.tableWidget.setHorizontalHeaderLabels(HeaderLabels)
ExpireDates = []
IssueDates = []
# returns pandas array object
for row in df.iterrows():
values = row[1]
ExpireDates.append(values['Expire Date'])
IssueDates.append(values['Issue Date'])
for col_index, value in enumerate(values):
#if isinstance(value, (float, int)):
# value = '{0:0,.0f}'.format(value)
tableItem = QtWidgets.QTableWidgetItem(str(value))
self.tableWidget.setItem(row[0], col_index, tableItem)
for i in range(len(ExpireDates)) :
end_date = datetime.strptime(ExpireDates[i], "%d/%m/%Y")
start_date = datetime.strptime(datetime.today().strftime('%d/%m/%Y'),"%d/%m/%Y")
# Get the relativedelta between two dates
delta = relativedelta.relativedelta(end_date, start_date)
# Time to expire
if delta.years < 0 or delta.months < 0 or delta.days <0 :
remtime = '0 Years 0 Months 0 Days'
else :
remtime = str(delta.years)+' Years,'+str(delta.months)+' Months,'+str(delta.days)+' Days'
end_date2 = datetime.strptime(ExpireDates[i], "%d/%m/%Y")
start_date2 = datetime.strptime(IssueDates[i], "%d/%m/%Y")
# Get the relativedelta between two dates
delta2 = relativedelta.relativedelta(end_date2, start_date2)
#total days from issue date to expire date
totday = (delta2.years*365)+(delta2.months*30)+delta2.days
tabItem = QtWidgets.QTableWidgetItem(remtime)
self.tableWidget.setItem(i, 4, tabItem)
# Remaining days to expire
remday = (delta.years*365)+(delta.months*30)+delta.days
# Remaining months to expire
#remmonth = (delta.years*12)+delta.months
if remday >= (12*30) :
Pcolor = "#1ac73a" # Green color
elif remday > (6*30) :
Pcolor = "#db8514" # Orange color
elif remday >= (3*30) or remday < (3*30) :
Pcolor = "#d61515" # Red color
Pbar = QtWidgets.QProgressBar(self)
Pstyle = """
QProgressBar {
border: 2px solid #5c5c5c;
border-radius: 5px;
text-align: center;
}
QProgressBar::chunk {
background-color: """+Pcolor+""";
width: 20px;
}
"""
Pbar.setStyleSheet(Pstyle)
Pbar.setFormat("%p%")
self.tableWidget.setCellWidget(i,5,Pbar)
prec = int((remday/totday)*100)
if prec<0:
prec=0
Pbar.setValue(prec)
self.tableWidget.setColumnWidth(0, 110)
self.tableWidget.setColumnWidth(1, 100)
self.tableWidget.setColumnWidth(2, 70)
self.tableWidget.setColumnWidth(3, 70)
self.tableWidget.setColumnWidth(4, 150)
self.tableWidget.setColumnWidth(5, 140)
for x in range(df.shape[0]):
self.tableWidget.setRowHeight(x,28)
self.tableWidget.setEditTriggers(QtWidgets.QTableWidget.NoEditTriggers)
# Button Connects
self.insertWindowbtn.clicked.connect(self.OpenInsertWindow)
class second(QtWidgets.QMainWindow, Ui_MainWindow2):
#-> CHANGE SIGNATURE
def __init__(self, first):
super().__init__()
#-> ADD REFERENCE TO FIRST WINOW
self.first_window = first
self.setupUi(self)
self.insertDatabtn.clicked.connect(self.getAndInsert)
def getAndInsert(self):
dataCol1 = self.txtDescription.text()
dataCol2 = self.txtSerialno.text()
dataCol3 = self.dateIssue.text()
dataCol4 = self.dateExpire.text()
from openpyxl import load_workbook
myFileName=r'CadetData.xlsx'
#load the workbook, and put the sheet into a variable
wb = load_workbook(filename=myFileName)
ws = wb['Sheet1']
#max_row is a sheet function that gets the last row in a sheet.
#newRowLocation = ws.max_row +1
for max_row, row in enumerate(ws, 1):
if all(c.value is None for c in row):
newRowLocation = max_row
break
#write data
try:
ws.cell(column=1,row=newRowLocation, value=dataCol1)
ws.cell(column=2,row=newRowLocation, value=dataCol2)
ws.cell(column=3,row=newRowLocation, value=dataCol3)
ws.cell(column=4,row=newRowLocation, value=dataCol4)
wb.save(filename=myFileName)
wb.close()
except Exception as e:
msg1 = QtWidgets.QMessageBox()
#msg1.setIcon(QtWidgets.QMessageBox.warning)
msg1.setText(str(e))
msg1.setWindowTitle("Error")
msg1.setStandardButtons(QtWidgets.QMessageBox.Ok)
msg1.exec_()
else:
#-> ACCESS FIRST WINOW
self.first_window.loadExcelData()
msg2 = QtWidgets.QMessageBox()
#msg2.setIcon(QtWidgets.QMessageBox.information)
msg2.setText("Successfully Inserted Data.")
msg2.setWindowTitle("Success")
msg2.setStandardButtons(QtWidgets.QMessageBox.Ok)
msg2.exec_()
if __name__ == "__main__":
import sys
app = QtWidgets.QApplication(sys.argv)
MainWindow = first()
MainWindow.show()
sys.exit(app.exec_())