openpyxl读写 *** 作

openpyxl读写 *** 作

前提知识：

测试数据：

openpyxl

安装 ：pip install openpyxl

读取

from openpyxl import load_workbook

# 导入excel文件
workbook = load_workbook(filename='test.xlsx’) 
或
workbook = open("test.xlsx")

# 打印该文件所有表名
print(workbook.sheetnames)

# 切换表
sheet = workbook['sheet1']

# 打印该表单元格规模
print(sheet.dimensions)

# 取该表的某个单元格
cell = sheet['A1']

# 打印该单元格的坐标
print(cell.row, cell.column, cell.coordinate)

# 获取多个单元格
for cell in sheet['A1:A10']
for cell in sheet['A1:G10']

for rows in sheet.iter_rows(min_row=1,max_row=5,min_col=2,max_col=7):
	for cell in rows:
		print(cell.value,end='')
	print()

# 获取单元格对象的值,[必须是单元格对象]，如果取到元组是点不出value的
print(cell.value)

写入

# 保存
workbook.save('test.xlsx')

# 修改
cell = sheet['A1'] 
cell.value = 'A1'

# 追加到最后最后一行
sheet.append([15, None, None, 14, 'qq.com'])

# 添加公式
sheet['K11'] = '=AVERAGE(K1:K10)'

# 在第二行处增加4行
sheet.insert_rows(2,4)
# 在第二列处增加4列
sheet.insert_cols(2,4)

# 删除行
sheet.delete_rows(2,4)
# 删除列
sheet.delete_cols(2,4)

# 最后一定要保存一遍
workbook.save('test.xlsx')

# 创建excel
from openpyxl import Workbook
workbook = Workbook()