用Python设置Excel样式

任务要求：将图片上面的Excel修改成下面的Excel表格样式。

功能拆解

根据我们刚刚的一番对比分析，需要调整的格式有：表格的列宽、单元格的颜色、对齐方式和边框。
你可能会猜，是不是就要分成这四个功能块？

其实不然，因为Python中的openpyxl库整合了一些“修改单元格样式”的功能，设置起来很有规律，所以无需分为四个功能分别实现。

所谓整合，其实跟用Excel软件的工具栏处理非常类似，比如下面这个动图中展示的就是，选中单元格后，选择样式工具栏如“填充颜色”，再确认所需样式内容如“红色”，最后“点击”，样式修改就完成了。

难道说也可以用openpyxl库选择样式类别，确认样式值并执行修改，来完成样式设置？

确实是这样，而且只需：选择样式属性，定义该属性的样式值 ， 赋值修改（具体各个）单元格的样式值。

因此对于所有单元格样式的修改可以分为两个功能块：“定义单元格样式” 和 “循环修改单元格”。

再加上对于表格的操作——“打开工作表”和“保存工作簿”，以及“调整（工作表）列宽”的功能，共有五个功能块。

3. 设置Excel样式

3.1 打开工作表

复习题中，我们使用​​load_workbook()​​​打开了一个工作簿，并使用​​工作簿对象.active​​获取到了工作簿中的活动工作表。那么如何打开文件夹下所有的工作簿和其中的活动工作表呢？

“各部门利润表汇总”文件夹中的52个工作簿——“事业01部.xlsx”、“事业02部.xlsx”等等，每次打开都要重复告诉​​load_workbook()​​函数它们的不同路径。

而工作簿的路径又有很强的规律性，都是文件夹路径 + 文件名。

那如果我们能用循环遍历取到不同的文件名，再一一拼接上前面的文件夹路径，问题就不大了。所以需要用到​​os.listdir()​​​和​​for循环​​。

如果文件夹路径为：'./各部门利润表汇总/'，那么当我们把这个路径传入​​os.listdir()​​函数后，就会得到一个包含了文件夹中所有文件或文件夹名字的列表。

再通过​​for循环​​去遍历返回的列表，我们就能获取到文件夹下的每一个文件名了。

先看获取所有文件路径的代码：

# 导入模块
import os
# 设置文件夹路径
path = './各部门利润表汇总/'
# 返回当前目录下所有文件名
files = os.listdir(path)
# 循环文件名列表
for file in files:
    # 拼接文件路径
    file_path = path + file
    # 打印文件路径
    print(file_path)

 运行结果：

有了循环获取的路径，就可以去实现第一个功能块——打开工作表啦。

至此，功能块一就完成了。接下来就该对“打开的工作表”进行样式的修改了，比如功能二“调整列宽”啊，功能三“修改单元格样式”等。

但是为了方便后续讲解，后面会以单个工作表为例，让你先看到对一个工作表中样式修改的操作效果。最后在“程序实现与总结”中我们就能看到如何对所有工作表进行操作了。

3.2 调整列宽

调整工作表的列宽，需要用到​​Sheet.column_dimensions['列位置'].width​​。

这条语句可以确定列位置，并用 ​​width​​ 属性，对该列的列宽进行修改。

比如说我想让第2列的列宽为20个单位，那么我就可以用 ws.column_dimensions['B'] 先确定找到第2列。

然后使用​​ws.column_dimensions['B'].width = 20​​进行赋值。

接下来就让我们来体验一下这段代码的效果吧。

原本工作表中的列宽，基本上都为​​8​​个单位：

我们以修改material文件夹下的“事业01部_副本.xlsx”这个工作簿为例，给你看看调整列宽的效果。

代码：

from openpyxl import load_workbook

# 打开工作表
file_path = './material/事业01部_副本.xlsx'
wb = load_workbook(file_path)
ws = wb.active

# 调整第二列列宽
ws.column_dimensions['B'].width = 20

# 保存
wb.save(file_path)

 运行效果：

根据体验结果，我们会发现上面的程序中使用
​​​ws.column_dimensions['B'].width = 20​​ 实现了将 第2列 的列宽修改为20。

到这里，我们就完成了“调整列宽”功能块。接下来我们将会学习如何去定义和修改单元格的样式属性。

还记得吗？前面划分功能块时，我们类比Excel软件的设置方法，先确认样式工具栏并选择需要的样式，点击修改后，单元格的颜色才会真正的改变。使用openpyxl库操作也是一样的，要先定义好样式，再来修改单元格样式。

3.3 定义单元格样式

要想实现这个功能块，我们得根据任务要求去确定，要对单元格的哪些样式进行调整，以及该如何定义具体的样式。先来看一下代码：

首先第 3 行从openpyxl库的​​styles​​​模块中，导入了4个表示各类别的样式对象。
1）PatternFill 对象表示填充，可以用于填充颜色。所以第 6、8、10 三行代码分别定义了3种颜色样式；
2）Alignment 对象表示对齐方式，所以第 13 行代码定义了1种对齐样式；
3）Border 对象表示边框，所以第 18、20 行代码分别定义了2种边框样式；
4）第16行代码Side对象是作为Border对象的参数值，它的作用是定义边样式。

定义好样式之后，就可以去修改属性值，改变单元格的样式了。

单元格的样式属性

单元格有很多的属性，比如上一节课学过的Cell.value属性，表示单元格的值。而关于单元格的样式属性，今天我们会了解到三个，基本可以对应Excel中这三处工具栏：

）边框样式由​​Cell.border​​属性表示，2）颜色填充由​​Cell.fill​​属性表示，3）对齐方式由​​Cell.alignment​​属性表示。

以上就是本次需要用到的样式属性，接下来我们来看里面具体的样式如何定义，就能知道这些样式属性分别能存储什么样的样式效果了。

单元格样式的定义

Excel中的边框样式非常丰富，有上下左右不同位置，还有边框的线条形状粗细，这些效果在openpyxl中可以实现吗？

当然可以，边框样式由Border对象来定义，线条由Side对象定义，想要什么样的都可以。因为通过对类的不同参数设置不同的参数值，就可以定义出各种不同的样式效果。

例如，如何定义一个细线条的下边框样式？

先定义个细线条的边框线 —— ​​side = Side('thin')​​​，thin表示细的。再来定义下边为细线条的边框样式boder1 —— ​​boder1 = Border(bottom=side)​​，bottom表示底部。所以底部边框是细线条的样式就定义好了。

是不是并不像想象的那么难？我们直接来看“边框”、“填充”、“对齐”等不同属性该如何定义吧。

根据上面的图片，我们会发现：填充样式、对齐样式，与边框样式一样，都是由特定的类实例化得到的。

而具体的样式效果则由参数来确定。比如填充样式PatternFill类的fgColor参数表示前景颜色，如果将颜色的十六进制码传给该参数，就可以填充为该颜色。

例如​​fgColor = 'FFA500'​​ 表示填充的颜色为橙色。下图是常用颜色的十六进制码：

如果你需要其它颜色，可以在 Excel 中的颜色填充中去查找对应颜色的 RGB 值，如下图：

再通过去网上搜索“RGB颜色值与十六进制颜色码转换工具”，将 RGB 值转化为十六进制码。

同理，对齐方式Alignment类的horizontal参数表示水平方向上的对齐，如果参数值是'center'则表示水平居中。

3.4 循环修改单元格

由于表头、表中、表尾的样式分别不同，所以我们把这个功能块分成这3部分，分别去完成。先来看如何取出表头的单元格，并进行样式修改。

修改“表头”的单元格样式

先来观察一下第一行，表头的单元格：

 

表头是第一行的单元格，如果要取出这一行很简单，用​​ws[1]​​​就可以了。然后就可以用​​for cell in ws[1]​​取出每个单元格，再来对单元格的样式属性进行修改。

不过，你可能会问，第一行的表格已经合并了，它不就是一个单元格吗？为什么还要循环修改呢？

这是因为，对于这个大的合并单元格来说，它其实不是单元格（Cell）对象，而是合并单元格（MergedCell）对象，合并单元格对象没有这些样式的属性。所以不能直接对这个大的单元格进行样式修改。

来观察一下表中的单元格：

“表中”是中间2-9行的单元格，如果要取出这一行很简单，但是怎么取出这一个区域中的行，继而取出单元格呢？

不知道你还记不记得，我们之前接触过一个方法，可以取出工作表中一定范围的数据。

那就是工作表Sheet的​​iter_rows()​​方法，它有两个参数max_row和min_row，可以指定所取数据的行数范围（从min_row行取到max_row行为止）。

有了​​Sheet.iter_rows()​​，我们就可以根据其最大行参数值和最小行参数值，确定需要修改的范围了。

其实还有另一个问题，你可能忽略了。那就是：刚刚只是对“事业01部”进行处理，所以可以知道最大行参数是取到第9行，如果是其他工作表，表中区域可能就不是从第2-第9行了，而是从第2行到倒数第2行。

那应该如何取出不同工作表的倒数第二行的行号呢？这时候我们需要一个新知识：Sheet.max_row。

​​max_row​​是工作表Sheet的一个属性，返回的属性值是一个整数。这个整数值代表了工作表的最大行数。如果工作表共有9行，那Sheet.max_row 返回的就是整数9。

Sheet.max_row的功能:

# 导入模块
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
# 打开工作表
file_path = './material/事业02部_副本.xlsx'
wb = load_workbook(file_path)
ws = wb.active

# 打印最大行max_row
print(ws.max_row)

 

但是我们要想调整“表中”的单元格样式，还需要能够取到每一个单元格。这里我们需要用到for循环的嵌套：

for row in ws.iter_rows(min_row=2, max_row = ws.max_row - 1):
    for cell in row:
        print(cell)

在上一段代码for循环读取“表中”每一行的基础上，循环体中增加一个for循环，去遍历这一行row中的单元格cell。这样就能用for循环的嵌套，来取出“表中”的所有单元格了。

终于取出了单元格，可以正式进入主题：修改“表中”单元格的样式。

修改“表尾”的单元格样式

由于表尾只有一行，我们只需要循环遍历这一行的所有单元格，并对其属性进行赋值即可。

完整代码：

from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Alignment, Side, Border


# 定义表头颜色样式为橙色
header_fill = PatternFill('solid', fgColor='FF7F24')
# 定义表中颜色样式为淡黄色
content_fill = PatternFill('solid', fgColor='FFFFE0')
# 定义表尾颜色样式为淡桔红色
bottom_fill = PatternFill('solid', fgColor='EE9572')

# 定义对齐样式横向居中、纵向居中
align = Alignment(horizontal='center', vertical='center')

# 定义边样式为细条
side = Side('thin')
# 定义表头边框样式，有底边和右边
header_border = Border(bottom=side, right=side)
# 定义表中、表尾边框样式，有左边
content_border = Border(left=side)

# 打开工作表
file_path = './material/事业02部_副本.xlsx'
wb = load_workbook(file_path)
ws = wb.active

# 循环第一行单元格，调整表头样式
for cell in ws[1]:
    # 设置单元格填充颜色
    cell.fill=header_fill
    # 设置单元格对齐方式
    cell.alignment=align
    # 设置单元格边框
    cell.border=header_border

# 获取最后一行行号
row_num =ws.max_row

# 从第二行开始，循环到倒数第二行
for row in ws.iter_rows(min_row= 2  , max_row=  (row_num-1)  ):
    # 循环取出单元格，调整表中样式
    for cell in row:
        cell.fill =content_fill
        cell.alignment =align
        cell.border =content_border

# 循环最后一行单元格，调整表尾样式
for b in ws[row_num]:
    b.fill =bottom_fill
    b.alignment =align
    b.border =content_border
    


# 保存
wb.save(file_path)

运行结果：

3.5 保存工作簿

实现了对于单元格样式的修改，项目任务就算完成了，但是我们可不能忘记了保存工作簿，不然对于该工作簿中所有的修改可都不会保存。

4. 程序实现与总结

4.1 功能块合并

接下来我们会进入程序实现与总结。先来回顾一下我们之前的目标：将52个事业部的利润表修改为统一的样式，需要修改的样式有以下四个：

对于这些样式修改，我们主要分为三个功能块去实现：功能块二：调整列宽、功能块三：定义单元格样式、功能块四：循环修改单元格。

1.调整列宽用到的语句是​​Sheet.column_dimensions['列位置'].width = 列宽​​。对于这52个工作表，所有列都需要修改，我们使用的代码是这样的：

2.定义单元格样式中，我们知道了这次需要修改的填充颜色、边框、对齐方式，分别对应单元格Cell的fill、border、alignment三个属性。而这三个属性需要的值，分别是PatternFill、Border、Alignment对象。

对于单元格样式的定义，是这样实现的：

3.循环修改单元格，本来上面已经定义好的单元格样式，修改样式时对所有单元格的样式属性进行赋值即可。

但复杂的是，表头、表中、表尾的单元格样式各不相同，这时候要分别用3个for循环，对各行的单元格进行不同样式的修改。

那么具体如何循环修改单元格，可以使用以下代码：

最终的项目代码整体上是这样的：

先导入模块 ➡️ 定义单元格样式 ➡️ 打开工作表 ➡️ 调整列宽 ➡️ 修改单元格样式 ➡️ 保存工作簿。

这里要注意两点大的变化：1）调整列宽、循环修改单元格、保存工作簿都相对功能一中的for循环缩进了一层，表明这些是功能一中for循环的循环体。

主要的新知识集中在openpyxl库的styles模块，里面有PatternFill、Border、Alignment三个对象分别可以用于定义“填充”样式、“边框”样式、“对齐方式”样式。这些对象分别有不同的参数，不同的参数值设置会有不同的样式效果。

将定义好的样式对象，分别赋值给对应的单元格Cell的fill、border、alignment属性，就可以修改单元格的这三个样式。

另外我们还学习了如何调整列宽，一条语句即可：​​Sheet.column_dimensions['列位置'].width = 列宽​​

还有一个新知识是：工作表的max_row属性，用它可以知道工作表对象的最大行数（最后一行的行号）。

# 导入模块
import os
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Alignment, Side, Border

# 定义表头颜色样式为橙色
header_fill = PatternFill('solid', fgColor='FF7F24')
# 定义表中颜色样式为淡黄色
content_fill = PatternFill('solid', fgColor='FFFFE0')
# 定义表尾颜色样式为淡桔红色
bottom_fill = PatternFill('solid', fgColor='EE9572')

# 定义对齐样式横向居中、纵向居中
align = Alignment(horizontal='center', vertical='center')

# 定义边样式为细条
side = Side('thin')
# 定义表头边框样式，有底边和右边
header_border = Border(bottom=side, right=side)
# 定义表中、表尾边框样式，有左边
content_border = Border(left=side)

# 设置文件夹路径
path = './各部门利润表汇总/'
# 返回当前目录下所有文件名
files = os.listdir(path)

# 循环文件名列表
for file in files:
    # 拼接文件路径
    file_path = path + file
    # 打开工作簿
    wb = load_workbook(file_path)
    # 打开工作表
    ws = wb.active

    # 调整列宽
    ws.column_dimensions['A'].width = 10
    ws.column_dimensions['B'].width = 25
    ws.column_dimensions['C'].width = 50
    ws.column_dimensions['D'].width = 10
    ws.column_dimensions['E'].width = 20
    ws.column_dimensions['F'].width = 15

    # 循环第一行单元格，调整表头样式
    for cell in ws[1]:
        # 设置单元格填充颜色
        cell.fill = header_fill
        # 设置单元格对齐方式
        cell.alignment = align
        # 设置单元格边框
        cell.border = header_border

    # 获取最后一行行号
    row_num = ws.max_row

    # 从第二行开始，循环到倒数第二行
    for row in ws.iter_rows(min_row=2, max_row=(row_num-1)):
        # 循环取出单元格，调整表中样式
        for cell in row:
            cell.fill = content_fill
            cell.alignment = align
            cell.border = content_border

    # 循环最后一行单元格，调整表尾样式
    for cell in ws[row_num]:
        cell.fill = bottom_fill
        cell.alignment = align
        cell.border = content_border

    # 保存
    wb.save(file_path)