实现Excel文件xls与xlsx格式批量互相转换

目录

python实现方法

将文件夹中的所有xls或xlsx文件转换为xlsx或xls文件

import win32com.client as win32 # 需安装pywin32import os.pathimport globexcel = win32.gencache.EnsureDispatch('Excel.Application')#简易使用方法#filename = r'E:\xlsx\1.xls'#wb = excel.Workbooks.Open(filename)#wb.SaveAs(filename+'x', FileFormat=51) #FileFormat=51 是 .xlsx 的扩展#wb.Close()#FileFormat=56 是 .xls 的扩展#excel.Application.Quit()def xls2xlsx(xls_path, xlsx_path):# xls_path参数为待转换的xls文件所在文件夹# xlsx_path参数为转换完成的xlsx文件保存文件夹# 当xlsx保存文件夹存在同名原xls时，会弹窗提示是否替换，建议保存至空文件夹​path_list = glob.glob(xls_path + '\\*.xls') # 获取文件夹下所有xlsfor file in path_list:filename = os.path.basename(file).replace('.xls', '.xlsx') # 获取文件名wb = excel.Workbooks.Open(file)wb.SaveAs(xlsx_path + '\\' + filename, FileFormat=51) # xlsx为51wb.Close()excel.Application.Quit()print('xls2xlsx转换完成')def xlsx2xls(xlsx_path, xls_path):# xlsx_path参数为待转换的xlsx文件所在文件夹# xls_path参数为转换完成的xls文件保存文件夹# 当xls保存文件夹存在同名原xlsx时，会弹窗提示是否替换，建议保存至空文件夹​path_list = glob.glob(xlsx_path + '\\*.xlsx') # 获取文件夹下所有xlsxfor file in path_list:filename = os.path.basename(file).replace('.xlsx', '.xls') # 获取文件名wb = excel.Workbooks.Open(file)wb.SaveAs(xls_path + '\\' + filename, FileFormat=56) # xls为56wb.Close()excel.Application.Quit()print('xlsx2xls转换完成')if __name__ == '__main__':xls_path = r'E:\xlsx\xls'xlsx_path = r'E:\xlsx\xlsx'xls2xlsx(xls_path, xlsx_path)#xlsx2xls(xlsx_path, xls_path)

VBA实现方法

将文件夹中的所有xls或xlsx文件转换为xlsx或xls文件；保存时如果存在同名文件，则会覆盖

Function xls2xlsx(xls_path$, save_path$)'将xls_path文件夹中所有xls文件转为xlsx格式，保存至save_path文件夹；注意同名覆盖Dim fso As Object, wb As Workbook, save_file$Application.ScreenUpdating = False '关闭屏幕更新，加快程序运行Application.DisplayAlerts = False '不显示警告信息Set fso = CreateObject("Scripting.FileSystemObject")If Not fso.FolderExists(save_path) Then fso.CreateFolder (save_path) '创建文件夹For Each f In fso.GetFolder(xls_path).Files '遍历文件夹里文件If fso.GetExtensionName(f.Name) = "xls" Thensave_file = save_path & "\" & f.Name & "x" '保存文件全名（文件路径、文件名、扩展名）Set wb = Workbooks.Open(f)wb.SaveAs filename:=save_file, FileFormat:=xlOpenXMLWorkbookwb.Close (False)End IfNextApplication.ScreenUpdating = TrueApplication.DisplayAlerts = TrueEnd FunctionFunction xlsx2xls(xlsx_path$, save_path$)'将xlsx_path文件夹中所有xlsx文件转为xls格式，保存至save_path文件夹；注意同名覆盖Dim fso As Object, wb As Workbook, save_file$Application.ScreenUpdating = False '关闭屏幕更新，加快程序运行Application.DisplayAlerts = False '不显示警告信息Set fso = CreateObject("Scripting.FileSystemObject")If Not fso.FolderExists(save_path) Then fso.CreateFolder (save_path) '创建文件夹For Each f In fso.GetFolder(xlsx_path).Files '遍历文件夹里文件If fso.GetExtensionName(f.Name) = "xlsx" Thensave_file = save_path & "\" & fso.GetBaseName(f.Name) & ".xls" '保存文件全名（文件路径、文件名、扩展名）Set wb = Workbooks.Open(f)wb.SaveAs filename:=save_file, FileFormat:=xlExcel8wb.Close (False)End IfNextApplication.ScreenUpdating = TrueApplication.DisplayAlerts = TrueEnd FunctionPrivate Sub xls和xlsx转换测试()Dim file_path$, save_path$file_path = "E:\测试\xls"save_path = "E:\测试\xlsx"a = xls2xlsx(file_path, save_path) '2种调用方式' Call xlsx2xls(save_path, file_path)End Sub