一个简单的Excel查询系统
本帖最后由 林浅黑 于 2021-5-6 14:27 编辑天天白嫖编程语言区大佬们的代码，我也发一个我写的Excel简单查询系统，写的不怎么样，大佬别喷{:301_971:}
保存的功能也有就是写的有些繁琐就删了
成品图
import sys
import xlwings as xw
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import *
#from PyQt5.QtWidgets import QMainWindow, QApplication, QDesktopWidget
from PyQt5.QtCore import Qt
from xlrd import *
class Main_Win(QWidget):
def __init__(self):
self.i = 0
super(Main_Win,self).__init__()
self.Main_WinUI()
def Main_WinUI(self):
self.setWindowTitle('简单的Excel查询系统')
self.resize(1700,880)
screen = QDesktopWidget().screenGeometry()
size = self.geometry()
newLeft = (screen.width() - size.width()) // 2
newTop = (screen.height() - size.height()) // 2
self.move(newLeft, newTop)
self.setFixedSize(1800,880)
palette = QPalette()
pix = QPixmap("D:\\BaiduNetdiskDownload\\d\\d\\pythonandpycharm\\QT5_new\\images\\one.jpg")
pix = pix.scaled(1300, 880)
palette.setBrush(QPalette.Background, QBrush(pix))
self.setPalette(palette)
self.setWindowOpacity(0.8)
self.setToolTip('软件仅供参考，有bug欢迎上论坛交流')
self.setWindowIcon(QIcon('D:\\BaiduNetdiskDownload\\d\\d\\pythonandpycharm\\QT5_new\\images\\favicon.ico'))
QToolTip.setFont(QFont('Times',10,QFont.Black))
self.Main_WinLayout()
self.show()
def Main_WinLayout(self):
self.group = QGroupBox(self)
self.group.setTitle('参数设置区')
self.group.setGeometry(10,10,400,300)
self.layout = QGridLayout()
self.label_one = QLabel('<font color=#00FA9A>请输入要搜索的关键词：<\\font>')
self.layout.addWidget(self.label_one,0,0)
self.edit_one = QLineEdit()
self.edit_one.setAlignment(Qt.AlignCenter)
self.edit_one.setPlaceholderText('这里输入要搜索的关键词')
self.layout.addWidget(self.edit_one,0,1)
self.button_one = QPushButton('开始搜索')
self.layout.addWidget(self.button_one,1,0)
self.button_one.clicked.connect(self.dialoginfo)
self.button_two = QPushButton('导入EXCAL文件')
self.layout.addWidget(self.button_two, 1, 1)
self.button_two.clicked.connect(self.groove_five)
self.group_two = QGroupBox(self)
self.group_two.setTitle('输出日志')
self.group_two.setGeometry(10, 320, 400, 550)
self.textedit_one = QTextEdit()
self.textedit_one.setHtml('<font color=red>温馨提示！<\\font>')##00FA9A
self.textedit_one.setFont(QFont("Times",11))
self.textedit_one.moveCursor(QTextCursor.End)
self.textedit_one.insertPlainText("\n目前软件只支持.xlsx后缀的Excel文件\n")
self.textedit_one.moveCursor(QTextCursor.End)
self.textedit_one.insertPlainText("\n程序初始化成功！\n")
self.layout_two = QGridLayout()
self.textedit_one.setReadOnly(True)
self.layout_two.addWidget(self.textedit_one)
self.group_two.setLayout(self.layout_two)
self.group_three = QGroupBox(self)
self.layout_two = QHBoxLayout()
self.group_three.setTitle('订单数据')
self.group_three.setGeometry(420, 10, 1400, 860)
self.table_one = QTableWidget()
self.table_one.setRowCount(0)
self.table_one.setColumnCount(11)
self.table_one.setHorizontalHeaderLabels(['订单编号', '买家昵称', '发货地址','姓名','联系电话','款式','付款时间','付款金额','佣金','空包号','是否评价'])
self.table_one.setEditTriggers(QAbstractItemView.NoEditTriggers)# 设置单元格不可编辑
#self.table_one.setEditTriggers(QAbstractItemView.SelectedClicked)# 选中单元格，在点击一次可编辑
#self.table_one.setEditTriggers(QAbstractItemView.DoubleClicked) # 设置单元格双击可编辑
self.table_one.setColumnWidth(0, 180)#设置单元格的宽度
self.table_one.setColumnWidth(1, 155)
self.table_one.setColumnWidth(2, 400)
self.table_one.setColumnWidth(3, 155)
self.table_one.setColumnWidth(4, 155)
self.table_one.setColumnWidth(5, 245)
self.table_one.setColumnWidth(6, 185)
self.table_one.setColumnWidth(7, 155)
self.table_one.setColumnWidth(8, 155)
self.table_one.setColumnWidth(9, 155)
self.table_one.setColumnWidth(10, 155)
self.table_one.setColumnWidth(11, 155)
self.setContextMenuPolicy(Qt.CustomContextMenu)
self.customContextMenuRequested.connect(self.showMenu)
self.contextMenu = QMenu(self)
self.CP = self.contextMenu.addAction('复制')
self.CP.triggered.connect(lambda:self.selected_tb_text(self.table_one))
self.CP = self.contextMenu.addAction('打开表格可编辑')
self.CP.triggered.connect(lambda: self.compile_True(self.table_one))
self.CP = self.contextMenu.addAction('关闭表格可编辑')
self.CP.triggered.connect(lambda: self.compile_False(self.table_one))
self.CP = self.contextMenu.addAction('添加数据')
self.CP.triggered.connect(lambda: self.appendinfo(self.table_one))
self.layout_two.addWidget(self.table_one)
self.group_three.setLayout(self.layout_two)
QApplication.processEvents()
self.group.setLayout(self.layout)
def dialoginfo(self):
if self.edit_one.text() == "":
reply=QMessageBox.question(win, '温馨提示！', '大哥，你关键字，怎么也输点吧！', QMessageBox.Yes | QMessageBox.No, (QMessageBox.Yes))
if reply == QMessageBox.Yes:
pass
else:
self.dialoginfo()
else:
try:
infoname = self.edit_one.text()
items = self.table_one.findItems(infoname, Qt.MatchExactly)
print(items)
print(items)
if items == "":
self.textedit_one.moveCursor(QTextCursor.End)
self.textedit_one.insertPlainText("查询失败，找不到该数据！\n")
else:
self.textedit_one.moveCursor(QTextCursor.End)
self.textedit_one.insertPlainText("查询成功！\n")
item = items
item.setSelected(False)# 设置指定单元格是否选中
s = item.row()
#self.table_one.item(s,Column).setForeground(QBrush(QColor(255, 0, 0)))
item.setBackground(QColor(250, 0, 0))
except:
self.textedit_one.moveCursor(QTextCursor.End)
self.textedit_one.insertPlainText("查询失败，找不到该数据！\n")
def selected_tb_text(self,table_view):
try:
indexes = table_view.selectedIndexes()# 获取表格对象中被选中的数据索引列表
indexes_dict = {}
for index in indexes:# 遍历每个单元格
row, column = index.row(), index.column()# 获取单元格的行号，列号
if row in indexes_dict.keys():
indexes_dict.append(column)
else:
indexes_dict =
print(indexes_dict)
print(row,column)
# 将数据表数据用制表符(\t)和换行符(\n)连接，使其可以复制到excel文件中
text = []
for row, columns in indexes_dict.items():
row_data = []
for column in columns:
try:
data = table_view.item(row, column).text()
except BaseException as e:
data = ' '
finally:
if row_data:
row_data = ' ' + data
else:
row_data = data
if text:
if len(text) % 4 == 0:
text.append('\n')
text.append(row_data)
else:
text.append(row_data)
print(text)
text_two = ''
for item in text:
text_two += item
try:
clipboard = QApplication.clipboard()
clipboard.setText(text_two)# 复制到粘贴板
except BaseException as e:
print(e)
except BaseException as e:
print(e)
clipboard = QApplication.clipboard()
clipboard.setText(text_two)
return ''
def keyPressEvent(self, event): # 重写键盘监听事件
# 监听 CTRL+C 组合键，实现复制数据到粘贴板
if (event.key() == Qt.Key_C) and QApplication.keyboardModifiers() == Qt.ControlModifier:
text = self.selected_tb_text(self.table_one)# 获取当前表格选中的数据
def showMenu(self, pos):
print(pos)
self.contextMenu.exec_(QCursor.pos())# 在鼠标位置显示
def groove_five(self):
self.textedit_one.moveCursor(QTextCursor.End)
self.textedit_one.insertPlainText("\n正在导入Excel文件..............\n")
self.filename,ok = QFileDialog.getOpenFileName(self,'想好了在选，记得选EXCAL文件！！！！','C:\\','文件类型默认所有，这个你不用担心：(*.*)')
self.excal()
def excal(self):
print(self.filename)
if self.filename[-4:] == "xlsx":
self.textedit_one.moveCursor(QTextCursor.End)
self.textedit_one.insertPlainText("导入成功！\n")
self.textedit_one.moveCursor(QTextCursor.End)
self.textedit_one.insertPlainText(f'成功导入Excel文件!,路径为：{self.filename}\n')
app = xw.App(visible=False, add_book=False)# 程序可见，只打开不新建工作薄
app.display_alerts = False# 警告关闭
app.screen_updating = False# 屏幕更新关闭
wb = app.books.open(self.filename)
# 类似 openpyxl 中的 sheet = workbook.active
sheet = wb.sheets.active
# 获取横向或纵向多个单元格的值，返回列表
self.shape = sheet.used_range.shape
print(self.shape)
self.i = 1
while self.i < self.shape:
A1_AN = sheet.range('A' + str(self.i + 1) + ':' + 'K' + str(self.i + 1)).value
self.table_one.setRowCount(self.i)
self.table_one.setItem(self.i-1, 0, QTableWidgetItem(str(A1_AN)))
self.table_one.setItem(self.i-1, 1, QTableWidgetItem(str(A1_AN)))
self.table_one.setItem(self.i-1, 2, QTableWidgetItem(str(A1_AN)))
self.table_one.setItem(self.i-1, 3, QTableWidgetItem(str(A1_AN)))
self.table_one.setItem(self.i-1, 4, QTableWidgetItem(str(A1_AN)))
self.table_one.setItem(self.i-1, 5, QTableWidgetItem(str(A1_AN)))
self.table_one.setItem(self.i-1, 6, QTableWidgetItem(str(A1_AN)))
self.table_one.setItem(self.i-1, 7, QTableWidgetItem(str(A1_AN)))
self.table_one.setItem(self.i-1, 8, QTableWidgetItem(str(A1_AN)))
self.table_one.setItem(self.i-1, 9, QTableWidgetItem(str(A1_AN)))
self.table_one.setItem(self.i-1, 10, QTableWidgetItem(str(A1_AN)))
self.table_one.setEditTriggers(QAbstractItemView.NoEditTriggers)# 设置单元格不可编辑
#self.table_one.setEditTriggers(QAbstractItemView.DoubleClicked)
# self.table_one.resizeRowsToContents()#设置单元格行自动补齐
for i2 in range(len(A1_AN)):
s = QTableWidgetItem(str(A1_AN))
# s.setTextAlignment(Qt.AlignCenter)#设置单元格对齐方式
s.setTextAlignment(Qt.AlignCenter)
self.table_one.setItem(self.i-1,i2, s)
#self.table_one.resizeColumnsToContents()# 设置单元格列自动补齐
self.i += 1
self.i -= 1
self.textedit_one.moveCursor(QTextCursor.End)
self.textedit_one.insertPlainText(f'目前有{self.shape-1}条数据！\n')
wb.save()# 保存文件
wb.close()# 关闭文件
app.quit()# 关闭程序
else:
self.textedit_one.moveCursor(QTextCursor.End)
self.textedit_one.insertPlainText("导入失败，请检查是否导入的是后缀为：xlsx的文件！\n")
def Queryinfomation(self):
print(self.info)
for i in range(self.shape):
for i2 in range(self.shape):
Query = self.table_one.item(i,i2).text()
print(Query)
def compile_True(self,table_view):
self.table_one.setEditTriggers(QAbstractItemView.DoubleClicked)
def compile_False(self,table_view):
self.table_one.setEditTriggers(QAbstractItemView.NoEditTriggers)
def appendinfo(self,table_view):
self.i +=1
self.table_one.setRowCount(self.i)
class Crawler(object):
pass
if __name__ == '__main__':
app = QApplication(sys.argv)
win = Main_Win()
sys.exit(app.exec_())
背景图
https://attach.52pojie.cn//forum/202105/06/132931z4evdlbbnkxnva1n.jpg?l
测试用的Excel文件
导入 EXCAL 是什么鬼{:1_925:}
绅士：背景图不怎么样 虽然看不懂，还是顶一个 代码可以运行， 不过一脸懵逼。 顶一个，感谢分享，这应该是特定的功能才需要吧 这个不错 感谢楼主 看不懂，反正我是来学习的 coolcalf 发表于 2021-5-6 13:52
导入 EXCAL 是什么鬼
绅士：背景图不怎么样
这是一个对Excel文件进行查询的软件，点击导入Excel按钮导入Excel文件就可以使用查询功能了，背景图的话还好吧，哈哈，不喜欢的话可以在29行的地方修改背景 阿傑 发表于 2021-5-6 13:40
顶一个，感谢分享，这应该是特定的功能才需要吧
只是一个查询系统，要查询其他格式的Excel文件修改88行的标题和234-245的代码就可以了，不一定说一定要查询Excel文件，代码修修改改也可以做其他功能也可以当做模板 牛逼 又是py 羡慕你们会py的大佬们