目录
0 对应包安装
- 安装
openpyxl
模块:- 方法一:
pip
命令安装:pip install openpyxl
- 方法二:直接用Anaconda即可
- 方法一:
- Excel表格介绍:
1 Excel读取
1.1 读取对应表格
- 打开已存在表格:
load_workbook
from openpyxl import load_workbook exl = load_workbook(filename = 'test.xlsx') print(exl.sheetnames) # 打印所有工作表名 # ['Sheet1']
- 选择对应名称的工作表:单个表
exl.active
,选择表exl['表名']
from openpyxl import load_workbook exl_1 = load_workbook(filename = 'test.xlsx') print(exl_1.sheetnames) # 打印所有工作表名 # ['work'] # 选择'work'工作表 sheet = exl_1['work'] # 若只有一张表,则 sheet = exl_1.active
- 获取Excel中 内容所占的范围区域:
.dimensions
sheet = exl_1['work'] print(sheet.dimensions) # A1:B51104
1.2 读取单元格
- 获取对应单元格的具体内容:
- 方法一:指定行列数
cell=sheet.cell(row=1,column=2) print(cell.value) # 人流数
- 方法二:指定坐标
cell_1=sheet['A2'] print(cell_1.value) # 2020-09-01
- 获取单元格对应的行、列和坐标:
print(cell_1.row, cell_1.column, cell.coordinate) # 2 1 B1
1.3 读取多个格子的值
- 指定坐标范围:
cells = sheet['A1:C8'] #A1到C8区域的值
- 指定行的值:
Row = sheet[1] #第1行的值 Rows = sheet[1:2] #第1到2行的值
- 指定列的值:
Column = sheet['A'] #第A列 Columns = sheet['A:C'] #第A到C列
- 指定范围的值:
# 行获取 for row in sheet.iter_rows(min_row = 1, max_row = 5, min_col = 2, max_col = 6): print(row)
# 行获取 for row in sheet.iter_rows(min_row = 1, max_row = 5, min_col = 2, max_col = 6): print(row) # 一列由多个单元格组成,若需要获取每个单元格的值则循环获取即可 for cell in row: print(cell.value)