安装Excel库
- pip install openpyxl
- openpyxl专门处理excel 表格的。 只支持 xlsx 格式
#Excel基本操作
#导入openpyxl
#coding=utf-8
#导入openpyxl
from pprint import pprint
import openpyxl
from openpyxl.worksheet.worksheet import Worksheet
#打开excel表单
workbook = openpyxl.load_workbook("cases.xlsx")
print (workbook)
#获取表单
worksheet = workbook['Sheet1']
print(worksheet)
#获取某一行,某一列的单元
#cell() 方法得到的是一个单元格对象,不是 case_id 值
cell = worksheet.cell(row=2, column=3)
print (cell)
#获取单元格的内容
print (cell.value)
rows = list(worksheet.rows)
pprint(rows)
for row in rows:
# row: 一行数据 ()
for cell in row:
print(cell.value)
"""作业:标题不要,转换为列表格式,例如:
[ [1, '登录', '登录失败', '{"username:...}'], [] , [] ]
"""
data = []
for row in rows[1:]:
for cell in row:
row_data = []
row_data.append (cell.value)
data.append(row_data)
print(data)
"""今天的作业:标题不要,转换为字典格式,例如:
[ {"case_id": 1, "module": "登录"}, {}, {} , {}]
"""
rows = list(sheet.rows)
data = []
header = []
for tittle in rows[0]:
#获取标题
header.append(tittle.value)
print(header)
for row in rows[1:]:
row_data = {}
for idx,cell in enumerate(row):
#enumerate获取索引和值
row_data.append[header[idx]] = cell.value
data.append(row_data)
print (data)
#写入Excel操作
print(cell.value)
print(sheet.cell(row=2, column=3).value)
cell.value = 'login failed'
#写入后保存在后在excel表中生效,保存后一定要记得关闭文件
workbook.save('cases.xlsx') #如果要覆盖就填入之前的文件名,如果另存为就填写新的文件名
#关闭文件
workbook.close()
Excel封装
#Excel封装
#coding=utf-8
import openpyxl
class ExcelHandler:
def __init__(self,file_path):
"""初始化"""
self.file_path = file_path
self.workbook = None
def open_file(self):
""""打开文件"""
workbook = openpyxl.load_workbook(self,file_path)
self.workbook = workbook
return workbook
def get_sheet(self,name):
"""获取表格"""
workbook = self.open_file()
return workbook[name]
def read_data(self,name):
"""读取数据"""
sheet = self.get_sheet(name)
rows = list(sheet.rows)
data = []
header = []
for tittle in rows[0]:
#获取标题
header.append(tittle.value)
print(header)
for row in rows[1:]:
row_data = {}
for idx,cell in enumerate(row):
#enumerate获取索引和值
row_data.append[header[idx]] = cell.value
data.append(row_data)
print (data)
return data
def write_file(self,sheet_name,row,cloumn,data):
"""写入数据"""
sheet = self.get_sheet(sheet_name) #获取表单
sheet.cell(row, column).value = data
self.save()
self.close()
def save_file(self):
"""保存文件"""
self.workbook.save(self.file_path)
def close_file(self):
""""关闭文件"""
self.workbook.close()
if __name__ = '__main__'
excel = ExcelHandler('cases.xlsx')