7

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}
2
  • 2
    Not sure whether this is possible from within LaTeX. However, there's an Excel2LaTeX add-on for Excel that formats an Excel table for LaTeX use. See tex.stackexchange.com/questions/24897/…
    – Werner
    Oct 5, 2011 at 18:54
  • 2
    IMHO I don't like xls or other OLE 2 Compound Document format. If you want to automate your table generation, I strongly suggest you to export the xls spreadsheet to a csv format and then read it in LaTeX using the datatool package. I have no idea how to integrate datatool with TikZ. :-( Oct 5, 2011 at 19:00

4 Answers 4

11

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:

LibreOffice

When saving it, I can choose the csv format:

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:

Table

(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.

2
  • Datatool is good idea. I will test. Thanks. Oct 6, 2011 at 0:54
  • Show me example, please. Oct 6, 2011 at 1:09
9

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}

enter image description here

1
  • Thanks, Ignasi! Now I learned a new way of displaying csv data. :-) Oct 6, 2011 at 11:33
4

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:

spreadsheet screenshot

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:

resulting table

2

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}
1
  • 1
    Please expand on this to make it a full answer. When possible, answers here should be self contained or point to other questions and answers on TeX.sx.
    – qubyte
    Feb 10, 2012 at 11:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .