python合并excel

 有的时候从网上导出的excel资源有条数限制，可能一次只有500条。

当最终需要5000条数据的时候，手动整合就变得很不便捷，自己写了一个小的脚本，可以对同一布局类型的excel文件进行整合

import os
import warnings 

from openpyxl import load_workbook

warnings.filterwarnings('ignore')

path = os.getcwd()
append_rows_pivot = 0


def get_excel_list(path):
    file_list = os.listdir(path)
    excel_lists = [item for item in file_list if '.xls' in item]
    print("您当前文件夹下的excel文件共有{}个\n"
          "它们分别是{}".format(len(excel_lists), excel_lists))
    return excel_lists


excel_lists = get_excel_list(path)


def merge_excel(ws_write, matrix, rows_pivot):
    max_row = ws_write.max_row
    for row_index in range(len(matrix)):
        for col_index in range(1, len(matrix[0]) + 1):
            ws_write.cell(rows_pivot + row_index, col_index).value \
                = matrix[row_index][col_index - 1]
    return len(matrix) + rows_pivot


def read_all_excel():
    for excel in excel_lists[1:]:
        # print(excel)
        wb_read = load_workbook(excel)
        ws_read = wb_read.active
        total_list = []
        for row in range(1 + title, ws_read.max_row + 1):  #
            row_list = []
            for col in range(1, ws_read.max_column + 1):
                value = ws_read.cell(row, col).value
                row_list.append(value)
            total_list.append(row_list)
        wb_read.close()  # 关闭excel释放资源
        # print(len(total_list))
        # print(total_list)
        print("第%d张excel\t%s\t合并完毕"%(excel_lists.index(excel),excel))
        global append_rows_pivot
        append_rows_pivot = merge_excel(ws_basic, total_list, append_rows_pivot)


if __name__ == '__main__':
    # 是否包含表头的文件
    title = True
    # 将数据保存在第一张表中
    wb_basic = load_workbook(excel_lists[0])
    print("本次合并以%s为基准文件"%(excel_lists[0]))
    ws_basic = wb_basic.active
    append_rows_pivot = ws_basic.max_row + 1
    read_all_excel()
    wb_basic.save(excel_lists[0])
    wb_basic.close()