Automate Excel and Word with Python - Excel Sheet to Word Docs [openpyxl, docxtpl]

hi everyone and welcome back to my channel so in today's video I'm going to show you how to create this automation project with python this project will automate both Microsoft Excel as well as word and what we're going to do is we have tons of data about students so here you have the student name the course they completed the date they completed it and the instructor name and what we're going to do is we're going to generate certificates for every single student based on their course and their name so this is going to be very fast in a very automated way and this is going to give you an idea of how you can automate things with python on your own machine so if you take a look here you can see this is our certificate template what it does is it certifies that the name of this person has completed the course on date with this instructor then what we do is we will run this script in Python it will generate 30 different word documents with each document being a certificate and then if you open up these certificates you can see that the values have been filled in so you can see this one CER ifies that Benjamin has completed this course introduction to AI on this date with this instructor and you can open up literally any of these and you can see that the correct information has been filled out so this is Elizabeth Lee she has completed this course on this date with this instructor so super easy super simple let's build this and let's read all this data from Excel generate 30 different certificates in very few lines of code in Python without further Ado let's get started so the first thing we're going to do is we're going to talk about our project structure so here we have this folder in my case I named the folder Excel sheet to Word documents you can name this whatever you want inside the folder we're going to have the student data Excel sheet which is going to be this Excel sheet with all the data that we have the other thing is going to be the template so I'll talk a bit more about the template in just a second but basically this is how it looks like and it's going to be inside this folder and the third and final file in this folder is our main.py file this is where we're going to write our code for the python project now you can see here these two files as well but this is only because the Excel sheet and the word document are both open on my machine therefore vs code shows it to us like this also I am using VSS code but you can use any editor that you like so long as you're able to run Python and that you're comfortable with it all right so how does our starter code look like here we have some imports we're importing Open PI Excel this is going to be the library that we're going to use to actually talk to Excel and read from the Excel file the other thing is docs TPL this is what we're going to use to generate Word documents based on a template finally I'm importing date time you'll see why as we go through the project now the first thing you need to do is you need to actually install both Open PI Excel and Doc TPL to do that you just have to pull up a terminal here I'm using the integrated terminal in vs code you can use any terminal you can use the regular regular old CMD on your machine now I'm just going to say pip and install Open PI Excel and Doc TPL you should press enter and these two libraries should install I'm not going to do this because I already have them installed but in your case just wait a couple minutes and you should be good to go all right going back to our code let's see what we have so here we have the Imports as I already talked about now the first thing we want to do is we want to read the information from the Excel sheet so let's totally forget about word for now forget about the template forget about the certificate we just want to read the information from Excel now to do that it's very simple with Open PI Excel to do that simply here's what I need to do first things first is I need to specify the path this is going to be the path to my Excel file so in this case this is my path make sure you replace this with your own path to your own file all right we've specified the path next thing is we use Open PI Excel to load the workbook so we say Open PI excel. loore workbook and we pass the path here this should open up the Excel workbook that we have which is this file that I have showed you countless times by now all right you opened up the workbook next thing is you want to access the sheet that you have in your file so if you take a look at the workbook you can see here you can have multiple sheets this is just how Excel usually works in our case we only have one sheet so what we're going to do is we're going to access this sheet to access it it's pretty simple you just say sheet is equal to workbook do active so active means what sheet sheet is currently open in your workbook all right we have the sheet we've loaded everything what we want to do next is to actually get the data to get the data it's very simple it's one line of code you just say sheet. values and I'm converting this into a python list so you can see here I say list sheet. values and I save this in a variable called list values if you want to print it to see what we have let's just say print list values and let's run our program to see what it does so far so running it let's just wait a second there you go you can see this is all our data taken from the Excel sheet and loaded in Python you can see here we have our headers participant name course name completion date and instructor name the other thing is you have tuples of all of our data so you can see here John Doe he took this course intro to python this is the date and time in which he took the course and he took it with the instructor Alice Johnson this is dummy data I got all of this from chat GPT so what I did is I asked chat GPT give me some dummy data for the course name course instructor and things like that and then it generated all of that for me so none of this is real these courses don't exist and these people don't exist all right so you can see all of our data is inside these different topples this is how we're going to be accessing it all right so we have our data and we've pulled it up we've loaded everything from Excel what we want to do next is actually put everything in the word documents so let's go back and take a look at our template so the way docs TPL this python Library the way it works is you take a document template so this is going to be a document that you have it's a standard template I have a previous video where we generated invoices based on a document template so you had customers they would buy stuff and you would generate invoices in this case we have this certificate template so I'm just going to zoom out a little and you can see this is the certificate you can look here you can see the title is course completion certificate by the way this is a template provided by word so if you go inside the Microsoft Office templates and you search certificate you can find this there I just tweaked a few things and that's really it but it will be available in the GitHub all right so course completion certificate and we say this certifies that name has successfully completed the course on everything else all right so what is this syntax right here for docs TPL to work you need to specify certain syntax in your document template so you can't just say this certifies and leaves it leave it blank you need to specify name and then here you put these curly brackets around it two pairs of curly brackets around it now why do we say this it's very similar of how you would use variables in programming so here what we're going to do is we're going to take the name from the Excel sheet put it in place of the name here and then we will be able to generate the template so it's super simple super easy so here's what we specify we have four different variables first variable is name so we say here name and we put the curly brackets and we say has successfully completed the course and in this case we have course between curly brackets so this is where the course name is going to go on and then here we have the date between brackets and finally we have here the instructor so let's say it's signed by the instructor so you have instructor between curly brackets and we're reusing the same variable both places and finally here I just put this as a dummy value um to have it signed by someone else let's say it's the CEO of whatever company this so this is totally fake um but I'm I just did this for the template all right so this is our template now what we need to do is we need to take the information that we gathered from Excel and basically fill it out in each of these documents with different Valu first things first here's what we do so we say Doc is equal to docs template so this docs template object this comes from the docs TPL library and in this case we say certificate. Doc so here you need to pass the name of your document template in this case it's certific do docs in my case all right so I created my docs template object the next thing I want to do is I'm going to Loop over the values of the list this is the values that we got from Excel to Loop over them I say for Value tle in list values and I slice it from one until the end so why do I do this the reason is if we go back here and we look at how our list looks you can see that the first topple is just the header so obviously I don't want to generate a document with just the participant the word participant name on the name I just want John do Jane do and so on so we're just going to skip over this first one and this is why we slice starting from one until the end all right now I'm slicing it I have each value tle now I want to write each value tle into my document to do that I use something called doc. render so this is a function provided by the docs TPL library for the docs template object so here I say doc. render and I pass a python dict here all right I've passed the python dict but what information should I put inside it first things first we're going to say name is going to be equal to the value tle Sub Zero what this means is the name variable so the name that we specified here so this is name so name is going to go here and the value for it is going to be value tle Subzero so pulling up our data again let's say this joho here this is our value tle value tle Sub Zero is just going to be the name John do the first value and then value tle sub one is going to be introduction to python okay so I specified the name next thing I want to specify the course so I say the course is equal to Value tle sub one and I do the same thing for the date and the instructor so basically I took all the information from my Excel sheet put it into this list of tuples then I looped over the tuples and each tupple I put it in the respective variable or the respective value all right so I'm rendered the document the last step I need to do is I need to save this document so to save it first things first you have to give it a name if you don't give it a name it's going to save in place of the template and we don't want that we want different documents we want many certificates so first thing we say the doc name is going to be equal to certificate plus value T Sub Zero so in this case the name of the person plus the name of the course dot Docs and finally we just say doc. saave Doc name so now I'm going to run it and see how our program works let's run it and as you can see this got printed and we got a bunch of documents that were generated here so let's take a look at what these documents look like so going back here this is my folder this is where we're working these are all the generated documents that were just generated at 10:41 a.m. you can see the time so let's check this one so this says certificate Amanda Smith machine learning so opening it as you can see this says this certifies that Amanda Smith has completed the course machine learning fundamentals on this date and it was signed by this instructor so all the information was filled in into the right places and you can check any of these documents so going back here you can check this one John do for example um it has the date introduction to Python and the instructor Alice Johnson you can check as many of these as you want but basically basically all of them will have been filled out using our code so this is a great python project it helps you automate two things you can automate both reading from Excel and writing and generating these documents based on a certain template if you run a course this can be a great way to generate certificates if you have thousands of students you can just run this and have thousands of certificates generated as well you can do this for invoices like I mentioned I have in a separate video all right thank you so much for watching and I'll see you in the next video bye-bye

Channel: Code First with Hala

Views: 6,606

Keywords:

Id: 6A-S91rqBho

Length: 12min 30sec (750 seconds)

Published: Mon Mar 27 2023