2-1-05 文件操作 - Excel 格式文件
模块使用
#使用Excel文件操作
from openpyxl import load_workbook
#引用设置 ---单元格设置
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
Excel格式文件
1.定义和安装
Python内部未提供处理Excel文件的功能，想要在Python中操作Excel需要按照第三方的模块。
pip install openpyxl
此模块中集成了Python操作Excel的相关功能，接下来我们就需要去学习该模块提供的相关功能即可。
2. 读Excel
2.1 读sheet
2.1.1 获取Excel文件中所有sheet名称
from openpyxl import load_workbook
wb = load_workbook("files/p1.xlsx")
# 1. 获取Excel文件中所有的sheet名称
print(wb.sheetnames) # ['数据导出', '用户列表', 'Sheet1', 'Sheet2']
2.1.2 选择sheet，基于sheet名称
from openpyxl import load_workbook
wb = load_workbook("files/p1.xlsx")
sheet = wb["数据导出"]
cell = sheet.cell(1,1) # 打开sheet后，读取第一行第一列的单元格
print(cell.value) #输出单元格的值
2.1.3 选择sheet，基于索引位置
from openpyxl import load_workbook
wb = load_workbook("files/p1.xlsx")
# worksheets是可以找到所有的sheet
# ['数据导出', '用户列表', 'Sheet1', 'Sheet2']
sheet = wb.worksheets[0] # 数据导出
cell = sheet.cell(1,1) # 打开sheet后，读取第一行第一列的单元格
print(cell.value) #输出单元格的值
2.1.4 循环所有的sheet
from openpyxl import load_workbook
wb = load_workbook("files/p1.xlsx")
# 方法一：
for name in wb.sheetnames:
sheet = wb[name]
cell = sheet.cell(1,1)
print(cell.value)
# 每个sheet的第一行第一列都获取出来
# 方法二：
for sheet in wb.worksheets:
cell = sheet.cell(1,1)
print(cell.value)
# 方法三
for sheet in wb:
cell = sheet.cell(1,1)
print(cell.value)
2.2 读单元格
2.2.1 获取第N行第N列单元格（位置是从1开始）
from openpyxl import load_workbook
wb = load_workbook("files/p1.xlsx")
sheet = wb.worksheets[0] #取到了第一个sheet
# 获取第N行第N列单元格
cell = sheet.cell(1,1) #注意都是从1开始，没有0
print(cell.value) #值
print(cell.style) #样式
print(cell.font) #字体
print(cell.alignment) #排列情况：水平居中还是……
2.2.2 获取某个单元格
from openpyxl import load_workbook
wb = load_workbook("files/p1.xlsx")
sheet = wb.worksheets[0] #取到了第一个sheet
# 获取某个单元格
c1 = sheet["A2"] #直接根据Excel表格的排列方式获取
print(c1.value)
2.2.3 获取第N行所有的单元格
from openpyxl import load_workbook
wb = load_workbook("files/p1.xlsx")
sheet = wb.worksheets[0] #取到了第一个sheet
# 获取第N行所有的单元格
print(sheet[1]) # 获取第1行所有的单元格,是一个元祖里面包含了一个个的单元格
# 循环展示
for cell in sheet[1]:
print(cell,value)
2.2.4 获取所有行的数据（获取某一列数据）
from openpyxl import load_workbook
wb = load_workbook("files/p1.xlsx")
sheet = wb.worksheets[0] #取到了第一个sheet
#获取所有行的数据
#sheet.rows 获取所有每一行的数据
for row in sheet.rows:
print(row[0].value,row[1].value) #row[0] 每一行的第一列, row[1] 每一行的第一列
2.2.5 获取所有列的数据（获取某一行数据）
from openpyxl import load_workbook
wb = load_workbook("files/p1.xlsx")
sheet = wb.worksheets[0] #取到了第一个sheet
#获取所有列的数据
#sheet.columns 获取所有列
for col in sheet.columns:
print(col[0].value) #col[0] 第一行数据
2.3 读合并的单元格
一般合并的单元格都是写在最前一个单元格
from openpyxl import load_workbook
wb = load_workbook("files/p1.xlsx")
sheet = wb.worksheets[2]
# 获取第N行第N列的单元格(位置是从1开始）
c1 = sheet.cell(1, 1)
print(c1) # <Cell 'Sheet1'.A1>
print(c1.value) # 用户信息
c2 = sheet.cell(1, 2)
print(c2) # <MergedCell 'Sheet1'.B1> MergedCell 合并的意思
print(c2.value) # None
# 循环获取每一行每一列的数据
from openpyxl import load_workbook
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[2]
for row in sheet.rows:
print(row)
>>> 输出结果
(<Cell 'Sheet1'.A1>, <MergedCell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>)
(<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>) #垂直方向的合并单元格，用的还是上面那个单元格的值
(<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>)
(<MergedCell 'Sheet1'.A4>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.C4>)
(<Cell 'Sheet1'.A5>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.C5>)
注意：合并单元格中，除了第一个是有值得，其他的都是显示：
<MergedCell 'Sheet1'.A4>
# MergedCell 合并的意思，且值为空
3. 写Excel
在Excel中想要写文件，大致要分为在：
- 原Excel文件基础上写内容。
from openpyxl import load_workbook
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[0]
# 找到单元格，并修改单元格的内容
cell = sheet.cell(1, 1)
cell.value = "新的开始"
# 将excel文件保存到p2.xlsx文件中
wb.save("files/p2.xlsx")
# 覆盖原有文件p1.xlsx
wb.save("files/p1.xlsx")
- 新创建Excel文件写内容。
from openpyxl import workbook
# 创建excel且默认会创建一个sheet（名称为Sheet）
wb = workbook.Workbook()
sheet = wb.worksheets[0] # 或 sheet = wb["Sheet"]
# 找到单元格，并修改单元格的内容
cell = sheet.cell(1, 1)
cell.value = "新的开始"
# 将excel文件保存到p2.xlsx文件中
wb.save("files/p2.xlsx")
在了解了如何读取Excel和创建Excel之后，后续对于Excel中的sheet和cell操作基本上都相同。
1. 操作sheet
1.1 修改sheet名称
from openpyxl import workbook
wb = workbook.Workbook() # Sheet
# 1. 修改sheet名称
sheet = wb.worksheets[0]
sheet.title = "数据集"
wb.save("p2.xlsx")
1.2 创建sheet表并修改sheet颜色
from openpyxl import workbook
wb = workbook.Workbook() # Sheet
# 2. 创建sheet并设置sheet颜色
#创建sheet工作计划，0表示默认放在哪个位置，放在最开始的位置
sheet = wb.create_sheet("工作计划", 0)
# tabColor 颜色 1072BA这个是rgb的颜色表
sheet.sheet_properties.tabColor = "1072BA"
wb.save("p2.xlsx")
1.3 默认打开的sheet
from openpyxl import workbook
wb = workbook.Workbook() # Sheet
# 3. 默认打开的sheet
# 设置默认打开的sheet
wb.active = 0
wb.save("p2.xlsx")
1.4 拷贝sheet
from openpyxl import workbook
wb = workbook.Workbook() # Sheet
# 4. 拷贝sheet
sheet = wb.create_sheet("工作计划")
sheet.sheet_propertie
s.tabColor = "1072BA"
new_sheet = wb.copy_worksheet(wb["Sheet"])
new_sheet.title = "新的计划"
wb.save("p2.xlsx")
1.5 删除sheet
from openpyxl import workbook
wb = workbook.Workbook() # Sheet
# 5.删除sheet
"""
del wb["用户列表"]
wb.save('files/p2.xlsx')
"""
2. 单元格操作
#引用设置
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
# Alignment 对齐方式
#Border 边框
#Side 边框里面的设置：粗细、颜色等等
#Font 字体大小
#PatternFill 单元格设置背景色
#GradientFill 单元格设置渐变的背景色
1. 获取某个单元格，修改值 --方法一
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[1]
# 1. 获取某个单元格，修改值
cell = sheet.cell(1, 1)
cell.value = "开始"
wb.save("p2.xlsx")
2. 获取某个单元格，修改值 ---方法二
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[1]
sheet["B3"] = "Alex"
wb.save("p2.xlsx")
3. 获取某些单元格，修改值
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[1]
cell_list = sheet["B2":"C3"]
for row in cell_list:
for cell in row:
cell.value = "新的值"
wb.save("p2.xlsx")
sheet["B2":"C3"]
以元祖的方式存在
（
（单元格，单元格）
（单元格，单元格）
）
4. 对齐方式
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[1]
cell = sheet.cell(1, 1)
# horizontal，水平方向对齐方式："general", "left", "center", "right", "fill", "justify", "centerContinuous", "distributed"
# vertical，垂直方向对齐方式："top", "center", "bottom", "justify", "distributed"
# text_rotation，旋转角度。
# wrap_text，是否自动换行。
cell.alignment = Alignment(horizontal='center', vertical='distributed', text_rotation=45, wrap_text=True)
wb.save("p2.xlsx")
5. 边框
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[1]
cell = sheet.cell(9, 2)
cell.border = Border(
top=Side(style="thin", color="FFB6C1"), #上
bottom=Side(style="dashed", color="FFB6C1"),#下
left=Side(style="dashed", color="FFB6C1"),#左
right=Side(style="dashed", color="9932CC"),#右
diagonal=Side(style="thin", color="483D8B"), # 对角线
diagonalUp=True, # 从左下到右上
diagonalDown=True # 从左上到右下
)
wb.save("p2.xlsx")
# side的style有如下：dashDot','dashDotDot', 'dashed','dotted','double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot','mediumDashed', 'slantDashDot', 'thick', 'thin'
6.字体
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[1]
cell = sheet.cell(5, 1)
cell.font = Font(name="微软雅黑", size=45, color="ff0000", underline="single")
# underline 下划线 默认不带
wb.save("p2.xlsx")
7.背景色
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[1]
cell = sheet.cell(5, 3)
cell.fill = PatternFill("solid", fgColor="99ccff")
wb.save("p2.xlsx")
8.渐变背景色
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[1]
cell = sheet.cell(5, 5)
cell.fill = GradientFill("linear", stop=("FFFFFF", "99ccff", "000000")) # stop显示的是左中右三种颜色
wb.save("p2.xlsx")
9.宽高（索引从1开始）
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[1]
#row_dimensions 表示第几行 height 高度
sheet.row_dimensions[1].height = 50
#column_dimensions 第几列 width 款度
sheet.column_dimensions["E"].width = 100
wb.save("p2.xlsx")
10.合并单元格
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[1]
# 1.合并单元格
#方式一
sheet.merge_cells("B2:D8")
wb.save("p2.xlsx")
#方式二
sheet.merge_cells(start_row=15, start_column=3, end_row=18, end_column=8)
wb.save("p2.xlsx")
# 2.解除合并单元格
sheet.unmerge_cells("B2:D8")
wb.save("p2.xlsx")
11.写入公式
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[1]
sheet = wb.worksheets[3]
sheet["D1"] = "合计"
sheet["D2"] = "=B2*C2"
wb.save("p2.xlsx")
sheet = wb.worksheets[3]
sheet["D3"] = "=SUM(B3,C3)"
wb.save("p2.xlsx")
12.删除
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[1]
# idx，要删除的索引位置 idx=1 表示删除第一行
# amount，从索引位置开始要删除的个数（默认为1）
# delete_rows 删除第N行
# delete_cols 删除第N列
sheet.delete_rows(idx=1, amount=20)
sheet.delete_cols(idx=1, amount=3)
wb.save("p2.xlsx")
13.插入
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[1]
# idx，要删除的索引位置 idx=1 表示删除第一行
# amount，从索引位置开始要删除的个数（默认为1）
# insert_rows 删除第N行
# insert_cols 删除第N列
sheet.insert_rows(idx=5, amount=10)
sheet.insert_cols(idx=3, amount=2)
wb.save("p2.xlsx")
14.循环写内容
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[1]
# 方式一
sheet = wb["Sheet"]
cell_range = sheet['A1:C2']
for row in cell_range:
for cell in row:
cell.value = "xx"
#方式二：
# min_row=5 第5行开始
# min_col=1 第1列开始
# max_col=7 第7行结束
# max_row=10 第10列结束
for row in sheet.iter_rows(min_row=5, min_col=1, max_col=7, max_row=10):
for cell in row:
cell.value = "oo"
wb.save("p2.xlsx")
15.移动
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[1]
# 将H2:J10范围的数据，向右移动15个位置、向上移动1个位置
# "H2:J10" 选中的Excel区域
# rows = 1 表示选择的区域向下移动一位
# cols=15 表示向右移动15个位置
# 注意：rows 和 cols 向左和向上移动都是负值 -1 -15
sheet.move_range("H2:J10",rows=1, cols=15)
wb.save("p2.xlsx")
sheet = wb.worksheets[3]
sheet["D1"] = "合计"
sheet["D2"] = "=B2*C2"
sheet["D3"] = "=SUM(B3,C3)"
sheet.move_range("B1:D3",cols=10, translate=True) # translate=True 自动翻译公式
wb.save("p2.xlsx")
16.打印区域
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[1]
# print_area 打印区域
sheet.print_area = "A1:D200"
wb.save("p2.xlsx")
17.打印时，每个页面的固定表头
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[1]
# 表头的列是A:D
sheet.print_title_cols = "A:D"
# 保留第一行
sheet.print_title_rows = "1:1"
wb.save("p2.xlsx")
浙公网安备 33010602011771号