[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Orgmode] new spreadsheet features: field coordinates, multi line TBLFM
From: |
Michael Brand |
Subject: |
[Orgmode] new spreadsheet features: field coordinates, multi line TBLFM with comments |
Date: |
Sat, 13 Mar 2010 16:27:14 +0100 |
User-agent: |
Thunderbird 2.0.0.23 (Windows/20090812) |
Hi Carsten, hi all
1) field coordinates:
As a proposal I implemented the following to cover one of my spreadsheet use
cases: Why not let address@hidden' and `$#' in Calc formulas (works also for Lisp
formulas) be substituted to the row or column number of the formula result
field?. The traditional Lisp formula equivalents are org-table-current-dline
and org-table-current-column. I believe it does not break the TBLFM syntax
for any other use.
My use case is an overview with the average annual relative changes of a time
series. Here is an example with averages for a history of 1 year up to 4 years:
| year | quote | 1 a | 2 a | 3 a | 4 a |
|------+-------+-------+-------+-------+-------|
| 2005 | 10 | | | | |
| 2006 | 12 | 0.200 | | | |
| 2007 | 14 | 0.167 | 0.183 | | |
| 2008 | 16 | 0.143 | 0.155 | 0.170 | |
| 2009 | 18 | 0.125 | 0.134 | 0.145 | 0.158 |
#+TBLFM: $3 = if(@# + 1 > $#, ($2 / subscr(@address@hidden, @# + 1 - $#)) ^ (1 /
($# - 2)) - 1, string("")); f3 :: $4 = if(@# + 1 > $#, ($2 /
subscr(@address@hidden, @# + 1 - $#)) ^ (1 / ($# - 2)) - 1, string("")); f3 ::
$5 = if(@# + 1 > $#, ($2 / subscr(@address@hidden, @# + 1 - $#)) ^ (1 / ($# -
2)) - 1, string("")); f3 :: $6 = if(@# + 1 > $#, ($2 / subscr(@address@hidden,
@# + 1 - $#)) ^ (1 / ($# - 2)) - 1, string("")); f3
The patch (the files with `---' were taken from org-version 6.34c):
======================================================================
--- a/doc/org.texi
+++ b/doc/org.texi
@@ -2098,6 +2098,21 @@ table in that entry. REF is an absolute
described above for example @code{@@3$3} or @code{$somename}, valid in the
referenced table.
address@hidden Field coordinates
address@hidden field coordinates
address@hidden coordinates, of field
address@hidden row, of field coordinates
address@hidden column, of field coordinates
+
+For Calc formulas and Lisp formulas @code{@@#} and @code{$#} can be used to
+get the row or column number of the field where the formula result goes.
+The traditional Lisp formula equivalents are @code{org-table-current-dline}
+and @code{org-table-current-column}. Example:
+
address@hidden
+if(@@# % 2, $#, string("")) @r{column number on odd lines only}
address@hidden example
+
@node Formula syntax for Calc, Formula syntax for Lisp, References, The
spreadsheet
@subsection Formula syntax for Calc
@cindex formula syntax, Calc
--- a/lisp/org-table.el
+++ b/lisp/org-table.el
@@ -2243,6 +2243,21 @@ not overwrite the stored one."
(setq form (copy-sequence formula)
lispp (and (> (length form) 2)(equal (substring form 0 2) "'(")))
(if (and lispp literal) (setq lispp 'literal))
+
+ ;; Insert row number of formula result field
+ (while (string-match "address@hidden" form)
+ (setq form
+ (replace-match
+ (save-match-data
+ (format "%d" (org-table-current-dline)))
+ t t form)))
+ ;; Insert column number of formula result field
+ (while (string-match "\\$#" form)
+ (setq form
+ (replace-match
+ (save-match-data
+ (format "%d" (org-table-current-column)))
+ t t form)))
;; Check for old vertical references
(setq form (org-table-rewrite-old-row-references form))
;; Insert remote references
======================================================================
2) self-edited multi line TBLFM with comments:
For me it would be great to have the now missing option to hack all the
formulas myself just directly, but rather into a multi line TBLFM with
comments than into the one line #+TBLFM:. For the use case above this would
look like:
[...]
| 2009 | 18 | 0.125 | 0.134 | 0.145 | 0.158 |
#+BEGIN_TBLFM
$3 = if(@# + 1 > $#, ($2 / subscr(@address@hidden, @# + 1 - $#)) ^
(1 / ($# - 2)) - 1, string("")); f3 :: # rel. change for 1 year
$4 = if(@# + 1 > $#, ($2 / subscr(@address@hidden, @# + 1 - $#)) ^
(1 / ($# - 2)) - 1, string("")); f3 :: # average for 2 years
$5 = if(@# + 1 > $#, ($2 / subscr(@address@hidden, @# + 1 - $#)) ^
(1 / ($# - 2)) - 1, string("")); f3 :: # average for 3 years
$6 = if(@# + 1 > $#, ($2 / subscr(@address@hidden, @# + 1 - $#)) ^
(1 / ($# - 2)) - 1, string("")); f3 # average for 4 years
#+END_TBLFM
I suggest to leave the one line #+TBLFM without the comment possibility to
avoid issues with the very nice write back to #+TBLFM when editing with e. g.
`C-c =' or moving rows and columns. In favor of a complexity reduction and to
avoid confusion I suggest to leave the #+BEGIN_TBLFM option without write
back and to unconditionally reject a trial to do it. Similar to the nice
reject when trying to edit column 1 with `C-c =' in the following example,
where #+TBLFM has been upset by self-editing it:
| 0 |
#+TBLFM: $1 = 0 :: $1 = 0
This way the implementation for the multi line TBLFM with comments could on
evaluation simply strip the comments from all the lines between #+BEGIN_TBLFM
and #+END_TBLFM, join the rest of these lines into one line and pass it to
where the #+TBLFM: content is passed today. Of course there would still be
more to do to make the whole work.
Surprise: #+BEGIN_TBLFM already folds by TAB without implementing anything yet.
<side_note on comments>
The TBLFM comments should not be parsed as `#' but rather ` #' in order to
not break
a) the new field coordinates address@hidden' and `$#' if done as in my proposal
above
b) the Calc input radix
Example:
| (1, 1) | (1, 2) | 256 |
#+BEGIN_TBLFM
$1 = (@#, $#) :: $2 = (@#, $#) :: # a) numbers of row and column
$3 = 16#100 # b) Calc input radix with hash char
#+END_TBLFM
I'm not sure if adding a regexp for multi line TBLFM comments to some face
for highlighting would make too much trouble with breaking other stuff
containing ` #' outside multi line TBLFM.
</side_note>
How easy would that be to implement?
Michael
- [Orgmode] new spreadsheet features: field coordinates, multi line TBLFM with comments,
Michael Brand <=