使用wxPython开发订单管理系统－查询页面

查询页面可以修改订单状态，打勾表示结案。另外可以生成excel的报告，供客户对账使用
主要涉及知识：
wx.StatusBar 状态栏

wx.dataview 数据视图，可以像EXCEL那样查看数据

wx.adv.DatePickerCtrl 日期选择

xlwt 将数据写入excel 的第三方库

import sys
import os
import operator
import datetime
import time
import wx
import wx.dataview as dv
import wx.adv
import xlwt
from sqloperation import  select_manydata_from_db,update_db,read_data_all
import order
import analysis


def open_file(file):
    with open(file,"r",encoding="utf-8") as f:
        return f.readlines()
    
customer=open_file(".//constant//客户.txt")
driver=open_file(".//constant//司机.txt")

row_list=[]
value="ID,日期,客户,调度单号,线路,驾驶员,含税运费,含税其它费用,运费客户计,其它费用客户计,费用差异,是否结案,给司机费用"
table_name="费用明细"
field="费用差异"

RELATIVEWIDTHS = False

class DatePicker( wx.adv.DatePickerCtrl):  #日期选择类
    def __init__(self,parent,dt,style=wx.adv.DP_DEFAULT):
        super(DatePicker,self).__init__(parent,dt=dt,style=style)
        self.SetInitialSize((120,-1))


class CustomStatusBar(wx.StatusBar):     #状态栏
    def __init__(self, parent):
        wx.StatusBar.__init__(self, parent, -1)
        # This status bar has three fields
        self.SetFieldsCount(3)
        if RELATIVEWIDTHS:
            # Sets the three fields to be relative widths to each other.
            self.SetStatusWidths([-2, -1, -2])
        else:
            self.SetStatusWidths([-2, -1, 140])   #右边固定，左边两项分配比例
        self.sizeChanged = False
        

class MyFrame(wx.Frame):
    def __init__(self,user,parent,size=(1160, 650)):
        self.user=user
        super(MyFrame,self).__init__(parent,title="订单查询_"+user,size=size,pos=(60,60))
        self.locale = wx.Locale(wx.LANGUAGE_CHINESE_SIMPLIFIED)
        icon = wx.Icon('smart.ico')
        self.SetIcon(icon)
        
        data1=[]#select_manydata_from_db( value,table_name) #获取用户数据data_all#[]#
        
        data_num=len(data1)
        self.panel=TestPanel(self,sys.stdout, data= data1)  #MyPanel实例  (self, parent, log, model=None, data=None):
        
        global sb
        sb =CustomStatusBar(self)
        sb.SetStatusText("符合筛选要求共"+str(data_num)+"项", 0)
        self.SetStatusBar(sb)    #设置状态栏


class TestModel(dv.DataViewIndexListModel):  #能够按行得到位置的特定数据模型
    def __init__(self, data, log=None):
        dv.DataViewIndexListModel.__init__(self, len(data))      
        self.data = data
        self.log = log

    def GetValueByRow(self, row, col): #按行获取数据
        return str(self.data[row][col])

    def SetValueByRow(self, value, row, col):    #在模型中写入值，修改数据，当使用者编辑数据时调用函数
        #self.log.write("SetValue: (%d,%d) %s\n" % (row, col, value))  #打印出动作
        self.data[row][col] = value  #单元格值    问题在这里
        return True

    def GetColumnCount(self):   #得到数据的列数        
        return len(self.data[0])
       
    def GetColumnType(self, col):   #制定列的数据属性
        return "string"

    def GetCount(self):   #得到数据的行数
        return len(self.data)

    def GetAttrByRow(self, row, col, attr):
        if col == 9:       #第八列蓝色
            attr.SetColour('blue')
            attr.SetBold(True)
            return True
        return False

    def Compare(self, item1, item2, col, ascending):      #DataViewIndexListModel的方法Compare(self, item1, item2, column, ascending)
        if not ascending: # swap sort order?
            item2, item1 = item1, item2
        row1 = self.GetRow(item1)
        row2 = self.GetRow(item2)
        if col == 0:
            return operator.lt(int(self.data[row1][col]), int(self.data[row2][col]))
        else:
            return operator.lt(self.data[row1][col], self.data[row2][col])

    def DeleteRows(self, rows):
        rows = list(rows)
        print("rows",rows)
        rows.sort(reverse=True)
        for row in rows:
            del self.data[row]
            self.RowDeleted(row)


class TestPanel(wx.Panel):
    def __init__(self, parent, log, model=None, data=None):
        self.log = log
        self.data=data
        wx.Panel.__init__(self, parent, -1)
        
        # Create a dataview control
        self.dvc = dv.DataViewCtrl(self,
                                   style=wx.BORDER_THEME
                                   | dv.DV_ROW_LINES # 行与行之间的颜色
                                   | dv.DV_HORIZ_RULES #网格
                                   | dv.DV_VERT_RULES   #列于列之间的分隔线
                                   | dv.DV_MULTIPLE
                                   )
    
        # Create an instance of our simple model...
        if model is None:
            self.model = TestModel(self.data, log)  #表单模型实例(含数据)
        else:
            self.model = model

        print("old mode",self.model)
        self.dvc.AssociateModel(self.model)    #数据与模型连接
        #del self.model
        
        self.dvc.AppendTextColumn("日期",   1, width=100,align=wx.ALIGN_CENTER, mode=dv.DATAVIEW_CELL_INERT)  
        self.dvc.AppendTextColumn("客户",   2, width=80,align=wx.ALIGN_CENTER, mode=dv.DATAVIEW_CELL_INERT)
        self.dvc.AppendTextColumn("调度单号",  3, width=120, mode=dv.DATAVIEW_CELL_EDITABLE) #可编辑的 
        self.dvc.AppendTextColumn("线路",   4, width=150, mode=dv.DATAVIEW_CELL_INERT)
        self.dvc.AppendTextColumn("驾驶员",   5, width=50, mode=dv.DATAVIEW_CELL_INERT)
        self.dvc.AppendTextColumn("含税运费",   6, width=70,align=wx.ALIGN_CENTER, mode=dv.DATAVIEW_CELL_INERT)
        self.dvc.AppendTextColumn("含税其它费用",   7, width=90,align=wx.ALIGN_CENTER, mode=dv.DATAVIEW_CELL_INERT)
        self.dvc.AppendTextColumn("运费客户计",   8, width=80,align=wx.ALIGN_CENTER, mode=dv.DATAVIEW_CELL_INERT)
        self.dvc.AppendTextColumn("其它费用客户计",   9, width=110,align=wx.ALIGN_CENTER, mode=dv.DATAVIEW_CELL_INERT)
        self.dvc.AppendTextColumn("费用差异",   10, width=80,align=wx.ALIGN_CENTER,  mode=dv.DATAVIEW_CELL_INERT)
        self.dvc.AppendToggleColumn("是否结案",   11, width=80,align=wx.ALIGN_CENTER,  mode=wx.dataview.DATAVIEW_CELL_ACTIVATABLE)
        self.dvc.AppendTextColumn("给司机的费用",   12, width=80,align=wx.ALIGN_CENTER,  mode=dv.DATAVIEW_CELL_INERT)


        c0 = self.dvc.PrependTextColumn("ID", 0, width=40)   #Prepends a column for rendering text.往前插入
        c0.Alignment = wx.ALIGN_RIGHT
        c0.Renderer.Alignment = wx.ALIGN_RIGHT
        c0.MinWidth = 40
        c0.Reorderable = False


        for c in self.dvc.Columns:    #设置列属性
            c.Sortable = False #True
            c.Reorderable = True

        self.Sizer = wx.BoxSizer(wx.VERTICAL)

        filter_layout= wx.BoxSizer(wx.HORIZONTAL)    #删选布局
        self.label_start=wx.StaticText( self,label="起始日期:")
        self.text_start=wx.TextCtrl( self,style=wx.TE_LEFT)
        self.text_start.SetValue('2020-01-01')
        self.label_end=wx.StaticText( self,label="结束日期:")
        #self.text_end=wx.TextCtrl( self,style=wx.TE_LEFT)
        now=wx.DateTime.Now()
        self.text_end=DatePicker(self,now,wx.adv.DP_DROPDOWN|wx.adv.DP_SHOWCENTURY)
        #print("date",self.text_end.GetValue().FormatISODate())
        self.label_customer=wx.StaticText( self,label="客户:")
        self.text_customer=wx.Choice(self, -1, choices=customer) 
        self.text_customer.SetSelection(0)

        self.label_diff=wx.StaticText( self,label="费用差异")
        self.text_diff=wx.Choice(self, -1, choices=["全部","无差异"]) 
        self.text_diff.SetSelection(0)
        
        self.label_case=wx.StaticText( self,label="是否结案")
        self.text_case=wx.Choice(self, -1, choices=["全部","是","否"]) 
        self.text_case.SetSelection(0)

        self.label_driver=wx.StaticText( self,label="驾驶员")
        self.text_driver=wx.Choice(self, -1, choices=driver) 
        self.text_driver.SetSelection(0)
        
        button_query = wx.Button(self, label="确认")
        self.Bind(wx.EVT_BUTTON, self.OnConfirmed, button_query)
        button_excel = wx.Button(self, label="转化成EXCEL")
        self.Bind(wx.EVT_BUTTON, self.ToExcel, button_excel )

        #button_analysis = wx.Button(self, label="图表分析")
        #self.Bind(wx.EVT_BUTTON, self.ToAnalysis, button_analysis )        

        
        filter_layout.Add(self.label_start, 0, wx.LEFT, 10)
        filter_layout.Add(self.text_start, 0, wx.LEFT, 10)
        filter_layout.Add(self.label_end, 0, wx.LEFT, 10)
        filter_layout.Add(self.text_end, 0, wx.LEFT, 10)
        filter_layout.Add(self.label_customer, 0, wx.LEFT, 10)
        filter_layout.Add(self.text_customer, 0, wx.LEFT, 10)
        filter_layout.Add(self.label_diff, 0, wx.LEFT, 10)
        filter_layout.Add(self.text_diff, 0, wx.LEFT, 10)
        filter_layout.Add(self.label_case, 0, wx.LEFT, 10)
        filter_layout.Add(self.text_case, 0, wx.LEFT, 10)
        filter_layout.Add(self.label_driver, 0, wx.LEFT, 10)
        filter_layout.Add(self.text_driver, 0, wx.LEFT, 10)

        
        filter_layout.Add(button_query, 0, wx.LEFT, 40)
        filter_layout.Add(button_excel, 0, wx.LEFT, 10)
        #filter_layout.Add(button_analysis, 0, wx.LEFT, 10)

        self.Sizer.Add(filter_layout,0, wx.TOP|wx.BOTTOM, 5)            

        self.Sizer.Add(self.dvc, 1, wx.EXPAND)  #加入表格
        
        b1 = wx.Button(self, label="返回")
        self.Bind(wx.EVT_BUTTON, self.Back, b1)
        bitmap=wx.Bitmap("pic//back.png")
        b1.SetBitmap(bitmap)
        
        b3 = wx.Button(self, label="清空")
        self.Bind(wx.EVT_BUTTON, self.OnDeleteRows, b3)
        bitmap=wx.Bitmap("pic//clear.png")
        b3.SetBitmap(bitmap)


        button_analysis = wx.Button(self, label="图表分析")
        self.Bind(wx.EVT_BUTTON, self.ToAnalysis, button_analysis )
        bitmap=wx.Bitmap("pic//chart.png")
        button_analysis.SetBitmap(bitmap)
        
        b2 = wx.Button(self, label="保存")
        self.Bind(wx.EVT_BUTTON, self.UpdateData, b2)
        bitmap=wx.Bitmap("pic//save.png")
        b2.SetBitmap(bitmap)        

        btnbox = wx.BoxSizer(wx.HORIZONTAL)    #按钮布局
        btnbox.Add(b1, 0 , wx.EXPAND, 10)
        btnbox.Add(b3, 0,  wx.EXPAND, 10)
        btnbox.Add(button_analysis, 0,  wx.EXPAND, 10)
        #btnbox.Add((60, 20),0, wx.EXPAND)
        btnbox.Add(b2, 1, wx.LEFT, 700)

        self.Sizer.Add(btnbox,0, wx.TOP|wx.BOTTOM, 5)
        self.SetSizer(self.Sizer)

        self.Bind(dv.EVT_DATAVIEW_ITEM_EDITING_DONE, self.OnEditingDone, self.dvc)
        self.Bind(dv.EVT_DATAVIEW_ITEM_VALUE_CHANGED, self.OnSelected, self.dvc)        
          
    def OnEditingDone(self, evt):
        self.log.write("OnEditingDone\n")

    def OnValueChanged(self, evt):        
        self.log.write("OnValueChanged\n")
        

    def Back(self, evt):
          self.Parent.Hide()  #frame隐藏
          #frame=mainwindow.MyFrame("user",parent=None)  #导入新窗体
          frame=order.MyFrame("usertst",parent=None)
          frame.Show()


    def ToAnalysis(self, evt):
          self.Parent.Hide()  #frame隐藏
          #frame=mainwindow.MyFrame("user",parent=None)  #导入新窗体
          frame=analysis.MyFrame("usertst",parent=None)
          frame.Show()

    def OnDeleteRows(self, evt):      
        '''items = self.dvc.GetSelections()
        print("items",items)
        rows = [self.model.GetRow(item) for item in items]  #返回行数
        print("rows",rows)'''
        rows=[]
        rows_counted=len(self.data)
        for i in range(rows_counted):
             rows.append(i)
        self.model.DeleteRows(rows)
        
                 
    def OnSelected(self, evt):
        global row_list
        items = self.dvc.GetSelections()   #返回列表Return type:	DataViewItemArray
        #items = self.dvc
        print( items,self.dvc.GetSelectedItemsCount())
        #rows = [self.model.GetRow(item) for item in items]   #返回位置GetRow(self, item)
        rows = [self.model.GetRow(item) for item in items]
        row_list+=rows
        #print("row_list",row_list)

    def UpdateData(self, evt):    #保存数据
           y=iter(set(row_list))           
           #for i in set(row_list):
           for i in y:
                 a=self.model.GetValueByRow( i, 0) #获取单元格值
                 b=self.model.GetValueByRow( i, 11)
                 c=self.model.GetValueByRow( i, 3)
                 update_db(table_name,"是否结案",b,"ID",a)
                 update_db(table_name,"调度单号",c,"ID",a)
                 #next(y)
           wx.MessageBox("数据已更新")
           

    def OnConfirmed(self, evt):            
        #self.OnDeleteRows(evt)
        d={}
        start=self.text_start.GetValue()
        end=self.text_end.GetValue().FormatISODate()
        GetCase=["全部","是","否"][self.text_case.GetSelection()]
        GetDiff=["全部","无差异"][self.text_diff.GetSelection()]       
        GetCustomer=customer[self.text_customer.GetSelection()].strip()
        GetDriver=driver[self.text_driver.GetSelection()].strip()
        if GetDiff !="全部":
            if  GetDiff == "无差异":
                 d["费用差异"]='0.0'

            
        if GetCase !="全部":
            if  GetCase == "是":
                 d["是否结案"]="True"
            if  GetCase == "否":
                 d["是否结案"]="False"
                 
        if  GetCustomer !="全部":
            d["客户"]=GetCustomer

        if GetDriver !="全部":
            d["驾驶员"]=GetDriver
        print(d)
        self.data=read_data_all(table_name,value, start,end,condition=d)
        self.model= TestModel(self.data)
        self.dvc.AssociateModel(self.model)        
        sb.SetStatusText("符合筛选要求共"+str(len(self.data))+"项", 0)


    def ToExcel(self, evt):
         path=os.getcwd()+"//对账报告"
         start=self.text_start.GetValue()
         end=self.text_end.GetValue().FormatISODate()
         if not os.path.exists(path):
                  os.makedirs(path)
         filename=start+'_'+end+'report.xls'
         file=os.path.join(path,filename)
          #filesrc=os.path.join(file_path,file) 
         workbook = xlwt.Workbook(encoding = 'utf-8')
         worksheet = workbook.add_sheet('账单')           
         style= xlwt.XFStyle()
         al = xlwt.Alignment()
         al.horz = 0x02  # 设置水平居中
         al.vert = 0x01   # 设置垂直居中
         style.alignment = al

         style1= xlwt.XFStyle()
         #pattern2 = xlwt.Pattern()
         #pattern2.pattern = xlwt.Pattern.SOLID_PATTERN
         #pattern2.pattern_fore_colour = 4
         
         al1= xlwt.Alignment()
         al1.horz = 0x02  # 设置水平居中
         al1.vert = 0x01   # 设置垂直居中

         fnt = xlwt.Font()
         fnt.bold = True
         #style1.pattern = pattern2
         style1.alignment = al 
         style1.font = fnt

         
         listhead=value.split(",")
         for k,p in enumerate(listhead):
                worksheet.col(k).width=150*40
                worksheet.write(2, k, p,style1)
                
         for i in range(len(self.data)):
              for j in range(len(self.data[0])):           
                  worksheet.write(i+3, j, self.data[i][j],style)                  
         workbook.save(file)
         wx.MessageBox(filename+"已生成")
        
if __name__ == '__main__':
    app=wx.App()      #创建App类的实例
    frame=MyFrame("usertest",parent=None)
    frame.Show()
    app.MainLoop()  #调用MainLoop()主循环方法