python读写excel实例-python读写Excel表格的实例代码（简单实用）

python读写excel实例_python读写Excel表格的实例代码（简单实⽤）安装两个库：pipinstallxlrd、pipinstallxlwt1.python读excel――xlrd2.python写excel――xlwt1.读excel数据，包括⽇期等数据#coding=utf-8importxlrdimportdatetimefromdatetimeimportdatedefread_excel():#打开⽂件wb=xlrd.open_workbook(r"test.xlsx")#获取所有sheet的名字print(wb.sheet_names())#获取第⼆个sheet的表明sheet2=wb.sheet_names()[1]#sheet1索引从0开始，得到sheet1表的句柄sheet1=wb.sheet_by_index(0)rowNum=sheet1.nrowscolNum=sheet1.ncols#s=sheet1.cell(1,0).value.encode("utf-8")s=sheet1.cell(1,0).value#获取某⼀个位置的数据#1ctype:0empty,1string,2number,3date,4boolean,5errorprint(sheet1.cell(1,2).ctype)print(s)#print(s.decode("utf-8"))#获取整⾏和整列的数据#第⼆⾏数据row2=sheet1.row_values(1)#第⼆列数据cols2=sheet1.col_values(2)#python读取excel中单元格内容为⽇期的⽅式#返回类型有5种foriinrange(rowNum):ifsheet1.cell(i,2).ctype==3:d=xlrd.xldate_as_tuple(sheet1.cell_value(i,2),wb.datemode)print(date(*d[:3]),end="")print("")if__name__=="__main__":read_excel()~运⾏效果2.往excel写⼊数据#coding=utf-8importxlwt#设置表格样式defset_stlye(name,height,bold=False):#初始化样式style=xlwt.XFStyle()#创建字体font=xlwt.Font()font.bold=boldfont.colour_index=4font.height==namestyle.font=fontreturnstyle#写⼊数据defwrite_excel():f=xlwt.Workbook()#创建sheet1sheet1=f.add_sheet(u"sheet1",cell_overwrite_ok=True)row0=[u"业务",u"状态",u"北京",u"上海",u"⼴州",u"深圳",u"状态⼩计",u"合计"]column0=[u"机票",u"船票",u"⽕车票",u"汽车票",u"其他"]status=[u"预定",u"出票",u"退票",u"业务⼩计"]foriinrange(0,len(row0)):sheet1.write(0,i,row0[i],set_stlye("TimeNewRoman",220,True))i,j=1,0whilei<4*len(column0):#控制循环：每次加4#第⼀列sheet1.write_merge(i,i+3,0,0,column0[j],set_stlye("Arial",220,True))#最后⼀列sheet1.write_merge(i,i+3,7,7)i+=4sheet1.write_merge(21,21,0,1,u"合计",set_stlye("TimeNewRoman",220,True))i=0whilei<4*len(column0):#控制外层循环：每次加4forjinrange(0,len(status)):#控制内层循环：设置每⼀⾏内容sheet1.write(i+j+1,1,status[j])i+=4#创建sheet2sheet2=f.add_sheet(u"sheet2",cell_overwrite_ok=True)row0=[u"姓名",u"年龄",u"出⽣⽇期",u"爱好",u"关系"]column0=[u"UZI",u"Faker",u"⼤司马",u"PDD",u"冯提莫"]#⽣成第⼀⾏foriinrange(0,len(row0)):sheet2.write(0,i,row0[i],set_stlye("TimesNewRoman",220,True))#⽣成第⼀列foriinrange(0,len(column0)):sheet2.write(i+1,0,column0[i],set_stlye("TimesNewRoman",220,True))f.save("data.xls")if__name__=="__main__":write_excel()~在data.xls种⽣成了sheet1和sheet2：