Can XPySort work in LibreOffice?

English
, , ,
#1

Hi,

I’m migrating an OO workbook to LO. I make use of the array sort function extension XPySort in OO but, try as I might, I can’t make it work in LO. I install it and the installation seems to proceed without error, but when I try to use it, I get VALUE! errors; the same errors I get when I try to invoke the function without installing the extension.

Is there any way to make this function work in LO? Is there a similar array sort function that does?

Thanks for any help.

Edited to add: File uploaded.Test sort LibO.ods

#2

From list of error codes in Calc:
519 #VALUE No result (#VALUE is in the cell rather than Err:519!) - The formula yields a value that does not correspond to the definition; or a cell that is referenced in the formula contains text instead of a number.

Thus I’d suppose the error is not related to the extension, but to the type of data in some cells. But one thing os absolutely weird. How could you call a function from an extension without the extension being installed (according to …the same errors I get when I try to invoke the function without installing the extension). This normally would result in#NAME? error. Could you upload an anonymized version of your file?

#3

Thanks for the quick response. I originally developed this workbook in OO, where the extension was installed. Then I opened the file in LO – before installing the extension – and got the VALUE! errors. Those errors prompted me to install the extension. When I did, the VALUE! errors did not go away.

I have a small file that illustrates this. If you open the file in OO Calc with this extension installed, there will be no errors. If you open the same file in LO with the extension installed, you’ll get the VALUE! errors.

I’m new here and don’t know how to upload a file. If you’ll give me a hint, I’ll upload the file. Sorry, I figured out how to upload a file to the original question. You should see the file there.

Thanks again very much.

#4

Hello again,

Before I posted here, I posted this question over on the OpenOffice forums here When I didn’t get a response, I thought I’d try here. I didn’t not intentionally cross-post.

Villeroy suspects a Python2 vs. Python3 issue but doesn’t have time to debug now, and I understand.

I don’t know Python at all, so I’m not in a position to debug myself.

I’m in no particular hurry, but any help is really appreciated.

Thanks.

#5

I think that this part:

the same errors I get when I try to invoke the function without installing the extension

is incorrect (based on false memory): I have tested the attached document (without the “extension” installed), and indeed, it results in #NAME?, as @anon73440385 reasonably expected.

#6

Sorry for the misdirection. Indeed, I saw a page full of errors and was prompted to install the extension, whereupon I was greeted with another page full of errors. I didn’t carefully note the errors the first time, and ended up assuming I had seen the same error the second time.

#7

Error message:

<class 'NameError'>: name 'unicode' is not defined, traceback follows
  File "[path]\user\uno_packages\cache\uno_packages\lu79842zjpdl.tmp_\XPySort.oxt\PySort.py", line 25, in PySort
    specs = CheckFields(fieldspecs,len(inRange[0]))  #Checks fields for validity
  File "[path]\user\uno_packages\cache\uno_packages\lu79842zjpdl.tmp_\XPySort.oxt\PySort.py", line 254, in CheckFields
    testfields = [tuple([fieldnumbersout[i]] + [getfieldvalue(inArray[i][1],j) if speclen0 > 1 else 0 for j in range(0,4)]) for i in range(speclen)]
  File "[path]\user\uno_packages\cache\uno_packages\lu79842zjpdl.tmp_\XPySort.oxt\PySort.py", line 254, in <listcomp>
    testfields = [tuple([fieldnumbersout[i]] + [getfieldvalue(inArray[i][1],j) if speclen0 > 1 else 0 for j in range(0,4)]) for i in range(speclen)]
#8

… continued

  File "[path]\user\uno_packages\cache\uno_packages\lu79842zjpdl.tmp_\XPySort.oxt\PySort.py", line 254, in <listcomp>
    testfields = [tuple([fieldnumbersout[i]] + [getfieldvalue(inArray[i][1],j) if speclen0 > 1 else 0 for j in range(0,4)]) for i in range(speclen)]
  File "[path]\user\uno_packages\cache\uno_packages\lu79842zjpdl.tmp_\XPySort.oxt\PySort.py", line 272, in getfieldvalue
    if type(s) == unicode:

See Strings, Bytes, and Unicode in Python 2 and 3 - Timothy Bramlett for some description; it looks like type ‘unicode’ from Python 2 is ‘str’ in Python 3.

Just tested: simply replacing unicode with str in the archive’s PySort.py and installing that edited version fixes this. No idea how to make it working in both applications (likely need something like top-leven type variable initialized by corresponding type conditionally).

#9

The problem is that the extension’s Python code is written for Python 2 (used in OOo and AOO), and uses unicode type when refers to Unicode strings, while Python 3 (used in LibreOffice) uses str for those strings.

The problem is fixed (and the code is compatible with both office suites) by adding this to the PySort.py (and installing the resulting package):

 # SortArray Calc Add-in implementation.
 NUMBER_RE = re.compile(r'[1-9]\d*')
 
+# unicode from Python 2 is str in Python 3
+try:
+    unicode
+except NameError:
+    unicode = str
+
 class PySortImpl( unohelper.Base, XPySort ):
     def __init__( self, ctx ):
         self.ctx = ctx
#10

(OT) By the way: Can someone imagine for what reasons neither Calc nor LibreOffice BASIC provide efficient (or any compiled) functions for sorting?
Lots of efficient algorithms are available (among them -under some restrictions- linear time-complexity), but implementing them in BASIC is unnecessariy effort - and slows them down by a factor of 100 or higher.

#11

I don’t think that it’s possible to come with a generally usable solution to this. The real used algorithm would likely need too many arguments: besides the array, it needs the order of sort columns, and the sort rules for each column. E.g., “natural sort”, or lexicographic sort; and language conventions for the text-based sort; sometimes custom sort rules … While it’s possible for a dialog in UI (which could use e.g. locally-defined sort rules), it’s hardly possible for a function in Calc, or for Basic where no lambdas or function-as-parameter is possible…

#12

An enhanced SortDescriptor might do. The central task should be the creation of an array-like object capable of calling a Sort method applying the descriptor.
Concerning the language dependent settings a Locale object should do. Beyond that sorting of texts (word lists) is a mess anyway. The lack of lambda and of procedural parameters should not be essential if the algorithms can be addressed via an API element.

#13

Hi,

@mikekaganski, Is there a new version of the .oxt file that incorporates your change for me to download and test in my workbook? I’m not familiar with Python and don’t know how to build a package, but I’ll learn if that’s for me to do.

Thanks for your help.

#14

@FrogFan: see my post to the original extension topic at https://forum.openoffice.org.

#15

Thank you, Mike Kaganski! I’ve downloaded it and it is working great. Problem solved.

#16

A note for myself: to debug this kind of problem, a breakpoint may be put to ScUnoAddInCall::ExecuteCallWithArgs in the catch handlers; and then inspect rWrapped for the useful wrapped exception’s message.

#17

(OT cont’d) @Lupp: so you seem to agree that in case of Calc, the imagined spreadsheet function would have overwhelmingly more complicated usage than all the current functions. Let’s discuss the API case. You propose to add some API just to allow users to use some (useful) algorithm. But that’s not how it’s done in LibreOffice (and its predecessors). We don’t create APIs for the only purpose of being available to scripts. They are added only for more important goals: to access some functionality available in core; to ensure compatibility (with other suites - see VBA set of APIs); to make use of other APIs possible (some Basic things like ConvertToURL fall into that category). Making some new utility API just because “we can do it” kind of makes LibreOffice pretend to be a “general-purpose library”, which it is not.

#18

(again OT as above mentioned)
Of course you are right under relevant aspects, and as a developer you know most issues concerning the context much better than I can. However, I mainly had in mind the sorting algorithm(s) already “available in core”. There is no standard function making them available for sorting arrays, and user code trying to make them available has to accept a lot of overhead (temporary helper sheet e.g.) very likely giving away the gained efficiency and more.

#19

You refer to the Sort tool. But having that implemented only as GUI, and not as API, allows to avoid making the tool “set in stone”: any API is a contract both in terms of syntax and behaviour, and any API change is a breaking change. Most times, when we need to change APIs, we have to add new version of them, keeping the legacy ones for compatibility (and so, increasing maintenance cost). So making the sort tool an API would be less than you wrote (it would only be a table-sorting thing, not a general-purpose algorithm - or else we’d need to expand/change its implementation considerably, making this not exposing something already available in core), while at the same time, make it next to impossible to modify and improve it. Even any bug fix in the sorting algorithm would be a breakage of someone’s custom macro. Not everything implemented as a user tool in the program should be exposed through API.

#20

Sorry to resurrect an old thread, but since I was a participant in this one, it seems like an appropriate place to return.

I have used the revised pysort code developed by @mikekaganski for a couple years, most recently within LO 7.0.6.2. I’ve had no problems with it until today.

For whatever reason, LO crashed while I was doing some work, and getting it back up and running was difficult. I ended up trying to uninstall LO 7.0.6.2 and installing 7.2 and then uninstalling again and re-installing 7.0.6.2. Regardless of what I did, I could not install the pysort code and I could not uninstall it. When I tried to uninstall it, I got the following message:

(com.sun.star.lang.IllegalArgumentException) { { { Message = “There is no such extension deployed: org.openoffice.legacy.XPySort.oxt”, Context = (com.sun.star.uno.XInterface) @1ba0aeb37b0 } }, ArgumentPosition = (short) -1 }

When I tried to install it (again) (the file was saved on my computer), I got this message:

(com.sun.star.lang.IllegalArgumentException) { { { Message = “Cannot detect media-type: file:///C:/Users/g_e_f/AppData/Roaming/LibreOffice/4/user/uno_packages/cache/uno_packages/lu11360263uy6.tmp_/XPySort.oxt”, Context = (com.sun.star.uno.XInterface) @1ba13c23940 } }, ArgumentPosition = (short) -1 }

The extension is happily running in 7.0.6.2 on another Windows 10 computer in my house.

Any help in getting it running on this computer would be very much appreciated.

Thanks!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.