1. 同一目录下的多工作簿，每个工作簿中有一张或多张工作表，一键全部汇总
 
Sub 合并目录所有工作簿全部工作表()

Dim MP, MN, AW, Wbn, wn

Dim Wb As Workbook

Dim i, a, b, d, c, e

Application.ScreenUpdating = False

MP = ActiveWorkbook.Path

MN = Dir(MP & "\" & "*.xls")

AW = ActiveWorkbook.Name

Num = 0

e = 1

Do While MN <> ""

If MN <> AW Then

Set Wb = Workbooks.Open(MP & "\" & MN)

a = a + 1

With Workbooks(1).ActiveSheet

For i = 1 To Sheets.Count

If Sheets(i).Range("a1") <> "" Then

Wb.Sheets(i).Range("a1").Resize(1, Sheets(i).UsedRange.Columns.Count).Copy .Cells(1, 1)

d = Wb.Sheets(i).UsedRange.Columns.Count

c = Wb.Sheets(i).UsedRange.Rows.Count - 1

wn = Wb.Sheets(i).Name

.Cells(1, d + 1) = "表名"

.Cells(e + 1, d + 1).Resize(c, 1) = MN & wn

e = e + c

Wb.Sheets(i).Range("a2").Resize(c, d).Copy .Cells(.Range("a1048576").End(xlUp).Row + 1, 1)

End If

Next

Wbn = Wbn & Chr(13) & Wb.Name

Wb.Close False

End With

End If

MN = Dir

Loop

Range("a1").Select

Application.ScreenUpdating = True

MsgBox "共合并了" & a & "个工作薄下全部工作表。如下：" & Chr(13) & Wbn, vbInformation, "提示"

End Sub

 
2. 同一工作簿中的多工作表，将指定的多工作表一键汇总
 
Sub huizongdata()

    Rows("2:10000").Clear
     '第一步是先清空汇总表的数据，这里是从第2行开始清理到10000行，可以自己修改。
     
    Application.Wait Now + TimeValue("00:00:01")   '延迟1秒
    
    Dim st As Worksheet, rng As Range, rrow As Integer, i As Integer
    
    '定义一些需要用到的变量
    
    'For Each st In Worksheets  循环开始，遍历所有的工作表
    For i = 3 To Worksheets.Count
    
        Set st = Sheets(i)
        
        Set rng = Range("A10000").End(xlUp).Offset(1, 0)
        
        '每次循环就动态定位需要拷贝数据的区域，即获得A列第一个空的单元格
        
        rrow = st.Range("A2").CurrentRegion.Rows.Count - 1
        
        '获得每个工作表中的数据记录数，即行数，同时需要减去表头的行数，这里是减掉2行。
        
        st.Range("A2").Resize(rrow, 9).Copy rng
        
        '将数据扩展rows行，4列拷贝到数据，并粘贴到汇总表
    
    Next i
   

End Sub
 
 

Python办公自动化实践1：从多个excel表中提取数据并汇总到一个工作表页中,表格,抽取,sheet
 
发表时间：2020-04-26
 
问题：从当前目录或子目录中查询符合条件的excel表格，并从这些excel表格中抽取符合条件的行汇总到1个excel的sheet页中。
 所有excel表格名字为：交付清单1、交付清单2，交付清单3……，格式也一样，样式如下：
 将类似多个excle表中，抽取序号中的N行（上图是1~4行）汇总到指定excel的sheet页中。
 该表格在DataFrame中的显示方式如下：

 一、希望得到的汇总表格为：
 1、列头是：“系统名称、用途 、CPU 内存、操作系统、内网IP、 互联网IP 、交付时间 、到期时间”
 注意：列头（columns header)是合并格。
 2、删除掉A列，删除最后的空行
 3、将交付时间，到期时间从datetime类型转变为字符串类型； #第三方模块datetime
 4、将汇总表格中所有的单元格 字体，大小，字体是否据中等 全部调为一致；#第三方模块xlsxwriter
 
二、具体实现步骤如下：
 1、查询符合条件的excel表； #第三方模块os
 2、用pandas读取符合条件的表；
 3、对读入后的excel表（dataframe）进行裁剪；
 4、将多个excel表格汇总到单一excel的sheet页中；
 5、将最终的sheet页再进行单元格格式调整，再重新输出新的sheet页；
 该脚本涉及第三方模块有：pandas,os,datetime,xlsxwriter。
 
三、代码展现
 1、查询符合条件的excel表
 a、通过os.walk查询当前目录及子目录中的文件
 b、在这些文件中筛选匹配“交付清单”的文件
 c、将匹配文件和绝对路径“连接后”存放到列表中；
 
import numpy as np 
import pandas as pd
import xlsxwriter 
import os
from pandas import Series,DataFrame
from datetime import date,timedelta,datetime

path=r'D:\cloud_files'
path_out=r'D:\cloud_files\vmachines_list.xlsx'

substr='交付清单'
file_list=[]
for path_name,dirs,filename in os.walk(path):
    for files in filename:
        if files.find(substr)!=-1:
            file_list.append(os.path.join(path_name,files))
 
该段重点：
 a、path_name 存放文件所在的绝对路径，filename 存放文件名；
 b、files.find(substr) 匹配substr字符串的文件，若不匹配返回-1，
 若匹配，返回该字符串在文件名中第一次匹配成功的位置。
 c、os.path.join(path_name,files)将绝对路径与匹配的文件结合起来,再存放到file_list列表中。
 
file_list列表中的数据：
 
['D:\\cloud_files\\~$交付清单01.xlsx', 'D:\\cloud_files\\交付清单01.xlsx', 'D:\\cloud_files\\交付清单02.xlsx', 'D:\\cloud_files\\交付清单03.xlsx']
 
在交付清单01.xlsx 是打开的情况下，提交该段程序，将会出现~$交付清单01.xlsx。
 表示该表被进程占用。
 
2、用pandas读取符合条件的表
 a、通过pandas读取在file_list中的excel表格；
 b、并将结果写入到vm_tmplist 临时列表中。
 
vm_tmplist=[]
for item in file_list:
    if '~$' not in item:
        temp=pd.read_excel(item,sheet_name='交付清单',skiprows=2,usecols='B:J')
        vm_tmplist.append(temp)
 
该段重点：
 a、跳过excel中的前两行，并定义B到J列区域；
 
3、对读入后的excel表（dataframe）进行裁剪
 a、选择合适的列头；
 b、删除掉A列，删除最后的空行
 c、将交付时间，到期时间从datetime类型转变为字符串类型；
 转换的原因是xlsxwriter不支持对时间类型，index，columns类型的单元格进行格式化。
 官网原文：It isn’t possible to format any cells that already have a format
 such as the index or headers or any cells that contain dates or datetimes .
 
def trim_frame(df):
    df.columns=np.concatenate([df.columns[:3],df.iloc[0,3:5],df.columns[5:]])
    df=df.dropna(subset=['系统名称'])
    df=df.loc[(df['序号'].isin(range(1,10,1)))]
    df['交付时间']=pd.to_datetime(df['交付时间'],errors='coerce').dt.strftime('%Y-%m-%d')
    df['到期时间']=pd.to_datetime(df['到期时间'],errors='coerce').dt.strftime('%Y-%m-%d')
    return df
    
for value in vm_tmplist:
    df=trim_frame(value)
    vm_trimlist.append(df)
 
本段重点：
 a、将前三列（0，1，2）的columns（列名）与第0行的第3、4列及columns第5列至最后一列合并；
 通过numpy.concatenate函数合并
 b、将“系统名称”列中空格（NaN）所在的行去掉；
 c、选取“序号”列中数字为1~10所在的行；
 d、将“交付时间”，“到期时间”两列datetime类型数据转换为string。
 e、将“修剪好的”表存入vm_trimlist列表中；
 
4、将多个excel表格汇总到单一excel的sheet页中
 a、调用xlsxwriter引擎；
 b、从vm_list列表中选取“修剪好”的数据，按顺序写入到“虚机清单”sheet页中；
 c、保存写好后的数据.writer.save()
 
def trimDfs_to_Excel(df_list, sheets, path_out):
    writer = pd.ExcelWriter(path_out,engine='xlsxwriter')   
    row = 0
    for dataframe in df_list:
        dataframe.to_excel(writer,sheet_name=sheets,startrow=row,startcol=0,index=False) 
        row = row + len(dataframe.index) + 1
    writer.save()
    
trimDfs_to_Excel(vm_trimlist,'虚机清单',path_out)
 
本段重点：
 a、从vm_trimlist中读取数据（注：df_list是形参）
 b、定义row=0，开始写的行startrow=row。
 第2张表写入时，启始位置是第1张表的长度len(dataframe.index) 加1（第一行是从0开始的，所以加1，避免第2张表第一行冲掉第一张表最后一行）
 c、to_excel（index=False）是防止将索引（index）写入excel表内。
 
5、将最终的sheet页再进行单元格格式调整，重新输出新的sheet页；
 a、重新读取输出的表；
 b、读取后，将该表删掉；因为xlsxwriter不能对原表进行修改；
 c、对单元格及列头（columns header）进行格式化；
 d、重新输出新表
 
def formatExcel(df,path):

    df=df.loc[df['序号'].isin(range(1,10,1))]
    os.remove(path)
    df.reset_index(drop=True,inplace=True)
    
    for i in df.index:
        df['序号'].at[i]=i+1

    writer = pd.ExcelWriter(path,engine='xlsxwriter')
    df.to_excel(writer,sheet_name='虚机清单',index=False)
    
    workbook=writer.book
    worksheet=writer.sheets['虚机清单']
    fmt_cell={'bold':False,'font_name':'微软雅黑','font_size':9,'align':'center','valign':'vcenter','border':0,'num_format':'#,##0'}
    fmt_header={'bold':True,'font_name':'微软雅黑','font_size':10,'align':'center','valign':'vcenter','border':0}

    cell_format=workbook.add_format(fmt_cell)
    header_format=workbook.add_format(fmt_header)

    worksheet.set_column('A:I',15,cell_format)

    for colx,value in enumerate(df.columns.values):
        worksheet.write(0,colx,value,header_format)

    writer.save()
    
formatExcel(pd.read_excel(path_out),path_out)
print ('Done!')
 
该段重点：
 a、对序号列重新赋值新的数字，df[‘序号’].at[i]=i+1；
 b、定义单元格（cell）格式；
 c、定义列头的格式，通过worksheet.write的方式；
 
生成的新表：

   使用这个工具可以去表头合并，需要格式一样，在线工具无需安装。地址：www.excelfb.com ,
 
    如果是按Sheet名称合并点击：  批量合并  中的   多个Excel文件合并（相同名称的Sheet表合并
 
    如果是所有的合并成一个表点击： 批量合并  中的   多个Excel文件多个Sheet合并（合并到一个Sheet）
 
    填入从第几行开始，即可跳过表头合并。支持多个Excel文件，多个Sheet表（多个工作簿）的合并。
 
   工具界面如下图：
 
 
    
 
 
 
   
 
 

本文的文字及图片来源于网络,仅供学习、交流使用,不具有任何商业用途,版权归原作者所有,如有问题请及时联系我们以作处理
 
以下文章来源于财会学习联盟，作者：我是刀哥啊
 
 
 
 
私信回复“资料”，即可免费领取Python实战案例讲解视频
 
Python 自动化办公，处理Excel表格视频讲解
 
https://www.bilibili.com/video/BV1Lh411R7Kd/
 
前言
 
这篇文章要做的事情，如标题所述，就是提取多张excel表上的数据或信息，合并汇总到一张新表上，这是我们工作中经常会遇到的事情。
 
比如将每月销售情况汇总到一张表上进行销售情况分析，比如将各月发票信息汇总到一张表上进行统计分析，还比如将每月工资表上的某些信息汇总到一张表上进行工资成本分析等等。
 
具体看表即为：
 
 
 
 
 
 
 
 
 
 
 
 
 
 
各期科目余额表截图
 
最后得到的新表为：
 
 
要实现上述目标，可以分如下四步进行。
 
1.获取各科目余额表文件路径
 
将2017年1-12月、2018年1-12月、2019年1-12月及2020年1-6月各期科目余额表放在同一文件夹下，要读取多少个文件，就把多少个文件全部放在同一个文件夹下，如下图。
 
 
 
 
然后读取所有文件的路径，代码如下。
 
1dir_xls = []
2def get_file(folder_path):  #获取同一文件夹下所有科目余额表各自的文件路径
3    dir_file = os.listdir(folder_path)
4    #print(dir_file)  
5    for path in dir_file:
6        if path[-4:] == 'xlsx' or path[-3:] == 'xls':
7            whole_path = r'd:/F：学习/python/账龄分析/科目余额表/{}'.format(path)
8            dir_xls.append(whole_path)
9    return dir_xls
 
2.获取各科目余额表中应收账款一级科目编码所在的行列
 
比如在2017年科目余额表中，应收账款一级科目编码为“1122”，其所在的单元格为C12，也即为第12行第3列，这里的行号12、列号3，即为其定位。
 
 
 
 
其余科目余额表同理，均为获取一级科目编码“1122”的行号和列号，获取代码如下。
 
1dict_row_col = {}
 2def get_row_col(dir_xls):  #获取每一张表中应收账款一级科目编码所在的行号和列号
 3    for i in dir_xls:
 4        #print(i)
 5        account_balance_sheet_data = pd.DataFrame(pd.read_excel(i))
 6        for a in account_balance_sheet_data.index:
 7            for b in range(len(account_balance_sheet_data.loc[a].values)):
 8                if account_balance_sheet_data.loc[a].values[b] == '1122':
 9                    row = a+1
10                    col = b+1
11                    dict_row_col[i] = [row,col]
12    return dict_row_col
 
3.获取各科目余额表中应收账款所有二级科目编码
 
根据获取到的应收账款一级科目编码行号和列号，即根据其定位，再获取每一张表中应收账款所有二级科目编码，并将其不重复且升序排列添加到一张新表中，代码如下。
 
1def get_ar_code(dict_row_col):
 2    i = 0
 3    ar_list1 = []
 4    ar_list2 = []
 5    for key in dict_row_col.keys():
 6        workbook = xlrd.open_workbook(key)
 7        balance_sheet = workbook.sheet_by_index(0)
 8        row = dict_row_col[key][0]
 9        col = dict_row_col[key][1]
10        while True:
11            if '1122' in balance_sheet.cell_value(row+1,col-1):
12                ar_code = balance_sheet.cell_value(row+1,col-1)
13                if ar_code not in ar_list1:
14                    ar_list1.append(ar_code)
15                else:
16                    pass                
17                row = row+1                
18            else:
19                break
20    ar_list1.append('科目编码')    
21    ar_list1.sort(reverse=False) #科目编码列表升序排列
22    #将“科目编码”从最后一个元素整体移动到第一个元素
23    ar_list2.append(ar_list1[len(ar_list1)-1]) 
24    for i in range(1,len(ar_list1)):
25        ar_list2.append(ar_list1[i-1])
26    #将所有元素写入到excel表中
27    for i in range(len(ar_list2)):
28        ar_sheet.write(i,0,ar_list2[i])
 
得到的新表内容如下。
 
 
 
 
由上可看出，2017年至2020年1-6月，四张科目余额表，应收账款共有617个二级科目，对应着617个不同的客户。
 
4.根据二级科目索引获取全部所需信息
 
此步的操作过程，即上一篇《如何用python实现excel中的vlookup功能？》所分享的过程，这里就不再详述了，代码如下。
 
1def get_ar_info(dict_row_col):
 2    #读取导入目标表
 3    file_target = r'd:\F：学习\python\账龄分析\AR.xls'
 4    list_ar_code = []
 5    workbook = xlrd.open_workbook(file_target)
 6    balance_sheet = workbook.sheet_by_index(0)
 7    rows = balance_sheet.nrows
 8    for i in range(1,rows):
 9        list_ar_code.append(balance_sheet.cell_value(i,0))
10    #print(list_ar_code)
11    data = {'科目编码':list_ar_code}
12    df_target = pd.DataFrame(data)
13
14    for key in dict_row_col.keys():
15        #读取原始数据来源表
16        file_source = key
17        df_source = pd.read_excel(file_source)
18        #将原始数据来源表及导入目标表信息合并到同一表上
19        dfneed = df_source[['科目编码','科目名称','期初借方','期初贷方','本期发生借方','本期发生贷方','期末借方','期末贷方']]
20        df_target = pd.merge(df_target,dfneed,how='left',on='科目编码')
21    df_target.to_excel(file_target,index=False)
 
5.最终目标实现
 
前四步即为封装的四个函数，每个函数为其中一个步骤，最终汇总可以实现此文总体目标，调用代码及运行代码如下。
 
1import os
 2import pandas as pd
 3import xlrd,xlwt
 4
 5folder_path = r'd:\F：学习\python\账龄分析\科目余额表'
 6f = xlwt.Workbook()
 7ar_sheet = f.add_sheet(u'ar_sheet',cell_overwrite_ok=True)
 8dir_xls = get_file(folder_path)
 9dict_row_col = get_row_col(dir_xls)
10get_ar_code(dict_row_col)
11f.save(r'd:\F：学习\python\账龄分析\AR.xls')
12get_ar_info(dict_row_col)
 
运行后生成的表格如下。
 
 
 
 
再经过简单整理后，便可得出上文最终表格，至此实现了从多张excel表中提取所需数据或信息并汇总到同一张新表上的目的。