将多个文件夹的xls文件汇总成一个xls文件

# @Author: BigMoore
# @Time: 2022/3/29 11:36
import csv
import importlib
import sys
import os
import traceback
import pandas as pd
import xlrd
from io import StringIO


def Profiles(dir, profiles):
    for file in os.listdir(dir):
        newDir = os.path.join(dir, file)
        if os.path.isfile(newDir):
            profiles.append(newDir)
        else:
            Profiles(newDir, profiles)
    return profiles


def readExcel(path, writer):
    try:
        print(path)
        # 1 连接Excel表，打开工作簿workBook
        workbook = xlrd.open_workbook(path)
        # 2 选定想打开的工作表worksheet
        # 其中，0表示第一张worksheet.
        worksheet = workbook.sheet_by_index(0)
        # 3 指定row_num从第几行开始读, 索引从0开始
        #     worksheet.nrows:工作表的有效数据行数
        row_num = 2
        # names = []
        while row_num < worksheet.nrows:
            # row_data = []
            # 读取每一行的数据
            row_info = worksheet.row_values(row_num)
            # 成绩>98的，输出它的名字
            # if int(row_info[4]) > 98:
            #     # print(row_info[1])
            #     names.append(row_info[1])
            # else:
            #     print('error')
            # writer.writerow():单行写入，writer.writerows():多行写入
            writer.writerow(row_info)
            row_num += 1
        # print(names)
    except Exception as e:
        print('Error ' + path + ' ' + traceback.format_exc())
    finally:
        pass


def xlsToCsv(dir, cpath):
    # 定义文件列表，存放文件的path
    profiles = []
    # 调用函数，处理文件和文件夹的路径
    Profiles(dir, profiles)
    print(len(profiles))
    # for item in profiles:
    #     print('path:', item)
    # 打开要写入数据的文件
    file = open(cpath, 'w', encoding='utf-8', newline='')
    # with open(cpath, 'w', encoding='utf-8', newline='') as file:
    writer = csv.writer(file)
    for proj_path in profiles:
        readExcel(proj_path, writer)


# 利用pandas将csv文件转换为xlsx文件
def csvToXls(cpath, xpath):
    csv_file = pd.read_csv(cpath, encoding='utf-8')
    csv_file.to_excel(xpath, sheet_name='汇总表', header=False, index=False)


# 用pandas将csv--->xls
def csvToXls2(cpath, x_path):
    with open(cpath, 'r', encoding='utf-8', errors='ignore') as f:
        data = f.read()
    # 进行字符串数据缓存，以便循环利用
    data_file = StringIO(data)
    # print(type(data_file))
    # 此处读取到的数据是将每行数据当做列表返回的
    reader = csv.reader(data_file)
    list_csv = []
    for row in reader:
        # 查看每行的数据
        # print(' '.join(row))
        # join语句返回的是str字符串，a = ' '.join(row)# print(type(a))
        # print(row)
        list_csv.append(row)
    dataframe_csv = pd.DataFrame(list_csv).applymap(str)
    print(dataframe_csv)

    writer = pd.ExcelWriter(x_path)
    # 写入excel
    dataframe_csv.to_excel(excel_writer=writer, index=False, header=False)
    # index=True表示列出行名，header表示列出列名
    # dataframe_csv.to_excel(excel_writer=writer, index=True, header=True)
    writer.save()


if __name__ == '__main__':
    importlib.reload(sys)
    # 加 r确保后面的符号为普通字符，如\n不会换行
    # dir：待处理文件放的目录
    dir = r'E:\研究生文档\助管\梁老师\37'
    c_path = r'E:\研究生文档\助管\梁老师\37跑出来的数据\学_专_成绩汇总.csv'
    x_path = r'E:\研究生文档\助管\梁老师\37跑出来的数据\学_专_成绩汇总.xlsx'
    # xlsToCsv(dir, c_path)
    csvToXls(c_path, x_path)
    # 用pandas将csv--->xls
    csvToXls2(c_path, x_path)