一、读操作
1、加载excel
# -*- coding: utf-8 -*-
import openpyxl
path = 'demo.xlsx'
# 加载工作本
wb = openpyxl.load_workbook(path)
2、获取所有sheet
# 获得所有标签页
#sheet=wb.get_sheet_by_name("Sheet1") #已过时,不用
#获取所有工作表名称
sheet = wb.sheetnames #返回一个list
3、获取指定sheet
sheet = wb["demo1"]
4、复制 sheet
copy_sheet = wb.copy_worksheet(sheet)
5、获取指定一个单元格对象(法一)
cell = sheet.cell(1, 1)
6、获取指定一个单元格对象(法二)
cell = sheet[‘A1’]
7、cell.value获取属性值
cell.value
8、访问多个单元格
cells_range = sheet['A1':'B5'] #返回一个元组
for cells in cells_range:
#print(cells)
for cell in cells:
print(cell)
9、获取最大行,最大列(openpyxl)(谨慎使用,不准确)
sheet.max_row
sheet.max_column
10、获取最大行,最大列(自定义函数)
def get_max_row(sheet):
i=sheet.max_row
real_max_row = 0
while i > 0:
row_dict = {i.value for i in sheet[i]}
if row_dict == {None}:
i = i-1
else:
real_max_row = i
break
return real_max_row
j = get_max_row(sheet)
def get_max_column(sheet):
i=sheet.max_column
real_max_column = 0
while i > 0:
column_dict = {i.value for i in sheet[i]}
if column_dict == {None}:
i = i-1
else:
real_max_column = i
break
return real_max_column
j = get_max_column(sheet)
二、写操作
1、写入数据
sheet['A14'] = 'm14'
wb.save(path)
也可以
m='14'
sheet['A'+m] = 'm14'
wb.save(path)