Hello everyone, i had an excel file with 450 rows and 9 columns, and using _ExcelReadSheetToArray() takes about 30 seconds to read to array, which i found very time consuming!
So i rewrote the code and now i can read to array the same file in less than 1 second.
So i decided to share here!
The new code:
#include <Excel.au3>
; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelReadSheetToArrayEx
; Description ...: Create a 2D array from the rows/columns of the active worksheet.
; Syntax.........: _ExcelReadSheetToArray($oExcel[, $iStartRow = 1[, $iStartColumn = 1[, $iRowCnt = 0[, $iColCnt = 0]]]])
; Parameters ....: $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
; $iStartRow - Row number to start reading, defaults to 1 (first row)
; $iStartColumn - Column number to start reading, defaults to 1 (first column)
; $iRowCnt - Count of rows to read, defaults to 0 (all)
; $iColCnt - Count of columns to read, defaults to 0 (all)
; Return values .: Success - Returns a 2D array with the specified cell contents by [$row][$col]
; Failure - Returns 0 and sets @error on errors:
; |@error=1 - Specified object does not exist
; |@error=2 - Start parameter out of range
; |@extended=0 - Row out of range
; |@extended=1 - Column out of range
; |@error=3 - Count parameter out of range
; |@extended=0 - Row count out of range
; |@extended=1 - Column count out of range
; Author ........: SEO; Rewrited by Golfinhu.
; Modified.......: litlmike (added Column shift parameter to Start Array Column on 0) and PsaltyDS 01/04/08 - 2D version _ExcelReadSheetToArray()
; Remarks .......: Returned array has row count in [0][0] and column count in [0][1].
; Except for the counts above, row 0 and col 0 of the returned array are empty, as actual
; cell data starts at [1][1] to match R1C1 numbers.
; By default the entire sheet is returned.
; If the sheet is empty [0][0] and [0][1] both = 0.
; Related .......:
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _ExcelReadSheetToArrayEx($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0)
Local $avRET[1][2] = [[0, 0]] ; 2D return array
; Test inputs
If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
If $iStartRow < 1 Then Return SetError(2, 0, 0)
If $iStartColumn < 1 Then Return SetError(2, 1, 0)
If $iRowCnt < 0 Then Return SetError(3, 0, 0)
If $iColCnt < 0 Then Return SetError(3, 1, 0); Get size of current sheet as R1C1 string
; Note: $xlCellTypeLastCell and $x1R1C1 are constants declared in ExcelCOM_UDF.au3
Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
; Extract integer last row and col
$sLastCell = StringRegExp($sLastCell, "(d+)", 3)
Local $iLastRow = $sLastCell[0]
Local $iLastColumn = $sLastCell[1]; Return 0's if the sheet is blank
If $sLastCell = "R1C1" And $oExcel.Activesheet.Cells($iLastRow, $iLastColumn).Value = "" Then Return $avRET
; Check input range is in bounds
If $iStartRow > $iLastRow Then Return SetError(2, 0, 0)
If $iStartColumn > $iLastColumn Then Return SetError(2, 1, 0)
If $iStartRow + $iRowCnt - 1 > $iLastRow Then Return SetError(3, 0, 0)
If $iStartColumn + $iColCnt - 1 > $iLastColumn Then Return SetError(3, 1, 0); Check for defaulted counts
If $iRowCnt = 0 Then $iRowCnt = Number($iLastRow)
If $iColCnt = 0 Then $iColCnt = Number($iLastColumn)
;Read data
Local $aArray = $oExcel.ActiveSheet.Range($oExcel.Cells($iStartRow, $iStartColumn), $oExcel.Cells($iRowCnt, $iColCnt)).Value
Dim $avRET[UBound($aArray, 2) + 1][UBound($aArray)] = [[UBound($aArray, 2), UBound($aArray)]] ;Declare Array again and set row and col count
For $i = 0 To UBound($aArray, 1) - 1
For $j = 0 To UBound($aArray, 2) - 1
$avRET[$j + 1][$i] = $aArray[$i][$j]
Next
Next
Return $avRET
EndFunc ;==>_ExcelReadSheetToArrayEx
the modification is very simple, but gave a huge difference in time!
It is because you have created the tab using the GUITab UDF. This returns a handle and not a ControlID. Within your message handler you are testing for the ControlID - as the tab does not have one, you do not detect it.
To detect clicks on UDF created tabs you need to register WM_NOTIFY - look at the Help file for _GUICtrlTab_Create to see how to do it.
golfinhu reacted to lorenkinzel in [SOLVED] - Excel - Get an array of cell colors
golfinhu got a reaction from JScript in _ExcelReadSheetToArrayEX() much faster than the original