将列标题添加到表中并为单元格添加边框

Question

我想在下面的代码中做两件事

1. 向表中添加列标题

2. 包围牢房

我想，列标题的解决方案可以通过“使用for循环或范围”来解决，但我无法管理如何做到这一点

至于边境线，我对如何做到这一点没有任何意见

多谢各位

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():
    from os import path
    if not path.exists(filename):
        wb = openpyxl.Workbook()

        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'
        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]
    for c in range(2,100):
       if not ws.cell(2,c).value: break
    for r in range(len(data)):
       ws.cell(r+2,c).value = data[r]
    wb.save(filename)

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

Answer 1

在openpyxml中，可以使用styles对象设置单元格边框。对于标题，只需创建一个字符串列表并枚举值来设置单元格值

将此代码添加到checkfile：

def checkfile():
    ..........
        
    # write headers
    for c,v in enumerate(['Entry 1','Entry 2','Entry 3','Entry 4','Entry 5']): 
       ws.cell(1,c+2).value = v
       
    # define border 
    bdr = openpyxl.styles.borders.Side(style='thin')          
    thin_border = openpyxl.styles.borders.Border(left=bdr, right=bdr, top=bdr, bottom=bdr)
    # set cell borders 
    for c in  range(1, 7):
       for r in  range(1, 9):
          ws.cell(r,c).border = thin_border # set border

    wb.save(filename)