Page 1 of 1

Assign Calc 2d named range - Python

PostPosted: Mon Mar 11, 2019 10:35 pm
by bdika
I can't seem to find a simple answer to the question. I have this successfully working in Basic:
Code: Select all   Expand viewCollapse view
NamedRange = ThisComponent.NamedRanges.getByName("transactions_detail")

RefCells = NamedRange.getReferredCells()

Set MainRange = RefCells.getDataArray()

Then I iterate over MainRange and pull out the rows I am interested in.

Can I do something similar in a python macro? Can I assign a 2d named range to a python variable or do I have to iterate over the range to assign the individual cells?

I am new to python but hope to convert my iteration intensive macro function to python in hopes of making it faster.

Any help would be much appreciated.

Thanks.

Re: Assign Calc 2d named range - Python

PostPosted: Mon Mar 11, 2019 10:43 pm
by RoryOF

Re: Assign Calc 2d named range - Python

PostPosted: Mon Mar 11, 2019 11:09 pm
by hubert lambert
Hi,

You should have a look at this page on the wiki : Transfer from Basic to Python.
Basic arrays and UNO sequences are mapped to python tuples. Here is a translation of your snippet:
Code: Select all   Expand viewCollapse view
def bdika(event=None):
    doc = XSCRIPTCONTEXT.getDocument()
    NamedRange = doc.NamedRanges.getByName("transactions_detail")
    RefCells = NamedRange.getReferredCells()
    MainRange = RefCells.getDataArray()
    
# for row in MainRange:
    #     for cell in row:
    #         pass
 

Regards.

Re: Assign Calc 2d named range - Python

PostPosted: Mon Mar 11, 2019 11:27 pm
by FJCC
It is always helpful to have mri (or Xray) available to look at your objects.
Code: Select all   Expand viewCollapse view
import uno

def getNameRange():
  oDoc = XSCRIPTCONTEXT.getDocument()
  oNamedRngs = oDoc.NamedRanges
  oRng = oNamedRngs.getByName("transactions_detail")
  oRefCells = oRng.getReferredCells()
  oDataArray = oRefCells.getDataArray()
  mri(oDataArray[2])  #get the third row as a tuple

def mri(target):
  ctx = uno.getComponentContext()
  mri = ctx.ServiceManager.createInstanceWithContext("mytools.Mri", ctx)
  mri.inspect(target)

Re: Assign Calc 2d named range - Python

PostPosted: Thu Mar 14, 2019 6:49 am
by bdika
Thank you FJCC & hubert lambert:

I have been hard at work since your excellent pointers.

I have a working function which I call from basic. It works once the calc is opened but I get two Basic error messages when I open calc before the spreadsheet appears. The first message comes up approximately thirty times (ie once I press OK to remove the dialog box it immediately reappears for about thirty presses of the OK button). I think this happens once for each instance of the function in my worksheet. This message appears to relate to NamedRanges not available in the document. I think this has to do with the location of the python script in my /home/user/.config/libreoffice/4/user/Scripts/python directory. This location works once the file is open. I am using location=user.

Code: Select all   Expand viewCollapse view
sURL = "vnd.sun.star.script:my-python-macros.py$AveCostPerShare?language=Python&location=user"


I have tried location=share, location=document and location=application. All without success. I think location=document is the location that should work since my Basic and python scripts are in the document. I have to copy the py script to the user directory separately after I have saved it. I am using APSO to write my python scripts.

My Basic function to call the python function is:

Code: Select all   Expand viewCollapse view
Function AveCostPerShare(p1,p2)
   Dim sURL as String
   Dim oMSP as Object
   Dim oScript as Object
   sURL = "vnd.sun.star.script:my-python-macros.py$AveCostPerShare?language=Python&location=user"
    oMSP = getMasterScriptProvider()
    oScript = oMSP.getScript(sURL)
    AveCostPerShare = oScript.invoke(Array(p1,p2), Array(), Array())
End Function

Function getMasterScriptProvider()
'   Dim g_MasterScriptProvider as Object
   Dim oMasterScriptProviderFactory as Object
    if isNull(g_MasterScriptProvider) then
      oMasterScriptProviderFactory = createUnoService("com.sun.star.script.provider.MasterScriptProviderFactory")
      g_MasterScriptProvider = oMasterScriptProviderFactory.createScriptProvider("")
    endif
   getMasterScriptProvider = g_MasterScriptProvider
End Function



Part of my python script is:

Code: Select all   Expand viewCollapse view
# -*- coding: utf-8 -*-
from __future__ import unicode_literals
import uno
import unohelper
#from operator import itemgetter

def AveCostPerShare(share = "NEI SELECT GROWTH PORTFOLIO", ldate = 43404):
  oDoc = XSCRIPTCONTEXT.getDocument()
  oNamedRngs = oDoc.NamedRanges
  oRng = oNamedRngs.getByName("transactions_detail")
  oRefCells = oRng.getReferredCells()
  oDataArray = oRefCells.getDataArray()
  cnt = 0
  i = 0
  units = 0
  Reduction = 0
  TotalCost = 0
  AveCost = 0
  oFilteredArray = [[ 0 for j in range(4)] for i in range(100)]  #max 100 transactions
  for i in range(0, len(oDataArray) - 1):
        if ((oDataArray[i][7] == share) and ((oDataArray[i][5] == "Purchase") or (oDataArray[i][5] == "Disposition") or (oDataArray[i][6] == "Dividend Reinvestment") or (oDataArray[i][6] == "Return of Capital")) and (oDataArray[i][10] <= ldate)):
               
                oFilteredArray[cnt][0] = oDataArray[i][10]
                oFilteredArray[cnt][1] = oDataArray[i][9]
                oFilteredArray[cnt][2] = oDataArray[i][6]
                if isinstance(oDataArray[i][8], str):
                        oFilteredArray[cnt][3] = 0
                else:
                        oFilteredArray[cnt][3] = (oDataArray[i][8])
                cnt = cnt + 1
 
  if cnt == 0: # if no eligible transactions
    return 0

  del oFilteredArray[cnt:100] #delete the empty list items
       
  oFilteredArray.sort(key=lambda x: x[0])  #sort in place

  for i in range(0, cnt):

    if (oFilteredArray[i][3] > 0):
        units = units + oFilteredArray[i][3]
        TotalCost = TotalCost + oFilteredArray[i][1]
        if units == 0:
            AveCost = 0
        else:
            AveCost = TotalCost/units

    if (oFilteredArray[i][2] == "Return of Capital"):
        TotalCost = TotalCost - oFilteredArray[i][1]
        if units == 0:
            AveCost = 0
        else:
            AveCost = TotalCost / units

    if (oFilteredArray[i][3] < 0):
        units = units + oFilteredArray[i][3]
        Reduction = oFilteredArray[i][3] * AveCost
        TotalCost = TotalCost + Reduction
        if units == 0:
            AveCost = 0
        else:
            AveCost = TotalCost / units
   
    if TotalCost < .00001 or units < .00001:
        AveCost = 0

  AveCostPerShare = AveCost

  return AveCostPerShare

                   
#  mri(oFilteredArray)



The error messages I'm getting are :

the first error message which appears approximately thirty times is


python-function-error-on-startup-1.png



the secord error which I have to dismiss approximately six or seven times is

python-function-error-on-startup-2.png


Any help would be appreciated, since the python function works accurately and is much faster than the Basic function that I was using.

If could get a trouble free start up I would be all set.

Re: Assign Calc 2d named range - Python

PostPosted: Thu Mar 14, 2019 1:48 pm
by Villeroy
The spreadsheet functions are even faster since they are written by professionals in C++.

Re: Assign Calc 2d named range - Python

PostPosted: Thu Mar 14, 2019 2:47 pm
by hubert lambert
It seems that the ScriptContext object is not fully accessible when formulas are evaluated.
If you only need the document model to retrieve the values of the named range, why not passing that range as parameter of your function ?
If you need the document model for more complex stuff, than you should think about creating an addin.

Re: Assign Calc 2d named range - Python

PostPosted: Thu Mar 14, 2019 4:55 pm
by Villeroy
A function named "AveCostPerShare" indicates that someone tries to mimic an aggregation query which is a database feature and has not so much to do with spreadsheets. Aggregation queries in spreadsheet programs are called "pivot tables". A pivot table is a feature that is somewhat "borrowed" from database applications.

Indeed, a sheet function takes nothing but numbers and texts as arguments and returns one number or one text unless it is designed as an array function.

Re: Assign Calc 2d named range - Python

PostPosted: Wed Mar 20, 2019 8:45 pm
by bdika
Hi hubert lambert:

Thanks for your response.

If you only need the document model to retrieve the values of the named range, why not passing that range as parameter of your function ?


I am not sure of how to go about this. Besides that, the range name will never change although the size of the range will.

If you need the document model for more complex stuff, than you should think about creating an addin.


I have tried this but run into a similar problem with the named range. Below you can see my addin attempt. I have tried various combinations of the commented statements but get a VALUE error every time I enter the function into the spreadsheet. The only way I can get the addin to return 25 is to comment out all of the statements at the beginning of the function. What I am trying to do is to get the addin to recognise the document it is loaded into and get the named range from that document so that it can be manipulated. In my searches to solve this problem, it appears that

Code: Select all   Expand viewCollapse view
oDoc = XSCRIPTCONTEXT.getDocument


is the required code but like all the other statements, it gives me a VALUE error every time I enter it into the spreadsheet and does not reach the

Code: Select all   Expand viewCollapse view
return 25


statement.

My python code attempt at creating an addin is below. Any help to solve this problem which appears no matter how I try to access the named range, would be much appreciated.

Code: Select all   Expand viewCollapse view
# -*- coding: utf-8 -*-
from __future__ import unicode_literals
import os
import uno
import unohelper
import sys

from com.billdika.Financial.Shares import XBillDika

class BillDikaImpl(unohelper.Base, XBillDika ):

    def __init__( self, ctx ):
            self.ctx = ctx



    def AveCostPerShare( self, share, ldate ):
        #StarDesktop = createUnoService ( "com.sun.star.frame.Desktop" )
        #local = uno.getComponentContext()
        #resolver = local.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", local)
        #context = resolver.resolve("uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext")
        #desktop = context.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop", context)
        #document = desktop.getCurrentComponent()
        #oDesktop = self.ctx.ServiceManager.createInstanceWithContext( "com.sun.star.frame.Desktop", self.ctx)
        #oDoc = oDesktop.getCurrentComponent()
        #oDoc = XSCRIPTCONTEXT.getDesktop()
        #oDoc = XSCRIPTCONTEXT.getDocument()
        #oNamedRngs = oDoc.NamedRanges
        #oRng = NamedRanges.getByName("transaction_details")           
        #oRefCells = oRng.getReferredCells()
        #oDataArray = oRefCells.getDataArray()
       
        cnt = 0
        return 25
        i = 0
        units = 0
        Reduction = 0
        TotalCost = 0
        AveCost = 0

        oFilteredArray = [[ 0 for j in range(4)] for i in range(100)]  #max 100 transactions

        for i in range(0, len(oDataArray) - 1):
            if ((oDataArray[i][7] == share) and ((oDataArray[i][5] == "Purchase") or (oDataArray[i][5] == "Disposition") or (oDataArray[i][6] == "Dividend Reinvestment") or (oDataArray[i][6] == "Return of Capital")) and (oDataArray[i][10] <= ldate)):
                   
                    oFilteredArray[cnt][0] = oDataArray[i][10]
                    oFilteredArray[cnt][1] = oDataArray[i][9]
                    oFilteredArray[cnt][2] = oDataArray[i][6]
                    if isinstance(oDataArray[i][8], str):
                            oFilteredArray[cnt][3] = 0
                    else:
                            oFilteredArray[cnt][3] = (oDataArray[i][8])
                    cnt = cnt + 1
   
        if cnt == 0: # if no eligible transactions
            return 0

        del oFilteredArray[cnt:100] #delete the empty list items
           
        oFilteredArray.sort(key=lambda x: x[0])  #sort in place

        for i in range(0, cnt):

            if (oFilteredArray[i][3] > 0):
                units = units + oFilteredArray[i][3]
                TotalCost = TotalCost + oFilteredArray[i][1]
                if units == 0:
                    AveCost = 0
                else:
                    AveCost = TotalCost/units

            if (oFilteredArray[i][2] == "Return of Capital"):
                TotalCost = TotalCost - oFilteredArray[i][1]
                if units == 0:
                    AveCost = 0
                else:
                    AveCost = TotalCost / units

            if (oFilteredArray[i][3] < 0):
                units = units + oFilteredArray[i][3]
                Reduction = oFilteredArray[i][3] * AveCost
                TotalCost = TotalCost + Reduction
                if units == 0:
                    AveCost = 0
                else:
                    AveCost = TotalCost / units
           
            if TotalCost < .00001 or units < .00001:
                AveCost = 0

        AveCostPerShare = AveCost

        return AveCostPerShare


def createInstance( ctx ):
  return BillDikaImpl( ctx )

g_ImplementationHelper = unohelper.ImplementationHelper()
g_ImplementationHelper.addImplementation( \
        createInstance,"com.billdika.Financial.Shares.BillDika.python.BillDikaImpl",
                ("com.sun.star.sheet.AddIn",),)