Optimizing reading data from Excel

[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:

expandcollapse popup

#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!

Edited March 12, 2019 by SDL
excel -> Excel

[FrancescoDiMuro]

@SDL
Does this file need to be in XLSX or you can convert it in CSV format as you did above?

[TheDcoder]

7 minutes ago, FrancescoDiMuro said:

Does this file need to be in XLSX or you can convert it in CSV format as you did above?

Good question, the only reason I am using XLSX is because I need Excel to automatically update those values via an addon. Otherwise I would simply have used CSV or anything else which is appropriate

I gave the CSV because it is easier to paste it here... and I could not provide the xlsx file permanently without using up my attachment space.

[water]

As you are only reading a few rows/columns I would use _Excel_RangeRead to read the whole used range and then process the returned array.

[kaisies]

Rangeread can read more than just one cell at a time, and returns  2d array. is your data not next to each other in the sheet? 

[TheDcoder]

@water & @kaisies I see, I will try adapting my script to reduce calls to RangeRead and see how it performs

Will report back once I test it!

[TheDcoder]

I have modified my original script to read all the data once instead of one by one for every cell... and boy are the speed gains huge!

expandcollapse popup

#include <Array.au3>
#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
    Time()
    Local $aData = _Excel_RangeRead($oBook, $sSheet, "A1:B6")
    Time()
    _ArrayDisplay($aData)
    For $iRow = 0 To 5
        Time()
        Local $iNum = Number($aData[$iRow][0])
        If $iNum = 1 Then
            ConsoleWrite("Row " & $iRow + 1 & " is 1 and value of column B is " & $aData[$iRow][0])
        Else
            ConsoleWrite("Row " & $iRow + 1 & " 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

120.15
Row 1 is 1 and value of column B is 1. Reading took: 0.07
Row 2 is not 1. Reading took: 0.02
Row 3 is 1 and value of column B is 1. Reading took: 0.02
Row 4 is 1 and value of column B is 1. Reading took: 0.02
Row 5 is not 1. Reading took: 0.01
Row 6 is 1 and value of column B is 1. Reading took: 0.02
The whole operation took 120.31 milliseconds.

From ~1500 ms to ~120 ms, that is a huge 12.5x improvement in speed

And even most of that is the first initial call to Angered, definitely going to try this method of caching in my project... I actually feel a bit dumb not having tried this before asking

[water]

You learn something new each day

[TheDcoder]

I discovered another way to hugely cut up the speed... by using the inbuilt AutoIt transpose function! Just set $bForceFunc parameter to true and enjoy the gains

expandcollapse popup

#include <Array.au3>
#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
    Time()
    Local $aData = _Excel_RangeRead($oBook, $sSheet, "A1:B6", 1, True)
    Time()
    _ArrayDisplay($aData)
    For $iRow = 0 To 5
        Time()
        Local $iNum = Number($aData[$iRow][0])
        If $iNum = 1 Then
            ConsoleWrite("Row " & $iRow + 1 & " is 1 and value of column B is " & $aData[$iRow][0])
        Else
            ConsoleWrite("Row " & $iRow + 1 & " 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

24.44
Row 1 is 1 and value of column B is 1. Reading took: 0.04
Row 2 is not 1. Reading took: 0.01
Row 3 is 1 and value of column B is 1. Reading took: 0.01
Row 4 is 1 and value of column B is 1. Reading took: 0.02
Row 5 is not 1. Reading took: 0.01
Row 6 is 1 and value of column B is 1. Reading took: 0.01
The whole operation took 24.54 milliseconds.

From 1500 ms to 120 ms, and finally to 25 ms, that is an astonishing 60x improvement in total

The excel function for transposing must really suck... or it accounts for a lot of things that our array transpose function cannot. Either way, I am happy with the outcome.