本文共 1671 字,大约阅读时间需要 5 分钟。
最近在做自动化测试,需要对已存在的excel进行操作,就整理了一下关于excel的操作,
from openpyxl import *from config.config import DATA_ROOT_PATHclass 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)
转载地址:http://gzjmf.baihongyu.com/