最近在做自动化测试,需要对已存在的excel进行操作,就整理了一下关于excel的操作,
from openpyxl import *
from config.config import DATA_ROOT_PATH
class ExcelOp:
def __init__(self, file):
self.file = file
self.wb = load_workbook(self.file)
sheets = self.wb.get_sheet_names() # 获取所有的sheet
self.sheet = sheets[0]
self.ws = self.wb[self.sheet]
# 获取表格的总行数和总列数
def get_row_clo_num(self):
rows = self.ws.max_row
columns = self.ws.max_column
return rows, columns
# 获取某个单元格的值
def get_cell_value(self, row, column):
cell_value = self.ws.cell(row=row, column=column).value
return cell_value
# 获取某列的所有值
def get_col_value(self, column):
rows = self.ws.max_row
column_data = []
for i in range(1, rows + 1):
cell_value = self.ws.cell(row=i, column=column).value
column_data.append(cell_value)
return column_data
# 获取某行所有值
def get_row_value(self, row):
columns = self.ws.max_column
row_data = []
for i in range(1, columns + 1):
cell_value = self.ws.cell(row=row, column=i).value
row_data.append(cell_value)
return row_data
# 设置某个单元格的值
def set_cell_value(self, row, colunm, cellvalue):
try:
self.ws.cell(row=row, column=colunm).value = cellvalue
self.wb.save(self.file)
except:
self.ws.cell(row=row, column=colunm).value = "writefail"
self.wb.save(self.file)
if __name__ == "__main__":
# 在最后一行添加数据
excel_op = ExcelOp(file="test.xlsx")# 加载对应路径的文件
row = excel_op.get_row_clo_num()[0] + 1
for i in range(1, 10):
excel_op.set_cell_value(row, i, 888888)