# Getting data dynamically into LaTeX from a spreadsheet

We need to compile each month a lot of factsheets and therefore want to do it dynamically and not just convert the data one time into a useful format. It is important for us to have access to single cells of different spreadsheets in the same LaTeX document.

Already explored exceltex from this post, but we need the solution on windows systems.

-
So batch conversion to CSV is out of question? –  percusse Aug 26 at 16:13
You may want to consider R in the middle - it works well with spreadsheet files, you can do the extraction and preparation there. It then can produce LaTeX tables. –  vaettchen Aug 26 at 16:14
@percusse yes i think so because of access to many single values. It may be possible, but it gets messy since the cells needed increases over time (future data points need to be added). –  Sebastian Büchler Aug 26 at 16:27
@vaettchen At the moment it is not crucial to make tables. It is about getting single values out of spreadsheets and insert them in many places in the document. For example i want to have something like this: Last months sharpe ratio was \input{"spreadsheet_xy.xlsx";"Cell_yz"}... –  Sebastian Büchler Aug 26 at 16:32
@SebastianBüchler The last part is possible with CSVs but Excel is a nasty nasty format. –  percusse Aug 26 at 17:05

Quick and not too dirty version inspired by @Alex's idea.

Template document:

\documentclass\{article\}

\begin\{document\}
Cell B2 from Book1.xlsx has contents {$workbook1B2}, while Cell A1 from Book2.xlsx has contents {$workbook2A1}.
\end\{document\}


Perl script (tested on Strawberry Perl for Windows, after installing the Text::Template, Spreadsheet::Read, and Spreadsheet::XLSX modules from CPAN):

#!/usr/bin/perl
use strict;
use warnings;

use Text::Template;

my $workbook1 = ReadData ("Book1.xlsx"); my$workbook1B2 = $workbook1->[1]{B2}; my$workbook2 = ReadData ("Book2.xlsx");
my $workbook2A1 =$workbook2->[1]{A1};

my $template = Text::Template->new(SOURCE => 'form.tmpl') or die "Couldn't construct template:$Text::Template::ERROR";

my %vars = (workbook1B2 => $workbook1B2, workbook2A1 =>$workbook2A1,
);

my $result =$template->fill_in(HASH => \%vars);

if (defined $result) { print$result }
else { die "Couldn't fill in template: \$Text::Template::ERROR" }


Contents of the two spreadsheets book1.xlsx and book2.xlsx, respectively:

Result:

C:\Users\renfro\Desktop>perl excel.pl
\documentclass{article}

\begin{document}
Cell B2 from Book1.xlsx has contents 3,
while Cell A1 from Book2.xlsx has contents Column A.
\end{document}

-
Very nice. Perl rules ;-) –  Alex Aug 26 at 18:21

I've used three solutions for such tasks:

1. The datatool package, which can import and format data in text files.
2. Perl scripts, in particular template toolkit. This is a very powerful template language, which can harness the full power of Perl's database interface. Reading Excel files should also be possible.
3. Since the question seems to be about .xlsx files (which are zipped .xml files), my answer to this question may also help: use xslt to process the xml into tex.
-
Please show an example. Guiding the user is certainly useful but still doesn't add anything other than a google search and getting equally confused. –  percusse Aug 26 at 16:13
@percusse I'll look for examples and post them later... –  Alex Aug 26 at 16:15
datatool can be used with datatooltk which can fetch data from Excel files. The only examples I can think of are in my new LaTeX book, which is still a work in progress but there's a draft version available for the curious. –  Nicola Talbot Aug 26 at 17:21
@NicolaTalbot Nice, I didn't know this new GUI tool. –  Alex Aug 26 at 18:22

You should have a look on the package pgfplotstable which is part of the famous pgfplots. Here is the link to the CTAN page:

It is very powerful and cam import csv files (basically text files) and also process the data (like adding columns and so on).

But I think you will have to convert your Excel files into csv files by some mechanism (e. g. Visual Basic) since the xls or xlsx format is not suitable to be read by LaTeX directly. In addition a Excel document normally has formulas and dynamic data / calculations.

-
Thank you for your fast reply. I already took a look at pgfplotstable. My concerns are mainly that you cannot access single values out of the spreadsheets. We need to do that dozens of times for each factsheet. Also converting the excel files each time into a csv is not that convenient since we have a lot of input files. –  Sebastian Büchler Aug 26 at 16:08
Please show an example. Otherwise this is better as a comment –  percusse Aug 26 at 16:14

This answer is based on my answer at Reading datafile entry by entry, except that the logic has been put into the readarray package.

It has some limitations, like using a space as an item separator (which I hope to change in a future release), but depending on the structure of your data, it could suffice. The important commands are readdef{file}{token} to place the contents of a file into a \def, \readArrayij{token}{arrayname}{width} to read a token's contents into a array, and \Arrayij{arrayname}{row}{column} to regurgitate a data entry of the array (with error checking).

If one saves the Excel spreadsheet as a text file, and if the data entries are without internal spaces, then this approach would suffice.

\documentclass{article}
\begin{document}
%
\begin{filecontents}{dataA.txt}
.15 12   13 14
.20 22   23 24
.25 45.6 33 Ending
\end{filecontents}
\def\dataB{%
B.15 B12 B13 B14
B.20 B22 B23 B24
B.25 B32 B33 BEnding
}
%
% This says to read the variable \dataA, stick it into an "array"
% called arA with a 4-column width