Simplifying Test Automation With Excel File Management in Python

In the ever-evolving landscape of software development, test automation has become an indispensable tool for ensuring quality and efficiency. One common challenge in test automation is managing large sets of test data, often stored in Excel files. This is what we call test automation with Excel file management.

In the following sections, we will delve into practical examples demonstrating how to:

• Read data from Excel files: Extract specific values or entire worksheets for use in your test cases.
• Write data to Excel files: Generate reports, store test results, or update existing data.
• Manipulate Excel data: Perform operations such as filtering, sorting, and calculations.
• Integrate Excel file management with your test automation framework: Seamlessly incorporate Excel data into your existing test scripts.

This blog post will explore how Python can be leveraged to streamline the process of reading, writing, and manipulating test automation with Excel file management within your test automation framework.

Prerequisites for Test Automation with Excel File Management

To work with an Excel file with Python you need to install the openpyxl  module using the command line pip install openpyxl

With that, you need a code editor like VSCode or PyCharm.

When you have the openpyxl module installed in your machine now it is time to work with any Excel file. For this blog, we will have an Excel file named “test_file.xlsx”.

The following is the code that is used for the same with the description given below:-

import openpyxl
class GuviExcelOperations:
    """
    A class representing Excel operations using openpyxl.
    """


    def __init__(self, filename):
        """
        Initializes the ExcelOperations object with the specified filename.


        Args:
            filename (str): The name of the Excel file.
        """
        self.workbook = openpyxl.load_workbook(filename)


    def get_sheet_names(self):
        """
        Retrieves the names of all sheets in the workbook.


        Returns:
            list: A list of sheet names.
        """
        return self.workbook.sheetnames


    def get_active_sheet(self):
        """
        Returns the active sheet in the workbook.


        Returns:
            openpyxl.worksheet.Worksheet: The active sheet object.
        """
        return self.workbook.active


    def get_sheet_by_name(self, sheet_name):
        """
        Retrieves a specific sheet by its name.


        Args:
            sheet_name (str): The name of the sheet.


        Returns:
            openpyxl.worksheet.Worksheet: The sheet object.
        """
        return self.workbook[sheet_name]


    def get_cell_value(self, sheet_name, row, column):
        """
        Gets the value of a cell in a specified sheet.


        Args:
            sheet_name (str): The name of the sheet.
            row (int): The row number.
            column (int): The column number.


        Returns:
            object: The value of the cell.
        """
        sheet = self.workbook[sheet_name]
        cell = sheet.cell(row=row, column=column)
        return cell.value


    def set_cell_value(self, sheet_name, row, column, value):
        """
        Sets the value of a cell in a specified sheet.


        Args:
            sheet_name (str): The name of the sheet.
            row (int): The row number.
            column (int): The column number.
            value (object): The new value for the cell.
        """
        sheet = self.workbook[sheet_name]
        cell = sheet.cell(row=row, column=column)
        cell.value = value


    def add_sheet(self, sheet_name):
        """
        Adds a new sheet to the workbook.


        Args:
            sheet_name (str): The name of the new sheet.
        """
        self.workbook.create_sheet(sheet_name)


    def delete_sheet(self, sheet_name):
        """
        Deletes a sheet from the workbook.


        Args:
            sheet_name (str): The name of the sheet to delete.
        """
        self.workbook.remove(self.workbook[sheet_name])


    def save(self):
        """
        Saves the workbook to the original file.
        """
        self.workbook.save(self.workbook.filename)


# main execution function
if __name__ == "__main__":
    filename = "test_file.xlsx"
    excel_ops = GuviExcelOperations(filename)


    # Get sheet names
    sheet_names = excel_ops.get_sheet_names()
    print("Sheet names:", sheet_names)


    # Get active sheet
    active_sheet = excel_ops.get_active_sheet()
    print("Active sheet:", active_sheet.title)


    # Get a specific sheet
    sheet1 = excel_ops.get_sheet_by_name("Sheet1")
    print("Sheet1:", sheet1)


    # Get cell value
    cell_value = excel_ops.get_cell_value("Sheet1", 2, 3)
    print("Cell value:", cell_value)


    # Set cell value
    excel_ops.set_cell_value("Sheet1", 2, 3, "New value")


    # Add a new sheet
    excel_ops.add_sheet("NewSheet")


    # Delete a sheet
    excel_ops.delete_sheet("Sheet2")  # Assuming "Sheet2" exists


    # Save the workbook
    excel_ops.save()

The provided Python code defines a class named GuviExcelOperations for interacting with Excel files using the openpyxl library. Here’s a breakdown of the code:

Class Definition:

• class GuviExcelOperations: This defines a class named GuviExcelOperations that encapsulates methods for working with Excel files.
  
• __init__(self, filename): This is the constructor method that gets called when you create an instance of the class. It takes a filename argument, which is the path to the Excel file you want to work with. Inside the constructor, it uses openpyxl.load_workbook to load the Excel file and store it in the self.workbook attribute.
  

Methods:

• get_sheet_names(self): This method retrieves a list of all sheet names present in the loaded workbook. It utilizes self.workbook.sheetnames to access the list of sheet names.
• get_active_sheet(self): This method returns the currently active sheet object in the workbook. It calls self.workbook.active to get the active sheet.
  
• get_sheet_by_name(self, sheet_name): This method takes a sheet_name argument and retrieves the specific sheet object with that name. It uses self.workbook[sheet_name] to access the sheet by its name.
  
• get_cell_value(self, sheet_name, row, column): This method retrieves the value of a cell in a specified sheet. It takes three arguments: sheet_name (sheet name), row (row number), and column (column number). It first uses self.workbook[sheet_name] to get the sheet object, then uses sheet.cell(row=row, column=column) to access the specific cell and finally return the cell’s value with cell.value.
  
• set_cell_value(self, sheet_name, row, column, value): This method allows you to set the value of a cell in a specified sheet. Similar to get_cell_value, it takes sheet_name, row, column, and an additional value argument. It retrieves the cell and then assigns the value to the cell using cell.value = value.
  
• add_sheet(self, sheet_name): This method adds a new sheet to the workbook with the provided sheet_name. It utilizes self.workbook.create_sheet(sheet_name) to create the new sheet.
  
• delete_sheet(self, sheet_name): This method deletes a sheet from the workbook based on the provided sheet_name. It first retrieves the sheet object with self.workbook[sheet_name] and then uses self.workbook.remove to remove the sheet.
  
• save(self): This method saves the modified workbook back to the original file. It uses self.workbook.save(self.workbook.filename) to save the changes to the Excel file.

Main Execution:

• The if __name__ == “__main__”: block ensures the code within this block only executes when the script is run directly, not when imported as a module.
  
• Inside this block, it defines a filename variable with the path to your Excel file.
  
• Then, it creates an instance of the GuviExcelOperations class by calling its constructor with the filename.
  
• The following lines demonstrate how to use the various methods:
  • get_sheet_names to retrieve and print sheet names.
  • get_active_sheet to get the active sheet and print its title.
  • get_sheet_by_name to access a specific sheet (“Sheet1” in this case).
  • get_cell_value to read the value from a cell (row 2, column 3 in “Sheet1”).
  • set_cell_value to modify the value of a cell (setting “New value” in row 2, column 3 of “Sheet1”).
  • add_sheet to create a new sheet named “NewSheet”.
  • delete_sheet to remove a sheet named “Sheet2” (assuming it exists).
  • Finally, save to save the changes made to the workbook.

In case you want to learn more about automation testing with Selenium, consider enrolling for GUVI’s certified Selenium Automation Testing Course online course that teaches you everything from scratch and also provides you with an industry-grade certificate!

Conclusion

Thus, we can say for any Python Automation Testers who want to use an Excel file for Data Driven Testing Framework (DDTF) it is necessary to know the usage of an Excel file with Python then and only we can be able to carry out bulk data testing in details.