[Solved] Testing StarBasic Sort Algorithms with Calc cells

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
Mr.Dandy
Posts: 427
Joined: Tue Dec 11, 2012 4:22 pm

[Solved] Testing StarBasic Sort Algorithms with Calc cells

Post by Mr.Dandy »

Try to test these routines: https://forum.openoffice.org/en/forum/v ... p?t=107599

capture.png
capture.png (61.06 KiB) Viewed 3280 times
What's wrong?

Thanks
Last edited by MrProgrammer on Thu Sep 01, 2022 12:47 am, edited 1 time in total.
Reason: Tagged ✓ [Solved] for solution by gpgrego -- MrProgrammer, forum moderator
OpenOffice 4.1.12 - Windows 10
ms777
Volunteer
Posts: 177
Joined: Mon Oct 08, 2007 1:33 am

Re: Testing StarBasic Sort Algorithms

Post by ms777 »

you have to call

Code: Select all

InsertionSort(oArray.Data(0))
Data is a 2-dimensional array
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Testing StarBasic Sort Algorithms

Post by MrProgrammer »

Mr.Dandy wrote: Tue Aug 23, 2022 9:51 pm What's wrong?
StarBasic Sort Algorithms wrote: The array type can be String or one of the numeric types.
The expression oArray.Data would be an array of objects, correct? The algorithms are not written to be used with object arrays. Use StarBasic's TYPENAME() function to determine the array type. The algorithms I provided expect to compare array elements with the >, <, >=, or <= operators. Those operators only apply to strings and numbers. In this situation you can copy the numbers from your objects into a numeric array, sort that, then copy the sorted data back to your objects.

I can help if the InsertionSort algorithm doesn't sort numbers correctly, but I do very little with the UNO API and don't offer to help with retrieving the data from Calc cells nor with storing the sorted data back into Calc cells. But others can probably help with that if you get stuck. You will want to use an object inspector like MRI or Xray when working with the API.

Your picture forces people who want to test your situation to retype your data and program. That is a nuisance for us, since you have the actual files but didn't attach them. I will ignore any further pictures that you post.

ms777 wrote: Tue Aug 23, 2022 10:18 pm Data is a 2-dimensional array
I believe a breakpoint would show that Data is a 1-dimensional array of nine objects and Data(0) [the first object] is a 1-dimensional array of one number, the value in the first cell.

ms777 wrote: Tue Aug 23, 2022 10:18 pm InsertionSort(oArray.Data(0))
When sorting one number, nothing changes.


If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Testing StarBasic Sort Algorithms

Post by Lupp »

I came back more than once to sorting since 1965. Now and then the topic had some fascination for me, Summing the experience I would say that only non-comparing algorithms can be really efficient. In addition sorting should always be done by creating the needed permutation(s). Having the permutations, an ordered output is only needed if it is what actually is to be achived. In many cases "order-regarding access" to the data is sufficient to solve the current task. If "physical sorting" is required, the mentioned permutation (its inverse to be precise) can be used to output cycle by cycle, and only in the worst case (all elements belong to a cycle of 2) this requires N/2 (N: number of rows) factual transpositions: linear time-complexity insofar or better. I don't know anything about IT with texts in logographic writing, but for languages written in alphabets which I know (a bit) non-comparing sorting can be done. Then there is also a linear limit to the needed time depending on N. Sorting numbers this way is simple in a sense though preliminary steps may be needed if the data are to take from RAM.
Why do I tell all this?
Learning from my experience I judge sorting in Calc to actually use very clever and well optimized algorithms. It's time complexity is mostly better than linear in N. There are limitations of Calc, of course, but for somebody using StarBasic, and being supposed to write the data to a sheet in most cases, the usage of the API he has at hand anyway, should be considered.
The overhead?
The data mostly come from somewhere, we need to pass parameters, and to modify the algorithm probably if conditions change a bit... There always are use-case dependencies. Calc handles much of that already.
Why not accept the overhead for the creation of a sheet dedicated to the sorting - and disposed on mission completed?
I made an example doing the sort for 100 rows (4 columns each) of data by exactly one (very short) line of code: rg.sort(sd). All the rest is overhead:
Read data from a csv-like tab-separated-values file.
Create a hidden Spredsheet model.
Write the data into a hidden sheet.
Create the SortDescriptor as needed (based on a parameter string).
Execute the sort by the already mentioned line of code.
Take the result and close the helper document.
Write the data to a new "tsv" file.
All that overhead uses about 50 lines of simple code.
It runs in clearly less than a second - and for not exactly known reasons (cache?) sometimes even much faster if repeated.
Do you want to try the suggested way?
I attach a little archive with a fake extension. It's containing two files: An unsorted .tsv of data and an .odg holding the code.
Extract the archive to an empty folder, enter your actual folderpath into the respective line of the code.
Run with fun.
SortingCalc.zip.fake.odg
(28.73 KiB) Downloaded 111 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
DiGro
Posts: 173
Joined: Mon Oct 08, 2007 1:31 am
Location: Hoorn NH, The Netherlands

Re: Testing StarBasic Sort Algorithms

Post by DiGro »

Lupp,

AOo complains that the downloaded file is damaged. Won't open :-(
____________
DiGro

AOO 4.1.15 (Dutch) on Windows 11. Scanned with Ziggo Safe Online (F-Secure)
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Testing StarBasic Sort Algorithms

Post by Lupp »

I just also downloaded the above attached file (was the fourth one to do so), treated it as described in my previous post
Lupp wrote: attach a little archive with a fake extension. It's containing two files: An unsorted .tsv of data and an .odg holding the code.
Extract the archive to an empty folder, enter your actual folderpath into the respective line of the code.
ran the caller code, and it worked as expected.
Be sure to notice:
The downloaded file is actually an ordinary zip archive. The .odg is a fake applied because I'm not allowed (imo) to upload a .zip or a .7c file, am I? The file needs to be opened with a zip software, and the contained actual .odg file must be opened with AOO/LibO after the extraction of both contained files from the .fake.odg.
The "respective line of code you need to enter an actual path in is Basic editor's line 7 in

Code: Select all

Standard.caller.readRawDataSortWriteToTextFile
For me everything worked in AOO 4.1.7 and in recent LibreOffice as well.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Testing StarBasic Sort Algorithms

Post by Lupp »

Concerning the "overhead":
Any sorting can only be applied if the data to sort are represented in a suitable way, and then a reorganization may be needed to make use of the sorted data and/or to output them as required The mentioned steps will also mostly require the passing and interpretation of parameters (which I did not actuallöy include with my example).
There always is the respective overhead.
The additional overhead needed for the proceeding I suggested is:
Creation, filling in, taking the result (a .DataArray) from, and closing of the hidden helper document (a few lines of code, some ms of time, and some MiB of RAM).
The (valuable?) saving is that no user code for any sorting algorithm is needed - and no considerations concerning the choice.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
DiGro
Posts: 173
Joined: Mon Oct 08, 2007 1:31 am
Location: Hoorn NH, The Netherlands

Re: Testing StarBasic Sort Algorithms

Post by DiGro »

Lupp wrote: Wed Aug 24, 2022 12:59 pm I just also downloaded the above attached file (was the fourth one to do so), treated it as described in my previous post
Lupp wrote: attach a little archive with a fake extension. It's containing two files: An unsorted .tsv of data and an .odg holding the code.
Extract the archive to an empty folder, enter your actual folderpath into the respective line of the code.
ran the caller code, and it worked as expected.
You're absolutely right. Sorry for that. :oops:
Should have read more carefully :crazy:
____________
DiGro

AOO 4.1.15 (Dutch) on Windows 11. Scanned with Ziggo Safe Online (F-Secure)
gpgrego
Posts: 39
Joined: Fri Jan 31, 2020 4:04 pm

Re: Testing StarBasic Sort Algorithms

Post by gpgrego »

Code: Select all

Sub Main
sData = "A1:A9"

oDoc = ThisComponent
oSheet = oDoc.CurrentController.ActiveSheet
oArray = oSheet.getCellRangeByName(sData)
InsertionSort(oArray.getDataArray()) 

End Sub

Sub InsertionSort( A )
Dim LB As Long, I As Long, J As Long, Temp As Variant
LB = LBOUND(A)
For I = LB+1 To UBOUND(A)
   Temp = A(I)(0) 
   For J = I-1 To LB Step -1
      A(J+1)(0) = A(J)(0)
      If Temp >= A(J)(0) Then Exit For
   Next J
   A(J+1)(0) = Temp
Next I

' view result
For I = 0 To UBOUND(A)
   ThisComponent.CurrentController.ActiveSheet.getCellByPosition(1,I).value = A(I)(0)
NEXT I  
End Sub       

openoffice 4.1.6 - Windows 10
Post Reply