Reply to: [calc] generate all Combinations

[karolus]

But the algorithm GENCOMB uses is efficient, and my system can execute =GENCOMB(20;10) in less than 30 seconds.

On my system Calc crashes anyway after some long time to run this.

on the other side the simple test with pythons »itertools.combinations«

Code: Select all

list(combinations(range(20), 10))

runs less than a second
and inclusive dump into a Calc.DataArray, it needs ~4Seconds

So much for the alleged efficiency!

[FJCC]

Why the unfriendly reply? You could post your alternative, maybe even with complete code, without including a sentence starting with "So much for". That a python library routine is faster than Basic code is not surprising, is it?

[MrProgrammer]

You have created your post in the Code Snippets forum. Please review its rules. You have not supplied the actual source code listing, so your post is subject to deletion. A working example of a Python program to generate combinations could be of interest here, both as a way to create the data and as an illustration of how easy it is to call Python from Calc.

202310090840.gif (45.29 KiB) Viewed 10545 times

[karolus]

Hallo

Code: Select all

from itertools import combinations

def combis_from_range(*_):
    """
    takes a string like 
    »20;10«
    from current selected cell
    and produce from that
    a DataArray of all combinations 
    of lenght 10 from the given range of numbers
    """
    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.CurrentSelection
    sheet = sel.Spreadsheet
    a, b = map(int, sel.String.split(";"))
    out = tuple(combinations(range(a+1),b))
    cursor = sheet.createCursorByRange(sel)
    cursor.collapseToSize(len(out[0]), len(out))
    cursor.DataArray = out

That a python library routine is faster than Basic code is not surprising, is it?

For me its more suprising that @MrProgrammer announce the efficience of Basic … although he knows that python exists

[RoryOF]

Gentlemen, gentlemen! Politeness, please!

We all have our favored languages. Having started programming in 1964 with Fortran, I find great difficulty in getting my mind around Object Oriented languages.

[MrProgrammer]

For me its more suprising that @MrProgrammer announce the efficience of Basic … although he knows that python exists

You will find that I said the algorithm was efficient, not the use of Basic. The algorithm succeeded despite the nested loops in an interpreted language.

Reviewing your program, it looks as if it is a script which updates the spreadsheet directly, not a Calc function, and thus not really a substitute for the Calc function which I wrote in Basic. Still, your script could be useful for some people. Reading that I have done suggests to me that writing Calc functions in Python requires either an interface written in Basic, or creation of an Add-in, neither of which seems straightforward.

Having not used Python with OpenOffice, I wanted to execute your program. Unfortunately, this language does not seem to be an choice for MacOS users. Beginning with a new user profile, and a new spreadsheet document, I use Tools → Macros → Organize Macros → Python. OpenOffice shows it's trying to execute the feature, but I don't receive any response after ten minutes. I have to conclude that I can't execute Python macros on my system.

It appears that OpenOffice 4.1.7 cannot even open the window which would allow me to specify a Python script. That would be the first step toward executing a script, right? Given that Python doesn't seem to be a choice for me, I must stick with Basic. It looks as though you run Linux and LibreOffice, but being able to use Python there doesn't help me.

[FJCC]

Python scripts have to be manually stored in Whatever/user/scripts/python where user is the main folder of the user profile. There is no editing interface from OpenOffice to the python scripts. You can run python scripts through (excuse the Windows menus. Is it OpenOffice/Preferences on Mac?) Tools -> Macros -> Run or Tools -> Macros -> Organize Macros -> Python .

[karolus]

Python scripts … Whatever/user/scripts/python

Sorry for nitpicking, but the path need to be exactly …/Scripts/python

@MrProgrammer: yes we know, Calc searchs only in path …/Basic/Standard/?/ for simple UDFs, but it is not in scope of this topic to provide a full Addin.oxt nor to explain how to invoke python from Basic.

[JeJe]

The Python script is in a file and after any edit in your text editor you need to save that file again before you can run the code... that clunkiness compared with pressing F5 in the Basic IDE is a reason I don't use Python. If you just want to call some ready made functions that may be fine but making something complicated with inevitable bugs and trying to pin them down - must be tedious...

[karolus]

… and trying to pin them down - must be tedious...

No it isn't, its just a matter of using the right tool!
I use since ~2014 https://jupyter-notebook.readthedocs.io ... ebook.html for prototyping, exploring ectpp.
This offers functionalities that you never had and never will have in the Basic IDE.

[JeJe]

@karolus Thanks, from your reply in another thread that it looks like that'll just work with LibreOffice... but I'll probably have another look at Python sometime. The LO/OO argument is another one where people have to agree to disagree. The load speed is important to me which means its OO...

[MrProgrammer]

There is no editing interface from OpenOffice to the python scripts.

I wouldn't need an editing interface since I am quite familiar with edit tools on my Mac. Even for Basic I normally write the program with a MacOS editor and then just paste it into the Tools → Macros → Organize Macros → OpenOffice Basic dialog. I could also use the Import feature of the dialog .

You can run python scripts through … Tools -> Macros -> Run or Tools -> Macros -> Organize Macros -> Python .

I should be able to, but no. My choices in Tools → Macros are limited:
✓ Record Macro
x Run Macro
✓ Organize Macros → OpenOffice Basic
x Organize Macros → Python
x Organze Macros → BeanShell
x Organize Macros → Javascript
✓ Organize Dialogs

✓ means the feature works. ✗ means the feature is not available to me because OpenOffice never responds and I have to kill the application with the Force Quit dialog of MacOS. So my only choice for macros is OpenOffice Basic. Since the Tools → Macros → Run dialog isn't available, my only way to run macros is from the Organize Macros → OpenOffice Basic dialog or by creating shortcuts to them. This isn't a problem for me since I don't do much with macros, except some recorded macros that have shortcuts since they are used often.

I believe the Python problem is due to OpenOffice issue 123975 which says (comment 31):
Since MacOS 12.3, Apple don't provide Python 2.7 anymore. So, unable to run any Python scripts.

Sorry for nitpicking, but the path need to be exactly …/Scripts/python

That is the path I created, but I have determined that can't use Python.

… it is not in scope of this topic to provide a full Addin.oxt nor to explain how to invoke python from Basic.

Fair enough. And I can't use Python anyway so I can't help with creating examples of those methods.

… excuse the Windows menus. Is it OpenOffice/Preferences on Mac?

Macros is under Tools on MacOS, just as it is on Linux and Windoze. It's only the options dialog which was moved to the standard place for that on MacOS: OpenOffice → Preferences. I will emphasize that the dialog is accessed with OpenOffice → Preferences, not with OpenOffice → Preferences → Options. However, usually I use ⌘, (Command+Comma) which is the normal keyboard shortcut for the options/settings/preferences dialog in any MacOS application. This is standard, just like ⌘C, ⌘X, ⌘V, ⌘O, ⌘S, ⌘F, ⌘P, etc.

[Lupp]

When I still was a school boy (in Germany about 1959, not in Texas) I got told a joke:
A rancher wants to show off the size of his land: "If I drive my pickup to the other end of the ranch in the morning to replace some fence posts, I can't be back before evening."
The farmer next door responds, "Yeah, I used to have a car like that."

You may vary it the self-critical way using an answer like "Yes I also once fell asleep for hours when I just wanted to have a short rest after the second post fixed."

But you also can get serious about the matter and talk of tools an roads and everything - simply ignoring the bloat.

In this case we may come to assume that even Python programmers working on mathematical tasks vill throw a glance on Knuth's books.

[karolus]

…

Thank you for this constructive contribution