Assign Calc 2d named range - Python

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
bdika
Posts: 3
Joined: Mon Mar 11, 2019 10:27 pm

Assign Calc 2d named range - Python

Post by bdika »

I can't seem to find a simple answer to the question. I have this successfully working in Basic:

Code: Select all

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
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Assign Calc 2d named range - Python

Post by RoryOF »

Also at
https://ask.libreoffice.org/en/question ... -variable/

Other posting noted to prevent fragmentation of discussion.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
hubert lambert
Posts: 145
Joined: Mon Jun 13, 2016 10:50 am

Re: Assign Calc 2d named range - Python

Post 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

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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Assign Calc 2d named range - Python

Post by FJCC »

It is always helpful to have mri (or Xray) available to look at your objects.

Code: Select all

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)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
bdika
Posts: 3
Joined: Mon Mar 11, 2019 10:27 pm

Re: Assign Calc 2d named range - Python

Post 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

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

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

# -*- 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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Assign Calc 2d named range - Python

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
hubert lambert
Posts: 145
Joined: Mon Jun 13, 2016 10:50 am

Re: Assign Calc 2d named range - Python

Post 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.
AOOo 4.1.2 on Win7 | LibreOffice on various Linux systems
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Assign Calc 2d named range - Python

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
bdika
Posts: 3
Joined: Mon Mar 11, 2019 10:27 pm

Re: Assign Calc 2d named range - Python

Post 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

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

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

 # -*- 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
Post Reply