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))