Excel 公式、图表以及单元格的操作

时间:2022-10-31 11:03:13

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')

上面代码中,首先读取 Excel 文档 sum.xlsx 并获取活跃工作表;其次在单元格 A5 以及 B5 中分别写入公式 =SUM(A1:A3) 和 =SUM(A1:A3,B1:B3);最后对 Excel 文档进行保存。运行上述代码之前的文档如下图所示:

Excel 公式、图表以及单元格的操作

上述代码运行之后的文档如下图所示:

Excel 公式、图表以及单元格的操作

A5 单元格的值为 A1,A2,A3 单元格的值相加。B5 单元格的值为 A1,A2,A3,B1,B2,B3 单元格的值相加。

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')

上面代码中,首先读取 Excel 文档 vlookup.xlsx 并获取活跃工作表;其次在单元格 B7 中写入公式 =VLOOKUP(A7,A1:B5,2,0);最后对 Excel 文档进行保存。运行上述代码之前的文档如下图所示:

Excel 公式、图表以及单元格的操作

上述代码运行之后的文档如下图所示:

Excel 公式、图表以及单元格的操作

根据 A7 中的苹果查找到了金额 50。在公式 VLOOKUP(A7,A1:B5,2,0) 中,第一个参数 A7 是要查找的值;第二个参数 A1:B5 为要在其中查找值的区域;第三个参数 2 为查找值所在列的列号;第四个参数 0 为查找匹配项,近似匹配指定 TRUE(1),精确匹配指定 FALSE(0)。

2. 图表

我们可以使用 openpyxl 提供的方法为 Excel 中的数据作图表,下面以柱状图举例说明:

2.1 步骤

1.创建数据的 Reference 对象以及类别的 Reference 对象。
2.创建一个 BarChart 对象,并设置对象的属性,如:标题、x 轴名称、y 轴名称等。
3.将数据的 Reference 对象以及类别的 Reference 对象添加到 BarChart 对象。
4.将 BarChart 对象添加到工作表并保存工作表。

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")

上面的代码中,步骤 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 对应的代码为:

chart = BarChart()
chart.title = "销售数量&销售金额"
chart.y_axis.title = '数量&金额'
chart.x_axis.title = '商品名称'

步骤 3 对应的代码为:

chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

步骤 4 对应的代码为:

ws.add_chart(chart,"F2")
wb.save("sampleChart.xlsx")

运行上述代码之前的文档如下图所示:

Excel 公式、图表以及单元格的操作

上述代码运行之后的文档如下图所示:

Excel 公式、图表以及单元格的操作

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')

在上面的代码中,创建了两个字体对象 fontObj1 和 fontObj2。并把 A1 单元格的字体设置成 fontObj1,B3 单元格的字体设置成 fontObj2。上面代码执行完成后的 Excel 文档如下图所示:

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')

在上面的代码中,将第一行的行高设置成 70,将第二列的列宽设置成 20。上面代码执行完成后的 Excel 文档如下图所示:

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')

在上面的代码中,首先将 A1:D3 矩形区域内的单元格进行合并,其次将 C5 和 D5 单元格进行合并。上面代码执行完成后的 Excel 文档如下图所示:

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')

在上面的代码中,将合并后的单元格进行拆分。上面代码执行完成后的 Excel 文档如下图所示:

Excel 公式、图表以及单元格的操作

3.4 冻结单元格

当 Excel 文档中的行数较多时,我们下滑鼠标查看行内容时,行的标题也会上滑消失,这时候想知道没列代表的含义就不是很方便。为了查看的方便,我们可以冻结标题。冻结单元格的示例代码如下:

import openpyxl

wb = openpyxl.load_workbook("produceSales.xlsx")
ws = wb.active
ws.freeze_panes = 'E2'

wb.save('produceSales.xlsx')

冻结前查看后边行内容的 Excel 文档如下图所示:

Excel 公式、图表以及单元格的操作

可以看到下滑到后面行的时候,标题看不到了。上面代码执行完成后的 Excel 文档如下图所示:

Excel 公式、图表以及单元格的操作

当冻结首行后,当下滑到后面行的时候,标题依然是可以看到的。