PyQT5- Storing users' inputs from an opening window prompt into excel file

Question

I am trying to save users' inputs from an opening window prompt into an excel file. I tried something in the lines of code between 53-59, but it does not work. Can you please help how can I solve this issue?

Also, in every new user entry, new information should be placed on the bottom lines of excel table. It should not overwrite the previous inputs.

Many thanks

import sys
from PyQt5.QtWidgets import QDialogButtonBox
from PyQt5.QtWidgets import QFormLayout
from PyQt5.QtWidgets import QLineEdit
from PyQt5.QtWidgets import QVBoxLayout
from PyQt5.QtWidgets import *
from xlwt import Workbook

class InputDialog(QDialog):

    def __init__(self, parent=None):
        super().__init__(parent)
        self.setWindowTitle("Information Window")
        self.first = QLineEdit()
        self.second = QLineEdit()
        self.third = QLineEdit()
        self.fourth = QLineEdit()
        self.fifth = QLineEdit()
        self.sixth = QLineEdit()
        self.seventh = QLineEdit()

        dlglayout = QVBoxLayout(self)
        formlayout = QFormLayout()
        formlayout.addRow("Fırst Name:", self.first)
        formlayout.addRow("Second Name:", self.second)
        formlayout.addRow("Age:", self.third )
        formlayout.addRow("Sex:", self.fourth)
        formlayout.addRow("Marital Status:", self.fifth)
        formlayout.addRow("Education:", self.sixth)
        formlayout.addRow("Job:", self.seventh)
        dlglayout.addLayout(formlayout)
        btns = QDialogButtonBox()
        btns.setStandardButtons(QDialogButtonBox.Cancel | QDialogButtonBox.Save)
        dlglayout.addWidget(btns)

        btns.accepted.connect(self.accept)
        btns.rejected.connect(self.reject)

    def getInputs(self):
        return self.first.text(), self.second.text(), self.third.text(), 
        self.fourth.text(), self.fifth.text(), self.sixth.text(), self.seventh.text()

wb = Workbook()

sheet1 = wb.add_sheet('Sheet 1')

sheet1.write(1, 0, 'First Name')
sheet1.write(2, 0, 'Second Name')
sheet1.write(3, 0, 'Age')
sheet1.write(4, 0, 'Sex')
sheet1.write(5, 0, 'Marital Status')
sheet1.write(6, 0, 'Education')
sheet1.write(7, 0, 'Job:')
sheet1.write(0, 1, 'self.first')
sheet1.write(0, 2, 'self.second')
sheet1.write(0, 3, 'self.third')
sheet1.write(0, 4, 'self.fourth')
sheet1.write(0, 5, 'self.fifth')
sheet1.write(0, 6, 'self.sixth')
sheet1.write(0, 7, 'self.seventh')

wb.save('output example.xls')

if __name__ == '__main__':


    app = QApplication(sys.argv)
    dialog = InputDialog()
    if dialog.exec():
        print(dialog.getInputs())
    exit(0)

Answer 1

For newer excel files (xlsx), use the openpyxl module. It allows reading and writing.

Here is the updated code:

import sys
from PyQt5.QtWidgets import QDialogButtonBox
from PyQt5.QtWidgets import QFormLayout
from PyQt5.QtWidgets import QLineEdit
from PyQt5.QtWidgets import QVBoxLayout
from PyQt5.QtWidgets import *
import openpyxl

filename = "data.xlsx"

def checkfile():  # create file if needed
    # check if excel file exists
    import os.path
    from os import path
    if not path.exists(filename):
        # create workbook
        wb = openpyxl.Workbook()

        #sheet1 = wb.create_sheet('Sheet1')
        ws = wb.worksheets[0]

        ws.cell(2, 1).value = 'First Name'
        ws.cell(3, 1).value = 'Second Name'
        ws.cell(4, 1).value = 'Age'
        ws.cell(5, 1).value = 'Sex'
        ws.cell(6, 1).value = 'Marital Status'
        ws.cell(7, 1).value = 'Education'
        ws.cell(8, 1).value = 'Job'
        ws.cell(1, 2).value = 'self.first'
        ws.cell(1, 3).value = 'self.second'
        ws.cell(1, 4).value = 'self.third'
        ws.cell(1, 5).value = 'self.fourth'
        ws.cell(1, 6).value = 'self.fifth'
        ws.cell(1, 7).value = 'self.sixth'
        ws.cell(1, 8).value = 'self.seventh'

        wb.save(filename)

class InputDialog(QDialog):

    def __init__(self, parent=None):
        super().__init__(parent)
        self.setWindowTitle("Information Window")
        self.first = QLineEdit()
        self.second = QLineEdit()
        self.third = QLineEdit()
        self.fourth = QLineEdit()
        self.fifth = QLineEdit()
        self.sixth = QLineEdit()
        self.seventh = QLineEdit()

        dlglayout = QVBoxLayout(self)
        formlayout = QFormLayout()
        formlayout.addRow("First Name:", self.first)
        formlayout.addRow("Second Name:", self.second)
        formlayout.addRow("Age:", self.third )
        formlayout.addRow("Sex:", self.fourth)
        formlayout.addRow("Marital Status:", self.fifth)
        formlayout.addRow("Education:", self.sixth)
        formlayout.addRow("Job:", self.seventh)
        dlglayout.addLayout(formlayout)
        btns = QDialogButtonBox()
        btns.setStandardButtons(QDialogButtonBox.Cancel | QDialogButtonBox.Save)
        dlglayout.addWidget(btns)

        btns.accepted.connect(self.accept)
        btns.rejected.connect(self.reject)

    def getInputs(self):
        return self.first.text(), self.second.text(), self.third.text(), \
        self.fourth.text(), self.fifth.text(), self.sixth.text(), self.seventh.text()

def writefile(data):
    wb = openpyxl.load_workbook(filename)
    ws = wb.worksheets[0]
    # find empty column
    for c in range(2,100):
       if not ws.cell(2,c).value: break
    # enter data
    for r in range(len(data)):
       ws.cell(r+2,c).value = data[r]
    wb.save(filename)


if __name__ == '__main__':
    checkfile()  # create if needed
    app = QApplication(sys.argv)
    dialog = InputDialog()
    if dialog.exec():
        writefile(dialog.getInputs())
    exit(0)