Python modules for calc

English
,
#21

How is it this does not work? Function ParseLocation(strSourceText As String) As Variant() according to this, it should.

#22

How in the world is this Argument is not optional?

Private Function ParseLocationText(strSourceText As String)
	' Have already stepped through this and 'it works'
End Function

Function ParseLocation(strSourceText As String)

	Dim Values(5) As Variant

	Values = ParseLocationText(strSourceText)

	ParseLocation = Values

End Function

I have tried with and without declaring Values to no avail.

#23

Okay finally got at least some result, a step forward. Returns a Double() or Variant(). But that is only setting the first value in a single Cell. I want to populate a target Cell Range, preferably for the row from which the source test is drawn.

I did start the post out with the intention of leaning into the python side. Possibly looking at the Pythonista to help with that, Iā€™m not sure.

Just from a BASIC perspective, how do I convey a cell range to the Function? Or a Sub, or whatever. And/or return with the same.

#24

This is a step forward, and the Cells appear to be addressed properly by row and column, but the result is still displeasing, the values are not landing properly in the sheet.

Function ParseLocation(ByVal strSourceText As String, oDestRange As Range)

	Dim Values() As Variant

	Values = ParseLocationText(strSourceText)

	Dim oSheet As Object
	Dim oCell As Object

	oSheet = ThisWorkbook.ActiveSheet

	Dim I, Row, Column As Integer

	For I = LBound(Values) To UBound(Values)
		Row = oDestRange.Row
		Column = oDestRange.Column + I
		oCell = oSheet.Cells(Row, Column)
		oCell.Value = Values(I)
	Next I

	ParseLocation = ArraySize(Values)

End Function

Note, I am also, FWIW, which appears to have some impact on the Sheet and Cell classes involved:

Option Explicit
Option VBASupport 1

Only the first and either the last value or returned size are populated.

survey x y z pitch yaw roll
{X=1 Y=2 Z=|P=4 Y=5 R=6} 1 6 6

Have verified the parsing is correct should be six values in all.

https://docs.libreoffice.org/sc/html/classScVbaWorksheet.html
https://docs.libreoffice.org/sc/html/classScVbaRange.html

#25

This appears to work, but man getting there, one climb after the next.

Dim Values() As Variant
Dim Size As Integer

Values = ParseLocationText(strSourceText)
Size = ArraySize(Values)

Dim oSheet As Object
Dim oCell As Object
Dim oStartCell As Object
Dim oEndCell As Object
Dim oRange As Object

oSheet = ThisWorkbook.ActiveSheet

Dim Row As Integer
Dim StartColumn As Integer
Dim EndColumn As Integer
Row = oDestRange.Row
StartColumn = oDestRange.Column
EndColumn = StartColumn + Size - 1

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

oRange = oSheet.Range(oStartCell, oEndCell)

oRange.Value = Values

Again, somewhat if not entirely dependent upon: Option VBASupport 1.

Here instead of iterating I work back to the ranges, cells, and set the values to the array result. And voila, the range lands in the cells as expected.

#26

And with that, still there may be some room for Pythonista analytics forward from this breakthrough.

#27

The StarBasic+API of the LibreOffice is not VBA.
(VBA: Visual Basic for Applications. The VBA is a Microsoft product.)
(API: Application Programming Interface)
.
Never will be 100% compatibility between the VBA and the StarBasic+API.
Try to avoid to use the ā€œOption VBASupport 1ā€ in the LibreOffice.
.
Generic programming rule:
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. The VBA breaks this rule.

#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.

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