python使用openpyxl操作excel

一、类

1、定义：class + 大写字母开头，函数里面必须有self

class Student:
    def __init__(self, name):  // 构造函数
        self.name = name
    def say(self):
        print(self.name)
s = Student('lilei')
print(s.name)

二、Excel处理

1、模块openpyxl安装：pip install openpyxl

2、术语：行-row，列-column，格子-cell，表-sheet

3、读取Excel的基本命令

from openpyxl import load_workbook #读取Excel的函数
workbook = load_workbook(filename="id.xlsx")  # 加载Excel文件
print(workbook.sheetnames)   # 打印工作簿的名字，集合
sheet1 = workbook['数据']    # 获取名为数据的工作簿
print(sheet1.dimensions)     # 表的范围，左上角到右下角A1:B86

sheet2 = workbook.active     # 打开当前活跃的表（打开Excel默认的表）
print(sheet2)

# 通过坐标获取格子
cell = sheet1['B5']          # 获取B5的格子
print(cell.value)            # 获取B5的值
print(cell.row, cell.column, cell.coordinate)   #(行，列，坐标)5 2 B5

# 通过行列获取格子
cell = sheet1.cell(row=2, column=2)
print(cell.row, cell.column, cell.coordinate)

# 多个格子A列的五行
cells = sheet1['A1:A5']
print(cells)

# 整个A列
cells = sheet1['A']

# A列-C列
cells = sheet1['A:C']

# 第5行
cells = sheet1[5]

# 5-6行
cells = sheet1[5:6]

# 获取一系列的格子D2-F4，也有iter_cols()一样的，只是返回数据的顺序不一样
for cells in sheet1.iter_rows(min_row=2,max_row=4,min_col=4,max_col=6):
		print(cells)
		for r in cells:
			print(r.value)

# 获取所有的行
rows = sheet1.rows

4、写入Excel的操作

（1）写单个

from openpyxl import load_workbook

workbook = load_workbook(filename="id.xlsx")  # 加载
sheet = workbook.active  # 当前工作薄
cell = sheet['D2']     # 获取格子
cell.value = "你好呀"   # 写入格子内容
workbook.save(filename="id.xlsx")  # 保存

（2）通过字典插入行

from openpyxl import load_workbook

workbook = load_workbook(filename="id.xlsx")  # 加载
sheet = workbook.active  # 当前工作薄
data = [
	['a', 1],
	['b', 2],
	['c', 3],
	['d', 4],
	['e', 5]
]
for row in data:
	sheet.append(row)   # 追加
workbook.save(filename="id.xlsx")  # 保存

（3）直接赋值公式

from openpyxl import load_workbook
workbook = load_workbook(filename='id.xlsx')
sheet = workbook.active
sheet['C2'] = '=Average(B2:B5)'
sheet['C3'] = '那你能'
workbook.save(filename="id.xlsx")

（4）插入一空白列，行，，insert_cols(idx=数字编号第几列，amount=要插入的行属，插入3行等)

from openpyxl import load_workbook
workbook = load_workbook(filename='id.xlsx')
sheet = workbook.active
sheet.insert_cols(idx=2)  # 在第二列插入一个空白列
sheet.insert_rows(idx=2)  # 在第二行插入一个空白行
workbook.save(filename="id.xlsx")

（5）删除行列，delete_cols(idx=第几列，amount=删除几列)

from openpyxl import load_workbook
workbook = load_workbook(filename='id.xlsx')
sheet = workbook.active
sheet.delete_cols(idx=2,amount=2)  
sheet.delete_rows(idx=2)  
workbook.save(filename="id.xlsx")

（6）移动行列，move_range(范围,rows=上负下正，cols=左负右正)

from openpyxl import load_workbook
workbook = load_workbook(filename='id.xlsx')
sheet = workbook.active
sheet.move_range('E3:F7',rows=2,cols=-2)
workbook.save(filename="id.xlsx")

（7）删除添加一个工作簿

from openpyxl import load_workbook
workbook = load_workbook(filename='id.xlsx')
workbook.create_sheet('表格2')  # 增加
print(workbook.sheetnames)
sheet = workbook['表格2']
workbook.remove(sheet)   # 删除
print(workbook.sheetnames)
workbook.save(filename="id.xlsx")

（7）复制一个工作薄，修改工作薄的名字

from openpyxl import load_workbook
workbook = load_workbook(filename='id.xlsx')
sheet = workbook.active
workbook.copy_worksheet(sheet)  # 复制一个工作薄
print(workbook.sheetnames)
sheet.title = '备份'   # 修改工作簿的名字
workbook.save(filename="id.xlsx")

5、创建新的Excel

from openpyxl import Workbook
workbook = Workbook()   # 初始化一个
sheet  = workbook.active  # 当前一个工作薄
sheet.title = '表格1'      # 赋值标题
workbook.save(filename="测试.xlsx")  # 文件名

6、冻结行列，freeze_panes会冻结其上和左边的行列，这个B2会冻结第一行和第一列

from openpyxl import load_workbook
workbook = load_workbook(filename='id.xlsx')
sheet = workbook.active
sheet.freeze_panes = "B2"  
workbook.save(filename="id.xlsx")

7、筛选auto_filter.ref

from openpyxl import load_workbook
workbook = load_workbook(filename='id.xlsx')
sheet = workbook.active
sheet.auto_filter.ref = sheet.dimensions # 筛选的范围，这个是整张表
workbook.save(filename="id.xlsx")