Modify

Opened 10 years ago

Closed 10 years ago

Last modified 9 years ago

#850 closed Bug (Fixed)

_ExcelReadSheetToArray doesn't work with German Excel 2003

Reported by: Prog@… Owned by: Gary
Milestone: 3.3.1.0 Component: Standard UDFs
Version: 3.3.0.0 Severity: None
Keywords: Cc:

Description

In _ExcelReadSheetToArray, the last-cell-string is Split by the character 'R' and 'C' for Row and Column. The problem is, Excel localizes this string, e.g. German: 'Z' (Zeile) instead of 'R' and 'S' (Spalte) instead of 'C'. So, the cell coordinates should be rad with a language-independant StringRegExp:

    Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
    
    ; Extract integer last row and col
    $sLastCell = StringRegExp($sLastCell,"\A[^0-9]*(\d+)[^0-9]*(\d+)\Z",3)
    Local $iLastRow = $sLastCell[0]
    Local $iLastColumn = $sLastCell[1]

Attachments (0)

Change History (4)

comment:1 Changed 10 years ago by locodarwin@…

Changed the code to submitter's suggestion; nothing breaks, and seems to work as suggested. Recommend updating build with the following replacement function:

; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelReadSheetToArray
; Description ...: Create a 2D array from the rows/columns of the active worksheet.
; Syntax.........: _ExcelReadSheetToArray($oExcel, [$iStartRow , $iStartColumn [, $iRowCnt, $iColCnt], [$iColShift]])
; 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 - Determines if the Array returned, from Excel, will begin in the 0-index base or 1-index base Column.  False by Default to 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 <locodarwin at yahoo dot com>
; 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 .......; Yes
; ===============================================================================================================================
Func _ExcelReadSheetToArray($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0, $iColShift = False)
	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,"\A[^0-9]*(\d+)[^0-9]*(\d+)\Z",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 = $iLastRow - $iStartRow + 1
	If $iColCnt = 0 Then $iColCnt = $iLastColumn - $iStartColumn + 1

	; Size the return array
	ReDim $avRET[$iRowCnt + 1][$iColCnt + 1]
	$avRET[0][0] = $iRowCnt
	$avRET[0][1] = $iColCnt

	If $iColShift Then ;Added by litlmike
		; Read data to array
		For $r = 1 To $iRowCnt
			For $c = 1 To $iColCnt
				$avRET[$r][$c - 1] = $oExcel.Activesheet.Cells($iStartRow + $r - 1, $iStartColumn + $c - 1).Value
			Next
		Next
	Else ;Default for $iColShift
		; Read data to array
		For $r = 1 To $iRowCnt
			For $c = 1 To $iColCnt
				$avRET[$r][$c] = $oExcel.Activesheet.Cells($iStartRow + $r - 1, $iStartColumn + $c - 1).Value
			Next
		Next
	EndIf
	;Return data
	Return $avRET
EndFunc   ;==>_ExcelReadSheetToArray

comment:2 Changed 10 years ago by Gary

  • Milestone set to 3.3.1.0
  • Resolution set to Fixed
  • Status changed from new to closed

Fixed in version: 3.3.1.0

comment:3 Changed 9 years ago by MrCreatoR <mscreator@…>

Ironicly this fix caused another bug - the docs sais this:

Remarks: If the sheet is empty [0][0] and [0][1] both = 0.

but it's not quite true, here is an example that shows this:

#include <Array.au3>
#include <Excel.au3>

$oExcel = ObjCreate("Excel.Application")

If Not IsObj($oExcel) Then
    MsgBox(16, "Error: _Exel_Paste", "Couldn't Create Object Excel.Application")
EndIf

$oExcel = $oExcel.WorkBooks.Add
$oExcel.Application.Visible = True

For $xlWindow In $oExcel.Windows
    $xlWindow.Visible = True
Next

$oExcel.Activate
$aLastCell = _ExcelReadSheetToArray($oExcel)
_ArrayDisplay($aLastCell, 'Excel')

$oExcel.Saved = True
$oExcel.Close

To fix it there is need to replace in _ExcelReadSheetToArray UDF this:

	; Extract integer last row and col
	$sLastCell = StringRegExp($sLastCell, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 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

with this:

	; Extract integer last row and col
	Local $aLastCell = StringRegExp($sLastCell, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3)
	Local $iLastRow = $aLastCell[0]
	Local $iLastColumn = $aLastCell[1]
	
	; Return 0's if the sheet is blank
	If $sLastCell = "R1C1" And $oExcel.Activesheet.Cells(1, 1).Value = "" Then Return $avRET

comment:4 Changed 9 years ago by MrCreatoR <mscreator@…>

One more fix for another bug when the $sLastCell variable includes double info (not sure why), in this form: R120C30:R120C32.

The replace block is now changed to this (regexp changed, and added @error checking)

; Extract integer last row and col
	Local $aLastCell = StringRegExp($sLastCell, "\A(?:.*:)?[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3)
	If @error Then Return SetError(4, 0, 0) ;should not happen
	
	Local $iLastRow = $aLastCell[0]
	Local $iLastColumn = $aLastCell[1]
	
	; Return 0's if the sheet is blank
	If $sLastCell = "R1C1" And $oExcel.Activesheet.Cells(1, 1).Value = "" Then Return $avRET

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 Gary.
Author


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

 
Note: See TracTickets for help on using tickets.