How to generate documents from a spreadsheet with Python

A common routine for operations, sales and finance teams is to generate a batch of documents from spreadsheets. For example: monthly invoices for customers, proposals for leads, receipts for investors - the list goes on.

‍

Automating this process with Python is easy, and saves up hours of weekly work. In 25 lines of code, build a web app so anyone on your team can generate these documents in under a minute.

‍

Need to create personalized documents from spreadsheet data? Let’s see how to easily generate invoices from client info using Abstra Cloud:

‍

‍

First off, we need to import the required Python libs to the project: Abstra’s lib to generate UI with a simple command; Pandas and datetime to work with the spreadsheet data; and docxtpl and zipfile to build and zip the generated files.


from abstra.forms import *
import pandas as pd
from docxtpl import DocxTemplate
from datetime import datetime
import zipfile

‍

Moving on to the logic, we first need our user to upload the spreadsheet they want to get data from. For that we’ll use the read_file widget.

Then, we’ll transform the file’s content into a Pandas dataframe and put in a read_pandas_row_selection, so the user can choose which clients (aka spreadsheet rows) they want to create an invoice for.


uploaded_file = read_file("Upload your spreadsheet below:")
file_content = uploaded_file.content

df = pd.read_excel(file_content)
selection = read_pandas_row_selection(df, hint = "Select which customers you'd like to generate an invoice to.")

‍

For the documents generation, we’ll prep by getting the invoice’s template from our workspace’s Files and opening up an empty list.

To create the documents we’re going to use a for loop: for every selected spreadsheet row, the script will transform the date into the correct format and render the client’s info into the invoice template, adding the client’s name to the file name for easy identification. We’ll append every created invoice into the document list.


doc = DocxTemplate('invoice_template.docx')

list = []

for item in selection:
    name = item['Name']
    item["Date"] = pd.to_datetime(item["Date"]).strftime("%Y-%m-%d")
    doc.render(item)
    doc.save(f'Template_rendered_{name}.docx')
    file_name = f'Template_rendered_{name}.docx'

    list.append(file_name)

‍

Your invoices are ready! To make it easy for your Form’s user to download, we’ll zip them up into a .zip file and use a Page display to add a download button + an instructive text. Finish everything off with a nice goodbye message.


with zipfile.ZipFile('invoices.zip', 'w') as zipF:
    for invoice in list:
        zipF.write(invoice, compress_type=zipfile.ZIP_DEFLATED)

Page().display(f"Your invoices are ready! Download them below:") \
    .display_file('invoices.zip') \
    .run()

display("See you next time!")

‍

Good to go. Deploy and share to give access to all your coworkers!

Want to automatically send the documents via email? Check out our quick how-to.

‍

It’s important to note this script works off of the spreadsheet’s column names and template’s variable’s names. Make sure everything lines up when customizing it to fit your own use case! Download our spreadsheet template and invoice template for a closer look.

‍

Try out this interactive example of the form we just built.

TUTORIAL

How to generate documents from a spreadsheet with Python

More like this