Strange results returned from UDF

43 views
Skip to first unread message

Santa Ana Droning

unread,
Feb 12, 2023, 4:13:55 PM2/12/23
to xlSlim Support
I moving from Pyxll to Xlslim, and getting strange results from a couple of simple UDFs, that should not require any addtional Python libraries.  For example the UDF bellow, finds "oldWords" (in a range or table columnn), wthin "text", and replaces them with "newWords".   Nevertheless, in Xlslim, it distorts the original "text" and returns meaningless text.   Help!!!!

def REPLACETEXTS_py(text, oldWords, newWords):
    oldWordsArr = []
    newWordsArr = []
    for row in oldWords:
        for element in row:
            oldWordsArr.append(str(element))
    for row in newWords:
        for element in row:
            newWordsArr.append(str(element))
    for i in range(len(oldWordsArr)):
        text = text.replace(oldWordsArr[i], newWordsArr[i])
    return text

in...@adians.com

unread,
Feb 12, 2023, 4:16:24 PM2/12/23
to sup...@xlslim.com

xlSlim Dev

unread,
Feb 15, 2023, 4:20:49 AM2/15/23
to xlSlim Support
Hello,

Your function can be simplified to:

def REPLACETEXTS_py(text, oldWords, newWords):
    for oldWord, newWord in zip(oldWords, newWords):
        text = text.replace(oldWord, newWord)
    return text

This works fine for me.

xlSlim is passing the cell ranges as .Net arrays which usually behave just the same as Python lists. However if you really want Python lists the conversion is simply:
oldWords = list(oldWords)
newWords = list(newWords)

The best way to debug this sort of problem is to log the inputs being received by the function.

If I amend the code to:

import logging

LOG = logging.getLogger(__name__)

def REPLACETEXTS_py(text, oldWords, newWords):
    LOG.info("Received oldWords type %s data %s", type(oldWords), repr(oldWords))
    LOG.info("Received newWords type %s data %s", type(newWords), repr(newWords))
    oldWords = list(oldWords)
    newWords = list(newWords)
    LOG.info("After conversion to list, oldWords type %s data %s", type(oldWords), repr(oldWords))
    LOG.info("After conversion to list, newWords type %s data %s", type(newWords), repr(newWords))
    for oldWord, newWord in zip(oldWords, newWords):
        text = text.replace(oldWord, newWord)
    return text

I get these log messages:

2023-02-15 09:18:54,950 ReplaceWords: INFO Received oldWords type <class 'System.Object[,]'> data <System.Object[,] object at 0x0000023667694348>
2023-02-15 09:18:54,950 ReplaceWords: INFO Received newWords type <class 'System.Object[,]'> data <System.Object[,] object at 0x0000023667695108>
2023-02-15 09:18:54,950 ReplaceWords: INFO After conversion to list, oldWords type <class 'list'> data ['Mary', 'lamb']
2023-02-15 09:18:54,950 ReplaceWords: INFO After conversion to list, newWords type <class 'list'> data ['Bob', 'horse']

FYI, the log location can be found by using the LogLocation() function.

All the best,
Russel
Reply all
Reply to author
Forward
0 new messages