excel_openpyxl_读取数据

excel_openpyxl_读取数据。

宠着楠楠的猪

389人浏览 · 2024-09-21 22:45:50

宠着楠楠的猪 · 2024-09-21 22:45:50 发布

一 excel_openpyxl_读取数据

1.1 安装

pip install openpyxl

1.2 常用方法与属性

函数名&属性	含义
openpyxl.load_workbook(path)	加载Excel文件
workbook.active	激活第1个工作薄
workbook[name]	获取根据名字工作薄
workbook.get_sheet_by_name(name)	获取根据名字工作薄
workbook.sheetnames	获取所有工作薄名字
sheet.title	获取工作薄名字
sheet.max_row	获取行数
sheet.max_column	获取列数
sheet.cell(row,col)	获取指定单元格
sheet[cell_name]	获取指定单元格,例如:`C2`
sheet[col/row_name]	获取整行或整列单元格:填写num为整行,row_name为整列
sheet[col:col]	获取指定范围整行单元格
sheet[cell_name:cell_name]	获取指定范围单元格
sheet.iter_rows(min_row =0 , max_row =0, min_col =0, max_col =0)	获取指定范围单元格
sheet.rows	获取所有行
sheet.columns	获取所有列
cell.value	获取单元格的值

1.3 代码

# pip install openpyxl
def open():
  from openpyxl import load_workbook
  wb = load_workbook('./base_data/data01.xlsx')
  # 获取工作薄
  sh1 = wb.active
  sh2 = wb['Sheet1']
  sh3 = wb.get_sheet_by_name('Sheet1')
  print(sh1 is sh2 is sh3)


def show_sheets():
  from openpyxl import load_workbook
  wb = load_workbook('./base_data/data01.xlsx')
  print(wb.sheetnames)
  for sh in wb:
    print(sh.title)


def get_one_value():
  from openpyxl import load_workbook
  wb = load_workbook('./base_data/data01.xlsx')
  sh1 = wb.active
  value1 = sh1.cell(2,3).value
  value2 = sh1['c2'].value
  print(value1,value2)


def get_many_value():
  from openpyxl import load_workbook
  wb = load_workbook('./base_data/data01.xlsx')
  sh1 = wb['Sheet1']
  # 切片
  cells1 = sh1['c2':'d3']
  # print(cells1)
  # 整行，整列
  cell_row3 = sh1[3]
  cell_col3 = sh1['c']
  print(cell_row3)
  print(cell_col3)
  cell_row3_5 = sh1[3:5]
  print(cell_row3_5)


  # 通过迭代获取数据
  # for row in sh1.iter_rows(min_row =2 , max_row =5, max_col =3):
  #   for cell in row:
  #     print(cell.value)


  for row in sh1.iter_rows(min_row =2 , max_row =5, min_col =2, max_col =4):
    for cell in row:
      print(cell.value)
def get_all_data():
  from openpyxl import load_workbook
  wb = load_workbook('./base_data/data01.xlsx')
  sh1 = wb['Sheet1']
  for row in sh1.rows:
    for cell in row:
      print(cell.value)


  for column in sh1.columns:
    for cell in column:
      print(cell.value)


def get_num():
  from openpyxl import load_workbook
  wb = load_workbook('./base_data/data01.xlsx')
  sh1 = wb['Sheet1'] 
  print(sh1.max_row)
  print(sh1.max_column)
if __name__ == "__main__":
  # open()
  # show_sheets()
  # get_one_value()
  # get_many_value()
  # get_all_data()
  get_num()