python实战之实现excel读取、统计、写入的示例讲解

#version_1import osimport numpy as npimport pandas as pdos.chdir('C://Users//dell//Desktop//0711任务')print(os.getcwd())data = pd.read_excel('for_python.xlsx','Sheet2')return_set = set(data['回执名单'])demand_set = set(data['理事名单'])answer_list = []unanswer_list = []for each in demand_set: if each in return_set: answer_list.append(each) else: unanswer_list.append(each)notattend_set = set(data['回执名单'][-15:])nt = []for each in notattend_set: if each in answer_list: nt.append(each)def disp(ll, cap, num = True): print(cap) if num: for i, each in enumerate(ll):  print(i+1,each) else: for each in enumerate(ll):  print(each)disp(answer_list,'/n理事回执名单')disp(unanswer_list,'/n理事未回执名单')disp(nt,'/n理事回执说不参加名单')

#version_2import osimport numpy as npimport pandas as pdos.chdir('C://Users//dell//Desktop//0711任务')print(os.getcwd())data = pd.read_excel('for_python.xlsx','Sheet2')return_set = set(data['回执名单'])demand_set = set(data['理事名单'])answer_set = set([]) #理事回执名单unanswer_set = set([]) #理事未回执名单for each in demand_set: if each in return_set: answer_set.add(each) else: unanswer_set.add(each)notattend_set = set(data['回执名单'][-17:])nt = set([]) #理事回执说不参加名单for each in notattend_set: if each in answer_set: nt.add(each)ans_att_set = answer_set - nt #理事回执参加名单def disp(ss, cap, num = False): print(cap) if num: for i, each in enumerate(ss):  print(i+1,each) else: for each in ss:  print(each)#disp(answer_set,'/n理事回执名单')disp(ans_att_set,'/n理事回执说参加名单')disp(nt,'/n理事回执说不参加名单')disp(unanswer_set,'/n理事未回执名单')print(len(ans_att_set),len(nt),len(unanswer_set))

import osimport numpy as npimport pandas as pdos.chdir('C://Users//dell//Desktop')print('work_directory: ', os.getcwd())data = pd.read_excel('理事与会员名单.xlsx','理事与会员名单')#1.载入excel，得到三个名单ans_attend_set = set(data['回执参加']) #回执参会名单N = len(ans_attend_set)ans_notatt_idx = [i for i in range(N) if type(data['回执不参加'][i]) == np.float][0]ans_notatt_set = set(data['回执不参加'][:ans_notatt_idx])#回执不参会名单concil_idx = [i for i in range(N) if type(data['理事名单'][i]) == np.float][0]concil_set = set(data['理事名单'][:concil_idx])  #理事名单#2.统计理事参会情况concil_attend_set = set([]) #理事回执参会名单concil_notatt_set = set([]) #理事回执不参会名单concil_notans_set = set([]) #理事未回执名单for each in concil_set: if each in ans_attend_set: concil_attend_set.add(each) elif each in ans_notatt_set: concil_notatt_set.add(each) else: concil_notans_set.add(each)#3. 显示结果def disp(ss, cap, num = True): #ss: 名单集合 #cap: 开头描述 print(cap,'({})'.format(len(ss))) for i in range(np.ceil(len(ss)/5).astype(int)): pre = i * 5 nex = (i+1) * 5 #调整显示格式 dd = '' for each in list(ss)[pre:nex]:  if len(each) == 2:  dd = dd + ' ' + each  elif len(each) == 3:  dd = dd + ' ' + each  else:  dd = dd + '' + each print('{:3.0f} -{:3.0f} {}'.format(i*5+1,(i+1)*5,dd))disp(concil_attend_set,'/n参会理事')disp(concil_notatt_set,'/n不参会理事')disp(concil_notans_set,'/n未回执理事')#4. 将理事参会情况，写入exceldf = pd.DataFrame(list(concil_attend_set),columns = ['参会理事'])df['']=pd.DataFrame([''])df['序号1'] = pd.DataFrame(np.arange(len(concil_notatt_set))+1)df['不参会理事'] = pd.DataFrame(list(concil_notatt_set))df['_']=pd.DataFrame([''])df['序号2'] = pd.DataFrame(np.arange(len(concil_notans_set))+1)df['未回执理事'] = pd.DataFrame(list(concil_notans_set))df.index = df.index + 1df.to_excel('理事和会员回执统计.xlsx', sheet_name='理事回执统计')print('/n/n写入excel成功~~')

import osimport numpy as npimport pandas as pdos.chdir('C://Users//dell//Desktop')print('work_directory: ', os.getcwd())data = pd.read_excel('理事与会员名单.xlsx','理事与会员名单')#1.载入excel，得到三个名单ans_attend_set = set(data['回执参加']) #回执参会名单N = len(ans_attend_set)ans_notatt_idx = [i for i in range(N) if type(data['回执不参加'][i]) == np.float][0]ans_notatt_set = set(data['回执不参加'][:ans_notatt_idx])#回执不参会名单mem_idx = [i for i in range(N) if type(data['被推荐人'][i]) == np.float][0]mem_set = set(data['被推荐人'][:mem_idx])  #被推荐为会员代表名单#2.统计会员参会情况mem_attend_set = set([]) #回执参会会员mem_notatt_set = set([]) #回执不参会会员mem_notans_set = set([]) #未回执会员for each in mem_set: if each in ans_attend_set: mem_attend_set.add(each) elif each in ans_notatt_set: mem_notatt_set.add(each) else: mem_notans_set.add(each)#3. 显示结果def disp(ss, cap, num = True): #ss: 名单集合 #cap: 开头描述 print(cap,'({})'.format(len(ss))) for i in range(np.ceil(len(ss)/5).astype(int)): pre = i * 5 nex = (i+1) * 5 #调整显示格式 dd = '' for each in list(ss)[pre:nex]:  if len(each) == 2:  dd = dd + ' ' + each  elif len(each) == 3:  dd = dd + ' ' + each  else:  dd = dd + '' + each print('{:3.0f} -{:3.0f} {}'.format(i*5+1,(i+1)*5,dd))disp(mem_attend_set,'/n参会会员')disp(mem_notatt_set,'/n不参会会员')disp(mem_notans_set,'/n未回执会员')#4. 将会员参会情况，写入excelif len(mem_attend_set) > len(mem_notans_set): print('#1') L = len(mem_attend_set) mem_notans_list = list(mem_notans_set) mem_notans_list.extend([''] * (L - len(mem_notans_set))) mem_attend_list = list(mem_attend_set)else: print('#2') L = len(mem_notans_set) mem_attend_list = list(mem_attend_set) mem_attend_list.extend([''] * (L - len(mem_attend_set))) mem_notans_list = list(mem_notans_set) df = pd.DataFrame(mem_attend_list,columns = ['参会会员'])df['']=pd.DataFrame([''])if len(mem_notatt_set) == 0: df['序号1'] = np.NaN df['不参会会员'] = np.NaNelse: df['序号1'] = pd.DataFrame(np.arange(len(mem_notatt_set))+1) df['不参会会员'] = pd.DataFrame(list(mem_notatt_set))df['_']=pd.DataFrame([''])df['序号2'] = pd.DataFrame(np.arange(len(mem_notans_set))+1)df['未回执会员'] = pd.DataFrame(mem_notans_list)df.index = df.index + 1df0 = pd.read_excel('理事和会员回执统计.xlsx',sheet_name='理事回执统计')writer = pd.ExcelWriter('理事和会员回执统计.xlsx')df0.to_excel(writer, sheet_name='理事回执统计')df.to_excel(writer, sheet_name='会员回执统计')writer.save()print('/n/n写入excel成功~~')

import osimport numpy as npimport pandas as pdos.chdir('C://Users//dell//Desktop')print('work_directory: ', os.getcwd())loadfile_sheet = ['理事与会员名单.xlsx','理事与会员名单']columns = ['回执参加','回执不参加','理事','会员']savefile_sheet = ['理事和会员回执统计.xlsx','理事回执统计','会员回执统计']display = [1,1]def main(loadfile_sheet,columns,savefile_sheet,display): #1. 载入excel，得到名单 data = pd.read_excel(loadfile_sheet[0],loadfile_sheet[1]) def first_nan_index(pd): for i, each in enumerate(pd):  if type(each) == np.float:  return i return i idx = first_nan_index(data[columns[0]]) ans_attend_set = set(data[columns[0]][:idx])#回执参会名单 idx = first_nan_index(data[columns[1]]) ans_notatt_set = set(data[columns[1]][:idx])#回执不参会名单 idx = first_nan_index(data[columns[2]]) concil_set = set(data[columns[2]][:idx])#理事名单 idx = first_nan_index(data[columns[3]]) mem_set = set(data[columns[3]][:idx])#会员名单 #2. 统计参会情况 concil_attend_set = set([]) #回执参会理事 concil_notatt_set = set([]) #回执不参会理事 concil_notans_set = set([]) #未回执理事 for each in concil_set: if each in ans_attend_set:  concil_attend_set.add(each) elif each in ans_notatt_set:  concil_notatt_set.add(each) else:  concil_notans_set.add(each) mem_attend_set = set([]) #回执参会会员 mem_notatt_set = set([]) #回执不参会会员 mem_notans_set = set([]) #未回执会员 for each in mem_set: if each in ans_attend_set:  mem_attend_set.add(each) elif each in ans_notatt_set:  mem_notatt_set.add(each) else:  mem_notans_set.add(each) #3. 是否显示中间结果  def disp(ss, cap, num = True): #ss: 名单集合 #cap: 开头描述 print(cap,'({})'.format(len(ss))) for i in range(np.ceil(len(ss)/5).astype(int)):  pre = i * 5  nex = (i+1) * 5  #调整显示格式  dd = ''  for each in list(ss)[pre:nex]:  if len(each) == 2:   dd = dd + ' ' + each  elif len(each) == 3:   dd = dd + ' ' + each  else:   dd = dd + '' + each  print('{:3.0f} -{:3.0f} {}'.format(i*5+1,(i+1)*5,dd)) if display[0]: disp(concil_attend_set,'/n参会理事') disp(concil_notatt_set,'/n不参会理事') disp(concil_notans_set,'/n未回执理事') if display[1]: disp(mem_attend_set,'/n参会会员') disp(mem_notatt_set,'/n不参会会员') disp(mem_notans_set,'/n未回执会员') #4. 写入excel def trans_pd(df,ss,cap,i=1): if len(ss) == 0:  df['序号{}'.format(i)] = np.NaN  df[cap] = np.NaN else:  df['序号{}'.format(i)] = pd.DataFrame(np.arange(len(ss))+1)  df[cap] = pd.DataFrame(list(ss)) df['_'*i]=pd.DataFrame(['']) return df def set2list(mem_attend_set,mem_notans_set): if len(mem_attend_set) > len(mem_notans_set):  L = len(mem_attend_set)  mem_notans_list = list(mem_notans_set)  mem_notans_list.extend([''] * (L - len(mem_notans_set)))  mem_attend_list = list(mem_attend_set) else:  L = len(mem_notans_set)  mem_attend_list = list(mem_attend_set)  mem_attend_list.extend([''] * (L - len(mem_attend_set)))  mem_notans_list = list(mem_notans_set) return mem_attend_list,mem_notans_list mem_attend_list, mem_notans_list = set2list(mem_attend_set, mem_notans_set)  df1 = pd.DataFrame(mem_attend_list,columns = ['参会会员']) df1['']=pd.DataFrame(['']) df1 = trans_pd(df1,mem_notatt_set,'不参会会员') df1 = trans_pd(df1,mem_notans_set,'未回执会员',2) df1.index = df1.index + 1 concil_attend_list, concil_notans_list = set2list(concil_attend_set, concil_notans_set) df2 = pd.DataFrame(concil_attend_list,columns = ['参会理事']) df2['']=pd.DataFrame(['']) df2 = trans_pd(df2,concil_notatt_set,'不参会理事') df2 = trans_pd(df2,concil_notans_list,'未回执理事',2) df2.index = df2.index + 1 writer = pd.ExcelWriter(savefile_sheet[0]) df2.to_excel(writer, sheet_name=savefile_sheet[1]) df1.to_excel(writer, sheet_name=savefile_sheet[2]) writer.save() print('/n/n写入excel成功~~')if __name__ == '__main__': main(loadfile_sheet,columns,savefile_sheet,display)

import osimport numpy as npimport pandas as pdos.chdir('C://Users//dell//Desktop')print('work_directory: ', os.getcwd())loadfile_sheet = ['理事与会员名单.xlsx','理事与会员名单']common_columns = ['回执参加','回执不参加']concerned_columns = ['理事','会员']disp_columns = ['参会','不参会','未回执']savefile_sheet = ['理事和会员回执统计.xlsx','理事回执统计','会员回执统计']def disp(ss, cap, num = True): #ss: 名单集合 #cap: 开头描述 print(cap,'({})'.format(len(ss))) for i in range(np.ceil(len(ss)/5).astype(int)): pre = i * 5 nex = (i+1) * 5 #调整显示格式 dd = '' for each in list(ss)[pre:nex]:  if len(each) == 2:  dd = dd + ' ' + each  elif len(each) == 3:  dd = dd + ' ' + each  else:  dd = dd + '' + each print('{:3.0f} -{:3.0f} {}'.format(i*5+1,(i+1)*5,dd))def trans_pd(df,ss,cap,i=1): df['_'*i]=pd.DataFrame(['']) if len(ss) == 0: df['序号{}'.format(i)] = np.NaN df[cap] = np.NaN else: df['序号{}'.format(i)] = pd.DataFrame(np.arange(len(ss))+1) df[cap] = pd.DataFrame(list(ss))  return dfdef set2list(ss1,ss2): if len(ss1) > len(ss2): L = len(ss1) ss2_list = list(ss2) ss2_list.extend([''] * (L - len(ss2))) ss1_list = list(ss1) else: L = len(ss2) ss1_list = list(ss1) ss1_list.extend([''] * (L - len(ss1))) ss2_list = list(ss2) return ss1_list,ss2_list def get_df(loadfile_sheet,common_columns,concerned_column,disp_columns, display = True): #1. 载入excel data = pd.read_excel(loadfile_sheet[0],loadfile_sheet[1]) common_set1 = set(data[common_columns[0]]) common_set1.discard(np.NaN) common_set2 = set(data[common_columns[1]]) common_set2.discard(np.NaN) concerned_set = set(data[concerned_column]) concerned_set.discard(np.NaN) #2. 统计 concerned_in_set_1 = set([]) concerned_in_set_2 = set([]) concerned_in_no_set = set([]) for each in concerned_set: if each in common_set1:  concerned_in_set_1.add(each) elif each in common_set2:  concerned_in_set_2.add(each) else:  concerned_in_no_set.add(each) #3. 显示 if display: disp(concerned_in_set_1,'/n'+disp_columns[0]+concerned_column) disp(concerned_in_set_2,'/n'+disp_columns[1]+concerned_column) disp(concerned_in_no_set,'/n'+disp_columns[2]+concerned_column) #4. 返回DataFrame concerned_in_set_1_list, concerned_in_set_2_list = set2list(concerned_in_set_1, concerned_in_no_set)  df = pd.DataFrame(concerned_in_set_1_list,columns = [disp_columns[0]]) df = trans_pd(df,concerned_in_set_2,disp_columns[1]) df = trans_pd(df,concerned_in_no_set,disp_columns[2],2) df.index = df.index + 1 return dfdef save2excel(df, concerned_column, savefile_sheet): L = len(savefile_sheet) - 1 idx = 0 for i in np.arange(L)+1: if concerned_column in savefile_sheet[i]:  idx = i  break if idx != 0:   names = locals() for i in np.arange(L)+1:  if i != idx:  names['df%s' % i] = pd.read_excel(savefile_sheet[0], sheet_name=savefile_sheet[i]) writer = pd.ExcelWriter(savefile_sheet[0]) for i in np.arange(L)+1:  if i != idx:  names['df%s' % i].to_excel(writer, sheet_name=savefile_sheet[i])  else:  df.to_excel(writer, sheet_name=savefile_sheet[i]) writer.save() else:   names = locals() for i in np.arange(L)+1:  names['df%s' % i] = pd.read_excel(savefile_sheet[0], sheet_name=savefile_sheet[i]) writer = pd.ExcelWriter(savefile_sheet[0]) for i in np.arange(L)+1:  names['df%s' % i].to_excel(writer, sheet_name=savefile_sheet[i]) df.to_excel(writer, sheet_name=concerned_column) writer.save() print('writing success')if __name__ == '__main__': for concerned_column in concerned_columns: df = get_df(loadfile_sheet,common_columns,   concerned_column,disp_columns, display = True) save2excel(df, concerned_column, savefile_sheet)

import osimport numpy as npimport pandas as pdos.chdir('C://Users//dell//Desktop')print('work_directory: ', os.getcwd())loadfile_sheet = ['理事与会员名单.xlsx','理事与会员名单']common_columns = ['回执参加','回执不参加']concerned_columns = ['理事','会员']disp_columns = ['参会','不参会','未回执']savefile_sheet = ['理事和会员回执统计.xlsx','理事回执统计','会员回执统计']def disp(ss, cap, num = True): #功能：显示名单 #ss : 名单集合 #cap ：开头描述 print(cap,'({})'.format(len(ss))) for i in range(np.ceil(len(ss)/5).astype(int)): pre = i * 5 nex = (i+1) * 5 #调整显示格式 dd = '' for each in list(ss)[pre:nex]:  if len(each) == 2:  dd = dd + ' ' + each  elif len(each) == 3:  dd = dd + ' ' + each  else:  dd = dd + '' + each print('{:3.0f} -{:3.0f} {}'.format(i*5+1,(i+1)*5,dd))def trans_pd(df,ll,cap,i=1): #功能：生成三列--空列、序号列、数据列 #df : DataFrame结构 #ll : 列表 #cap : 显示的列名 #i : 控制空列的名字 df['_'*i]=pd.DataFrame(['']) if len(set(ll)) == 1: df['序号{}'.format(i)] = np.NaN df[cap] = np.NaN else: df['序号{}'.format(i)] = pd.DataFrame(np.arange(len(set(ll))-1)+1) df[cap] = pd.DataFrame(ll)  return dfdef prep(ss, N): #功能：预处理，生成列表，并补齐到长度N #ss : 集体 #N ：长度 ll = list(ss) L = len(ll) ll.extend([np.NaN] * (N-L)) return lldef get_df(loadfile_sheet,common_columns,concerned_column,disp_columns, display = True): #1. 载入excel data = pd.read_excel(loadfile_sheet[0],loadfile_sheet[1])  common_set1 = set(data[common_columns[0]]) common_set2 = set(data[common_columns[1]])  concerned_set = set(data[concerned_column]) common_set1.discard(np.NaN) common_set2.discard(np.NaN) concerned_set.discard(np.NaN) #2. 统计 concerned_in_set_1 = set([]) concerned_in_set_2 = set([]) concerned_in_no_set = set([]) for each in concerned_set: if each in common_set1:  concerned_in_set_1.add(each) elif each in common_set2:  concerned_in_set_2.add(each) else:  concerned_in_no_set.add(each) #3. 显示 if display: disp(concerned_in_set_1,'/n'+disp_columns[0]+concerned_column) disp(concerned_in_set_2,'/n'+disp_columns[1]+concerned_column) disp(concerned_in_no_set,'/n'+disp_columns[2]+concerned_column) #4. 返回DataFrame N = np.max([len(concerned_in_set_1),len(concerned_in_set_2),len(concerned_in_no_set)]) concerned_in_set_1_list = prep(concerned_in_set_1,N) concerned_in_set_2_list = prep(concerned_in_set_2,N) concerned_in_no_list = prep(concerned_in_no_set,N) df = pd.DataFrame(concerned_in_set_1_list,columns = [disp_columns[0]]) df = trans_pd(df,concerned_in_set_2_list,disp_columns[1]) df = trans_pd(df,concerned_in_no_list,disp_columns[2],2) df.index = df.index + 1 return dfdef save2excel(df, concerned_column, savefile_sheet): L = len(savefile_sheet) - 1 idx = 0 for i in np.arange(L)+1: if concerned_column in savefile_sheet[i]:  idx = i  break if idx != 0: #如果有对应sheet   names = locals() for i in np.arange(L)+1:  if i != idx:  names['df%s' % i] = pd.read_excel(savefile_sheet[0], sheet_name=savefile_sheet[i]) writer = pd.ExcelWriter(savefile_sheet[0]) for i in np.arange(L)+1:  if i != idx:  names['df%s' % i].to_excel(writer, sheet_name=savefile_sheet[i])  else:  df.to_excel(writer, sheet_name=savefile_sheet[i]) writer.save() else: #如果没有对应sheet，创建一个新sheet   names = locals() for i in np.arange(L)+1:  names['df%s' % i] = pd.read_excel(savefile_sheet[0], sheet_name=savefile_sheet[i]) writer = pd.ExcelWriter(savefile_sheet[0]) for i in np.arange(L)+1:  names['df%s' % i].to_excel(writer, sheet_name=savefile_sheet[i]) df.to_excel(writer, sheet_name=concerned_column) writer.save() print('writing success')if __name__ == '__main__': for concerned_column in concerned_columns: df = get_df(loadfile_sheet,common_columns,   concerned_column,disp_columns, display = True) save2excel(df, concerned_column, savefile_sheet)