grid控件实现显示表单数据功能,同时实现界面的上下翻页以及跳转功能。
1. 效果展示
2. 代码实现
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Author: Logintern09
###########################################################################
## Python code generated with wxFormBuilder (version Jun 17 2015)
## http://www.wxformbuilder.org/
##
## PLEASE DO "NOT" EDIT THIS FILE!
###########################################################################
import wx
import wx.xrc
import wx.grid
import wx.adv
import os
import sys
from openpyxl import load_workbook
# 由于字典的keys是无序排列的,需要增加bill_view_list列表,帮助确认key在keys中的索引
bill_view_list = ["单号", "需求描述", "需求来源", "创建人", "流程状态",
"问题来源", "项目状态", "严重等级", "创建日期"]
# 此列表应用于单号总表.xlsx的列标题排序
excel_bill_list = ["单号", "需求描述", "需求来源", "创建人", "问题来源",
"项目状态", "严重等级", "流程状态", "创建日期"]
view_bill_nums = 10
class excel():
def __init__(self, file, sheet_idx):
self.file = file
self.wb = load_workbook(self.file)
sheets = self.wb.get_sheet_names()
self.sheet = sheets[sheet_idx]
self.ws = self.wb[self.sheet]
def getSheetNames(self):
sheets = self.wb.get_sheet_names()
return sheets
# 删除sheet
def del_sheet(self, sheet_name):
sheets = self.wb.get_sheet_names()
if sheet_name in sheets:
sheet = self.wb[sheet_name]
self.wb.remove(sheet)
self.wb.save(self.file)
else:
raise NameError("%s不存在该sheet页签:%s" % (self.file, sheet_name))
# 删除指定的行
def delete_row(self, row_idx):
# 删除行(列)后,下(后)面的表格将自动上(前)移。
self.ws.delete_rows(row_idx)
self.wb.save(self.file)
# 获取表格的总行数和总列数
def getRowsClosNum(self):
rows = self.ws.max_row
columns = self.ws.max_column
return rows, columns
# 获取某个单元格的值
def getCellValue(self, row, column):
cellvalue = self.ws.cell(row=row, column=column).value
return cellvalue
# 获取某列的所有值
def getColValues(self, column):
rows = self.ws.max_row
columndata = []
for i in range(1, rows + 1):
cellvalue = self.ws.cell(row=i, column=column).value
columndata.append(cellvalue)
return columndata
# 获取某行所有值
def getRowValues(self, row):
columns = self.ws.max_column
rowdata = []
for i in range(1, columns + 1):
cellvalue = self.ws.cell(row=row, column=i).value
rowdata.append(cellvalue)
return rowdata
# 设置某个单元格的值
def setCellValue(self, row, colunm, cellvalue):
try:
self.ws.cell(row=row, column=colunm).value = cellvalue
self.wb.save(self.file)
except:
self.ws.cell(row=row, column=colunm).value = "writefail"
self.wb.save(self.file)
###########################################################################
## Class Menu_Frame
###########################################################################
class Menu_Frame(wx.Frame):
def __init__(self, parent, id=-1, UpdateUI=None):
wx.Frame.__init__(self, parent, id=wx.ID_ANY, title=u"", pos=wx.DefaultPosition,
size=wx.Size(1300, 700), style=wx.DEFAULT_FRAME_STYLE)
self.UpdateUI = UpdateUI
# self.ShowFullScreen(True, wx.DEFAULT_FRAME_STYLE^(wx.CLOSE_BOX)) # 初始化窗口就默认全屏显示
self.SetSizeHints(wx.DefaultSize, wx.DefaultSize)
self.SetBackgroundColour(wx.Colour("TURQUOISE"))
bSizer20 = wx.BoxSizer(wx.VERTICAL)
bSizer22 = wx.BoxSizer(wx.VERTICAL)
self.m_staticText5 = wx.StaticText(self, wx.ID_ANY, u"", wx.DefaultPosition, wx.DefaultSize, 0)
self.m_staticText5.Wrap(-1)
self.m_staticText5.SetFont(wx.Font(24, 75, 90, 90, False, "黑体"))
bSizer22.Add(self.m_staticText5, 0, wx.ALIGN_CENTER | wx.ALL, 20)
bSizer20.Add(bSizer22, 0, wx.EXPAND, 5)
self.m_notebook6 = wx.Notebook(self, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize, 0)
self.m_panel23 = wx.Panel(self.m_notebook6, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize, wx.TAB_TRAVERSAL)
sbSizer1 = wx.StaticBoxSizer(wx.StaticBox(self.m_panel23, wx.ID_ANY, u""), wx.VERTICAL)
bSizer51 = wx.BoxSizer(wx.HORIZONTAL)
self.m_button25 = wx.Button(sbSizer1.GetStaticBox(), wx.ID_ANY, u"刷新", wx.DefaultPosition, wx.DefaultSize, 0)
bSizer51.Add(self.m_button25, 0, wx.ALIGN_CENTER_VERTICAL | wx.LEFT, 1050)
self.m_button24 = wx.Button(sbSizer1.GetStaticBox(), wx.ID_ANY, u"退出", wx.DefaultPosition, wx.DefaultSize, 0)
bSizer51.Add(self.m_button24, 0, wx.ALIGN_CENTER_VERTICAL | wx.LEFT, 20)
sbSizer1.Add(bSizer51, 1, wx.EXPAND, 5)
bSizer52 = wx.BoxSizer(wx.VERTICAL)
self.m_grid1 = wx.grid.Grid(sbSizer1.GetStaticBox(), wx.ID_ANY, wx.DefaultPosition, wx.Size(2000, 400), 0)
# Grid
self.m_grid1.CreateGrid(0, 9)
self.m_grid1.EnableEditing(False)
self.m_grid1.EnableGridLines(True)
self.m_grid1.EnableDragGridSize(True)
self.m_grid1.SetMargins(0, 0)
# Columns
self.m_grid1.EnableDragColMove(False)
self.m_grid1.EnableDragColSize(False)
self.m_grid1.SetColLabelSize(50)
self.m_grid1.SetColLabelValue(0, u"单号")
self.m_grid1.SetColLabelValue(1, u"需求描述")
self.m_grid1.SetColLabelValue(2, u"需求来源")
self.m_grid1.SetColLabelValue(3, u"创建人")
self.m_grid1.SetColLabelValue(4, u"流程状态")
self.m_grid1.SetColLabelValue(5, u"问题来源")
self.m_grid1.SetColLabelValue(6, u"项目状态")
self.m_grid1.SetColLabelValue(7, u"严重等级")
self.m_grid1.SetColLabelValue(8, u"创建日期")
self.m_grid1.SetColLabelAlignment(wx.ALIGN_CENTRE, wx.ALIGN_CENTRE)
# Rows
self.m_grid1.EnableDragRowSize(True)
self.m_grid1.SetRowLabelSize(50)
self.m_grid1.SetRowLabelAlignment(wx.ALIGN_CENTRE, wx.ALIGN_CENTRE)
# Label Appearance
# Cell Defaults
self.m_grid1.SetDefaultCellAlignment(wx.ALIGN_LEFT, wx.ALIGN_TOP)
bSizer52.Add(self.m_grid1, 0, wx.ALL, 10)
sbSizer1.Add(bSizer52, 3, wx.EXPAND, 20)
bSizer57 = wx.BoxSizer(wx.HORIZONTAL)
self.m_staticText451 = wx.StaticText(sbSizer1.GetStaticBox(), wx.ID_ANY, u"共 页", wx.DefaultPosition,
wx.DefaultSize, 0)
self.m_staticText451.Wrap(-1)
bSizer57.Add(self.m_staticText451, 0, wx.ALIGN_CENTER_VERTICAL | wx.LEFT, 500)
self.m_textCtrl251 = wx.TextCtrl(sbSizer1.GetStaticBox(), wx.ID_ANY, wx.EmptyString, wx.DefaultPosition,
wx.Size(40, -1), 0)
bSizer57.Add(self.m_textCtrl251, 0, wx.ALIGN_CENTER_VERTICAL | wx.LEFT, 15)
self.m_button28 = wx.Button(sbSizer1.GetStaticBox(), wx.ID_ANY, u"跳转", wx.DefaultPosition, wx.DefaultSize, 0)
bSizer57.Add(self.m_button28, 0, wx.ALIGN_CENTER_VERTICAL | wx.LEFT, 5)
self.m_button29 = wx.Button(sbSizer1.GetStaticBox(), wx.ID_ANY, u"上一页", wx.DefaultPosition, wx.DefaultSize, 0)
bSizer57.Add(self.m_button29, 0, wx.ALIGN_CENTER_VERTICAL | wx.LEFT, 5)
self.m_button30 = wx.Button(sbSizer1.GetStaticBox(), wx.ID_ANY, u"下一页", wx.DefaultPosition, wx.DefaultSize, 0)
bSizer57.Add(self.m_button30, 0, wx.ALIGN_CENTER_VERTICAL | wx.LEFT, 5)
sbSizer1.Add(bSizer57, 1, wx.EXPAND, 5)
self.m_panel23.SetSizer(sbSizer1)
self.m_panel23.Layout()
sbSizer1.Fit(self.m_panel23)
self.m_notebook6.AddPage(self.m_panel23, u"主界面", True)
bSizer20.Add(self.m_notebook6, 1, wx.EXPAND | wx.ALL, 5)
self.SetSizer(bSizer20)
self.Layout()
self.Centre(wx.BOTH)
##************************************##
# TODO
# 窗口关闭事件
self.Bind(wx.EVT_CLOSE, self.exit_sys)
# 退出系统
self.m_button24.Bind(wx.EVT_BUTTON, self.quit)
self.m_button24.Bind(wx.EVT_LEFT_DOWN, self.change_color_4)
# 系统主界面展示信息
self.cur_page_idx = 0
bill_total_nums = self.get_menu_bill_nums()
if bill_total_nums:
total_pages, page_nums_list = self.split_bill_view(bill_total_nums)
self.get_bill_data(0, page_nums_list)
self.m_staticText451.SetLabel("共" + str(total_pages) + "页")
self.m_textCtrl251.SetValue(str(self.cur_page_idx + 1))
else:
self.m_staticText451.SetLabel("共" + str(0) + "页")
# 实现上下翻页以及单号显示数据跳转功能
self.m_button29.Bind(wx.EVT_LEFT_DOWN, self.open_last_page) # 上一页
self.m_button30.Bind(wx.EVT_LEFT_DOWN, self.open_next_page) # 下一页
self.m_button28.Bind(wx.EVT_LEFT_DOWN, self.open_special_page) # 跳转页面
def __del__(self):
pass
def exit_sys(self, event):
toastone = wx.MessageDialog(None, "确定要退出系统吗?", "确认信息提示",
wx.YES_NO | wx.ICON_QUESTION)
if toastone.ShowModal() == wx.ID_YES: # 如果点击了提示框的确定按钮
toastone.Destroy() # 则关闭提示框
sys.exit(1)
if toastone.ShowModal() == wx.ID_NO:
toastone.Destroy() # 则关闭提示框
def change_color_2(self, event):
self.m_button25.SetBackgroundColour(wx.Colour("yellow"))
event.Skip()
def change_color_4(self, event):
self.m_button24.SetBackgroundColour(wx.Colour("red"))
event.Skip()
def open_last_page(self, event):
# 打开上一页
bill_total_nums = self.get_menu_bill_nums()
if bill_total_nums:
total_pages, page_nums_list = self.split_bill_view(bill_total_nums)
if self.cur_page_idx == 0:
toastone = wx.MessageDialog(None, "已位于首页,无法跳到上一页!", "消息提示",
wx.YES_DEFAULT | wx.ICON_QUESTION)
if toastone.ShowModal() == wx.ID_YES: # 如果点击了提示框的确定按钮
toastone.Destroy() # 则关闭提示框
# self.get_bill_data(total_pages - 1, page_nums_list)
# self.cur_page_idx = total_pages - 1
else:
self.get_bill_data(self.cur_page_idx - 1, page_nums_list)
self.cur_page_idx -= 1
self.m_textCtrl251.SetValue(str(self.cur_page_idx + 1))
else:
if self.cur_page_idx == 0:
toastone = wx.MessageDialog(None, "已位于首页,无法跳到上一页!", "消息提示",
wx.YES_DEFAULT | wx.ICON_QUESTION)
if toastone.ShowModal() == wx.ID_YES: # 如果点击了提示框的确定按钮
toastone.Destroy() # 则关闭提示框
def open_next_page(self, event):
# 打开下一页
bill_total_nums = self.get_menu_bill_nums()
if bill_total_nums:
total_pages, page_nums_list = self.split_bill_view(bill_total_nums)
if self.cur_page_idx == total_pages - 1:
toastone = wx.MessageDialog(None, "已位于最后一页,无法跳到下一页!", "消息提示",
wx.YES_DEFAULT | wx.ICON_QUESTION)
if toastone.ShowModal() == wx.ID_YES: # 如果点击了提示框的确定按钮
toastone.Destroy() # 则关闭提示框
# self.get_bill_data(0, page_nums_list)
# self.cur_page_idx = 0
else:
self.get_bill_data(self.cur_page_idx + 1, page_nums_list)
self.cur_page_idx += 1
self.m_textCtrl251.SetValue(str(self.cur_page_idx + 1))
else:
if self.cur_page_idx == 0:
toastone = wx.MessageDialog(None, "已位于最后一页,无法跳到下一页!", "消息提示",
wx.YES_DEFAULT | wx.ICON_QUESTION)
if toastone.ShowModal() == wx.ID_YES: # 如果点击了提示框的确定按钮
toastone.Destroy() # 则关闭提示框
def open_special_page(self, event):
# 跳转到特定的页面
try:
temp_page_num = int(self.m_textCtrl251.GetValue())
bill_total_nums = self.get_menu_bill_nums()
if bill_total_nums:
total_pages, page_nums_list = self.split_bill_view(bill_total_nums)
if (temp_page_num <= total_pages) and (temp_page_num >= 1):
page_idx = temp_page_num - 1
self.get_bill_data(page_idx, page_nums_list)
self.cur_page_idx = page_idx
self.m_textCtrl251.SetValue(str(self.cur_page_idx + 1))
else:
total_pages = self.split_bill_view(bill_total_nums)[0]
toastone = wx.MessageDialog(None, "请输入%s范围内的有效数字" % str([1, total_pages]), "错误信息提示",
wx.YES_DEFAULT | wx.ICON_QUESTION)
if toastone.ShowModal() == wx.ID_YES: # 如果点击了提示框的确定按钮
toastone.Destroy() # 则关闭提示框
else:
toastone = wx.MessageDialog(None, "系统还没有创建任何单号信息哦!", "信息提示",
wx.YES_DEFAULT | wx.ICON_QUESTION)
if toastone.ShowModal() == wx.ID_YES: # 如果点击了提示框的确定按钮
toastone.Destroy() # 则关闭提示框
except ValueError:
bill_total_nums = self.get_menu_bill_nums()
if bill_total_nums:
total_pages = self.split_bill_view(bill_total_nums)[0]
toastone = wx.MessageDialog(None, "请输入%s范围内的有效数字" % str([1, total_pages]), "错误信息提示",
wx.YES_DEFAULT | wx.ICON_QUESTION)
if toastone.ShowModal() == wx.ID_YES: # 如果点击了提示框的确定按钮
toastone.Destroy() # 则关闭提示框
else:
toastone = wx.MessageDialog(None, "系统还没有创建任何单号信息哦!", "信息提示",
wx.YES_DEFAULT | wx.ICON_QUESTION)
if toastone.ShowModal() == wx.ID_YES: # 如果点击了提示框的确定按钮
toastone.Destroy() # 则关闭提示框
def get_menu_bill_nums(self):
cur_path = os.path.dirname(os.path.realpath(__file__))
filename = os.path.join(cur_path, "单号总表.xlsx")
exe_excel = excel(filename, sheet_idx=0)
id_list = exe_excel.getColValues(column=1)[1:]
# 统计系统目前存在多少单号
for i in range(len(id_list)):
if id_list[i].strip() == "":
id_list.pop(i)
bill_total_nums = len(id_list)
return bill_total_nums
def split_bill_view(self, bill_total_nums):
if bill_total_nums % view_bill_nums > 0:
total_pages = bill_total_nums // view_bill_nums + 1
else:
total_pages = bill_total_nums // view_bill_nums
last_page_nums = bill_total_nums % view_bill_nums
page_nums_list = []
# page_nums_list表示每页展示的单号个数
if (total_pages >= 1) and (last_page_nums == 0):
last_page_nums = view_bill_nums
if total_pages <= 1:
page_nums_list.append(last_page_nums)
else:
page_nums_list.extend([view_bill_nums] * (total_pages - 1) + [last_page_nums])
return total_pages, page_nums_list
def get_bill_data(self, page_idx, page_nums_list):
# excel序号从1开始,grid控件序号从0开始
row_nums = self.m_grid1.GetNumberRows()
if row_nums > 0:
self.m_grid1.DeleteRows(pos=0, numRows=row_nums)
cur_path = os.path.dirname(os.path.realpath(__file__))
filename = os.path.join(cur_path, "单号总表.xlsx")
exe_excel = excel(filename, sheet_idx=0)
rows = exe_excel.getRowsClosNum()[0]
remain_rows_list = list(range(2, rows+1))[::-1][(view_bill_nums * page_idx):]
curr_rows_list = remain_rows_list[:page_nums_list[page_idx]]
column_idx_list = []
for i in range(len(bill_view_list)):
column_idx_list.append(excel_bill_list.index(bill_view_list[i]))
count = -1
for row in curr_rows_list:
count += 1
self.m_grid1.InsertRows(pos=count, numRows=1, updateLabels=True)
row_data = exe_excel.getRowValues(row)
default_col_size = 100
col_size_list = [default_col_size] * len(bill_view_list)
id_idx = bill_view_list.index("单号")
task_idx = bill_view_list.index("需求描述")
flow_idx = bill_view_list.index("流程状态")
col_size_list[id_idx] = 150
col_size_list[task_idx] = 200
col_size_list[flow_idx] = 200
default_row_size = 32
for col in range(len(bill_view_list)):
self.m_grid1.SetCellValue(count, col, str(row_data[column_idx_list[col]]))
# self.m_grid1.AutoSize() # 根据字体长度自动调整列宽
self.m_grid1.SetColSize(col, col_size_list[col])
self.m_grid1.SetRowSize(count, default_row_size)
if col == 1:
self.m_grid1.SetCellAlignment(count, col, wx.ALIGN_LEFT, wx.ALIGN_CENTRE)
else:
self.m_grid1.SetCellAlignment(count, col, wx.ALIGN_CENTRE, wx.ALIGN_CENTRE)
for row in range(count + 1):
id_idx = bill_view_list.index("单号")
demand_idx = bill_view_list.index("需求来源")
self.m_grid1.SetCellTextColour(row, id_idx, wx.Colour("blue"))
self.m_grid1.SetCellTextColour(row, demand_idx, wx.Colour("blue"))
def quit(self, event):
self.Destroy()
sys.exit(0)
def main():
app = wx.App()
frame = Menu_Frame(None)
frame.Show()
app.MainLoop()
if __name__ == '__main__':
main()
3. 代码适配指南
3.1 修改界面显示的内容
3.1.1 列标题数量不变的情况
与程序代码所在的.py文件同级的目录下面应该放置一个命名为“单号总表.xlsx”的表格,里面记录需要在界面上显示的数据。
可以修改“单号总表.xlsx”表格第一行的表头标题,更换需要在界面上显示的内容,但是列标题的数量不变还是9个。同时记得修改代码处全局的excel_bill_list这个列表,这个列表是用来记录表格“单号总表.xlsx”第一行列标题的。
bill_view_list这个列表是用来指定grid按照什么顺序显示“单号总表.xlsx”第一行列标题的。
还需要修改界面初始化设置代码处m_grid1每列显示的标题,与bill_view_list这个列表保持一致。
以上是grid显示的列数不变的情况,也就是说列标题还是9个只是内容替换了,如果列数少于9个,还需要调整grid初始化设置时候的列数;如果列数多于9个,不仅要修改grid初始化设置时的列数,为保证界面的美观,还需要调整grid的默认大小以及界面的大小等等,具体视情况而定。
3.1.2 列标题数量减少的情况
界面上需要呈现的内容只有5列,在原有程序代码的基础上,需要做如下适配。
需要保证“单号总表.xlsx”的列标题个数和两个列表bill_view_list和excel_bill_list的成员个数一致,都为5个。
grid初始化的列数设置:
grid的列标题设置,注意需要与列表bill_view_list成员顺序保持一致:
代码修改后的界面显示情况:
为保证界面的美观度,还需要适当的调整grid的行列宽度,行列宽度可以根据每列单元格需要显示的字数来设置,下面会具体介绍如何修改grid的行列宽度。
3.1.3 列标题数量增加的情况
列标题在9个的基础上增加2个,需要保证“单号总表.xlsx”的列标题个数和两个列表bill_view_list和excel_bill_list的成员个数一致,都为11个。
grid初始化的列数设置:
grid的列标题设置,注意需要与列表bill_view_list成员顺序保持一致:
3.2 修改界面每一页的数据显示个数
目前界面呈现的效果是一共70个数据,每页显示10个数据,一共7页可以上下翻页。
可以通过修改全局变量view_bill_nums指定界面每页显示的数据个数。
3.3 修改grid单元格的行宽和列宽
需要修改grid单元格的行列宽度可以在函数get_bill_data()处修改设置行列宽度的代码,grid采用SetColSize设置列宽,grid采用SetRowSize设置行宽。
def get_bill_data(self, page_idx, page_nums_list):
# excel序号从1开始,grid控件序号从0开始
row_nums = self.m_grid1.GetNumberRows()
if row_nums > 0:
self.m_grid1.DeleteRows(pos=0, numRows=row_nums)
cur_path = os.path.dirname(os.path.realpath(__file__))
filename = os.path.join(cur_path, "单号总表.xlsx")
exe_excel = excel(filename, sheet_idx=0)
rows = exe_excel.getRowsClosNum()[0]
from vactor_manage import view_bill_nums
remain_rows_list = list(range(2, rows+1))[::-1][(view_bill_nums * page_idx):]
curr_rows_list = remain_rows_list[:page_nums_list[page_idx]]
column_idx_list = []
for i in range(len(bill_view_list)):
column_idx_list.append(excel_bill_list.index(bill_view_list[i]))
count = -1
for row in curr_rows_list:
count += 1
self.m_grid1.InsertRows(pos=count, numRows=1, updateLabels=True)
row_data = exe_excel.getRowValues(row)
default_col_size = 100
col_size_list = [default_col_size] * len(bill_view_list)
id_idx = bill_view_list.index("单号")
task_idx = bill_view_list.index("需求描述")
flow_idx = bill_view_list.index("流程状态")
col_size_list[id_idx] = 150
col_size_list[task_idx] = 200
col_size_list[flow_idx] = 200
default_row_size = 32
for col in range(len(bill_view_list)):
self.m_grid1.SetCellValue(count, col, str(row_data[column_idx_list[col]]))
# self.m_grid1.AutoSize() # 根据字体长度自动调整列宽
self.m_grid1.SetColSize(col, col_size_list[col])
self.m_grid1.SetRowSize(count, default_row_size)
if col == 1:
self.m_grid1.SetCellAlignment(count, col, wx.ALIGN_LEFT, wx.ALIGN_CENTRE)
else:
self.m_grid1.SetCellAlignment(count, col, wx.ALIGN_CENTRE, wx.ALIGN_CENTRE)
for row in range(count + 1):
id_idx = bill_view_list.index("单号")
demand_idx = bill_view_list.index("需求来源")
self.m_grid1.SetCellTextColour(row, id_idx, wx.Colour("blue"))
self.m_grid1.SetCellTextColour(row, demand_idx, wx.Colour("blue"))