Google| Subject | Re: spreadsheet ergonomics |
| From | owl |
| Date | 04/06/2017 04:44 (04/06/2017 02:44) |
| Message-ID | <ka9aie.ay44futrr@rooftop.invalid> |
| Client | |
| Newsgroups | comp.os.linux.advocacy |
| Follows | DFS |
| Followups | DFS (11h & 7m) > owl |
DFS <nospam@dfs.com>wrote:
DFSStill working with this, and there's some extra vars in there for future use. "max" is set a 2000 and holds the number of tables you want in each column, and it runs from a-zz, for a total 702*2000 such tables (0-1403999). So just change max to whatever you want. A value max=1 gives 702 tables, one each in columns a-zz. I may change the naming scheme. Right now it's just sequential.
On 4/5/2017 5:17 PM, owl wrote:DFSowlowlDFS
https://vid.me/pG0C
zero to 52000 tables in 2.57 seconds. Each with locked labels and formulas. Instantaneous jump to any table.
hot damn!
1.4 million tables in a just over a minute. This is in bash.
code?
#!/bin/bash echo "set numeric extfun craction=1 color colorerr" echo "format A:ZZ 25 2 0" lets=( a b c d e f g h i j k l m n o p q r s t u v w x y z ) cols=($(for (( i=0; i<26; i++ ));do echo -n "${lets[${i}%26]} "; done)) cols+=( $(for (( i=0; i<26; i++ ));do for (( j=0; j<26; j++ ));do echo -n "${lets[${i}%26]}${lets[${j}%26]} " done done) ) k=0 l=0 b=1 e=$((b+4)) s=$((e+9)) n=$((s+1)) j=0 x=0 f=0 max=2000 for (( i=0;i<${max};i++ ));do for((x=0,f=k;x<${#cols[@]};x++,f+=${max}));do echo "rightstring ${cols[x]}${l}=\"table${f}\"" echo "lock ${cols[x]}${l}" echo "let ${cols[x]}${s}=@sum(${cols[x]}${b}:${cols[x]}${e})" echo "lock ${cols[x]}${s}" done ((k++)) ((l+=n)) ((b+=n)) ((e+=n)) ((s+=n)) done anon@lowtide:~/code/sc/mktables$
run it with ./mktables >somefile.sc
Then open the file in sc. go to a specific table with:
g "table35014$"
It's treated as a regex, and the $ keeps it from matching with table340145 or whatever.
The whole file is one "table" to sc. My tables here are just cell labels above a column add section, so it's not really a named range. Just a cell label. (sc has named ranges, though). You can jump to a cell label with "g <regex>". Labels and formulas are locked.owlDFS
I might try it in C and see if I can get it down to a few seconds per million.
Looking at your vid, a 'table' appears to be an 11-row by 1-column name-defined range in an sc sheet.
Max in Excel 2003 would be approx 1.525M 'tables': (65536 rows x 256 columns) / 11.heh, purty is in the eye of the beholder. Your mission, should you choose to accept it, is to get Relf to switch to python.
Public Sub createtables()
Const MAXROWS = 65535 Const MAXCOLS = 256 Dim startTime As Date, endTime As Date Dim row As Long, col As Integer Dim tblcnt As Long, tablesize As Integer row = 1 col = 1 tblCount = 1 tblSize = 11
startTime = Timer For col = 1 To MAXCOLS For row = 1 To (MAXROWS - tblSize) Step tblSize Cells(row, col).Select ActiveCell.FormulaR1C1 = "Table" & tblCount ActiveWorkbook.Names.Add Name:="Table" & tblCount, _ RefersToR1C1:=Chr(col + 64) & row & ":" & Chr(col + 64) & (row + tblSize) Cells(row + tblSize - 1, col).Select ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)" tblCount = tblCount + 1 If Timer - startTime >15 Then Debug.Print tblCount & " tables in 15 seconds" Exit Sub End If Next row Next col endTime = Timer Debug.Print tblCount & " tables in " & Format(endTime - startTime, "0.0") & " seconds"
End Sub
Results: 5169 tables in 15 seconds
http://imgur.com/a/2CugK
(note: it's not perfect - the range definitions are set for the correct range of cells, but they're surrounded by quotes, like ="F12:F23" so you can't use goto TableN. My bug and I don't care to fix it. The SUM formula is correct, though)owlDFS
Or I could try it in Python to see how slow I could make it.
The way to think about python speed is this: the code is purty.
Good article here - not about python http://blog.fourthbit.com/2014/03/01/the-best-programming-language-or-how-to-stop-worrying-and-love-the-codeDFSYou could be a heavyweight contender in the sc event in the Owlympics.owl
I've got enough on my plate already, what with managing Bohemian Grove and suing pornographic comic book publishers...
I understand. I'm busy preparing my Ted talk ("Usenet addiction: healthier than cocaine?") for later this month, and kidnapping neighbors' dogs for ransom.
03/31/2017-
-
-
