Edited
I have a similar previous post, but this one is different.
I made an interface, but it doesn't work the way I want.
If you click the Create empty Excel button, an excel is created with the headers in the columns. Until this step everything works fine.
What I tried to do: By pressing the ok button, the information written in the form is exported as a row in Excel. But I have a problem: The information is added as a column and the previous information is deleted. The aforementioned corresponds to the function def Excel_information (self): .This function is the one that I must write well, but I don't know how
import sys, re
from PyQt5.QtWidgets import QApplication, QWidget, QDialog, QFormLayout, QCheckBox, QComboBox, QSpinBox, QDialogButtonBox, QMessageBox, QErrorMessage, QToolTip, QPushButton, QLineEdit, QLabel, QTextEdit, QMainWindow, QGroupBox, QHBoxLayout, QVBoxLayout
from PyQt5 import uic
from PyQt5.QtGui import QFont
from PyQt5.QtCore import Qt
import ctypes
import pandas as pd
import os.path
import os
from pandas import ExcelWriter
from openpyxl import load_workbook
from openpyxl import workbook
import openpyxl
headers = ['Fecha', 'Dias abiertos', 'Nombre del caso', 'Responsable', 'Ok Mkt' , 'Solicitud Lotus' ,'OS' , 'Monto' , 'Fecha de cierre' , 'Comentario']
class Dialog(QDialog):
NumGridRows = 10
NumButtons = 4
def __init__(self):
super(Dialog, self).__init__()
self.createFormGroupBox()
self.setStyleSheet("QDialog {background:rgb(66,85,99);}")
buttonBox = QDialogButtonBox(QDialogButtonBox.Ok | QDialogButtonBox.Cancel)
buttonBox1 = QPushButton('Create empty Excel',self)
buttonBox1.setStyleSheet("background:rgb(255,199,44);")
buttonBox1.clicked.connect(self.export_excel)
buttonBox.accepted.connect(self.Excel_information)
buttonBox.setStyleSheet("background:rgb(255,199,44);")
mainLayout = QVBoxLayout()
mainLayout.addWidget(self.formGroupBox)
mainLayout.addWidget(buttonBox)
mainLayout.addWidget(buttonBox1)
self.setLayout(mainLayout)
self.setWindowTitle("Cuadro seguimiento")
def createFormGroupBox(self):
self.Fecha= QLineEdit(self)
self.Diasabiertos =QLineEdit(self)
self.Nombredelcaso=QLineEdit(self)
self.Responsable=QLineEdit(self)
self.OkMkt=QLineEdit(self)
self.Solicitudlotus= QLineEdit(self)
self.Os=QLineEdit(self)
self.Monto=QLineEdit(self)
self.Fechacierre=QLineEdit(self)
self.Comentario=QLineEdit(self)
self.formGroupBox = QGroupBox("Python")
self.formGroupBox.setStyleSheet("QGroupBox, QLabel {color: rgb(255,199,44);}")
layout = QFormLayout()
layout.addRow(QLabel("Fecha"), self.Fecha)
layout.addRow(QLabel("Dias abiertos"), self.Diasabiertos)
layout.addRow(QLabel("Nombre del caso"), self.Nombredelcaso)
layout.addRow(QLabel("Responsable"), self.Responsable)
layout.addRow(QLabel("Ok MKT"), self.OkMkt)
layout.addRow(QLabel("Solicitud Lotus"), self.Solicitudlotus)
layout.addRow(QLabel("OS"), self.Os)
layout.addRow(QLabel("Monto"), self.Monto)
layout.addRow(QLabel("Fecha cierre"), self.Fechacierre)
layout.addRow(QLabel("Comentario"), self.Comentario)
self.formGroupBox.setLayout(layout)
def export_excel (self):
df = pd.DataFrame(columns = headers)
writer= ExcelWriter(os.path.expanduser('~/Desktop\\') + r'New.xlsx')
df.to_excel(writer, 'Sheet 1' , index=0)
writer.save()
def Excel_information(self):
Fecha1= str(self.Fecha.text())
Diasabiertos1= str(self.Diasabiertos.text())
Nombredelcaso1= str(self.Nombredelcaso.text())
Responsable1= str(self.Responsable.text())
OkMkt1= str(self.Fecha.text())
Solicitudlotus1= str(self.Solicitudlotus.text())
Os1= str(self.Os.text())
Monto1= str(self.Monto.text())
Fechacierre1= str(self.Fechacierre.text())
Comentario1= str(self.Comentario.text())
fill_form= [Fecha1, Diasabiertos1, Nombredelcaso1, Responsable1, OkMkt1, Solicitudlotus1, Os1, Monto1, Fechacierre1, Comentario1 ]
df = pd.DataFrame(fill_form)
path = (os.path.expanduser('~/Desktop\\') + r'New.xlsx')
wb=openpyxl.load_workbook(path)
sheet = wb['Sheet 1']
try:
writer.sheets = dict((ws.title, ws) for ws in wb.worksheets)
reader = pd.read_excel(path)
df.to_excel(path,index=False,header=False,startrow=len(reader)+1)
writer.close()
except:
with pd.ExcelWriter(path, engine='openpyxl') as writer:
df.to_excel(writer, sheet_name='Sheet1', index=False)
if __name__ == '__main__':
app = QApplication(sys.argv)
dialog = Dialog()
sys.exit(dialog.exec_())
