Openpyxl笔记

Openpyxl笔记

常用方法

01安装

1、找到pip3.exe所在的文件夹，复制路径
我的路径是：C:\Users\孙艺航\AppData\Local\Programs\Python\Python37\Scripts


2、按Win+R,输入CMD确定

3、进入后，先输入cd 路径 回车

4、输入 pip3 install openpyxl 回车

pip install openpyxl

或

pip3 install openpyxl


今后如果遇到库出问题解决方法：
（1）卸载出问题的库
pip uninstall pandas
pip uninstall openpyxl
（2）重新安装
python -m pip install pandas -i https://pypi.tuna.tsinghua.edu.cn/simple
python -m pip install openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple

02工作簿

1、创建工作簿
工作簿=openpyxl.Workbook('文件名称.xlsx')
工作簿.save(路径)
2、打开工作簿
工作簿 =openpyxl.load_workbook('文件名称.xlsx')
工作表 = 工作簿 ['工作表名']

03工作表

从工作簿中获取工作表
（1）获取工作表的名称
		工作簿 .sheetnames
（2）指定工作表
		工作簿 ['工作表名称']
（3）激活第一个工作表
		工作簿.active
（4）获取工作表名称
		工作表.title

freeze_panes，参数比较特别，主要用于在表格较大时冻结顶部的行或左边的行。对于冻结的行，在用户滚动时，
是始终可见的，可以设置为一个Cell对象或一个端元个坐标的字符串，单元格上面的行和左边的列将会冻结(单元格所在的行和列不会被冻结)。
例如我们要冻结第一行那么设置A2为freeze_panes，如果要冻结第一列，freeze_panes取值为B1，如果要同时冻结第一行和第一列，
那么需要设置B2为freeze_panes，freeze_panes值为none时!表示 不冻结任何列。

04单元格

01.工作簿新建、保存和打开

一、新建和保存
import openpyxl as vb
路径 = r'c:/孙兴华.xlsx'
# 注意：
# 1. 使用Workbook新建，首字母大写 
# 2. 别忘记保存
工作簿 = vb.Workbook(路径) 
工作簿.save(路径1)

二、打开工作簿
import openpyxl as vb
路径 = r'c:/001.xlsx'
# 注意：
# 1. 指定工作簿
# 2. 指定工作表
工作簿 = vb.load_workbook(路径)
工作表 = 工作簿['1月']
print(工作表)

关于工作表的相关属性，查阅常用代码04

三、激活工作表，默认第0个
工作表 = 工作簿.active

02.工作表的创建、删除、复制

一、显示工作簿中所有的工作表和表名
import openpyxl as vb
路径 = r'c:/001.xlsx'
工作簿 = vb.load_workbook(路径)
显示所有工作表 = 工作簿.worksheets
for 工作表 in 显示所有工作表:
    print(工作表.title)

二、删除指定工作表
import openpyxl as vb
路径 = r'c:/001.xlsx'
工作簿 = vb.load_workbook(路径)
# 步骤：
# 1.给工作表指定对象
# 2.用remove删除这个对象
工作表 = 工作簿['4月']
工作簿.remove(工作表)
工作簿.save(路径)

三、新建指定工作表
import openpyxl as vb
路径 = r'c:/001.xlsx'
工作簿 = vb.load_workbook(路径)
工作簿.create_sheet('4月')
工作簿.save(路径)

四、复制指定工作表
import openpyxl as vb
路径 = r'c:/测试.xlsx'
工作簿 = vb.load_workbook(路径)
复制表 = 工作簿.copy_worksheet(工作簿['9月']) # 这里是工作表对象
复制表.title = '我是刚复制的表'
工作簿.save(路径)

练习

练习1：新建100张工作表
import openpyxl as vb
路径 = r'c:/测试.xlsx'
工作簿 = vb.Workbook(路径) # 打开工作簿
for i in range(1,101):
    工作簿.create_sheet(str(i) + '月')
工作簿.save(路径)

练习2：除了9月份的工作表以外都删除
import openpyxl as vb
路径 = r'c:/测试.xlsx'
工作簿 = vb.load_workbook(路径) # 打开工作簿
显示所有工作表 = 工作簿.worksheets
for 工作表 in 显示所有工作表:
    if 工作表.title != '9月':
    	# 如果表名是：
	#北京-01，北京-02，上海-01，上海-02
	# if 工作表.title.split("-")[0]  != '北京':
        工作表 = 工作簿[工作表.title]
        工作簿.remove(工作表)
工作簿.save(路径)

练习3：批量修改工作表的名称
import openpyxl as vb
路径 = r'c:/模板.xlsx'
工作簿 = vb.load_workbook(路径)
显示所有工作表 = 工作簿.worksheets
for 工作表 in 显示所有工作表:
        工作表.title = '北京' + 工作表.title
工作簿.save(路径)

练习4：将模板批量复制
import openpyxl as vb
路径 = r'c:/模板.xlsx'
工作簿 = vb.load_workbook(路径)
for i in range(1,32):
    复制表 = 工作簿.copy_worksheet(工作簿['Sheet1'])
    复制表.title = '7月'+ str(i) + '日'
工作簿.remove(工作簿['Sheet1']) # 把模板删除
工作簿.save(路径)

03.工作表中单元格的操作

一、获取一个单元格的值

import openpyxl as vb
路径 = r'c:/测试.xlsx'
工作簿 = vb.load_workbook(路径)
# 单元格 = 工作簿['1月']['A1'].value
# 单元格 = 工作表.cell(row=1,column=1).value
# 工作表 = 工作簿.worksheets[0]
工作表 = 工作簿['Sheet1']
单元格 = 工作表['A1'].value
print(单元格)

# VBA中表示单元格：
# range('A1')
# cells(行，列)

二、获取第2列1、3、5、7行的数据

import openpyxl as vb
路径 = r'c:/测试.xlsx'
工作簿 = vb.load_workbook(路径)
工作表 = 工作簿['Sheet1']
for i in range(1,8,2):
    print(i,工作表.cell(row=i,column=2).value)

3.1获取一个区域的单元格

import openpyxl as vb
路径 = r'c:/测试.xlsx'
工作簿 = vb.load_workbook(路径)
工作表 = 工作簿['Sheet1']
单元格区域 = 工作表['A1:C10']
# 单元格区域 = 工作表['1:10']==========按行的优势：这一行所有使用的单元格都会获取到，新增列也可以获取到
# 单元格区域 = 工作表['A:C']===========按列的优势：取完一列的数据之后再取下一列。【这是唯一的按列取数据】
for 数据 in 单元格区域:    # 循环每行
    for 单元格 in 数据:   # 循环每个单元格
        print(单元格.value)

例1：使用list(工作表.values)

import openpyxl as vb
路径 = r'c:/测试.xlsx'
工作簿 = vb.load_workbook(路径)
工作表 = 工作簿['Sheet1']
# 只能对整个工作表操作，不能对单独区域操作
# print(list(工作表.values)[1:3])
# 可以用切片的方式截取某一段数据
print(list(工作表.values))

例2：.iter_rows(min_row=最低行数，max_row=最高行数，min_col=最低列数，max_col=最高列数)
[一般情况下只需要定位起点]

import openpyxl as vb
路径 = r'c:/测试.xlsx'
工作簿 = vb.load_workbook(路径) 
工作表 = 工作簿['Sheet1']  
# 行和列的范围  iter_cols按列
# 						行，行，列，列
for 行 in 工作表.iter_rows(min_row=1,max_row=10,min_col=1,max_col=3):
    for 单元格 in 行: 
        print(单元格.value)
        
        
或：


import openpyxl as vb
路径 = r'c:/测试.xlsx'
工作簿 = vb.load_workbook(路径) 
工作表 = 工作簿['Sheet1']
for 列 in 工作表.iter_cols(min_row=1,max_row=10,min_col=1,max_col=3):
    for 单元格 in 列: 
        print(单元格.value)

3.1.1获取每一行，每一列

import openpyxl as vb
路径 = r'c:/测试.xlsx'
工作簿 = vb.load_workbook(路径)
工作表 = 工作簿['Sheet1']
# 因为按行，所以返回A1, B1, C1这样的顺序
for 行 in 工作表.rows:
    for 单元格 in 行:
        print(单元格.value)
# A1, A2, A3这样的顺序
for 列 in 工作表.columns:
    for 单元格 in 列:
        print(单元格.value)

3.1.2列字母和数字之间的转换

import openpyxl as vb
# 根据列的数字返回字母
# 使用这些函数时，不必加载一个工作簿
数字转字母 = vb.utils.get_column_letter(2)
print(数字转字母)
# 根据字母返回列的数字
字母转数字 = vb.utils.column_index_from_string('D')
print(字母转数字)

3.2读取数据

import openpyxl as vb
路径 = r'c:/测试2.xlsx'
工作簿 = vb.load_workbook(路径)
工作表 = 工作簿['Sheet1']
最大行 = 工作表.max_row
最大列 = 工作表.max_column
A1单元格的值 = 工作表['A1'].value
A1单元格的值2 = 工作表.cell(1,1).value
A1单元格的列 = 工作表['A1'].column
A1单元格的行 = 工作表['A1'].row
# 获取C列所有数据
列表1 = []
for i in 工作表['C']:
    列表1.append(i.value)
# 获取第1行所有数据
列表2 = []
for i in 工作表[1]:
    列表2.append(i.value)
# 获取所有数据
列表3 = []
for 行 in 工作表.rows:
    for 单元格 in 行:
        列表3.append(单元格.value)
print(列表3)

3.3写入数据

一、向一个单元格写入数据

import openpyxl as vb
路径 = r'c:/测试2.xlsx'
工作簿 = vb.load_workbook(路径)
工作表 = 工作簿['Sheet1']
工作表.cell(1,5,value='孙兴华')
工作表['E2']='兴华'
工作簿.save(路径)

二、在最后一列写入数据

工作表.append(列表)

三，向一个区域内写入数据

for 行 in 工作表['A1:B4']:
	for 单元格 in 行：
	    单元格.value = 520