如何用python从excel中同时提取两个列的数据，并生成dict（用xlrd） python操作excel，使用xlrd模块，获取某一列数据...
python\u5982\u4f55\u8bfb\u53d6\u591a\u4e2aexcel\u5408\u5e76\u5230\u4e00\u4e2aexcel\u4e2d\u601d\u8def \u5229\u7528python xlrd\u5305\u8bfb\u53d6excle\u6587\u4ef6\uff0c\u7136\u540e\u5c06\u6587\u4ef6\u5185\u5bb9\u5b58\u5165\u4e00\u4e2a\u5217\u8868\u4e2d\uff0c\u518d\u5229\u7528xlsxwriter\u5c06\u5185\u5bb9\u5199\u5165\u5230\u4e00\u4e2a\u65b0\u7684excel\u6587\u4ef6\u4e2d\u3002
\u5b8c\u6574\u4ee3\u7801
# -*- coding: utf-8 -*-#\u5c06\u591a\u4e2aExcel\u6587\u4ef6\u5408\u5e76\u6210\u4e00\u4e2aimport xlrdimport xlsxwriter#\u6253\u5f00\u4e00\u4e2aexcel\u6587\u4ef6def open_xls(file):fh=xlrd.open_workbook(file) return fh#\u83b7\u53d6excel\u4e2d\u6240\u6709\u7684sheet\u8868def getsheet(fh):return fh.sheets()#\u83b7\u53d6sheet\u8868\u7684\u884c\u6570def getnrows(fh,sheet):table=fh.sheets()[sheet] return table.nrows#\u8bfb\u53d6\u6587\u4ef6\u5185\u5bb9\u5e76\u8fd4\u56de\u884c\u5185\u5bb9def getFilect(file,shnum):fh=open_xls(file)table=fh.sheets()[shnum]num=table.nrows for row in range(num):rdata=table.row_values(row)datavalue.append(rdata) return datavalue#\u83b7\u53d6sheet\u8868\u7684\u4e2a\u6570def getshnum(fh):x=0sh=getsheet(fh) for sheet in sh:x+=1return xif __name__=='__main__': #\u5b9a\u4e49\u8981\u5408\u5e76\u7684excel\u6587\u4ef6\u5217\u8868allxls=['F:/test/excel1.xlsx','F:/test/excel2.xlsx'] #\u5b58\u50a8\u6240\u6709\u8bfb\u53d6\u7684\u7ed3\u679cdatavalue=[] for fl in allxls:fh=open_xls(fl)x=getshnum(fh) for shnum in range(x):print("\u6b63\u5728\u8bfb\u53d6\u6587\u4ef6\uff1a"+str(fl)+"\u7684\u7b2c"+str(shnum)+"\u4e2asheet\u8868\u7684\u5185\u5bb9...")rvalue=getFilect(fl,shnum) #\u5b9a\u4e49\u6700\u7ec8\u5408\u5e76\u540e\u751f\u6210\u7684\u65b0\u6587\u4ef6endfile='F:/test/excel3.xlsx'wb1=xlsxwriter.Workbook(endfile) #\u521b\u5efa\u4e00\u4e2asheet\u5de5\u4f5c\u5bf9\u8c61ws=wb1.add_worksheet() for a in range(len(rvalue)): for b in range(len(rvalue[a])):c=rvalue[a][b]ws.write(a,b,c)wb1.close()print("\u6587\u4ef6\u5408\u5e76\u5b8c\u6210")1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162
\u6e90\u6587\u4ef6excel1:
\u6e90\u6587\u4ef6excel2:
\u8fd0\u884c\u7ed3\u679c\uff1a
\u5408\u5e76\u540e\u7684excel3\uff1a
a = [[table.cell(i,ord('A')-ord('A')).value, table.cell(i,ord('B')-ord('A')).value] for i in range(1,nrows)]
import xlrd
data = xlrd.open_workbook('excelFile.xls')
table = data.sheet_by_index(0) #通过索引顺序获取工作表
colnumber_b = ord('B')-ord('A')
colnumber_g = ord('G')-ord('A')
scroe_dict = {}
for i in range(100): #前一百行
score_dict[table.cell(i,colnumber_b).value]=table.cell(i,colnumber_g).value
import xlrd
data = xlrd.open_workbook('excelFile.xls')
table = data.sheet_by_index(0) #通过索引顺序获取工作表
colnumber_b = ord('B')-ord('A')
colnumber_g = ord('G')-ord('A')
score_dict = {}
for i in range(100): #前一百行
score_dict[table.cell(i,colnumber_b).value]=table.cell(i,colnumber_g).value
import xlrd
data = xlrd.open_workbook('excelFile.xls')
table = data.sheet_by_index(0) #通过索引顺序获取工作表
colnumber_b = ord('B')-ord('A')
colnumber_g = ord('G')-ord('A')
score_dict = {}
for i in range(100): #前一百行
score_dict[table.cell(i,colnumber_b).value]=table.cell(i,colnumber_g).value
绛旓細涓銆浣跨敤宸ュ叿锛excel銆乸ython 浜屻佷娇鐢ㄦ柟娉曞拰姝ラ锛1銆佸厛瀹夎python2.7锛岀劧鍚庨厤缃幆澧冨彉閲忥紝淇濊瘉python鍦璇ョ幆澧冧腑锛屾墦寮寮濮嬭彍鍗曪紝鎵惧埌璁＄畻鏈猴紝鐒跺悗鍙抽敭鍗曞嚮瀹冿紝閫夋嫨銆愬睘鎬с2銆佹壘鍒伴珮绾х郴缁熻缃 3銆佹壘鍒扮幆澧冨彉閲忥紝鐐瑰嚮璇ユ寜閽紝鎵撳紑鐜鍙橀噺璁剧疆绐楀彛 4銆佹壘鍒癙ath绯荤粺鍙橀噺锛岀劧鍚庣偣鍑汇愮紪杈戙5銆佸湪杩欓噷锛岀‘淇...
绛旓細鐢╬ython璇诲彇excel涓鐨勪竴鍒楁暟鎹楠ゅ涓嬶細1銆侀鍏堟墦寮dos鍛戒护绐楋紝瀹夎蹇呴』鐨勪袱涓簱锛屽懡浠ゆ槸锛歱ip3 install xlrd锛汸ip3 install xlwt銆2銆佸噯澶囧ソexcel銆3銆佹墦寮pycharm锛屾柊寤轰竴涓猠xcel.py鐨勬枃浠讹紝棣栧厛瀵煎叆鏀寔搴搃mport xlrdimport xlwt銆4銆佽鎿嶄綔excel锛岄鍏堝緱鎵撳紑excel锛屼娇鐢╫pen_workbook(鈥樿矾寰勨)锛...
绛旓細璇诲彇绗簩涓 Excel 鏂囦欢 wb2 = openpyxl.load_workbook("file2.xlsx")閬嶅巻绗簩涓 Excel 鏂囦欢涓殑姣忎釜宸ヤ綔琛 for sheet in wb2.worksheets:澶嶅埗璇ュ伐浣滆〃鍒版柊鐨 Excel 鏂囦欢涓 wb.create_sheet(title=sheet.title, index=sheet.index)淇濆瓨鏂扮殑 Excel 鏂囦欢 wb.save("newfile.xlsx")鍦涓婇潰鐨勪唬鐮佷腑锛...
绛旓細sheet_name = excel.sheet_names() // sheet_name涓哄彉閲忥紝鍏跺间负琛ㄦ牸琛ㄥ崟鍚嶇О 濡傛灉鎯宠鐪嬪埌涓婅堪涓や釜鍙橀噺锛屽彲浠浣跨敤print()鍑芥暟灏嗗畠浠墦鍗板嚭鏉 鎯宠璇诲彇鏌愪釜琛ㄥ崟鐨勬暟鎹紝棣栧厛鑾峰彇琛ㄥ崟 excel.sheet_by_index(0) //琛ㄥ崟绱㈠紩浠0寮濮嬶紝鑾峰彇绗竴涓〃鍗曞璞 excel.sheet_by_name('xxx')...
绛旓細鏈鍚庢垜閲囩敤浜嗕竴涓瘮杈冨彉閫氱殑鏂瑰紡:鐩存帴浠巈xcel涓鑾峰彇鎴戞兂瑕佺殑鍊煎啀杩涜姣旇緝,鏁堟灉鏄笉閿欏氨鏄氱敤琛屼笉澶ソ,涓 鍛笉鑳介棶棰樿繕娌¤В鍐炽備簩銆佸啓excel琛 鍐檈xcel琛ㄨ鐢ㄥ埌xlwt妯″潡,瀹樼綉涓嬭浇(http://pypi.python.org/pypi/xlwt)銆傚ぇ鑷浣跨敤娴佺▼濡備笅: 1銆佸鍏ユā鍧 澶嶅埗浠ｇ爜浠ｇ爜濡備笅: import xlwt2銆佸垱寤簑orkbook(鍏跺疄灏辨槸...
绛旓細棣栧厛锛屾偍闇瑕佸畨瑁呰繖浜涘簱銆傚湪鍛戒护琛屼腑锛屾偍鍙互杩愯浠ヤ笅鍛戒护锛歴hell澶嶅埗pip install pandas openpyxl鐒跺悗锛屾偍鍙互浣跨敤浠ヤ笅浠ｇ爜灏Python鏁版嵁瀵煎叆Excel锛python澶嶅埗import pandas as pd# 鍒涘缓涓涓暟鎹瓧鍏竏ata = {'Name': ['Alice', 'Bob', 'Charlie'], 'Age': [25, 30, 35]}# 灏嗘暟鎹瓧鍏歌浆鎹负...
绛旓細table = excel.sheet_by_name('Sheet1') #閫氳繃琛ㄥ悕鑾峰彇 澶囨敞锛氫互涓嬫柟娉曠殑鎿嶄綔閮借鍦sheet鍩虹涓浣跨敤 鑾峰彇琛屾暟鍜屽垪鏁帮細rows=table.nrows #鑾峰彇琛屾暟 cols=table.ncols #鑾峰彇鍒楁暟 鑾峰彇鍗曞厓鏍煎硷細Data=table.cell(row,col).value #鑾峰彇琛ㄦ牸鍐呭锛屾槸浠庣涓琛岀涓鍒楁槸浠0寮濮嬬殑锛屾敞鎰忎笉...
绛旓細Python鎿嶄綔Excel瀹炵幇鑷姩鍖栨姤琛 瀹夎 python -m pip install xlrd xlwt xlutils銆傚熀鏈敤娉 1.浠庢寚瀹氭枃浠惰矾寰勮鍙excel琛ㄦ牸锛岃繘琛屼竴瀹氭搷浣滐紝鐒跺悗淇濆瓨鍒板彟涓涓猠xcel鏂囦欢锛歳esult.xlsx import xlwt import xlrd from xlutils.copy import ...
绛旓細3銆佸湪鍛戒护琛岃緭鍏python锛岃繘鍏ython缂栫▼鐜銆傝緭鍏ワ細import xlwings as xw锛屾祴璇曟槸鍚﹀凡缁忔垚鍔熷畨瑁呫傦紙娉ㄦ剰锛氳緭鍏w.__path__鍙煡鐪嬪畨瑁呯洰褰曪級4銆鍒╃敤xlwings鎿嶆帶涓涓┖鐧界殑excel琛ㄥ崟锛屽彲浠ユ煡鐪媥lwings鐨勬枃妗ｏ紝鍙互閫氳繃鐧惧害xlwings锛屾壘鍒癲oc.xlwings.org銆5銆佸埄鐢▁lwings鏇夸唬VBA鍐欏畯锛屾搷鎺xcel銆傚皢xlwings.bas...
绛旓細鎬庝箞鍦‥xcel涓杩愯python鑴氭湰锛熻繖涓棶棰樼敱鏉ュ凡涔咃紝寰堝pythoner閮藉笇鏈涚洿鎺鐢╬ython鎺у埗Excel锛屼絾鏄彧鑳戒粠澶栭儴璋冪敤锛屾湁娌℃湁涓涓猇BA鐨勪笢瑗垮彲浠ョ洿鎺ュ湪Excel涓繍琛屽憿锛熺瓟妗堟槸鏈夛紝DataNitro灏辨槸杩欎箞涓涓笢瑗匡紝鐪嬬湅涓嬮潰Excel涓殑鐣岄潰锛屼粬灏辨槸浜嗭紝鍙互鐩存帴鐐瑰嚮鈥榬un'鏉ヨ繍琛屼竴涓狿ython鑴氭湰銆備笅闈㈡垜浠潵鐪嬬湅瀹冩槸濡備綍瀹夎...
