精通pandas：用OpenPyXL包设置Excel工作表

pandas与Excel工作表交互的第2种实现方式是结合OpenPyXL包来进行。OpenPyXL也支持Excel对象模型，可以操作工作簿、工作表、单元格、图表等对象。相对于xlwings，OpenPyXL的主要特点是不依赖Excel，即在计算机上没有安装Excel的情况下也可以打开Excel文件进行编辑修改。缺点是功能没有xlwings的全面。[大谦Excel，dqexcel点com]

设置边框

【问题描述】

用OpenPyXL包设置单元格区域的边框。

【示例11-6】

本例使用示例11-1的数据，请给数据所在单元格区域添加边框。内边框设置为黑色细线，外边框设置为红色粗线。

编写下面的OpenPyXL代码：

code.python

import openpyxl
from openpyxl.styles import Border, Side
# 打开Excel文件
path = 'D:/Samples/ch11/OpenPyXL/01 设置边框/班级检查.xlsx'
wb = openpyxl.load_workbook(path)
# 获取当前活动的工作表
ws = wb.active
# 定义多种边线类型
border_thin_black = Border(left=Side(border_style='thin', color='000000'),
                           right=Side(border_style='thin', color='000000'),
                           top=Side(border_style='thin', color='000000'),
                           bottom=Side(border_style='thin', color='000000'))
border_bold_red = Border(left=Side(border_style='medium', color='FF0000'),
                         right=Side(border_style='medium', color='FF0000'),
                         top=Side(border_style='medium', color='FF0000'),
                         bottom=Side(border_style='medium', color='FF0000'))
# 将单元格B3:I16的边线全部设置为第二种边线类型
for row in range(3, 17):
    for col in range(2, 10):
        ws.cell(row=row, column=col).border = border_thin_black
# 遍历单元格区域B列单元格，设置左边线为第一种边线类型，其他边线为第二种边线类型
for row in range(3, 17):
    cell = ws.cell(row=row, column=2)
    cell.border = Border(left=Side(border_style='medium', color='FF0000'),
                         right=Side(border_style='thin', color='000000'),
                         top=cell.border.top,
                         bottom=cell.border.bottom)
# 遍历单元格区域I列单元格，设置右边线为第一种边线类型，其他边线为第二种边线类型
for row in range(3, 17):
    cell = ws.cell(row=row, column=9)
    cell.border = Border(left=Side(border_style='thin', color='000000'),
                         right=Side(border_style='medium', color='FF0000'),
                         top=cell.border.top,
                         bottom=cell.border.bottom)
# 遍历第3行单元格，设置顶边线为第一种边线类型，其他边线不变
for col in range(2, 10):
    cell = ws.cell(row=3, column=col)
    cell.border = Border(left=cell.border.left,
                         right=cell.border.right,
                         top=Side(border_style='medium', color='FF0000'),
                         bottom=cell.border.bottom)
# 遍历第16行单元格，设置底边线为第一种边线类型，其他边线不变
for col in range(2, 10):
    cell = ws.cell(row=16, column=col)
    cell.border = Border(left=cell.border.left,
                         right=cell.border.right,
                         top=cell.border.top,
                         bottom=Side(border_style='medium', color='FF0000'))
# 保存文件并退出
wb.save(path)
wb.close()

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存。运行脚本，打开示例数据文件，在工作表中设置数据所在单元格区域的内边框和外边框如图11-2所示。然后关闭工作簿。

【知识点扩展】

使用OpenPyXL包之前需要先导入该包，即

code.python

import openpyxl

使用OpenPyXL包的load_workbook函数可以直接打开示例数据文件。该函数返回一个工作簿对象。

code.python

path = 'D:/Samples/ch11/OpenPyXL/01 设置边框/班级检查.xlsx'
wb = openpyxl.load_workbook(path)

获取工作簿中的活动工作表。

code.python

ws = wb.active

用工作表对象的cell属性，用参数指定行号和列号，得到单元格。

code.python

cell = ws.cell(row=2, column=3)

然后就可以对该单元格进行设置了。

设置单元格的边框需要先导入Border类和Side类，即

code.python

from openpyxl.styles import Border, Side

Border对象表示单元格的边框，Side表示边框中的某一条边线。可以设置边框的线宽、线型和颜色等。

操作完成以后，用工作簿对象的save方法保存数据。

code.python

wb.save(path)

用工作簿对象的close方法关闭工作簿。

code.python

wb.close()

设置背景色

【问题描述】

用OpenPyXL包打开Excel文件，并设置工作表中指定单元格区域的背景色。

【示例11-7】

本例使用示例11-2的数据。要求在B-D列中，将值大于等于95的单元格的背景色设置为粉红色，将值小于60的单元格的背景色设置为淡绿色。

编写下面的OpenPyXL代码：

code.python

import openpyxl
from openpyxl.styles import PatternFill
# 打开Excel文件
workbook = openpyxl.load_workbook('D:/Samples/ch11/OpenPyXL/02 设置背景色/学生成绩.xlsx')
# 选择工作表
worksheet = workbook.active
# 定义粉红色填充样式
pink_fill = PatternFill(start_color='FFC0CB', end_color='FFC0CB', fill_type='solid')
# 定义淡绿色填充样式
light_green_fill = PatternFill(start_color='98FB98', end_color='98FB98', fill_type='solid')
# 遍历B-D列中的单元格
for row in worksheet.iter_rows(min_row=2, min_col=2, max_col=4):
    for cell in row:
        if cell.value is not None:
            if cell.value >= 95:
                # 将值大于等于95的单元格的背景色设置为粉红色
                cell.fill = pink_fill
            elif cell.value < 60:
                # 将值小于60的单元格的背景色设置为淡绿色
                cell.fill = light_green_fill
# 保存并退出Excel文件
workbook.save('D:/Samples/ch11/OpenPyXL/02 设置背景色/学生成绩.xlsx')
workbook.close()

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存。运行脚本，打开示例数据文件，在工作表中设置指定单元格的背景色如图11-4所示。然后保存工作簿，退出Excel应用。

【知识点扩展】

用OpenPyXL包可以对单元格的背景进行颜色填充或图案填充。图案填充要用到PatternFill类，颜色填充用到GradientFill类，使用之前需要先导入它们，如

code.python

from openpyxl.styles import PatternFill
from openpyxl.styles import GradientFill

图案填充和颜色填充都可以实现单色填充。

用类的构造函数创建对应的对象，例如本例用PatternFill类实现单色填充，创建时指定起始颜色和终止颜色都是粉红色，填充类型为单色填充。

code.python

pink_fill = PatternFill(start_color='FFC0CB', end_color='FFC0CB', fill_type='solid')

设置好类对象后，将它赋给单元格对象的fill属性即可。

code.python

cell.fill = pink_fill

设置字体

【问题描述】

用OpenPyXL包打开Excel文件，并设置工作表中指定单元格中文本的字体。

【示例11-8】

本例使用示例11-3的数据。要求设置B3单元格中文本的字体，字体名称为“黑体”，字体大小为20，加粗，字体颜色为红色；D4单元格中文本的字体名称为“宋体”，字体大小为30，字体颜色为兰色，倾斜。

编写下面的OpenPyXL代码：

code.python

import openpyxl
# 打开文件
workbook = openpyxl.load_workbook('D:/Samples/ch11/OpenPyXL/03 设置字体/成绩.xlsx')
# 选择工作表
worksheet = workbook.active
# 设置B3单元格的字体
cell_b3 = worksheet['B3']
cell_b3.font = openpyxl.styles.Font(name='黑体', size=20, bold=True, color='FF0000')
# 设置D4单元格的字体
cell_d4 = worksheet['D4']
cell_d4.font = openpyxl.styles.Font(name='宋体', size=30, italic=True, color='00FFFF')
# 保存文件并退出
workbook.save('D:/Samples/ch11/OpenPyXL/03 设置字体/成绩.xlsx')
workbook.close()

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存。运行脚本，打开示例数据文件，在工作表中设置指定单元格中文本的字体如图11-6所示。然后保存工作簿，关闭工作簿。

【知识点扩展】

用OpenPyXL包设置单元格中文本的字体需要用到Font类，利用该类的构造函数得到一个指定了字体格式的Font对象，然后把这个对象赋给单元格对象的font属性。

code.python

cell.font = openpyxl.styles.Font(name='宋体', size=30, italic=True, color='00FFFF')

设置对齐方式

【问题描述】

用xlwings包打开Excel文件，并设置工作表中指定单元格中内容的对齐方式。内容对齐，有水平对齐和垂向对齐两个方向的设置。

【示例11-9】

本例使用示例11-4的数据。要求设置B3单元格中文本水平居中对齐；D5单元格中的文本水平左对齐；B8单元格中的文本水平右对齐，垂直方向顶对齐。

编写下面的OpenPyXL代码：

code.python

import openpyxl
# 打开Excel文件
wb = openpyxl.load_workbook('D:/Samples/ch11/OpenPyXL/04 设置对齐方式/人员信息.xlsx')
# 选择工作表
ws = wb.active
# 设置B3单元格中文本水平居中对齐
ws['B3'].alignment = openpyxl.styles.Alignment(horizontal='center')
# 设置D5单元格中的文本水平左对齐
ws['D5'].alignment = openpyxl.styles.Alignment(horizontal='left')
# 设置B8单元格中的文本水平右对齐，垂直方向顶对齐
ws['B8'].alignment = openpyxl.styles.Alignment(horizontal='right', vertical='top')
# 保存并退出Excel文件
wb.save('D:/Samples/ch11/OpenPyXL/04 设置对齐方式/人员信息.xlsx')
wb.close()

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存。运行脚本，打开示例数据文件，在工作表中设置指定单元格中文本的对齐方式如图11-8所示。然后保存工作簿，关闭工作簿。

【知识点扩展】

用OpenPyXL包设置单元格中文本的对齐方式需要用到Alignment类，利用该类的构造函数得到一个指定了字体格式的Alignment对象，然后把这个对象赋给单元格对象的alignment属性。

code.python

cell.alignment = openpyxl.styles.Alignment(horizontal='right', vertical='top')

单元格合并和拆分

【问题描述】

用OpenPyXL包打开Excel文件，并合并工作表中指定的单元格区域，或取消合并某合并单元格。

【示例11-10】

本例使用示例11-5的数据，B8是合并单元格。要求合并单元格区域B3:C4，将单元格B8取消合并。

编写下面的OpenPyXL代码：

code.python

import openpyxl
# 打开Excel文件
workbook = openpyxl.load_workbook(filename='D:/Samples/ch11/OpenPyXL/05 单元格的合并和拆分/合并和拆分.xlsx')
# 选择操作的工作表
worksheet = workbook.active
# 合并单元格区域B3:C4
worksheet.merge_cells('B3:C4')
# 取消合并单元格区域B8:C9
worksheet.unmerge_cells('B8:C9')
# 保存文件
workbook.save('D:/Samples/ch11/OpenPyXL/05 单元格的合并和拆分/合并和拆分.xlsx')
# 退出Excel
workbook.close()

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存。运行脚本，打开示例数据文件，在工作表中合并单元格区域B3:C4，将单元格B8取消合并如图11-10所示。然后保存工作簿，关闭工作簿。

【知识点扩展】

用OpenPyXL包合并单元格区域使用工作表对象的merge_cells方法，取消合并单元格区域使用工作表对象的unmerge_cells方法。

code.python

worksheet.merge_cells('B3:C4')
worksheet.unmerge_cells('B8:C9')

注意，取消合并是需要指定合并单元格所占据的单元格区域。

本页目录

设置边框

【问题描述】

【示例11-6】

【知识点扩展】

设置背景色

【问题描述】

【示例11-7】

【知识点扩展】

设置字体

【问题描述】

【示例11-8】

【知识点扩展】

设置对齐方式

【问题描述】

【示例11-9】

【知识点扩展】

单元格合并和拆分

【问题描述】

【示例11-10】

【知识点扩展】