openpyxl module encapsulation in Python, read and write automated test cases in Excel files

Often when we are summarizing automated tests, we place data in Excel files, CSV files, databases

There are many modules in Python that handle excel data, such as xlxd (read-only), xlwd (write-only), openpyxl (read-write)

We will use data-driven thinking, using load_workbook in openpyxl to process the data of existing Excel files, only read and write files with.xlsx extensions;

Files with the extension.xlsx are an Excel file object. In an excel object, there are often multiple forms, and in a form, there are multiple cell objects.

  • Integer obtained from excel file is of type int, decimal obtained is of type float
  • Except for the numeric type, all the data you get is a string class
  • If an empty cell does not write anything, it reads an empty type.
  • Writes FALSE in various cells in uppercase format bool, depending on the format of the cell

Q: Use classes to encapsulate excel-related operations? What are the benefits of encapsulation?

  • Pain points:
    • Each interface opens a file, locates a form, retrieves data, and if there are 100, each requires these steps
  • Packaging benefits:
    • Favors expansion, simplicity and readability, makes the program more expansive, makes the code more concise and reusable
    • Once encapsulated, you can get two lines of code for all the test cases by importing the encapsulated class and specifying the Excel file name and form name

1. Basic Use

Excel files and the following py file code must be in the same folder, or you need to specify a specific Excel file path----- I won't say much about everyone who is a big guy

Reading data does not require closing the file, writing must close the file;

from openpyxl import load_workbook


# Step One. open excel File: Use load_workbook(My Neither in the Building)
wb = load_workbook("cases.xlsx")    # Return to create one Workbook Object, Quite a excel file

# Step 2. Locate the form in two ways   
# active(Exercise)Is the default first form
# ws = wb.active  # Getting the first active form by default creates a Worksheet object, equivalent to a form

# You can also specify a form
ws = wb['multiply']

# Step 3. Locate Cells cell(Translate: Theo, row: (Translate: Meat) Line, column: (Translate: Redwood)Column
one_cell = ws.cell(row=2, column=2)     # Will create a Cell object, Equivalent to a cell

# Use Cell(Translate: Cyclo) in object value attribute, Can get the value in the cell
# print(one_cell.value)

# Method One: After positioning the cell, use value Property that writes data to the specified cell
one_cell.value = "Over my dead body"   # Modify cell values

# Method 2: Locate the form, using cell Method to write data to a specified cell
ws.cell(row=2, column=3, value="Over my dead body")

# Preservation excel file     save(Translate: Saiu)
# Write if error occurs: PermissionError: [Errno 13] Permission denied: 'cases.xlsx' ,By all means excel File not closed
wb.save("cases.xlsx")

Excel Files and Print Results:

   

2. Get the data in the form below

Method 1: cell method:

- Not recommended because only fixed row-to-row, column-to-column data can be obtained

  

from openpyxl import load_workbook  # For existing excel Read and write

# 1. open excel file
wb = load_workbook("cases.xlsx")    # Will create a Workbook Object, Is a excel file

# 2. Location Form
ws = wb['aaa']

# 3. Locate Cells
# Method 1: Get the data in the form: Use ws(Who am I?) cell(Translate: Theo) Method
# range(Translate: Soften)
# min_row(Translate: Sensitive.Around) represents the smallest line number: max_row(Translate: Karl Karl Karl.Meat: Maximum line number
# min_column(Translate: Sensitive.Reed) Minimum column number, max_column(Translate: Sensitive.Rehmannia: Maximum column number

for row in range(ws.min_row+1, ws.max_row+1):           # Get Line Number
    for col in range(ws.min_column, ws.max_column+1):   # Get Column Number
        data = ws.cell(row, col).value                  # Get value, use value attribute
        print("Value is: {}\n Type is: {}".format(data, type(data)))   # Print results

Code execution results document first line:

  

Method 2: Use iter_rows method in load_workbook + zip function to get data from form

--- Recommended use, where the header can be used to get specific values for each test case executed during automated testing

from openpyxl import load_workbook  # For existing excel Read and write

# 1. open excel file
wb = load_workbook("cases.xlsx")    # Will create a Workbook Object, Is a excel file

# 2. Location Form
ws = wb['aaa']

# min_row(Translate: Sensitive.Around) represents the smallest line number: max_row(Translate: Karl Karl Karl.Meat: Maximum line number
# min_column(Translate: Sensitive.Reed) Minimum column number, max_column(Translate: Sensitive.Rehmannia: Maximum column number

# 3. Locate Cells
# Method 2: Use load_workbook in iter_rows(ET.Bodily Death) Method to Get Data from Form
# openpyxl Version 2.6 Above without parameters: values_only
# Get the cell location using values_only = False Property Default Get Cell Object
# Get the cell location using values_only = True Then you get the value of the cell

# Gets the header ancestor, the header position is 0, the header is the first row, the minimum line number can not write 1, because the maximum line number is 1
# The result is a generator object and needs to be used tuple Convert to, and then to, the ancestor of the nested ancestor
head_data_tuple = tuple(ws.iter_rows(min_row=1, max_row=1, values_only=True))[0]
print(head_data_tuple)

# Get test case data in addition to the header,As with getting a header, the largest row and the smallest column can be left unwritten
for one_tuple in tuple(ws.iter_rows(min_row=2, max_row=5, min_col=1, max_col=7, values_only=True)):
    print(one_tuple)

# Headers and test data results
# ('case_id', 'title', 'l_data', 'r_data', 'expected', 'actual', 'result')
# (1, 'Multiply negative numbers by negative numbers', -2, -4, 8, None, None)
# (2, 'Multiplication of negative and positive numbers', -3, 4, 12, None, None)
# (3, 'Multiply Zero by Zero', 0, 0, 0, None, None)
# (4, 'Multiply positive numbers by positive numbers', 5, 3, -15, None, None)

head_data_tuple1 = tuple(ws.iter_rows(min_row=1, max_row=1, values_only=True))[0]
one_list1 = []
for one_tuple1 in tuple(ws.iter_rows(min_row=2, max_row=5, min_col=1, max_col=7, values_only=True)):
    # Meta-ancestors of tables and data, using zip Method to convert (return zip Generator object)
    # in use dict(Dickett) into a dictionary
    # Use append(Add to the list to get a list of nested Dictionaries
    one_list1.append(dict(zip(head_data_tuple1, one_tuple1)))
print(one_list1)     # Print results

# Result:
# [{'case_id': 1, 'title': 'Multiply negative numbers by negative numbers', 'l_data': -2, 'r_data': -4, 'expected': 8, 'actual': None, 'result': None}, 
#  {'case_id': 2, 'title': 'Multiplication of negative and positive numbers', 'l_data': -3, 'r_data': 4, 'expected': 12, 'actual': None, 'result': None}, 
#  {'case_id': 3, 'title': 'Multiply Zero by Zero', 'l_data': 0, 'r_data': 0, 'expected': 0, 'actual': None, 'result': None}, 
#  {'case_id': 4, 'title': 'Multiply positive numbers by positive numbers', 'l_data': 5, 'r_data': 3, 'expected': -15, 'actual': None, 'result': None}]

3. Encapsulation of openpyxl module:

from openpyxl import load_workbook


class HandleExcel:
    """
    //Encapsulate excel file processing class
    """
    def __init__(self, filename, sheetname=None):
        """
        :param filename: file name
        :param sheetname: Form name, if only one form name can be set to default
        """
        self.filename = filename
        self.sheetname = sheetname

    def get_cases(self):
        """
        //Get all test cases, instance methods
        :return:
        """
        wb = load_workbook(self.filename)   # Open File
        if self.sheetname is None:          # Locate the form to determine whether to make the form blank by default, the first form
            ws = wb.active
        else:
            ws = wb[self.sheetname]         # Otherwise, get the specified form
        # min_row=Minimum line number, max_row=Maximum Line Number(Can not write)
        # min_col=Minimum column number, max_col=Maximum Column Number
        # values_only=Get the value of a cell
        # Get header information, use iter_rows(Aitmouth) method, nesting ancestors of ancestors, omitting the smallest line number
        head_data_tuple = tuple(ws.iter_rows(max_row=1, values_only=True))[0]
        one_list = []
        for one_tuple in tuple(ws.iter_rows(min_row=2, values_only=True)):  # No maximum line number, no maximum minimum column number
            # zip The function splices the primitives of the header with the primitives of each row of use cases. dict After converting to a dictionary, add to the list one_list = []
            one_list.append(dict(zip(head_data_tuple, one_tuple)))
        return one_list

    def get_one_case(self, row):
        """
        //Get a test case
        :param row: Line Number
        :return:
        """
        return self.get_cases()[row - 1]

    def write_result(self, row, actual, result):
        """
        //Write data to the rows and columns specified by the test case
        :param row: Line Number
        :param actual: Actual results
        :param result: Result of use case execution(Pass perhaps Fail)
        :return:
        """
        # Same Workbook object, If you write data to multiple forms, Then only the last form can be written successfully and different objects need to be created
        other_wb = load_workbook(self.filename)     # create object = Open a file
        if self.sheetname is None:
            other_ws = other_wb.active
        else:
            other_ws = other_wb[self.sheetname]
        # Write in
        if isinstance(row, int) and (2 <= row <= other_ws.max_row):     # Header cannot be modified, next line begins
            other_ws.cell(row=row, column=6, value=actual)              # Write the actual result on line 6
            other_ws.cell(row=row, column=7, value=result)              # Write the result of use case execution on line 7
            other_wb.save(self.filename)                                # save Save File
            other_wb.close()        # close Close-----You don't need to close when reading data, you can turn it off or off when writing data
        else:   # If not an integer, the line number is less than 2 and greater than the maximum line number
            print("Incoming line number is incorrect, Line number should be an integer greater than 1")


if __name__ == '__main__':      # Write your own modules for your own use main function
    filename = "cases.xlsx"
    sheetname = "Sheet1"     # Specify the second form name
    # Create an object, filename=File name and sheetname=Form name may not be passed

    # do_excel = HandleExcel(filename)  # Do not pass the default first form

    do_excel = HandleExcel(filename, sheetname)
    # Get all test cases cases
    cases = do_excel.get_cases()
    print(cases)
    # Write on second line Write"Centroid", "Lime"
    a = do_excel.write_result(2, "Initial Heart 7", "Lime")
    print(a)

 

 

******* Please respect the original. If you want to reprint it, please indicate the source: reprinted from: https://www.cnblogs.com/shouhu/ Thank you!*******

Tags: Python Excel Attribute Permission denied

Posted on Mon, 06 Jan 2020 01:09:25 -0800 by dylan001