python如何读取多个excel合并到一个excel中 如何用python把多个excel文件自动合并到一个文件中
\u5982\u4f55\u7528python\u628a\u591a\u4e2aexcel\u6587\u4ef6\u81ea\u52a8\u5408\u5e76\u5230\u4e00\u4e2a\u6587\u4ef6\u4e2d\u5de5\u4f5c\u4e2d\u7ecf\u5e38\u7528\u5230Excel\uff0c\u5f88\u591a\u4e8b\u60c5\u90fd\u91cd\u590d\u5904\u7406\uff0c\u6bd4\u5982\u6bcf\u5929\u7684\u65e5\u62a5\uff0c\u6bcf\u5468\u7684\u5468\u62a5\uff0c\u5404\u79cd\u6570\u636e\u8868\uff0c\u8fd9\u79cd\u56fa\u5b9a\u7684\u8868\u5176\u5b9e\u90fd\u662f\u91cd\u590d\u6027\u7684\u52b3\u52a8\u3002\u6700\u8fd1\u8fd9\u6bb5\u65f6\u95f4\u4e00\u76f4\u5728\u5b66python\uff0c\u4f46\u662f\u65ad\u65ad\u7eed\u7eed\u7684\uff0c\u773c\u9ad8\u624b\u4f4e\uff0c\u770b\u522b\u4eba\u7684\u4ee3\u7801\u770b\u7684\u5f88\u61c2\uff0c\u4f46\u662f\u81ea\u5df1\u5c31\u662f\u5199\u4e0d\u51fa\u6765\uff0c\u51b3\u5b9a\u81ea\u5df1\u5199\u4e2a\u5c0f\u7a0b\u5e8f\u7ec3\u7ec3\u624b\uff0c\u89e3\u51b3\u4e0b\u8eab\u8fb9\u7684\u5b9e\u9645\u95ee\u9898\uff0c\u63d0\u9ad8\u5de5\u4f5c\u6548\u7387\u3002
\u8fd9\u4e2a\u5c0f\u811a\u672c\u4e3b\u8981\u662f\u628a\u591a\u4e2aexcel\u6587\u4ef6\u5408\u5e76\u5230\u4e00\u4e2a\u6587\u4ef6\u4e2d\u3002\u7f51\u4e0a\u641c\u7d22\u4e86\u4e0b\u6587\u7ae0\uff0c\u6709\u4e0d\u5c11excel\u7684python\u5e93\uff0c\u6700\u540e\u9009\u62e9\u4e86\u9002\u5408python3\u7684openpyxl\u5e93\uff0c\u8fd9\u4e2a\u5e93\u5b89\u88c5\u5f88\u7b80\u5355\uff0c
pip install openpyxl
\u5f00\u53d1\u7684\u73af\u5883\u662f\uff1amac\uff0fwin +python3.5 +pycharm
\u5e9f\u8bdd\u4e0d\u591a\u8bf4\uff0c\u628a\u4ee3\u7801\u8d34\u51fa\u6765\uff0c\u8bf7\u5927\u5bb6\u591a\u6307\u70b9\u4e0b\uff0c\u6211\u89c9\u7740\u4ee3\u7801\u8fd8\u53ef\u4ee5\u66f4\u7b80\u6d01\u4e0b\uff0c\u5982\u679c\u5927\u5bb6\u6709\u597d\u7684\u4f18\u5316\u65b9\u6848\u9ebb\u70e6\u7559\u8a00\u6307\u5bfc\u4e0b\uff1a
#coding=gbkimport openpyxlexcel_data=['qihu.xlsx','baidu.xlsx']# new=openpyxl.load_workbook('all.xlsx')for excel_name in excel_data:
wb= openpyxl.load_workbook(excel_name,data_only=True)
sheet_name=wb.get_sheet_names()
# print(sheet_name)
for work in sheet_name:
nb = openpyxl.load_workbook('all.xlsx',data_only=True)
newsheet_name = nb.get_sheet_names()
if work in newsheet_name :
name = nb.get_sheet_by_name(work)
sheet = wb.get_sheet_by_name(work)
for i in range(1,sheet.max_row+1):
for j in range(1,sheet.max_column+1):
# \u83b7\u53d6\u6574\u4e2asheet\u6570\u636e
data=sheet.cell(row=i,column=j).value
name.cell(column=j,row=i).value=data
else:
newsheet = nb.create_sheet(title=work)
name = nb.get_sheet_by_name(work)
sheet = wb.get_sheet_by_name(work)
for i in range(1,sheet.max_row+1):
for j in range(1,sheet.max_column+1):
# \u83b7\u53d6\u6574\u4e2asheet\u6570\u636e
data=sheet.cell(row=i,column=j).value
name.cell(column=j,row=i).value=data
print(name)
nb.save('all.xlsx')
# print(newsheet_name)
http://jingyan.baidu.com/article/148a19218539ae4d70c3b161.html
思路
利用python xlrd包读取excle文件，然后将文件内容存入一个列表中，再利用xlsxwriter将内容写入到一个新的excel文件中。
完整代码
# -*- coding: utf-8 -*-#将多个Excel文件合并成一个import xlrdimport xlsxwriter#打开一个excel文件def open_xls(file):
fh=xlrd.open_workbook(file) return fh#获取excel中所有的sheet表def getsheet(fh):
return fh.sheets()#获取sheet表的行数def getnrows(fh,sheet):
table=fh.sheets()[sheet] return table.nrows#读取文件内容并返回行内容def 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#获取sheet表的个数def getshnum(fh):
x=0
sh=getsheet(fh) for sheet in sh:
x+=1
return xif __name__=='__main__': #定义要合并的excel文件列表
allxls=['F:/test/excel1.xlsx','F:/test/excel2.xlsx'] #存储所有读取的结果
datavalue=[] for fl in allxls:
fh=open_xls(fl)
x=getshnum(fh) for shnum in range(x):
print("正在读取文件："+str(fl)+"的第"+str(shnum)+"个sheet表的内容...")
rvalue=getFilect(fl,shnum) #定义最终合并后生成的新文件
endfile='F:/test/excel3.xlsx'
wb1=xlsxwriter.Workbook(endfile) #创建一个sheet工作对象
ws=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("文件合并完成")1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162
源文件excel1:
源文件excel2:
运行结果：
合并后的excel3：
绛旓細item['Url'] = response.url 鐒跺悗鍦ㄦ暟鎹鎶婂偍瀛榰rl鐨刢olumn璁剧疆鎴恥nique銆備箣鍚庡湪python浠ｇ爜涓崟鑾锋暟鎹簱commit鏃惰繑鍥炵殑寮傚父锛屽拷鐣ユ帀鎴栬呰浆鍏og涓兘鍙互銆傛垜浣跨敤鐨勬槸SqlAlchemy銆傛垜鏄繖涔堝啓鐨 from sqlalchemy.exc import IntegrityError class XxxPipeline(object):def process_item(self, item, spider):...
绛旓細!/usr/bin/env python3def main(): fname = 'errors.txt' a, b = 3, 0 try: c = a / b except Exception as e: print(repr(e)) with open(fname, 'w') as f: f.write(repr(e))print('save error message to {}'.format(fname)) else: p...
绛旓細1銆愪笓娉:Python+浜哄伐鏅鸿兘|Java澶ф暟鎹畖HTML5鍩硅銆 2銆愬厤璐规彁渚涘悕甯堢洿鎾鍫傘佸叕寮璇惧強瑙嗛鏁欑▼銆 3銆愬湴鍧:鍖椾含甯傛槍骞冲尯涓夋棗鐧炬眹鐗╃編澶у崠鍦2灞,寰俊鍏紬鍙:yuzhitc銆 鍚慣A鎻愰棶 鍏虫敞 灞曞紑鍏ㄩ儴 1.Python寮傚父绫 Python鏄潰鍚戝璞¤瑷,鎵浠ョ▼搴忔姏鍑虹殑寮傚父涔熸槸绫汇傚父瑙佺殑Python寮傚父鏈変互涓嬪嚑涓,澶у鍙澶ц嚧鎵竴鐪,鏈変釜...
绛旓細涓婁笅鏂囩鐞嗗櫒 锛屽紩鐢ㄨ嚜 Python 瀹樻柟鏂囨。锛 鏄竴绉嶈鎮ㄥ湪闇瑕佹椂鍑嗙‘鍒嗛厤鍜岄噴鏀捐祫婧愮殑鏂规硶 锛屾垨鑰呯畝鍗曟潵璇达細 褰撴偍鍦ㄦ煇浜涜祫婧愪笂鍋氭煇浜嬫椂缂╃煭鎮ㄧ殑浠ｇ爜鐗囨 锛岃繖鎰忓懗鐫鎮ㄥ彲浠ヨ嚜宸卞畾涔 with 璇彞鐨勭敤娉曪紒鎴戜滑濡備綍鍋氬埌杩欎竴鐐癸紵鍡紝寰堢畝鍗曪紝浣犲彧闇瑕佸疄鐜颁袱涓 榄旀湳鍑芥暟 锛氫竴涓彨鍋 __enter__ 锛屽彟...
绛旓細涓锛氬畨瑁厇abbix api 鎺ュ彛锛屼慨鏀箊abbix api 璋冪敤鎺ュ彛锛岃幏鍙栨暟鎹乫rom zabbix_api import ZabbixAPI import sys import datetime import time import argparse def fetch_to_csv(username,password,server,hostname,key,output,datetime1,datetime2,debuglevel):zapi = ZabbixAPI(server=server, log_level=...
绛旓細"Column 'ipint' cannot be null"鎻掑叆鏁版嵁鏃讹紝鍒梚pint鐨勫煎瓨鍦∟ULL 鍊
绛旓細!/usr/bin/env python -*- coding: utf-8 -*- classMyWith(object):def__init__(self):print"__init__ method"def__enter__(self):print"__enter__ method"returnself# 杩斿洖瀵硅薄缁檃s鍚庣殑鍙橀噺 def__exit__(self, exc_type, exc_value, exc_traceback):print"__exit__ method"ifexc...
绛旓細import jsonwith open('who.json', 'r') as f: data = json.load(f) dependencies = data['dependencies'] for k, v in dependencies.iteritems(): print(f'{k}@{v}')
绛旓細[python] view plain copy try:self.run()except SystemExit:...except:...if _sys:if id(_sys.excepthook) != id(_sys.__excepthook__):exc_type, exc_value, exc_tb = self.__exc_info()_sys.excepthook(exc_type, exc_value, exc_tb)else:_sys.stderr.write("Exception ...
绛旓細coefficients(a,b,c):") a,b,c=int(a,b,c) discRoot=math.sqrt(b*b-4*a*c) root1=(-b+discRoot)/(2*a) root2=(-b-discRoot)/(2*a) print ("\nThe solutions are",root1,root2)except Exception,e: traceback.print_exc() print a,...
