openpyxl模块介绍
openpyxl模块是一个读写Excel 2010文档的Python库,如果要处理更早格式的Excel文档,需要用到额外的库,openpyxl是一个比较综合的工具,能够同时读取和修改Excel文档。其他很多的与Excel相关的项目基本只支持读或者写Excel一种功能。
安装openpyxl模块
openpyxl是一个开源项目,这里使用如下命令安装openpyxl模块
pip3 install openpyxl
一.作成Excel文件
1.作成空的excel
import openpyxl
if __name__ == '__main__':
#作成一个新的空的Excel
wb = openpyxl.Workbook()
# 保存
wb.save('example.xlsx')
2.变更默认sheet页的名称
import openpyxl
if __name__ == '__main__':
wb = openpyxl.Workbook
#当前打开的sheet页 wb.active
ws = wb.active
# 更改默认名称Sheet`
ws.title = "WorkSheetTitle"
# 保存
wb.save('example.xlsx')
3.作成含多个sheet页的Excel
import openpyxl
if __name__ == '__main__':
wb = openpyxl.Workbook()
#当前打开的sheet页 wb.active
ws = wb.active
# #更改默认名称Sheet`
ws.title = "WorkSheetTitle"
# 定义第二个sheet页
ws2 = wb.create_sheet("NewWorkSheet2")
# 定义第三个sheet页
# `0` 的设定 会将该sheet页 置于wb最前面
ws3 = wb.create_sheet("NewWorkSheet3", 0)
# 保存
wb.save('example.xlsx')
4.sheet页WS的tab颜色变更
import openpyxl
if __name__ == '__main__':
wb = openpyxl.Workbook()
ws = wb.active
# 更改默认名称Sheet`
ws.title = "WorkSheetTitle"
# WS的tab颜色设定
ws.sheet_properties.tabColor = "1072BA"
# 保存
wb.save('example.xlsx')
二.单元格赋值
1.指定坐标
if __name__ == '__main__':
wb = openpyxl.Workbook()
ws = wb.active
# 更改默认名称Sheet`
ws.title = "WorkSheetTitle"
# 给单元格赋值
ws["A1"] = "HOGE"
ws["B1"] = "FUGA"
# 保存
wb.save('example.xlsx')
2.指定行列
if __name__ == '__main__':
wb = openpyxl.Workbook()
ws = wb.active
# 更改默认名称Sheet`
ws.title = "WorkSheetTitle"
# 指定行列给单元格赋值
ws.cell(row=4, column=2, value=10)
# 保存
wb.save('example.xlsx')
3.指定范围
import openpyxl
if __name__ == '__main__':
wb = openpyxl.Workbook()
ws = wb.active
# 更改默认名称Sheet`
ws.title = "worksheettitle"
# 指定行列给单元格赋值
v = 0
for i in range(1,10):
for n in range(1,10):
ws.cell(row=i, column=n, value=v)
v += 1
# 保存
wb.save('example.xlsx')
4.一次输出行
import openpyxl
# column名
column_title = ["FirstName", "LastName"]
if __name__ == '__main__':
"""
CELL放入值
"""
wb = openpyxl.Workbook()
ws = wb.active
# 更改默认名称Sheet`
ws.title = "worksheettitle"
# column名和値顺序放入单元格中
rows = [
column_title,
["Tarou", "Tanaka"],
["Tarou", "Suzuki"],
["Tarou", "Uchiayama"],
]
for row in rows:
ws.append(row)
# 保存
wb.save('example.xlsx')
一次输出行运行结果
5.单元格内换行
import openpyxl
if __name__ == '__main__':
"""
单元格内换行
"""
wb = openpyxl.Workbook()
ws = wb.active
# 更改默认名称Sheet`
ws.title = "WorkSheetTitle"
# 单元格内换行
ws['A1'] = "A\nB\nC"
ws['A1'].alignment = openpyxl.styles.Alignment(wrapText=True)
# 保存
wb.save('example.xlsx')
单元格内换行运行结果图
三.设置单元格的style
1.style文稿
https://openpyxl.readthedocs.io/en/default/styles.html#cell-styles-and-named-styles
2.设置字体font
import openpyxl
if __name__ == '__main__':
"""
设置字体font
"""
wb = openpyxl.Workbook()
ws = wb.active
# 更改默认名称Sheet`
ws.title = "worksheettitle"
# 设置font
font = openpyxl.styles.Font(
name = "宋体",
size = 15,
)
a1 = ws["A1"]
a1.font = font
a1.value = "TEST"
# 保存
wb.save('example.xlsx')
3.单元格边框border
import openpyxl
from openpyxl.styles import Border, Side
if __name__ == '__main__':
"""
设置单元格style
"""
wb = openpyxl.Workbook()
ws = wb.active
# 更改默认名称Sheet`
ws.title = "worksheettitle"
# 设置单元格border的style
border = Border(
left=Side(
border_style="thin",
color="FF0000"
),
right=Side(
border_style="thin",
color="FF0000"
),
top=Side(
border_style="thin",
color="FF0000"
),
bottom=Side(
border_style="thin",
color="FF0000"
)
)
b2 = ws["B2"]
b2.border = border
b2.value = "TEST"
# 保存
wb.save('example.xlsx')
单元格边框style 运行结果图
4.合并单元格
import openpyxl
if __name__ == '__main__':
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "worksheettitle"
# 合并单元格
ws.merge_cells("A1:E1")
ws["A1"] = "HOGE"
# 保存
wb.save('example.xlsx')
合并单元格 运行效果图
5.单元格填充颜色
import openpyxl
from openpyxl.styles import PatternFill
if __name__ == '__main__':
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "worksheettitle"
# 单元格填充颜色
fill = PatternFill(fill_type='solid',
fgColor='FFFF0000')
b2 = ws["B2"]
b2.fill = fill
b2.value = "TEST"
# 保存
wb.save('example.xlsx')
单元格填充颜色 效果图
四.hyperlink超链接
import openpyxl
if __name__ == '__main__':
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "worksheettitle"
# 作成第二个sheet页 名称胃example
ws2 = wb.create_sheet("example")
# 设置超链接 到“example”sheet页 鼠标定格在A5单元格
ws["A1"] = "Link"
ws["A1"].hyperlink = "example.xlsx#example!A5"
# 保存
wb.save('example.xlsx')
五.读Excel文档
from openpyxl import Workbook, load_workbook
wb = load_workbook('./example.xlsx')
ws = wb.active
for row in ws:
for cell in row:
print(cell)