import pandas as pd
# 讀取所有xlsx
def read_xlsx(xlsx_list):
    """
    :param xlsx_list: xlsx文件列表
    :return: 
    """
    if len(xlsx_list) == 0 or xlsx_list is None:
        print("沒有任何xlsx文件，無法讀取數據")
        return
    df_list = []
    for xlsx in xlsx_list:
        df = pd.read_excel(xlsx)
        df_list.append(df)
    return df_list
# 合并xlsx
def merge_xlsx(df_list):
    """
    :param df_list: df列表
    :return: 
    """
    if len(df_list) == 0 or df_list is None:
        print("沒有任何df，無法合并數據")
        return
    merged_df = pd.concat(df_list, axis=0)
    xlsx_path = "文件保存路徑"
    merged_df.to_excel(xlsx_path, index=False)
    return xlsx_path
file_list = ["xlsx文件路徑1","xlsx文件路徑2","xlsx文件路徑3","xlsx文件路徑4"]
read_df_list = read_xlsx(file_list)
merged_file = merge_xlsx(read_df_list)
print(f"合并后的文件路徑：{merged_file}")

import xlrd
import xlwt
import pandas as pd
# 拆分數據
def split_file(file_path):
    """
    將傳入的xls文件格式的表格進行，按照指定行數進行拆分
    :return: 返回拆分后的文件路徑列表
    """
    if file_path is None:
        print("沒有需要拆分的表格")
        return
    split_rows = 500  # 每個表拆分數據的行數
    wb = xlrd.open_workbook(file_path)
    sheet = wb.sheet_by_index(0)
    total_rows = sheet.nrows
    split_size = split_rows + 1
    header = sheet.row_values(0)
    # 拆分大于指定的長度的文件
    first_file = True
    for i in range(total_rows // split_size):
        if first_file:  # 如果是第一個文件則從第二行開始（跳過表頭）
            start_row = i * split_size + 1
            first_file = False
        else:
            start_row = i * split_size
        end_row = start_row + split_rows
        data = [sheet.row_values(j) for j in range(start_row, end_row)]
        filename = f'拆分的文件_{i + 1}.xls'
        workbook = xlwt.Workbook()
        sheet_new = workbook.add_sheet("Sheet1")
        for col_index, value in enumerate(header):
            sheet_new.write(0, col_index, value)  # 寫入表頭
        for row_index, row in enumerate(data):
            for col_index, value in enumerate(row):
                sheet_new.write(row_index + 1, col_index, value)  # 寫入數據
        workbook.save(filename)
        print(f"拆分的文件路徑：{filename}")
your_file_path = "需要拆分的表格路徑"
split_file(your_file_path )