def find1(path1, bianhao): # 动迁系统查询 是否认领完毕 path1动迁照片路径 bianhao系统内总报告编号 df_m = model_excel() mm2 = sql_server("192.168.1.8", "sa", "ldpjwy", "gjgl_xh", "1433") r2 = mm2.select("dbo.cq1", { 'gdbh': 13431, 'zl_ne': '', 'zgjs': '王昱鹏' }, ['bcqrxm']) df11 = [x['bcqrxm'].replace("(亡)", "").replace("(亡)", "") for x in r2] # df1=df_m.readexcel("E:\\21.xlsx",0,None,1,"H") # df1.dropna(axis=0,inplace=True) # df1[7]=df1[7].apply(lambda x: x.replace("(亡)","").replace("(亡)","")) # df11=df1.iloc[:,0].tolist() ls1 = [] bianli2(path1, ls1) #print(ls1) t = True for name in ls1: for pp in df11: if name.find(pp) > -1: t = False break if t: print(name) else: t = True
def bijiao_excel(x1, x2): r = pd.merge(x1, x2, how='left') r.fillna(value=-99) r.sort_values(by="nn") f_excel = model_excel() n_t = time.strftime("%Y%m%d%H%M%S", time.time()) f_excel.xlwingcreate(r, r"e:\bj_sl" + n_t + r".xlsx")
def model2(self): ff1 = self.readexcel2() ff2 = self.search2(['报告编号', '估价人员']) dftf = pd.merge(ff1, ff2, how='left', left_on=10, right_on='报告编号') dftf['估价人员'].replace("殷?", "殷旸", inplace=True) dfff = dftf.groupby(['估价人员'], as_index=False) ff2 = dfff[12].agg(np.sum) print(ff2) pff = dfff.size() pff = pff.reset_index(name='数量') ff3 = pd.merge(ff2, pff, how="left") print(ff3) xlww = model_excel() xlww.xlwingwirte(dftf[[10, 12, '估价人员']], self.f, '结果', True, 'A1') #width =0.5 #plt.rcParams['font.sans-serif'] = ['SimHei'] xlww.xlwingwirte(ff3, self.f, '结果', True, 'A1')
def model(self): df1 = self.readexcel() if df1.shape[0] > 10: df1.drop(df1[df1[0].map(np.isnan)].index) df2 = self.search(['报告编号', '地址', '客户', '完成日期', '总价', '估价人员']) df2["地址"] = df2["地址"].apply(lambda x: repl(x)) df2["总价"] = df2["总价"] * 10000 df2["完成日期"] = df2["完成日期"].apply(lambda x: x.strftime("%Y%m%d")) df2["估价人员"] = df2["估价人员"].apply(lambda x: x.replace("殷?", "殷旸")) file2 = datetime.now().strftime("%Y-%m-%d,%H-%M-%S") f1 = self.f.rfind(".") #f2=self.f.rfind("\\") st1 = self.f[f1:len(self.f)] #后缀名 #st2=self.f[f2:f1] #文件名 #st3=self.f[0:f2] #文件所在目录 fd2fullname = os.path.join(os.getcwd(), file2 + st1) df2.to_excel(fd2fullname) df2['总价'] = df2['总价'].astype(int) #df3=df1.drop_duplicates(subset=[2],keep='first') #删除重复客户 #df4=df2.drop_duplicates(subset=['客户'],keep='first') #查询到的表删除重复客户 #df4=df4[['报告编号','客户']] #df5=pd.merge(df3,df4,how='left',left_on=2,right_on='客户') df7 = df2[['报告编号', '地址']] df = pd.merge(df1, df7, how='left', left_on=5, right_on='地址') #df6=df5[[0,'报告编号']] #df=pd.merge(df,df6,how='left',on=0,) #df['报告编号_x']=df.apply(lambda x: x['报告编号_x'] if x["报告编号_x"] is not np.nan else x['报告编号_y'],axis=1) dft = pd.merge(df, df2, how='left', left_on='报告编号', right_on="报告编号") dft.drop_duplicates(subset=[0], keep='first', inplace=True) sna = dft['报告编号'].isna().sum() print(sna) xlww = model_excel() xlww.xlwingwirte(dft[['报告编号', '完成日期', '总价', '估价人员']], self.f, None, False, 'K5') return sna else: return -1
def run(self): lists1 = read_excel(self.fp, self.sheetname, self.nc) sflist = [] mul = Pool(6) qq = Manager().Queue() for lsd in lists1: mul.apply_async(runss, (lsd, qq)) mul.close() mul.join() while not qq.empty(): sflist.append(qq.get()) sflist.sort(key=lambda x: x[6]) xlww = model_excel() df = pd.DataFrame( sflist, columns=['售价', '开盘时间', '主力面积', '信息', '信息2', '名称', '序号']) xlww.xlwingwirte(df, self.fp, self.sheetname, True)
def select_mysql_to_excel(database, user, pwd, host, tablename, where, search_list, between): smo = mysql_model(database, user, pwd, host, "gbk") lsr = smo.select(where, tablename, search_list, between, False) df1 = pd.DataFrame(lsr) df1.columns = [ 'id', '报告类型', '项目编号', '房地产座落', '委托单位', '借款方', '不动产权利人', '委托方联系人', '估价时点', '完成日期', '估价目的', '估价设定用途', '建筑面积', '房地产总价', '房地产单价', '估价人员', '协办人员', '开票金额', '开票日期', '到帐日期', '付款单位', '项目来源(总部)', '项目来源(分部)', '项目来源联系人', '项目进度', '项目状态', '备注', '暗号', '委托号', '收据金额', '内评价格' ] df = df1[(df1['报告类型'] == 'X') | (df1['报告类型'] == 'F') | (df1['报告类型'] == 'T') | (df1['报告类型'] == 'Z')] df = df[~df['项目编号'].str.contains('临')] ex = model_excel() n_t = time.strftime("%Y%m%d%H%M%S") df['建筑面积'] = df['建筑面积'].astype("float64") df['房地产总价'] = df['房地产总价'].astype("int64") print(df.info()) ex.xlwingcreate(df, r"e:\工行" + n_t + r".xlsx")
#fname2="E:\\"+str(time.strftime("%H%M%S",time.localtime()))+".xls" #lists1=[["禹洲雍锦府",1]] # print(lists1) # test("澜庭") fname = r"E:\414ww.xlsx" lists1 = read_excel(fname, 'Sheet1', 3) print(lists1) longlis = [] mul = Pool(1) sflist = [] qq = Manager().Queue() for lsd in lists1: mul.apply_async(runss, (lsd, qq)) #sflist.append(searchbegin(lsd)) mul.close() mul.join() while not qq.empty(): sflist.append(qq.get()) sflist.sort(key=lambda x: x[6]) print(sflist) xlww = model_excel() df = pd.DataFrame(sflist, columns=['售价', '开盘时间', '主力面积', '信息', '信息2', '名称', '序号']) #xlww.xlwingwirte(df,fname,'21S1',True,"K2") print(time.strftime("%H:%M:%S", time.localtime())) print((time.time() - t) / 60)