PythonOK章节一：python使用openpyxl操作excel
1、openpyxl库介绍
- openpyxl最好用的python操作excel表格库，不接受反驳；
- openpyxl官网链接：openpyxl官网
- openpyxl只支持【.xlsx / .xlsm / .xltx / .xltm】格式的文件；
2、python怎么打开及读取表格内容？
1）Excel表格述语
这里需要大家仔细查看图中的每一项内容，知道什么是“行(row)、列(column)”？什么是“格子(cell)”？什么是“sheet表”？
2）打开Excel表格并获取表格名称
from openpyxl import load_workbook
workbook = load_workbook(filename = "test.xlsx")
workbook.sheetnames
结果如下：
3）通过sheet名称获取表格
from openpyxl import load_workbook
workbook = load_workbook(filename = "test.xlsx")
workbook.sheetnames
sheet = workbook["Sheet1"]
print(sheet)
结果如下：
4）获取表格的尺寸大小
- 这里所说的尺寸大小，指的是excel表格中的数据有几行几列，针对的是不同的sheet而言。
sheet.dimensions
结果如下：
5）获取表格内某个格子的数据
① sheet[“A1”]方式
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
cell1 = sheet["A1"]
cell2 = sheet["C11"]
print(cell1.value, cell2.value)
"""
workbook.active 打开激活的表格；
sheet["A1"] 获取A1格子的数据；
cell.value 获取格子中的值；
"""
结果如下：
② sheet.cell(row=, column=)方式
- 这种方式更简单，大家可以对比这两种方式；
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
cell1 = sheet.cell(row = 1,column = 1)
cell2 = sheet.cell(row = 11,column = 3)
print(cell1.value, cell2.value)
结果如下：
6）获取某个格子的行数、列数、坐标
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
cell1 = sheet["A1"]
cell2 = sheet["C11"]
print(cell1.value, cell1.row, cell1.column, cell1.coordinate)
print(cell2.value, cell2.row, cell2.column, cell2.coordinate)
"""
.row 获取某个格子的行数；
.columns 获取某个格子的列数；
.corordinate 获取某个格子的坐标；
"""
结果如下：
7）获取一系列格子
① sheet[]方式
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
# 获取A1:C2区域的值
cell = sheet["A1:C2"]
print(cell)
for i in cell:
for j in i:
print(j.value)
结果如下：
特别的：如果我们只想获取“A列”，或者获取“A-C列”，可以采取如下方式：
sheet["A"] --- 获取A列的数据
sheet["A:C"] --- 获取A,B,C三列的数据
sheet[5] --- 只获取第5行的数据
② .iter_rows()方式
- 当然有.iter_rows()方式，肯定也会有.iter_cols()方式，只不过一个是按行读取，一个是按列读取。
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
# 按行获取值
for i in sheet.iter_rows(min_row=2, max_row=5, min_col=1, max_col=2):
for j in i:
print(j.value)
# 按列获取值
for i in sheet.iter_cols(min_row=2, max_row=5, min_col=1, max_col=2):
for j in i:
print(j.value)
结果如下：
③ sheet.rows()
- 帮助我们获取所有行
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
for i in sheet.rows:
print(i)
结果如下：
3、python如何向excel中写入某些内容？
1）修改表格中的内容
① 向某个格子中写入内容并保存
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
sheet["A1"] = "哈喽" # 这句代码也可以改为cell = sheet["A1"] cell.value = "哈喽"
workbook.save(filename = "哈喽.xlsx")
"""
注意：我们将“A1”单元格的数据改为了“哈喽”，并另存为了“哈喽.xlsx”文件。
如果我们保存的时候，不修改表名，相当于直接修改源文件；
"""
结果如下：
② .append()：向表格中插入行数据
- .append()方式：会在表格已有的数据后面，增添这些数(按行插入)；
- 这个操作很有用，爬虫得到的数据，可以使用该方式保存成Excel文件；
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
data = [
["唐僧","男","180cm"],
["孙悟空","男","188cm"],
["猪八戒","男","175cm"],
["沙僧","男","176cm"],
]
for row in data:
sheet.append(row)
workbook.save(filename = "test.xlsx")
结果如下：
③ 在python中使用excel函数公式(很有用)
# 这是我们在excel中输入的公式
=IF(RIGHT(C2,2)="cm",C2,SUBSTITUTE(C2,"m","")*100&"cm")
# 那么，在python中怎么插入excel公式呢？
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
sheet["D1"] = "标准身高"
for i in range(2,16):
sheet["D{}".format(i)] = '=IF(RIGHT(C{},2)="cm",C{},SUBSTITUTE(C{},"m","")*100&"cm")'.format(i,i,i)
workbook.save(filename = "test.xlsx")
结果如下：
此时，你肯定会好奇，python究竟支持写哪些“excel函数公式”呢？我们可以使用如下操作查看一下。
import openpyxl
from openpyxl.utils import FORMULAE
print(FORMULAE)
结果如下：
④ .insert_cols()和.insert_rows()：插入空行和空列
- .insert_cols(idx=数字编号, amount=要插入的列数)，插入的位置是在idx列数的左侧插入；
- .insert_rows(idx=数字编号, amount=要插入的行数)，插入的行数是在idx行数的下方插入；
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
sheet.insert_cols(idx=4,amount=2)
sheet.insert_rows(idx=5,amount=4)
workbook.save(filename = "test.xlsx")
结果如下：
⑤ .delete_rows()和.delete_cols()：删除行和列
- .delete_rows(idx=数字编号, amount=要删除的行数)
- .delete_cols(idx=数字编号, amount=要删除的列数)
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
# 删除第一列，第一行
sheet.delete_cols(idx=1)
sheet.delete_rows(idx=1)
workbook.save(filename = "test.xlsx")
结果如下：
⑥ .move_range()：移动格子
- .move_range(“数据区域”,rows=,cols=)：正整数为向下或向右、负整数为向左或向上；
# 向左移动两列，向下移动两行
sheet.move_range("C1:D4",rows=2,cols=-1)
演示效果如下：
⑦ .create_sheet()：创建新的sheet表格
- .create_sheet(“新的sheet名”)：创建一个新的sheet表；
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
workbook.create_sheet("我是一个新的sheet")
print(workbook.sheetnames)
workbook.save(filename = "test.xlsx")
结果如下：
⑧ .remove()：删除某个sheet表
- .remove(“sheet名”)：删除某个sheet表；
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(workbook.sheetnames)
# 这个相当于激活的这个sheet表，激活状态下，才可以操作；
sheet = workbook['我是一个新的sheet']
print(sheet)
workbook.remove(sheet)
print(workbook.sheetnames)
workbook.save(filename = "test.xlsx")
结果如下：
⑨ .copy_worksheet()：复制一个sheet表到另外一张excel表
- 这个操作的实质，就是复制某个excel表中的sheet表，然后将文件存储到另外一张excel表中；
workbook = load_workbook(filename = "a.xlsx")
sheet = workbook.active
print("a.xlsx中有这几个sheet表",workbook.sheetnames)
sheet = workbook['姓名']
workbook.copy_worksheet(sheet)
workbook.save(filename = "test.xlsx")
结果如下：
⑩ sheet.title：修改sheet表的名称
- .title = “新的sheet表名”
workbook = load_workbook(filename = "a.xlsx")
sheet = workbook.active
print(sheet)
sheet.title = "我是修改后的sheet名"
print(sheet)
结果如下：
⑪ 创建新的excel表格文件
from openpyxl import Workbook
workbook = Workbook()
sheet = workbook.active
sheet.title = "表格1"
workbook.save(filename = "新建的excel表格")
结果如下：
⑫ sheet.freeze_panes：冻结窗口
- .freeze_panes = “单元格”
workbook = load_workbook(filename = "花园.xlsx")
sheet = workbook.active
print(sheet)
sheet.freeze_panes = "C3"
workbook.save(filename = "花园.xlsx")
"""
冻结窗口以后，你可以打开源文件，进行检验；
"""
结果如下：
⑬ sheet.auto_filter.ref：给表格添加“筛选器”
- .auto_filter.ref = sheet.dimension 给所有字段添加筛选器；
- .auto_filter.ref = “A1” 给A1这个格子添加“筛选器”，就是给第一列添加“筛选器”；
workbook = load_workbook(filename = "花园.xlsx")
sheet = workbook.active
print(sheet)
sheet.auto_filter.ref = sheet["A1"]
workbook.save(filename = "花园.xlsx")
结果如下：
4、批量调整字体和样式
1）修改字体样式
- Font(name=字体名称,size=字体大小,bold=是否加粗,italic=是否斜体,color=字体颜色)
from openpyxl.styles import Font
from openpyxl import load_workbook
workbook = load_workbook(filename="花园.xlsx")
sheet = workbook.active
cell = sheet["A1"]
font = Font(name="微软雅黑",size=20,bold=True,italic=True,color="FF0000")
cell.font = font
workbook.save(filename = "花园.xlsx")
"""
这个color是RGB的16进制表示，自己下去百度学习；
"""
结果如下：
2）获取表格中格子的字体样式
from openpyxl.styles import Font
from openpyxl import load_workbook
workbook = load_workbook(filename="花园.xlsx")
sheet = workbook.active
cell = sheet["A2"]
font = cell.font
print(font.name, font.size, font.bold, font.italic, font.color)
结果如下：
3）设置对齐样式
- Alignment(horizontal=水平对齐模式,vertical=垂直对齐模式,text_rotation=旋转角度,wrap_text=是否自动换行)
- 水平对齐：‘distributed’，‘justify’，‘center’，‘leftfill’， ‘centerContinuous’，‘right，‘general’；
- 垂直对齐：‘bottom’，‘distributed’，‘justify’，‘center’，‘top’；
from openpyxl.styles import Alignment
from openpyxl import load_workbook
workbook = load_workbook(filename="花园.xlsx")
sheet = workbook.active
cell = sheet["A1"]
alignment = Alignment(horizontal="center",vertical="center",text_rotation=45,wrap_text=True)
cell.alignment = alignment
workbook.save(filename = "花园.xlsx")
结果如下：
4）设置边框样式
- Side(style=边线样式，color=边线颜色)
- Border(left=左边线样式，right=右边线样式，top=上边线样式，bottom=下边线样式)
- style参数的种类： ‘double, ‘mediumDashDotDot’, ‘slantDashDot’,’dashDotDot’,’dotted’,’hair’, ‘mediumDashed, ‘dashed’, ‘dashDot’, ‘thin’,’mediumDashDot’,’medium’, ‘thick’from openpyxl.styles import Side,Borderfrom
openpyxl import load_workbook
workbook = load_workbook(filename="花园.xlsx")
sheet = workbook.active
cell = sheet["D6"]
side1 = Side(style="thin",color="FF0000")
side2 = Side(style="thick",color="FFFF0000")
border = Border(left=side1,right=side1,top=side2,bottom=side2)
cell.border = border
workbook.save(filename = "花园.xlsx")
结果如下：
5）设置填充样式
- PatternFill(fill_type=填充样式，fgColor=填充颜色）
- GradientFill(stop=(渐变颜色1，渐变颜色2……))
from openpyxl.styles import PatternFill,GradientFill
from openpyxl import load_workbook
workbook = load_workbook(filename="花园.xlsx")
sheet = workbook.active
cell_b9 = sheet["B9"]
pattern_fill = PatternFill(fill_type="solid",fgColor="99ccff")
cell_b9.fill = pattern_fill
cell_b10 = sheet["B10"]
gradient_fill = GradientFill(stop=("FFFFFF","99ccff","000000"))
cell_b10.fill = gradient_fill
workbook.save(filename = "花园.xlsx")
结果如下：
6）设置行高和列宽
- .row_dimensions[行编号].height = 行高
- .column_dimensions[列编号].width = 列宽
workbook = load_workbook(filename="花园.xlsx")
sheet = workbook.active
# 设置第1行的高度
sheet.row_dimensions[1].height = 50
# 设置B列的宽度
sheet.column_dimensions["B"].width = 20
workbook.save(filename = "花园.xlsx")
"""
sheet.row_dimensions.height = 50
sheet.column_dimensions.width = 30
这两句代码，是将整个表的行高设置为50，列宽设置为30；
"""
结果如下：
7）合并单元格
- .merge_cells(待合并的格子编号)
- .merge_cells(start_row=起始行号，start_column=起始列号，end_row=结束行号，end_column=结束列号)
workbook = load_workbook(filename="花园.xlsx")
sheet = workbook.active
sheet.merge_cells("C1:D2")
sheet.merge_cells(start_row=7,start_column=1,end_row=8,end_column=3)
workbook.save(filename = "花园.xlsx")
结果如下：
当然，也有“取消合并单元格”，用法一致。
* .unmerge_cells(待合并的格子编号)
* .unmerge_cells(start_row=起始行号，start_column=起始列号，end_row=结束行号，end_column=结束列号)
未经允许不得转载：PythonOK » python使用openpyxl操作excel
python使用python-docx操作word
python使用PyPDF2和pdfplumber操作pdf
《Think Python》中文版
Python之路V1.3.pdf
《Python Cookbook》 3rd Edition pdf 下载
关于Python的面试题