Getting data dynamically into LaTeX from a spreadsheet

Question

Is it possible to access to data from spreadsheets (i.e. excel spreadsheets) and include them dynamically into a LaTeX document?

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.

Answer 1

This answer is probably long past being needed but for any future users who are looking to do this. I've used PerlTeX to get the values from the .xlsx file and found a very simple solution. All that will have to be called in the LaTeX document is a simple \getValue{B7} or whatever cell you want to access.

Things that need to be installed;

• Strawberry Perl
• Spreadsheet::Read module (Perl)
• Spreadsheet::XLSX module (Perl)
• PerlTex (You can do this through the MikTex package manager (or whatever compiler you're using))

Okay, once you've done that you can simply add the function within your LaTeX docuemnt

\perlnewcommand{\getValue}[1]{

#use Text::Template;
use Spreadsheet::Read;
use Spreadsheet::XLSX;

$data = ReadData ("FileName.xlsx");

$Output = $data->[1]{$_[0]}; #Where the [1] is the sheet, not the input

return $Output

}

You'll need to compile the document with perltex but that's not too hard. I've set up a simple batch file that does it all for me.

perltex --nosafe --latex=pdflatex filename.tex

Here is the code anyway if you guys need it. Feel free to message or comment if you need any help, or I've not provided the right information. I just thought I'd get all this down before I forget to contribute to this thread that has helped me.

Answer 2

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 Spreadsheet::Read;
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}

Answer 3

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.

Answer 4

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

• pgfplotstable
• pgfplotstable documentation

It is very powerful and can 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 or the save as... function of Excel) since the xls or xlsx format is not suitable to be read by LaTeX directly. In addition, an Excel document normally has formulas and dynamic data / calculations.

Answer 5

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

REVISED ANSWER

The revised readarray package provides a more natural syntax than shown in the ORIGINAL ANSWER below. Additionally, it allows a user-specifiable data-separator character.

\documentclass{article}
\usepackage{readarray,filecontents}
\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
}
\begin{document}
%
% This says to read the variable \dataA, stick it into an "array"
% called arA with a 4-column width
\readarraysepchar{,}
\readdef{dataA.txt}{\dataA}
\readarray\dataA\arA[-,\ncols]
%
\readarraysepchar{ }
\readarray\dataB\arB[-,4]
%
\noindent
The data in the 3,2 position of the dataA file is \arA[3,2]\\
The data in the 3,4 position of dataB is \arB[3,4]
\end{document}

ORIGINAL ANSWER (based on deprecated syntax)

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}
\usepackage{readarray,filecontents}
\begin{document}
%
\begin{filecontents*}{dataA.txt}
  .15 12   13 14
  .20 22   23 24
  .25 45.6 33 Ending
\end{filecontents*}
\readdef{dataA.txt}{\dataA}
\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
\readArrayij{\dataA}{arA}{4}
\readArrayij{\dataB}{arB}{4}
\noindent
The data in the 3,2 position of the dataA file is \Arrayij{arA}{3}{2}\\
The data in the 3,4 position of dataB is \Arrayij{arB}{3}{4}
\end{document}