Python Forum

I need to create a bunch of individualized e-mail signatures in html from data collected in an Excel file. I need a separate html/txt file as output for each signature to which the data from the excel file is populated to. I think this should be achievable with Python (flask and jinja2) and a html template. However, my knowledge of Python is unfortunately still very basic. Therefore, I would be very interested and grateful for some tipps and help in how such task could be achieved.

My project structure at the moment is as foillows:

/data.xlsx
/signature.py
  /templates/sig.html
  /output/

The simplified content of my excel file named data.xlsx looks like this within the columns A:C and rows 1:3

name_given  |  name  |  mail
==========================================
John        |  Doe   | [email protected]
Steve       |  Jobs  | [email protected]

My simplified html template is filed under sig.html and looks like this:

<!DOCTYPE html>
<html lang="de" xmlns="http://www.w3.org/1999/xhtml" xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office">
<HEAD>
    <head>
        <some css code>
    </head>
</HEAD>
<body width="100%" style="margin: 0; padding: 0 !important; mso-line-height-rule: exactly;">
    <left role="article" aria-roledescription="email" lang="de" style="width: 100%; ">
    <!--[if mso | IE]>
    <table role="presentation" border="0" cellpadding="0" cellspacing="0" width="100%" >
      <tr>
        <td>
            {{ name_given }} {{ name }}
        </td>
      </tr>
      <tr>
        <td>
          {{ mail }}
        </td>
      </tr>
    </table>
    </left>
</BODY>
</html>

I would like the following to happen:

Python should read the data from data.xlsx with pandas to a dataframe.
Every attribute within the dataframe is converted to separate list which is assigned to a variable.
The variables populated by the step before are parsed to the html template (sig.html) or maybe better a virtual copy of the html template file. The variables are already found in the html code and are enclosed in two pair of curly brackets, eg. {{ foo }}.
After the first item in each list from step 2 has been populated to the (virtual copy of) the html template its content should be saved in a new html or txt file which should be placed into the output folder. This seems to be possible by the implementation of a for-loop which could also be used to name the resulting html/txt files consecutively.

My very basic start of this looks like this:

from flask import Flask, render_template
import pandas as pd

workbook = pd.read_excel('data.xlsx', usecols='A:C')
workbook.head()

# print(workbook)
df = pd.DataFrame(workbook)

name_given = df['name_given'].values.tolist()
name = df['name'].values.tolist()
mail = df['mail'].values.tolist()

app = Flask(__name__)
@app.route('/', methods=['GET'])

def sig(name=None):
        return render_template('sig.html', name_given=name_given, name=name, mail=mail)

Any input is highly appreciated.

If you would like me to attache sample files as attachments please let me know.

ChainyDaisy