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
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.
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)
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.
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
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.
Org Extended offers the ability to clear the text from a cell as a means of helping improve your editing experience.
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:
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.
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*$piSometimes, 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_propnameThe $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 documentIn 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
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 | |
- 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
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
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.
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'] = 299792458We 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.
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())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]))
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 |
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
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.
