python璇诲啓Excel鏈€杩戝皬缂栧湪澶勭悊鍚勭.xlsx琛ㄦ牸鐨勬暟鎹鐞嗗拰璁＄畻銆傜洰鍓嶆搷浣滆〃鐨刾ython妯″潡鏈夊緢澶氾紝鍚勬湁浼樼己鐐广€傝繖绡囨枃绔犱富瑕佽xlwt鍐欑殑锛寈lrt璇荤殑銆倄lwt妯″潡鍙敤浜庤〃鏍煎啓鍏ョ殑绠€鍗曞啓鍏ュ姛鑳姐€傜紪鍐欏嚱鏁扮殑闅剧偣鍦ㄤ簬缂栧啓鍚堝苟鐨勫崟鍏冩牸銆傚崟鍏冩牸涓嬫爣浠?寮€濮嬨€倄lwt瀹樻柟API锛歨ttps://xlwt.readthedocs.io/e...瀹夎锛歱ipinstallxlwt鏂板缓workbook锛歸k=xlwt.Workbook()鏂板缓sheet锛歴heet1=wk.add_sheet("data",cell_overwrite_ok=True)鍐欏叆鏅€氬崟鍏冩牸锛氬啓鍏ョ3琛岀2鍒梥heet1.write(2,1,"liebao")#鍙傛暟涓€锛氳涓嬫爣#鍙傛暟浜岋細鍒椾笅鏍?鍙傛暟涓夛細鍐欏叆鍚堝苟鍗曞厓鏍肩殑鍐呭锛?鍒楀悎骞讹細鍐欏叆绗?琛岋紝绗?~5鍒梥heet1.write_merge(1,1,1,4,"鍒楀悎骞?)#琛屽悎骞讹細鍐欏叆绗?~3琛岋紝绗?鍒梥heet1.write_merge(0,2),2,2,"rowmerge")#鍙傛暟1锛氳捣濮嬭涓嬫爣#鍙傛暟2锛氱粨鏉熻涓嬫爣锛堝惈锛?鍙傛暟3锛氳捣濮嬪垪涓嬫爣#鍙傛暟4锛氱粨鏉熷垪涓嬫爣锛堝惈锛?鍙傛暟5锛氬皢鍐欏叆鐨勫唴瀹逛繚瀛樺埌琛ㄦ牸鏂囦欢涓瓀k.save(file_name)#鍙傛暟1锛氫繚瀛樿〃鏍兼枃浠跺悕鎴栨祦浣嗘槸鎴戜滑濡備綍涓烘垜浠殑鍗曞厓鏍艰缃牱寮忓憿锛熶竴鑸崟鍏冩牸閮戒細璋冩暣鏍峰紡锛屾瘮濡傚悎骞跺眳涓紝璁剧疆瀛椾綋澶у皬锛岃儗鏅鑹茬瓑绛夛紵鍏紡鎬庝箞鍐欙紵渚嬪瀹炵幇濡備笅{defxlwt_excel():'''Formwriting:return:'''#鑾峰彇涓€涓彲鍐欑殑宸ヤ綔绨垮璞k=xlwt.Workbook()#娣诲姞涓€涓伐浣滆〃锛屽崟鍏冩牸鍙互琚敼鍐檚heet1=wk.add_sheet(sheet_name,cell_overwrite_ok=True)#鍚堝苟琛岋細鍐欏叆鍚堝苟鐨勭涓€琛屽拰绗簩琛宖oriinrange(0,len(row0_2)):sheet1.write_merge(0,1,i,i,row0_2[i],get_style())#鍐欏崟涓渶鍚庝竴鍒楃殑绗竴琛屽拰绗簩琛岋細鍒嗗紑涓よ鎶樺彔鍓嶆秷鑰楋紙鍏冿級sheet1.write(0,len(row0_2),simple_end_col[0],get_style())sheet1.write(1,len(row0_2),simple_end_col[1],get_style())#鍚堝苟琛岋細鍐欏叆绗竴鍒梥heet1.write_merge(2,len(liebao_accounts)+1,0,0,colum0[0],get_style())sheet1.write_merge(2+len(liebao_accounts),len(liebao_accounts)+len(wifi_accounts)+1,0,0,colum0[1],get_style())#鍐欏叆鍗曚釜鍗曞厓鏍硷細writecheetahdata:forindexinrange(0,len(liebao_accounts)):sheet1.write(2+index,1,liebao_accounts[index]['app'],get_style(True))sheet1.write(2+index,2,liebao_accounts[index]['system'],get_style(True))sheet1.write(2+index,3,liebao_accounts[index]['account'],get_style(True))sheet1.write(2+index,4,float(liebao_accounts[index]['spend']),get_style(True))#鍐欏叆鍗曚釜鍗曞厓鏍?writewifidataforindexinrange(0,len(wifi_accounts)):sheet1.write(2+len(liebao_accounts)+index,1,wifi_accounts[index]['app'],get_style(True))sheet1.write(2+len(liebao_accounts)+index,2,wifi_accounts[index]['system'],get_style(True))sheet1.write(2+len(liebao_accounts)+index,3,wifi_accounts[index]['account'],get_style(True))sheet1.write(2+len(liebao_accounts)+index,4,float(wifi_accounts[index]['spend']),get_style(True))#鍐欏叆鏁板瓧鏍煎紡sheet1.write_merge(2+len(liebao_accounts)+len(wifi_accounts),2+len(liebao_accounts)+len(wifi_accounts),0,1,datetime.now(),get_style(num_format=True))#鍐欏叆鍚堝苟鍒楋細totalsheet1.write_merge(2+len(liebao_accounts)+len(wifi_accounts),2+len(liebao_accounts)+len(wifi_accounts),2,3,"Total",get_style())#鍐欏叕寮忥細sum娑堣垂sumsheet1.write(2+len(liebao_accounts)+len(wifi_accounts),4,xlwt.Formula("SUM(E3:E%d)"%(3+len(liebao_accounts)+len(wifi_accounts)-1),get_style())#鍐欏叆瓒呴摼鎺heet1.write_merge(3+len(liebao_accounts)+len(wifi_accounts),3+len(liebao_accounts)+len(wifi_accounts),0,4,xlwt.Formula('瓒呴摼鎺?"https://sunflowercoder.com/";"鏇村濂芥枃绔狅紝鐐瑰嚮鏌ョ湅鎴戠殑鍗氬")'),get_style(bold=True))#淇敼鍒楀foriinrange(0,len(row0_2)+1):sheet1.col(i).width=150*30#瀹氫箟鍒楀sheet1.col(0).width=50*30#瀹氫箟鍒楀sheet1.col(2).width=200*30#瀹氫箟鍒楀#save杞埌鏂囦欢wk.save(file_name)defget_style(simple_ceil=False,num_format=False,bold=False):'''璁剧疆琛ㄦ牸鏍峰紡:paramsimple_ceil:鏄惁涓烘櫘閫氬崟鍏冩牸锛岄粯璁や负anuncommoncell:paramnum_format:鏄惁涓洪渶瑕佹牸寮忓寲鐨勬暟瀛楀崟鍏冩牸:parambold:鏄惁闇€瑕佸姞绮?return:'''style=xlwt.XFStyle()ifnotsimple_ceil:#fontfont=xlwt.Font()font.name="瀹嬩綋"font.bold=boldfont.underline=Falsefont.italic=Falsefont.colour_index=0font.height=200#200鏄瓧浣撳ぇ灏?0style.font=font#鍗曞厓鏍煎眳涓榻?xlwt.Alignment()align.horz=xlwt.Alignment.HORZ_CENTER#姘村钩鏂瑰悜align.vert=xlwt.Alignment.VERT_CENTER#鍨傜洿鏂瑰悜鏍峰紡.alignment=align#鑳屾櫙棰滆壊pattern=xlwt.Pattern()pattern.pattern=xlwt.Pattern.SOLID_PATTERNpattern.pattern_fore_colour=xlwt.Style.colour_map['pale_blue']#璁剧疆鍗曞厓鏍艰儗鏅鑹蹭负榛勮壊style.pattern=pattern#Borderborder=xlwt.Borders()#缁欏崟鍏冩牸娣诲姞杈规border.left=xlwt.Borders.THIN#Leftborder.top=xlwt.Borders.THIN#Upperborder.right=xlwt.Borders.THIN#Rightborder.bottom=xlwt.Borders.THIN#bottomborder.left_colour=0x40#杈规绾块鑹瞓order.right_colour=0x40border.top_colour=0x40border.bottom_colour=0x40style.borders=border#鏁板瓧鏍煎紡ifnum_format:style.num_format_str='M/D/YY'#閫夐」锛欴-MMM-YY銆丏-MMM-YY銆丏-MMM銆丮MM-YY銆乭:mm銆乭:mm:ss銆乭:mm銆乭:mm:ss銆丮/D/YYh:mm,mm:ss,[h]:mm:ss,mm:ss.0杩斿洖鏍峰紡琛ㄦ牸璇诲彇璇诲彇鍚堝苟鍗曞厓鏍肩殑鍐呭姣旇緝楹荤儲锛孭ython璇诲彇Excel涓殑鍗曞厓鏍兼湁5绉嶅唴瀹硅繑鍥烇紝ctypes鏄細0empty,1string,2number,3date,4boolean锛?閿欒xlrd瀹樻柟API锛歨ttps://xlrd.readthedocs.io/e...瀹夎锛歱ipinstallxlrd璇诲彇绀轰緥锛歞efxlrd_excel():'''Tablereading:return:'''#鎵撳紑鏂囦欢wb=xlrd.open_workbook(filename=file_name,formatting_info=True)#鑾峰彇鎵€鏈夎〃鍚峱rint("AlltablesName:",wb.sheet_names())#鏍规嵁绱㈠紩鑾峰彇琛╯heet1=wb.sheet_by_index(0)#鏍规嵁鍚嶇О鑾峰彇琛?sheet2=wb.sheet_by_name(sheet_name)#杈撳嚭琛ㄥ悕锛岃鏁板拰鍒楁暟print("绗竴涓〃鍚嶏細",sheet1.name,"琛屾暟锛?,sheet1.nrows,"鍒楁暟锛?,sheet1.ncols)#鑾峰彇琛屽拰鍒楃殑鍐呭rows=sheet1.row_values(0)#鑾峰彇绗竴琛岀殑鍐呭cols=sheet1.col_values(0)#鑾峰彇绗竴鍒楃殑鍐呭print(rows)print(cols)#鑾峰彇鍗曞厓鏍煎唴瀹圭殑涓夌鏂瑰紡print(sheet1.cell(0,4).value)print(sheet1.cell_value(0,4))print(sheet1.row(0)[4].value)#杈撳嚭鍚堝苟琛ㄧ殑鍐呭锛氭敞鎰忓綋xlrd.open_workbook(),formatting_info=True鏄繀闇€鐨勶紝鍚﹀垯merged_cells杩斿洖绌哄悎骞禿_cells=sheet1.merged_cellsprint("Mergedcells:",merged_cells)foriteminmerged_cells:#鍚堝苟鍚庣殑鍗曞厓鏍间负鍏冪粍褰㈠紡锛屽(12,13,0,2)(璧峰琛屾爣绛撅紝缁撴潫琛屾爣绛?璧峰鍒楁爣绛?缁撴潫鍒楁爣绛?鍙互璁剧疆涓?璧峰琛屾爣绛?璧峰鍒楁爣绛?print("mergedcell",item,"value:",sheet1.cell_value(item[0],item[2]))#Python璇诲彇Excel涓殑鍗曞厓鏍煎唴瀹癸紝杩斿洖5绉嶇被鍨嬶紝ctypes鍒嗗埆鏄細0empty,1string,2number,3date,4boolean,5error#杈撳嚭鏃ユ湡鏍煎紡ifsheet1.cell_type(12,0)==3:date_value=xlrd.xldate_as_tuple(sheet1.cell_value(12,0),wb.datemode)print("Thedateis:",date_value,)print("Thedateis锛堟牸寮忎负2019-09-17锛?",date(*date_value[:3]))print("鏃ユ湡鏍煎紡涓?019/09/17):",date(*date_value[:3]).strftime('%Y/%m/%d'))xlwt鏈€澶х殑缂虹偣灏辨槸涓嶈兘淇敼琛紝鍙兘娣诲姞淇敼鏂规硶銆傚皬缂栦細鍦ㄥ悗闈㈢殑鏂囩珷涓鏄庨渶瑕佺ず渚嬩唬鐮侊紝鐐瑰師鏂囬摼鎺ヰ煉℃洿澶氬ソ鏂囨杩庡叧娉ㄦ垜鐨勫叕浼楀彿~
