Linux下如何使用openpyxl处理表格数据

openpyxl模块是一个读写Excel 2010文档的Python库，如果要处理更早格式的Excel文档，需要用到额外的库，openpyxl是一个比较综合的工具，能够同时读取和修改Excel文档。其他很多的与Excel相关的项目基本只支持读或者写Excel一种功能。

在Centos8中安装方式如下：

[root@localhost ~]# yum -y install python3-openpyxl

从Excel表格中读取数据

如果要读取 Excel 文件，必须使用 load_workbook() 方法打开电子表格。之后可以使用 active 来选择第一个可用的工作表，并通过传递 row 和 column 参数使用 cell() 属性来选择单元格。value 属性返回特定单元格的值。请参阅下面的示例以获得更好的理解。

注意：第一行或第一列整数是 1，而不是 0。

下面的数据可以保存在excel表格文件中，用来做下面的实验，文件名称为wb.xlsx：

[root@localhost data]# python3# 导入openpyxl库>>> import openpyxl# path变量指定表格文件的位置>>> path = "/data/wb.xlsx"# 加载表格文件>>> wb_obj = openpyxl.load_workbook(path)# 激活默认的工作表>>> sheet_obj = wb_obj.active# 选择第一行第一列的单元格，也就是A1单元格>>> cell_obj = sheet_obj.cell(row = 1, column = 1)# 打印单元格所属位置>>> print(cell_obj)'Sheet1'.A1># 打印A1单元格的值>>> print(cell_obj.value)
Name

从多个单元格读取数据

从多个单元格中读取数据的方式有两种。

**方法一：**我们可以分别使用 max_row 和 max_column 获得总行数和总列数。我们可以在 for 循环中使用这些值来根据情况获取所需行或列或任何单元格的值。让我们看看如何获取第一列和第一行的值。

# 使用max_row获取工作表有多少行的数据>>> row = sheet_obj.max_row# 使用max_column获取工作表有多少列的数据>>> column = sheet_obj.max_column# 打印行数>>> print("Total Rows: ", row)
Total Rows:  6# 打印列数>>> print("Total Cols: ", column)
Total Cols:  4# 使用for循环打印第一列数据>>> for i in range(1, row + 1):
...   cell_obj = sheet_obj.cell(row = i, column = 1)
...   print(cell_obj.value)
...
Name
Ankit
Rahul
Priya
Nikhil
Nisha# 使用for循环打印第二条数据：>>> for i in range(1, column + 1):
...   cell_obj = sheet_obj.cell(row = 3, column = i)
...   print(cell_obj.value, end = " ")
...
Rahul M.Tech CSE 2

**方法二：**我们还可以使用单元格名称从多个单元格中读取。这可以看作是Python的列表切片。

# 选中A1到B6的单元格>>> cell_obj = sheet_obj['A1':'B6']
>>># for玄幻打印两列的数据>>> for cell1, cell2 in cell_obj:
...   print(cell1.value, cell2.value)
...
Name Course
Ankit B.Tech
Rahul M.Tech
Priya MBA
Nikhil B.Tech
Nisha B.Tech

写入表格数据

首先，让我们创建一个新的表格，然后我们将一些数据写入新创建的文件。可以使用 Workbook() 方法创建一个空的表格。让我们看看下面的例子。

[root@localhost data]# python3# 导入openpyxl库里面的Workbook>>> from openpyxl import Workbook
>>># 创建空的工作簿>>> workbook = Workbook()
>>># 保存文件>>> workbook.save(filename="/data/sample.xlsx")
>>>

创建一个空文件后，让我们看看如何使用 Python 向其中添加一些数据。要首先添加数据，我们需要选择活动工作表，然后使用 cell() 方法，我们可以通过传递行号和列号作为其参数来选择任何特定的单元格。我们也可以使用单元格名称进行书写。

# 获取激活的工作表>>> sheet = workbook.active
>>># 为A1添加Hello值>>> c1 = sheet.cell(row = 1, column = 1, value = 'Hello')# 为B1添加World值>>> C2 = sheet.cell(row = 1, column = 2, value = 'World')
>>># 为A2添加Welcome值>>> C3  = sheet['A2'].value = 'Welcome'# 为B2添加Everyone值>>> C4 = sheet['B2'].value = 'Everyone'>>># 保存到文件>>> workbook.save("/data/sample.xlsx")

附加数据到表格

在上面的示例中，您将看到每次尝试写入表格时，现有数据都会被覆盖，并且该文件将另存为新文件。发生这种情况是因为 Workbook() 方法总是创建一个新的工作簿文件对象。要写入现有工作簿，必须使用 load_workbook() 方法打开文件。

[root@localhost data]# python3>>> import openpyxl
>>># 使用load_workbook加载文件>>> wb = openpyxl.load_workbook("/data/sample.xlsx")
>>>
>>> sheet = wb.active
>>>
>>> c = sheet['A3']
>>>
>>> c.value = 'New Data'>>>
>>> wb.save("/data/sample.xlsx")

我们还可以使用 append() 方法在工作表的末尾附加多个数据。

# 定义数据>>> data = (
... (1,2,3),
... (4,5,6)
... )
>>># 使用for循环将内容附加到工作表中>>> for row in data:
...   sheet.append(row)
...
>>> wb.save('/data/sample.xlsx')

Excel表格的算术运算

可以通过在电子表格的特定单元格中键入公式来执行算术运算。例如，如果我们想求和，则使用 Excel 文件的 =Sum() 公式。

[root@localhost data]# python3>>> import openpyxl
>>># 创建新工作簿>>> wb = openpyxl.Workbook()
>>>
>>> sheet = wb.active
>>># 在单元格中写入数据>>> sheet['A1'] = 200
>>> sheet['A2'] = 300
>>> sheet['A3'] = 400
>>> sheet['A4'] = 500
>>> sheet['A5'] = 619# 在A7单元格求和>>> sheet['A7'] = '=SUM(A1:A5)'>>># 保存文件名称为sum.xlsx>>> wb.save('/data/sum.xlsx')

调整单元格的行和列

工作表对象具有控制行高和列宽的 row_dimensions 和 column_dimensions 属性。工作表的 row_dimensions 和 column_dimensions 是类似字典的值；row_dimensions 包含 RowDimension 对象， column_dimensions 包含 ColumnDimension 对象。在 row_dimensions 中，可以使用行号（在本例中为 1 或 2）访问其中一个对象。在 column_dimensions 中，可以使用列的字母（在本例中为 A 或 B）访问其中一个对象。

[root@localhost data]# python3# 导入模块>>> import openpyxl
>>># 创建新工作簿>>> wb = openpyxl.Workbook()
>>>
>>> sheet = wb.active
>>># 向A1单元格写入数据>>> sheet.cell(row = 1, column = 1, value = ' Hello ')'Sheet'.A1># 向B2单元格写入数据>>> sheet['B2'].value = ' Everyone '>>># 设置第一行单元格高度为45>>> sheet.row_dimensions[1].height = 45
>>># 设置B列单元格宽度为70>>> sheet.column_dimensions['B'].width = 70
>>>
>>> wb.save('/data/h_and_w.xlsx')

合并单元格

可以使用 merge_cells() 方法将多个单元格合并为单个单元格。

[root@localhost data]# python3>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active# 合并A2到D4单元格，并添加数据>>> sheet.merge_cells('A2:D4')
>>> sheet['A2'].value = 'Twelve cells join together.'>>># 合并C6到D6单元格，并添加数据>>> sheet.merge_cells('C6:D6')
>>> sheet['C6'].value = 'Two merge cells.'>>>
>>> wb.save('/data/merge_cells.xlsx')

取消合并单元格

如要取消合并单元格，请用 unmerge_cells() 方法。

# 取消合并A2到D4单元格>>> sheet.unmerge_cells('A2:D4')
>>># 取消合并C6到D6单元格>>> sheet.unmerge_cells('C6:D6')
>>>
>>> wb.save('/data/merge_cells.xlsx')

设置字体样式

要自定义单元格中的字体样式，重点是从 openpyxl.styles 模块导入 Font() 函数。

[root@localhost data]# python3>>> import openpyxl# 从openpyxl.styles导入Font函数>>> from openpyxl.styles import Font
>>>
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>># 为A1单元格添加内容，并设置字体大小为24>>> sheet['A1'].value = 'Linuxprobe.com'>>> sheet['A1'].font = Font(size = 24)# B2单元格添加内容，设置字体大小24，斜体>>> sheet.cell(row = 2, column = 2, value = "Linuxprobe.com").font = Font(size = 24, italic = True)# C3单元格添加内容，设置字体大小24，粗体>>> sheet.cell(row = 3, column = 3, value ="Linuxprobe.com").font = Font(size = 24, bold = True)# D4单元格添加内容，设置字体大小24，设置字体样式>>> sheet.cell(row = 4, column = 4, value = "Linuxprobe.com").font = Font(size = 24, name = 'Times New Roman')
>>> wb.save('/data/font_styles.xlsx')

绘制图表

要在 Excel 表格上绘制图表，首先要创建特定图表类（即 BarChart、LineChart 等）的图表对象。创建图表对象后，在其中插入数据，最后将该图表对象添加到工作表中。

[root@localhost data]# python3>>> import openpyxl# 导入图表库>>> from openpyxl.chart import BarChart, Reference
>>>
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>># 使用循环为工作表附加内容>>> for i in range(10):
...   sheet.append([i])
...# 为图表创建数据>>> values = Reference(sheet, min_col=1,min_row=1,max_col=1,max_row=10)#创建图表实例>>> chart = BarChart()# 向条形图添加数据>>> chart.add_data(values)# 添加图表标题>>> chart.title = " BAR-CHART "# 添加X坐标标题>>> chart.x_axis.title = " X_AXIS "# 添加Y坐标标题>>> chart.y_axis.title = " Y_AXIS ">>># 工作表的E2单元格添加图表>>> sheet.add_chart(chart, 'E2')
>>>
>>> wb.save("/data/charts.xlsx")

下面实例，是添加折线图：

[root@localhost data]# python3>>> import openpyxl# 导入图表库>>> from openpyxl.chart import LineChart, Reference
>>>
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>># 使用循环为工作表附加内容>>> for i in range(10):
...   sheet.append([i])
...# 为图表创建数据>>> values = Reference(sheet, min_col=1,min_row=1,max_col=1,max_row=10)#创建图表实例>>> chart = LineChart()# 向折现图添加数据>>> chart.add_data(values)# 添加图表标题>>> chart.title = " LINE-CHART "# 添加X坐标标题>>> chart.x_axis.title = " X_AXIS "# 添加Y坐标标题>>> chart.y_axis.title = " Y_AXIS ">>># 工作表的E2单元格添加图表>>> sheet.add_chart(chart, 'E2')
>>>
>>> wb.save("/data/charts.xlsx")

添加图片

为了在我们的工作表中导入图像，我们将使用 openpyxl.drawing.image.Image。该方法是在pillow库中找到的 PIL.Image 方法。因此，必须安装 python3-pillow库才能使用此方法。

[root@localhost data]# yum -y install python3-pillow[root@localhost data]# python3>>> import openpyxl# 导入Image函数>>> from openpyxl.drawing.image import Image
>>>
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>># 向工作表第一行添加内容>>> sheet.append(["abc",10,"def",20])# 添加图片>>> img = Image("/data/banner1.jpg")
>>>
>>> sheet.add_image(img, 'A2')
>>>
>>> wb.save("/data/images.xlsx")