; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelReadSheetToArrayEX
; Description ...: Create a 2D array from the rows/columns of the active worksheet.
; Syntax.........: _ExcelReadSheetToArrayEX($oExcel[, $iStartRow = 1[, $iStartColumn = 1[, $iLastRow = 0[, $iLastCol = 0[, $iColShift = 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 or letter 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 _ExcelReadSheetToArrayEX($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0, $iColShift = 0)
; Parameter edits
If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
If Not IsNumber($iStartColumn) Then $iStartColumn = _ExcelColumnNumber($iStartColumn)
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 $iColShift > 1 Then $iColShift = 1
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 ;==>_ExcelReadSheetToArrayEX
