Python自动化测试之openpyxl二次封装

import openpyxl


class ReadExcel:

    def __init__(self, file_name, sheet_name):
        self.file_name = file_name  # 文件路径和文件名
        self.sheet_name = sheet_name  # sheet名称

    def openExcel(self):
        self.workBook = openpyxl.load_workbook(self.file_name)  # 打开文件
        self.workSheet = self.workBook[self.sheet_name]  # 获取sheet表格

    def closeExcel(self):  # 关闭文件
        self.workBook.close()

    def readData(self):
        """按行读取数据，最后返回一个存储字典的列表"""
        self.openExcel()
        rows = list(self.workSheet.rows)  # 获取所有行
        titles = []  # titles空列表，用于当字典key
        for x in rows[0]:  # 循环获取列名，写入titles
            title = x.value
            titles.append(title)
        cases = []
        for row in rows[1:]:  # 循环获取剩余所有行
            case = []
            for y in row:
                case.append(y.value)  # 循环获取每一行的内容
            cases.append(dict(zip(titles, case)))  # 通过zip聚合打包用例的标题和数据
        self.closeExcel()
        return cases

    def writeData(self, row, column, msg):  # 写入数据
        self.openExcel()
        self.workSheet.cell(row=row, column=column, value=msg)
        self.workBook.save(self.file_name)
        self.closeExcel()


if __name__ == '__main__':
    test = ReadExcel('../data/excel_data/testCase.xlsx', 'login')
    res = test.readData()  # 最后返回一个存储字典的列表
    for i in res:
        print(i)    # 输出所有字典

openpyxl简单用法：

一、新建Excel：

import openpyxl

# 创建一个工作簿
workbook = openpyxl.Workbook()
# 创建一个sheet
sheet = workbook.create_sheet('表单1')
# 写入一个数据
sheet.cell(row=1, column=1, value="python")  # row行，colum列，value值
# 保存
workbook.save('test.xlsx')  # 路径+文件名，未指定路径，默认存在当前py文件目录下
# 如果保存的文件名已存在，将会覆盖原来的文件

二、打开已存在的文件：

import openpyxl

# 打开工作簿
workbook = openpyxl.load_workbook('test.xlsx')  # 文件在当前py文件目录下无需指定路径
# 获取表单
sheet = workbook['表单1']
# 读取指定的单元格数据
cell = sheet.cell(row=1, column=1).value
print(cell)

三、openpyxl常见操作

关键代码	相关描述
cell(row, column, value)	写入数据，三个参数分别是行，列，值
cell(row, column).value	获取指定单元格的值，两个参数分别是行，列
create_sheet('表单名')	创建一个sheet
workbook.remove('表单名')	删除表单
del workbook['表单名']	删除表单
workbook.save('文件名')	保存工作簿
sheet.max_row	获取表单数据的总行数
sheet.max_column	获取表单数据的总列数
sheet.rows	获取按行所有的数据
sheet.columns	获取按列所有的数据
workbook.close()	关闭文件

 

　　注意：

• openpyxl中列和行的起始标识都是1，不是从0开始
• 从excel中读取出来的数据只有两种类型，即数值类型和字符串类型
• 不要随便在表格中敲空格，会影响判断最大行数和最大列数
• 运行操作excel的代码时，要先关闭在操作系统中打开的相关excel表，否则可能会无法读取/写入数据
• 写入数据时要执行保存

四、读取与写入数据

　　写入：

import openpyxl

# 打开工作簿
workbook = openpyxl.load_workbook('test.xlsx')  # 文件在当前py文件目录下无需指定路径
# 激活第一个表单
sheet = workbook.active
sheet["A1"] = "hello"  # 在A1单元格写入字符串
sheet["B1"] = "world"  # 在B1单元格写入字符串
sheet.cell(row=2, column=1).value = 123  # 通过cell写入第一列第二行
sheet.cell(row=2, column=2).value = 456  # 通过cell写入第二列第二行
sheet.append(["a", "b"])    # 在表底部写入一组值
workbook.save("test.xlsx")
workbook.close()

　　运行结果：

 

 

　　读取：

import openpyxl

# 打开工作簿
workbook = openpyxl.load_workbook('test.xlsx')  # 文件在当前py文件目录下无需指定路径
# 激活第一个表单
sheet = workbook.active
a = sheet["A1"].value  # 读取A1单元格内容
b = sheet["B1"].value  # 读取B1单元格内容
c = sheet.cell(row=2, column=2).value  # 读取第二行第二列的单元格内容
print(a, b, c)

 五、填充颜色及字体颜色

导入PatternFill和Font

from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.styles import Font
import os

#按照某一列指定条件对单元格填充颜色
def fill_cell(path,column,value,fill_color):
    """
    path：文件路径
    column：指定列
    value：指定值
    fill_color：填充颜色
    
    """
    files=os.listdir(path)
    for file in files:
        
        wb = load_workbook(path+'/'+file)
        ws = wb['按人汇总']

        rows = ws.max_row#行数
        columns = ws.max_column#列数

        for i in range(1, rows + 1):
            cell_value = ws.cell(row=i, column=column).value
            if cell_value == value:
                for j in range(1,columns+1):
                    ws.cell(row=i,column=j).fill = fill_color
        wb.save(path+'/'+file)
    
#按照某一列指定条件对单元格设置字体颜色
def font_cell(path,column,value,font_color):
    """
    path：文件路径
    column：指定列
    value：指定值
    font_color：字体颜色
    
    """
    files=os.listdir(path)
    for file in files:
        
        wb = load_workbook(path+'/'+file)
        ws = wb['按人汇总']

        rows = ws.max_row#行数
        columns = ws.max_column#列数

        for i in range(1, rows + 1):
            cell_value = ws.cell(row=i, column=column).value
            if cell_value == value:
                for j in range(1,columns+1):
                    ws.cell(row=i,column=j).font = font_color
        wb.save(path+'/'+file)


#填充颜色
yellow_fill = PatternFill(patternType='solid',fgColor='FFFF00')#黄
green_fill = PatternFill(patternType='solid',fgColor='90EE90')#淡绿色
blue_fill = PatternFill(patternType='solid',fgColor='00CCFF')

#字体颜色
red_font = Font(color='c00000')#红色字体

fill_cell(r'E:\测试,15,'是',yellow_fill)#是否对手账户是否为目标账户
font_cell(r'E:\测试',16,'是',red_font)#对手账户是否涉案
fill_cell(r'E:\测试.xlsx',17,'是',green_fill)#对手账户是否被冻结
fill_cell(r'E:\测试',17,'待确定',blue_fill)#对手账户是否被冻结