From: Michael Brand <michael.ch.brand@gmail.com>
To: Bastien <bzg@altern.org>
Cc: Bob Newell <bobnewell@bobnewell.net>,
Thorsten Jolitz <tjolitz@googlemail.com>,
Nick Dokos <nicholas.dokos@hp.com>,
Org Mode <emacs-orgmode@gnu.org>
Subject: Re: Distinguish between blank and zero in org-mode spreadsheet
Date: Fri, 28 Dec 2012 15:10:22 +0100 [thread overview]
Message-ID: <CALn3zois8R3YL3B6dA8n25kj_HvFnqrrRxGRRaTTrrK4C8=V5w@mail.gmail.com> (raw)
In-Reply-To: <87ehirekir.fsf@bzg.ath.cx>
[-- Attachment #1: Type: text/plain, Size: 2086 bytes --]
Hi all
On Sat, Dec 15, 2012 at 10:23 AM, Bastien <bzg@altern.org> wrote:
> Michael Brand <michael.ch.brand@gmail.com> writes:
>> Ok, so I thought to try myself this time. Now it looks as I am on a
>> good way with a solution. With quite some ERT for this and some more
>> of basic formulas.
>
> Thanks for working on this, if there is a clean solution, I'm willing
> to add it for Org 8.0.
Patch 6 of the attached patches makes it possible to write
spreadsheet Calc formulas that check for empty fields: To sum the
first two columns unless one or both empty:
$3 = if("$1" = "nan" || "$2" = "nan", string(""), $1 + $2); E
The other patches:
- patch 1: testing/README: add howto run ERT partially
- patch 2: Add ERT for table alignment within Org buffer
The new function org-test-table-target-expect is to simplify writing
spreadsheet ERT by providing just a target table to apply the formula
to, the expected result table and the table formula with optionally
additional variants. The variants typically are to check the same
result with a Lisp formula and a Calc formula. test-org-table.el is
also a howto example collection as a user documentation.
- patch 3: Extend org-table-number-regexp
- patch 4: Add ERTs for basic spreadsheet functionality
My plan is to add spreadsheet examples only to Org ERT
(test-org-table.el) from now on and someday to move those I already
made on Worg.
- patch 5: org-table.el: Fix range len bugs and inconsistencies
The range len bugs may lead to wrong calculations for range references
with empty fields when the range len is relevant. Affects typically
Calc vmean on simple range and without format specifier EN. Also
Lisp with e. g. `length' on simple range or with L.
Note: Org ERT passes after every single patch to document the behavior
of each patch.
After the review is complete please make sure that “make check” passes
before applying the patches and also before git push. I hope I got it
right with the require to resolve the dependencies.
Michael
[-- Attachment #2: 0001-testing-README-add-howto-run-ERT-partially.patch.txt --]
[-- Type: text/plain, Size: 2450 bytes --]
From 2e2479a894076108a34dbaacc46658e3eb5ec16c Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Fri, 28 Dec 2012 14:58:24 +0100
Subject: [PATCH 1/6] testing/README: add howto run ERT partially
* testing/README (Interactive testing from within Emacs): Add a
description how to run a single ERT or all tests of a single test
file.
---
testing/README | 33 ++++++++++++++++++++++++++++++++-
1 files changed, 32 insertions(+), 1 deletions(-)
diff --git a/testing/README b/testing/README
index a81efa0..8ce7a19 100644
--- a/testing/README
+++ b/testing/README
@@ -43,11 +43,42 @@ load and run the test suite with the following commands.
(require 'org-test)
#+END_SRC
-2) Then run the test suite.
+2) Then run the test suite,
#+BEGIN_SRC emacs-lisp
(org-test-run-all-tests)
#+END_SRC
+ or when a test fails run it interactively and investigate the
+ problem in the ERT results buffer.
+
+ How to run one test:
+ Use this as a demo example of a failing test
+ #+BEGIN_SRC emacs-lisp
+ (ert-deftest test-org/org-link-escape-ascii-character-demo-of-fail ()
+ (should (string= "%5B" ;; expected is right
+ (org-link-escape "[")))
+ (should (string= "%5C" ;; expected is wrong, "%5D" would be right
+ (org-link-escape "]"))))
+ #+END_SRC
+ or evaluate the ert-deftest form of the test you want to run. Then
+ "M-x ert RET test-org/org-link-escape-ascii-character-demo-of-fail RET"
+ When not visible yet switch to the ERT results buffer named
+ "\*ert\*". When a test failed the ERT results buffer shows the
+ details of the first "should" that failed. See
+ (info "(ert)Running Tests Interactively") on how to re-run, start
+ the debugger etc.
+
+ How to run all tests of a single test file:
+ "M-x ert-delete-all-tests RET", confirm. Open the file
+ ./lisp/test-*.el, "M-x eval-buffer RET", "M-x ert RET t RET"
+
+ Consider to set pp-escape-newlines nil before running the test when
+ looking at "should" in the ERT results buffer. Especially when
+ using "l" to look at passed test results and possibly missing an
+ appropriate setting of pp-escape-newlines made only temporarily for
+ the running time of the test as e. g. tests using
+ org-test-table-target-expect-tblfm do.
+
* Troubleshooting
- If the value of the =org-babel-no-eval-on-ctrl-c-ctrl-c= is non-nil
--
1.7.1
[-- Attachment #3: 0002-Add-ERT-for-table-alignment-within-Org-buffer.patch.txt --]
[-- Type: text/plain, Size: 4572 bytes --]
From 14c6cc72fd2ee408046d4830c436ae0becfe8144 Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Fri, 28 Dec 2012 14:59:17 +0100
Subject: [PATCH 2/6] Add ERT for table alignment within Org buffer
* testing/lisp/test-org-table.el (test-org-table/align): New
ert-deftest for table alignment within Org buffer.
* testing/org-test.el (org-test-table-target-expect): New defun.
The new function org-test-table-target-expect is to simplify writing
spreadsheet ERT by providing just a target table to apply the formula
to, the expected result table and the table formula with optionally
additional variants. The variants typically are to check the same
result with a Lisp formula and a Calc formula. test-org-table.el is
also a howto example collection as a user documentation.
---
testing/lisp/test-org-table.el | 14 ++++++++++
testing/org-test.el | 52 ++++++++++++++++++++++++++++++++++++++++
2 files changed, 66 insertions(+), 0 deletions(-)
diff --git a/testing/lisp/test-org-table.el b/testing/lisp/test-org-table.el
index 8b59114..4c899a8 100644
--- a/testing/lisp/test-org-table.el
+++ b/testing/lisp/test-org-table.el
@@ -23,6 +23,20 @@
;; Template test file for Org-mode tests
;;; Code:
+
+(ert-deftest test-org-table/align ()
+ "Align columns within Org buffer, depends on `org-table-number-regexp'."
+ (org-test-table-target-expect "
+| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+| ab | 12 | 12.2 | 2.4e-08 | 2x10^12 | 4.034+-0.02 | 2.7(10) | >3.5 |
+| ab | ab | ab | ab | ab | ab | ab | ab |
+")
+ (org-test-table-target-expect "
+| 0 | 0 | 0 | 0 | 0 | 0 |
+| <-0x0ab.cf | >-36#0vw.yz | nan | uinf | -inf | inf |
+| ab | ab | ab | ab | ab | ab |
+"))
+
(ert-deftest test-org-table/org-table-convert-refs-to-an/1 ()
"Simple reference @1$1."
(should
diff --git a/testing/org-test.el b/testing/org-test.el
index 8270be0..56bcb10 100644
--- a/testing/org-test.el
+++ b/testing/org-test.el
@@ -220,6 +220,58 @@ otherwise place the point at the beginning of the inserted text."
,results)))
(def-edebug-spec org-test-with-temp-text-in-file (form body))
+(defun org-test-table-target-expect (target &optional expect laps
+&rest tblfm)
+ "For all TBLFM: Apply the formula to TARGET, compare EXPECT with result.
+Either LAPS and TBLFM are nil and the table will only be aligned
+or LAPS is the count of recalculations that should be made on
+each TBLFM. To save ERT run time keep LAPS as low as possible to
+get the table stable. Anyhow, if LAPS is 'iterate then iterate,
+but this will run one recalculation longer. When EXPECT is nil
+it will be set to TARGET.
+
+If running a test interactively in ERT is not enough and you need
+to examine the target table with e. g. the Org formula debugger
+or an Emacs Lisp debugger (e. g. with point in a data field and
+calling the instrumented `org-table-eval-formula') then copy and
+paste the table with formula from the ERT results buffer or
+temporarily substitute the `org-test-with-temp-text' of this
+function with `org-test-with-temp-text-in-file'.
+
+Consider setting `pp-escape-newlines' to nil manually."
+ (require 'pp)
+ (let ((back pp-escape-newlines) (current-tblfm))
+ (unless tblfm
+ (should-not laps)
+ (push "" tblfm)) ; Dummy formula.
+ (unless expect (setq expect target))
+ (while (setq current-tblfm (pop tblfm))
+ (org-test-with-temp-text (concat target current-tblfm)
+ ;; Search table, stop ERT at end of buffer if not found.
+ (while (not (org-at-table-p))
+ (should (eq 0 (forward-line))))
+ (when laps
+ (if (and (symbolp laps) (eq laps 'iterate))
+ (should (org-table-recalculate 'iterate t))
+ (should (integerp laps))
+ (should (< 0 laps))
+ (let ((cnt laps))
+ (while (< 0 cnt)
+ (should (org-table-recalculate 'all t))
+ (setq cnt (1- cnt))))))
+ (org-table-align)
+ (setq pp-escape-newlines nil)
+ ;; Declutter the ERT results buffer by giving only variables
+ ;; and not directly the forms to `should'.
+ (let ((expect (concat expect current-tblfm))
+ (result (buffer-substring-no-properties
+ (point-min) (point-max))))
+ (should (equal expect result)))
+ ;; If `should' passed then set back `pp-escape-newlines' here,
+ ;; else leave it nil as a side effect to see the failed table
+ ;; on multiple lines in the ERT results buffer.
+ (setq pp-escape-newlines back)))))
+
\f
;;; Navigation Functions
(when (featurep 'jump)
--
1.7.1
[-- Attachment #4: 0003-Extend-org-table-number-regexp.patch.txt --]
[-- Type: text/plain, Size: 2994 bytes --]
From 2fd7ddf550bc2f4ad7e9894e49bf6bae50485924 Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Fri, 28 Dec 2012 15:00:03 +0100
Subject: [PATCH 3/6] Extend org-table-number-regexp
* lisp/org-table.el (org-table-number-regexp): Extend 0x hex to
fixed-point number, add <radix>#<number>, add Calc infinite numbers
uinf, -inf and inf.
* testing/lisp/test-org-table.el (test-org-table/align): Adapt
alignment.
---
lisp/org-table.el | 10 +++++-----
testing/lisp/test-org-table.el | 4 ++--
2 files changed, 7 insertions(+), 7 deletions(-)
diff --git a/lisp/org-table.el b/lisp/org-table.el
index 509a1ef..6a9d0b4 100644
--- a/lisp/org-table.el
+++ b/lisp/org-table.el
@@ -112,7 +112,7 @@ table, obtained by prompting the user."
:type 'string)
(defcustom org-table-number-regexp
- "^\\([<>]?[-+^.0-9]*[0-9][-+^.0-9eEdDx()%:]*\\|\\(0[xX]\\)[0-9a-fA-F]+\\|nan\\)$"
+ "^\\([<>]?[-+^.0-9]*[0-9][-+^.0-9eEdDx()%:]*\\|[<>]?[-+]?0[xX][0-9a-fA-F.]+\\|[<>]?[-+]?[0-9]+#[0-9a-zA-Z.]+\\|nan\\|[-+u]?inf\\)$"
"Regular expression for recognizing numbers in table columns.
If a table column contains mostly numbers, it will be aligned to the
right. If not, it will be aligned to the left.
@@ -136,10 +136,10 @@ Other options offered by the customize interface are more restrictive."
"^[-+]?\\([0-9]*\\.[0-9]+\\|[0-9]+\\.?[0-9]*\\)$")
(const :tag "Exponential, Floating point, Integer"
"^[-+]?[0-9.]+\\([eEdD][-+0-9]+\\)?$")
- (const :tag "Very General Number-Like, including hex"
- "^\\([<>]?[-+^.0-9]*[0-9][-+^.0-9eEdDx()%]*\\|\\(0[xX]\\)[0-9a-fA-F]+\\|nan\\)$")
- (const :tag "Very General Number-Like, including hex, allows comma as decimal mark"
- "^\\([<>]?[-+^.,0-9]*[0-9][-+^.0-9eEdDx()%]*\\|\\(0[xX]\\)[0-9a-fA-F]+\\|nan\\)$")
+ (const :tag "Very General Number-Like, including hex and Calc radix"
+ "^\\([<>]?[-+^.0-9]*[0-9][-+^.0-9eEdDx()%]*\\|[<>]?[-+]?0[xX][0-9a-fA-F.]+\\|[<>]?[-+]?[0-9]+#[0-9a-zA-Z.]+\\|nan\\|[-+u]?inf\\)$")
+ (const :tag "Very General Number-Like, including hex and Calc radix, allows comma as decimal mark"
+ "^\\([<>]?[-+^.,0-9]*[0-9][-+^.0-9eEdDx()%]*\\|[<>]?[-+]?0[xX][0-9a-fA-F.]+\\|[<>]?[-+]?[0-9]+#[0-9a-zA-Z.]+\\|nan\\|[-+u]?inf\\)$")
(string :tag "Regexp:")))
(defcustom org-table-number-fraction 0.5
diff --git a/testing/lisp/test-org-table.el b/testing/lisp/test-org-table.el
index 4c899a8..89704ff 100644
--- a/testing/lisp/test-org-table.el
+++ b/testing/lisp/test-org-table.el
@@ -32,9 +32,9 @@
| ab | ab | ab | ab | ab | ab | ab | ab |
")
(org-test-table-target-expect "
-| 0 | 0 | 0 | 0 | 0 | 0 |
+| 0 | 0 | 0 | 0 | 0 | 0 |
| <-0x0ab.cf | >-36#0vw.yz | nan | uinf | -inf | inf |
-| ab | ab | ab | ab | ab | ab |
+| ab | ab | ab | ab | ab | ab |
"))
(ert-deftest test-org-table/org-table-convert-refs-to-an/1 ()
--
1.7.1
[-- Attachment #5: 0004-Add-ERTs-for-basic-spreadsheet-functionality.patch.txt --]
[-- Type: text/plain, Size: 26306 bytes --]
From 42a845053a8d205ed24f83e14f973118ad8c3fe5 Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Fri, 28 Dec 2012 15:00:54 +0100
Subject: [PATCH 4/6] Add ERTs for basic spreadsheet functionality
* doc/org.texi (Formula syntax for Calc): Extend explanations of
format specifiers.
* testing/lisp/test-org-table.el: Various new ert-deftest, mark with
TODO what needs to be fixed.
---
doc/org.texi | 11 +-
testing/lisp/test-org-table.el | 605 ++++++++++++++++++++++++++++++++++++++--
2 files changed, 587 insertions(+), 29 deletions(-)
diff --git a/doc/org.texi b/doc/org.texi
index b160517..0f6297d 100644
--- a/doc/org.texi
+++ b/doc/org.texi
@@ -2616,9 +2616,12 @@ n3 s3 e2 f4 @r{Normal, scientific, engineering, or fixed}
@r{long as the Calc calculation precision is greater.}
D R @r{angle modes: degrees, radians}
F S @r{fraction and symbolic modes}
-N @r{interpret all fields as numbers, use 0 for non-numbers}
-E @r{keep empty fields in ranges}
-L @r{literal}
+E @r{keep empty fields in ranges; else suppress empty fields in}
+ @r{range references and use 0 for empty field references, see}
+ @r{also the notes for `Range references' in @pxref{References}}
+N @r{interpret all fields as numbers, use 0 for non-numbers;}
+ @r{N has higher precedence than E (for the value of the field)}
+L @r{literal, for Lisp formulas only}
@end example
@noindent
@@ -2642,7 +2645,7 @@ $0;%.1f @r{Reformat current cell to 1 decimal}
$c/$1/$cm @r{Hz -> cm conversion, using @file{constants.el}}
tan($1);Dp3s1 @r{Compute in degrees, precision 3, display SCI 1}
sin($1);Dp3%.1e @r{Same, but use printf specifier for display}
-vmean($2..$7) @r{Compute column range mean, using vector function}
+vmean($2..$7) @r{Compute column range mean, suppress empty fields}
vmean($2..$7);EN @r{Same, but treat empty fields as 0}
taylor($3,x=7,2) @r{Taylor series of $3, at x=7, second degree}
@end example
diff --git a/testing/lisp/test-org-table.el b/testing/lisp/test-org-table.el
index 89704ff..68949d9 100644
--- a/testing/lisp/test-org-table.el
+++ b/testing/lisp/test-org-table.el
@@ -1,7 +1,7 @@
;;; test-org-table.el --- tests for org-table.el
;; Copyright (c) David Maus
-;; Authors: David Maus
+;; Authors: David Maus, Michael Brand
;; This file is not part of GNU Emacs.
@@ -20,10 +20,141 @@
;;;; Comments:
-;; Template test file for Org-mode tests
+;; Template test file for Org-mode tests. First the tests that are
+;; also a howto example collection as a user documentation, more or
+;; less all those using `org-test-table-target-expect'. Then the
+;; internal and more abstract tests. See also the doc string of
+;; `org-test-table-target-expect'.
;;; Code:
+(require 'org-table) ; `org-table-make-reference'
+
+(ert-deftest test-org-table/simple-formula/no-grouping/no-title-row ()
+ "Simple sum without grouping rows, without title row."
+ (org-test-table-target-expect
+ "
+| 2 |
+| 4 |
+| 8 |
+| replace |
+"
+ "
+| 2 |
+| 4 |
+| 8 |
+| 14 |
+"
+ 1
+ ;; Calc formula
+ "#+TBLFM: @>$1 = vsum(@<..@>>)"
+ ;; Lisp formula
+ "#+TBLFM: @>$1 = '(+ @<..@>>); N"))
+
+(ert-deftest test-org-table/simple-formula/no-grouping/with-title-row ()
+ "Simple sum without grouping rows, with title row."
+ (org-test-table-target-expect
+ "
+| foo |
+|---------|
+| 2 |
+| 4 |
+| 8 |
+| replace |
+"
+ "
+| foo |
+|-----|
+| 2 |
+| 4 |
+| 8 |
+| 14 |
+"
+ 1
+ ;; Calc formula
+ "#+TBLFM: @>$1 = vsum(@I..@>>)"
+ ;; Lisp formula
+ "#+TBLFM: @>$1 = '(+ @I..@>>); N"))
+
+(ert-deftest test-org-table/simple-formula/with-grouping/no-title-row ()
+ "Simple sum with grouping rows, how not to do."
+ ;; The first example has a problem, see the second example in this
+ ;; ert-deftest.
+ (org-test-table-target-expect
+ "
+| 2 |
+| 4 |
+| 8 |
+|---------|
+| replace |
+"
+ "
+| 2 |
+| 4 |
+| 8 |
+|----|
+| 14 |
+"
+ 1
+ ;; Calc formula
+ "#+TBLFM: $1 = vsum(@<..@>>)"
+ ;; Lisp formula
+ "#+TBLFM: $1 = '(+ @<..@>>); N")
+
+ ;; The problem is that the first three rows with the summands are
+ ;; considered the header and therefore column formulas are not
+ ;; applied on them as shown below. Also export behaves unexpected.
+ ;; See next ert-deftest how to group rows right.
+ (org-test-table-target-expect
+ "
+| 2 | replace |
+| 4 | replace |
+| 8 | replace |
+|---------+---------|
+| replace | replace |
+"
+ "
+| 2 | replace |
+| 4 | replace |
+| 8 | replace |
+|----+---------|
+| 14 | 28 |
+"
+ 2
+ ;; Calc formula
+ "#+TBLFM: @>$1 = vsum(@<..@>>) :: $2 = 2 * $1"
+ ;; Lisp formula
+ "#+TBLFM: @>$1 = '(+ @<..@>>); N :: $2 = '(* 2 $1); N"))
+
+(ert-deftest test-org-table/simple-formula/with-grouping/with-title-row ()
+ "Simple sum with grouping rows, how to do it right."
+ ;; Always add a top row with the column names separated by hline to
+ ;; get the desired header when you want to group rows.
+ (org-test-table-target-expect
+ "
+| foo | bar |
+|---------+---------|
+| 2 | replace |
+| 4 | replace |
+| 8 | replace |
+|---------+---------|
+| replace | replace |
+"
+ "
+| foo | bar |
+|-----+-----|
+| 2 | 4 |
+| 4 | 8 |
+| 8 | 16 |
+|-----+-----|
+| 14 | 28 |
+"
+ 2
+ ;; Calc formula
+ "#+TBLFM: @>$1 = vsum(@I..@>>) :: $2 = 2 * $1"
+ ;; Lisp formula
+ "#+TBLFM: @>$1 = '(+ @I..@>>); N :: $2 = '(* 2 $1); N"))
+
(ert-deftest test-org-table/align ()
"Align columns within Org buffer, depends on `org-table-number-regexp'."
(org-test-table-target-expect "
@@ -37,6 +168,453 @@
| ab | ab | ab | ab | ab | ab |
"))
+(defconst references/target-normal "
+| 0 | 1 | replace | replace | replace | replace | replace | replace |
+| z | 1 | replace | replace | replace | replace | replace | replace |
+| | 1 | replace | replace | replace | replace | replace | replace |
+| | | replace | replace | replace | replace | replace | replace |
+"
+ "Normal numbers and non-numbers for Lisp and Calc formula.")
+
+(defconst references/target-special "
+| nan | 1 | replace | replace | replace | replace | replace | replace |
+| uinf | 1 | replace | replace | replace | replace | replace | replace |
+| -inf | 1 | replace | replace | replace | replace | replace | replace |
+| inf | 1 | replace | replace | replace | replace | replace | replace |
+"
+ "Special numbers for Calc formula.")
+
+(ert-deftest test-org-table/references/format-specifier-EL ()
+ "Basic: Assign field reference, sum of field references, sum
+and len of simple range reference (no row) and complex range
+reference (with row). Format specifier EL."
+ ;; Empty fields are kept during parsing field but lost as list
+ ;; elements within Lisp formula syntactically when used literally
+ ;; and not enclosed with " within fields, see last columns with len.
+ (org-test-table-target-expect
+ references/target-normal
+ ;; All the #ERROR show that for Lisp calculations N has to be used.
+ "
+| 0 | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| z | 1 | z | #ERROR | #ERROR | #ERROR | 2 | 2 |
+| | 1 | | 1 | 1 | 1 | 1 | 1 |
+| | | | 0 | 0 | 0 | 0 | 0 |
+"
+ 1 (concat
+ "#+TBLFM: $3 = '(identity \"$1\"); EL :: $4 = '(+ $1 $2); EL :: "
+ "$5 = '(+ $1..$2); EL :: $6 = '(+ @0$1..@0$2); EL :: "
+ "$7 = '(length '($1..$2)); EL :: $8 = '(length '(@0$1..@0$2)); EL"))
+
+ ;; Empty fields are kept during parsing field _and_ as list elements
+ ;; within Lisp formula syntactically even when used literally when
+ ;; enclosed with " within fields, see last columns with len.
+ (org-test-table-target-expect
+ "
+| \"0\" | \"1\" | repl | repl | repl | repl | repl | repl |
+| \"z\" | \"1\" | repl | repl | repl | repl | repl | repl |
+| \"\" | \"1\" | repl | repl | repl | repl | repl | repl |
+| \"\" | \"\" | repl | repl | repl | repl | repl | repl |
+"
+ "
+| \"0\" | \"1\" | \"0\" | 1 | #ERROR | #ERROR | 2 | 2 |
+| \"z\" | \"1\" | \"z\" | 1 | #ERROR | #ERROR | 2 | 2 |
+| \"\" | \"1\" | \"\" | 1 | #ERROR | #ERROR | 2 | 2 |
+| \"\" | \"\" | \"\" | 0 | #ERROR | #ERROR | 2 | 2 |
+"
+ 1 (concat
+ "#+TBLFM: $3 = '(concat \"\\\"\" $1 \"\\\"\"); EL :: "
+ "$4 = '(+ (string-to-number $1) (string-to-number $2)); EL :: "
+ "$5 = '(+ $1..$2); EL :: $6 = '(+ @0$1..@0$2); EL :: "
+ "$7 = '(length '($1..$2)); EL :: $8 = '(length '(@0$1..@0$2)); EL")))
+
+(ert-deftest test-org-table/references/format-specifier-E ()
+ "Basic: Assign field reference, sum of field references, sum
+and len of simple range reference (no row) and complex range
+reference (with row). Format specifier E."
+ (let ((lisp
+ (concat
+ "#+TBLFM: $3 = '(identity $1); E :: $4 = '(+ $1 $2); E :: "
+ "$5 = '(+ $1..$2); E :: $6 = '(+ @0$1..@0$2); E :: "
+ "$7 = '(length '($1..$2)); E :: $8 = '(length '(@0$1..@0$2)); E"))
+ (calc
+ (concat
+ "#+TBLFM: $3 = $1; E :: $4 = $1 + $2; E :: "
+ "$5 = vsum($1..$2); E :: $6 = vsum(@0$1..@0$2); E :: "
+ "$7 = vlen($1..$2); E :: $8 = vlen(@0$1..@0$2); E")))
+ (org-test-table-target-expect
+ references/target-normal
+ ;; All the #ERROR show that for Lisp calculations N has to be used.
+ "
+| 0 | 1 | 0 | #ERROR | #ERROR | #ERROR | 2 | 2 |
+| z | 1 | z | #ERROR | #ERROR | #ERROR | 2 | 2 |
+| | 1 | | #ERROR | #ERROR | #ERROR | 2 | 2 |
+| | | | #ERROR | #ERROR | #ERROR | 2 | 2 |
+"
+ 1 lisp)
+ (org-test-table-target-expect
+ references/target-normal
+ "
+| 0 | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| z | 1 | z | z + 1 | z + 1 | z + 1 | 2 | 2 |
+| | 1 | 0 | 1 | #ERROR | #ERROR | #ERROR | #ERROR |
+| | | 0 | 0 | #ERROR | #ERROR | #ERROR | #ERROR |
+"
+ 1 calc)
+ (org-test-table-target-expect
+ references/target-special
+ "
+| nan | 1 | nan | nan | nan | nan | 2 | 2 |
+| uinf | 1 | uinf | uinf | uinf | uinf | 2 | 2 |
+| -inf | 1 | -inf | -inf | -inf | -inf | 2 | 2 |
+| inf | 1 | inf | inf | inf | inf | 2 | 2 |
+"
+ 1 calc)))
+
+(ert-deftest test-org-table/references/format-specifier-EN ()
+ "Basic: Assign field reference, sum of field references, sum
+and len of simple range reference (no row) and complex range
+reference (with row). Format specifier EN."
+ (let ((lisp (concat
+ "#+TBLFM: $3 = '(identity $1); EN :: $4 = '(+ $1 $2); EN :: "
+ "$5 = '(+ $1..$2); EN :: $6 = '(+ @0$1..@0$2); EN :: "
+ "$7 = '(length '($1..$2)); EN :: "
+ "$8 = '(length '(@0$1..@0$2)); EN"))
+ (calc (concat
+ "#+TBLFM: $3 = $1; EN :: $4 = $1 + $2; EN :: "
+ "$5 = vsum($1..$2); EN :: $6 = vsum(@0$1..@0$2); EN :: "
+ "$7 = vlen($1..$2); EN :: $8 = vlen(@0$1..@0$2); EN")))
+ (org-test-table-target-expect
+ references/target-normal
+ "
+| 0 | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| z | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| | | 0 | 0 | 0 | 0 | 2 | 2 |
+"
+ 1 lisp calc)
+ (org-test-table-target-expect
+ references/target-special
+ "
+| nan | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| uinf | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| -inf | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| inf | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+"
+ 1 calc)))
+
+(ert-deftest test-org-table/references/format-specifier-L ()
+ "Basic: Assign field reference, sum of field references, sum
+and len of simple range reference (no row) and complex range
+reference (with row). Format specifier L."
+ (org-test-table-target-expect
+ references/target-normal
+ ;; All the #ERROR show that for Lisp calculations N has to be used.
+ ;; TODO: Len for range reference with only empty fields should be 0.
+ "
+| 0 | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| z | 1 | z | #ERROR | #ERROR | #ERROR | 2 | 2 |
+| | 1 | | 1 | 1 | 1 | 1 | 1 |
+| | | | 0 | 0 | 0 | 1 | 1 |
+"
+ 1 (concat
+ "#+TBLFM: $3 = '(identity \"$1\"); L :: $4 = '(+ $1 $2); L :: "
+ "$5 = '(+ $1..$2); L :: $6 = '(+ @0$1..@0$2); L :: "
+ "$7 = '(length '($1..$2)); L :: $8 = '(length '(@0$1..@0$2)); L")))
+
+(ert-deftest test-org-table/references/format-specifier-none ()
+ "Basic: Assign field reference, sum of field references, sum
+and len of simple range reference (no row) and complex range
+reference (with row). No format specifier."
+ (let ((lisp (concat
+ "#+TBLFM: $3 = '(identity $1) :: $4 = '(+ $1 $2) :: "
+ "$5 = '(+ $1..$2) :: $6 = '(+ @0$1..@0$2) :: "
+ "$7 = '(length '($1..$2)) :: $8 = '(length '(@0$1..@0$2))"))
+ (calc (concat
+ "#+TBLFM: $3 = $1 :: $4 = $1 + $2 :: "
+ "$5 = vsum($1..$2) :: $6 = vsum(@0$1..@0$2) :: "
+ "$7 = vlen($1..$2) :: $8 = vlen(@0$1..@0$2)")))
+ (org-test-table-target-expect
+ references/target-normal
+ ;; All the #ERROR show that for Lisp calculations N has to be used.
+ "
+| 0 | 1 | 0 | #ERROR | #ERROR | #ERROR | 2 | 2 |
+| z | 1 | z | #ERROR | #ERROR | #ERROR | 2 | 2 |
+| | 1 | | #ERROR | #ERROR | #ERROR | 1 | 1 |
+| | | | #ERROR | #ERROR | #ERROR | 1 | 1 |
+"
+ 1 lisp)
+ (org-test-table-target-expect
+ references/target-normal
+ "
+| 0 | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| z | 1 | z | z + 1 | z + 1 | z + 1 | 2 | 2 |
+| | 1 | 0 | 1 | 1 | 1 | 1 | 1 |
+| | | 0 | 0 | 0 | 0 | 1 | 1 |
+"
+ 1 calc)
+ (org-test-table-target-expect
+ references/target-special
+ "
+| nan | 1 | nan | nan | nan | nan | 2 | 2 |
+| uinf | 1 | uinf | uinf | uinf | uinf | 2 | 2 |
+| -inf | 1 | -inf | -inf | -inf | -inf | 2 | 2 |
+| inf | 1 | inf | inf | inf | inf | 2 | 2 |
+"
+ 1 calc)))
+
+(ert-deftest test-org-table/references/format-specifier-N ()
+ "Basic: Assign field reference, sum of field references, sum
+and len of simple range reference (no row) and complex range
+reference (with row). Format specifier N."
+ (let ((lisp
+ (concat
+ "#+TBLFM: $3 = '(identity $1); N :: $4 = '(+ $1 $2); N :: "
+ "$5 = '(+ $1..$2); N :: $6 = '(+ @0$1..@0$2); N :: "
+ "$7 = '(length '($1..$2)); N :: $8 = '(length '(@0$1..@0$2)); N"))
+ (calc
+ (concat
+ "#+TBLFM: $3 = $1; N :: $4 = $1 + $2; N :: "
+ "$5 = vsum($1..$2); N :: $6 = vsum(@0$1..@0$2); N :: "
+ "$7 = vlen($1..$2); N :: $8 = vlen(@0$1..@0$2); N")))
+ (org-test-table-target-expect
+ references/target-normal
+ ;; TODO: Len for simple range reference with empty field should
+ ;; also be 1
+ "
+| 0 | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| z | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| | 1 | 0 | 1 | 1 | 1 | 2 | 1 |
+| | | 0 | 0 | 0 | 0 | 2 | 1 |
+"
+ 1 lisp calc)
+ (org-test-table-target-expect
+ references/target-special
+ "
+| nan | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| uinf | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| -inf | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| inf | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+"
+ 1 calc)))
+
+(ert-deftest test-org-table/compare ()
+ "Basic: Compare field references in Calc."
+ (org-test-table-target-expect
+ "
+| | 0 | z | nan | uinf | -inf | inf |
+|------+------+------+------+------+------+------|
+| 0 | repl | repl | repl | repl | repl | repl |
+| z | repl | repl | repl | repl | repl | repl |
+| nan | repl | repl | repl | repl | repl | repl |
+| uinf | repl | repl | repl | repl | repl | repl |
+| -inf | repl | repl | repl | repl | repl | repl |
+| inf | repl | repl | repl | repl | repl | repl |
+"
+ "
+| | 0 | z | nan | uinf | -inf | inf |
+|------+---+---+-----+------+------+-----|
+| 0 | x | | | | | |
+| z | | x | | | | |
+| nan | | | x | | | |
+| uinf | | | | x | | |
+| -inf | | | | | x | |
+| inf | | | | | | x |
+"
+ 1
+ ;; Compare field reference ($1) with field reference (@1)
+ "#+TBLFM: @I$<<..@>$> = if(\"$1\" = \"@1\", x, string(\"\")); E"
+ ;; Compare field reference ($1) with absolute term
+ (concat "#+TBLFM: "
+ "$2 = if(\"$1\" = \"(0)\" , x, string(\"\")); E :: "
+ "$3 = if(\"$1\" = \"(z)\" , x, string(\"\")); E :: "
+ "$4 = if(\"$1\" = \"(nan)\" , x, string(\"\")); E :: "
+ "$5 = if(\"$1\" = \"(uinf)\", x, string(\"\")); E :: "
+ "$6 = if(\"$1\" = \"(-inf)\", x, string(\"\")); E :: "
+ "$7 = if(\"$1\" = \"(inf)\" , x, string(\"\")); E"))
+
+ ;; Check field reference converted from an empty field: Despite this
+ ;; field reference will not end up in a result, Calc evaluates it.
+ ;; Make sure that also then there is no Calc error.
+ (org-test-table-target-expect
+ "
+| 0 | replace |
+| z | replace |
+| nan | replace |
+"
+ "
+| 0 | 1 |
+| z | z + 1 |
+| nan | nan |
+"
+ 1 "#+TBLFM: $2 = if(\"$1\" = \"nan\", string(\"\"), $1 + 1); E"))
+
+(ert-deftest test-org-table/empty-field ()
+ "Examples how to deal with empty fields."
+ ;; Empty fields in simple and complex range reference: Suppress them
+ ;; ($5 and $6) or keep them and use 0 ($7 and $8)
+
+ ;; Calc formula
+ (org-test-table-target-expect
+ "\n| | | 5 | 7 | replace | replace | replace | replace |\n"
+ "\n| | | 5 | 7 | 6 | 6 | 3 | 3 |\n"
+ 1
+ (concat "#+TBLFM: "
+ "$5 = vmean($1..$4) :: $6 = vmean(@0$1..@0$4) :: "
+ "$7 = vmean($1..$4); EN :: $8 = vmean(@0$1..@0$4); EN"))
+
+ ;; Lisp formula
+ ;; TODO: Len for simple range reference with empty field should also
+ ;; be 6
+ (org-test-table-target-expect
+ "\n| | | 5 | 7 | replace | replace | replace | replace |\n"
+ "\n| | | 5 | 7 | 3 | 6 | 3 | 3 |\n"
+ 1
+ (concat "#+TBLFM: "
+ "$5 = '(/ (+ $1..$4 ) (length '( $1..$4 ))); N :: "
+ "$6 = '(/ (+ @0$1..@0$4) (length '(@0$1..@0$4))); N :: "
+ "$7 = '(/ (+ $1..$4 ) (length '( $1..$4 ))); EN :: "
+ "$8 = '(/ (+ @0$1..@0$4) (length '(@0$1..@0$4))); EN")))
+
+;; End of table examples and beginning of internal tests.
+
+(ert-deftest test-org-table/org-table-make-reference/format-specifier-EL ()
+ (fset 'f 'org-table-make-reference)
+ ;; For Lisp formula only
+ (should (equal "0" (f "0" t nil 'literal)))
+ (should (equal "z" (f "z" t nil 'literal)))
+ (should (equal "" (f "" t nil 'literal)))
+ (should (equal "0 1" (f '("0" "1") t nil 'literal)))
+ (should (equal "z 1" (f '("z" "1") t nil 'literal)))
+ (should (equal " 1" (f '("" "1") t nil 'literal)))
+ (should (equal " " (f '("" "" ) t nil 'literal))))
+
+(ert-deftest test-org-table/org-table-make-reference/format-specifier-E ()
+ (fset 'f 'org-table-make-reference)
+ ;; For Lisp formula
+ (should (equal "\"0\"" (f "0" t nil t)))
+ (should (equal "\"z\"" (f "z" t nil t)))
+ (should (equal "\"\"" (f "" t nil t)))
+ (should (equal "\"0\" \"1\"" (f '("0" "1") t nil t)))
+ (should (equal "\"z\" \"1\"" (f '("z" "1") t nil t)))
+ (should (equal "\"\" \"1\"" (f '("" "1") t nil t)))
+ (should (equal "\"\" \"\"" (f '("" "" ) t nil t)))
+ ;; For Calc formula
+ (should (equal "(0)" (f "0" t nil nil)))
+ (should (equal "(z)" (f "z" t nil nil)))
+ (should (equal "(0)" (f "" t nil nil)))
+ (should (equal "[0,1]" (f '("0" "1") t nil nil)))
+ (should (equal "[z,1]" (f '("z" "1") t nil nil)))
+ (should (equal "[,1]" (f '("" "1") t nil nil)))
+ (should (equal "[,]" (f '("" "" ) t nil nil)))
+ ;; For Calc formula, special numbers
+ (should (equal "(nan)" (f "nan" t nil nil)))
+ (should (equal "(uinf)" (f "uinf" t nil nil)))
+ (should (equal "(-inf)" (f "-inf" t nil nil)))
+ (should (equal "(inf)" (f "inf" t nil nil)))
+ (should (equal "[nan,1]" (f '( "nan" "1") t nil nil)))
+ (should (equal "[uinf,1]" (f '("uinf" "1") t nil nil)))
+ (should (equal "[-inf,1]" (f '("-inf" "1") t nil nil)))
+ (should (equal "[inf,1]" (f '( "inf" "1") t nil nil))))
+
+(ert-deftest test-org-table/org-table-make-reference/format-specifier-EN ()
+ (fset 'f 'org-table-make-reference)
+ ;; For Lisp formula
+ (should (equal "0" (f "0" t t t)))
+ (should (equal "0" (f "z" t t t)))
+ (should (equal "0" (f "" t t t)))
+ (should (equal "0 1" (f '("0" "1") t t t)))
+ (should (equal "0 1" (f '("z" "1") t t t)))
+ (should (equal "0 1" (f '("" "1") t t t)))
+ (should (equal "0 0" (f '("" "" ) t t t)))
+ ;; For Calc formula
+ (should (equal "(0)" (f "0" t t nil)))
+ (should (equal "(0)" (f "z" t t nil)))
+ (should (equal "(0)" (f "" t t nil)))
+ (should (equal "[0,1]" (f '("0" "1") t t nil)))
+ (should (equal "[0,1]" (f '("z" "1") t t nil)))
+ (should (equal "[0,1]" (f '("" "1") t t nil)))
+ (should (equal "[0,0]" (f '("" "" ) t t nil)))
+ ;; For Calc formula, special numbers
+ (should (equal "(0)" (f "nan" t t nil)))
+ (should (equal "(0)" (f "uinf" t t nil)))
+ (should (equal "(0)" (f "-inf" t t nil)))
+ (should (equal "(0)" (f "inf" t t nil)))
+ (should (equal "[0,1]" (f '( "nan" "1") t t nil)))
+ (should (equal "[0,1]" (f '("uinf" "1") t t nil)))
+ (should (equal "[0,1]" (f '("-inf" "1") t t nil)))
+ (should (equal "[0,1]" (f '( "inf" "1") t t nil))))
+
+(ert-deftest test-org-table/org-table-make-reference/format-specifier-L ()
+ (fset 'f 'org-table-make-reference)
+ ;; For Lisp formula only
+ (should (equal "0" (f "0" nil nil 'literal)))
+ (should (equal "z" (f "z" nil nil 'literal)))
+ (should (equal "" (f "" nil nil 'literal)))
+ (should (equal "0 1" (f '("0" "1") nil nil 'literal)))
+ (should (equal "z 1" (f '("z" "1") nil nil 'literal)))
+ (should (equal "1" (f '("" "1") nil nil 'literal)))
+ ;; TODO: Should result in empty string like with field reference of
+ ;; empty field.
+ (should (equal "0" (f '("" "" ) nil nil 'literal))))
+
+(ert-deftest test-org-table/org-table-make-reference/format-specifier-none ()
+ (fset 'f 'org-table-make-reference)
+ ;; For Lisp formula
+ (should (equal "\"0\"" (f "0" nil nil t)))
+ (should (equal "\"z\"" (f "z" nil nil t)))
+ (should (equal "\"\"" (f "" nil nil t)))
+ (should (equal "\"0\" \"1\"" (f '("0" "1") nil nil t)))
+ (should (equal "\"z\" \"1\"" (f '("z" "1") nil nil t)))
+ (should (equal "\"1\"" (f '("" "1") nil nil t)))
+ ;; TODO: Should result in empty string like with field reference of
+ ;; empty field.
+ (should (equal "\"0\"" (f '("" "" ) nil nil t)))
+ ;; For Calc formula
+ (should (equal "(0)" (f "0" nil nil nil)))
+ (should (equal "(z)" (f "z" nil nil nil)))
+ (should (equal "(0)" (f "" nil nil nil)))
+ (should (equal "[0,1]" (f '("0" "1") nil nil nil)))
+ (should (equal "[z,1]" (f '("z" "1") nil nil nil)))
+ (should (equal "[1]" (f '("" "1") nil nil nil)))
+ (should (equal "[0]" (f '("" "" ) nil nil nil)))
+ ;; For Calc formula, special numbers
+ (should (equal "(nan)" (f "nan" nil nil nil)))
+ (should (equal "(uinf)" (f "uinf" nil nil nil)))
+ (should (equal "(-inf)" (f "-inf" nil nil nil)))
+ (should (equal "(inf)" (f "inf" nil nil nil)))
+ (should (equal "[nan,1]" (f '( "nan" "1") nil nil nil)))
+ (should (equal "[uinf,1]" (f '("uinf" "1") nil nil nil)))
+ (should (equal "[-inf,1]" (f '("-inf" "1") nil nil nil)))
+ (should (equal "[inf,1]" (f '( "inf" "1") nil nil nil))))
+
+(ert-deftest test-org-table/org-table-make-reference/format-specifier-N ()
+ (fset 'f 'org-table-make-reference)
+ ;; For Lisp formula
+ (should (equal "0" (f "0" nil t t)))
+ (should (equal "0" (f "z" nil t t)))
+ (should (equal "0" (f "" nil t t)))
+ (should (equal "0 1" (f '("0" "1") nil t t)))
+ (should (equal "0 1" (f '("z" "1") nil t t)))
+ (should (equal "1" (f '("" "1") nil t t)))
+ (should (equal "0" (f '("" "" ) nil t t)))
+ ;; For Calc formula
+ (should (equal "(0)" (f "0" nil t nil)))
+ (should (equal "(0)" (f "z" nil t nil)))
+ (should (equal "(0)" (f "" nil t nil)))
+ (should (equal "[0,1]" (f '("0" "1") nil t nil)))
+ (should (equal "[0,1]" (f '("z" "1") nil t nil)))
+ (should (equal "[1]" (f '("" "1") nil t nil)))
+ (should (equal "[0]" (f '("" "" ) nil t nil)))
+ ;; For Calc formula, special numbers
+ (should (equal "(0)" (f "nan" nil t nil)))
+ (should (equal "(0)" (f "uinf" nil t nil)))
+ (should (equal "(0)" (f "-inf" nil t nil)))
+ (should (equal "(0)" (f "inf" nil t nil)))
+ (should (equal "[0,1]" (f '( "nan" "1") nil t nil)))
+ (should (equal "[0,1]" (f '("uinf" "1") nil t nil)))
+ (should (equal "[0,1]" (f '("-inf" "1") nil t nil)))
+ (should (equal "[0,1]" (f '( "inf" "1") nil t nil))))
+
(ert-deftest test-org-table/org-table-convert-refs-to-an/1 ()
"Simple reference @1$1."
(should
@@ -69,29 +647,6 @@
;; (should
;; (string= "$3 = remote(FOO, @@#$2)" (org-table-convert-refs-to-rc "C& = remote(FOO, @@#B&)"))))
-(ert-deftest test-org-table/simple-formula ()
- (org-test-with-temp-text-in-file "
-
-* simple formula
- :PROPERTIES:
- :ID: 563523f7-3f3e-49c9-9622-9216cc9a5d95
- :END:
-
-#+tblname: simple-formula
-| 1 |
-| 2 |
-| 3 |
-| 4 |
-|----|
-| |
- #+TBLFM: $1=vsum(@1..@-1)
-"
- (re-search-forward (regexp-quote "#+tblname: simple-formula") nil t)
- (forward-line 1)
- (should (org-at-table-p))
- (should (org-table-recalculate 'all))
- (should (string= "10" (first (nth 5 (org-table-to-lisp)))))))
-
(provide 'test-org-table)
;;; test-org-table.el ends here
--
1.7.1
[-- Attachment #6: 0005-org-table.el-Fix-range-len-bugs-and-inconsistencies.patch.txt --]
[-- Type: text/plain, Size: 5830 bytes --]
From ef9b7f9dc4d9f0513b36e8630443f099280636c2 Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Fri, 28 Dec 2012 15:02:01 +0100
Subject: [PATCH 5/6] org-table.el: Fix range len bugs and inconsistencies
* lisp/org-table.el (org-table-eval-formula): Keep empty fields during
preprocessing.
(org-table-make-reference): A range with only empty fields should not
always return 0 but also empty string, consistent with field reference
of an empty field. Use future design for nan but replicate current
behavior.
* testing/lisp/test-org-table.el: Adapt expected for several
ert-deftest.
The range len bugs may lead to wrong calculations for range references
with empty fields when the range len is relevant. Affects typically
Calc vmean on simple range and without format specifier EN. Also
Lisp with e. g. `length' on simple range or with L.
---
lisp/org-table.el | 13 ++++++++++---
testing/lisp/test-org-table.el | 30 ++++++++----------------------
2 files changed, 18 insertions(+), 25 deletions(-)
diff --git a/lisp/org-table.el b/lisp/org-table.el
index 6a9d0b4..1f540b4 100644
--- a/lisp/org-table.el
+++ b/lisp/org-table.el
@@ -2557,7 +2557,10 @@ not overwrite the stored one."
fields)))
(if (eq numbers t)
(setq fields (mapcar
- (lambda (x) (number-to-string (string-to-number x)))
+ (lambda (x)
+ (if (string-match "\\S-" x)
+ (number-to-string (string-to-number x))
+ x))
fields)))
(setq ndown (1- ndown))
(setq form (copy-sequence formula)
@@ -2862,7 +2865,7 @@ LISPP means to return something appropriate for a Lisp list."
(delq nil
(mapcar (lambda (x) (if (string-match "\\S-" x) x nil))
elements))))
- (setq elements (or elements '("0")))
+ (setq elements (or elements '("")))
(if lispp
(mapconcat
(lambda (x)
@@ -2872,7 +2875,11 @@ LISPP means to return something appropriate for a Lisp list."
elements " ")
(concat "[" (mapconcat
(lambda (x)
- (if numbers (number-to-string (string-to-number x)) x))
+ (if (string-match "\\S-" x)
+ (if numbers
+ (number-to-string (string-to-number x))
+ x)
+ (if (or (not keep-empty) numbers) "0" "")))
elements
",") "]"))))
diff --git a/testing/lisp/test-org-table.el b/testing/lisp/test-org-table.el
index 68949d9..6133005 100644
--- a/testing/lisp/test-org-table.el
+++ b/testing/lisp/test-org-table.el
@@ -309,12 +309,11 @@ reference (with row). Format specifier L."
(org-test-table-target-expect
references/target-normal
;; All the #ERROR show that for Lisp calculations N has to be used.
- ;; TODO: Len for range reference with only empty fields should be 0.
"
| 0 | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
| z | 1 | z | #ERROR | #ERROR | #ERROR | 2 | 2 |
| | 1 | | 1 | 1 | 1 | 1 | 1 |
-| | | | 0 | 0 | 0 | 1 | 1 |
+| | | | 0 | 0 | 0 | 0 | 0 |
"
1 (concat
"#+TBLFM: $3 = '(identity \"$1\"); L :: $4 = '(+ $1 $2); L :: "
@@ -378,13 +377,11 @@ reference (with row). Format specifier N."
"$7 = vlen($1..$2); N :: $8 = vlen(@0$1..@0$2); N")))
(org-test-table-target-expect
references/target-normal
- ;; TODO: Len for simple range reference with empty field should
- ;; also be 1
"
| 0 | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
| z | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
-| | 1 | 0 | 1 | 1 | 1 | 2 | 1 |
-| | | 0 | 0 | 0 | 0 | 2 | 1 |
+| | 1 | 0 | 1 | 1 | 1 | 1 | 1 |
+| | | 0 | 0 | 0 | 0 | 1 | 1 |
"
1 lisp calc)
(org-test-table-target-expect
@@ -453,22 +450,15 @@ reference (with row). Format specifier N."
;; Empty fields in simple and complex range reference: Suppress them
;; ($5 and $6) or keep them and use 0 ($7 and $8)
- ;; Calc formula
(org-test-table-target-expect
"\n| | | 5 | 7 | replace | replace | replace | replace |\n"
"\n| | | 5 | 7 | 6 | 6 | 3 | 3 |\n"
1
+ ;; Calc formula
(concat "#+TBLFM: "
"$5 = vmean($1..$4) :: $6 = vmean(@0$1..@0$4) :: "
- "$7 = vmean($1..$4); EN :: $8 = vmean(@0$1..@0$4); EN"))
-
- ;; Lisp formula
- ;; TODO: Len for simple range reference with empty field should also
- ;; be 6
- (org-test-table-target-expect
- "\n| | | 5 | 7 | replace | replace | replace | replace |\n"
- "\n| | | 5 | 7 | 3 | 6 | 3 | 3 |\n"
- 1
+ "$7 = vmean($1..$4); EN :: $8 = vmean(@0$1..@0$4); EN")
+ ;; Lisp formula
(concat "#+TBLFM: "
"$5 = '(/ (+ $1..$4 ) (length '( $1..$4 ))); N :: "
"$6 = '(/ (+ @0$1..@0$4) (length '(@0$1..@0$4))); N :: "
@@ -553,9 +543,7 @@ reference (with row). Format specifier N."
(should (equal "0 1" (f '("0" "1") nil nil 'literal)))
(should (equal "z 1" (f '("z" "1") nil nil 'literal)))
(should (equal "1" (f '("" "1") nil nil 'literal)))
- ;; TODO: Should result in empty string like with field reference of
- ;; empty field.
- (should (equal "0" (f '("" "" ) nil nil 'literal))))
+ (should (equal "" (f '("" "" ) nil nil 'literal))))
(ert-deftest test-org-table/org-table-make-reference/format-specifier-none ()
(fset 'f 'org-table-make-reference)
@@ -566,9 +554,7 @@ reference (with row). Format specifier N."
(should (equal "\"0\" \"1\"" (f '("0" "1") nil nil t)))
(should (equal "\"z\" \"1\"" (f '("z" "1") nil nil t)))
(should (equal "\"1\"" (f '("" "1") nil nil t)))
- ;; TODO: Should result in empty string like with field reference of
- ;; empty field.
- (should (equal "\"0\"" (f '("" "" ) nil nil t)))
+ (should (equal "\"\"" (f '("" "" ) nil nil t)))
;; For Calc formula
(should (equal "(0)" (f "0" nil nil nil)))
(should (equal "(z)" (f "z" nil nil nil)))
--
1.7.1
[-- Attachment #7: 0006-Use-nan-for-empty-fields-in-Calc-formulas.patch.txt --]
[-- Type: text/plain, Size: 13560 bytes --]
From a0fcb1048ee3c638dbc5f3be98bce6b3bf0e5588 Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Fri, 28 Dec 2012 15:03:10 +0100
Subject: [PATCH 6/6] Use nan for empty fields in Calc formulas
* doc/org.texi (Formula syntax for Calc): Add explanation and example
for empty field.
* lisp/org-table.el (org-table-eval-formula): Use `keep-empty' in more
places.
(org-table-make-reference): Use nan (not a number) for empty fields in
Calc formulas.
* testing/lisp/test-org-table.el: Adapt expected for several
ert-deftest.
(test-org-table/empty-field): New examples dealing with empty fields.
(test-org-table/copy-field): New ert-deftest with examples dealing with
empty fields.
This makes it possible to write spreadsheet Calc formulas that check
for empty fields: To sum the first two columns unless one or both
empty:
$3 = if("$1" = "nan" || "$2" = "nan", string(""), $1 + $2); E
---
doc/org.texi | 9 ++-
lisp/org-table.el | 30 +++++--
testing/lisp/test-org-table.el | 173 +++++++++++++++++++++++++++++++++-------
3 files changed, 174 insertions(+), 38 deletions(-)
diff --git a/doc/org.texi b/doc/org.texi
index 0f6297d..e282050 100644
--- a/doc/org.texi
+++ b/doc/org.texi
@@ -2616,7 +2616,9 @@ n3 s3 e2 f4 @r{Normal, scientific, engineering, or fixed}
@r{long as the Calc calculation precision is greater.}
D R @r{angle modes: degrees, radians}
F S @r{fraction and symbolic modes}
-E @r{keep empty fields in ranges; else suppress empty fields in}
+E @r{keep empty fields in ranges and use nan (not a number)}
+ @r{in Calc formulas for empty fields in range references and}
+ @r{for empty field references; else suppress empty fields in}
@r{range references and use 0 for empty field references, see}
@r{also the notes for `Range references' in @pxref{References}}
N @r{interpret all fields as numbers, use 0 for non-numbers;}
@@ -2653,7 +2655,10 @@ taylor($3,x=7,2) @r{Taylor series of $3, at x=7, second degree}
Calc also contains a complete set of logical operations. For example
@example
-if($1<20,teen,string("")) @r{"teen" if age $1 less than 20, else empty}
+if($1 < 20, teen, string(""))
+ @r{"teen" if age $1 is less than 20, else empty}
+if("$1" = "nan" || "$2" = "nan", string(""), $1 + $2); E
+ @r{sum of first two columns unless one or both empty}
@end example
Note that you can also use two org-specific flags @code{T} and @code{t} for
diff --git a/lisp/org-table.el b/lisp/org-table.el
index 1f540b4..0e7b944 100644
--- a/lisp/org-table.el
+++ b/lisp/org-table.el
@@ -2637,7 +2637,8 @@ not overwrite the stored one."
(match-string 0 form)))
(setq form (replace-match
(save-match-data
- (org-table-make-reference x nil numbers lispp))
+ (org-table-make-reference
+ x keep-empty numbers lispp))
t t form)))
(if lispp
@@ -2664,7 +2665,8 @@ not overwrite the stored one."
(setq ev (if (and duration (string-match "^[0-9]+:[0-9]+\\(?::[0-9]+\\)?$" form))
form
- (calc-eval (cons form org-tbl-calc-modes) (if numbers 'num)))
+ (calc-eval (cons form org-tbl-calc-modes)
+ (when (and (not keep-empty) numbers) 'num)))
ev (if duration (org-table-time-seconds-to-string
(if (string-match "^[0-9]+:[0-9]+\\(?::[0-9]+\\)?$" ev)
(string-to-number (org-table-time-string-to-seconds ev))
@@ -2851,15 +2853,27 @@ and TABLE is a vector with line types."
"Convert list ELEMENTS to something appropriate to insert into formula.
KEEP-EMPTY indicated to keep empty fields, default is to skip them.
NUMBERS indicates that everything should be converted to numbers.
-LISPP means to return something appropriate for a Lisp list."
- (if (stringp elements) ; just a single val
+LISPP non-nil means to return something appropriate for a Lisp
+list, 'literal is for the format specifier L."
+ ;; Calc nan (not a number) is used for the conversion of the empty
+ ;; field to a reference for several reasons: (i) It is accepted in a
+ ;; Calc formula (e. g. "" or "()" would result in a Calc error).
+ ;; (ii) In a single field (not in range) it can be distinguished
+ ;; from "(nan)" which is the reference made from a single field
+ ;; containing "nan".
+ (if (stringp elements)
+ ;; field reference
(if lispp
(if (eq lispp 'literal)
elements
(prin1-to-string (if numbers (string-to-number elements) elements)))
- (if (equal elements "") (setq elements "0"))
- (if numbers (setq elements (number-to-string (string-to-number elements))))
- (concat "(" elements ")"))
+ (if (string-match "\\S-" elements)
+ (progn
+ (when numbers (setq elements (number-to-string
+ (string-to-number elements))))
+ (concat "(" elements ")"))
+ (if (or (not keep-empty) numbers) "(0)" "nan")))
+ ;; range reference
(unless keep-empty
(setq elements
(delq nil
@@ -2879,7 +2893,7 @@ LISPP means to return something appropriate for a Lisp list."
(if numbers
(number-to-string (string-to-number x))
x)
- (if (or (not keep-empty) numbers) "0" "")))
+ (if (or (not keep-empty) numbers) "0" "nan")))
elements
",") "]"))))
diff --git a/testing/lisp/test-org-table.el b/testing/lisp/test-org-table.el
index 6133005..4c09239 100644
--- a/testing/lisp/test-org-table.el
+++ b/testing/lisp/test-org-table.el
@@ -254,10 +254,10 @@ reference (with row). Format specifier E."
(org-test-table-target-expect
references/target-normal
"
-| 0 | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
-| z | 1 | z | z + 1 | z + 1 | z + 1 | 2 | 2 |
-| | 1 | 0 | 1 | #ERROR | #ERROR | #ERROR | #ERROR |
-| | | 0 | 0 | #ERROR | #ERROR | #ERROR | #ERROR |
+| 0 | 1 | 0 | 1 | 1 | 1 | 2 | 2 |
+| z | 1 | z | z + 1 | z + 1 | z + 1 | 2 | 2 |
+| | 1 | nan | nan | nan | nan | 2 | 2 |
+| | | nan | nan | nan | nan | 2 | 2 |
"
1 calc)
(org-test-table-target-expect
@@ -398,24 +398,26 @@ reference (with row). Format specifier N."
"Basic: Compare field references in Calc."
(org-test-table-target-expect
"
-| | 0 | z | nan | uinf | -inf | inf |
-|------+------+------+------+------+------+------|
-| 0 | repl | repl | repl | repl | repl | repl |
-| z | repl | repl | repl | repl | repl | repl |
-| nan | repl | repl | repl | repl | repl | repl |
-| uinf | repl | repl | repl | repl | repl | repl |
-| -inf | repl | repl | repl | repl | repl | repl |
-| inf | repl | repl | repl | repl | repl | repl |
+| | 0 | z | | nan | uinf | -inf | inf |
+|------+------+------+------+------+------+------+------|
+| 0 | repl | repl | repl | repl | repl | repl | repl |
+| z | repl | repl | repl | repl | repl | repl | repl |
+| | repl | repl | repl | repl | repl | repl | repl |
+| nan | repl | repl | repl | repl | repl | repl | repl |
+| uinf | repl | repl | repl | repl | repl | repl | repl |
+| -inf | repl | repl | repl | repl | repl | repl | repl |
+| inf | repl | repl | repl | repl | repl | repl | repl |
"
"
-| | 0 | z | nan | uinf | -inf | inf |
-|------+---+---+-----+------+------+-----|
-| 0 | x | | | | | |
-| z | | x | | | | |
-| nan | | | x | | | |
-| uinf | | | | x | | |
-| -inf | | | | | x | |
-| inf | | | | | | x |
+| | 0 | z | | nan | uinf | -inf | inf |
+|------+---+---+---+-----+------+------+-----|
+| 0 | x | | | | | | |
+| z | | x | | | | | |
+| | | | x | | | | |
+| nan | | | | x | | | |
+| uinf | | | | | x | | |
+| -inf | | | | | | x | |
+| inf | | | | | | | x |
"
1
;; Compare field reference ($1) with field reference (@1)
@@ -424,10 +426,11 @@ reference (with row). Format specifier N."
(concat "#+TBLFM: "
"$2 = if(\"$1\" = \"(0)\" , x, string(\"\")); E :: "
"$3 = if(\"$1\" = \"(z)\" , x, string(\"\")); E :: "
- "$4 = if(\"$1\" = \"(nan)\" , x, string(\"\")); E :: "
- "$5 = if(\"$1\" = \"(uinf)\", x, string(\"\")); E :: "
- "$6 = if(\"$1\" = \"(-inf)\", x, string(\"\")); E :: "
- "$7 = if(\"$1\" = \"(inf)\" , x, string(\"\")); E"))
+ "$4 = if(\"$1\" = \"nan\" , x, string(\"\")); E :: "
+ "$5 = if(\"$1\" = \"(nan)\" , x, string(\"\")); E :: "
+ "$6 = if(\"$1\" = \"(uinf)\", x, string(\"\")); E :: "
+ "$7 = if(\"$1\" = \"(-inf)\", x, string(\"\")); E :: "
+ "$8 = if(\"$1\" = \"(inf)\" , x, string(\"\")); E"))
;; Check field reference converted from an empty field: Despite this
;; field reference will not end up in a result, Calc evaluates it.
@@ -436,11 +439,13 @@ reference (with row). Format specifier N."
"
| 0 | replace |
| z | replace |
+| | replace |
| nan | replace |
"
"
| 0 | 1 |
| z | z + 1 |
+| | |
| nan | nan |
"
1 "#+TBLFM: $2 = if(\"$1\" = \"nan\", string(\"\"), $1 + 1); E"))
@@ -463,7 +468,119 @@ reference (with row). Format specifier N."
"$5 = '(/ (+ $1..$4 ) (length '( $1..$4 ))); N :: "
"$6 = '(/ (+ @0$1..@0$4) (length '(@0$1..@0$4))); N :: "
"$7 = '(/ (+ $1..$4 ) (length '( $1..$4 ))); EN :: "
- "$8 = '(/ (+ @0$1..@0$4) (length '(@0$1..@0$4))); EN")))
+ "$8 = '(/ (+ @0$1..@0$4) (length '(@0$1..@0$4))); EN"))
+
+ ;; Test if one field is empty, else do a calculation
+ (org-test-table-target-expect
+ "
+| -1 | replace |
+| 0 | replace |
+| | replace |
+"
+ "
+| -1 | 0 |
+| 0 | 1 |
+| | |
+"
+ 1
+ ;; Calc formula
+ "#+TBLFM: $2 = if(\"$1\" = \"nan\", string(\"\"), $1 + 1); E"
+ ;; Lisp formula
+ "#+TBLFM: $2 = '(if (eq \"$1\" \"\") \"\" (1+ $1)); L")
+
+ ;; Test if several fields are empty, else do a calculation
+ (org-test-table-target-expect
+ "
+| 1 | 2 | replace |
+| 4 | | replace |
+| | 8 | replace |
+| | | replace |
+"
+ "
+| 1 | 2 | 3 |
+| 4 | | |
+| | 8 | |
+| | | |
+"
+ 1
+ ;; Calc formula
+ (concat "#+TBLFM: $3 = if(\"$1\" = \"nan\" || \"$2\" = \"nan\", "
+ "string(\"\"), $1 + $2); E")
+ ;; Lisp formula
+ (concat "#+TBLFM: $3 = '(if (or (eq \"$1\" \"\") (eq \"$2\" \"\")) "
+ "\"\" (+ $1 $2)); L"))
+
+ ;; $2: Use $1 + 0.5 if $1 available, else only reformat $2 if $2 available
+ (org-test-table-target-expect
+ "
+| 1.5 | 0 |
+| 3.5 | |
+| | 5 |
+| | |
+"
+ "
+| 1.5 | 2.0 |
+| 3.5 | 4.0 |
+| | 5.0 |
+| | |
+"
+ 1
+ ;; Calc formula
+ (concat "#+TBLFM: $2 = if(\"$1\" = \"nan\", "
+ "if(\"$2\" = \"nan\", string(\"\"), $2 +.0), $1 + 0.5); E f-1")
+ ;; Lisp formula not implemented yet
+ ))
+
+(ert-deftest test-org-table/copy-field ()
+ "Experiments on how to copy one field into another field."
+ (let ((target
+ "
+| 0 | replace |
+| a b | replace |
+| c d | replace |
+| | replace |
+| 2012-12 | replace |
+| [2012-12-31 Mon] | replace |
+"))
+ ;; Lisp formula to copy literally
+ (org-test-table-target-expect
+ target
+ "
+| 0 | 0 |
+| a b | a b |
+| c d | c d |
+| | |
+| 2012-12 | 2012-12 |
+| [2012-12-31 Mon] | [2012-12-31 Mon] |
+"
+ 1 "#+TBLFM: $2 = '(identity $1)")
+
+ ;; Calc formula to copy quite literally
+ (org-test-table-target-expect
+ target
+ "
+| 0 | 0 |
+| a b | a b |
+| c d | c d |
+| | |
+| 2012-12 | 2012-12 |
+| [2012-12-31 Mon] | <2012-12-31 Mon> |
+"
+ 1 (concat "#+TBLFM: $2 = if(\"$1\" = \"nan\", "
+ "string(\"\"), string(subvec(\"$1\", 2, vlen(\"$1\")))); E"))
+
+ ;; Calc formula simple
+ (org-test-table-target-expect
+ target
+ "
+| 0 | 0 |
+| a b | a b |
+| c d | c d |
+| | |
+| 2012-12 | 2000 |
+| [2012-12-31 Mon] | <2012-12-31 Mon> |
+"
+ 1 "#+TBLFM: $2 = if(\"$1\" = \"nan\", string(\"\"), $1); E")))
;; End of table examples and beginning of internal tests.
@@ -491,11 +608,11 @@ reference (with row). Format specifier N."
;; For Calc formula
(should (equal "(0)" (f "0" t nil nil)))
(should (equal "(z)" (f "z" t nil nil)))
- (should (equal "(0)" (f "" t nil nil)))
+ (should (equal "nan" (f "" t nil nil)))
(should (equal "[0,1]" (f '("0" "1") t nil nil)))
(should (equal "[z,1]" (f '("z" "1") t nil nil)))
- (should (equal "[,1]" (f '("" "1") t nil nil)))
- (should (equal "[,]" (f '("" "" ) t nil nil)))
+ (should (equal "[nan,1]" (f '("" "1") t nil nil)))
+ (should (equal "[nan,nan]" (f '("" "" ) t nil nil)))
;; For Calc formula, special numbers
(should (equal "(nan)" (f "nan" t nil nil)))
(should (equal "(uinf)" (f "uinf" t nil nil)))
--
1.7.1
next prev parent reply other threads:[~2012-12-28 14:10 UTC|newest]
Thread overview: 9+ messages / expand[flat|nested] mbox.gz Atom feed top
2012-12-10 22:32 Distinguish between blank and zero in org-mode spreadsheet Bob Newell
2012-12-10 22:57 ` Thorsten Jolitz
2012-12-11 4:50 ` Bob Newell
2012-12-11 6:10 ` Nick Dokos
2012-12-11 15:14 ` Michael Brand
2012-12-15 9:15 ` Michael Brand
2012-12-15 9:23 ` Bastien
2012-12-28 14:10 ` Michael Brand [this message]
2012-12-29 13:39 ` Bastien
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
List information: https://www.orgmode.org/
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to='CALn3zois8R3YL3B6dA8n25kj_HvFnqrrRxGRRaTTrrK4C8=V5w@mail.gmail.com' \
--to=michael.ch.brand@gmail.com \
--cc=bobnewell@bobnewell.net \
--cc=bzg@altern.org \
--cc=emacs-orgmode@gnu.org \
--cc=nicholas.dokos@hp.com \
--cc=tjolitz@googlemail.com \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
Code repositories for project(s) associated with this public inbox
https://git.savannah.gnu.org/cgit/emacs/org-mode.git
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).