By
TheDcoder
Hello everyone, I am working on a project which requires reading a few values from Excel, the catch is that I need it to be very fast... unfortunatley I found out that read operations using the supplied Excel UDF are very slow, more than 150 ms for each operation on average
Here is my testing setup that I made:
#include <Excel.au3>
#include <MsgBoxConstants.au3>
Global $iTotalTime = 0
Test()
Func Test()
Local $oExcel = _Excel_Open()
Local $oBook = _Excel_BookAttach("Test.xlsx", "FileName", $oExcel)
Local $sSheet = "Sheet1"
If @error Then Return MsgBox($MB_ICONERROR, "Excel Failed", "Failed to attach to Excel")
Local $iNum
For $iRow = 1 To 6
Time()
Local $iNum = Number(_Excel_RangeRead($oBook, $sSheet, "A" & $iRow))
If ($iNum = 1) Then
ConsoleWrite("Row " & $iRow & " is 1 and value of column B is " & _Excel_RangeRead($oBook, $sSheet, "B" & $iRow))
Else
ConsoleWrite("Row " & $iRow & " is not 1")
EndIf
ConsoleWrite(". Reading took: ")
Time()
Next
ConsoleWrite("The whole operation took " & $iTotalTime & " milliseconds." & @CRLF)
EndFunc
Func Time()
Local Static $hTimer
Local Static $bRunning = False
If $bRunning Then
Local $iTime = Round(TimerDiff($hTimer), 2)
$iTotalTime += $iTime
ConsoleWrite($iTime & @CRLF)
Else
$hTimer = TimerInit()
EndIf
$bRunning = Not $bRunning
EndFunc
And Test.xlsx in CSV format:
1,-1
-1,1
1,-1
1,1
-1,-1
1,1
Here is the actual xlsx but it should expire in a week: https://we.tl/t-EVkxGp1kc6
And finally output from my script:
Row 1 is 1 and value of column B is -1. Reading took: 276.06
Row 2 is not 1. Reading took: 163.36
Row 3 is 1 and value of column B is -1. Reading took: 302.58
Row 4 is 1 and value of column B is 1. Reading took: 294.65
Row 5 is not 1. Reading took: 152.33
Row 6 is 1 and value of column B is 1. Reading took: 284.92
The whole operation took 1473.9 milliseconds.
Taking ~1.5 seconds for reading 6 rows of data is bad for my script, which needs to run as fast as possible . It would be nice if I can bring this down to 100 ms somehow, I am not very experienced working with MS office so I thought about asking you folks for help and advice on how I can optimize my script to squeeze out every bit of performance that I can get from this script
Thanks for the help in advance!