There is a delay in updating articles on this site, if you want to see articles about Python + Appium, please follow me over to Testhome. Testerhome.com/topics/2780…

There are three methods for reading external Excel files: Pandas, XLRD, and OpenPyXL. Excel files include XLS and XLSX. From APP Android end automation test beginner’s notes, write wrong place we a lot of advice oh.

A, pandas

Pandas uses the same method to read excel files with the suffix.xls and.xlsx

1. Install pip3 install pandas

Installation success display

2. Read the excel file

Excel forms and data needed

To read external tables using PANDAS, import the PANDAS package

import pandas as pd

def pd_read_excel() :
    The path to the file to read
    path = "E:\\study\\Fork\\other_file\\test.xlsx"

    Read_excel (); read_excel()
    st_data = pd.read_excel(path, sheet_name=None)
    # 1. Print the names of all subtables
    print(list(st_data))
    # 2. Console direct output
    for i in st_data.keys():
        print(i)
Copy the code

The output is:

(1) Display in the form of list

(2) Direct output

import pandas as pd

def pd_read_excel() :
    The path to the file to read
    path = "E:\\study\\Fork\\other_file\\test.xlsx"

    # 1.sheet_name defaults to 0 to return the first child table and read all the table data (including column names)
    st_data = pd.read_excel(path, sheet_name=0)
    Select * from table_name where table_name = 1;
    print(st_data.values)

    # 2. Sheet_name is defined as a child table name, indicating that the "test_sheet1" child table is returned to read all data in the table
    st_data = pd.read_excel(path, sheet_name="test_sheet1")
    Test_sheet1 (test_sheet1)
    print(st_data.values)
Copy the code

The output is:

import pandas as pd

def pd_read_excel() :
    The path to the file to read
    path = "E:\\study\\Fork\\other_file\\test.xlsx"		

    Select * from table where column names are not included;
    st_data = pd.read_excel(path, sheet_name="test_sheet1", usecols=[1])
    Select * from 'book' where 'list';
    print(st_data.values)

    # 2. Select * from table where column 1.3 is not included.
    st_data = pd.read_excel(path, sheet_name="test_sheet1", usecols=[1.3])
    Select * from list where id = 1;
    print(st_data.values)
Copy the code

import pandas as pd

def pd_read_excel() :
    The path to the file to read
    path = "E:\\study\\Fork\\other_file\\test.xlsx"		

	Read data from a fixed row in a table
    st_data = pd.read_excel(path, sheet_name="test_sheet1", nrows=1)
    Select * from 'book' where table 1 = 1;
    print(st_data.head())
    Select * from 'book'; select * from 'list';
    print(st_data.values)
Copy the code

Second, the openpyxl

PIP install OpenPyXL

Installation success display

2. Read the excel file

Excel forms and data needed

Reading external tables using OpenPyXL requires an OpenPyXL package to be imported, and only.xlsx files are identified in the following example

import openpyxl

def opl_read_xlsx() :
    The path to the file to read
    path = "E:\\study\\Fork\\other_file\\test.xlsx"
    st_data = openpyxl.load_workbook(path)

    # 1. Read all child tables
    print(st_data.worksheets)
    # 2. Get a single subtable using an index
    sheet = st_data.worksheets[0]
    print(sheet)
    # 3. Active means active form, selected sheet, open file default form (default first form)
    active_sheet = st_data.active
    print(active_sheet)
    # 4. Formal usage, indexed by name
    sheet = st_data["test_sheet2"]
    print(sheet)
Copy the code

(1) The output result is:

(2) The output result is:

(3) The output result is:

(4) The output result is:

import openpyxl

def opl_read_xlsx() :
    The path to the file to read
    path = "E:\\study\\Fork\\other_file\\test.xlsx"
    st_data = openpyxl.load_workbook(path)

    Get a row
    sheet = st_data["test_sheet2"]
    columns = sheet[2]
    # fetch the value of a row. Since the column obtained corresponds to the cell object, we iterate over the cell object and use value to fetch the value in the cell
    for column in columns:
        print(column.value)
Copy the code

The output is:

import openpyxl

def opl_read_xlsx() :
    The path to the file to read
    path = "E:\\study\\Fork\\other_file\\test.xlsx"
    st_data = openpyxl.load_workbook(path)

    Get a column
    sheet = st_data["test_sheet2"]
    rows = sheet["B"]
    Rows = 'value'; rows = 'value'
    for row in rows:
        print(row.value)
Copy the code

The output is:

It is used for python+ Appium automated testing

1. The class get_excel_data.py is used to obtain the fixed columns of the Excel table in pandas

Get external Excel file content
import pandas as pd

class GetExcelData:
    Pass a path argument externally
    def __init__(self, path: ' ') :
        self.path = path

    # Use pandas to read external Excel files
    def pd_read_excel(self, path) :
        Test_sheet1 (test_sheet1)
        st_data = pd.read_excel(path, sheet_name="test_sheet1", usecols=[1])
        Select * from test_sheet1;
        data = st_data.values
        return data

# Test function, during execution can comment
# if __name__ == '__main__':
# path = "E:\\study\\Fork\\other_file\\test.xlsx"
# a = GetExcelData(path)
# a.pd_read_excel(path)
Copy the code

2. This class is used to call the get_excel_data table

Call the get_excel_data table
from utils.get_excel_data import GetExcelData

class TestOne:
    The path to the file to read
    path = "E:\\study\\Fork\\other_file\\test.xlsx"
    driver: webdriver = None

    def __init__(self, driver) :
        super().__init__(driver)
        self.excel_data = GetExcelData(self.driver)

    def test_01(self) :
	Get the contents of the Excel file
        search_contexts = self.excel_data.pd_read_excel(self.path)
Copy the code