公式

公式以一个等号开始，可以配置单元格，让它包含通过其他单元格计算得到的值。在本节中，你将利用 openpyxl 模块，用编程的方式在单元格中添加公式，就像添加普通的值一样。例如：

>>> sheet['B9'] = '=SUM(B1:B8)'

这将 =SUM(B1:B8) 作为单元格 B9 的值。这将 B9 单元格设置为一个公式，计算单元格 B1 到 B8 的和。

为单元格设置公式就像设置其他文本值一样。在交互式环境中输入以下代码：

import openpyxl
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.utils import FORMULAE

"""插入公式"""
workbook = openpyxl.load_workbook('shaokao.xlsx')
sheet1 = workbook.get_sheet_by_name('Sheet1')  # type:Worksheet

sheet1['F32'] = '=AVERAGE(H2:H31)'
# 公式列表
print(FORMULAE)
"""
插入行列
.insert_rows(idx=1, )
"""
sheet1.insert_cols(idx=2, amount=2)
sheet1.insert_rows(idx=2)

"""
删除行列
"""
sheet1.delete_cols(idx=2, amount=2)
sheet1.delete_rows(idx=2)

"""
移动格子
正整数为向下或者向右，负整数为向左或者向上
"""
sheet1.move_range('C1:D31', rows=31, cols=2)
workbook.save('shaokao_copy.xlsx')

设置单元格的字体风格

设置某些单元格行或列的字体风格，可以帮助你强调电子表格中重点的区域。例如，在这个产品电子表格中，程序可以对 potato、garlic 和 parsnip 等行使用粗体。或者也许你希望对每磅价格超过5美元的行使用斜体。手工为大型电子表格的某些部分设置字体风格非常令人厌烦，但程序可以马上完成。

为了定义单元格的字体风格，需要从 openpyxl.styles 模块导入 Font() 和 Style() 函数。

Font 对象

Font 对象的 style 属性影响文本在单元格中的显示方式。要设置字体风格属性，就向 Font() 函数传入关键字参数。下表展示了 Font() 函数可能的关键字参数。

Font style 属性的关键字参数

关键字参数	数据类型	描述
name	字符串	字体名称，诸如'Calibri'或'Times New Roman'
size	整型	大小点数
bold	布尔型	True表示粗体
italic	布尔型	True表示斜体

可以调用Font()来创建一个Font对象，并将这个Font对象保存在一个变量中。然后将它传递给Style() ，得到的Style对象保存在一个变量中，并将该变量赋给Cell对象的style属性。例如，下面的代码创建了各种字体风格：

import openpyxl
from openpyxl.styles import Font
from openpyxl.styles import Alignment
import openpyxl.utils

workbook = openpyxl.Workbook()
sheet = workbook.get_sheet_by_name('Sheet')
cell = sheet['A1']
cell.value = 'hello excel !'

# 字体设置
# Font(name=字体名称, size=字体大小，bold=是否加粗，italic=是否斜体，color=字体颜色)
font = Font(name='思源黑体Regular', size=24, bold=True, italic=True, color='FF0000')
cell.font = font

# 对齐方式
# Alignment(horizontal=水平对齐模式，vertical=垂直对齐模式，text_ rotation=旋转角度，wrap_ text=是否自动换行)

# 水平对齐："general", "left", "center", "right", "fill", "justify", "centerContinuous","distributed",
# 垂直对齐："top", "center", "bottom", "justify", "distributed",
# 上中下，左中右

alignment = Alignment(horizontal='center', vertical='center', text_rotation=15)
cell.alignment = alignment

workbook.save(filename='style.xlsx')

这里，我们将一个 Font 对象保存在 fontObj1 中，并用它创建一个 Style 对象，该对象保存在 styleObj1 中，然后将 A1 的 Cell 对象的 style 属性设置为 styleObj。我们针对另一个 Font 对象和 Style 对象重复这个过程，设置第二个单元格的字体风格。运行这段代码后，电子表格中 A1 和 B3 单元格的字体风格将设置为自定义的字体风格，

边框对象

import openpyxl
from openpyxl.styles import Side, Border

import openpyxl.utils

workbook = openpyxl.Workbook()
sheet = workbook.get_sheet_by_name('Sheet')
cell = sheet['B2']
cell.value = 'hello excel !'

"""边框设置
Side(style=边线样式，color=边线颜色)
    边框样式：thin、dotted
Border(lef=-左边线样式, right=右边线样式, top=上边线样式, bottom=下边线样式, )
"""
side = Side(style='thin', color='FF0000')
side2 = Side(style='dotted', color='FF0000')
border = Border(left=side, right=side, top=side2, bottom=side2)
cell.border = border
workbook.save(filename='style.xlsx')

调整行和列

在 Excel 中，调整行和列的大小非常容易，只要点击并拖动行的边缘，或列的头部。但如果你需要根据单元格的内容来设置行或列的大小，或者希望设置大量电子表格文件中的行列大小，编写Python程序来做就要快得多。

行和列也可以完全隐藏起来。或者它们可以“冻结”，这样就总是显示在屏幕上，如果打印该电子表格，它们就出现在每一页上（这很适合做表头）。

设置行高和列宽

Worksheet 对象有 row_dimensions 和 column_dimensions 属性，控制行高和列宽。在交互式环境中输入以下代码：

import openpyxl
from openpyxl.styles import PatternFill, GradientFill

import openpyxl.utils
from openpyxl.worksheet.worksheet import Worksheet

workbook = openpyxl.Workbook()
sheet = workbook.get_sheet_by_name('Sheet')  # type:Worksheet

"""
.row_dimensions[行编号].height= 行高
.column_dimensions[列编号].width=列宽

"""
sheet.row_dimensions[2].height = 50
sheet.column_dimensions['B'].width = 20

工作表的 row_dimensions 和 column_dimensions 是像字典一样的值，row_dimensions 包含 RowDimension 对象， column_dimensions 包含 ColumnDimension 对象。在 row_dimensions 中，可以用行的编号来访问一个对象（在这个例子中，是1或）。 在 column_dimensions 中，可以用列的字母来访问一个对象（在这个例子中，是A或B）。

一旦有了 RowDimension 对象，就可以设置它的高度。一旦了 ColumnDimension 对象，就可以设置它的宽度。行的高度可以设置为 0 到 409 之间的整数或浮点值。这个值表示高度的点数。一点等于 1/72 英寸。默认的行高是 12.75。列宽可以设置为 0 到 255 之间的整数或浮点数。这个值表示使用默认字体大小时（11 点），单元格可以显示的字符数。默认的列宽是 8.43 个字符。列宽为零或行高为零，将使单元格隐藏。

合并和拆分单元格

利用 merge_cells() 工作表方法，可以将一个矩形区域中的单元格合并为一个单元格。在交互式环境中输入以下代码：

"""
.merge_cells(待合并的格子编号)
.merge_cells(start. row=起始行号，start_column=起始列号，end_row=结束行号，end_column=结束列号)
"""

sheet.merge_cells("C1:D2")
# A7:D8
sheet.merge_cells(start_row=7, start_column=1, end_row=8, end_column=4)

"""
.unmerge_cells(待合并的格子编号)
.unmerge_cells(start. row=起始行号，start_column=起始列号，end_row=结束行号，end_column=结束列号)
"""

sheet.unmerge_cells("C1:D2")
# A7:D8
sheet.unmerge_cells(start_row=7, start_column=1, end_row=8, end_column=4)

"""
Patternill(fill type=填充样式，fgColor=填充颜色)
Gradientl(lstop=(渐变颜色1,渐变颜色2,....))
"""

cell_b2 = sheet['B2']
pattern_fill = PatternFill(fill_type='solid', fgColor='99ccff')
cell_b2.fill = pattern_fill

cell_d2 = sheet['D2']
gradient_fill = GradientFill(stop=('FFFFFF', '99ccff', '000000'))
cell_d2.fill = gradient_fill

merge_cells() 的参数是一个字符串，表示要合并的矩形区域左上角和右下角的单元格：'A1:D3' 将 12 个单元格合并为一个单元格。要设置这些合并后单元格的值，只要设置这一组合并单元格左上角的单元格的值。

要拆分单元格，就调用 unmerge_cells() 工作表方法。

冻结窗格

对于太大而不能一屏显示的电子表格，“冻结”顶部的几行或最左边的几列，是很有帮助的。例如，冻结的列或行表头，就算用户滚动电子表格，也是始终可见的。这称为“冻结窗格”。在OpenPyXL中，每个Worksheet对象都有一个freeze_panes属性，可以设置为一个Cell对象或一个单元格坐标的字符串。请注意，单元格上边的所有行和左边的所有列都会冻结，但单元格所在的行和列不会冻结。

要解冻所有的单元格，就将 freeze_panes 设置为 None 或 'A1'。下表展示了 freeze_panes 设定的一些例子，以及哪些行或列会冻结。

freeze_panes 的设置	冻结的行和列
sheet.freeze_panes = 'A2'
sheet.freeze_panes = 'B1'	行 1
sheet.freeze_panes = 'C1'	列 A
sheet.freeze_panes = 'C2'	列 A 和列 B
sheet.freeze_panes = 'A1'或	行1和列A和列B
sheet.freeze_panes = None	没有冻结窗格

"""冻结第一行"""
sheet.freeze_panes = 'A2'
workbook.save(filename='style.xlsx')

图表

openpyxl 支持利用工作表中单元格的数据，创建条形图、折线图、散点图和饼图。要创建图表，需要做下列事情：

1．从一个矩形区域选择的单元格，创建一个 Reference 对象。

2．通过传入 Reference 对象，创建一个 Series 对象。

3．创建一个 Chart 对象。

4．将 Series 对象添加到 Chart 对象。

5．可选地设置 Chart 对象的 drawing.top、drawing.left、drawing.width 和 drawing.height 变量。

6．将 Chart 对象添加到 Worksheet 对象。

Reference 对象需要一些解释。Reference 对象是通过调用 openpyxl.charts. Reference() 函数并传入 3 个参数创建的：

1．包含图表数据的 Worksheet 对象。

2．两个整数的元组，代表矩形选择区域的左上角单元格，该区域包含图表数据：元组中第一个整数是行，第二个整数是列。请注意第一行是1，不是0。

3．两个整数的元组，代表矩形选择区域的右下角单元格，该区域包含图表数据：元组中第一个整数是行，第二个整数是列。

在交互式环境中输入以下代码，创建一个条形图，将它添加到电子表格中：

import openpyxl
from openpyxl.chart import BarChart, Reference

"""
BarChart()
Reference()
"""
workbook = openpyxl.load_workbook('data.xlsx')
sheet = workbook.active

# 新建一个柱状图
chart = BarChart()
# 加载数据
data = Reference(worksheet=sheet, min_row=1, max_row=7, min_col=2, max_col=3)
# 加载分类
categories = Reference(sheet, min_col=1, min_row=2, max_row=7)

# 设置图形需要的数据
chart.add_data(data, titles_from_data=True)
# 设置X轴类目
chart.set_categories(categories)
sheet.add_chart(chart, 'E2')

workbook.save(filename="chart.xlsx")

我们可以调用 openpyxl.charts.BarChart()，创建一个条形图。也可以调用 openpyxl.charts.LineChart() 、openpyxl.charts.ScatterChart() 和 openpyxl.charts.PieChart()，创建折线图、散点图和饼图。

import openpyxl
from openpyxl.chart import LineChart, Reference

"""
BarChart()
Reference()
"""
workbook = openpyxl.load_workbook('data.xlsx')
sheet = workbook.get_sheet_by_name('score')

# 新建一个柱状图
chart = LineChart()
# 加载数据
data = Reference(worksheet=sheet, min_row=2, min_col=1, max_row=4, max_col=13)
# 加载分类
categories = Reference(sheet, min_row=1, min_col=2, max_col=13)

# 设置图形需要的数据
# 数据是横向的
chart.add_data(data, from_rows=True, titles_from_data=True)
# 设置X轴类目
chart.set_categories(categories)
sheet.add_chart(chart, 'B23')

workbook.save(filename="chart.xlsx")

插入图片

import openpyxl
from openpyxl.drawing.image import Image

"""
from openpyxl.drawing.image import Image

sheet.add_image(Image, 'C1')
"""
workbook = openpyxl.Workbook()
sheet = workbook.active

logo = Image('logo.png')
logo.height = 100
logo.width = 100

sheet.add_image(logo, 'C1')

workbook.save(filename="style.xlsx")

小结

处理信息是比较难的部分，通常不是处理本身难，而是为程序得到正确格式的数据较难。一旦你将电子表格载入Python，就可以提取并操作它的数据，比手工操作要快得多。

你也可以生成电子表格，作为程序的输出。所以如果同事需要将包含几千条销售合同的文本文件或PDF转换成电子表格文件，你就不需要无聊地将它拷贝粘贴到 Excel 中。

有了 openpyxl 模块和一些编程知识，你会发现处理很大的电子表格也是小事一桩。