Tables and Spreadsheets

Org Extended has some support for org mode tables. True org tables support would allow for arbitrary emacs lisp to run as cell formulas within the table. Org Extended is not likely to build a lisp interpreter within sublime. I am less likely to support the full gamut of useful lisp functions.

That said, a lot can be done with basic calc support in sublime!

There are a number of things that make org tables super powerful:

Org has a ton of power for editing and dynamically modifying tables, in plain text!
Org supports a full computation engine built on emacs calc. We have a simplified version of that.
Org tables can reference other tables. (Much like tabs in a spreadsheet)
Org tables can act as inputs to babel really opening up your options for living documents. (we do not have this yet)
Org tables can be the output of babel, again, really comming full circle on things. (we do not have this yet)
Org can easily import csv files and other document types into your documents. (we have some support here)

That said, using the spreadsheet like capabilities of Org Extended will take a little effort on your part

Basic Editing

Tab and Shift Tab are your friend. To start building a table, put a vertical bar symbol followed by some text and press tab. Voila, you have a table!

There is more. You can import a table or create a blank WxH table. But we will cover those later.

Adding Rows and Columns

Next we need to extend our table. It is a pain to have to go over to the righthand side of the table to add a column. Org lets you insert a column almost anywhere!

Shift+Alt+Right Arrow  --> Add a Col (pull it out, text grows to the right in english)
Shift+Alt+Left Arrow   --> Remove a Col (push it back)

The same works vertically!

Shift+Alt+Down Arrow --> Add a row (pull it out, tables grow down right!)
Shift+Alt+Up   Arrow --> Remove a row (push it back)

Moving Cells Around

So now you realize that you laid out your table poorly and want to move the content around.

Alt+Right Arrow  --> Move this row right
Alt+Left Arrow   --> Move this row left

The same works vertically!

Alt+Down Arrow --> Move this row down
Alt+Up   Arrow --> Move this row up

The cool thing you will see later is that org tries to preserve formula correctness as well when you move your cells around. But more on that in a sec.

Adding a Formula

Okay so now you have a table with some content that you hand authored. It’s time to start adding some smarts to your table!

First a quick demonstration. Org, like a spreadsheet can take a formula in a cell. Here is an example:

=vmean($2..$3)

To regisster the formula you have to tell org about it, otherwise the formula is just text. Org stores the formula in a TBLFM comment at the bottom of the table. No exceptions that comment needs to be at the bottom of the table.

In this case we added a column formula. You can add a targetted formula by using:

:= - single cell formula
=  - column formula
>= - row formula

You will notice org added some funny symbols to reference the target cell. We also used what looks like a range of cells. Org has a whole gamut of these, we support a subset.

Org use a row and column notation that has the following structure:

@ <ROW> $ <COL>

Some examples might include:

‘@2$3’

2nd row, 3rd column

‘$5’: column 5 in the current row

‘@2’

current column, row 2

‘@-1$-3’

field one row up, three columns to the left

‘@>$5’

field in the last row, in column 5

Range syntax is also supported:

‘$1..$3’: first three fields in the current row
‘$<<<..$>>’: start in third column, continue to the last but one
‘@2$1..@4$3’: nine fields between these two fields
‘@-1$-2..@-1’: 3 fields in the row above, starting from 2 columns on the left

OrgMode Table References Table Range References

To help with figuring out the indexes of your rows and columns Org Extended has a set of phantoms it can pop up to show you the layout of your table:

Org Show Table Rows - Will show the layout of your table
Org Hide Table Rows - Will hide the phantoms. These can be a little distracting so it helps to be able to turn them off

Evaluating Your Formula

Once you have a few formulas you might want to re-evaluate all your formulas. You do this with the org Do What I Mean execute method:

Alt + o + c + c 
OR
<space> c c

This will re-evaluate all references in the table.

Clear a Cell

Org Extended offers the ability to clear the text from a cell as a means of helping improve your editing experience.

Editing By Formula

Sometimes you just need to adjust the values of various cells by a constant. OrgExtended offers a means of running a temporary expression over a column of cells below the cursor:

Re-Editing a Formula

When you have written a formula, sometimes you need to tweak it. The Org Edit Table Formula will recall the formula attached to the current cell. Note: the target expression currently needs to be changed back to reflect a newly created formula.

Symbols and Constants

Sometimes when doing a calculation it is a bit of a pain to work with a gamut of numbers and it helps to be able to assign a meaningful name. The CONSTANTS comment allows you to created named symbols that can be referenced from your tables:

#+CONSTANTS: pi=3.1415926 c=299792458

Note the lack of spaces and equal signs. These can now be referenced as symbols in your expressions:

=$1*$pi

Sometimes, when planning or doing other operations you have stored some valuable data as properties in your document. You can access that data using property symbols:

=$1*$PROP_propname

The $PROP_ prefix is added by the system to all properties in your document.

In addition, there are two special properties that can be used:

$# - Current column index of the document
@# - Current row index of the document

Formatting Output

In Emacs org mode formulas run inside calc. Calc has quite a few modes and control systems. For this reason you can append a semi colon to your formula and add control flags that control how calc operates when evaluating your expression. We do not have this situation. Our parser is OrgExtended specific and as such most of the control flags for calc are meaningless. That said, there are a couple of flags that are pretty useful:

N - Adding this flag to a formula will treat all empty cells as a 0 value.
%.#f - This lets you control the precision of floating point output. It is often visually disturbing to have many decimal places in your output. With this you can limit the output to a smaller set of decimal places.

Here we are limiting our output to 3 decimal places:

| a     | b     | c   | d     | e     |
| 1     | 1     |     | 1     | 1     |
| 0.476 | 0.476 | 0.0 | 0.476 | 0.476 |
#+TBLFM:@3=@2/2.1;N%.3f

Remote References

Tables become even more useful when you can reference between tables! The remote function lets you do just that.

=remote('TableName',@2$3)

#+NAME: TableName
| a | b | c | d |
| 1 | 2 | 3 | 4 |

Another table somewhere:
| a | b | c                             | d |
|   |   | >=remote('TableName',@2$3)*10 |   |

Functions

abs: Convert value to a positive value
acos: Return the arc cosine of x radians.
acosh: Return the inverse hyperbolic cosine of x.
asin: Return the arc sine of x radians.
asinh: Return the inverse hyperbolic sine of x.
atan: Return the arc tangent of x radians.
atanh: Return the inverse hyperbolic tangent of x.
bool: Explicitly convert value to a boolean value if possible
ceil: Force a value to the next integer
cos: Return the cosine of x radians.
cosh: Return the hyperbolic cosine of x.
date: Convert string to a date value
day: Get the day value from a datetime - datetime.time().day
degrees: Convert from radians to degress
duration: Convert to a timespan value
exp: Return e raised to the power x, where e = 2.718281
float: Force a value to a float
floor: Force a value to the previous integer
highlight: highlight(cell,color,text) highlights a cell to one of: green,red,orange,white,black,purple,yellow,cyan and returns the text specified
hour: Get the hours value from a datetime - datetime.time().hour
int: Force a value to an integer
log: Return the natural logarithm of x (to base e).
log10: Return the base-10 logarithm of x.
log2: Return the base-2 logarithm of x.
minute: Get the minutes value from a datetime - datetime.time().minute
month: Get the month value from a datetime - datetime.time().month
now: Returns the current date time
nowstr: Not Yet Documented
passed: Not Yet Documented
pow: Return x raised to the power y
radians: Convert from degrees to radians
rand: random() -> x in the interval [0, 1).
randint: return a random int below <top>
random: Returns a random value in a range specified start..end
randomf: Returns a random value from 0..1
remote: remote(‘table-name OR custom-id-value’,cellRef) returns a cell from a remote table. table-name only works local to a file while custom-id or id will look up the first table in a heading marked with that id.
round: Round to the nearest integer
second: Get the seconds value from a datetime - datetime.time().second
sin: Return the sine of x radians.
sinh: Return the hyperbolic sine of x.
sqrt: Return the square root of x.
str: str(object=”) -> str str(bytes_or_buffer[, encoding[, errors]]) -> str
Create a new string object from the given object. If encoding or errors is specified, then the object must expose a data buffer that will be decoded using the given encoding and error handler. Otherwise, returns the result of object.__str__() (if defined) or repr(object). encoding defaults to sys.getdefaultencoding(). errors defaults to ‘strict’.
tan: Return the tangent of x radians.
tanh: Return the hyperbolic tangent of x.
time: Return the current time from a datetime object time(datetime)
trunc: Round down to the nearest int
vmax: Computes the max value of a range of cells
vmean: Computes the average value of a column or row. Takes a range of cells
vmedian: Computes the median (middle) value of a sorted range of cells
vmin: Computes the minimum value of a range of cells
vsum: Computes the sum of a range of cells
weekday: Get an index for the day of the week where monday is 0
year: Get the year value from a datetime - datetime.time().year
yearday: Get the numerical day of the year where jan 1 is 1

Built in Symbols

The following symbols are available for use in your tables (at the moment)

nil: None
pi: 3.1415926
False: False
True: True
t: True
true: True
false: False
None: None

Extending Org Tables With Your Own Functions

While we will extend the list of functions and symbols available over time, it is really handy to be able to add your own methods to tables. In Emacs you can use any lisp function you define dynamically OR src blocks in your org file itself as functions in your tables. We are working on the babel support, but that is not yet available AND arbitrary lisp will never be an option for us.

That said, we have a means for you to extend the list of functions and symbols yourself. We can dynamically load python code put in a specific location. To use this you MUST turn on table extension support in your settings file:

"enableTableExtensions": true,

Once enabled OrgExtended will search your:

Packages/User/orgtable/

folder for pythong file. Any file placed in this folder will be a candidate for table extension.

Symbols

You can add symbols from any python file in your orgtable folder, even a python file that adds a function. (See below) Add an AddSymbols(symbolTable) function to your file and define your symbols as values in that dictionary. Here I am defining the symbol c for use in my tables.

def AddSymbols(s):
  s['c'] = 299792458

We have a very limited set of symbols by default. That said, it is easy to add your own and we have an example from the emacs constants.el that you can easily drop in that will give you a great place to start.

mathconstants.org

Functions

For functions the name of your file becomes the name of your function in the table. You place a single function: Execute in your python file. This function will be called with the parameters of your function when called through a TBLFM.

def Execute():
  import sublime
  import datetime
  return str(datetime.datetime.now())

Exporting a Table

Because of babel tables act like a data source. Because of this it is super easy to convert a table to almost any format you require. Here is a simple example of outputting a table as a simple csv file.

for r in DATA:
  print(",".join([str(x) for x in r]))

Advanced Table Features

Orgmode supports an advanced table mechanism. When using these features you need to put symbols in the first column of the table. These symbols are as follows.

OrgMode Advanced Table Features

#: Auto compute this row. When you use the table commands to move between cells these cells will be auto computed.
*: Non auto compute cell. Cells in this row will be included when recomputing the table (blank symbol will exclude a row from computation)
$: defines local table symbols in this row (max=5) that can be used in formulas.
_: name the cells below this row in this column. Cells can be referenced by name.
^: name the cells above this row in this column. Cells can be referenced by name.
!: name all cells in this column. Cells can be referenced by this name.

	a	b	c
#	0.38	0.1	0.46
#	0.38	0.1	0.86
#	0.03	0.6	0.01
*	0.02	0.0	0.06
^	hello	world	namedRow
*		0.3

_	below
#	3.5	0.7
#	4.5	0.9
#	4.0	0.8
#	2.0	0.4
$	max=5

GNU Plot

No spreadsheet application would be complete without the ability to graph data. Emacs, and now we make that possible with GNU Plot.

To use it:

install gnuplot

Set your gnuplot path:

"gnuplot": "<fullpathtognuplot.exe>",

Run “Org Plot Table” with cursor on the table

#+PLOT: title:"Citas" ind:1 deps:(3 4) with:lines set:grid file:plot.png
|    Sede   |  Max   | H-index |  top  |
|-----------+--------+---------+-------|
| Sao Paolo |  71.00 |   11.50 |  13.5 |
| Stockholm | 134.19 |   14.33 | 16.33 |
| Leeds     | 165.77 |   19.68 | 21.68 |
| Morelia   | 257.56 |   17.67 | 19.67 |
| Chile     | 257.72 |   21.39 | 23.39 |
#+TBLFM:$4=$3+2.0

Testing

We are trying to stay on top of the need to test the gamut of potential issues in this thing. If you find a bug, please report it.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

tables.org

tables.org

Tables and Spreadsheets

Basic Editing

Adding Rows and Columns

Moving Cells Around

Adding a Formula

Evaluating Your Formula

Clear a Cell

Editing By Formula

Re-Editing a Formula

Symbols and Constants

Formatting Output

Remote References

Functions

Built in Symbols

Extending Org Tables With Your Own Functions

Symbols

Functions

Exporting a Table

Advanced Table Features

GNU Plot

Testing

Files

tables.org

Latest commit

History

tables.org

File metadata and controls

Tables and Spreadsheets

Basic Editing

Adding Rows and Columns

Moving Cells Around

Adding a Formula

Evaluating Your Formula

Clear a Cell

Editing By Formula

Re-Editing a Formula

Symbols and Constants

Formatting Output

Remote References

Functions

Built in Symbols

Extending Org Tables With Your Own Functions

Symbols

Functions

Exporting a Table

Advanced Table Features

GNU Plot

Testing