合并/拆分 Excel？Python、VBA轻松自动化

最新推荐文章于 2023-11-14 23:59:04 发布

AI科技大本营

最新推荐文章于 2023-11-14 23:59:04 发布

阅读量2.4k

点赞数

文章标签： os git excel csv 反编译

这篇博客介绍了如何使用VBA和Python来快速合并及拆分Excel表格。对于合并，提供了VBA代码，可以直接在Excel中运行，将同一目录下的所有Excel文件整合到一个工作簿中。而对于拆分，同样给出了VBA和Python的解决方案，可以将一个大表按行数拆分为多个小表。这些技巧大大提高了办公自动化效率。

摘要由CSDN通过智能技术生成

作者 | Ryoko

来源 | 凹凸数据

当你收集了 n 个人的 EXCEL 记录表，需要将它们汇成一个总表时你会怎么做呢？

如果不通过技术手段，要一个个打开再复制粘贴也太麻烦了吧！

此时就需要一个通过几秒钟的点击就能完成合并的工具。

最终成品

合并 EXCEL

VBA 实现合并

不套路，下面直接放出 VBA 代码（来源于网络，经过了我修改）：

Sub 合并当前目录下所有工作簿的全部工作表()Dim MyPath, MyName, AWbNameDim Wb As Workbook, WbN As StringDim G As LongDim Num As LongDim BOX As StringApplication.ScreenUpdating = FalseMyPath = ActiveWorkbook.PathMyName = Dir(MyPath & "\" & "*.xls")AWbName = ActiveWorkbook.NameNum = 0Do While MyName <> ""If MyName <> AWbName ThenSet Wb = Workbooks.Open(MyPath & "\" & MyName)Num = Num + 1With Workbooks(1).ActiveSheet.Cells(.Range("B200000").End(xlUp).Row + 2, 1) = Left(MyName, Len(MyName) - 4)For G = 1 To Sheets.CountWb.Sheets(G).UsedRange.Copy .Cells(.Range("B200000").End(xlUp).Row + 1, 1)NextWbN = WbN & Chr(13) & Wb.NameWb.Close FalseEnd WithEnd IfMyName = DirLoopRange("B1").SelectApplication.ScreenUpdating = TrueMsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下：" & Chr(13) & WbN, vbInformation, "提示"End Sub

看不懂没关系，不用再学 VBA，直接跑就完事儿了。那么该如何使用呢？

如下目录中放着 3 个待合并的 EXCEL 表，每个表中数据不同；

新建一个 EXCEL 文件后打开它，用于存放合并后的数据；

通过快捷键 Alt + F11 打开 VBA 界面；

打开Sheet1，将上面的代码复制粘贴到其中，按 F5 运行；

即将同目录下其他 3 个 EXCEL 文件中的数据合并至此；

办公自动化有着各种各样的手段，下面让我们来看看万能的 Python 又是如何实现这个功能的。

Python 实现合并

直接上代码，说明见注释：

def merge_excel(dir):    print('--- 执行合并 ---')    filename_excel = [] # 存表名    frames = [] # 存表内容    d = dir.replace('/','\\\\') # 因pandsa读取路径为双斜杠，需转换if d.endswith('\\\\') == False: # 若为磁盘根目录则路径结尾自带\\，若为文件夹则无，需添加\\        d = d + '\\\\'    print("路径是：",d,"\n有以下文件：")for files in os.listdir(path=dir): # 遍历目录下的文件        print(files)if 'xlsx' in files or 'xls' in files : # 搜索xlsx/xls后缀文件            filename_excel.append(files)             df = pd.read_excel(d+files) # 读取一个表内容存入一个DataFrame            frames.append(df)if len(frames)!= 0: # 若存在EXCEL表则合并保存        result = pd.concat(frames) # 上下连接多个df        result.to_excel(d+"合并结果表.xlsx")merge_excel("D:/某文件夹")

拆分 EXCEL

分久必合，合久必分（这句话不是这么引用的吧）。那么如果是要分配工作，比如把一个大表按行数分成多份小表该如何实现呢？我们还是先来看看 VBA 版本。

VBA 实现拆分

Sub ChaiFenSheet()    Dim r, c, i, WJhangshu, WJshu, bt As Long    r = Range("A" & Rows.Count).End(xlUp).Row    b = InputBox("请输入分表行数")If IsNumeric(b) Then           WJhangshu = Int(b)Else            MsgBox "输入错误", vbOKOnly, "错误"            End    End If    c = Cells(1, Columns.Count).End(xlToLeft).Column    bt = 1 '标题行数    'WJhangshu = 50 '每个文件的行数    WJshu = IIf(r - bt Mod WJhangshu, Int((r - bt) / WJhangshu), Int((r - bt) / WJhangshu) + 1)
    '------    Set fs = CreateObject("Scripting.FileSystemObject") '    For i = 0 To WJshu        Workbooks.Add        Application.DisplayAlerts = False        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Format(i + 1, String(Len(WJshu), 0)) & "." & fs.GetExtensionname(ThisWorkbook.FullName)   '扩展名        Application.DisplayAlerts = True        ThisWorkbook.ActiveSheet.Range("A1").Resize(bt, c).Copy ActiveSheet.Range("A1")        ThisWorkbook.ActiveSheet.Range("A" & bt + i * WJhangshu + 1).Resize(WJhangshu, c).Copy _        ActiveSheet.Range("A" & bt + 1)        ActiveWorkbook.Close True    NextEnd Sub

与合并表类似，先打开要拆分的大表，按 Alt + F11 进入 VBA 界面，再按 F5 运行代码即可。如下图所示，将一个表中的 15 个任务拆分到了 3 个新的表中

Python 实现拆分

拆分部分的源码由笔者的同事 yang 编写：

def split_excel(path,num):# print("--- 执行拆分 ---")    p = path.replace('/', '\\\\') # 传入pd库read_excel方法的路径，含文件名    dir = p[ : p.rfind('\\') + 1 ] # 输出被拆分表的目录，不含文件名    sheetname = path[ path.rfind('/') + 1 :].strip('.xlsx').strip('.xlx') # 无后缀的文件名    data = pd.read_excel(p) # 数据    nrows = data.shape[0]  # 获取行数    split_rows = num # 自定义要拆分的条数，即分隔成多少行一份    count = int(nrows/split_rows) + 1  # 拆分的份数# print("应当拆分成%d份"%count)begin = 0end = 0for i in range(1,count+1):        sheetname_temp = sheetname+str(i)+'.xlsx' # 拆分后的每个表名if i == 1:end = split_rows        elif i == count:begin = endend = nrowselse:begin = endend = begin + split_rows        print(sheetname_temp)        data_temp = data.iloc[ begin:end , : ] # [ 行范围 , 列范围 ]        data_temp.to_excel(dir + sheetname_temp)# print('拆分完成')split_excel("test.xlsx",5)

作为 PyQt5 和画图爱好者，笔者又把这两段代码丢进界面里打包成一个小玩意儿。

exe 文件的图标是笔者绘制的字母 X 和 L 的组合画（因为 XL 速读即为 EXCEL ）。

合并和拆分功能图标也很形象（一本正经的卖瓜自夸）。

exe和完整源码已上传，欢迎下载使用（2种方式）！

奶牛快传（不限速）：

https://alltodata.cowtransfer.com/s/7ec265ff021d43

github：https://github.com/SeonPan/xltools