Python汇总指定文件下所有excel文件第二节

Python批量数据汇总

---

前言

提示：以下是本篇文章正文内容，下面案例可供参考，源码可直接使用，需要源数据可评论联系我；

一、Python汇总文件夹下所有文件

汇总文件夹下所有excel的第一张工作表内容到新工作表。

1.源码

代码如下（示例）：

# coding:utf-8

import xlrd
import os
import xlwt
from xlutils.copy import copy

"""
将文件夹下所有excel文件合并成一个文件
注意：
    本代码仅支持合并excel文件第一个sheet，如果合并的excel文件有多个sheet，只会读取和合并第一个sheet,
    需要合并的excel文件如果有多个sheet需要修改代码的merge_excel()函数
思路：
    1.获取路径下所有文件，注意 本代码没有异常处理
    2.新建一个excel文件，用于存储全部数据
    3.逐个打开需要合并的excel文件，逐行读取数据，再用一个列表来保存每行数据。最后该列表中会存储所有的数据
    4.向excel文件中逐行写入
"""


def get_allfile_msg(file_dir):
    for root, dirs, files in os.walk(file_dir):
        '''
        print(root) #当前目录路径  
        print(dirs) #当前路径下所有子目录  
        print(files) #当前路径下所有非目录子文件 
        '''
        return root, dirs, [file for file in files if file.endswith('.xls') or file.endswith('.xlsx')]


def get_allfile_url(root, files):
    """
    将目录的路径加上'/'和文件名，组成文件的路径
    :param root: 路径
    :param files: 文件名称集合
    :return: none
    """
    allFile_url = []
    for file_name in files:
        file_url = root + '/' + file_name
        allFile_url.append(file_url)
    return allFile_url


def all_to_one(root, allFile_url, file_name='allExcel.xls', title=None, have_title=True):
    """
    合并文件
    :param root: 输出文件的路径
    :param allFile_url: 保存了所有excel文件路径的集合
    :param file_name: 输出文件的文件名
    :param title: excel表格的表头
    :param have_title: 是否存在title(bool类型),默认为true，不读取excel文件的第0行
    :return: none
    """
    # 首先在该目录下创建一个excel文件,用于存储所有excel文件的数据
    file_name = root + '/' + file_name
    create_excel(file_name, title)

    list_row_data = []
    for f in allFile_url:
        # 打开excel文件
        print('打开%s文件' % f)
        excel = xlrd.open_workbook(f)
        # 根据索引获取sheet，这里是获取第一个sheet
        table = excel.sheet_by_index(0)
        print('该文件行数为：%d，列数为：%d' % (table.nrows, table.ncols))

        # 获取excel文件所有的行
        for i in range(table.nrows):
            # 如果存在表头，则跳过第0行，否则不跳过
            if have_title and i == 0:
                continue
            else:
                row = table.row_values(i)  # 获取整行的值，返回列表
                list_row_data.append(row)

    print('总数据量为%d' % len(list_row_data))
    # 写入all文件
    add_row(list_row_data, file_name)


# 创建文件名为file_name,表头为title的excel文件
def create_excel(file_name, title):
    print('创建文件%s' % file_name)
    a = xlwt.Workbook()
    # 新建一个sheet
    table = a.add_sheet('sheet1', cell_overwrite_ok=True)
    # 写入数据
    for i in range(len(title)):
        table.write(0, i, title[i])
    a.save(file_name)


# 向文件中添加n行数据
def add_row(list_row_data, file_name):
    # 打开excel文件
    allExcel1 = xlrd.open_workbook(file_name)
    sheet = allExcel1.sheet_by_index(0)
    # copy一份文件,准备向它添加内容
    allExcel2 = copy(allExcel1)
    sheet2 = allExcel2.get_sheet(0)

    # 写入数据
    i = 1
    for row_data in list_row_data:
        for j in range(len(row_data)):
            sheet2.write(sheet.nrows + i, j, row_data[j])
        i += 1
    # 保存文件，将原文件覆盖
    allExcel2.save(file_name)
    print('合并完成')


if __name__ == '__main__':
    # 设置文件夹路径，
#    file_dir = 'D:\SoftWare\PythonWorkSpace\excel-test'
    file_dir = r'E:\py\python3.7\test\test01\3'
    # 获取文件夹的路径,该路径下的所有文件夹，以及所有文件
    root, dirs, files = get_allfile_msg(file_dir)
    # 拼凑目录路径+文件名,组成文件的路径,用一个列表存储
    allFile_url = get_allfile_url(root, files)
    # 设置文件名，用于保存数据
    file_name = 'output.xls'
    # 设置excle文件表头
    title = ['a', 'b', 'c', 'd']
    # have_title参数默认为True,为True时不读取excel文件的首行
    all_to_one(root, allFile_url, file_name=file_name, title=title, have_title=True)

二、Python合并所有工作表内容

1.源码

代码如下（示例）：

import xlwings as xw

#1.新增工作表
def add_sheet():
    app=xw.App(visible=True,add_book=False)
    #1.打开原工作簿
    workbook = app.books.open("合并工作表.xlsx")
    #2.新增表格
    workbook.sheets.add("合并工作表")

    return workbook
    

   
#2.获取每个表中的最后位置信息
def get_sheet_value(workbook):
    listsht = workbook.sheets #获取所有工作表
    list_address = []   #创建一个空列表，放置每个表的最后位置信息

    for sheet in listsht:
        if sheet.name != "合并工作表":
            address_all = sheet.used_range.address.split("$")[-1]
            list_address.append(int(address_all))
            
    return list_address

#3.根据位置，将数值放进合并的表格中
def set_merge_sheet(workbook,list_address):
    #先写第一行数据
    workbook.sheets["合并工作表"].range("A1:E1").value = workbook.sheets[1].range("A1:E1").value
    
    #循环写入数值
    for address_one in list_address:
        #获得每一个表格对象
        sht = workbook.sheets[list_address.index(address_one)+1]

        str_address = "A2:"+"E"+str(address_one)  
        #获得每一个表格的值
        sht_value = sht.range(str_address).value

        #创建合并的表格对象
        sheet_merge = workbook.sheets["合并工作表"]

        #将这个值放到合并工作表中
        if list_address.index(address_one) == 0:
            sheet_merge.range(str_address).value = sht_value
        else:
            #获取合并单元格当前的有效区域
            str_name = sheet_merge.used_range.address.split("$")[-1]
            a_address = "A"+str(int(str_name)+1)+":"
            b_address = "E"+ str(int(str_name)+1+address_one)
            sheet_merge.range(a_address+b_address).value = sht_value

#4.保存和退出程序
def sava_close(workbook):
    workbook.save()
    workbook.close()
    


#python程序执行入口
if __name__ == "__main__":

    #1.新增工作表,返回workbook对象
    obj_workbook = add_sheet()

    #2.获取每个表最后的位置信息
    list_address =get_sheet_value(obj_workbook)

    #3.根据位置，将数值放进合并的表格中
    set_merge_sheet(obj_workbook,list_address)

    #4.保存和退出程序
    sava_close(obj_workbook)

    
    

三、Python合并不同(相同)文件夹指定行数据

提示：分别对应下面的源码1-4；901文件夹数据和906文件夹数据(其中906文件夹中的数据汇总的指定行数据可以与901中的是不同行，如果是相同行数据可直接使用源码一即可)；

1.源码一

提示：合并第一个文件夹下相同格式文件指定行数据到output.xls文件；

代码如下（示例）：

# coding:utf-8

import xlrd  #导入读取excel的模块，import语句用来导入其它python文件，称为module模块，使用该模块里定义的类，方法，或者变量，从而达到代码复用的目的
import os
import pandas as pd
import xlwt
from xlutils.copy import copy  #xlutils模块的功能是xlrd和xlwt的桥梁，解决了xlrd中book对象无法编辑的问题，通过copy模块将xlrd.Book对象转换为xlwt.Workbook对象，从而实现原始excel文件的编辑功能。

"""
将文件夹下所有excel文件合并成一个文件
注意：
    本代码仅支持合并excel文件第一个sheet，如果合并的excel文件有多个sheet，只会读取和合并第一个sheet,
    需要合并的excel文件如果有多个sheet需要修改代码的merge_excel()函数
思路：
    1.获取路径下所有文件，注意 本代码没有异常处理
    2.新建一个excel文件，用于存储全部数据
    3.逐个打开需要合并的excel文件，逐行读取数据，再用一个列表来保存每行数据。最后该列表中会存储所有的数据
    4.向excel文件中逐行写入
"""


def get_allfile_msg(file_dir):
    for root, dirs, files in os.walk(file_dir):
        '''
        print(root) #当前目录路径  
        print(dirs) #当前路径下所有子目录  
        print(files) #当前路径下所有非目录子文件 
        '''
        return root, dirs, [file for file in files if file.endswith('.xls') or file.endswith('.xlsx')]


def get_allfile_url(root, files):
    """
    将目录的路径加上'/'和文件名，组成文件的路径
    :param root: 路径
    :param files: 文件名称集合
    :return: none
    """
    allFile_url = []
    for file_name in files:
        file_url = root + '/' + file_name
        allFile_url.append(file_url)
    return allFile_url


def all_to_one(root, allFile_url, file_name='allExcel.xls', title=None, have_title=True):
    """
    合并文件
    :param root: 输出文件的路径
    :param allFile_url: 保存了所有excel文件路径的集合
    :param file_name: 输出文件的文件名
    :param title: excel表格的表头
    :param have_title: 是否存在title(bool类型),默认为true，不读取excel文件的第0行
    :return: none
    """
    # 首先在该目录下创建一个excel文件,用于存储所有excel文件的数据
    file_name = root + '/' + file_name
    create_excel(file_name, title)

    list_row_data = []
    for f in allFile_url:
        # 打开excel文件
        print('打开%s文件' % f)
        excel = xlrd.open_workbook(f)
        # 根据索引获取sheet，这里是获取第一个sheet
        table = excel.sheet_by_index(0)
        print('该文件行数为：%d，列数为：%d' % (table.nrows, table.ncols))

        # 获取excel文件所有的行
#        for i in range(table.nrows):
#        table.nrows=(22,23,25,26)
        for i in range(28,table.nrows):
            # 如果存在表头，则跳过第0行，否则不跳过
            if have_title and i == 0:
                continue
            else:
                row = table.row_values(11)  # 获取整行的值，返回列表
                list_row_data.append(row)
                row = table.row_values(12)  # 获取整行的值，返回列表
                list_row_data.append(row)				
                row = table.row_values(20)  # 获取整行的值，返回列表
                list_row_data.append(row)
                row = table.row_values(21)  # 获取整行的值，返回列表
                list_row_data.append(row)
                row = table.row_values(23)  # 获取整行的值，返回列表
                list_row_data.append(row)
                row = table.row_values(24)  # 获取整行的值，返回列表
                list_row_data.append(row)
                row = table.row_values(26)  # 获取整行的值，返回列表
                list_row_data.append(row)

    print('总数据量为%d' % len(list_row_data))
    # 写入all文件
    add_row(list_row_data, file_name)



# 创建文件名为file_name,表头为title的excel文件
def create_excel(file_name, title):
    print('创建文件%s' % file_name)
    a = xlwt.Workbook()
    # 新建一个sheet
    table = a.add_sheet('sheet1', cell_overwrite_ok=True)
    # 写入数据
    for i in range(len(title)):
        table.write(0, i, title[i])
    a.save(file_name)


# 向文件中添加n行数据
def add_row(list_row_data, file_name):
    # 打开excel文件
    allExcel1 = xlrd.open_workbook(file_name)
    sheet = allExcel1.sheet_by_index(0)
    # copy一份文件,准备向它添加内容
    allExcel2 = copy(allExcel1)
    sheet2 = allExcel2.get_sheet(0)

    # 写入数据
    i = 0
    for row_data in list_row_data:
        for j in range(len(row_data)):
            sheet2.write(sheet.nrows + i, j, row_data[j])
        i += 1
    # 保存文件，将原文件覆盖
    allExcel2.save(file_name)
    print('合并完成')
	
	#df = pd.read_excel(r'C:\Users\gxcaoty\AppData\python3.7\test\test01\4\output.xls')  # 读取需要转置的文件
	#df = df.T  # 转置
	#df.to_excel(r'C:\Users\gxcaoty\AppData\python3.7\test\test01\4\TestResult.xls')  # 另存为xlsx文件
	


if __name__ == '__main__':
    # 设置文件夹路径，
#    file_dir = 'E:\py\python3.7\test\test01\8'
    file_dir = r'E:\py\python3.7\test\test01\14\901'
    # 获取文件夹的路径,该路径下的所有文件夹，以及所有文件
    root, dirs, files = get_allfile_msg(file_dir)
    # 拼凑目录路径+文件名,组成文件的路径,用一个列表存储
    allFile_url = get_allfile_url(root, files)
    # 设置文件名，用于保存数据
    file_name = 'output.xls'
    # 设置excle文件表头
    title = ['UA3P', '检测项目','管控标准','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','日期','合格率']
    # have_title参数默认为True,为True时不读取excel文件的首行
    all_to_one(root, allFile_url, file_name=file_name, title=title, have_title=True)
	
	
	
	
	
	
	

2.源码二

提示：合并第二个文件夹下相同格式文件指定行数据到output.xls文件(此处的行数据与第一个文件夹下的行可以不是同一行，可自行定义)；

代码如下（示例）：

import os	
import openpyxl
import xlwt,xlrd
import pandas as pd
from xlutils.copy import copy



#path = r'E:\py\python3.7\test\test01\14\906'
#filenames = os.listdir(path)
#for filename in filenames:
#   print(filename)   

#wb_temp=xlrd.open_workbook(filename,'rb') # 打开待复制的表
wb_temp=xlrd.open_workbook(r'E:\py\python3.7\test\test01\14\906\1.xls') # 打开待复制的表
sheets = wb_temp.sheet_names() # 获取工作簿中的所有工作表名字，形成列表元素
#print(sheets)#打印查看一下aa文件中存在的sheet页名字列表
sheet1 = wb_temp.sheet_by_index(0) # 根据索引获取第一个sheet #打印查看获得的sheet，所有工作表中的的第一个工作表，是一个对象
#print(sheet1)

row1=sheet1.row_values(11)
row2=sheet1.row_values(12)
row3=sheet1.row_values(19)
row4=sheet1.row_values(21)
row5=sheet1.row_values(23)
row6=sheet1.row_values(24)
row7=sheet1.row_values(26)
 
#wb_all=xlrd.open_workbook(r'E:\py\python3.7\test\test01\14\901\output.xls')#打开待粘贴的表
 
#sheets = wb_all.sheet_names()  # 获取工作簿中的所有表格
#sheet2 = wb_all.sheet_by_name(sheets[0])  # 获取工作簿中所有表格中的的第一个sheet
#k=sheet2.nrows #获取表中已经有的数据行数
workbook = xlrd.open_workbook(r'E:\py\python3.7\test\test01\14\901\output.xls') # 打开工作簿
sheets = workbook.sheet_names()  # 获取工作簿中的所有表格
sheet2 = workbook.sheet_by_name(sheets[0])  # 获取工作簿中所有表格中的的第一个sheet
k=sheet2.nrows #获取表中已经有的数据行数
new_workbook = copy(workbook) # 将xlrd对象拷贝转化为xlwt对象
new_worksheet = new_workbook.get_sheet(0) # 获取转化后工作簿中的第一个工作表对象


for i,content in enumerate(row1):
   new_worksheet.write(36,i,content)
for i,content in enumerate(row2):
   new_worksheet.write(37,i,content)
for i,content in enumerate(row3):
   new_worksheet.write(38,i,content)
for i,content in enumerate(row4):
   new_worksheet.write(39,i,content)
for i,content in enumerate(row5):
   new_worksheet.write(40,i,content)
for i,content in enumerate(row6):
   new_worksheet.write(41,i,content)
for i,content in enumerate(row7):
   new_worksheet.write(42,i,content)

new_workbook.save('data.xls')  # 保存工作簿
 

3.源码三

提示：将两个文件夹合并后的数据进行转置输出；

代码如下（示例）：

import numpy as np
import pandas as pd
df = pd.read_excel(r'E:\py\python3.7\test\test01\14\data.xls',dtype=None,converters={'':str})  # 读取需要转置的文件
#df = df.values
#np.fromfile('output',dtypes=np.int)
df = df.T  # 转置
#df['1']=df['1'].astype('int')
df.style.number_format_str = '0.000'
#for cell in df()
#	values = int(cell,values)
#	cell.number_format = '0.000'
#df.write_number()
#df = df.applymap(lambda x:'{:.2f}'.format(x)).applymap(lambda x:eval(x))
#print(df.dtypes)
#df = df.to_numeric(df,errors='coerce')
#df = df.drop(df[df[55].astype(float).isnull()].index)
#df = df.iloc[:,:].values
#df = df.values



df.to_excel(r'E:\py\python3.7\test\test01\14\TestResult.xlsx')  # 另存为xlsx文件

4.源码四

提示：将执行命令汇总运行；

代码如下（示例）：

import os
os.system("python ./ACCNT.py")
os.system("python ./ACCNT1.py")
os.system("python ./ACCNT2.py")

该处使用的url网络请求的数据。

---

总结

提示：相互学习，欢迎评论留言。