Python之openpyxl函数操作excel

安装：

pip install openpyxl

导入

from openpyxl import load_workbook

指定excel文件路径，并读取

from openpyxl import load_workbook

# 读取excel方法
def read_excel():
    
    path = r"D:\Users\72036454\Desktop\python_automation\URL\Testcase_excel\test.xlsx"  # 指定excel文件路径
    excel = load_workbook(path)  # 读取excel
    print(excel)  # 返回的是一个文件对象
read_excel()

>>>　　
<openpyxl.workbook.workbook.Workbook object at 0x000001F81D7284C0>

读取excel文件一共有几个sheet页

from openpyxl import load_workbook

# 读取excel方法
def read_excel():

    path = r"D:\Users\72036454\Desktop\python_automation\URL\Testcase_excel\test.xlsx"  # 指定excel文件路径
    excel = load_workbook(path)  # 读取excel

    print(excel.active)  # 返回当前操作sheet页
    print(excel._sheets)  # 返回所有sheet页
    print(excel['Sheet1'])  # 通过名称读取sheet页
    print(excel._sheets[0])  # 通过下标读取sheet页

read_excel()

“一行行” 读取sheet页中的值

from openpyxl import load_workbook

# 读取excel方法
def read_excel():

    path = r"D:\Users\72036454\Desktop\python_automation\URL\Testcase_excel\test.xlsx"  # 指定excel文件路径
    excel = load_workbook(path)  # 读取excel
    sheet = excel['Sheet1']  # 读取sheet页
    for i in sheet.values:  # 遍历sheet页内的值
        print(i)

read_excel()