Modify

Opened 7 years ago

Closed 7 years ago

#2219 closed Bug (Fixed)

_ExcelReadSheetToArray() fix and upgrade

Reported by: Spiff59 Owned by: guinness
Milestone: 3.3.9.5 Component: Standard UDFs
Version: 3.3.8.1 Severity: None
Keywords: Cc:

Description

The helpfile and docs for _ExcelReadSheetToArray() state "If the sheet is empty [0][0] and [0][1] both = 0." This is not true, as it returns values of 1 and 1, the same result as an array that actually has 1 row or 1 column.

The production version hangs if you are trying to read a protected worksheet.

The production version takes 45 seconds to porcess a 260x35 spreadsheet.

The following corrects the bad values returned when reading an empty worksheet. It is able to read protected worksheets. It processed the same 260x35 worksheet in not 45 seconds, but .2 seconds.
Thanks.

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

Attachments (0)

Change History (5)

comment:1 Changed 7 years ago by trancexx

  • Component changed from AutoIt to Standard UDFs

comment:2 Changed 7 years ago by Spiff59

I noticed I was not handling the $iColShift parameter when a single-cell sheet was the target. I've corrected that. I also found a post by Footswitch proposing the use of "range" instead of "cell" to improve speed. The post is older than the Golfinhu post, so I now credited them both in the comments.

Updated version:

; #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, 
;                  Footswitch/Golfinhu - Speed enhancement, Spiff59 - 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
	
; Handle single-cell sheet	
    If Not IsArray($aArray) Then
        Local $avRET[2][2] = [[1, 1]]
		If $iColShift Then
			$avRET[1][0] = $aArray
		Else
			$avRET[1][1] = $aArray
		EndIf
        Return $avRET
    EndIf

; Insert Row-0 totals, convert Col/Row array (from Excel) to Row/Col, apply $iColShift	
    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

comment:3 Changed 7 years ago by anonymous

This is likely more efficient than the If/Else statement added to the last version to handle $iColShift on single-cell sheets:

; Handle single-cell sheet
    If Not IsArray($aArray) Then
        Local $avRET[2][2] = [[1, 1]]
	$avRET[1][Not $iColShift] = $aArray
        Return $avRET
    EndIf

comment:4 Changed 7 years ago by c.haslam@…

Thumbs up! I hit this bug, and am relieved by the much increased speed of your code.

...chris

comment:5 Changed 7 years ago by guinness

  • Milestone set to 3.3.9.5
  • Owner set to guinness
  • Resolution set to Fixed
  • Status changed from new to closed

Fixed by revision [7236] in version: 3.3.9.5

Guidelines for posting comments:

  • You cannot re-open a ticket but you may still leave a comment if you have additional information to add.
  • In-depth discussions should take place on the forum.

For more information see the full version of the ticket guidelines here.

Add Comment

Modify Ticket

Action
as closed The owner will remain guinness.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.