之前在公众号发起过一项调查,使用VBA新建4000个Excel2016工作簿需要多久,今天将使用wps+JS测试下需要的时间,对比2款软件的差别。
1、测试电脑配置
笔者在Win10 64位系统(笔记本配置Dell14-3468笔记本I5-7200U ,DDR4-1666 4g),分别对Excel2016和Excel2007进行测试,其中Excel2016耗时6171.16s,Excel2007耗时1197.38s,代码如下:
2、VBA新建工作簿(MIcrosoft Excel)
Sub NewWorkBook()
Dim t0 As Single
Dim t1 As Single
t0 = Timer
Dim path As String
path = "C:\Users\Vbahome\Documents\workbook\工作簿"
'此处自动以保存路径及文件名
For i = 1 To 4000
Dim wb As Workbook
Set wb = Application.Workbooks.Add()
wb.Sheets(1).Cells(1, 1) = path & Format(i, "0000")
wb.SaveAs (path & Format(i, "0000") & ".xlsx")
wb.Close
DoEvents
Next i
t1 = Timer
Debug.Print (t1 - t0)
End Sub
3、JS新建工作簿(WPS2023)
function NewWorkBook()
{
var start = new Date;
var strPath ="D:\\data\\workbook\\工作簿" ;
Application.DisplayAlerts = false;
Application.ScreenUpdating =false;
for(i=1;i<=4000;i++)
{
var wb = Application.Workbooks.Add()
var fullname =strPath + ("0000" + i).slice(-4) + ".xlsx";
wb.Sheets.Item(1).Cells.Item(1,1).Formula = fullname;
//Debug.Print(strPath + ("0000" + i).slice(-4) + ".xlsx")
wb.SaveAs (fullname);
wb.Close(false);
sleep(0.1);
}
var end = new Date;
var lngtime =end - start;
Application.DisplayAlerts = true;
Application.ScreenUpdating =true;
Debug.Print(lngtime);
alert( lngtime);
}
使用JS新建3809s,对比显示,比Excel2007速度慢,比Excel2016快。