Jump to content
Sign in to follow this  
JCarson

Excel ReadFrom / Write To array

Recommended Posts

JCarson

Alas, not enough posts to post in the "Example Scripts Forum".

This is by no means a finished script, simply a quick fix as I could feel my beard grow waiting for excel files to load.

My hope is that the Excel UDF would be updated with similar code.

This requires the Array2D include.

These are simply a modification of the same functions in Excel UDF to allow for more of a "copy and paste" as opposed to the 1 cell at a time that was being done. This affects whole sheets only, although I figure that someone with more time than I can adapt those two functions to take advantage of this for all the variances of the function.

Verified working with latest non-beta AutoIT, Windows XP SP2 with all subsequent updates and Excel 2003.

Let me know if it is of any help,

Carson

Func _ExcelWriteSheetFromArray2($oExcel, ByRef $bArray)
    $AARRAY = $BARRAY
    _ARRAY2D_DELETE($AARRAY, 0, 1)
    _ARRAY2D_DELETE($AARRAY, 0, 2)
    _ARRAY2D_TRANSPOSE($AARRAY)
    With $OEXCEL.Activesheet
        .Range(.Cells(1, 1), .Cells(UBound($AARRAY, 2), UBound($AARRAY, 1)) ).Value = $AARRAY
    EndWith
Return 1
EndFunc

Func _ExcelReadSheetToArray2($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[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
            ProgressSet(($r/$iRowCnt)*100,"","Completed " & int(($r/$iRowCnt)*100) & "%")   
        Next
    Else ;Default for $iColShift
    With $oExcel.Activesheet
        $avRET2=.Range(.Cells(1,1),.Cells($iRowCnt,$iColCnt)).Value
        _Array2D_Transpose($avRET2)
        _Array2D_Insert($avRET2,0,"",2)
        _Array2D_Add($avRET, $avRET2)
        $avRET2=""
    endwith
    EndIf
    ;Return data
    Return $avRET
EndFunc

Share this post


Link to post
Share on other sites
PsaltyDS

Yeah, we had a topic a few weeks ago where I learned you could get/put arrays via the COM interface to Excel. (I didn't know you could do that before.)

It is much faster than iterating single cells, and the technique is worth incorporating in other Excel.au3 UDF functions, too.

:idea:

Edit: Added link to older topic

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
JCarson

Apologies, despite my status on this forum, I do try to search before posting.

For the record on the benefits of AutoIT, my previous solution was VBA code and was taking about 2 hours despite using "manual calculation" and "update=false" tricks amongst other things.

To testify to the benefit of AutoIT, adapting the same logic of the VBA, once I changed the Excel Read/Write functions, yielded returned results in 3-6 minutes .. a 20-40x speed increase.

Yeah, we had a topic a few weeks ago where I learned you could get/put arrays via the COM interface to Excel. (I didn't know you could do that before.)

It is much faster than iterating single cells, and the technique is worth incorporating in other Excel.au3 UDF functions, too.

:idea:

Edit: Added link to older topic

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.