openpyxl是python中最好用的操作excel的表格库；可参考之前《Python中常用的内置模块》文章安装。
素材test.xlsx:
获取excel中sheet名
from openpyxl import load_workbook #导入openpyxl包
workbook=load_workbook(filename="F:\\pythonProject\\test.xlsx") #打开test.xlsx文件
print(workbook.sheetnames)#获取excel表格中的sheet名称
#结果为['学生表格', 'Sheet2']
#获取表格的大小，这个大小是行列
from openpyxl import load_workbook
workbook=load_workbook(filename='F:\\pythonProject\\test.xlsx')
sheet=workbook.active
print(sheet.dimensions) #结果为A1:D5
#获取某个格子的值，两种方式，分别如下：
from openpyxl import load_workbook
workbook=load_workbook(filename='F:\\pythonProject\\test.xlsx')#打开excel文件
sheet=workbook.active #激活打开的文档
cell1=sheet['A1']
cell2=sheet['A2']
cell1_value=cell1.value
cell2_value=cell2.value
print(cell1_value,cell2_value) #结果为 ID 1001
cell11=sheet.cell(row=2,column=2)
cell22=sheet.cell(row=3,column=2)
print(cell11.value,cell22.value) #结果为 李梅 黄玲
#获取某个格子的行数、列数、坐标
from openpyxl import load_workbook
workbook=load_workbook(filename="F:\\pythonProject\\test.xlsx")
sheet=workbook.active
print(sheet)
cell1=sheet['A2']
cell2=sheet['A3']
print(cell1.value,cell1.row,cell1.column,cell1.coordinate) #结果 1001 2 1 A2
print(cell2.value,cell2.row,cell2.column,cell2.coordinate) #结果 1002 3 1 A3
#获取一系列格子中的值
from openpyxl import load_workbook
workbook=load_workbook(filename='F:\\pythonProject\\test.xlsx')
sheet=workbook.active
cells=sheet['A1:D5']
print(cells)
for i in cells: #先行再列的遍历
for j in i:
print(j.value)#结果为 ID 姓名 年龄 成绩 ..............................................
#行列遍历方式
from openpyxl import load_workbook
workbook=load_workbook(filename='F:\\pythonProject\\test.xlsx')
sheet=workbook.active
for i in sheet.rows:#获取所有行
print(i)
for i in sheet.iter_rows(max_row=5,min_row=2,max_col=4,min_col=2): #按行读取
print(i)
for i in sheet.iter_cols(max_row=5,min_row=2,max_col=4,min_col=2): #按列读取
print(i)
