Subject | Re: spreadsheet ergonomics |
From | Snit |
Date | 04/07/2017 12:00 (04/07/2017 03:00) |
Message-ID | <D50CB16A.9D6D6%usenet@gallopinginsanity.com> |
Client | |
Newsgroups | comp.os.linux.advocacy |
Follows | Snit |
OK, last damned post on this where I respond to myself... but did away with the option+control+f and relaunched Numbers. Down to 16 seconds.Cleaned up the code but no faster. My current code:Ok, played some more and, frankly, am shocked at how close I am getting to what you have (which does not mean I have equaled it). What I have:owlSnit
How long does it take in Numbers to create a sheet with 52000 tables with locked formulas and labels (A-Z with 2000 tables in each column, each table with a label{#} at the top, and a sum the bottom, summing 10 rows)? And how long does it take to navigate to a specific such table?
2.57 seconds here with sc.
Ah, looking back to where you changed the topic and now re-viewing your video. Seems you mean ONE table with 26 columns and 2000 rows.
With yours you have many sums being made... I might fiddle and add that, but even having just ONE sum per column, and no locking of anything (Numbers does not even let me lock individual cells), and no added labels, it STILL took 4 seconds on my machine.
That sounds like it is pretty close to yours, BUT I am sure if I added the additional inclusion of formulas it would take it MUCH longer. Might fiddle with it at some point to see if I can do that... but even with my doing a subset of what you did (and given the formulas you have, a fairly small subset), Numbers took longer:
<https://youtu.be/bI-zgxFjijk>
* columns: 26 * rows: 2000 * sums: every 10 rows for each column
What I do NOT have * Locked formulas (no such feature in Numbers) * Quick navigation (ubsrd that Numbers does not have that... though I have scroll bars) * Labels on the top of each column. Might be able to do that... did not try
But my time: 22 second (compared to your 2.57). So I am still MUCH slower than you but, damn, I did not expect to get ANYWHERE near that.
<https://youtu.be/a3ifmy150Xw>
To be clear: you and sc are CLEARLY still blowing me and Numbers out of the water here... and I do not think it is merely my ignorance of scripting (though if I could get fill down to work as well as fill right *maybe* I could match your times?... just does not seem to want to work from scripting).
-------- set startTime to (current date)
set columnCount to 26 -- MIN=2, MAX=254 set rowCount to 2000 -- MIN=2, MAX=34463
tell application "Numbers" activate tell document 1 tell active sheet set thisTable to make new table with properties {row count:rowCount + 1, column count:columnCount + 1} remove first column of thisTable remove first row of thisTable tell thisTable repeat with r from 10 to rowCount by 10 tell cell r of column 1 set value to "=SUM(A" & r - 9 & ":A" & r - 1 & ")" end tell end repeat set selection range of thisTable to cell range tell application "System Events" to keystroke "f" using {option down, control down} end tell end tell end tell end tell
tell me to activate set endTime to (current date) set theDuration to (endTime - startTime) display dialog "Rows: " & rowCount & return & "Columns: " & columnCount & return & "Runtime: " & theDuration & " seconds." --------
The option+control+r is fill right (and I had to add that hotkey to Numbers so this would NOT work for other people unless they did that). No other way to do it with scripting that I can see... well, other than to actually have the menu item be referenced directly. I do not think that would really impact the speed and would make it work for others. Hmmm, should do that. :)