Posted onOct 24, 2024

Automation - Using Python to Interact with Excel

#python #automation #excel #tooling

Interacting with Excel files is a common need in many industries, from data analysis to reporting. Python, thanks to powerful and versatile libraries such aspandas,openpyxl andxlrd, offers a fast and efficient way to read, write and manipulate Excel files.

In this article, we will see how to use theopenpyxl library to interact with Excel and what techniques we can adopt to automate data analysis and manipulation tasks.

🔗 Do you like Techelopment? Check out thesite for all the details!

Introduction

The goal of this article is to show how, usingPython, you can simplify and automate repetitive or complex tasks that would otherwise be time-consuming if done manually in Excel. While many of the tasks described here can be performed directly in Excel, using Python opens the door to greater flexibility and automation, making it possible to manipulate large amounts of data, create automated reports, and manage complex processes in just a few steps.

The following article lays the foundation for automating and manipulating Excel files with Python. Once you have mastered these concepts, it will be easy to expand the scripts to suit your needs, adding custom functionality or combining different tools to achieve even more powerful results.

Example excel file

The scripts that we will see in this article will refer to an excel file that contains the list of tasks of a work group. The file, called "team_tasks", is structured as shown in the image below and will contain 50 tasks:

Setup (Windows)

Let's start by installing the library that will allow us to work with the Excel file. Open the terminal (Win+r typecmd and thenEnter) and run the following command:

pip install openpyxl

At this point we are ready to open our favorite IDE and start writing our python code, so let's define the library import:

from openpyxl import load_workbook

Reading an Excel file

In this first script we will see how to instantiate theopenpyxl library object that will represent our excel file. This will allow us to open the file for reading. After that we will print the first 15 tasks to the screen:

from openpyxl import load_workbookxlsx_file_name = "team_tasks.xlsx"print(f"Reading xlsx file '{xlsx_file_name }'...")wb = load_workbook(filename='C:\\Temp\\' + xlsx_file_name )print("Read ok")ws = wb.activemy_rows = tuple(ws.rows)dictOfTasks = dict()for row in my_rows[1:15]:  #ignore first row related to the header    taskId = row[0].value    taskInfo = {"Priority": row[1].value, "Title": row[2].value, "Description": row[3].value, "Due Date": row[4].value, "Owner":row[5].value}    dictOfTasks[taskId] = taskInfofor taskIdKey in dictOfTasks:        print("Task id:", taskIdKey)        print(f" - Title: {dictOfTasks[taskIdKey]['Title']}\n"              f" - Description: {dictOfTasks[taskIdKey]['Description']}\n"              f" - Due date: {dictOfTasks[taskIdKey]['Due Date']}\n"              f" - Priority: {dictOfTasks[taskIdKey]['Priority']}\n"              f" - Owner: {dictOfTasks[taskIdKey]['Owner']}\n")input("\nHit Enter to exit...")

load_workbook allows us to open the file "team_tasks.xlsx" for reading
wb.active retrieves the first sheet of the file
tuple(ws.rows) retrieves all the rows of the excel and creates a tuple
dictOfTasks we use a dictionary to manipulate the data. The dictionary structure will be organized in the following way - for each taskid the related information. The dictionary is built only for the first 15 tasks (from row 1 to row 15 - row 0 represents the header but we will not consider it in these examples):

dictOfTasks = { taskId: {                  "Priority": "task priority",                   "Title": "task title",                   "Description": "task description",                  "Due Date": "task due date",                },                ...              }

thefor loop iterates through the dictionary to print information for each task:

#OutputReading xlsx file 'team_tasks.xlsx'...Read okTask id: ID1 - Title: Lorem ipsum - Description: dolor sit amet, consectetur adipisci elit, sed eiusmod tempor incidunt ut labore et dolore magna aliqua - Due date: 04 Nov - Priority: Medium - Owner: Developer 1Task id: ID2 - Title: Lorem ipsum - Description: dolor sit amet, consectetur adipisci elit, sed eiusmod tempor incidunt ut labore et dolore magna aliqua - Due date: 04 Nov - Priority: Medium - Owner: Developer 1Task id: ID3 - Title: Lorem ipsum - Description: dolor sit amet, consectetur adipisci elit, sed eiusmod tempor incidunt ut labore et dolore magna aliqua - Due date: 04 Nov - Priority: High - Owner: Developer 2Task id: ID4 - Title: Lorem ipsum - Description: dolor sit amet, consectetur adipisci elit, sed eiusmod tempor incidunt ut labore et dolore magna aliqua - Due date: 04 Nov - Priority: High - Owner: Developer 2Task id: ID5 - Title: Lorem ipsum - Description: dolor sit amet, consectetur adipisci elit, sed eiusmod tempor incidunt ut labore et dolore magna aliqua - Due date: 04 Nov - Priority: Medium - Owner: Developer 2Task id: ID6 - Title: Lorem ipsum - Description: dolor sit amet, consectetur adipisci elit, sed eiusmod tempor incidunt ut labore et dolore magna aliqua - Due date: 04 Nov - Priority: Medium - Owner: PM Task id: ID7 - Title: Lorem ipsum - Description: dolor sit amet, consectetur adipisci elit, sed eiusmod tempor incidunt ut labore et dolore magna aliqua - Due date: 04 Nov - Priority: Medium - Owner: PM Task id: ID8 - Title: Lorem ipsum - Description: dolor sit amet, consectetur adipisci elit, sed eiusmod tempor incidunt ut labore et dolore magna aliqua - Due date: 04 Nov - Priority: Medium - Owner: PM Task id: ID9 - Title: Lorem ipsum - Description: dolor sit amet, consectetur adipisci elit, sed eiusmod tempor incidunt ut labore et dolore magna aliqua - Due date: 04 Nov - Priority: Medium - Owner: PM Task id: ID10 - Title: Lorem ipsum - Description: dolor sit amet, consectetur adipisci elit, sed eiusmod tempor incidunt ut labore et dolore magna aliqua - Due date: 11 Nov - Priority: Medium - Owner: Developer 1Task id: ID11 - Title: Lorem ipsum - Description: dolor sit amet, consectetur adipisci elit, sed eiusmod tempor incidunt ut labore et dolore magna aliqua - Due date: 12 Nov - Priority: Medium - Owner: Developer 2Task id: ID12 - Title: Lorem ipsum - Description: dolor sit amet, consectetur adipisci elit, sed eiusmod tempor incidunt ut labore et dolore magna aliqua - Due date: 13 Nov - Priority: Medium - Owner: Developer 2Task id: ID13 - Title: Lorem ipsum - Description: dolor sit amet, consectetur adipisci elit, sed eiusmod tempor incidunt ut labore et dolore magna aliqua - Due date: 14 Nov - Priority: Medium - Owner: Developer 1Task id: ID14 - Title: Lorem ipsum - Description: dolor sit amet, consectetur adipisci elit, sed eiusmod tempor incidunt ut labore et dolore magna aliqua - Due date: 15 Nov - Priority: Medium - Owner: Developer 3Task id: ID15 - Title: Lorem ipsum - Description: dolor sit amet, consectetur adipisci elit, sed eiusmod tempor incidunt ut labore et dolore magna aliqua - Due date: 16 Nov - Priority: Medium - Owner: Developer 3Hit Enter to exit...

Retrieving tasks assigned to a team member

Now suppose we are "Developer 2", our boss sends us the file "tasks_team.xlsx" and we want to automatically extract the tasks assigned to us sorted by priority. To do this we define 2 data structures that will help us identify the tasks assigned to "Developer 2" and perform the priority sorting:

list_owner = ["Developer 2"]#define the priority sort informationpriority_order = {'Urgent': 0, 'Important': 1, 'High': 2, 'Medium': 3}

We also add a dictionary to collect information about our tasks:

my_tasks = {} #dict of my tasks

The final script will be this:

from openpyxl import load_workbooklist_owner = ["Developer 2"]xlsx_file_name = "team_tasks.xlsx"print(f"Reading xlsx file '{xlsx_file_name }'...")wb = load_workbook(filename='C:\\Temp\\' + xlsx_file_name )print("Read ok")ws = wb.activemy_rows = tuple(ws.rows)dictOfTasks = dict()for row in my_rows[1:]:  #get all rows except first row related to the header    taskId = row[0].value    taskInfo = {"Priority": row[1].value, "Title": row[2].value, "Description": row[3].value, "Due Date": row[4].value, "Owner":row[5].value}    dictOfTasks[taskId] = taskInfomy_tasks = {} #dict of my tasks#find all tasks of Developer 2for taskIdKey in dictOfTasks:    if dictOfTasks[taskIdKey]['Owner'] in list_owner:        my_tasks[taskIdKey] = dictOfTasks[taskIdKey]#shows the total tasks of Developer 2print("\nMy tasks - Total:", len(my_tasks))print("\nMy tasks by Priority:")#define the priority sort informationpriority_order = {'Urgent': 0, 'Important': 1, 'High': 2, 'Medium': 3}#sort tasks by prioritysorted_tasks = dict(sorted(my_tasks.items(), key=lambda x: priority_order[x[1]['Priority']]))#print the tasks infofor my_task_id in sorted_tasks:    print(f"{my_task_id} [{sorted_tasks[my_task_id]["Priority"]}]: \n"          f" - Title: {sorted_tasks[my_task_id]["Title"]}\n"          f" - Due Date: {sorted_tasks[my_task_id]["Due Date"]}")input("\nHit Enter to exit...")

The output will show all the info to organize our to do list 😊

#Output Reading xlsx file 'team_tasks.xlsx'...Read okMy tasks by Priority:ID30 [Important]:  - Title: Lorem ipsum - Due Date: 30 OctID3 [High]:  - Title: Lorem ipsum - Due Date: 04 NovID4 [High]:  - Title: Lorem ipsum - Due Date: 04 NovID5 [Medium]:  - Title: Lorem ipsum - Due Date: 04 NovID11 [Medium]:  - Title: Lorem ipsum - Due Date: 12 NovID12 [Medium]:  - Title: Lorem ipsum - Due Date: 13 NovID34 [Medium]:  - Title: Lorem ipsum - Due Date: 19 NovHit Enter to exit...