def process():
try:
pythoncom.CoInitialize()
ex = Easyexcel(filePath,
visible=False,
access_password=openPassword,
write_res_password=editPassword)
dm = DataManager() # 子线程要创建一个单独的数据管理类
for i, sheet in enumerate(
sheet_info): # sheet_info格式为[(Sheet名, 数据表类型, 数据表名称)]
sheet_name, table_type, table_name = sheet
self.progressSignal.emit(
i, f"正在导入工作簿'{sheet_name}'({i}/{len(sheet_info)})")
header_dict, sheet_data = ex.get_sheet(sheet_name)
flag, msg = ExcelCheck.characters_check(sheet_data)
if not flag:
ex.close()
raise Exception(msg)
if table_type == '售后员提成明细':
sheet_data = ExcelCheck.formatted_after_sales(
header_dict, sheet_data)
dm.create_table(table_type, table_name,
ExcelCheck.headers["售后员提成明细"])
dm.insert_data(table_name,
ExcelCheck.headers["售后员提成明细"],
sheet_data)
else:
# 过滤掉列名中的特殊字符以防导致SQL命令格式错误
f_header_dict = {
k.replace('\n', ''): v
for k, v in header_dict.items()
}
columns = sorted(
f_header_dict.keys(),
key=lambda x: f_header_dict[x]) # 与Excel表格列顺序相同
dm.create_table(table_type, table_name, columns)
# 此处将表头按照顺序排列。如果header_dict和sheet_data不匹配的话可能会出问题
dm.insert_data(table_name, columns, sheet_data)
ex.close()
self.progressSignal.emit(len(sheet_info), "导入完成")
except Exception as err:
logging.exception(err)
self.errorSignal.emit(err)
def generateCmsTableWork(self):
"""子线程函数：生成提成表单并写入数据库"""
try:
dm = DataManager()
src_dict, src_data = dm.get_table(
self.dataSourceComboBox.currentText())
rul_dict, rul_data = dm.get_table(self.ruleComboBox.currentText())
calc_ratio = CalcRatio(rul_dict, rul_data)
clt_dict, clt_data = dm.get_table(
self.clientComboBox.currentText())
client_dict = {} # 映射关系：客户编号->该客户对应行
for row in clt_data:
client_dict[row[clt_dict['客户编号']]] = row
sht2_head, sht2 = dm.get_table(self.priceComboBox.currentText())
price = []
for row in sht2:
price.append([
row[sht2_head['编号']], row[sht2_head['指导单价(未税)\n元/KG']],
row[sht2_head['备注']], row[sht2_head['出货开始时间']],
row[sht2_head['出货结束时间']]
])
sht4_head, sht4 = dm.get_table(self.managerComboBox.currentText())
slr_dict, slr_data = dm.get_table(
self.aftersalesComboBox.currentText())
places = [] # 售后员表中的地点名
for row in slr_data:
if row[1] != 'None':
places.append([row[1], row[5], row[6]])
self.genProgressBar.setValue(10)
# 每次增加的进度=剩余进度(90)/每个表的阶段数(3)/要生成的表数
piece = 30 / len([
x
for x in [self.cmsLineEdit.text(),
self.asCmsLineEdit.text()] if x != ''
])
if self.cmsLineEdit.text() != '':
bs = Bonus(price)
h1, r1, r2 = bs.calc_commission(src_dict, src_data, clt_dict,
client_dict, rul_dict,
rul_data, places, sht4)
self.genProgressBar.setValue(self.genProgressBar.value() +
piece)
# 下面的h1应该是与ExcelCheck.headers["业务员提成明细"]完全相同的
dm.create_table("业务员提成明细", self.cmsLineEdit.text(), h1)
self.genProgressBar.setValue(self.genProgressBar.value() +
piece)
dm.insert_data(self.cmsLineEdit.text(), h1, r1)
self.genProgressBar.setValue(self.genProgressBar.value() +
piece)
self.generateCmsSignal.emit(self.cmsLineEdit.text())
self.cmsLineEdit.setText("") # 清空表名
if self.asCmsLineEdit.text() != '':
after_sales = AfterSales(slr_dict, slr_data)
as_header, as_content = after_sales.calc_commission(
src_dict, src_data, clt_dict, client_dict, calc_ratio)
self.genProgressBar.setValue(self.genProgressBar.value() +
piece)
# 下面的as_header应该是与ExcelCheck.headers["售后员提成明细"]完全相同的
dm.create_table("售后员提成明细", self.asCmsLineEdit.text(),
as_header)
self.genProgressBar.setValue(self.genProgressBar.value() +
piece)
dm.insert_data(self.asCmsLineEdit.text(), as_header,
as_content)
self.genProgressBar.setValue(self.genProgressBar.value() +
piece)
self.generateCmsSignal.emit(self.asCmsLineEdit.text())
self.asCmsLineEdit.setText("") # 清空表名
self.cmsTableGenPushButton.setEnabled(True)
self.tableManagePushButton.setEnabled(True)
self.tableViewPushButton.setEnabled(True)
except Exception as e:
logging.exception(e)
QMessageBox.warning(self, "生成出错", str(e))
