Using external tables in TikZ

Question

How do I use tabela.xls as a data source to a LaTeX table or even a table made with TikZ with the following command? Note: I wanted to use tabela.xls as a data source for multiple files. Tex and that it is synchronized when I change the tabela.xls.

\documentclass[a4paper]{article}
\usepackage{tikz}
\usetikzlibrary{matrix}
\begin{document}
  \begin{tikzpicture}
    \matrix[matrix of nodes,nodes={minimum width=1.5cm}] at (0,0)
      {
    1 & Knuth &         10\\
    2 & Lamport &  10\\
    3 & Mittelbach & 7\\
    4 & Oetiker &   7\\
      };
  \end{tikzpicture}
\end{document}

Answer 1

Sadly, there's no way of a direct use of xls files in LaTeX, more precisely the OLE 2 Compound Document format, which is in binary form.

From 2007 on, Microsoft Excel uses Office Open XML for their new xlsx format, based on XML. But I don't believe it would be easier to parse it from LaTeX. Update: format is zipped, so binary again.

My suggestion is to use an easier format, say, comma-separated values. cvs is a plain text format. Excel can easily export a spreadsheet to csv.

We have the datatool package which handles csv files and might help you to populate your table. I'm not sure how it would work with TikZ.

Joseph also suggested me that pdfplotstable might help with displaying the data.

If you do want to stick with xls files, Werner's suggestion is the way to go.

Hope it helps. :-)

Update: Here's an example with a plain and simple table:

When saving it, I can choose the csv format:

Which will give me the following plain text (scores.csv):

ID,Name,Score
1,Knuth,10
2,Lamport,10
3,Mittelbach,7
4,Oetiker,7

Now, in LaTeX with datatool:

ex1.tex

\documentclass[a4paper]{article}

\usepackage{datatool}

\begin{document}

\DTLloaddb[keys={ID,Name,Score}]{scores}{scores.csv}

\begin{table}[htbp]
\caption{Our final score}
\centering
\DTLdisplaydb{scores}
\end{table}

\end{document}

ex2.tex

\documentclass[a4paper]{article}

\usepackage{datatool}

\DTLloaddb[keys={ID,Name,Score}]{scores}{scores.csv}

\begin{document}

\begin{table}[htbp]
\caption{Our final score}
\centering
\begin{tabular}{clr}
\textbf{ID} & \textbf{Name} & \textbf{Score}
\DTLforeach{scores}{%
\id=ID,\name=Name,\score=Score}{%
\\ \id & \name & \score}     
\end{tabular}
\end{table}
\end{document}

The output:

(I don't know how to do that with TikZ, sorry.)

There's a follow-up to this question: Can luaLaTeX convert xlsx tables into LaTeX code?. With LuaLatex, it might be possible to unzip the file and extract data from the resulting XML. Not so easy though, IMHO.

Answer 2

With pgfplotstable (which is part of pgfplots) you can do something like

\documentclass{article}

\usepackage{pgfplotstable}
\usepackage{booktabs}

\begin{document}
\pgfplotstabletypeset[
    col sep=comma,
    columns/Name/.style={string type},
    every head row/.style={
        before row=\toprule,
        after row=\midrule},
    every last row/.style={
        after row=\bottomrule}
    ]{scores.csv}
\end{document}

Answer 3

For a different way of doing this, if you really need to import data in some sort of binary format, like xls, and especially if you want to do some calculation with the data, you could use R with Sweave. The R statistical software can read number of formats using the gdata library, and Sweave lets you incorporate R code into latex file. Assuming you have a file data.xls that looks like this:

You would create a file report.Rnw with this content:

\documentclass{article}
\usepackage{Sweave}

\begin{document}
<<echo=false, results=hide>>=
library(xtable)
library(gdata)
data <- read.xls("data.xls")
@
Table~\ref{tab:mydata} shows my data.
<<echo=false, results=tex>>=
print(xtable(data, caption = "My data", label="tab:mydata"))
@
\end{document}

As you see, it is a regular LaTeX file with pieces if R code delimited by <<options>> and @ on separate lines. You would then run Sweave on it like so:

R CMD Sweave report.Rnw 

That would produce a report.tex file. Running LaTeX on the file would then typeset the following:

Answer 4

I wrote a post with video tutorial on how to use Excel for tables in Latex. Hope you will find it useful: http://quicklatex.blogspot.com/2012/02/how-to-create-table-in-latex-from-ms.html

The first method - suing datatool package

\documentclass{article}
\usepackage{datatool}
\DTLloaddb[keys={c1,c2,c3}]{ctext}{csvtext.csv}
\begin{document}
\begin{table}
\begin{tabular}{clr}
\textbf{Software} & \textbf{Manufacturer} & \textbf{Malware}
\DTLforeach{ctext}{
\cola=c1,\colb=c2,\colc=c3}{\\
\cola & \colb & \colc}
\end{tabular}
\end{table}
\end{document}

The second method - using pgfplotstable package

\documentclass{article}
\usepackage{pgfplotstable}
\usepackage{booktabs}
\begin{document}
\pgfplotstabletypeset[
col sep=comma,
columns/Software/.style={string type},
columns/num1/.style={string type},
columns/num2/.style={string type},
every head row/.style={
before row=\toprule,
after row =\midrule
},
every last row/.style={after row=\bottomrule}
]{csvtext.csv}
\end{document}