[Solved] Avoid Calc functions in subroutine

[luofeiyu]

The target sheet is simple.

https://i.sstatic.net/frSmYl6t.png

I want to find which district's vol is 580,to ype "=INDEX(A2:A3,MATCH(580,B2:B3,0))" in B6 can get it.
And i can get the district name with below code:

Code: Select all

sub call_nested_calc()
    oFunAccess = createUnoService( "com.sun.star.sheet.FunctionAccess" )    
    oDoc = ThisComponent
    oSheet = oDoc.sheets.getByName("Sheet15")
    with oSheet
    	a_Range = .getCellRangeByName("a2:a3")
    	b_Range = .getCellRangeByName("b2:b3")
    end with	
    with oFunAccess
    	district = .CallFunction( "index",array( a_Range, .CallFunction("match", array(580, b_Range, 0))))
    end with
    print district
end sub

How can simplify the oobasic codes to get the result?Other way ,not to call worksheet function.

[karolus]

…
Other way ,not to call worksheet function.

use python, eg:

Code: Select all

doc = XSCRIPTCONTEXT.getDocument()
data = doc.Sheets["Sheet15"]["A2:B28"].DataArray
countries, values = list(zip(*data))
print( countries[values.index(52)])