上次课我们讲到了 Excel 文档的写入。
# 1.公式
# 1.1 SUM
SUM 函数将值相加，可以将单个值、单元格引用或是区域相加，或者将三者的组合相加。例如: =SUM(A1:A3)将单元格 A1：A3 中的值加在一起，=SUM(A1:A3,B1:B3)将单元格 A1：A3 以及单元格 B1：B3 中的值加在一起。 语法：SUM(number1,[number2],…)，number1（必需）：要相加的第一个数字。该数字可以是 4 之类的数字，A1 之类的单元格引用或 A1：A3 之类的单元格范围。number2（可选）：要相加的第二个数字。可以按照这种方式最多指定 255 个数字。下面我们来看怎么通过 Python 使用 SUM 函数。代码如下：
import openpyxl
wb = openpyxl.load_workbook('sum.xlsx')
ws = wb.active
ws['A5'] = '=SUM(A1:A3)'
ws['B5'] = '=SUM(A1:A3,B1:B3)'
wb.save('sum.xlsx')
2
3
4
5
6
7
8
9
上面代码中，首先读取 Excel 文档 sum.xlsx 并获取活跃工作表；其次在单元格 A5 以及 B5 中分别写入公式 =SUM(A1:A3) 和 =SUM(A1:A3,B1:B3)；最后对 Excel 文档进行保存。运行上述代码之前的文档如下图所示：
# 1.2 VLOOKUP
使用 VLOOKUP 可以在表格或区域中按行查找内容。下面我们来看怎么通过 Python 使用 VLOOKUP 函数。代码如下：
import openpyxl
wb = openpyxl.load_workbook('vlookup.xlsx')
ws = wb.active
ws['B7'] = '=VLOOKUP(A7,A1:B5,2,0)'
wb.save('vlookup.xlsx')
2
3
4
5
6
7
上面代码中，首先读取 Excel 文档 vlookup.xlsx 并获取活跃工作表；其次在单元格 B7 中写入公式 =VLOOKUP(A7,A1:B5,2,0)；最后对 Excel 文档进行保存。运行上述代码之前的文档如下图所示：
# 2.图表
我们可以使用 openpyxl 提供的方法为 Excel 中的数据作图表，下面以柱状图举例说明：
# 2.1 步骤
1.创建数据的 Reference 对象以及类别的 Reference 对象。
2.创建一个 BarChart 对象，并设置对象的属性，如：标题、x 轴名称、y 轴名称等。
3.将数据的 Reference 对象以及类别的 Reference 对象添加到 BarChart 对象。
4.将 BarChart 对象添加到工作表并保存工作表。
2
3
4
# 2.2 代码
import openpyxl
from openpyxl.chart import BarChart, Reference
wb = openpyxl.load_workbook('sampleChart.xlsx')
ws = wb.active
data = Reference(ws, min_row=1, max_row=9, min_col=3, max_col=4)
cats = Reference(ws, min_col=2, max_col=2, min_row=2, max_row=9)
chart = BarChart()
chart.title = "销售数量&销售金额"
chart.y_axis.title = '数量&金额'
chart.x_axis.title = '商品名称'
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart,"F2")
wb.save("sampleChart.xlsx")
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
上面的代码中,步骤 1 对应的代码为：
data = Reference(ws, min_row=1, max_row=9, min_col=3, max_col=4)
cats = Reference(ws, min_col=2, max_col=2, min_row=2, max_row=9)
2
步骤 2 对应的代码为：
chart = BarChart()
chart.title = "销售数量&销售金额"
chart.y_axis.title = '数量&金额'
chart.x_axis.title = '商品名称'
2
3
4
步骤 3 对应的代码为：
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
2
步骤 4 对应的代码为：
ws.add_chart(chart,"F2")
wb.save("sampleChart.xlsx")
2
运行上述代码之前的文档如下图所示：
# 3.单元格格式的设置
我们还可以使用 openpyxl 提供的方法对 Excel 文档的单元格格式进行设置。
# 3.1 单元格字体的设置
示例代码如下：
from openpyxl.styles import Font
wb = openpyxl.Workbook()
ws = wb['Sheet']
fontObj1 = Font(name='Times New Roman', bold=True)
ws['A1'] = 'Bold Times New Roman'
ws['A1'].font = fontObj1
fontObj2 = Font(size=24, italic=True)
ws['B3'] = '24 pt Italic'
ws['B3'].font = fontObj2
wb.save('styles.xlsx')
2
3
4
5
6
7
8
9
10
11
12
13
14
在上面的代码中，创建了两个字体对象 fontObj1 和 fontObj2。并把 A1 单元格的字体设置成 fontObj1，B3 单元格的字体设置成 fontObj2。
上面代码执行完成后的 Excel 文档如下图所示：
# 3.2 调整行高和列宽
示例代码如下：
import openpyxl
wb = openpyxl.Workbook()
ws = wb.active
ws['A1'] = 'Tall Row'
ws['B2'] = 'Wide Column'
ws.row_dimensions[1].height = 70
ws.column_dimensions['B'].width = 20
wb.save('dimensions.xlsx')
2
3
4
5
6
7
8
9
10
11
12
在上面的代码中，将第一行的行高设置成 70，将第二列的列宽设置成 20。上面代码执行完成后的 Excel 文档如下图所示：
# 3.3 合并和拆分单元格
合并单元格的示例代码如下：
import openpyxl
wb = openpyxl.Workbook()
ws = wb.active
ws.merge_cells('A1:D3')
ws['A1'] = 'Twelve cells merged together.'
ws.merge_cells('C5:D5')
ws['C5'] = 'Two cells merged together.'
wb.save('merged.xlsx')
2
3
4
5
6
7
8
9
10
11
12
在上面的代码中，首先将 A1:D3 矩形区域内的单元格进行合并，其次将 C5 和 D5 单元格进行合并。上面代码执行完成后的 Excel 文档如下图所示：
import openpyxl
wb = openpyxl.load_workbook('merged.xlsx')
ws = wb.active
ws.unmerge_cells('A1:D3')
ws.unmerge_cells('C5:D5')
wb.save('unmerged.xlsx')
2
3
4
5
6
7
8
在上面的代码中，将合并后的单元格进行拆分。上面代码执行完成后的 Excel 文档如下图所示：
# 3.4 冻结单元格
当 Excel 文档中的行数较多时，我们下滑鼠标查看行内容时，行的标题也会上滑消失，这时候想知道没列代表的含义就不是很方便。为了查看的方便，我们可以冻结标题。冻结单元格的示例代码如下：
import openpyxl
wb = openpyxl.load_workbook("produceSales.xlsx")
ws = wb.active
ws.freeze_panes = 'E2'
wb.save('produceSales.xlsx')
2
3
4
5
6
7
冻结前查看后边行内容的 Excel 文档如下图所示：
