2021-04-25 pyqt5界面 socketserver服务端 EXCEL读写 工单派发 数据展示

        之前通过 EXCEL读写 实现用户名密码校验，新增，修改，工单发起，显示。受到很多局限性影响，还是转回mysql操作便捷些。

不过前端显示不受影响，只是调整 socketserver服务端 代码。

 

 

 

 

 

 

代码如下：

import socketserver
import json
import xlrd
import xlwt
from xlutils.copy import copy

class MyServer(socketserver.BaseRequestHandler):
    def handle(self):
        print('conn is:',self.request)
        print('addr is:',self.client_address)
        while True:
            try:
                data = self.request.recv(102400)
                if not data:break
                data = data.decode('utf-8')
                print('收到客户端的消息是：',data,self.client_address)
                js_data = json.loads(data)

                #类型1登录
                if str(js_data['leixing'])=='1':
                    #关联Excel表里 用户名密码 是否存在
                    ws = xlrd.open_workbook('./images/数据存储表.xls').sheet_by_name('用户名密码')
                    crow=ws.nrows
                    for i in range(crow):
                        if ws.cell_value(i, 0) == js_data['ziduan1']:

                            if str(ws.cell_value(i,1))==js_data['ziduan2']:
                                data='{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s","ziduan4":"%s","ziduan5":"%s","ziduan6":"%s" }' %(
                                '类型1',js_data['ziduan1'],js_data['ziduan2'],'用户名密码正确',ws.cell_value(i,2),ws.cell_value(i,3),ws.cell_value(i,4))

                                break
                            elif str(ws.cell_value(i,1))!=js_data['ziduan2']:
                                data = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                            '类型1', js_data['ziduan1'], js_data['ziduan2'], '用户名密码错误')

                                break

                    else:
                        data = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                                '类型1', js_data['ziduan1'], js_data['ziduan2'], '用户名不存在')

                #类型2 注册新用户
                elif str(js_data['leixing'])=='2':
                    ws = xlrd.open_workbook('./images/数据存储表.xls').sheet_by_name('用户名密码')
                    crow = ws.nrows
                    for i in range(crow):
                        if ws.cell_value(i, 0) == js_data['ziduan1']:
                            data = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                                '类型1', js_data['ziduan1'], js_data['ziduan2'], '用户名已存在')
                            break
                    else:
                        wb=xlrd.open_workbook('./images/数据存储表.xls')
                        nwb=copy(wb)
                        nws=nwb.get_sheet('用户名密码')
                        nws.write(crow,0,js_data['ziduan1'])
                        nws.write(crow, 1, js_data['ziduan2'])
                        nwb.save('./images/数据存储表.xls')

                        data ='{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                                '类型1', js_data['ziduan1'], js_data['ziduan2'], '用户名新建成功')

                # 类型3 忘记密码
                elif str(js_data['leixing']) == '3':
                    ws = xlrd.open_workbook('./images/数据存储表.xls').sheet_by_name('用户名密码')
                    crow = ws.nrows
                    #校验用户名存在
                    for i in range(crow):
                        #print(ws.cell_value(i, 0),js_data['ziduan1'],ws.cell_value(i, 3),js_data['ziduan2'])

                        if ws.cell_value(i, 0) == js_data['ziduan1']:
                            if ws.cell_value(i, 3) == js_data['ziduan2']:
                                data = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                                    '密码找回', js_data['ziduan1'], ws.cell_value(i, 1), '密码找回')
                                break
                    else:

                        data = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s","ziduan3":"%s" }' % (
                            '密码找回', js_data['ziduan1'], js_data['ziduan2'], '用户名不存在')

                elif str(js_data['leixing']) == '耗材管理':
                    ws = xlrd.open_workbook('./images/耗材资源表.xls')
                    dalei = []
                    if js_data['ziduan1']=='大类':
                        ws1=ws.sheet_by_name('大类')
                        crow=ws1.nrows
                        for i in range(crow):
                            cell_data=ws1.cell_value(i,0)
                            dalei.append(cell_data)
                        data = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s" }' % (
                            '耗材管理', '大类', dalei)
                    elif js_data['ziduan1']=='中类':
                        ws1 = ws.sheet_by_name('中类')
                        crow = ws1.nrows
                        for i in range(crow):
                            if ws1.cell_value(i,0)==js_data['ziduan2']:
                                cell_data=ws1.cell_value(i,1)
                                dalei.append(cell_data)
                        data = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s" }' % (
                            '耗材管理', '中类', dalei)
                    elif js_data['ziduan1']=='小类':
                        ws1 = ws.sheet_by_name('小类')
                        crow = ws1.nrows
                        for i in range(crow):
                            if ws1.cell_value(i, 0) == js_data['ziduan2']:
                                cell_data = ws1.cell_value(i, 1)
                                dalei.append(cell_data)
                        data = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s" }' % (
                            '耗材管理', '小类', dalei)
                    elif js_data['ziduan1']=='具体类型':
                        ws1 = ws.sheet_by_name('具体类型')
                        crow = ws1.nrows
                        for i in range(crow):
                            if ws1.cell_value(i, 0) == js_data['ziduan2']:
                                cell_data = ws1.cell_value(i, 1)
                                dalei.append(cell_data)
                        data = '{"leixing":"%s","ziduan1":"%s","ziduan2":"%s" }' % (
                            '耗材管理', '具体类型', dalei)

                    elif js_data['ziduan1'] == '工单提交':
                        ws = xlrd.open_workbook('./images/耗材申请工单存储.xls')
                        if js_data['ziduan2'] == '申请':
                            print(js_data['ziduan3'])
                            gdtj_sq = js_data['ziduan3'].replace('[', '').replace(']', '').split(",")
                            print(gdtj_sq)
                            crow = ws.sheet_by_name('耗材申请工单').nrows
                            nwb = copy(ws)
                            nws = nwb.get_sheet('耗材申请工单')
                            #遍历列表
                            l_num=0
                            for i in (gdtj_sq):