_ExcelReadSheetToArrayEX() much faster than the original

[golfinhu]

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:

expandcollapse popup

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

Edited February 9, 2012 by golfinhu

[siareaxp]

Thanks for share , very fast ( try 2000 rows & 20 columns ) just 1sec

[JScript]

Very nice! Thanks for sharing. I'll do some testing and feedback!

Regards,

João Carlos.

[kalel69]

Oh My GOD! Not just a little, A LOT faster. This saves me hours on huge excel files. I wish I'd found it sooner. Thank you soooo much!

Can you reverse it for time savings as well? WritesheetToArrayEX? That would be GREAT!

Thanks,

[Jfish]

Unbelieveable. I went from about 30 seconds to read a single column with 25000 rows to ... so fast I can't even see the delay. VERY nice!!!! :-)

[Jfish]

Question, I am not sure your function is respecting the start column parameter. I am passing "21" in a sheet with 22 columns and it is building a large array starting at Col 1.

$truefalseArray=_ExcelReadSheetToArrayEx($IMARCTXT,1,21,0,1)

I am on Excel 2010. Any thoughts?

[Spiff59]

This function tries to put the row count in element [0][0] and the column count in element [0][1].

That is problematical if you've requested the return of a single column array.

Maybe this would be a better finish for the function:

;Read data
Local $aArray = $oExcel.ActiveSheet.Range($oExcel.Cells($iStartRow, $iStartColumn), $oExcel.Cells($iRowCnt, $iColCnt)).Value
Local $rows = UBound($aArray, 2)
Local $cols = UBound($aArray)
Local $avRET[$rows + 1][$cols] = [[$rows]]
If $cols > 1 Then $avRET[0][1] = $cols
For $i = 0 To ($cols - 1)
    For $j = 0 To ($rows - 1)
        $avRET[$j + 1][$i] = $aArray[$i][$j]
    Next
Next
Return $avRET
EndFunc   ;==>_ExcelReadSheetToArrayEx

[Spiff59]

Question, I am not sure your function is respecting the start column parameter.

You're correct, this function has some indexing issues. It also blows up in one area of code if the return is only a single cell and another area if the return is a single column. The $iColShift parameter is removed (defaulting to enabled behavior) making it a potential script-breaker.

Most of our spreadsheets here at work have at least portions that are protected, and I quickly noticed this version, as well as the production version, give a COM error if run on a protected worksheet.

The production version also returns a row/column count of 1/1 when run on an empty sheet (the docs say it should be 0/0), and it returns an extra blank column when the (peculiar) $iColShift parameter is enabled.

This example below (I believe) fixes the indexing and single-cell/column issues of the example in this thread, and restores the $iColShift parameter. It also, while retaining golfinhu's speed advantage over the production version, corrects the production version error when searching an empty sheet and removes the extra column returned when $iColShift is enabled.

expandcollapse popup

; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelReadSheetToArray
; 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)
;                $iColShift - Match R1C1 column position, or start array in column 0. Default is 0 (match R1C1 values)
; 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
; Modified.......: PsaltyDS 01/04/08 - 2D version, litlmike - Column shift parm, Golfinhu - Speed enhancement, Spiff59 - Allow protected sheets
; Remarks .......: Returned array has row count in [0][0] and column count in [0][1] (unless
;                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 _ExcelReadSheetToArray($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0, $iColShift = 0)
; Parameter edits
    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)

    Local $iLastRow = $oExcel.Activesheet.UsedRange.Rows.Count
    Local $iLastColumn = $oExcel.Activesheet.UsedRange.Columns.Count
    If ($iLastRow + $iLastColumn = 2) And $oExcel.Activesheet.Cells(1, 1).Value = "" Then ; empty result
        Local $avRET[1][2] = [[0, 0]]
        Return $avRET
    EndIf

; Parameter edits (continued)
    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 Then
        $iLastRow = $iStartRow + $iRowCnt - 1
    Else
        $iRowCnt = $iLastRow - $iStartRow + 1
    EndIf
    If $iColCnt Then
        $iLastColumn = $iStartColumn + $iColCnt - 1
    Else
        $iColCnt = $iLastColumn - $iStartColumn + 1
    EndIf

; Read data
    Local $aArray = $oExcel.ActiveSheet.Range($oExcel.Cells($iStartRow, $iStartColumn), $oExcel.Cells($iLastRow, $iLastColumn)).Value
    If Not IsArray($aArray) Then ; single-cell result
        Local $avRET[2][2] = [[1, 1],["", $aArray]]
        Return $avRET
    EndIf

; Convert Col/Row array from Excel to Row/Col
    Local $avRET[$iRowCnt + 1][$iColCnt + ($iColCnt = 1 Or $iColShift = 0)] = [[$iRowCnt, $iColCnt]]
    For $i = 1 To $iColCnt
        For $j = 1 To $iRowCnt
           $avRET[$j][$i - $iColShift] = $aArray[$i - 1][$j - 1]
        Next
    Next
    Return $avRET
EndFunc   ;==>_ExcelReadSheetToArray

Edit: Oops! Forgot to mention this version can also process protected worksheets.

Edited May 12, 2012 by Spiff59

[ShawnLuo]

this is the fastest function i got to read excel file. great!

[water]

At the moment I'm about to update the builtin Excel UDF. All those (speed) enhancements will be part of the new UDF.

Please check this for what I plan to do. Suggestions are always welcome!

I have a running to see how important backward compatibility is for users. Please vote!

Based on the poll some design decisions will be made!

[DXRW4E]

and even faster and does not useneed office & object ect ect, but only for the XLSX (Office 2007 or more recent)

Ciao.

Edited October 24, 2012 by DXRW4E

[BrewManNH]

I just speed tested the function from post #9 and _XLSXReadToArray in a head to head test, and the one in post #9 is at least twice as fast as your's. Although your's does have the added advantage of not needing to have Excel installed, which is always being asked for.

[DXRW4E]

I just speed tested the function from post #9 and _XLSXReadToArray in a head to head test, and the one in post #9 is at least twice as fast as your's

you mean post #8 right ???

I'm really very curious about your test ???, can you just post exactly all the the script to that used during the your test ?????

because if it's true what you said, means that your test and x 4 times faster than the Office 2013, this does not seem very logical hmmmmmmmmm

the and 2 times faster than Office 2013 because it uses a different way (or direct way) to read the file, but using the object, or the same way of the office, I do not see how he could never be faster than the same office, and not faster but 4 times faster than hmmmmmmmmm

Although your's does have the added advantage of not needing to have Excel installed, which is always being asked for.

this is not little, that makes a really big difference in my opinion Edited October 24, 2012 by DXRW4E

[BrewManNH]

Here's the exact script I used, I just changed out the file name used because it wouldn't make much sense to include that as well.

expandcollapse popup

#include <Array.au3>
#include "_XLSXReadToArray.au3"
#include <excel.au3>
Local $a1, $b1
$a1 = TimerInit()
$b1 = _XLSXReadToArray("insert a large xlsx file here")
$a1 = TimerDiff($a1)
_ArrayDisplay($b1, $a1)
ConsoleWrite("Time1 - " & $a1 & @LF)
$a1 = TimerInit()
$File = _ExcelBookOpen("insert a large xlsx file here", 0)
$b1 = __ExcelReadSheetToArray($File)
$a1 = TimerDiff($a1)
_ArrayDisplay($b1, $a1)
ConsoleWrite("Time1 - " & $a1 & @LF)
_ExcelBookClose($File)
Exit
; #FUNCTION# ====================================================================================================================
; Name...........: __ExcelReadSheetToArray
; 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)
;            $iColShift - Match R1C1 column position, or start array in column 0. Default is 0 (match R1C1 values)
; Return values .: Success - Returns a 2D array with the specified cell contents by [$row][$col]
;            Failure - Returns 0 and sets @error on errors:
;            [email="|@error=1"]|@error=1[/email] - Specified object does not exist
;            [email="|@error=2"]|@error=2[/email] - Start parameter out of range
;            [email="|@extended=0"]|@extended=0[/email] - Row out of range
;            [email="|@extended=1"]|@extended=1[/email] - Column out of range
;            [email="|@error=3"]|@error=3[/email] - Count parameter out of range
;            [email="|@extended=0"]|@extended=0[/email] - Row count out of range
;            [email="|@extended=1"]|@extended=1[/email] - Column count out of range
; Author ........: SEO
; Modified.......: PsaltyDS 01/04/08 - 2D version, litlmike - Column shift parm, Golfinhu - Speed enhancement, Spiff59 - Allow protected sheets
; Remarks .......: Returned array has row count in [0][0] and column count in [0][1] (unless
;            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 __ExcelReadSheetToArray($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0, $iColShift = 0)
; Parameter edits
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)
Local $iLastRow = $oExcel.Activesheet.UsedRange.Rows.Count
Local $iLastColumn = $oExcel.Activesheet.UsedRange.Columns.Count
If ($iLastRow + $iLastColumn = 2) And $oExcel.Activesheet.Cells(1, 1).Value = "" Then ; empty result
Local $avRET[1][2] = [[0, 0]]
Return $avRET
EndIf
; Parameter edits (continued)
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 Then
$iLastRow = $iStartRow + $iRowCnt - 1
Else
$iRowCnt = $iLastRow - $iStartRow + 1
EndIf
If $iColCnt Then
$iLastColumn = $iStartColumn + $iColCnt - 1
Else
$iColCnt = $iLastColumn - $iStartColumn + 1
EndIf
; Read data
Local $aArray = $oExcel.ActiveSheet.Range($oExcel.Cells($iStartRow, $iStartColumn), $oExcel.Cells($iLastRow, $iLastColumn)).Value
If Not IsArray($aArray) Then ; single-cell result
Local $avRET[2][2] = [[1, 1],["", $aArray]]
Return $avRET
EndIf
; Convert Col/Row array from Excel to Row/Col
Local $avRET[$iRowCnt + 1][$iColCnt + ($iColCnt = 1 Or $iColShift = 0)] = [[$iRowCnt, $iColCnt]]
For $i = 1 To $iColCnt
For $j = 1 To $iRowCnt
$avRET[$j][$i - $iColShift] = $aArray[$i - 1][$j - 1]
Next
Next
Return $avRET
EndFunc ;==>_ExcelReadSheetToArray

As to the point of it being able to open an Excel file without Excel installed, the size of the difference only applies to a subset of people that would use an Excel function on a spreadsheet. It's nice to have, but I don't see it as a really big difference to those that already have Excel installed, especially if it's slower.

EDIT: And yes, I meant the script in post 8, not 9, mistyped it

Edited October 24, 2012 by BrewManNH

[DXRW4E]

NO ehhhhh, as I suspected and as I said above and as a logical (object in autoit, they will never be faster than the same Office ehhhhh, if 2 times faster than Office 2013, was not logical that the__ExcelReadSheetToArray will be 2 times faster than ), __ExcelReadSheetToArray and 3 times more slowly then

expandcollapse popup

#include <Array.au3>
#include "_XLSXReadToArray.au3"
#include <excel.au3>
Local $a1, $b1
InetGet("http://go.microsoft.com/fwlink/?LinkID=245778", @WindowsDir & "TempBulletinSearch.xlsx")
$a1 = TimerInit()
$b1 = _XLSXReadToArray(@WindowsDir & "TempBulletinSearch.xlsx")
$a1 = TimerDiff($a1)
_ArrayDisplay($b1, $a1)
ConsoleWrite("Time1 - " & $a1 & @LF)
$a1 = TimerInit()
$File = _ExcelBookOpen(@WindowsDir & "TempBulletinSearch.xlsx", 0)
$b1 =  __ExcelReadSheetToArray($File)
$a1 = TimerDiff($a1)
_ArrayDisplay($b1, $a1)
ConsoleWrite("Time1 - " & $a1 & @LF)
_ExcelBookClose($File)
Exit
; #FUNCTION# ====================================================================================================================
; Name...........: __ExcelReadSheetToArray
; 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)
;               $iColShift - Match R1C1 column position, or start array in column 0. Default is 0 (match R1C1 values)
; Return values .: Success  - Returns a 2D array with the specified cell contents by [$row][$col]
;               Failure  - Returns 0 and sets @error on errors:
;               [email="|@error=1"]|@error=1[/email]  - Specified object does not exist
;               [email="|@error=2"]|@error=2[/email]  - Start parameter out of range
;               [email="|@extended=0"]|@extended=0[/email] - Row out of range
;               [email="|@extended=1"]|@extended=1[/email] - Column out of range
;               [email="|@error=3"]|@error=3[/email] - Count parameter out of range
;               [email="|@extended=0"]|@extended=0[/email] - Row count out of range
;               [email="|@extended=1"]|@extended=1[/email] - Column count out of range
; Author ........: SEO
; Modified.......: PsaltyDS 01/04/08 - 2D version, litlmike - Column shift parm, Golfinhu - Speed enhancement, Spiff59 - Allow protected sheets
; Remarks .......: Returned array has row count in [0][0] and column count in [0][1] (unless
;               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 __ExcelReadSheetToArray($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0, $iColShift = 0)
 ; Parameter edits
 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)
 Local $iLastRow = $oExcel.Activesheet.UsedRange.Rows.Count
 Local $iLastColumn = $oExcel.Activesheet.UsedRange.Columns.Count
 If ($iLastRow + $iLastColumn = 2) And $oExcel.Activesheet.Cells(1, 1).Value = "" Then ; empty result
  Local $avRET[1][2] = [[0, 0]]
  Return $avRET
 EndIf
 ; Parameter edits (continued)
 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 Then
  $iLastRow = $iStartRow + $iRowCnt - 1
 Else
  $iRowCnt = $iLastRow - $iStartRow + 1
 EndIf
 If $iColCnt Then
  $iLastColumn = $iStartColumn + $iColCnt - 1
 Else
  $iColCnt = $iLastColumn - $iStartColumn + 1
 EndIf
 ; Read data
 Local $aArray = $oExcel.ActiveSheet.Range($oExcel.Cells($iStartRow, $iStartColumn), $oExcel.Cells($iLastRow, $iLastColumn)).Value
 If Not IsArray($aArray) Then ; single-cell result
  Local $avRET[2][2] = [[1, 1],["", $aArray]]
  Return $avRET
 EndIf
 ; Convert Col/Row array from Excel to Row/Col
 Local $avRET[$iRowCnt + 1][$iColCnt + ($iColCnt = 1 Or $iColShift = 0)] = [[$iRowCnt, $iColCnt]]
 For $i = 1 To $iColCnt
  For $j = 1 To $iRowCnt
   $avRET[$j][$i - $iColShift] = $aArray[$i - 1][$j - 1]
  Next
 Next
 Return $avRET
EndFunc   ;==>_ExcelReadSheetToArray

>"C:\Program Files\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:UsersDXRW4EDesktoptest.au3" /UserParams

+>16:59:28 Starting AutoIt3Wrapper v.2.1.0.33 Environment(Language:0409 Keyboard:00000409 OS:WIN_8/ CPU:X86 OS:X86)

>Running AU3Check (1.54.22.0) from:C:Program FilesAutoIt3

+>16:59:29 AU3Check ended.rc:0

>Running:(3.3.8.1):C:Program FilesAutoIt3autoit3.exe "C:UsersDXRW4EDesktoptest.au3"

--> Press Ctrl+Alt+F5 to Restart or Ctrl+Break to Stop

Time1 _XLSXReadToArray - 10686.4807752785

Time1 __ExcelReadSheetToArray - 28028.814724749

+>17:01:21 AutoIT3.exe ended.rc:0

>Exit code: 0 Time: 114.286

Edited October 24, 2012 by DXRW4E

[DXRW4E]

not to mention after this (Date Posted) look here

Excel 2013

__ExcelReadSheetToArray

_XLSXReadToArray

however all ok, it was only to clarify

the speed is not important, I mean in general important and that one thing work well, after the speed less importance (I also believe the bug "Date" in __ExcelReadSheetToArray i think it will solve quickly and immediately, i think and a small thing, as mentioned above was just to clarify)

Good job to all

Edited October 24, 2012 by DXRW4E

[BrewManNH]

There is a difference in the output from the 2 functions in the dates, but on my computer, running Win7 x64, this is what I get for times.

>Running:(3.3.8.1):E:DropboxAppautoit3.exe "E:DropboxAutoItScriptstest.au3"

--> Press Ctrl+Alt+F5 to Restart or Ctrl+Break to Stop

Time1 - 14395.4843033773

Time1 - 5187.9234554614

+>11:43:01 AutoIT3.exe ended.rc:0

>Exit code: 0 Time: 64.978

>Running:(3.3.8.1):E:DropboxAppautoit3_x64.exe "E:DropboxAutoItScriptstest.au3"

--> Press Ctrl+Alt+F5 to Restart or Ctrl+Break to Stop

Time1 - 12466.4412559892

Time1 - 5485.35816632844

+>11:44:19 AutoIT3.exe ended.rc:0

>Exit code: 0 Time: 45.920

The top one is running autoit as a 32 bit app, the second as a 64 bit app.

[DXRW4E]

Time1 - 14395.4843033773 or Time1 - 12466.4412559892

I find this really strange, because it is impossible ?? that to me in x86 (in very old and slow pc) and faster that in x64 (Time1 _XLSXReadToArray - 10686.4807752785) hmmmmmmm, maybe something wrong when you do the test

I think (as logical) the REGEXP way and more direct (for this reason and also faster than the office, because it ignores many things and goes directly to the point, read only what you need, because the regexp in Autoit I do not think and faster than of what uses Office2013 ehhhhhhh), and logically normally should be faster than __ExcelReadSheetToArray or the use of Excel Object in Autoit, or so I believe

may be used as a converter (without having office installed), seen that a time had the array, after and very easy to write the file xml or csv ot txt ect ec, or be of help for script like

sorry again for my english

Good job to All

Edited October 24, 2012 by DXRW4E

[ptrex]

@golfinhu

Great function,

If combined with this function we can get rid of Excel all the way ;-)

Rgds

ptrex