Generate MS Word documents in bulk based on an Excel list using Python

Introduction

In this blog post, I will guide you through the process of generating multiple Word documents based on an Excel list using Python. This method can save you a lot of time and reduce the potential for errors that arise from manual copy-pasting.

Understanding the Process

To start, I have a Word document with different placeholders that I want to replace with data from an Excel sheet. For example, for the first document, I will replace:

• CLIENT with Mr Jason Peterson
• Vendor with Hall PLC

This process will be repeated for each row in the Excel sheet, resulting in a dedicated folder containing all the generated documents.

Setting Up the Environment

First, I need to install the required dependencies for this project. The essential modules include:

• pandas
• openpyxl
• docxtpl

You can install these modules by running the command:

pip install pandas openpyxl docxtpl

Creating a Simple Document

Once the modules are installed, I will create a simple example. I’ll create one Word document using the docxtpl library. Here’s how it works:

1. Import the necessary dependencies.
2. Set the path to the Word template.
3. Use Python’s DateTime module to insert today’s date and the date one week from now.
4. Create a dictionary where the keys are the placeholders in the template.
5. Render the document using the dictionary and save the output.

Generating Documents from an Excel List

Now, instead of hardcoding the placeholders, I want to generate Word documents based on an Excel list. Here’s how to do that:

1. Open the prepared Excel file, ensuring the header names match the placeholder names in the Word template.
2. Use Excel formulas for calculations, such as the non-refundable fee being 20% of the total amount.
3. Use the pandas library to load the Excel data into a DataFrame.
4. Convert the DataFrame content into a list of dictionaries using df.to_dict(orient='records').
5. Iterate over this list to generate the Word documents.

Final Steps

After running the script, you will find a new folder created in your directory containing all the generated Word documents. Each document will have the appropriate placeholders replaced with the corresponding data from the Excel sheet.

Conclusion

This method of generating Word documents in bulk using Python and Excel can greatly enhance your productivity by automating the document creation process. If you have any other automation ideas or topics you’d like to see, feel free to share them in the comments!

Further Links

• GitHub Repo for the Project