_ExcelReadSheetToArray() replacement. Opinions?

[Spiff59]

In this thread

golfinhu offered a version of _ExcelReadSheetToArray() using Excel's .Range method to retrieve all cell vaues in one call, Rather than the production versions technique of using nested loops and the .Cell method to read cells one-at-a-time.

I ran the old version on one 350-row spreadsheet, retrieving a single column, and it took 12 seconds on one slower machine at work. golfinhu's version finished in under a half-second.

It had a couple bugs, and was no longer backwards-compatible. I also noticed the production version could not read a protected worksheet, and that it returned array index values of 1 and 1 for an empty spreadsheet, when the docs claim 0 and 0.

The version below resolves the 0-0/1-1 issue, reads protected sheets, and incorporates golfinhu's speed enhancement (credited in comments).

I thought I'd run it by whomever is willing to take a look before risking a Bug tracker shoot-down...

Thanks.

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

Edited June 30, 2012 by Spiff59