python之openpyxl库操作excel文件

openpyxl库主要针对xlsx格式的excel进行读取和编辑

一、库安装

安装方法①
1、找到pip3.exe所在的文件夹，
2、在地址栏中输入CMD
3、输入 pip3 install openpyxl 回车 或 pip install openpyxl

安装方法②
1、进pycharm中左下角有个Terminal，点击进入
2、进入后直接输入pip3 install requests就可以安装

安装方法③
1、在Pycharm中：File->Setting->Project Interpreter
2、点击下图中的+号
3、输入openpyxl后，选中点左下角的安装即可

二、工作簿新建，保存，打开

一、新建和保存
import openpyxl as opl
路径 = r'd:/好好学习.xlsx'
工作簿 = opl.Workbook(路径) 
工作簿.save(路径)

二、打开工作簿
import openpyxl as opl
路径 = r'd:/好好学习.xlsx'
工作簿 = opl.load_workbook(路径)
工作表 = 工作簿['1月']
print(工作表)  # <Worksheet "1月">

三、工作表新建，删除，复制

一、显示工作簿中所有的工作表和表名
import openpyxl as opl
路径 = r'd:/好好学习.xlsx'
工作簿 = opl.load_workbook(路径)  # <openpyxl.workbook.workbook.Workbook object at 0x000001DADA701C40>
所有工作表 = 工作簿.worksheets     # [<Worksheet "1月">, <Worksheet "2月">, <Worksheet "3月">]
for 工作表 in 所有工作表:
    print(工作表.title)

二、删除指定工作表
import openpyxl as opl
路径 = r'd:/好好学习.xlsx'
工作簿 = opl.load_workbook(路径)
工作表 = 工作簿['Sheet1']
工作簿.remove(工作表)
工作簿.save(路径)

三、新建指定工作表
import openpyxl as opl
路径 = r'd:/好好学习.xlsx'
工作簿 = opl.load_workbook(路径)
工作簿.create_sheet('Sheet10')
工作簿.save(路径)

四、复制指定工作表
import openpyxl as opl
路径 = r'd:/好好学习.xlsx'
工作簿 = opl.load_workbook(路径)
复制表 = 工作簿.copy_worksheet(工作簿['Sheet1'])  # 这里是工作表对象
复制表.title = '我是复制的表'                                       # 修改复制过来的表的名称
工作簿.save(路径)  

四、获取工作表的方法

（1）获取所有工作表的名称

工作簿 .sheetnames   # ['Sheet1', 'Sheet2', 'Sheet3']

（2）获取指定工作表

工作簿 ['Sheet2']     # 获取第2个工作表 # <Worksheet "Sheet2">
工作簿.worksheets[0]  # 获取第1个工作表 # <Worksheet "Sheet1">

（3）获取最后保存的那个工作表

工作簿.active             # <Worksheet "Sheet2">

（4）获取工作表名称

工作表.title                # Sheet2

五、读单元格数据

一、获取一个单元格的值

**①通过指定坐标**
import openpyxl as opl
路径 = r'd:/好好学习.xlsx'
工作簿 = opl.load_workbook(路径)
工作表 = 工作簿['Sheet1']
单元格 = 工作表['A1'].value 
# 单元格 = 工作簿['Sheet1']['A1'].value  # 简写
print(单元格)  # 姓名

②通过指定行和列[cell(行，列)]
单元格 = 工作表.cell(row=1,column=1).value
print(单元格)  # 姓名

二、获取单元格对应的行、列和坐标

import openpyxl as opl
路径 = r'd:/好好学习.xlsx'
工作簿 = opl.load_workbook(路径)
单元格 = 工作簿['Sheet1']['A1']
print(单元格.row,单元格.column,单元格.coordinate)  # 1 1 A1    

三、获取一个区域的单元格

一、指定坐标范围
import openpyxl as opl
路径 = r'd:/好好学习.xlsx'
工作簿 = opl.load_workbook(路径)
工作表 = 工作簿['Sheet1']
单元格区域 = 工作表['A1:C10']
# 单元格区域 = 工作表['1:10']   # 第1行到第10行
# 单元格区域 = 工作表['A:C']    # 第A行到第C行
for 数据 in 单元格区域:    # 循环每行/每列
    for 单元格 in 数据:      # 循环每个单元格
        print(单元格.value)

注：单元格区域 = 工作表[2] # 只获取一行 ， 2 就是第二行
单元格区域 = 工作表[B] # 只获取一列， B 就是 B列

二、自定范围
例：.iter_rows(min_row=最低行数，max_row=最高行数，min_col=最低列数，max_col=最高列数)
[一般情况下只需要定位起点，终点定位多了，会获取到好多空白]
①按行：
import openpyxl as opl
路径 = r'd:/好好学习.xlsx'
工作簿 = opl.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 opl
路径 = r'd:/好好学习.xlsx'
工作簿 = opl.load_workbook(路径) 
工作表 = 工作簿['Sheet1']
# 行和列的范围  iter_cols按列
for 列 in 工作表.iter_cols(min_row=1(,max_row=10),min_col=1,max_col=3):
    for 单元格 in 列: 
        print(单元格.value)

注：设置了最大行和列，行和列内没有数据，就会获取到空None，一般不设置最大值，让程序自动获取。

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

获取每一列
# A1, A2, A3这样的顺序
for 列 in 工作表.columns:
    for 单元格 in 列:
        print(单元格.value)

前面总结练习

题目：找出text_1.xlsx中sheet1表中空着的格子，并输出这些格子的坐标

import openpyxl as opl
路径=r'C:\Users\49689\Desktop\pydir\test_1.XLSX'
工作簿 = opl.load_workbook(路径)   # 打开工作簿
工作表 = 工作簿['Sheet1']          # 打开工作表
for 单元格 in 工作表.columns:      # 遍历每列
	for i in 单元格:              # 遍历每列中的单元格
		if i.value==0:           # 判断单元格是否为0
print(i.coordinate)              # 打印坐标位置

六、写单元格数据

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

import openpyxl as opl
路径 = r'd:/好好学习.xlsx'
工作簿 = opl.load_workbook(路径)
工作表 = 工作簿['Sheet1']
工作表.cell(1,5,value='学python')  # 通过行和列点位写入内容
工作表['E2']='学JAVA'              # 通过指定坐标写入内容
工作簿.save(路径)  # 存入原Excel表中，若创建新文件则可命名为不同名称

注：若原来的坐标上有数据，在写入数据会替代原来的内容

2、在最后一列写入数据

list=['海绵宝宝',2,5,10]
工作表.append(list)

3、向一个区域内写入数据

for 行 in 工作表['B2:D4']:
	for 单元格 in 行：
		单元格.value = '良好'

4、用xlwt库向一个单元格写入内容

import xlwt
workbook = xlwt.Workbook(encoding = 'utf-8')  # 新建工作簿
sheet = workbook.add_sheet('My Worksheet')    # 创建一个sheet
# excel写入内容
# 参数对应   行, 列, 值
sheet.write(1,0,label = 'this is test')
# 保存
workbook.save('new_test.xls')

注：写入的行和列都是从索引0开始

5、用xlwt库写入多行内容

import xlwt
workbook = xlwt.Workbook(encoding='utf-8')
sheet = workbook.add_sheet('MyWorksheet')
data = [['hello',22,'hi'],
	['hell',23,'h'],
	['he',25,'him']]
for i in range(len(data)):                # 判断有几行
	for j in range(len(data[i])):         # 判断几列
		sheet.write(i,j,data[i][j])       # 写入数据
workbook.save('test.xlsx')  # 保存文档

七、行、列的插入与删除

插入语法：sheet.insert_cols(idx=?,amount=?)
idx=？—— 在第？行列前插入
amount=? —— 要插入的数量，无amount，就是插入一行

删除语法：sheet.delete_cols(idx=?,amount=?)
idx=？—— 在第？开始删除行列[包括？自己]
amount=? —— 要删除的数量，无amount，就是删除一行

import openpyxl as opl
路径 = r'd:/好好学习.xlsx'
工作簿 = opl.load_workbook(路径)
工作表 = 工作簿['Sheet1']
工作表.insert_cols(idx=2)           # 在第2列前，插入1行
工作表.insert_cols(idx=2,amount=2)  # 在第2列前，插入2行
工作表.insert_rows(idx=2,amount=2)
工作表.delete_cols(idx=2,amount=2)  # 从第2列开始[包括自己]删除2行
工作表.delete_rows(idx=2,amount=2)
工作簿.save(路径)

八、 移动单元格

import openpyxl as opl
路径 = r'd:/好好学习.xlsx'
工作簿 = opl.load_workbook(路径)
工作表 = 工作簿['Sheet1']
# rows和cols为正数为向下或向右、负数为向左或向上
工作表.move_range("A1:C3",rows=5,cols=5)  # 向下移动5行，在向右移动5行
工作簿.save(路径)