Automated quote from price list

Question

I achieved a very satisfying price list and now want to combine it with my Excel file.

I have changed my Excel file to CSV and have 1 column for quote and one for optional extras (as in my example: customer ordered red apples and pears, he can't order green apples, but I would like to give option of ordering rockmelons)

\documentclass[a4paper,12p]{article}

\usepackage[textwidth=450pt, textheight=680pt,top=3cm, left=2.5cm]{geometry} %margins

\usepackage{tabularx}
\newcommand\mce{\multicolumn{1}{c}{}}
\newcounter{RowNum}
\usepackage{siunitx}

\newcommand{\appleg} {6,225} %would like to import from excel sheet (and could probably automatically
\newcommand{\appler} {3,676} %put into tabular
\newcommand{\grape} {17,302}
\newcommand{\banana} {2,843}
\newcommand{\plum} {1,715}
\newcommand{\pear} {3,235}
\newcommand{\rockmelon} {1,941}

\begin{document}

\begin{tabular}{>{\stepcounter{RowNum}\theRowNum}r
                    >{\everypar{\hangindent0.7cm}}p{13cm} 
                    >{\$}S[table-format=2.5,
                        table-space-text-pre = {\$\,},
            output-decimal-marker={,},
                            table-column-width=2mm]} 
&   \textbf{Green Apples:} bla &   \mce    \\
\mce    &&   \appleg \\
\mce\\
&   \textbf{Red Apples:} bla &   \mce    \\
\mce    &&  \appler\\
\mce\\
&    \textbf{Grapes:} bla &   \mce    \\
\mce    &&  \grape \\
\mce\\
&     \textbf{Bananas} &   \mce    \\
\mce    &&  \banana \\
\mce\\
&     \textbf{Plums:} bla &   \mce    \\
\mce    &&  \plum \\
\mce\\
&     \textbf{Pears:} bla &   \mce    \\
\mce    && \pear \\
\mce\\
&    \textbf{Rockmelons:} bla &   \mce    \\
\mce    && \rockmelon \\
\end{tabular}

\end{document}

I came across this question & answer. I wanted to do what @Richard Roberts suggested, so downloaded Strawberry Üerl and could also install PerlTeX from MiKTeX.

Unfortunately, I have no idea though how to install Spreadsheet::Read module (Perl) or Spreadsheet::XLSX module (Perl). (I opened "Perl (command line)" app and tried to do insert Spreadsheet::Read module, but get following error: The filename, directory name, or volume label syntax is incorrect.)

Could somebody please explain how to do it. (I'm very familiar with Excel, so happy to alter my Excel table to suit LaTeX.)

I also want to clarify in the code that @Richard Roberts provided: Do I need to specify the filename in the preamble or can I do in within document via \getvalue[excel name]{B7}?

Plus I also need help with the batch file: Should that option come up under "Typeset" in TeXworks?

Answer 1

As promised I want to present my Python/pandas-based approach. You will need to have a Python distribution installed, I usually recommend Anaconda (https://www.anaconda.com/distribution/#download-section) as it already brings the pandas module we are going to use for reading the Excel file.

First let's put in some template code:

\newcommand{\appleg} {$$appleg$$} %would like to import from excel sheet (and could probably automatically
\newcommand{\appler} {$$appler$$} %put into tabular
\newcommand{\grape} {$$grape$$}
\newcommand{\banana} {$$banana$$}
\newcommand{\plum} {$$plum$$}
\newcommand{\pear} {$$pear$$}
\newcommand{\rockmelon} {$$rockmelon$$}

We will now simply replace those tokens with the value from the Excel. I assume the Excel looks like this:

What happens in the code is the following:

• I load the pandas module
• I read the Excel file into a so-called dataframe
• I open the template.tex (the file with the $$...$$ code)
• for each token I get row 0 for the resp. column, change the data type to string and replace the decimal point with the comma (one can skip this if you adapt the siunitx code)
• write out the file again

The Python code is the following:

import pandas as pd

data = pd.read_excel('data.xlsx')

with open('template.tex') as template:
    with open('finalfile.tex','wt') as outfile:
        temp = template.read()
        temp = temp.replace('$$appleg$$',str(data['appleg'][0]).replace('.', ','))
        temp = temp.replace('$$appler$$',str(data['appler'][0]).replace('.', ','))
        temp = temp.replace('$$grape$$',str(data['grape'][0]).replace('.', ','))
        temp = temp.replace('$$banana$$',str(data['banana'][0]).replace('.', ','))
        temp = temp.replace('$$plum$$',str(data['plum'][0]).replace('.', ','))
        temp = temp.replace('$$pear$$',str(data['pear'][0]).replace('.', ','))
        temp = temp.replace('$$rockmelon$$',str(data['rockmelon'][0]).replace('.', ','))

        outfile.write(temp)

The resulting TeX file then has

\newcommand{\appleg} {5,64} 
\newcommand{\appler} {23,12} 
\newcommand{\grape} {123,45}
\newcommand{\banana} {45,12}
\newcommand{\plum} {8765,54}
\newcommand{\pear} {2,15}
\newcommand{\rockmelon} {231,99}

Note that this is the quick and dirty solution for this task. There are quite a few better ways to do this in Python and pandas. In this blog entry (in German) I have used several Excel sheets and a template engine to create donation receipts.

You can also use PythonTeX to run the Python code from LaTeX, this is pretty sophisticated.

Answer 2

Thanks @Uwe Ziegenhagen. I had another look at my problem and found that csvsimple works better for me. I can read in rows (and certain cells) at a time with filter and certain columns:

\csvreader[tabular= >{\stepcounter{RowNum}\theRowNum}r %
           >{\everypar{\hangindent0.7cm}}p{13cm} b{1.3cm}, 
           filter ifthen=\equal{\csvcolii}{1}] %filter certain rows
           {\excel} %CSV source
           {14=\dbo, 15=\deo, 12=\pricel,13=\pricer} %columns to look at
           {& \textbf{\dbo} \deo & \mce  \\ \mce    && \$\,  \hfill \ifthenelse{\equal{\pricel}{0}}{}{\pricel,}\pricer \\ \mce}

Your answer is more automated though.

(Note: If your text has & symbols, make sure to substitute them with \& - took me ages to work out)