合并当前目录指定xlsx文件夹里所有excel文件

xlsx-merge.py

# conding=utf-8
# python3版本
# @说明:xlrd支持xls、xlsx读取; xlsxwriter用于写入一个新的excel，支持样式
# @author:llychao<lychao_vip@163.com>
# @date:2020-02-18

import xlsxwriter
import xlrd
import os
from datetime import datetime
import glob

now = datetime.now()  # 开始计时

# 建立合并的Excel
new_file = xlsxwriter.Workbook('merge.xlsx')
new_sheet = new_file.add_worksheet('Sheet1')

# 设置excel格式
format = new_file.add_format()
format.set_align('center')
format.set_valign('vcenter')
format.set_text_wrap()  # 设置单元格文字超出单元格长度后自动换行

new_sheet.freeze_panes(1, 1)  # 设置窗口冻结区域  距离顶部1行，距离左边1列
new_sheet.set_row(0, 12, format)
for i in range(1, 251):
    new_sheet.set_row(i, 18)  # 设置行高
new_sheet.set_column('A:B', 20, format)  # 设置列单元格的属性: 设置第一列宽度为20像素,居中对其
new_sheet.set_column('B:C', 15, format)
new_sheet.set_column('C:D', 25, format)

# 读取excel文件列表
pwd = os.getcwd()
num = 0
for filename in glob.glob(pwd + "/xlsx/*"):
    print("正在合并文件：" + filename + " ...")
    xlFile = xlrd.open_workbook(filename)
    for sheet in xlFile.sheets():
        row = sheet.nrows
        # 写表头
        if num == 0:
            for k, data in enumerate(sheet.row_values(1)):
                new_sheet.write(0, k, data)

        n = 2  # 跳过前n 行，将后面的行写入新的文件
        for i in range(row):
            if i > n - 1:
                num += 1
                new_row = sheet.row_values(i)
                for k, data in enumerate(new_row):
                    new_sheet.write(int(num), k, data)  # 参数：行号，列号，值

end = datetime.now()  # 结束计时
print("over！程序耗时： " + str(end - now))
new_file.close()