# Can LaTeX perform calculation like Excel formula table?

I wonder if this feature is available in TeX system? For example, given a formula within a table, it can apply the formula and display data after the calculation.

-
That depends on the complexity of the formula. Reasonably simple things can be done with pgfplotstable, but things become quite tedious relatively quickly. Could you provide an example of the kind of things you'd want to do? –  Jake Sep 9 '12 at 20:25
There's the package spreadtab which looks quite promising on this respect. –  egreg Sep 9 '12 at 20:28
Great question, and great answer. Just as an aside, there's OpenOffice which is free and open source :) –  cmhughes Sep 10 '12 at 0:06
I second @cmhughes' suggestion: It's great that this is possible with TeX, but you should install OpenOffice so you can have a real choice. Just because TeX can do something doesn't mean it's the easiest way to do it. (Also with OpenOffice you can generate a spreadsheet that your prof can read with Excel). –  alexis Sep 10 '12 at 8:39

There is the package spreadtab which provides spreadsheet like features. These examples are taken from the documentation:

\documentclass{article}
\begin{document}

22       & 54       & a1+b1 \\
43       & 65       & a2+b2 \\
49       & 37       & a3+b3 \\ \hline
a1+a2+a3 & b1+b2+b3 & a4+b4

@ values of $x$ & -5       &        -1 &        4 \\
@ $f(x)=2x$     & 2*[0,-1] &  2*[0,-1] & 2*[0,-1] \\\hline

@$\times$        & 1                     & \STcopy{>}{b1+1}  & & & & & & & & \\\hline
1                & \STcopy{>,v}{!a2*b!1} &                   & & & & & & & & \\
\STcopy{v}{a2+1} &                       &                   & & & & & & & & \\
&                       &                   & & & & & & & & \\
&                       &                   & & & & & & & & \\
&                       &                   & & & & & & & & \\
&                       &                   & & & & & & & & \\
&                       &                   & & & & & & & & \\
&                       &                   & & & & & & & & \\
&                       &                   & & & & & & & & \\
&                       &                   & & & & & & & & \\\hline

\end{document}


-
Very nice! Thanks a lot. –  Chan Sep 9 '12 at 21:09

Here's a simple example of using R, knitr and xtable to produce a LaTeX table.

Assume you have the following document called test.rnw

\documentclass{article}
\begin{document}

<<results='asis', echo=FALSE>>=
library(xtable)
sheet <- data.frame(x=c(1,2,3,4,5), y=c(10, 20, 30, 40, 50))
sheet$z <- sheet$x + sheet\$y
print(xtable(sheet))
@
\end{document}


In short, the code between <<>>= and @ in the rnw file is replaced by the latex output generated in a new tex file. In particular, xtable is a package and function that can convert a data frame into a latex table. It has a large number of options, but in the above example I've relied on the defaults.

To convert the file into latex and then to pdf you could just open the rnw file in the open source R IDE called RStudio and click "Compile PDF". Or you could issue commands like this from the command line.

Rscript -e 'library(knitr); knit("test.rnw")'
pdflatex test.tex


It would produce output like this:

To run the above example, you would need to install:

• R: http://www.r-project.org/
• knitr: install.packages('knitr') in R
• xtable: install.packages('xtable') in R

-

If you use LuaTeX you can extend TeX with any C math library and create Lua bindings.

To create the necessary bindings the program SWIG can be used. Here is a ConTeXt example which uses the PARI/GP algebra system to do the calculations.

Generate the binding

file:pari.i

%module pari
%{
#include "pari.h"
ulong overflow;
%}

%ignore gp_variable(char *s);
%ignore setseriesprecision(long n);
%ignore killfile(pariFILE *f);
%ignore pari_vfprintf(FILE *file, const char *fmt, va_list ap);
%ignore pari_vprintf(const char *fmt, va_list ap);
%ignore pari_vsprintf(const char *fmt, va_list ap);
%ignore out_vprintf(PariOUT *out, const char *fmt, va_list ap);

%include "pari/parisys.h";
%include "pari/paristio.h";
%include "pari/paricom.h";
%include "pari/parigen.h";
%include "pari/paridecl.h";
%include "pari/parierr.h";
%include "pari/paricast.h";
%include "pari/paritune.h";
%include "pari/pariinl.h";

%inline %{
GEN uti_mael2(GEN m,long x1,long x2)
{return mael2(m,x1,x2);}
%}


Generate the binding with

swig -lua -I/usr/include pari.i


then compile the module

gcc -ansi -I/usr/include/pari -I/usr/include/lua5.1 \
-fPIC -c pari_wrap.c -o pari_wrap.o
gcc -Wall -ansi -shared -I/usr/include/pari \
pari_wrap.o -lpari -lm -o pari.so


Now the module is available in LuaTeX with require("pari").

Using the library

\startluacode
require("pari")
pari.pari_init(4000000,500000)
thirddata = thirddata or {}
local function sum(X,a,b,expr,start)
local start = start or '0.'
"sum(%s=%s,%s,%s,%s)",X,a,b,expr,start))
res = pari.GENtoTeXstr(res)
return res
end
thirddata.sum = sum
\stopluacode

\starttext

\startformula
\sum_{k=0}^{30}\frac{4(-1)^k}{2k+1}=
\luacode{context(thirddata.sum(
"k",0,30,"4*(-1)^k/(2*k+1)","0"))}
\stopformula

\startformula
\sum_{k=0}^{3}\frac{1}{x^2+k}=
\luacode{context(thirddata.sum(
"k",0,3,"1/(x^2+k)","0"))}
\stopformula

\stoptext


The result of this code:

Since this approach does not make use of any ConTeXt features this code should work in LaTeX as well with only minor modifications. However, it only works with LuaTeX.

This description is based on a talk by Luigi Scarso at BachoTeX 2011. All credits go to him.

-