一、使用样式（working with styles）
介绍（introduction）：
一个空白的sheet，你可以按照自己的想象设置字体的大小、颜色、字体、下划线等，对于每个单元格可以设置单元的相关属性（填充颜色、边框），单元格中值的对齐方式，sheet的保护等进行设置，
1.1 设置单元格中字体的大小、颜色和下划线
1.11 函数：
from openpyxl.styles import Font, PatternFill1.12 font的语法：
font = Font(name='Calibri', # 字体名称，注意中文字体前加u
size=11, # 字号的大小，默认
bold=False, # True(加粗),False(不加粗)
italic=False, # True(倾斜),False(不倾斜)
vertAlign=None, # 'superscript'(上标)/'subscript'(下标)
underline='none',# 'single'(单下划线）/'double'(双下划线)/
# singleAccounting（会计用单下划线）/
# doubleAccounting（会计用双下划线）
strike=False, # (True，显示删除线）,(False,不显示删除线）
color='FF000000' # 字体的颜色
)经验：在设置color的时候可以现在excel中选定好颜色，然后复制HEX，注意不要复制#
1.13 实例：
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
# 或者也有这样的写法
# from openpyxl.styles import *
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl import load_workbook
wb = load_workbook(filename=文件名称)
ws = wb.active
# 如果有DataFrame，可以将其写入ws中
rows = dataframe_to_rows(df, index=False, header=True) # df是一个DataFrame，index=None是否包含行索引，默认是True，header=None，是否包含列名，默认是True
for r_idx, row in enumerate(rows, start=4):
for c_idx, value in enumerate(row, start=1):
new_worksheet.cell(row=r_idx, column=c_idx, value=value)
for col_idx in range(1, 19):
cell = ws.cell(row=4, column=col_idx)
cell.font = Font(size=10,name='DengXian')from openpyxl import Workbook
from openpyxl.styles import *
wb = Workbook()
ws = wb.active
# 设置A1单元格中的文本水平和垂直居中对齐，并自动换行
ws['A1'] = '默认'
ws['A1'].alignment = Alignment()
ws['A2'] = '水平居中'
ws['A2'].alignment = Alignment(horizontal='center')
ws.cell(row = 2,column=1).fill=PatternFill(start_color='000000',end_color='000000',fill_type='solid')
ws.cell(row = 2,column=1).font = Font(color='F2F2F2',bold=True,size = 20,underline='single',name = 'DengXian')
ws['A3'] = '水平居中,自动换行'
ws['A3'].alignment = Alignment(horizontal='center',wrap_text=True)
ws.cell(row = 3,column=1).fill=PatternFill(start_color='F27CB4',end_color='F27CB4',fill_type='solid')
ws.cell(row = 3,column=1).font = Font(color='000000',size = 20,strike='True',name = '宋体')
wb.save('example.xlsx')输出：
dataframe_to_rows的语法和介绍
1.2 单元格填充颜色
1.21 函数：
from openpyxl.styles import PatternFill1.22 语法：
PatternFill是用于设置单元格填充样式的类。它允许你设置单元格的填充类型、颜色和图案类型，从而创建丰富多彩的单元格样式。
PatternFill类的构造函数有两种不同的形式，分别是：
PatternFill(fill_type=None, #表示填充类型，例如：'solid'、'darkDown'等
fgColor=None, #表示前景色，即填充的颜色
bgColor=None, #表示背景色，一般用于图案填充
patternType=None #表示填充的图案类型，例如：'solid'、'darkDown'等
)
和
PatternFill(fill_type=None,
start_color=None, # 表示填充的起始颜色
end_color=None # 表示填充的结束颜色
)PatternFill的参数fill_type和patternType的选项
|
对于fill_type和patternType不等于solid的情况的颜色http://t.csdnimg.cn/uQk8C
图来自源1.23 实例：
def find_color(values,lists,work_sheet,n,yn,color,n2):
# values:需要查找的值
# lists：在那个list查找
# work_sheet：在那个sheet中操作
# n:第几行
# yn :是否需要填充颜色
#color:填充的颜色
# n2:填充数量
from openpyxl.styles.colors import Color
from openpyxl.styles import PatternFill
if values in lists:
# 获取值在列表中的位置
index = lists.index(values)+1
# 获取列字母
column_letter = work_sheet[n][index].column_letter
else:
print(values)
if yn == "Y":
for i in range(index,index+n2):
cell = work_sheet.cell(row=n, column=i) # 获得第n 行第i列的单元格
cell.fill = PatternFill(start_color=color, end_color=color, fill_type='solid')
work_sheet.cell(row=n, column=i，value= "值")#获得第n 行第i列的单元格并将值写入到
#单元格中
print(work_sheet.cell(row=n, column=i).value)# 读取单元格的值
from openpyxl import Workbook
from openpyxl import load_workbook
wb = load_workbook(r'.\test.xlsx')
ws = wb['sheet3']
list1 = ['column1','column2','column3','column4']
for i in range(1,5):
print(ws.cell(row=1, column=i).value)
ws.cell(row=1, column=i,value=list1 [i-1])#获得第n 行第i列的单元格并将值写入到
print(ws.cell(row=1, column=i).value)
values = ['column1','column2','column3','column4']
color = ['B7DEE8','F79646', 'E6B8B7','F79646']
number=[1,1,1,1]
for i in range(0,len(values)-1):
find_color(values = values[i]
,lists = values
,work_sheet = ws
,n = 1
,yn = "Y"
,color = color[i]
,n2 = number[i])
wb.save(.\test2.xlsx')output :
for i in range(0,len(values)):
find_color(values = values[i]
,lists = values
,work_sheet = ws
,n = 1
,yn = "Y"
,color = color[i]
,n2 = number[i])如果将代码改成这样，会报错，哪个大神知道什么原因，请指教。谢谢！非常感谢！
1.3 单元格对其方式
1.31 函数
from openpyxl.styles import Alignment1.32 语法
alignment = Alignment(horizontal='general', #水平对齐方式，可选值为center、left、right
vertical='bottom', #垂直对齐方式，可选值
#为center、top、bottom
text_rotation=0, #文本旋转角度，取值范围为-90到90，默认为0
wrap_text=False, #是否自动换行，取值为True或False，默认为False
shrink_to_fit=False, #是否缩小字体以适应单元格宽度，取值为True或False
indent=0 #缩进级别，取值为0到15，默认为0
)实例：简单的介绍一下几个常用的案例
from openpyxl import Workbook
from openpyxl.styles import *
wb = Workbook()
ws = wb.active
# 设置A1单元格中的文本水平和垂直居中对齐，并自动换行
ws['A1'] = '默认'
ws['A1'].alignment = Alignment()
ws['A2'] = '水平居中'
ws['A2'].alignment = Alignment(horizontal='center')
ws.cell(row = 2,column=1).fill=PatternFill(start_color='000000',end_color='000000',fill_type='solid')
ws.cell(row = 2,column=1).font = Font(color='F2F2F2',bold=True,size = 20,underline='single',name = 'DengXian')
ws['A3'] = '水平居中,自动换行'
ws['A3'].alignment = Alignment(horizontal='center',wrap_text=True)
ws.cell(row = 3,column=1).fill=PatternFill(start_color='F27CB4',end_color='F27CB4',fill_type='solid')
ws.cell(row = 3,column=1).font = Font(color='000000',size = 20,strike='True',name = '宋体')
wb.save('example.xlsx')输出： 
从结果中可以看出在设置单元格的对齐方式的时候，需要考虑字体，颜色，行高和列宽，结合以上的设置，才能设计更好的表格。
1.4 设置数字格式
1.41 常见的数字格式
| ”General“ | 通用格式 |
| ‘0.00%’ | 百分比格式（小数点后有几个0就是保留几位小数） |
| ‘€#,##0.00’ | 货币格式 |
| ‘0.00E+00’ | 科学计数法格式 |
| ‘yyyy-mm-dd’ | 日期格式 |
| ‘hh:mm:ss | 时间格式 |
| 自定义格式 | 可以使用Excel中的自定义格式代码，例如’0.00" kg"’ |
| 0.00 | 保留两位小数， 同理小数点之后有几个0就是保留几个小数 |
| #,##0 | 千分位表示 |
| #,##0.00 | 千分位表示并保留两位小数 |
| '0.00;[Red]-0.00' | 负数显示为红色，并保留两位小数 |
实例1：
from openpyxl import Workbook
from openpyxl.styles import *
wb = Workbook()
ws = wb.active
# 设置单个单元格的数字格式
numbers_list = [10,0.34345,.0312456,12345679,123456789,'2023/04/05',12.34566,10235,10234.123,100000]
format_list = ['General','0.00%','0%','€#,##0.00','0.00E+00','yyyy-mm-dd','0.00','#,##0','#,##0.00','#,##0_);(#,##0)']
for i in range(1,len(numbers_list)):
ws[f'A{i}'] = numbers_list[i-1]
ws[f'A{i}'] .number_format = format_list[i-1]
# 对一整行设置格式
for row in ws.iter_rows(min_row=2, max_row=10, min_col=1, max_col=1):
for cell in row:
cell.number_format = '0.00;[Red]-0.00'
wb.save('example.xlsx')输出：
总结：再最后的iter_rows，可以通过控制参数来确定你需要设定格式的行数、列数和位置。如果你想手动输入的时候（即空行）也能设置为该格式，可以将最大行的值设置的稍微大一些。
使用openpyxl.styles.numbers模块中的numbers设置数字格式
from openpyxl.styles import numbers以下是一些常用的numbers常量：
实例2：
FORMAT_GENERAL : 通用格式，不应用任何特定的数字格式
FORMAT_TEXT : 文本格式，
FORMAT_NUMBER : 数字格式，包括整数、小数、百分比等
FORMAT_PERCENTAGE : 百分比格式，将数字乘以100并显示为百分比形式
FORMAT_DATE : 日期格式
FORMAT_TIME : 时间格式
FORMAT_DATETIME : 日期时间格式
FORMAT_SCIENTIFIC : 科学计数法格式，将数字显示为科学计数法形式
FORMAT_ACCOUNTING : 会计格式，将数字显示为会计格式，包括货币符号、千位分隔符等
from openpyxl import Workbook
from openpyxl.styles import *
wb = Workbook()
ws = wb.active
# 设置单个单元格的数字格式
numbers_list = [10,0.34345,.0312456,12345679,123456789,'2023/04/05',12.34566,10235,10234.123,100000]
format_list = ['General','0.00%','0%','€#,##0.00','0.00E+00','yyyy-mm-dd','0.00','#,##0','#,##0.00','#,##0_);(#,##0)']
for i in range(1,len(numbers_list)):
ws[f'A{i}'] = numbers_list[i-1]
ws[f'A{i}'] .number_format = format_list[i-1]
format_list2 = ['numbers.FORMAT_GENERAL','numbers.FORMAT_PERCENTAGE_00','numbers.FORMAT_PERCENTAGE',
'numbers.FORMAT_ACCOUNTING','numbers.FORMAT_SCIENTIFIC','numbers.FORMAT_DATE','numbers.FORMAT_NUMBER_00',
'numbers.FORMAT_ACCOUNTING_#,##0','numbers.FORMAT_TEXT','numbers.FORMAT_NUMBER_00']
for i in range(1,len(numbers_list)):
ws[f'B{i}'] = numbers_list[i-1]
ws[f'B{i}'] .number_format = format_list2[i-1]
# 对一整列数字格式进行设置
for cell in ws.iter_cols(min_col=1, max_col=1):
for c in cell:
c.number_format = "0%"
for cell in ws.iter_cols(min_col=2, max_col=2):
for c in cell:
c.number_format = numbers.FORMAT_PERCENTAGE_00
wb.save('example.xlsx')注意：此处在使用 numbers.FORMAT_PERCENTAGE_00的时候会出现程序运行成功，但是在保存的表格中并未按照设定的格式展示数据的情况，所以建议使用1.41使用的方法设定格式。
1.5 对一整行/列填充和去除颜色
去除颜色的原理就是将填充的颜色设置为白色。
import openpyxl
from openpyxl.styles import PatternFill
# 打开一个工作簿
workbook = openpyxl.load_workbook('your_workbook.xlsx')
# 选择一个工作表
sheet = workbook.active
# 对一整行进行填充颜色
for cell in sheet[1]: # 假设我们选择第一行
cell.fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type = "solid")
# 去除一整行的颜色
for cell in sheet[1]: # 假设我们选择第一行
cell.fill = PatternFill(start_color="FFFFFFFF", end_color="FFFFFFFF", fill_type = "solid")
# 对一整列进行填充颜色
for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=1): # 假设我们选择第一列
for cell in row:
cell.fill = PatternFill(start_color="00FF00", end_color="00FF00", fill_type = "solid")
# 去除一整列的颜色
for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=1): # 假设我们选择第一列
for cell in row:
cell.fill = PatternFill(start_color="FFFFFFFF", end_color="FFFFFFFF", fill_type = "solid")
# 保存工作簿
workbook.save('your_workbook.xlsx')1.6 根据键值填写特定单元格、行、列的值
1.61 填写特定单元格的值
背景：很多excel表格是有格式的，如果存在一些数据是不能被修改的，我们如何在不改变其他单元格或者主键的顺序的情况下将数据填写正确呢！
分析：首先我们需要知道单元格的位置信息和需要填充的内容。然后根据主键将值填写在特定的单元格内。
实例：
import pandas as pd
sales = ['张三','李四','王五']
ages = [12,14,13]
df = pd.DataFrame(zip(sales,ages),columns=['sales','ages'])
print(df)
from openpyxl import Workbook
wb = Workbook() # 新建一个工作簿
ws = wb.active
for i in range(0,len(sales)):
ws[f'A{i+2}'].value = sales[-i-1]
ws[f'A{1}'].value = 'sales'
ws['B1'].value = 'ages'
# 此时ws中的第一列的值为'sales'
values = ws.values
df2 = pd.DataFrame(values)
print(df2)
# 我们需要sales的年龄填写到相对应的B列中
for rows in range(2,5):
if ws[f'A{rows}'].value in sales :
ws[f'B{rows}'].value = df['ages'][df.loc[df['sales'] ==ws[f'A{rows}'].value ].index[0]]
# 此时ws中的第一列的值为'sales'
values = ws.values
df2 = pd.DataFrame(values)
print(df2)
总结：在对特定的单元格填写值的程序中，
第一步：建立了一个DataFrame，实际工作中可能需要你读取一个excel的sheet，或者先对数据进行处理，然后得到相应DataFrame。
第二步：新建了一个sheet，实际工作中如果有文件可以直接读取文件，也可以先创建一个sheet（需要注意的是，我们新建的sheet在保存的时候是没有任何格式的。此时需要我们设置格式，如果你想偷懒，可以建立一个模板，模板中可以先设定格式，当我们使用openpyxl打开、写入和保存之后格式还存在。）
第三步，将openpyxl打开的sheet中的值，转换成DataFrame格式。这样的好处就是如果你想对sheet中某一列数据进行替换，则可以根据主键匹配其他已经处理好的的数据。这样做的好处是，如果表格中数据被不需要更新部分的数据是其他人手工更新的，可以保留，同时可以保留原有的顺序。不足之处，如果是匹配其他的数据（已经处理的）为何不直接使用pandas处理之后，再写到sheet中呢！
1.62 整行填充值、颜色和设置居中、加粗
首先，我们需要知道如何获得整行
我总结了三种方案用于获得整行
import pandas as pd
sales = ['张三','李四','王五']
ages = [12,14,13]
df = pd.DataFrame(zip(sales,ages),columns=['sales','ages'])
print(df)
from openpyxl import Workbook
wb = Workbook() # 新建一个工作簿
ws = wb.active
for i in range(0,len(sales)):
ws[f'A{i+2}'].value = sales[-i-1]
ws[f'A{1}'].value = 'sales'
ws['B1'].value = 'ages'
# 此时ws中的第一列的值为'sales'
values = ws.values
df2 = pd.DataFrame(values)
print(df2)
# 我们需要sales的年龄填写到相对应的B列中
for rows in range(2,5):
if ws[f'A{rows}'].value in sales :
ws[f'B{rows}'].value = df['ages'][df.loc[df['sales'] ==ws[f'A{rows}'].value ].index[0]]
# 此时ws中的第一列的值为'sales'
values = ws.values
df2 = pd.DataFrame(values)
print(df2)
out:
0 1
0 sales ages
1 王五 13
2 李四 14
3 张三 12
# 方案一：
row_4 = ws[4]
for cell in row_4:
print(cell.value)
方案二：
for row in ws.iter_rows(min_row=row_number, max_row=row_number, min_col=1, max_col=ws.max_column):
for cell in row:
print(cell .value)
方案三：
for i in range(1,ws.max_column+1):
ws.cell(row=4, column=i)
print(ws.cell(row=4, column=i).value)
此时就可以根据这种方案修改每行的值、单元格的设置。
方案一：
fill2 = PatternFill(start_color='E98300', end_color='E98300', fill_type='solid')
font = Font(name='DengXian', size=8,bold=True)
row_4 = ws[4]
for cell in row_4:
cell.fill=fill2
cell.font = font
wb.save(r'.\test.xlsx')
方案二：
fill2 = PatternFill(start_color='00B0F0', end_color='00B0F0', fill_type='solid')
font = Font(name='DengXian', size=13,bold=True)
alignment = Alignment(horizontal='center', vertical='center',wrap_text=True)
for row in ws.iter_rows(min_row=4, max_row=4, min_col=1, max_col=ws.max_column):
for cell in row:
cell.fill=fill2
cell.font=font
cell.alignment=alignment
wb.save(r'.\test.xlsx')
方案三：
fill2 = PatternFill(start_color='FABF8F', end_color='FABF8F', fill_type='solid')
font = Font(name='DengXian', size=13,bold=True,color='FF0000')
alignment = Alignment(horizontal='right', vertical='center',wrap_text=False)
for i in range(1,ws.max_column+1):
ws.cell(row=4, column=i)
print(ws.cell(row=4, column=i).value)
ws.cell(row=4, column=i).fill=fill2
ws.cell(row=4, column=i).font=font
ws.cell(row=4, column=i).alignment=alignment
wb.save(r'.\test.xlsx')
1.7 设置列宽和行高
1.71 设置固定的行高和列宽
在openpyxl中，可以使用column_dimensions和row_dimensions属性来设置列宽和行高。以下是如何设置列宽和行高的示例代码：
from openpyxl import Workbook
# 创建一个新的Workbook对象
wb = Workbook()
# 选择第一个工作表
ws = wb.active
# 设置第一列的宽度为20
ws.column_dimensions['A'].width = 20
# 设置第一行的高度为30
ws.row_dimensions[1].height = 30
# 保存工作表
wb.save('example.xlsx')
在这个例子中，我们创建了一个新的工作簿（Workbook）对象，并选择了第一个工作表（Worksheet）。然后，我们使用column_dimensions和row_dimensions属性来设置列宽和行高。我们通过指定列的字母来选择列，通过指定行的索引来选择行。最后，我们保存了工作簿为一个名为example.xlsx的文件。
请注意，设置的列宽和行高只会影响到有实际数据的列和行。不会影响到没有数据的列和行。如果要设置整个工作表的列宽和行高，可以使用iter_cols和iter_rows方法来遍历所有的列和行，并设置它们的宽度和高度。
1.72 根据一列中值的长度设置行高和列宽
未更新
1.8 openpyxl中的迭代行和单元格
在excel中有行和列，如何迭代行和列呢！openpyxl给出了几种方法：
iter_rows、iter_cols、rows和columns
1.81 iter_rows
语法：
worksheet.iter_rows(min_row=1, # 最小行
max_row=None, # 最大行
min_col=1, # 最小列
max_col=None, # 最大列
values_only=False # 为True，只得到单元格的值
)你是否疑问为什么 max_row和max_column 这两个参数的默认值是None呢！
其实两个参数默认是None等价于 max_row = ws.max_row,max_col = ws.max_column
但是为什么默认值不是max_row = ws.max_row,max_col = ws.max_column呢！两者之间还是有些许不同之处的，
例如：在写代码的时候，并没有注意max_row和max_col的默认值，而是使用max_row = ws.max_row,max_col = ws.max_column+1代替，结果在每次运行之后都会新增一列（空），
你可能会反驳你都使用max_col = ws.max_column+1肯定会增加1列呀！对确实是我书写问题，最大的是我对ws.max_column的理解不到位，但这不是重点，重点是AI给我的分析：
- 明确设置最大值：可能会创建一些不必要的空单元格对象，尤其是当表格有空行或空列时
实际的应用中建议使用默认值，因为代码会更简洁，同时运行速度更快。
values_only =False或者True，这个参数目前我用到的案例是：如果在你的表格中有一个单元格是公式，在默认是False时你对单元格中值进行对比或者其他操作的时候会出错，此时使用values_only =True,会得到该单元格公式计算的值。
worksheet.iter_cols(min_row=1, # 最小行
max_row=None, # 最大行
min_col=1, # 最小列
max_col=None, # 最大列
values_only=False # 为True，只得到单元格的值
)
扫一扫
968
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
