How to automate excel creation in Python

Programmers often deal with a LOT OF DATA. And if you are a Python programmer, or wish to become one, you will eventually have to deal with storing the program’s output data in an efficient way, be it web scraping, AI ML, we dev, or automation script.

Although saving data in files like txt, JSON, XML or HTML is nice, it may not provide a way to analyze the data easily. And that’s where excel files can be a big game-changer. Since excel files can be analyzed, and the data within them can be easily sorted or trimmed based on needs, with the help of software like Microsoft Excel, these files provide a user-friendly way to store data.

What’s up Pythoneers’ this is Vishesh Dvivedi and in this post, we are gonna learn how you can create excel files in python and how you can store data in them. This post will specifically focus on creating and storing data in excel files. If you guys want a post on reading data from an excel file, then do mention it in the comments below and I will make a post on it.

So without any further ado, let’s begin

So we are gonna start off with an empty python script right here. You can name the script whatever you want, I just named it excel.py. Now to start off, we will first import a module named xlsxwriter. This is a module you can find in python module packages, which is used to create excel files in python. You may find many other modules too that could do the same work, but I personally find this one to be the most commonly used and easy to understand.

First, you need to open the command prompt and type

pip install xlsxwriter

This command will install the xlsxwriter module onto your local system. Once that’s done, you can go back to your text editor (BTW i am using visual studio code, you can any IDE or text editor you want) and then type

import xlsxwriter

This line will import the xlsxwriter module onto our script, so that we can use all the classes and functions present within the module.

The first step to creating the excel file is, to create the excel file. So we will write

workbook = xlsxwriter.Workbook(‘AllAboutPythonExcel.xlsx’)

This line will create an instance of workbook class, which represents the new excel file that we are creating. We have passed the name AllAboutPythonExcel as the excel’s name, you can name it whatever you like.

Next, we will add a sheet to our newly created excel file. An excel can consist of one or more than one excel sheet, which contains the data. You can see the sheets at the bottom left of your screen when you open an excel in Microsoft excel.

We are gonna write

worksheet = workbook.add_worksheet()

If you guys want to name the worksheet, you can pass the name of the worksheet as a string onto the add_worksheet function, something like “firstSheet”

Now in order to enter data in excel, we need data, so we will consider this list as our sample data to add in the excel,

data = [
    {
        'name': "Edan Stein",
        'phone': "1-411-426-8735",
        'email': "facilisis.magna@aol.couk",
        'address': "594-6075 Elementum Ave",
        'country': "Belgium"
    },
    {
        'name': "Gretchen Whitfield",
        'phone': "(323) 253-9734",
        'email': "ipsum@protonmail.net",
        'address': "Ap #783-9102 Augue. Rd.",
        'country': "Netherlands"
    },
    {
        'name': "Violet Brooks",
        'phone': "1-389-367-4883",
        'email': "montes.nascetur.ridiculus@outlook.edu",
        'address': "Ap #814-4695 Odio. Street",
        'country': "India"
    },
    {
        'name': "Ethan Espinoza",
        'phone': "(428) 503-8130",
        'email': "vestibulum.lorem@yahoo.couk",
        'address': "3416 Suspendisse Rd.",
        'country': "Russian Federation"
    },
    {
        'name': "Martin Dunlap",
        'phone': "1-341-689-0165",
        'email': "natoque.penatibus@aol.couk",
        'address': "Ap #593-870 Rhoncus. Ave",
        'country': "Poland"
    }
]

We have a variable by the name data, which is a list containing multiple dictionaries, each containing data of an individual, We have a name, phone, email address, and country information with us.

Now one thing that you should understand is that the data in excel can be added using a for loop, so it will be better that the data you are gonna save in the excel, is present in the form of a collection, on which you can use a for loop, just like we have a list here.

So we will start by adding headers for the data we are gonna write in excel. We will write

worksheet.write(0, 0, ‘#’)
worksheet.write(0, 1, ‘Name’)
worksheet.write(0, 2,  ‘Phone’)
worksheet.write(0, 3, ‘Email’)
worksheet.write(0, 4, ‘Address’)
worksheet.write(0, 5, ‘Country’)

Write function is used to insert data at a specific row and column in excel. The first argument is the row number, the second argument is the column number and the third argument is the text you want to enter.

Since we are writing data at the top of the excel, the row number is zero, and the column number changes as we are writing data in the same row.

Now we will quickly create a for loop that will go through each dictionary object present inside the data list. We will use the enumerate function to also get the index of the iteration, which will be used to add data onto each row. We will write

for index, entry in enumerate(data):
    worksheet.write(index+1, 0, str(index))
    worksheet.write(index+1, 1, entry[‘name’])
    worksheet.write(index+1, 2, entry[‘phone’])
    worksheet.write(index+1, 3, entry[‘email’])
    worksheet.write(index+1, 4, entry[‘address’])
    worksheet.write(index+1, 5, entry[‘country’])

The for loop iterates through the entries in the data, and we use the worksheet.write function to keep adding data in excel. We are using index+1 as we have already added data to the excel at index 0.

Now to close off the excel, we will write,

workbook.close()

Here is the complete code:

import xlsxwriter

data = [
    {
        'name': "Edan Stein",
        'phone': "1-411-426-8735",
        'email': "facilisis.magna@aol.couk",
        'address': "594-6075 Elementum Ave",
        'country': "Belgium"
    },
    {
        'name': "Gretchen Whitfield",
        'phone': "(323) 253-9734",
        'email': "ipsum@protonmail.net",
        'address': "Ap #783-9102 Augue. Rd.",
        'country': "Netherlands"
    },
    {
        'name': "Violet Brooks",
        'phone': "1-389-367-4883",
        'email': "montes.nascetur.ridiculus@outlook.edu",
        'address': "Ap #814-4695 Odio. Street",
        'country': "India"
    },
    {
        'name': "Ethan Espinoza",
        'phone': "(428) 503-8130",
        'email': "vestibulum.lorem@yahoo.couk",
        'address': "3416 Suspendisse Rd.",
        'country': "Russian Federation"
    },
    {
        'name': "Martin Dunlap",
        'phone': "1-341-689-0165",
        'email': "natoque.penatibus@aol.couk",
        'address': "Ap #593-870 Rhoncus. Ave",
        'country': "Poland"
    }
]

workbook = xlsxwriter.Workbook("AllAboutPythonExcel.xlsx")
worksheet = workbook.add_worksheet("firstSheet")

worksheet.write(0, 0, "#")
worksheet.write(0, 1, "Name")
worksheet.write(0, 2, "Phone")
worksheet.write(0, 3, "Email")
worksheet.write(0, 4, "Address")
worksheet.write(0, 5, "Country")

for index, entry in enumerate(data):
    worksheet.write(index+1, 0, str(index))
    worksheet.write(index+1, 1, entry["name"])
    worksheet.write(index+1, 2, entry["phone"])
    worksheet.write(index+1, 3, entry["email"])
    worksheet.write(index+1, 4, entry["address"])
    worksheet.write(index+1, 5, entry["country"])

workbook.close()

If you run the script, you can see the script has created an excel file by the name AllAboutPythonExcel, the exact same name which we gave as an input to the script.

If we open the excel file, we can see the name of the sheet as firstSheet, again the same name which we gave as an input, and we can see all our data inside the excel.

If you want to make this code reusable, such that you can use it for your other python projects, you can create a function something like this.

def generate_excel(workbook_name: str, worksheet_name: str, headers_list: list, data: list):

    # Creating workbook
    workbook = xlsxwriter.Workbook(workbook_name)

    # Creating worksheet
    worksheet = workbook.add_worksheet(worksheet_name)

    # Adding headers
    for index, header in enumerate(headers_list):
        worksheet.write(0, index, str(header).capitalize())

    # Adding data
    for index1, entry in enumerate(data):
        for index2, header in enumerate(headers_list):
            worksheet.write(index1+1, index2, entry[header])

    # Close workbook
    workbook.close()

You can pass values to this function and the function will automatically perform the above-explained operations and create the excel for you.

As you can see here, the function requires parameters, the name of the excel, the name of the sheet, the list of keys contained in the data list, and finally the data list. The excel generated will look the same as it was created above.

All the code used in this post is uploaded on GitHub: