0

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?

6
  • 1
    I could provide you with a Python-based answer if you like. It's fairly easy to get data from Excel (XLSX and CSV) into Python, from there generating TeX code is just a fingersnip. Apr 2 '20 at 5:32
  • as mr. @UweZiegenhagen wrote, python solution is also possible and then using pythonTeX with very simmilar syntax than that in the linked question. According to your question, I have a few complementary answers (I have limited experience with perlTeX; as I was testing it it wasnt great experience). First, google how to install a perl module. Start here: http://www.cpan.org/modules/INSTALL.html ; Second: You can specify .xlsx file name as you write, but there might be some pitfalls depending on your OS and specific needs: a) best to not use spaces in filename, or test it Apr 2 '20 at 6:13
  • it might not be able to parse. b) If write only file name, it has to be in same directory as is .tex file. After that, you can test if it can take relative paths. Third: If you are using MikTeX, you have to install perltex. After than, in TeXwroks, as you wrote, you have to add it as typesetting tool. There are some options you should specify, depending on your workfow. I can give you hints for that either. But, I personally think that setting up pythontex and using it is better and provides more options and flexibility. Apr 2 '20 at 6:29
  • @UweZiegenhagen: Could you please provide a Python example. If I understand it correctly (when I ducked it) - it's another program I have to download, isn't it? (Is it a free program? Is there a certain version which works best?) Thanks in advance
    – Chris Peh
    Apr 2 '20 at 7:03
  • I will prepare a long answer with the required code tonight. Yes, Python is free. Best use the Anaconda Python distribution as it has all the packages. Apr 2 '20 at 9:23
2

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:

enter image description here

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.

2
  • Thanks @Uwe Ziegenhagen. I have one more question: If I want to read out multiple rows and only rows where appleg is more than e.g. 5, is that possible?
    – Chris Peh
    Apr 6 '20 at 3:21
  • Sure can do. Do you want to sum e.g. all rows with appleg etc? Apr 6 '20 at 12:22
0

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)

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.