Can LaTeX perform calculation like Excel formula table?

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

\documentclass{article}
\usepackage{spreadtab}
\begin{document}

\begin{spreadtab}{{tabular}{rr|r}}
 22       & 54       & a1+b1 \\
 43       & 65       & a2+b2 \\
 49       & 37       & a3+b3 \\
 \hline
 a1+a2+a3 & b1+b2+b3 & a4+b4
\end{spreadtab}

\begin{spreadtab}{{tabular}{|r|ccc|}}
 \hline
 @ values of $x$ & -5       &        -1 &        4 \\
 @ $f(x)=2x$     & 2*[0,-1] &  2*[0,-1] & 2*[0,-1] \\
 \hline
\end{spreadtab}

\begin{spreadtab}{{tabular}{|c|*{10}{c}|}}
 \hline
 @$\times$        & 1                     & \STcopy{>}{b1+1}  & & & & & & & & \\
 \hline
 1                & \STcopy{>,v}{!a2*b!1} &                   & & & & & & & & \\
 \STcopy{v}{a2+1} &                       &                   & & & & & & & & \\
                  &                       &                   & & & & & & & & \\
                  &                       &                   & & & & & & & & \\
                  &                       &                   & & & & & & & & \\
                  &                       &                   & & & & & & & & \\
                  &                       &                   & & & & & & & & \\
                  &                       &                   & & & & & & & & \\
                  &                       &                   & & & & & & & & \\
                  &                       &                   & & & & & & & & \\
 \hline
\end{spreadtab}

\end{document}

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

For more information check out the knitr website: http://yihui.name/knitr/

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.'
    local res = pari.gp_read_str(string.format(
      "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.