python实战之实现excel读取、统计、写入的示例讲解
背景
图像领域内的一个国内会议快要召开了，要发各种邀请邮件，之后要录入、统计邮件回复（参会还是不参会等）。如此重要的任务，老师就托付给我了。ps:统计回复邮件的时候，能知道谁参会或谁不参会。
而我主要的任务，除了录入邮件回复，就是统计理事和普通会员的参会情况了（参会的、不参会的、没回复的）。录入邮件回复信息没办法只能人工操作，但如果统计也要人工的话，那工作量就太大了（比如在上百人的列表中搜索另外上百人在不在此列表中！！），于是就想到了用python来帮忙，花两天时间不断修改，写了6个版本。。。
摘要
version_1基本实现了excel读取、统计、显示功能，但问题也有不少，像显示出来后还要自已复制、粘贴到excel表，而且set中还有nan这样的bug。
version_2相比较version_1而言，此版本用set代替list，可以自动去重。
version_3解决了set中出现nan的bug，而且还加入的excel写入的功能，但一次只能写入一张表，所以要运行两次才能写入两张表（sheet）。
version_4的改进在于将version_3中写入两张表格的操作，集成在一个程序里，只需要运行一次便可写入两张表，但也总是会写入两张表，万一你只想写入一张表呢？？
version_5相对之前版本的最大改进在于将程序模块化，更具可读性了;对修复set中出现nan的方法也进行了改进和简化;而且可以自由控制写入多少张表了。
version_final相比较version_5，修复了一个bug，之前需要先验知识，现在更通用一点（prep函数取代了set2list函数）。
version_1
基本实现了excel读取、统计、显示功能，但问题也有不少，像显示出来后还要自已复制、粘贴到excel表，而且set中还有nan这样的值。
#version_1
importos
importnumpyasnp
importpandasaspd
os.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=[]
foreachindemand_set:
ifeachinreturn_set:
answer_list.append(each)
else:
unanswer_list.append(each)
notattend_set=set(data['回执名单'][-15:])
nt=[]
foreachinnotattend_set:
ifeachinanswer_list:
nt.append(each)
defdisp(ll,cap,num=True):
print(cap)
ifnum:
fori,eachinenumerate(ll):
print(i+1,each)
else:
foreachinenumerate(ll):
print(each)
disp(answer_list,'\n理事回执名单')
disp(unanswer_list,'\n理事未回执名单')
disp(nt,'\n理事回执说不参加名单')
version_2
相比较上一个版本，此版本用set代替list，可以自动去重。
#version_2
importos
importnumpyasnp
importpandasaspd
os.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([])#理事未回执名单
foreachindemand_set:
ifeachinreturn_set:
answer_set.add(each)
else:
unanswer_set.add(each)
notattend_set=set(data['回执名单'][-17:])
nt=set([])#理事回执说不参加名单
foreachinnotattend_set:
ifeachinanswer_set:
nt.add(each)
ans_att_set=answer_set-nt#理事回执参加名单
defdisp(ss,cap,num=False):
print(cap)
ifnum:
fori,eachinenumerate(ss):
print(i+1,each)
else:
foreachinss:
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))
version_3
此版本解决了set中出现nan的bug，而且还加入的excel写入的功能，但一次只能写入一张表，所以要运行两次才能写入两张表（sheet）。
step_1
importos
importnumpyasnp
importpandasaspd
os.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=[iforiinrange(N)iftype(data['回执不参加'][i])==np.float][0]
ans_notatt_set=set(data['回执不参加'][:ans_notatt_idx])#回执不参会名单
concil_idx=[iforiinrange(N)iftype(data['理事名单'][i])==np.float][0]
concil_set=set(data['理事名单'][:concil_idx])#理事名单
#2.统计理事参会情况
concil_attend_set=set([])#理事回执参会名单
concil_notatt_set=set([])#理事回执不参会名单
concil_notans_set=set([])#理事未回执名单
foreachinconcil_set:
ifeachinans_attend_set:
concil_attend_set.add(each)
elifeachinans_notatt_set:
concil_notatt_set.add(each)
else:
concil_notans_set.add(each)
#3.显示结果
defdisp(ss,cap,num=True):
#ss:名单集合
#cap:开头描述
print(cap,'({})'.format(len(ss)))
foriinrange(np.ceil(len(ss)/5).astype(int)):
pre=i*5
nex=(i+1)*5
#调整显示格式
dd=''
foreachinlist(ss)[pre:nex]:
iflen(each)==2:
dd=dd+''+each
eliflen(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.将理事参会情况，写入excel
df=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+1
df.to_excel('理事和会员回执统计.xlsx',sheet_name='理事回执统计')
print('\n\n写入excel成功~~')
step_2
importos
importnumpyasnp
importpandasaspd
os.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=[iforiinrange(N)iftype(data['回执不参加'][i])==np.float][0]
ans_notatt_set=set(data['回执不参加'][:ans_notatt_idx])#回执不参会名单
mem_idx=[iforiinrange(N)iftype(data['被推荐人'][i])==np.float][0]
mem_set=set(data['被推荐人'][:mem_idx])#被推荐为会员代表名单
#2.统计会员参会情况
mem_attend_set=set([])#回执参会会员
mem_notatt_set=set([])#回执不参会会员
mem_notans_set=set([])#未回执会员
foreachinmem_set:
ifeachinans_attend_set:
mem_attend_set.add(each)
elifeachinans_notatt_set:
mem_notatt_set.add(each)
else:
mem_notans_set.add(each)
#3.显示结果
defdisp(ss,cap,num=True):
#ss:名单集合
#cap:开头描述
print(cap,'({})'.format(len(ss)))
foriinrange(np.ceil(len(ss)/5).astype(int)):
pre=i*5
nex=(i+1)*5
#调整显示格式
dd=''
foreachinlist(ss)[pre:nex]:
iflen(each)==2:
dd=dd+''+each
eliflen(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.将会员参会情况，写入excel
iflen(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([''])
iflen(mem_notatt_set)==0:
df['序号1']=np.NaN
df['不参会会员']=np.NaN
else:
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+1
df0=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成功~~')
version_4
version_4的改进在于将version_3中写入两张表格的操作，集成在一个程序里，只需要运行一次便可写入两张表，也总是会写入两张表。问题是要是你只想写入一张表呢？？
importos
importnumpyasnp
importpandasaspd
os.chdir('C:\\Users\\dell\\Desktop')
print('work_directory:',os.getcwd())
loadfile_sheet=['理事与会员名单.xlsx','理事与会员名单']
columns=['回执参加','回执不参加','理事','会员']
savefile_sheet=['理事和会员回执统计.xlsx','理事回执统计','会员回执统计']
display=[1,1]
defmain(loadfile_sheet,columns,savefile_sheet,display):
#1.载入excel，得到名单
data=pd.read_excel(loadfile_sheet[0],loadfile_sheet[1])
deffirst_nan_index(pd):
fori,eachinenumerate(pd):
iftype(each)==np.float:
returni
returni
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([])#未回执理事
foreachinconcil_set:
ifeachinans_attend_set:
concil_attend_set.add(each)
elifeachinans_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([])#未回执会员
foreachinmem_set:
ifeachinans_attend_set:
mem_attend_set.add(each)
elifeachinans_notatt_set:
mem_notatt_set.add(each)
else:
mem_notans_set.add(each)
#3.是否显示中间结果
defdisp(ss,cap,num=True):
#ss:名单集合
#cap:开头描述
print(cap,'({})'.format(len(ss)))
foriinrange(np.ceil(len(ss)/5).astype(int)):
pre=i*5
nex=(i+1)*5
#调整显示格式
dd=''
foreachinlist(ss)[pre:nex]:
iflen(each)==2:
dd=dd+''+each
eliflen(each)==3:
dd=dd+''+each
else:
dd=dd+''+each
print('{:3.0f}-{:3.0f}{}'.format(i*5+1,(i+1)*5,dd))
ifdisplay[0]:
disp(concil_attend_set,'\n参会理事')
disp(concil_notatt_set,'\n不参会理事')
disp(concil_notans_set,'\n未回执理事')
ifdisplay[1]:
disp(mem_attend_set,'\n参会会员')
disp(mem_notatt_set,'\n不参会会员')
disp(mem_notans_set,'\n未回执会员')
#4.写入excel
deftrans_pd(df,ss,cap,i=1):
iflen(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([''])
returndf
defset2list(mem_attend_set,mem_notans_set):
iflen(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)
returnmem_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)
version_5
version_5对修复set中出现nan的方法进行了改进和简化;而且将程序模块化，更具可读性;可以自由控制写入多少张表了。
importos
importnumpyasnp
importpandasaspd
os.chdir('C:\\Users\\dell\\Desktop')
print('work_directory:',os.getcwd())
loadfile_sheet=['理事与会员名单.xlsx','理事与会员名单']
common_columns=['回执参加','回执不参加']
concerned_columns=['理事','会员']
disp_columns=['参会','不参会','未回执']
savefile_sheet=['理事和会员回执统计.xlsx','理事回执统计','会员回执统计']
defdisp(ss,cap,num=True):
#ss:名单集合
#cap:开头描述
print(cap,'({})'.format(len(ss)))
foriinrange(np.ceil(len(ss)/5).astype(int)):
pre=i*5
nex=(i+1)*5
#调整显示格式
dd=''
foreachinlist(ss)[pre:nex]:
iflen(each)==2:
dd=dd+''+each
eliflen(each)==3:
dd=dd+''+each
else:
dd=dd+''+each
print('{:3.0f}-{:3.0f}{}'.format(i*5+1,(i+1)*5,dd))
deftrans_pd(df,ss,cap,i=1):
df['_'*i]=pd.DataFrame([''])
iflen(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))
returndf
defset2list(ss1,ss2):
iflen(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)
returnss1_list,ss2_list
defget_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([])
foreachinconcerned_set:
ifeachincommon_set1:
concerned_in_set_1.add(each)
elifeachincommon_set2:
concerned_in_set_2.add(each)
else:
concerned_in_no_set.add(each)
#3.显示
ifdisplay:
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
returndf
defsave2excel(df,concerned_column,savefile_sheet):
L=len(savefile_sheet)-1
idx=0
foriinnp.arange(L)+1:
ifconcerned_columninsavefile_sheet[i]:
idx=i
break
ifidx!=0:
names=locals()
foriinnp.arange(L)+1:
ifi!=idx:
names['df%s'%i]=pd.read_excel(savefile_sheet[0],sheet_name=savefile_sheet[i])
writer=pd.ExcelWriter(savefile_sheet[0])
foriinnp.arange(L)+1:
ifi!=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()
foriinnp.arange(L)+1:
names['df%s'%i]=pd.read_excel(savefile_sheet[0],sheet_name=savefile_sheet[i])
writer=pd.ExcelWriter(savefile_sheet[0])
foriinnp.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('writingsuccess')
if__name__=='__main__':
forconcerned_columninconcerned_columns:
df=get_df(loadfile_sheet,common_columns,
concerned_column,disp_columns,display=True)
save2excel(df,concerned_column,savefile_sheet)
version_final
相比较version_5，修复了一个bug，之前需要先验知识，现在更通用一点（prep函数取代了set2list函数）。
importos
importnumpyasnp
importpandasaspd
os.chdir('C:\\Users\\dell\\Desktop')
print('work_directory:',os.getcwd())
loadfile_sheet=['理事与会员名单.xlsx','理事与会员名单']
common_columns=['回执参加','回执不参加']
concerned_columns=['理事','会员']
disp_columns=['参会','不参会','未回执']
savefile_sheet=['理事和会员回执统计.xlsx','理事回执统计','会员回执统计']
defdisp(ss,cap,num=True):
#功能：显示名单
#ss:名单集合
#cap：开头描述
print(cap,'({})'.format(len(ss)))
foriinrange(np.ceil(len(ss)/5).astype(int)):
pre=i*5
nex=(i+1)*5
#调整显示格式
dd=''
foreachinlist(ss)[pre:nex]:
iflen(each)==2:
dd=dd+''+each
eliflen(each)==3:
dd=dd+''+each
else:
dd=dd+''+each
print('{:3.0f}-{:3.0f}{}'.format(i*5+1,(i+1)*5,dd))
deftrans_pd(df,ll,cap,i=1):
#功能：生成三列--空列、序号列、数据列
#df:DataFrame结构
#ll:列表
#cap:显示的列名
#i:控制空列的名字
df['_'*i]=pd.DataFrame([''])
iflen(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)
returndf
defprep(ss,N):
#功能：预处理，生成列表，并补齐到长度N
#ss:集体
#N：长度
ll=list(ss)
L=len(ll)
ll.extend([np.NaN]*(N-L))
returnll
defget_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([])
foreachinconcerned_set:
ifeachincommon_set1:
concerned_in_set_1.add(each)
elifeachincommon_set2:
concerned_in_set_2.add(each)
else:
concerned_in_no_set.add(each)
#3.显示
ifdisplay:
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
returndf
defsave2excel(df,concerned_column,savefile_sheet):
L=len(savefile_sheet)-1
idx=0
foriinnp.arange(L)+1:
ifconcerned_columninsavefile_sheet[i]:
idx=i
break
ifidx!=0:#如果有对应sheet
names=locals()
foriinnp.arange(L)+1:
ifi!=idx:
names['df%s'%i]=pd.read_excel(savefile_sheet[0],sheet_name=savefile_sheet[i])
writer=pd.ExcelWriter(savefile_sheet[0])
foriinnp.arange(L)+1:
ifi!=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()
foriinnp.arange(L)+1:
names['df%s'%i]=pd.read_excel(savefile_sheet[0],sheet_name=savefile_sheet[i])
writer=pd.ExcelWriter(savefile_sheet[0])
foriinnp.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('writingsuccess')
if__name__=='__main__':
forconcerned_columninconcerned_columns:
df=get_df(loadfile_sheet,common_columns,
concerned_column,disp_columns,display=True)
save2excel(df,concerned_column,savefile_sheet)
以上这篇python实战之实现excel读取、统计、写入的示例讲解就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持毛票票。