python合并文件夹下excel_python合并目录下excel数据

import os

import datetime

import xlrd

import xlsxwriter

# 遍历的文件目录

file_path = './test'

# 写入的目标文件：

tar_file = file_path + '/mixed.xlsx'

# 遍历目录下的所有文件名

def all_path(dirname):

result = []

# 当前主目录,当前主目录下的所有目录,当前主目录下的所有文件

for maindir, subdir, file_name_list in os.walk(dirname):

for filename in file_name_list:

if filename.endswith('xls') or filename.endswith('xlsx'):

apath = os.path.join(maindir, filename) # 合并成一个完整路径

result.append(apath)

return result

# 获取excel文件的内容数据

def concat_and_insert(fdir):

records = []

if len(fdir) > 0:

for dir in fdir:

# 读文件

data = xlrd.open_workbook(dir)

# 第一个sheet页的名称;

first_sheet = data.sheet_by_index(0).name

print(dir, '>' * 10, first_sheet)

# 获取sheet页的名称

sheet = data.sheet_by_name(first_sheet)

# 获取表的行数：

nrows = sheet.nrows

for i in range(nrows):

# 跳过第一行

if i < 5:

continue

records.append(sheet.row_values(i))

return records

def insert_file(alist, tarfile):

# 新建目标文件

wh = xlsxwriter.Workbook(tarfile)

wadd = wh.add_worksheet('total')

if len(alist) > 0:

for row_num, row_data in enumerate(alist):

wadd.write_row(row_num + 1, 0, row_data)

wh.close()

if __name__ == "__main__":

strat = datetime.datetime.now()

print(strat)

filename = all_path(file_path)

print(filename)

records = concat_and_insert(filename)

# 写入文件

insert_file(records, tar_file)

end = datetime.datetime.now()

print(end)

print("持续时间{}".format(end - strat))

print('ok')