Being a Microsoft Student Partner (MSP) means we have to deal with our developer community every day. And every day, we are trying to teach and learn new things. I was selected MSP from my university, Mehran University of Engineering and Technology (MUET), Jamshoro in January 2020. Later, I trained some other students, both juniors and my batchmates and teachers to become MSPs from MUET. They got inducted as MSPs in April. Since then, they had been trying hard to learn and teach something new every day.
Recently, we, MSPs from MUET Jamshoro, conducted a series of webinars, TechTalks with MSPs from MUET, Jamshoro. For which we were joined by almost 340+ participants around the world. After the series, we had promised them that they would all get certificates of participation. But creating these many certificates was a hectic task for us. That's when I thought of doing this task using Python.
For this, I first imported Image, ImageDraw, and ImageFont from PIL.
from PIL import Image, ImageDraw, ImageFont
Then, since I was generating certificates based on our Feedback forms, I had to import Pandas as well
import pandas as pd
After importing Pandas as pd, I read our excel file using pandas’ read_excel function and save it into another variable form.
form = pd.read_excel(“feedback_form.xlsx”)
Now I needed two more things to proceed with our real task, i.e. generating bulk certificates using Python. Those two things were the names and converting the names to a list. Other programing languages would take about 50+ lines to do this task and you might have required a loop or two to perform the task, but in Python, it takes only one line for the same.
name_list = form[‘Name’].to_list()
Now when I have everything required, I proceeded to create the certificates. But there was one more final thing that I needed, a certificate template. I had that covered too. Since the MSP Program also provides us the certificate templates as well.
After having the certificate template ready, I needed the coordinates inside our template image for our python code to start writing. For that, I can use other tools as well, but I chose to use the simples trick available. Yes, I used PAINT.
Ok, so I had out coordinates sorted out. If someone doesn't know how to get coordinates can get the coordinates by moving the required position, and the coordinates would be available at the bottom left corner of the Paint window. I have drawn these two lines to show you that I needed the coordinates of the intersection of these two lines as I can’t see the mouse cursor in screenshots.
Now before proceeding to create certificates in bulk, I needed to create the code for a single certificate. for that, I created a dummy list with the name dummy_name. Then I first had to open the image, i.e. our certificate image. I did that using the open() function of the Image that I imported from PIL and save it into another variable, I'm.
dummy_list = [“Dummy”]
im = Image.open("cert.jpg")
After that, I now had to start drawing on our image. For that, I used the ImageDraw that I imported from PIL. I used Draw() function of ImageDraw to do that and saved it into another variable, d.
d = ImageDraw.Draw(im)
Now, I saved our coordinates into a variable, location followed by the text color that I wanted out text on the certificates to be and save it using the RGB color Scheme into a new variable, text_color. Finally, before starting to generate the certificate, I had to declare which font the text should be in. I did this using the ImageFont that I imported
from PIL and saved it into the font variable.
location = (215, 882)
text_color = (0, 137, 209)
font = ImageFont.truetype(“arial.ttf”, 150)
Now, I had to embed these properties in my text. I used the text() function of ImageDraw(d).
d.text(location, dummy_list, fill=text_color, font=font)
Now in the last, I had to save the certificate as pdf. Using the Python, you can save your output file in any that you want. I also wanted to save each certificate with everyone’s name. Therefore, I used the following code.
im.save(“certificate_” + dummy_list + “.pdf”)
That’s how the final output of the above code looks.
Now that was the code for a single certificate. I did not have to use time on generating the certificates in bulk after creating the above-given code. I just had to introduce a loop at the appropriate place.
After I declared the name_list, I included a loop,
for i in name_list:
and then change the last line of my code from
im.save(“certificate_” + dummy_list + “.pdf”)
to
im.save(“certificate_” + i + “.pdf”)
You can compare your final code with mine at here:
from PIL import Image, ImageDraw, ImageFont
import pandas as pd
form = pd.read_excel("feedback_form.xlsx")
name_list = form['Name'].to_list()
for i in name_list:
im = Image.open("cert.jpg")
d = ImageDraw.Draw(im)
location = (215, 882)
text_color = (0, 137, 209)
font = ImageFont.truetype("arial.ttf", 150)
d.text(location, i, fill=text_color,font=font)
im.save("certificate_"+i+".pdf")
And finally! 👏 that’s how I solved my problem of generating the certificates of participation for my event attendees. Now the problem was as to how to send these certificates to everyone separately. Well, I created a python program for that too. I will share that in my next article.
Top comments (2)
Hello,
Thank you for your helpful script. Could you please add the way of sending these certificates to participants?
Thank you.
Hello! Thanks for the post. I would like to know how the code can be modified to have more than one text, in this case is name_list, from the excel cells.