pip install -i https://pypi.douban.com/simple openpyxl==2.6.2

from openpyxl import load_workbook
# load_workbook，往往對已存在的excel進行讀寫操作
class Handle_excel:

  def __init__(self,filename,sheetname = None):
    self.filename = filename
    self.sheetname = sheetname
    
  def read_data(self):
    """
    讀取表格數(shù)據(jù)
    :return:
    """
    wb = load_workbook(self.filename)
    if self.sheetname is None:
      ws = wb.active # active默認讀取第一個表單
    else:
      ws = wb[self.sheetname]  # 讀取指定表單

    testcase_list = [] # 所有數(shù)據(jù)信息
    header_list = []  # 表頭信息
    for row in range(1,ws.max_row+1):
      one_row_dict = {}  # 每一行數(shù)據(jù)信息
      for column in range(1,ws.max_column+1):
        one_cell_value = ws.cell(row,column).value # cell方法，獲取單元格，返回Cell對象
        if row == 1:
          header_list.append(one_cell_value)
        else:
          key = header_list[column-1]
          one_row_dict[key] = one_cell_value
      if row != 1:
        testcase_list.append(one_row_dict)

    return testcase_list

  def write_data(self, row, column, data):
    """
    寫操作
    :param row: 指定在某一行寫
    :param column: 指定在某一列寫
    :param data: 待寫入的數(shù)據(jù)
    :return:
    """
    # 將數(shù)據(jù)寫入到excel中，不能與讀取操作公用一個Workbook對象
    # 如果使用同一個Workbook對象，只能將最后一次寫入成功，會出現(xiàn)意想不到的結果
    wb = load_workbook(self.filename)
    if self.sheetname is None:
      ws = wb.active
    else:
      ws = wb[self.sheetname]

    # 第一種寫入方式：
    # one_cell = ws.cell(row, column)
    # one_cell.value = data

    # 第二種寫入方式：
    ws.cell(row, column, value=data)

    # c.PermissionError: [Errno 13] Permission denied: 'testcase.xlsx'
    # 對exel文件修改之后，要保存，一定要將excel文件關閉
    wb.save(self.filename)


if __name__ == "__main__":
  excel_name =“testcase.xlsx"
  sheet_name = "login"
  do_excel = Handle_excel(excel_name,sheet_name)
  print(do_excel.read_data())
  #do_excel.write_data(3,4,5)