3

I am writing documentation for a build log that needs to have part numbers inserted. Rather than have someone go through and update part numbers in a document to reflect revisions and updates, I wanted to have parts simply listed in an excel spreadsheet (possibly across sheets to organize them) with static line numbers (so I can reference that in LaTeX somehow). Then, revisions could be made to the spreadsheet and the document recompiled.

What I would like is a command like this:

\insertdata{filename.xlsx}{sheetname}{linenumber}{pn} which would produce a part number. The last two arguments are basically a row/column selector ("pn" representing "partnumber" but could also be "name" or "description", etc. for various data stored in that row).

I have seen that the exceltex package could be re-wrapped to do this, but I am working on Windows, and I can't guarantee that others using this will have Cygwin installed, so it needs to be fully reliant on a rather stock Windows environment.

I would also prefer not to export the spreadsheet as a CSV, unless that can be integrated in the builder somehow.

If it's relevant, I am using MikTeX 2.9 and Sublime Text 3 with LaTeXTools on Windows 8.1.

Thanks!

  • 2
    Good luck. Here are links to the specifications of the format. – Henri Menke Jun 28 '16 at 22:04
  • 1
    @HariGanti Welcome! The specs are the specs you need to write a tool to parse if you want to use the Excel file directly during compilation. Good luck with that. The MS 'open' formats and the process of standardising them are infamous. Also, note that MS is not always as conscientious as one might have liked in ensuring that its own implementations respect the specifications it developed. I'm not clear why you want to use an Excel format at all. If this is just to record part numbers, why would you need that complexity? – cfr Jun 29 '16 at 0:10
  • 2
    But if it is simple, can't everyone use a .csv? You can still edit it in Excel, after all, and you don't need the spreadsheet functions simply to record part numbers. If that's not an option, then you have to convert it in order to be able to import it or write a programme you can use as part of compilation to parse the data file. (Unless somebody else has already done it, which seems doubtful given the difficulties of the format and the fact that much better options are available.) – cfr Jun 29 '16 at 0:41
  • 1
    Note that this isn't about free software. ODS is not as difficult to deal with as MS's mash-up, but that just means it would be slightly less difficult to write your own programme to parse it. It doesn't mean it would be a sensible approach. I don't use Excel. But I do use Calc extensively for record keeping and I do use the spreadsheet options extensively. But I still export to CSV format when I want to integrate the data with LaTeX. In the case of Calc, conversion could be integrated into compilation. Maybe Office offers the same facility? That is, can it be run non-interactively to convert? – cfr Jun 29 '16 at 0:48
  • 1
    That is, with LibreOffice, you can say libreoffice --convert .... Hence, if you run with shell escape, you could run the conversion as part of the compilation process. However, obviously this is a facility provided by the office suite and not something provided by TeX. So, while this is possible with LibreOffice, whether it is an option in your case will depend on whether MS Office boasts the same feature. – cfr Jun 29 '16 at 0:50
7

The datatool package has a Java helper application datatooltk that has a batch mode and a GUI mode. The batch mode is the default and allows it to be integrated into the document build process. Using this sample XLS file as an example, I can fetch the data from the first sheet (labelled products) using datatooltk and store it in datatool's internal database structure:

% arara: datatooltk: {
% arara: --> output: products.dbtex,
% arara: --> xls: shop.xls,
% arara: --> sheet: products }
% arara: pdflatex
\documentclass{article}

\usepackage{datatool}

\DTLloaddbtex{\products}{products.dbtex}

\begin{document}

Fetch values from row 3:
\DTLassign{\products}{3}{\Product=Product,\Price=Price (inc VAT)}
Product: \Product. Price: \Price.

\end{document}

I've included arara directives, but if you're not using arara the build process is (assuming the file is called test.tex):

datatooltk --output products.dbtex --xls shop.xls --sheet products
pdflatex test

Fetch values from row 3: Product: USB stick. Price: 18.0.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.