Python modules for calc

English
,
#28

The StarBasic+API of the LibreOffice is not VBA.
…
Try to avoid to use the “Option VBASupport 1” in the LibreOffice.

Then I have to ask the question: why entertain it as an choice, a “feature”, and it isn’t fully supported? i.e. declaring Function As Tytpe.

Notwithstanding VBA breaking the Function result to Cell or CellRange, aspect.

Still, good to know.

What are the equivalent docs for the Worksheet, Cell, CellRange and so forth? Assuming possibly also needing some flavor of the ScriptForge services.

Best, and thanks so much.

#29

Just think a bit yourself…
.
Shall they remove everything, wich does not function “fully”?

  • reading xls. Format was never documented as standard.
  • reading xlsx. There is some standard, but MS usually stores in an “extended” format
  • reading csv, as there is no standard.
  • macro-recorder will not catch everything, so lets remove it
  • as suggested by you: forget about VBA-macros, if not all MS-macros are running
  • pdf/X not supported, so remove pdf-export
    …

Or we can leave all this in, so people can test, if it is useful for them.
For best compatibility with MS-Office I recommend MS-Office ( Even this fails sometimes … )

#30

The “Functions” has one or more RETURN values. They are named “Return” values because they will return to the place (Cell or Cellrange) where the Function was called from. And not to other places.

Question, when I return the Variant(), only the first value is placed in the given CellRange. How do I actually have ALL the values land in the range?

After dropping VBASupport, then I am also back to, parameters as values, not the spreadsheet objects, ranges and so forth.

How do I ensure passing the cell address, range addresses, etc?

#31

In the VBASupport approach, this was “working”:

Dim Row As Integer
Dim StartColumn As Integer
Dim EndColumn As Integer

Row = oDestRange.Row
StartColumn = oDestRange.Column
EndColumn = StartColumn + Size - 1

Dim oSheet As Object
oSheet = ThisWorkbook.ActiveSheet

Dim oStartCell As Object
Dim oEndCell As Object
Dim oRange As Object

oStartCell = oSheet.Cells(Row, StartColumn)
oEndCell = oSheet.Cells(Row, EndColumn)

oRange = oSheet.Range(oStartCell, oEndCell)

oRange.Value = Values

For that matter, I might even have be able to just set the Values on the argument, oDestRange As Range.

What is the non VBASupport equivalent of that? Or at least some doc references, please, that would be helpful.

#32

What is the non VBASupport equivalent of that? Or at least some doc references, please, that would be helpful.

This is much of my consternation. The docs are either INACURATE AF, or need updating.

One key area, for instance, are INDEXING. Providing SHEET() for instance from the worksheet perspective, CORRECTLY yields 1 in this case. However, ThisComponent.Sheets.getByIndex(1) throws IndexOutOfRangeException.

Yes or no, INDEXING SHOULD BE ONE BASED, NOT ZERO!!!

It should really not be this difficult to simply write some macros, Sub, Function, whatever. Yes, it is effort to write any sort of code, but with this every single word, line of code, is earned to the Nth degree. Really is exhausting to work with for no good reason, when docs are unreliable, runtime behavior is inconsistent, or outright incorrect, according to known BASIC principles, let alone “VBA” or Calc appliances.

Another take away, bearing in mind LO is doing BASIC things on a Java based infrastructure, which is, last time I checked, zero based indexing. But still… When SHEET() is correctly reporting 1 for the first sheet… That is inconsistent with the native LO BASIC infrastructure.

To be fair, I know the LO team are not the purveyors of the DF wiki site, but still; they must be basing the examples on some ostensibly well known source of truth.

https://wiki.documentfoundation.org/Macros/Basic/Calc/Sheets

#33

Have verified, I have an object in oCellRange from the appropriate rows and columns. Also arrValues are correctly there.

oCellRange = oSheet.getCellRangeByPosition(iStartCol, iRow, iStartCol + iSize - 1, iRow)
oCellRange.setDataArray(arrValues)

From the sheet formula =PARSELOCATION(B3,C3:H3, SHEET(), COLUMN(C3), ROW(C3)).

Error: Object variable not set. This is a lie. IT IS SET. I verified it.

Again, I’m just working from the docs.

https://docs.libreoffice.org/sc/html/classScTableSheetObj.html
https://docs.libreoffice.org/sc/html/classScCellRangeObj.html

So if there is some object or other not being set, it’s not my code not setting it.

#34

surveys.ods (20.7 KB)

Posted the ODS after all… Where it stands now, Object variable not set.

I’ve checked in the watch, it is set, AFAIK.

#35

I suppose not surprisingly, indexing is also 0-adjusted not 1-based getting the cell range, as well.

image

#36

If I have to iterate the cells themselves relative to the range, assuming that’s according to getCellByPosition(...) with 0-indexing relative to the range instance, yes?

#37

Without inspecting code, best guess, the internal implementation is probably broken, guessing around perhaps the same assumptions, whether 0-based or 1-based indexing, and in which contexts that should be the case. C C++ obviously is 0 based, as is Java. However spreadsheets, BASIC, are often 1-based by default, and should be respected as such.

Was forced to resort to the following workaround which seems to have worked.

oCellRange = oSheet.getCellRangeByPosition(iStartCol - 1, iRow - 1, iStartCol + iSize - 2, iRow - 1)

' Broken, fails internally: `Object variable not set`
oCellRange.setDataArray(arrValues)

' The workaround is to iterate the Cells from the range itself
Dim I As Integer
Dim oCell As Object

For I = 0 to iSize - 1
	' And which, yes, as semi-intuitively expected, both relatively indexed, and 0-based
	oCell = oCellRange.getCellByPosition(I, 0)
	oCell.setValue(arrValues(I))
Next I

Cell positions are indexed relative to the range, and are 0-based, which I think is inconsistent with the spreadsheet, never mind BASIC, expectation, that indexing should be 1-based. Always has been since at least Microsoft Excel days and before that. But please do not mistake that as any defense of Microsoft, I am not (defending). I’m just saying broader industry wide, spreadsheets are generally 1-based indexed by their intrinsic nature, whatever the programming API infrastructure is around it.

Approaching conclusion of this issue, I think. FWIW, my two cents.

#38

Should also mention, I am not the first to have encountered this issue around setDataArray, etc. Has been reported in at least this forum since at least 2021.

#39

And I posted my modified sample file again:
Parse_Zizi64_b.ods (19.2 KB)

The function written in Basic only, no Python.
The Function was called conventionally, the function will modify ONLY the cell range where it was called from.
The function has an Array output.

The function ParseLocation is called 10 times in the rows (and once the function CreateHeader)

#40

And I will reiterate @Zizi64, first much appreciate as a starting point; second, insightful as a good starting point.

The Function will modify ONLY the cell range where it was called from.
The function has an Array output.

Only the first element in the array lands in the source cell where the function was called, i.e. DOES NOT WORK!

Anyway, I adapted from there and manually place the array values in the desired source range. Values should land in a specified target row and member columns; I adapted toward that goal.

Certainly if there is a better more flexible way of doing it, through BASIC, employing py adapted solutions, I’m open to hearing it.

Notwithstanding the indexing issue, and apparent bugs in the BASIC object class API implementations, and so on.

Which result, for instance, is not displeasing. Will have to see how Pythonista is at working with those ranges to discover key analytics and so forth.

image

And what is required to connect the dots, =PARSELOCATION(B3, SHEET(), COLUMN(C3), ROW(C3)). I tried delivering the array range starting at the cell, but the only value that landed there was the position X value, nothing else. Rather I simply return the count of values parsed, more or less, for now.

#41

Can you upload your sample file again?

Do you know, how you can call an ARRAY FUNCTION from a CELL RANGE in a spreadsheet document?
.
You must select MORE THAN ONE CELLS for the “target range”, then you must edit the formula, and finally you must hit Shift-Ctrl-Enter.

#42

Here is my new sample file with a Subroutine. A StarBasic Subroutine can modify all of cells in the spreadsheet.
There is a Named range in my sample file. The Sub will get the strings from the first column of the range, and it will put the parsed values into the other columns. Tha Sub is assigned to an event of the Button. It will launch the Sub, because you can not call a Subroutine from a cell.
My Sub uses the getDataArray(), setDataArray() for the range, but you can get/set the cell contents individually too - by API functions.
Parse_Sub_Zizi64.ods (17.2 KB)

#43

Do you know, how you can call an ARRAY FUNCTION from a CELL RANGE in a spreadsheet document?

Did not know that… Will look into it. Thank you for pointing it out.

#44

It works brilliantly, thanks for the [x] Array tip. In a cell start with the formula, enter the formula dialog, check Array. The result is just what I am wanting there.

Can remove the sheet, column, row arguments now, as well.

image

Minor edit, with this approach, it is a bit cumbersome introducing an array formula to the cell range, or working with it after it has been established. Fortunately, it is fairly easy to do the routine spreadsheet fill operation, in this case down, or in whichever direction you want to fill, down or right.

#45

Of course you can create an “array function” with an one dimensional array input range and with a two dimensional array output range. In this case you must call the custom function once for the full range.

1 Like
#46
#47

LibrePythonista uses the custom Python function lp() to interface between Calc and Python. The lp() function accepts Calc objects like ranges, named ranges, and data ranges. You can also directly type references into a Python cell with the lp() function.

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