[Solved] Testing StarBasic Sort Algorithms with Calc cells
[Solved] Testing StarBasic Sort Algorithms with Calc cells
Try to test these routines: https://forum.openoffice.org/en/forum/v ... p?t=107599
What's wrong?
Thanks
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
Reason: Tagged ✓ [Solved] for solution by gpgrego -- MrProgrammer, forum moderator
OpenOffice 4.1.12 - Windows 10
Re: Testing StarBasic Sort Algorithms
you have to callData is a 2-dimensional array
Code: Select all
InsertionSort(oArray.Data(0))
- MrProgrammer
- Moderator
- Posts: 4907
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Testing StarBasic Sort Algorithms
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.StarBasic Sort Algorithms wrote: The array type can be String or one of the numeric types.
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.
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.
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).
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).
Re: Testing StarBasic Sort Algorithms
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.
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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Testing StarBasic Sort Algorithms
Lupp,
AOo complains that the downloaded file is damaged. Won't open
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)
DiGro
AOO 4.1.15 (Dutch) on Windows 11. Scanned with Ziggo Safe Online (F-Secure)
Re: Testing StarBasic Sort Algorithms
I just also downloaded the above attached file (was the fourth one to do so), treated it as described in my previous post
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
For me everything worked in AOO 4.1.7 and in recent LibreOffice as well.
ran the caller code, and it worked as expected.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.
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
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Testing StarBasic Sort Algorithms
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.
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
---
Lupp from München
Re: Testing StarBasic Sort Algorithms
You're absolutely right. Sorry for that.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 postran the caller code, and it worked as expected.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.
Should have read more carefully
____________
DiGro
AOO 4.1.15 (Dutch) on Windows 11. Scanned with Ziggo Safe Online (F-Secure)
DiGro
AOO 4.1.15 (Dutch) on Windows 11. Scanned with Ziggo Safe Online (F-Secure)
Re: Testing StarBasic Sort Algorithms
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