I. use of openpyxl

pip install openpyxl

Step 1: open the Workbook (read all the data in the Excel file and save it as a workbook object)

workbook = openpyxl.load_workbook('cases.xlsx')

Step 2: select the form object

sheet = workbook['login']

Step 3: select a form to read data

① read content (line 5, column 4)

data = sheet.cell(row=5,column=4)
printa(data.value) # value method for getting content

② write content (save will take effect)

sheet.cell(row=7,column=3,value='("Python","123456")')
# After the content is written, it must be saved before it takes effect
workbook.save('cases.xlsx')

③ get the maximum row and column

# Maximum row
sheet.max_row
# Maximum column
sheet.max_column

④ get all lattice objects by lines, and put each row of lattice into a tuple

sheet.rows

*Note: do not type spaces in the table

II. Encapsulation of openpyxl

Guide bag

import openpyxl

encapsulation

class ReadExcel(object):
    """operation Excl file"""

    def __init__(self, fileName, sheetName):
        """
        //Initialization method
        :param fileName: Excel file name
        :param sheetName: Form name
        """
        self.fileName = fileName
        self.sheetName = sheetName

    def open(self):
        """Open workbook, select form"""
        self.wb = openpyxl.load_workbook(self.fileName)
        self.sh = self.wb[self.sheetName]

    def save(self):
        """How to save workbook objects"""
        self.wb.save(self.fileName)
        self.wb.close()  # It doesn't matter if this line is added or not. If it is added, memory can be released

1. Read data

① store each data as a dictionary type

    def read_data_dict(self):
        """Read data(Store each data as a dictionary type)"""
        # Open workbook
        self.open()
        # Get all the row data in Excel file through rows, and then convert the data into a list
        rows = list(self.sh.rows)
        # Header
        title = []
        # Traverse the first row header information in Excel file
        for i in rows[0]:
            title.append(i.value)
        # Use case data list
        cases = []
        # Traverse case data lines
        for row in rows[1:]:
            # Define a list to hold the data of each row
            data = []
            # Traverse the data of each row
            for r in row:
                data.append(r.value)
            # The data of each row is packaged by zip, then converted into a dictionary and stored in the use case data list
            case = dict(zip(title, data))
            cases.append(case)
        return cases

② store each data as a CaseData class object type

class CaseData(object):
    """Save case data class"""
    pass

    def read_data_obj(self):
        """Read data(Save data to CaseData Class)"""
        self.open()
        rows = list(self.sh.rows)
        # Header
        title = []
        # Traverse the first row header information in Excel file
        for i in rows[0]:
            title.append(i.value)
        # Define the use case list to store the use case class list
        cases = []
        # Traverse case data lines
        for row in rows[1:]:
            # Define a list to hold the data of each row
            data = []
            # Traverse the data of each row
            for r in row:
                data.append(r.value)
            # The data of each row is packaged by zip, then converted into a dictionary and stored in the use case data list
            case = dict(zip(title, data))
            # Define a use case storage class object
            case_obj = CaseData()
            for k, v in case.items():
                # Add attributes to objects through setattr()
                setattr(case_obj, k, v)
            # Add objects to the list
            cases.append(case_obj)
        return cases

2. Write data

    def write_data(self, row, column, value):
        """
        //Write data
        :param row: That's ok
        :param column: column
        :param value: data
        """
        self.open()
        # Write data by specifying row and column
        self.sh.cell(row=row, column=column, value=value)
        # Preservation
        self.wb.save(self.fileName)
        # Close
        self.wb.close()

**When you need to operate excel, you can call it directly to reduce the amount of repeated code.