Python学习21 ：Excel基本操作和封装

安装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')