_Excel_RangeRead feature request

[iamtheky]

I would like to be able to dictate the return, rather than have to accommodate all possibilities every time I run this against a spreadsheet of unknown contents.  Here is my effort to normalize all data to a 2D array, in what is surely a non-optimal fashion.  Any other effective workarounds are welcome.  I feel like I have asked this for a previous project, but I can not find that thread.

 

#include <Excel.au3>

$xlName = FileOpenDialog("Select Excel File" , @ScriptDir , "Excel(*.xls;*.xlsx)" , 1)

$oExcel = _Excel_Open(False)
$oWorkBook = _Excel_BookOpen ($oExcel, $xlName)
$XLResult = _Excel_RangeRead($oWorkBook)
_Excel_BookClose($oWorkBook)
_Excel_Close($oExcel)

_ArrayDisplay(_2DResult($XLResult))


Func _2DResult($Result)

If IsString($Result) = 1 Then
    local $aTemp[1][2]
    $aTemp[0][0] = $Result
ElseIf Ubound($Result , 2) = 0 Then
    local $aTemp[ubound($result)][2]
    for $i = 0 to ubound($result) - 1
        $aTemp[$i][0] = $Result[$i]
    next
Else
    $aTemp = $Result
EndIf

return $aTemp

EndFunc

 

 

Edited May 22, 2015 by boththose

[water]

I remember this discussion and IIRC the _2DResult function was the result of this discussion.

The format of the returned data is defined by the Excel Transpose method.
So the enhanced _Excel_RangeRead function would (under the covers) do the same and translate the result.
 

[water]

I would enhance the function for less data movement:
 

Func _2DResult(ByRef $vResult)

    If IsString($vResult) = 1 Then
        Local $aTemp[1][2] = [[$vResult]]
        Return $aTemp
    ElseIf Ubound($vResult, 0) = 1 Then
        Local $aTemp[Ubound($vResult, 1)][1]
        For $i = 0 to Ubound($vResult) - 1
            $aTemp[$i][0] = $vResult[$i]
        Next
        Return $aTemp
    Else 
        Return $Result
    EndIf

EndFunc

 

[iamtheky]

and another feature request I may have made in the past:

If the cell occupied is not A1, return all cells A1 - the single item?  Or throw the cell it came from in @extended?

[water]

I'm sorry, I'm not 100% sure I understand what you mean.
Could you please give an example?

[iamtheky]

Sure, and the problem exists for returned arrays as well, when $aArray[0] (or $aArray[0][0]) does not coincide with A1.   This script will populate Column B IP addresses for all the names in column A.  However, if the first item found exists in any row below 1, I need to know which row the data started on, so I can start my rangewrite in that same location.

So create a spreadsheet, and put one name in A13, the result it is that it writes that name to A1 and the corresponding IP to B1 (and leaves that name in A13), because there is no way to determine that the item found was at A13.

expandcollapse popup

#include <Constants.au3>
#include <Excel.au3>

$xlName = FileOpenDialog("Select Excel File" , @ScriptDir , "Excel(*.xls;*.xlsx)" , 1)

$oExcel = _Excel_Open(False)
$oWorkBook = _Excel_BookOpen ($oExcel, $xlName)
$XLResult = _Excel_RangeRead($oWorkBook)

$aResult = _2DResult($XLResult)

For $i = 0 to ubound($aResult) - 1
    tooltip($aResult[$i][0] , 0 , 0)
    If $aResult[$i][1] = "" Then $aResult[$i][1] = NsKnowsIp($aResult[$i][0])
Next

_Excel_RangeWrite($oWorkBook , Default , $aResult)
_Excel_BookClose($oWorkBook)
_Excel_Close($oExcel)


Func NsKnowsIp($string)

    $Timer = TimerInit()
    $foo = Run("nslookup "& $string, @SystemDir, @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD)
    $output = ""
    $sOut = ""

    While 1
        $output &= StdoutRead($foo)
        If @error Then ExitLoop
        If TimerDiff($Timer) > 5000 Then
            run("taskkill /F /PID " & $foo, @SystemDir, @SW_HIDE)
            ExitLoop
        EndIf
    Wend

$aOut = stringsplit($output, @LF , 2)
If @Error > 0 Then Return $sOut

If ubound($aOut) > 4 Then $sOut = stringtrimleft($aOut[4] , 8)

run("taskkill /F /PID " & $foo, @SystemDir, @SW_HIDE)

   return $sOut

endfunc

Func _2DResult(ByRef $vResult)

    If IsString($vResult) = 1 Then
        Local $aTemp[1][2] = [[$vResult]]
        Return $aTemp
    ElseIf Ubound($vResult, 0) = 1 Then
        Local $aTemp[Ubound($vResult, 1)][2]
        For $i = 0 to Ubound($vResult) - 1
            $aTemp[$i][0] = $vResult[$i]
        Next
        Return $aTemp
    Else
        Return $vResult
    EndIf

EndFunc

 

Edited May 26, 2015 by boththose

[water]

I see.
When you do not use the range parameter to specify the range to be read the UsedRange is being returned.
When the usedrange does not start with A1 then you get an "offset".
In the wiki (https://www.autoitscript.com/wiki/Excel_Range) I describe how to return the UsedRange plus all cells to the left and top.

$oWorkbook.ActiveSheet.UsedRange.Cells(1, 1).Address

returns the address of the upper left corner of a range.
Does this help?

[water]

I see.
When you do not use the range parameter to specify the range to be read the UsedRange is being returned.
When the usedrange does not start with A1 then you get an "offset".
In the wiki (https://www.autoitscript.com/wiki/Excel_Range) I describe how to return the UsedRange plus all cells to the left and top.

$oWorkbook.ActiveSheet.UsedRange.Cells(1, 1).Address

returns the address of the upper left corner of a range.
Does this help?

[iamtheky]

That does it, thank you sir:

$xlName = FileOpenDialog("Select Excel File" , @ScriptDir , "Excel(*.xls;*.xlsx)" , 1)

$oExcel = _Excel_Open(False)
$oWorkBook = _Excel_BookOpen ($oExcel, $xlName)
$XLResult = _Excel_RangeRead($oWorkBook)

$sRange = stringreplace($oWorkbook.ActiveSheet.UsedRange.Cells(1, 1).Address , "$" , "")

$aResult = _2DResult($XLResult)

For $i = 0 to ubound($aResult) - 1
    tooltip($aResult[$i][0] , 0 , 0)
    If $aResult[$i][1] = "" Then $aResult[$i][1] = NsKnowsIp($aResult[$i][0])
Next

_Excel_RangeWrite($oWorkBook , Default , $aResult , $sRange)
_Excel_BookClose($oWorkBook)
_Excel_Close($oExcel)
exit

 

[water]

I just added this "trick" to the mentioned wiki article.

[water]

Another trick:

To convert formulas/cell references from R1C1 to A1 or vice versa or from absolute (e.g. $B$2) to relative (e.g. "B2") you could use:

_Excel_ConvertFormula($oAppl, "$B$2", $xlA1, $xlA1, $xlRelative)

Returns: B2.

[water]

Double post.

Edited May 26, 2015 by water