notes from /dev/null

by Charles Choi 최민수

Referencing Org Table Cells with Text Regions

08 Oct 2024  Charles Choi

In a typical spreadsheet app, a common user interface practice is to avoid forcing the user to keep track of cell references as much as possible. This is usually seen in selecting a range of cells to construct a formula, where the user only needs to control the range of selection; the app keeps track of which cell references to pass into the formula.

Sadly, this feature is not available by default in Org tables. But no need to fret; Emacs and Org give you the mechanisms to build a reasonable emulation of spreadsheet-style cell range selection. This post shows you how.

Before going into details, here’s a demo of selecting a row of values and using that selection to compute its sum.

| a | b | c | Total |
|---+---+---+-------|
| 1 | 5 | 2 |       |
| 3 | 4 | 1 |     8 |
| 8 | 1 | 2 |       |
#+TBLFM: $4=vsum(@3$1..@3$3)

In the example above the following steps are taken:

  1. Place the point in the cell to enter a Calc formula, in this case vsum().
    • Note that vsum here stands for vector sum.
  2. Select a range of cells, typically with a mouse by pointing and dragging.
    • The selection can be either be a region or a region-rectangle.
      • Did you know that C-M-mouse-1 lets you mouse select a rectangle? I didn't until recently 🤯.
  3. Raise a context menu pressing the right mouse button over the selected region.
  4. In the context menu, a menu item showing the selected cell region is displayed (in this case @3$1..@3$3).
    1. Select this menu item to copy the cell references into the kill-ring.
  5. Move the point back to the initial cell in step 1 and yank the cell reference as an argument for vsum().
  6. Press TAB or RET to compute the formula.

Some observations:

  • Throughout this workflow, the user did not need to keep track of cell references.
  • The resultant table formula #+TBLFM: left-hand-side (lhs) is populated with only the column after step 6.
    • The user must amend the lhs to be @3$4 to specify that the formula should only apply to that cell.
  • The table formula can be modified to use relative references at the cost of requiring the user to truly understand Org table reference syntax.
  • Keyboard enthusiasts can use the command cc/copy-org-table-reference-dwim to convert a text region into an Org table reference.

Intrigued? Okay, let’s do this.

Org Table Reference Background

First off, let’s understand how Org tables address cells. A table cell is addressed using the following representation:

@ROW$COLUMN

A row is prefixed with @ whereas a column is prefixed with $. The row specification is placed before the column specification which is opposite of what's seen in conventional spreadsheets and Cartesian coordinates that specify the column first, then the row. Anyways, water under the bridge. With Org tables, it is always row first, column second.

A reference to an individual cell is called a field reference.

A reference to a set of cells is called a range reference. A range reference is constructed from two field references, where a and c are row values, b and d are column values.

@a$b..@c$d

Range references are translated into a vector of values that can be fed into Calc vector functions. Typically the two field references in a range reference are either in the same row or column. If not, then the vector of values is constructed using the two field references as a bounding box.

Most importantly, a range reference specifies values in a single vector. The orientation (row or column) of a range reference matters. To clarify, this is best illustrated by example.

#+NAME: vector-example
| a | b | c |
|---+---+---|
| 1 | 2 | 3 |
| 4 | 5 | 6 |
| 7 | 8 | 9 |

| Range      | Vector       | Notes                                                  |
|------------+--------------+--------------------------------------------------------|
| @2$1..@2$3 | [1, 2, 3]    | Cell values taken left to right.                       |
| @2$2..@4$2 | [2, 5, 8]    | Cell values "sliced" wrt column specification.         |
| @3$2..@4$3 | [5, 6, 8, 9] | Cell values taken using the extents as a bounding box. |
#+TBLFM: @2$2=remote(vector-example, @2$1..@2$3)
#+TBLFM: @3$2=remote(vector-example, @2$2..@4$2)
#+TBLFM: @4$2=remote(vector-example, @3$2..@4$3)

Design Requirements

With the background out of the way, let’s put down some requirements.

  1. Given an active region within an Org table, translate it to a range reference with the following specifics:

    1. If the selected region is a single column or row, return a range reference using the mark and point of the region.
    2. If the selected region crosses has multiple rows and columns, then treat the mark and point of the region as the bounding box specification for a matrix represented as a Calc vector of vectors.
    3. If the selected region is a in a single cell, only return the field reference for that cell.
    4. The bounding box calculation is the same regardless if the mark is before or after the point.

  2. Support both a mouse menu and mini-buffer command interface for obtaining an Org table region reference from a selected text region.

As a table is a convenient representation of a matrix, the above requirements makes the design decision to reflect that in mapping a text region having multiple rows and columns to a vector of vectors instead of a single vector.

Show Code

Here’s code that meets the requirements described above. Commands of note:

  1. cc/copy-org-table-reference-dwim
  2. cc/mouse-copy-org-table-reference-dwim

Given a point or region (text or rectangle) in an Org table, either of the two commands will create an Org table reference and push it in the kill-ring. The first command is intended to be called from the keyboard, the second from a mouse menu.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
;; Org Table Cell Reference Functions

(defun cc/org-table-cell-at-point ()
  "At point, return the cell object from an Org table.

A cell object is defined to be a list containing the row and the
column, successively."
  (if (not (org-at-table-p))
      (error "Not in a table"))

  (let* ((row (org-table-current-dline))
         (col (org-table-current-column)))
    (list row col)))

(defun cc/format-org-table-field-reference (cell)
  "Format CELL object into @r$c format.

CELL object obtained via `cc/org-table-cell-at-point'.

See Info node `(org) References' for more on Org table field
reference format."
  (let ((row (nth 0 cell))
        (col (nth 1 cell)))
    (format "@%d$%d" row col)))

(defun cc/org-table-range ()
  "Return range object from a region defined within an Org table.

A range object is a list of two cells computed via
`cc/org-table-cell-at-point', the first being the cell at the
start of the region and the last being the cell at the end of the
region."
  (if (not (and (org-at-table-p) (use-region-p)))
      (error "Not in an Org table"))

  (save-excursion
    (let* ((end (cc/org-table-cell-at-point)))
      (exchange-point-and-mark)
      (let ((start (cc/org-table-cell-at-point)))
        (list start end)))))

(defvar cc/last-org-table-reference nil
  "Last stored Org table reference.

State variable to store an Org table reference (field or range)
to be used in an Org table formula. This variable is set via
`cc/org-table-reference-dwim'

NOTE: This state variable to work-around my lack of clarity on
region and mouse menu interaction.")

(defun cc/org-table-reference-dwim ()
  "Org table reference given point or region is defined.

Return Org table reference (field or range) depending on whether
a point or region is defined in an Org table.

If the region is defined over multiple columns, then a Calc
vector matrix is returned. See Info node `(org) Formula syntax
for Calc' for more.

Calling this function will set `cc/last-org-table-reference'.

See Info node `(org) References' for more on Org table field
reference format."
  (if (not (org-at-table-p))
      (error "Not in an Org table"))

  (cond
   ((use-region-p)

    (let* ((range (cc/org-table-range))
           (start (nth 0 range))
           (end (nth 1 range))
           (msg (format "%s..%s"
                        (cc/format-org-table-field-reference start)
                        (cc/format-org-table-field-reference end))))
      (setq cc/last-org-table-reference (cc/org-table-range-to-reference range))
      msg))

   (t
    (let ((msg (cc/format-org-table-field-reference (cc/org-table-cell-at-point))))
      (setq cc/last-org-table-reference msg)
      msg))))

(defun cc/copy-org-table-reference-dwim ()
  "Copy Org table reference (field or range) into kill ring.

Given a point or region defined in an Org table, add to the
`kill-ring' an Org table field or range reference.

If the region is defined over multiple columns, then a Calc
vector matrix is returned. See Info node `(org) Formula syntax
for Calc' for more.

See Info node `(org) References' for more on Org table field
reference format."
  (interactive)
  (if (not (org-at-table-p))
      (error "Not in an Org table"))

  (let ((msg (cc/org-table-reference-dwim)))

    (message "Range: %s, Copied %s" msg cc/last-org-table-reference)
    (kill-new cc/last-org-table-reference)))

(defun cc/mouse-copy-org-table-reference-dwim ()
  "Copy Org table reference (field or range) into kill ring via mouse.

Given a point or region defined in an Org table, add to the
`kill-ring' an Org table field or range reference.

NOTE: This function is intended to be called from a mouse menu
after `cc/copy-org-table-reference-dwim' is called which will set
`cc/last-org-table-reference'. This is to work-around my lack of
clarity on region and mouse menu interaction.

If the region is defined over multiple columns, then a Calc
vector matrix is returned. See Info node `(org) Formula syntax
for Calc' for more."
  (interactive)
  (if (not (org-at-table-p))
      (error "Not in an Org table"))

  (when cc/last-org-table-reference
    (let ((msg cc/last-org-table-reference))
      (message "Copied %s" msg)
      (kill-new msg))))

(defun cc/org-table-range-to-reference (range)
  "Convert RANGE object to Org table reference (field or range).

If the region is defined over multiple columns, then a Calc
vector matrix is returned. See Info node `(org) Formula syntax
for Calc' for more.

See `cc/org-table-range' for more on RANGE object."
  (let* ((start (nth 0 range))
         (end (nth 1 range))
         (a (nth 0 start))
         (b (nth 1 start))
         (c (nth 0 end))
         (d (nth 1 end))

         (r1 (apply #'min (list a c)))
         (c1 (apply #'min (list b d)))

         (r2 (apply #'max (list a c)))
         (c2 (apply #'max (list b d)))

         (rowrange (number-sequence r1 r2))
         (buflist (list)))


    (cond
     ((and (= r1 r2) (= c1 c2))
      (format "@%d$%d" r1 c1 ))

     ((or (= c1 c2) (= r1 r2))
      (format "@%d$%d..@%d$%d" r1 c1 r2 c2))

     (t
      (mapc (lambda (r)
              (push (format "@%d$%d..@%d$%d" r c1 r c2) buflist))
            rowrange)

      (format "vec(%s)"
              (string-join (reverse buflist) ", "))))))

Original Source.

The following abridged code illustrates how one could configure their context menu to show the current Org table reference and copy it into the kill-ring if selected. See Customizing the Emacs Context Menu for more on Emacs context menus.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
(defun cc/context-menu-addon-items (menu click)
  "Context menu hook function using MENU and CLICK event.

MENU - menu to be configured.
CLICK - event"
  (save-excursion
    (mouse-set-point click)
    (cc/context-menu-org-table-items menu (not (org-at-table-p)))
    menu))

(defun cc/context-menu-org-table-items (menu &optional inapt)
  "Menu items to populate MENU for Org table section if INAPT nil."
  (when (not inapt)
    (easy-menu-add-item menu nil
                        ["Table Cell Info"
                         cc/mouse-copy-org-table-reference-dwim
                         :label (cc/org-table-reference-dwim)
                         :help "Copy Org table reference (field or range) into kill ring via mouse"])))

(add-hook 'context-menu-functions #'cc/context-menu-addon-items)

Original Source.

All code running on Emacs 29.4.

Examples

The following table illustrates the result of selecting the text region between the cell with value 2 (@2$2) and the cell with value 12 (@4$4) and inserting its translated Org table reference into different formulas.

The first formula in @2$5 is just the matrix representation between 2 and 12.

The second formula takes the transpose (trn) of the first formula.

The third formula uses some matrix arithmetic to get the sum of each row of the matrix in @2$5.

#+NAME: matrix-example
| a |  b |  c |  d | result                               |
|---+----+----+----+--------------------------------------|
| 1 |  2 |  3 |  4 | [[2, 3, 4], [6, 7, 8], [10, 11, 12]] |
| 5 |  6 |  7 |  8 | [[2, 6, 10], [3, 7, 11], [4, 8, 12]] |
| 9 | 10 | 11 | 12 | [9, 21, 33]                          |
#+TBLFM: @2$5=vec(@2$2..@2$4, @3$2..@3$4, @4$2..@4$4)
#+TBLFM: @3$5=trn(vec(@2$2..@2$4, @3$2..@3$4, @4$2..@4$4))
#+TBLFM: @4$5=@2$5 * [1 1 1]

Closing Thoughts

Manually dealing with Org table references is both tedious and error-prone. Automating their generation using text range selection seems like a good way to make this more bearable. If you're comfortable with Elisp, I recommend you give this code a try.

I’m considering publishing this code as a package on MELPA but I think the utility of it is high enough to merit being in Org itself. For next steps, I look forward to finding out if that is true.

emacs   org mode

 

About • Mastodon • Instagram • GitHub

Feeds & Tags • Get Captee for macOS •

© 2009-2024 Charles Y. Choi

Powered by Pelican