Python Data Persistence 简明教程

Data Persistence - Openpyxl Module

Microsoft 的 Excel 是最流行的电子表格应用程序。它已使用超过 25 年。Excel 的更高版本使用 *Office 开放 XML *（OOXML）文件格式。因此，可以通过其他编程环境访问电子表格文件。

OOXML 是 ECMA 标准文件格式。Python 的 openpyxl 包提供读取/写入扩展名为 .xlsx 的 Excel 文件的功能。

openpyxl 包使用的类名称与 Microsoft Excel 术语类似。Excel 文档称为工作簿，并以 .xlsx 扩展名保存在文件系统中。工作簿可能有多个工作表。工作表显示一个大网格单元，它们中的每一个都可以存储值或公式。形成网格的行和列已编号。列由字母、A、B、C、…、Z、AA、AB 等标识。行从 1 开始编号。

一个典型的 Excel 工作表如下所示 −

excel worksheet

pip 实用程序足以安装 openpyxl 包。

pip install openpyxl

Workbook 类表示带有空工作表的一个空工作簿。我们需要激活它，以便向工作表中添加一些数据。

from openpyxl import Workbook
wb=Workbook()
sheet1=wb.active
sheet1.title='StudentList'

众所周知，工作表中的单元格被命名为 ColumnNameRownumber 格式。相应地，左上角的单元格为 A1。我们将一个字符串分配给这个单元格，如下所示 −

sheet1['A1']= 'Student List'

或者，使用工作表的 cell() 方法，它使用行号和列号来标识单元格。调用单元格对象的 value 属性以分配一个值。

cell1=sheet1.cell(row=1, column=1)
cell1.value='Student List'

在用数据填充工作表后，通过调用工作簿对象的 save() 方法保存工作簿。

wb.save('Student.xlsx')

该工作簿文件创建在当前工作目录中。

以下 Python 脚本将一个元组列表写入到工作簿文档中。每个元组都存储了学号、年龄和学生的成绩。

from openpyxl import Workbook
wb = Workbook()
sheet1 = wb.active
sheet1.title='Student List'
sheet1.cell(column=1, row=1).value='Student List'
studentlist=[('RollNo','Name', 'age', 'marks'),(1,'Juhi',20,100),
   (2,'dilip',20, 110) , (3,'jeevan',24,145)]
for col in range(1,5):
   for row in range(1,5):
      sheet1.cell(column=col, row=1+row).value=studentlist[row-1][col-1]
wb.save('students.xlsx')

工作簿 students.xlsx 保存于当前工作目录中。如果使用 Excel 应用程序打开它，它将如下图所示−

excel application

openpyxl 模块提供了 load_workbook() 函数，用于帮助在工作簿文档中读取数据。

from openpyxl import load_workbook
wb=load_workbook('students.xlsx')

现在，您可以通过行号和列号访问任何单元格的值。

cell1=sheet1.cell(row=1, column=1)
print (cell1.value)
Student List

Example

以下代码通过表格数据填充一个列表。

from openpyxl import load_workbook
wb=load_workbook('students.xlsx')
sheet1 = wb['Student List']
studentlist=[]
for row in range(1,5):
   stud=[]
for col in range(1,5):
   val=sheet1.cell(column=col, row=1+row).value
stud.append(val)
studentlist.append(tuple(stud))
print (studentlist)

Output

[('RollNo', 'Name', 'age', 'marks'), (1, 'Juhi', 20, 100), (2, 'dilip', 20, 110), (3, 'jeevan', 24, 145)]

Excel 应用程序的一个非常重要的特性是公式。要向单元格分配公式，请将它分配给一个包含 Excel 公式语法的字符串。将 AVERAGE 函数分配给 c6 单元格，其中包含年龄。

sheet1['C6']= 'AVERAGE(C3:C5)'

Openpyxl 模块具有 Translate_formula() 函数，可以将公式复制到一个范围内。以下程序在 C6 中定义了 AVERAGE 函数，并将其复制到 C7，计算平均成绩。

from openpyxl import load_workbook
wb=load_workbook('students.xlsx')

sheet1 = wb['Student List']
from openpyxl.formula.translate import Translator#copy formula
sheet1['B6']='Average'
sheet1['C6']='=AVERAGE(C3:C5)'
sheet1['D6'] = Translator('=AVERAGE(C3:C5)', origin="C6").translate_formula("D6")
wb.save('students.xlsx')

更改后的工作表现在如下所示−

changed worksheet