Copying a 1d array to a 2d array..

everseeker · January 4, 2008

OK, newbie type question, but here goes...

I need to load an excel sheet and copy a block of data to an array (say a 50x90 area...)

The only way I have found that works feels REALLY odd....

(Don't laugh too much...)

Open the Excel file
_ExcelBookOpen(FileOpenDialog("Load Excel Data File", "::{450D8FBA-AD25-11D0-98A8-0800361B1103}", "Data(*.xls)", 1, "RC Test Cases KY set.XLS"), 1, "False")

;Find the last cell
$array = _excelSheetUsedRangeGet($oExcel, 1)

;create the master Array
Global $numberOfCases = ($array[3])
Global $MasterArray[$numberOfCases + 5][90]
Global $grabrow[59]

;Load the array
Dim $fiddle = 1
For $Index = 1 To $numberOfCases Step 1
    $grabrow = _ExcelReadArray($oExcel, $Index, 1, 59, 0, 1)
    For $CellPos = 1 To 59
        $fiddle = $grabrow[$CellPos]
        $MasterArray[$Index][$CellPos] = $fiddle
    Next
Next

Is there a different function that can do this in 1 fell swoop?

moving an [x][y]Excel range to an [x][y]autoit array?

If not, is there a more...elegant... way to accomplish what I am doing by brute force?

Edited January 4, 2008 by everseeker

everseeker · January 4, 2008

In a more...general way...

Say I have a large array that is bigone[100][100]

and I am going to generate 100 1d arrays littleone[100]

How do I assign each of the littleones to 1 "row" of the bigone?

I am doing it cell by cell.... time (and memory) consuming!

but any assignment I have attempted with the 2 different dimensioned arrays in 1 line results in an error

Edited January 4, 2008 by everseeker

randallc · January 4, 2008

In a more...general way...
Say I have a large array that is bigone[100][100]
and I am going to generate 100 1d arrays littleone[100]
How do I assign each of the littleones to 1 "row" of the bigone?

I am doing it cell by cell.... time (and memory) consuming!
but any assignment I have attempted with the 2 different dimensioned arrays in 1 line results in an error

Hi,

You are right, that take stime.

The 2D array copies directly from Excel, though;

Func _XLArrayRead(ByRef $s_FilePath, $s_i_Sheet = 1, $s_i_ExcelValue = 1, $s_i_Visible = 0 ,$i_Suppress = 0)
    $var = _ExcelCOM($s_FilePath, $s_i_Sheet, 1, 1, "ArrayRead", "NoSave", $s_i_ExcelValue, $s_i_Visible  ,$i_Suppress, "NOTExit", "NotLastRow", "NOTToColumn"); LastRow excel
    ;$var=$s_i_ExcelValue
    Return $var
EndFunc   ;==>_XLArrayRead

See the link in my signature; also, I think, in LocoDarwin's funcs.

Best, Randall

everseeker · January 4, 2008

Hi,

You are right, that take stime.

The 2D array copies directly from Excel, though;

Func _XLArrayRead(ByRef $s_FilePath, $s_i_Sheet = 1, $s_i_ExcelValue = 1, $s_i_Visible = 0 ,$i_Suppress = 0)
    $var = _ExcelCOM($s_FilePath, $s_i_Sheet, 1, 1, "ArrayRead", "NoSave", $s_i_ExcelValue, $s_i_Visible  ,$i_Suppress, "NOTExit", "NotLastRow", "NOTToColumn"); LastRow excel
    ;$var=$s_i_ExcelValue
    Return $var
EndFunc   ;==>_XLArrayRead

See the link in my signature; also, I think, in LocoDarwin's funcs.

Best, Randall

Ummm...

Your signature link to ExcelCOM points to a thread that looks like it USED to contain your excelCOM.

I can't find yours in there. All I can see is lots of example files and documentation. no "ExcelCom.au3" file.

What I DO see is this "...I am recommending people consider LocoDarwin's (Yet Another) ExcelCOM UDF...", So I assume you pulled yours in deference to his version.

here's the problem... LocoDarwin's (Yet Another) ExcelCOM UDF does NOT have a function that handles importing a 2d array. (at least, nothing in the version I am running: Ver 1.32 02/12/07 )

All it has is 1D array manipulation via _ExcelReadArray and _ExcelWriteArray (Which I used in the code example I showed above)

So, Do you have a copy of your ExcelCom laying around that I could use? Or could you point me to the appropriate fuction/code example from your array handling UDF (Array2D.au3) that I could use?

From my code example above...

;Load the array
...
    $grabRow = _ExcelReadArray($oExcel, $Index, 1, 59, 0, 1)
...
How do I load the currently grabbed row ($GrabRow) into $MasterArray at the correct position   
 $MasterArray[$Index][????]=?????

PS Array2d has a lot of include files listed...

#include <Misc.au3>

#include <File.au3>

#include <Array.au3>

#include <GuiConstants.au3>

#include <GuiListView.au3>

are they all ...part of.... Autoit already? If not, where do I get them?

randallc · January 4, 2008

Hi,

1.

no "ExcelCom.au3" file.

It is there, inside the zip at the top of the post, 1600 downloads.

2. "Array2D" ; all those includes are part of the AutoIt install; note the updated version (Array2d10.zip) in the zip at the bottom of post #1 has amendments for current AutoIt syntax, and has been working OK as far as I know.

3. Any of the examples in the zip starting "FastSubSort2" have the subsort syntax, and many show the functions changing 2D arrays to 1D arrays of delimited strings and vice-versa.

Let me know..

Best, randall

Edited January 4, 2008 by randallc

PsaltyDS · January 4, 2008

I'm trying to learn some of the Office COM interface stuff from Locodarwin's excellent example. This is my attempt to morph his _ExcelReadArray() function into a 2D array that can read the entire active sheet by default, or be told to read a 2D range:

;===============================================================================
;
; Description:      Create a 2D array from the rows/columns of the active worksheet.
; Syntax:           _ExcelReadSheetToArray($oExcel, [$iStartRow , $iStartColumn [, $iRowCnt, $iColCnt]])
; Parameter(s):     $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)
; Requirement(s):   Requires ExcelCOM_UDF.au3
; Return Value(s):  On Success - Returns a 2D array with the specified cell contents by [$row][$col]
;                   On 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(s):        SEO <locodarwin at yahoo dot com> (original _ExcelReadArray() function)
; Modified:         PsaltyDS 01/04/08 - 2D version _ExcelReadSheetToArray()
; Note(s):          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.
;
;===============================================================================
Func _ExcelReadSheetToArray($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0)
    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, "R")
    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 = "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
    
    ; 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
    
    ;Return data
    Return $avRET
EndFunc   ;==>_ExcelReadSheetToArray

To read the entire current active sheet, just do: _ExcelReadSheetToArray($oExcel)

To read a 3x3 cell block starting at R7C9: _ExcelReadSheetToArray($oExcel, 7, 9, 3, 3)

To read the rest of the sheet from R7C9 to the end: _ExcelReadSheetToArray($oExcel, 7, 9)

To read two rows from R7C9 to the last column: _ExcelReadSheetToArray($oExcel, 7, 9, 2)

To read two columns from R7C9 to the last row: _ExcelReadSheetToArray($oExcel, 7, 9, 0, 2)

Hope that helps.

everseeker · January 5, 2008

I'm trying to learn some of the Office COM interface stuff from Locodarwin's excellent example. This is my attempt to morph his _ExcelReadArray() function into a 2D array that can read the entire active sheet by default, or be told to read a 2D range:

;===============================================================================
;
; Description:      Create a 2D array from the rows/columns of the active worksheet.
; Syntax:           _ExcelReadSheetToArray($oExcel, [$iStartRow , $iStartColumn [, $iRowCnt, $iColCnt]])
; Parameter(s):     $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)
; Requirement(s):   Requires ExcelCOM_UDF.au3
; Return Value(s):  On Success - Returns a 2D array with the specified cell contents by [$row][$col]
;                   On 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(s):        SEO <locodarwin at yahoo dot com> (original _ExcelReadArray() function)
; Modified:         PsaltyDS 01/04/08 - 2D version _ExcelReadSheetToArray()
; Note(s):          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.
;
;===============================================================================
Func _ExcelReadSheetToArray($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0)
    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, "R")
    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 = "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
    
    ; 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
    
    ;Return data
    Return $avRET
EndFunc   ;==>_ExcelReadSheetToArray

To read the entire current active sheet, just do: _ExcelReadSheetToArray($oExcel)

To read a 3x3 cell block starting at R7C9: _ExcelReadSheetToArray($oExcel, 7, 9, 3, 3)

To read the rest of the sheet from R7C9 to the end: _ExcelReadSheetToArray($oExcel, 7, 9)

To read two rows from R7C9 to the last column: _ExcelReadSheetToArray($oExcel, 7, 9, 2)

To read two columns from R7C9 to the last row: _ExcelReadSheetToArray($oExcel, 7, 9, 0, 2)

Hope that helps.

Yipee!!!

I love it! Quite readable...

Ummm... by any chance, are you working on _ExcellWriteSheetFromArray() .........

PsaltyDS · January 5, 2008

Yipee!!!
I love it! Quite readable...
Ummm... by any chance, are you working on _ExcellWriteSheetFromArray() .........

By popular demand (of a total of one person):

;===============================================================================
;
; Description:      Writes a 2D array to the active worksheet
; Syntax:           _ExcelWriteSheetFromArray($oExcel, ByRef $aArray [, $iStartRow = 1, $iStartColumn = 1 [, $iRowBase = 1, $iColBase = 1]])
; Parameter(s):     $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $aArray - The array ByRef to write data from (array is not modified)
;                   $iStartRow - The table row to start writing the array to, default is 1
;                   $iStartColumn - The table column to start writing the array to, default is 1
;                   $iRowBase - array index base for rows, default is 1
;                   $iColBase - array index base for columns, default is 1
; Requirement(s):   ExcelCOM_UDF.au3
; Return Value(s):  On Success - Returns 1
;                   On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - Parameter out of range
;                           @extended=0 - $iStartRow out of range
;                           @extended=1 - $iStartColumn out of range
;                       @error=3 - Array invalid
;                           @extended=0 - doesn't exist / variable is not an array
;                           @extended=1 - not a 2D array
;                       @error=4 - Base index out of range
;                           @extended=0 - $iRowBase out of range
;                           @extended=1 - $iColBase out of range
; Author(s):        SEO <locodarwin at yahoo dot com> (original ExcelWriteArray() function)
; Modified:         PsaltyDS 01/04/08 - 2D version _ExcelWriteSheetFromArray()
; Note(s):          Default base indexes in the array are both = 1, so first cell written is from $aArray[1][1].
;
;===============================================================================
Func _ExcelWriteSheetFromArray($oExcel, ByRef $aArray, $iStartRow = 1, $iStartColumn = 1, $iRowBase = 1, $iColBase = 1)
    ; 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 Not IsArray($aArray) Then Return SetError(3, 0, 0)
    Local $iDims = UBound($aArray, 0), $iLastRow = UBound($aArray, 1) - 1, $iLastColumn = UBound($aArray, 2) - 1
    If $iDims <> 2 Then Return SetError(3, 1, 0)
    If $iRowBase > $iLastRow Then Return SetError(4, 0, 0)
    If $iColBase > $iLastColumn Then Return SetError(4, 1, 0)
    
    For $r = $iRowBase To $iLastRow
        $iCurrCol = $iStartColumn
        For $c = $iColBase To $iLastColumn
            $oExcel.Activesheet.Cells($iStartRow, $iCurrCol).Value = $aArray[$r][$c]
            $iCurrCol += 1
        Next
        $iStartRow += 1
    Next
    Return 1
EndFunc   ;==>_ExcelWriteSheetFromArray

Can't test it 'cause I don't have Excel here for the weekend. Please test if you have the chance.

everseeker · January 9, 2008

Can't test it 'cause I don't have Excel here for the weekend. Please test if you have the chance.

Was out of the Office...

Will test it today

PsaltyDS · January 9, 2008

Was out of the Office...
Will test it today

Take note that Locodarwin has posted a new version of ExcelCOM_UDF.au3, which includes these functions and other new stuff. The new version is 1.4 dated 05 January, 2008.

everseeker · January 9, 2008

Take note that Locodarwin has posted a new version of ExcelCOM_UDF.au3, which includes these functions and other new stuff. The new version is 1.4 dated 05 January, 2008.

OIC!

Very nice!!!

(Congrats on the include )

Copying a 1d array to a 2d array..

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members