Python-Pandas方法 本文由“壹伴编辑器”提供技术支持 作者:E精精 公众号:作者:Excel办公实战 日期:20201111 功能:Excel多文件汇总 ''' import pandas as pd import os import time # 开始时间 start =time.clock() # 文件所在目录 base_path = r'D:\360MoveData\Users\10712\Desktop\Data' # 用于存放文件的list df_list = [] # 遍历文件 for excelfile in os.listdir(base_path): # 读取到dataframe df_excel = pd.read_excel(os.path.join(base_path,excelfile)) # 存放到list df_list.append(df_excel) # 合并到一起 pd_res = pd.concat(df_list) # 结果行数 print(len(pd_res)) # 保存到excel pd_res.to_excel('res.xlsx',index=False) end = time.clock() print("总耗时:{:.2f}s".format(end-start)) VBA方法 '作者:E精精 '功能:多文件合并 '============================================================== Sub 多文件合并() Dim sPath As String '接收要合并的文件夹路径 '-----------选择要合并的文件件----------------- With Application.FileDialog(msoFileDialogFolderPicker) .Title = "请选择要合并的文件夹" .InitialFileName = ThisWorkbook.Path .AllowMultiSelect = False If .Show Then sPath = .SelectedItems(1) End If End With '-----------遍历合并-------------------------------- Dim wb As Workbook, ws As Worksheet Dim fileCount As Integer, maxRow As Integer Dim iRow As Integer, iCol As Integer Dim sFile As String Set ws = ThisWorkbook.Sheets(1) Dim sht As Worksheet '清空历史内容,防重复 ws.Cells.ClearContents Application.ScreenUpdating = False If sPath <> "" Then sPath = sPath & "\" sFile = Dir(sPath & "*.xls?") Do While Len(sFile) > 0 fileCount = fileCount + 1 Set wb = Workbooks.Open(sPath & sFile) Set sht = wb.Sheets(1) '数据的行列数 With sht.Range("a1").CurrentRegion iRow = .Rows.Count iCol = .Columns.Count End With If ws.Range("a1") = "" Then '第一次粘贴 ws.Range("a1").Resize(iRow, iCol).Value = _ sht.Range("a1").CurrentRegion.Value Else maxRow = ws.Cells(Rows.Count, 1).End(3).Row + 1 ws.Cells(maxRow, 1).Resize(iRow - 1, iCol).Value = _ sht.Range("a2").Resize(iRow - 1, iCol).Value End If wb.Close 0 sFile = Dir Loop End If Application.ScreenUpdating = True MsgBox "合并完成,共合并" & fileCount & "个文件", vbOKOnly, "提示" End Sub Power Query 本文由“壹伴编辑器”提供技术支持 // 存放文件的路径 base_path = "D:\360MoveData\Users\10712\Desktop\Data", file_list = Folder.Files(base_path), // 提取首个工作表 trans_excel = List.Transform(file_list[Content],each Excel.Workbook(_,true){0}[Data]), // 合并 combine_file = Table.Combine(trans_excel) in combine_file 本文由“壹伴编辑器”提供技术支持 本文由 “壹伴编辑器”提供技术支持 |
|