参考链接: https://blog.csdn.net/shui6410/article/details/129810672
执行:
python merge_excel_dir.py 待合并
# encoding = utf-8
# 将所有查询到的数据插入到临时表,然后整个下载下来之后利用awk命令进行拆分
import sys,os,logging,chardet
from openpyxl import Workbook,load_workbook
logging.basicConfig(level=logging.DEBUG,format='%(asctime)s - %(pathname)s[line:%(lineno)d] - %(levelname)s: %(message)s')
"""
功能:
参数指定需要合并的所有excel,在某个路径下执行该脚本,最后生成文件名为"合并后excel.xlsx"的excel文件
执行命令:
python merge_excel_dir.py 待合并
执行逻辑:
python 功能py脚本 待合并的所有excel目录
缺陷:
1.待合并的excel只有一个sheet名称,且都为Sheet1
"""
# 参考 https://blog.csdn.net/shui6410/article/details/129810672
def copy_worksheet(src_xlsx,ssheetname,dst_xlsx,nsheetname=None):
'''
复制sheet工作表到另一个工作簿
:param src_xlsx:源xlsx工作簿文件(相对/绝对目录及文件名)包含xlsx后缀 不存在则报错 字符串类型
:param ssheetname:源工作表名 不存在则报错 字符串类型
:param dst_xlsx:复制后的工作表名 不填写默认与原名相同 字符串类型
:param nsheetname:目标xlsx工作簿文件 如不存在则创建
:return:
'''
if nsheetname==None: # 如果nsheetname==None,
nsheetname=ssheetname
try:
sw=load_workbook(f'{src_xlsx}',data_only=True)
except KeyError:
raise KeyError('旧工作簿不存在 The old xlsx is not exists')
try:
dw=load_workbook(f'{dst_xlsx}')
except FileNotFoundError:
dw=Workbook()
try:
sheet = dw[f'{nsheetname}']
except KeyError:
sheet=dw.create_sheet(f'{nsheetname}')
try:
src_sheet=sw[f'{ssheetname}']
except KeyError:
raise KeyError('源工作簿文件不存在该工作簿 The sheet does not exist in the source file')
for row in src_sheet.iter_rows():
# print(row)
row_list=[]
for cell in row:
row_list.append(cell.value)
# print(row_list)
sheet.append(row_list)
dw.save(f'{dst_xlsx}')
dw.close()
if __name__ == '__main__':
cwd = os.getcwd()
print(cwd)
awd = cwd+"\\"+sys.argv[1] # aim_work_dir
afl = os.listdir(awd) # aim_file_list
print(afl)
dst_excel_path = cwd+"\\"+"合并后excel.xlsx"
if os.path.exists(dst_excel_path):
os.remove(dst_excel_path) # 每次第一步,先删除合并后Sheet
for ele in afl:
src_excel_path = awd+"\\"+ele
logging.info(src_excel_path)
copy_worksheet(src_excel_path,"Sheet1",dst_excel_path,ele)
dw=load_workbook(f'{dst_excel_path}')
del dw["Sheet"] # 删除默认生成的空白Sheet
dw.save(f'{dst_excel_path}')
dw.close()