Hi,
ich habe die Funktion ExcelReadSheetToArray getestet.
Dabei ist mir aufgefallen, dass diese nicht funktioniert.
Nutze Office 2003 deutsch.
Denke es liegt an der Sprache.
Ich habe mal ein Bsp mit der originalen und geänderten Funktion angehängt.
Das Problem ist, dass die Funktion mit "R" (Row, Zeile) und "C" (Column, Spalte) arbeitet.
Im deutschen heißt es jedoch "Z" und "S".
Ist das bei euch auch so?
Sollte man im engl. mal posten, oder?
Spoiler anzeigen
#include <Excel.au3>
#include <Array.au3>
;*********************************************************************************************************************************************
[/autoit] [autoit][/autoit] [autoit];so gehts
[/autoit] [autoit][/autoit] [autoit]Local $oExcel = _ExcelBookNew() ;Create new book, make it visible
[/autoit] [autoit][/autoit] [autoit]; We can fill-up some cells using a simple loop and random Numbers
For $y = 1 To 10 ;Start on Column 1
For $x = 1 To 15
_ExcelWriteCell($oExcel, Round(Random(1000, 10000), 0), $x, $y) ;Some random numbers to file
Next
Next
$aArray = _ExcelReadSheetToArray_TEST($oExcel) ;Using Default Parameters
ConsoleWrite(@error & @CRLF & @extended & @CRLF)
_ArrayDisplay($aArray, "Array using Default Parameters")
_ExcelBookClose($oExcel, 0, 0)
[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit];*********************************************************************************************************************************************
[/autoit] [autoit][/autoit] [autoit];so gehts nicht
[/autoit] [autoit][/autoit] [autoit]Local $oExcel = _ExcelBookNew() ;Create new book, make it visible
[/autoit] [autoit][/autoit] [autoit]; We can fill-up some cells using a simple loop and random Numbers
For $y = 1 To 10 ;Start on Column 1
For $x = 1 To 15
_ExcelWriteCell($oExcel, Round(Random(1000, 10000), 0), $x, $y) ;Some random numbers to file
Next
Next
$aArray = _ExcelReadSheetToArray($oExcel) ;Using Default Parameters
ConsoleWrite(@error & @CRLF & @extended & @CRLF)
_ArrayDisplay($aArray, "Array using Default Parameters")
_ExcelBookClose($oExcel, 0, 0)
[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit];Es folgt die geänderte Funktion aus der Excel.au3
[/autoit] [autoit][/autoit] [autoit]; #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_TEST($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
Local $iLastRow = StringInStr($sLastCell, "Z")
;~ Local $iLastRow = StringInStr($sLastCell, "R")
Local $iLastColumn = StringInStr($sLastCell, "S")
;~ Local $iLastColumn = StringInStr($sLastCell, "C")
$iLastRow = Number(StringMid($sLastCell, $iLastRow + 1, $iLastColumn - $iLastRow - 1))
$iLastColumn = Number(StringMid($sLastCell, $iLastColumn + 1))
; Return 0's if the sheet is blank
If $sLastCell = "Z1S1" And $oExcel.Activesheet.Cells($iLastRow, $iLastColumn).Value = "" Then Return $avRET
;~ 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_TEST
Warum muss Excel im COM-Interface auch mit lokalisierten Namen arbeiten?