Assign Calc 2d named range - Python

Creating a macro - Writing a Script - Using the API

Assign Calc 2d named range - Python

Postby bdika » Mon Mar 11, 2019 10:35 pm

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.
Libreoffice 6 on Arch Linux
bdika
 
Posts: 3
Joined: Mon Mar 11, 2019 10:27 pm

Re: Assign Calc 2d named range - Python

Postby RoryOF » Mon Mar 11, 2019 10:43 pm

Apache OpenOffice 4.1.6 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29434
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Assign Calc 2d named range - Python

Postby hubert lambert » Mon Mar 11, 2019 11:09 pm

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.
AOOo 4.1.2 on Win7 | LibreOffice on various Linux systems
hubert lambert
 
Posts: 129
Joined: Mon Jun 13, 2016 10:50 am

Re: Assign Calc 2d named range - Python

Postby FJCC » Mon Mar 11, 2019 11:27 pm

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)
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7257
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Assign Calc 2d named range - Python

Postby bdika » Thu Mar 14, 2019 6:49 am

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.
Libreoffice 6 on Arch Linux
bdika
 
Posts: 3
Joined: Mon Mar 11, 2019 10:27 pm

Re: Assign Calc 2d named range - Python

Postby Villeroy » Thu Mar 14, 2019 1:48 pm

The spreadsheet functions are even faster since they are written by professionals in C++.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27095
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Assign Calc 2d named range - Python

Postby hubert lambert » Thu Mar 14, 2019 2:47 pm

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.
AOOo 4.1.2 on Win7 | LibreOffice on various Linux systems
hubert lambert
 
Posts: 129
Joined: Mon Jun 13, 2016 10:50 am

Re: Assign Calc 2d named range - Python

Postby Villeroy » Thu Mar 14, 2019 4:55 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27095
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Assign Calc 2d named range - Python

Postby bdika » Wed Mar 20, 2019 8:45 pm

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",),)                   

Libreoffice 6 on Arch Linux
bdika
 
Posts: 3
Joined: Mon Mar 11, 2019 10:27 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 9 guests