def import_custom():
try:
path = askopenfilename(defaultextension='xlsx')
# path = askopenfilename(defaultextension='xlsx', filetypes=[('excel', 'xlsx')])
if not path:
return
if not os.path.splitext(path)[1] == '.xlsx':
raise Exception('导入文件格式错误！请导入.xlsx结尾的文件！')
for i in range(0, 45, 5):
progress_bar["value"] = i + 1
root.update()
sleep(0.05)
new_customs, error_customs = data_handle.excel_provice_handle(path)
for i in range(40, 101, 10):
progress_bar["value"] = i
root.update()
sleep(0.05)
select_list["values"] = list(model.Custom().fetch_custom().keys())
msg = '导入成功！\n'
if new_customs:
for custom in new_customs:
msg += '新增用户：{} \n'.format(custom)
if error_customs:
for custom in error_customs:
msg += '导入格式有误用户：{}'.format(custom)
showinfo(message=msg, title='success!')
except Exception as e:
print(e)
showerror(title='导入失败', message=e)
progress_bar["value"] = 0
root.update()
def delete_custom(master, name, widget):
try:
custom = model.Custom()
custom_id = custom.find_custom(name)
if custom_id == 0:
raise Exception('用户名：{} 未录入数据库！'.format(name))
custom.delete_custom(custom_id)
widget['values'] = list(custom.fetch_custom().keys())
custom.close()
master.destroy()
showinfo(message='删除{}成功'.format(name))
except Exception as e:
print(e)
showerror('错误', e)
def save_contact(master, custom_name, remark, select_list):
custom = model.Custom()
try:
if not custom_name:
raise Exception('客户名不能为空！')
old_custom_name = select_list.get()
custom_id = custom.find_custom(old_custom_name)
custom.update_custom(custom_id, custom_name, remark)
select_list['values'] = list(custom.fetch_custom().keys())
index = select_list['values'].index(custom_name)
select_list.current(index)
master.destroy()
except Exception as e:
print(e)
showerror('错误', e)
finally:
custom.close()
def edit_contacts(select_list):
name = select_list.get()
custom = model.Custom()
infos = custom.fetch_custom()
remark = infos.get(name, '')
try:
if not name:
raise Exception('未选定编辑对象！')
custom_id = custom.find_custom(name)
is_special = custom.get_special_value(custom_id)
if is_special:
raise Exception('该用户为特殊客户，不可编辑！')
add_contacts(select_list, name, remark)
except Exception as e:
print(e)
showerror('错误', e)
finally:
custom.close()
params.nc = 3 # Num of image channels
params.nClass = 3 # Num of classes
params.isInVideo = False # Is Input video or still image
""" -------------------------------------------------------------------------"""
""" initialization """
trainLoader = dataset.loadTrain(params)
testLoader = dataset.loadTest(params)
device = torch.device("cuda:0" if (
torch.cuda.is_available() and params.nGPU > 0) else "cpu")
""" -------------------------------------------------------------------------"""
""" Create Networks & Optimizers"""
if (params.startEpoch == 0):
# Create the Classifier
if (params.model == 'custom'):
net = model.Custom(params).to(device)
elif (params.model == 'resnet'):
net = model.ResNet(params).to(device)
elif (params.model == 'custom3D'):
net = model.Custom3D(params).to(device)
elif (params.model == 'resnet3d'):
net = model.ResNet3D(params).to(device)
elif (params.model == 'resnet2p1d'):
net = model.ResNet2p1D(params).to(device)
if (device.type == 'cuda') and (params.nGPU > 1):
net = nn.DataParallel(net, list(range(params.nGPU)))
# Setup Adam optimizer
optimizer = optim.Adam(net.parameters(),
lr=params.lr,
def excel_provice_handle(path):
wb = load_workbook(path)
sheetnames = wb.sheetnames
custom = model.Custom()
custom_detail = model.CustomDetail()
special_custom = model.SpecialCustomDetail()
new_customs = []
error_customs = []
for sheetname in sheetnames:
sheet = wb[sheetname]
first_rows = list(sheet.rows)[0]
tr_index_dict = {}
is_error_date = True
is_special = 0
# 遍历第一行（表头）的值
for index, v in enumerate(first_rows):
# openpyxl 从1开始
index = index + 1
value = v.value
if value:
value = value.strip()
else:
continue
if value == '目的地':
tr_index_dict['目的地'] = index
elif value == '首重重量':
tr_index_dict['首重重量'] = index
elif value == '首重价格':
tr_index_dict['首重价格'] = index
elif value == '续重价格':
tr_index_dict['续重价格'] = index
elif '<=' in value:
value = '<=' + value.split('<=')[1]
tr_index_dict[value] = index
elif '>' in value:
value = '>' + value.split('>')[1]
tr_index_dict[value] = index
# 验证表格结构
if '目的地' in tr_index_dict:
if '首重重量' in tr_index_dict and '续重价格' in tr_index_dict:
is_error_date = False
else:
tmp = []
for k in tr_index_dict.keys():
if '<=' in k:
is_error_date = False
if '>' in k:
tmp.append(k)
# 需要保证同时存在 <= 和 >
if not tmp:
is_error_date = True
is_special = 1
if is_error_date:
error_customs.append(sheetname)
continue
custom_id = custom.find_custom(sheetname)
if custom_id == 0:
custom_id = custom.add_custom(sheetname, is_special)
new_customs.append(sheetname)
# 找到该用户的详情(如果有)，会根据是否是特殊用户来进行判断
if is_special:
detail_dict = special_custom.fetch_custom_detail(custom_id)
else:
detail_dict = custom_detail.fetch_custom_detail(custom_id)
# 一行一行的进行遍历
for row in range(2, sheet.max_row + 1):
provice = sheet.cell(row=row, column=tr_index_dict['目的地']).value
# 因为可能把空的单元格算进去，所以需要判断
if not provice:
continue
# 普通用户下的操作
if not is_special:
try:
f_num = float(
sheet.cell(row=row,
column=tr_index_dict['首重重量']).value)
f_price = float(
sheet.cell(row=row,
column=tr_index_dict['首重价格']).value)
n_price = float(
sheet.cell(row=row,
column=tr_index_dict['续重价格']).value)
except ValueError:
raise ValueError(
'请检查表 - {} 中的第{}行，确保首重重量/ 首重价格/ 续重价格中除了数字以及小数点以外不含其他字符！'
.format(sheetname, row))
custom_detail_id = custom_detail.find_custom_detail(
custom_id, provice)
# 找到则执行更新操作（小优化的），未找到则执行添加操作
if custom_detail_id:
if provice in detail_dict:
if f_num == detail_dict[provice]['首重重量'] \
and f_price == detail_dict[provice]['首重价格'] \
and n_price == detail_dict[provice]['续重价格']:
continue
else:
custom_detail.update_custom_detail(
custom_id, provice, f_num, f_price, n_price)
else:
custom_detail.update_custom_detail(
custom_id, provice, f_num, f_price, n_price)
else:
custom_detail.add_custom_detail(custom_id, provice, f_num,
f_price, n_price)
# 特殊用户下的操作
else:
range_dict = {}
for key in tr_index_dict.keys():
if key == '目的地':
continue
val = sheet.cell(row=row, column=tr_index_dict[key]).value
try:
val = float(val)
except ValueError:
if '（' in val:
val = val.replace('（', '(')
if '）' in val:
val = val.replace('）', ')')
if '+' in val:
tmp = val.split('+')
val = str(
sheet[tmp[0]].value) + '+' + tmp[1].strip()
# 限制输入字符
try:
tmp2 = val
if 'ROUNDUP' in tmp2:
tmp2 = tmp2.replace('ROUNDUP', '')
tmp2 = tmp2.replace('重量', '100')
eval(tmp2)
except Exception:
raise Exception(
'表-{}, 行-{}, 列-{} 中出现除了占位符<重量>以外的其他中文或中文符号！'
.format(sheetname, row,
tr_index_dict[key]))
else:
raise Exception('表-{}, 行-{}, 列-{} 格式有误！'.format(
sheetname, row, tr_index_dict[key]))
finally:
range_dict[key] = val
range_dict = dumps(range_dict)
special_custom_id = special_custom.find_custom_detail(
custom_id, provice)
if special_custom_id:
if provice in detail_dict:
if range_dict == detail_dict[provice]['重量价格']:
continue
else:
special_custom.update_custom_detail(
custom_id, provice, range_dict)
else:
special_custom.update_custom_detail(
custom_id, provice, range_dict)
else:
special_custom.add_custom_detail(custom_id, provice,
range_dict)
custom.close()
custom_detail.close()
special_custom.close()
return new_customs, error_customs
def excel_handle2(path):
# 注：openpyxl 下标从1开始
wb = load_workbook(path)
sheetnames = wb.sheetnames
custom = model.Custom()
custom_detail = model.CustomDetail()
special_custom = model.SpecialCustomDetail()
unsign_info = {} # 记录未注册用户 name: sheet位置
error_provice = {}
head = {} # 表头位置
for sheetname in sheetnames:
current_sheetname = sheetname
sheet = wb[sheetname]
price_col = sheet.max_column - 1
price_sum_col = sheet.max_column
max_row = sheet.max_row
print(max_row)
sum_price = 0
# 去除空sheet
if max_row <= 1:
continue
print(sheetname)
# 动态获取表头位置
for col in range(1, sheet.max_column + 1):
val = sheet.cell(1, col).value
if val:
val = val.strip()
if val == '重量':
head['weight'] = col
elif val == '目的省份':
head['provice'] = col
elif val == '寄件客户':
head['name'] = col
# 检查表头格式
if len(head.keys()) != 3:
raise Exception(
'sheet 名：{} 表头名称错误，请检查表头是否为 重量/ 目的省份/ 寄件客户!'.format(sheetname))
# 在最后一行增加运费与运费总数
if not sheet.cell(row=1, column=price_sum_col).value == '运费总数':
price_col = sheet.max_column + 1
price_sum_col = price_col + 1
sheet.cell(row=1, column=price_col).value = '运费'
sheet.cell(row=1, column=price_sum_col).value = '运费总数'
# 获取所有客户
custom_names = [name for name, _ in custom.fetch_custom().items()]
"""
name_dict字典格式：{
'张三': {
'湖南': {
'首重重量': 1.0,
...
}
'湖北': ...
}
'李四': ...
}
"""
name_dict = {}
for custom_name in custom_names:
custom_id = custom.find_custom(custom_name)
is_special = custom.get_special_value(custom_id)
if is_special:
name_dict[custom_name] = special_custom.fetch_custom_detail(
custom_id)
else:
name_dict[custom_name] = custom_detail.fetch_custom_detail(
custom_id)
# 从第2行开始，一行行的数据
for row in range(2, max_row + 1):
weight = sheet.cell(row=row, column=head['weight']).value
provice = sheet.cell(row=row, column=head['provice']).value
name = sheet.cell(row=row, column=head['name']).value
is_special = 0
can_calc = False
custom_id = custom.find_custom_like(name)
if custom_id:
is_special = custom.get_special_value(custom_id)
if weight:
try:
weight = float(weight)
except ValueError:
raise ValueError('重量行 - {} 中不能包含中文! '.format(row))
else:
continue
if provice:
provice = provice.strip()
if '=' in provice:
provice = provice.replace('=', '')
provice = sheet[provice].value
else:
continue
if name:
name = name.strip()
else:
continue
# 检查名字是否存在于数据库中
if name not in custom_names:
if name not in unsign_info:
unsign_info[name] = [
'{}, 第一次出现在工作表<{}> - {}行)'.format(
name, current_sheetname, row - 1), 1
]
else:
unsign_info[name][-1] += 1
continue
# 精确查找未找到时，模糊匹配省份
if provice in name_dict[name]:
can_calc = True
else:
if is_special:
if special_custom.find_custom_detail(custom_id, provice):
provice = special_custom.find_provice(
custom_id, provice)
if provice:
can_calc = True
else:
if custom_detail.find_custom_detail(custom_id, provice):
provice = custom_detail.find_provice(
custom_id, provice)
if provice:
can_calc = True
if can_calc:
if is_special:
price_data = name_dict[name][provice]['重量价格']
price_dict = loads(price_data)
price = calc_special_custom_price(weight, price_dict)
else:
first_weight_num = name_dict[name][provice]['首重重量']
first_weight_price = name_dict[name][provice]['首重价格']
next_weight_price = name_dict[name][provice]['续重价格']
price = calc_price(weight, first_weight_num,
first_weight_price, next_weight_price)
sum_price += price
sheet.cell(row=row, column=price_col).value = price
else:
print(provice)
error_provice[provice] = row
# 写入总金额
sheet.cell(2, price_sum_col).value = float(sum_price)
wb.save(path)
custom.close()
custom_detail.close()
return unsign_info, error_provice
print(e)
showerror(title='导入失败', message=e)
progress_bar["value"] = 0
root.update()
model.init_db()
root = Tk()
root.title('费用计算工具')
root.resizable(False, False) # 让窗口不可以缩放
size = utils.center_root(root, 600, 320)
root.geometry(size)
root.maxsize(600, 450)
root.minsize(300, 240)
custom = model.Custom()
infos = custom.fetch_custom()
# 第零行：下拉列表、新增按钮、编辑按钮
Label(root, text='联系人列表:').grid(row=0, column=0, pady=10)
select_list = Combobox(root, width=30)
select_list["values"] = list(infos.keys())
select_list.bind("<<ComboboxSelected>>", select_contact_by_name)
select_list.grid(row=0, column=1, pady=10)
btn_import_custom = Button(root, text='导入', command=import_custom)
btn_import_custom.grid(row=0, column=2, pady=10, sticky=W)
btn_edit = Button(root, text='编辑', command=lambda: edit_contacts(select_list))
btn_edit.grid(row=0, column=3, pady=10, sticky=W)
btn_add = Button(root, text='新增', command=lambda: add_contacts(select_list))
btn_add.grid(row=0, column=4, pady=10, sticky=W)
def edit_provice_info(custom_name, btn_confirm):
provice_root = Toplevel()
if not custom_name:
showerror('错误', '请先填写客户名！')
provice_root.destroy()
# 解除确认按钮不可编辑状态
btn_confirm['state'] = 'normal'
provice_root.title('编辑省份运费信息')
if provice_root.winfo_screenheight() > 768:
provice_root.geometry(center_root(provice_root, 400, 840))
else:
provice_root.geometry(center_root(provice_root, 1300, 200))
provices = [
'长沙市', '湖南省', '北京市', '天津市', '上海市', '重庆市', '河北省', '山西省', '辽宁省', '吉林省',
'黑龙江省', '江苏省', '浙江省', '安徽省', '福建省', '江西省', '山东省', '河南省', '湖北省', '广东省',
'海南省', '四川省', '贵州省', '云南省', '陕西省', '甘肃省', '青海省', '内蒙古自治区', '广西壮族自治区',
'西藏自治区', '宁夏回族自治区', '新疆维吾尔自治区'
]
provice_dict = {}
custom = model.Custom()
custom_detail = model.CustomDetail()
custom_id = custom.find_custom(custom_name)
provice_info = custom_detail.fetch_custom_detail(custom_id)
print(custom_id)
# 当为新建的联系人时，会自动新建联系人详细数据
if custom_id == 0:
custom_id = custom.add_custom(custom_name)
for provice in provices:
if provice in provice_info:
continue
else:
custom_detail.add_custom_detail(custom_id, provice, 0, 0, 0)
provice_info = custom_detail.fetch_custom_detail(custom_id)
l1 = Label(provice_root, text='省份', font=('Helvetica', '9', 'normal'))
l2 = Label(provice_root,
text='首重重量(kg)',
font=('Helvetica', '9', 'normal'))
l3 = Label(provice_root, text='首重价格(元)', font=('Helvetica', '9', 'normal'))
l4 = Label(provice_root, text='续重价格(元)', font=('Helvetica', '9', 'normal'))
for index, provice in enumerate(provices):
# 设置值到输入框
f_w_text = StringVar()
f_p_text = StringVar()
n_p_text = StringVar()
if provice in provice_info:
f_w_text.set(provice_info[provice]['首重重量'])
f_p_text.set(provice_info[provice]['首重价格'])
n_p_text.set(provice_info[provice]['续重价格'])
tmp = []
provice_label = Label(provice_root,
text=provice,
font=('Helvetica', '9', 'normal'))
f_weight = Entry(provice_root,
width=5,
textvariable=f_w_text,
font=('Helvetica', '9', 'normal'))
f_price = Entry(provice_root,
width=5,
textvariable=f_p_text,
font=('Helvetica', '9', 'normal'))
n_price = Entry(provice_root,
width=5,
textvariable=n_p_text,
font=('Helvetica', '9', 'normal'))
tmp.append(f_weight)
tmp.append(f_price)
tmp.append(n_price)
provice_dict[provice] = tmp
if provice_root.winfo_screenheight() > 768:
provice_label.grid(row=index + 1)
f_weight.grid(row=index + 1, column=1)
f_price.grid(row=index + 1, column=2)
n_price.grid(row=index + 1, column=3)
else:
provice_label.grid(row=0, column=index + 1)
f_weight.grid(row=1, column=index + 1)
f_price.grid(row=2, column=index + 1)
n_price.grid(row=3, column=index + 1)
btn_save = Button(provice_root,
text='确定',
command=lambda: save_provice_info(
provice_root, provice_dict, custom_id))
btn_save.grid(row=len(provices) + 2, column=0)
btn_cancel = Button(provice_root, text='取消', command=provice_root.destroy)
if provice_root.winfo_screenheight() > 768:
l1.grid(row=0, column=0)
l2.grid(row=0, column=1)
l3.grid(row=0, column=2)
l4.grid(row=0, column=3)
btn_save.grid(row=len(provices) + 2, column=0)
btn_cancel.grid(row=len(provices) + 2,
column=1,
columnspan=3,
sticky=E)
else:
l1.grid(row=0, column=0)
l2.grid(row=1, column=0)
l3.grid(row=2, column=0)
l4.grid(row=3, column=0)
btn_save.grid(row=4, column=0)
btn_cancel.grid(row=4, column=1)
custom.close()
custom_detail.close()