Python Data Persistence 简明教程

Data Persistence - Openpyxl Module

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

Microsoft’s Excel is the most popular spreadsheet application. It has been in use since last more than 25 years. Later versions of Excel use *Office Open XML *(OOXML) file format. Hence, it has been possible to access spreadsheet files through other programming environments.

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

OOXML is an ECMA standard file format. Python’s openpyxl package provides functionality to read/write Excel files with .xlsx extension.

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

The openpyxl package uses class nomenclature that is similar to Microsoft Excel terminology. An Excel document is called as workbook and is saved with .xlsx extension in the file system. A workbook may have multiple worksheets. A worksheet presents a large grid of cells, each one of them can store either value or formula. Rows and columns that form the grid are numbered. Columns are identified by alphabets, A, B, C, …., Z, AA, AB, and so on. Rows are numbered starting from 1.

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

A typical Excel worksheet appears as follows −

excel worksheet

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

The pip utility is good enough to install openpyxl package.

pip install openpyxl

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

The Workbook class represents an empty workbook with one blank worksheet. We need to activate it so that some data can be added to the worksheet.

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

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

As we know, a cell in worksheet is named as ColumnNameRownumber format. Accordingly, top left cell is A1. We assign a string to this cell as −

sheet1['A1']= 'Student List'

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

Alternately, use worksheet’s cell() method which uses row and column number to identify a cell. Call value property to cell object to assign a value.

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

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

After populating worksheet with data, the workbook is saved by calling save() method of workbook object.

wb.save('Student.xlsx')

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

This workbook file is created in current working directory.

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

Following Python script writes a list of tuples into a workbook document. Each tuple stores roll number, age and marks of student.

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 应用程序打开它，它将如下图所示−

The workbook students.xlsx is saved in current working directory. If opened using Excel application, it appears as below −

excel application

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

The openpyxl module offers load_workbook() function that helps in reading back data in the workbook document.

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

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

You can now access value of any cell specified by row and column number.

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

Example

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

Following code populates a list with work sheet data.

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 单元格，其中包含年龄。

One very important feature of Excel application is the formula. To assign formula to a cell, assign it to a string containing Excel’s formula syntax. Assign AVERAGE function to c6 cell having age.

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

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

Openpyxl module has Translate_formula() function to copy the formula across a range. Following program defines AVERAGE function in C6 and copies it to C7 that calculates average of marks.

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

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

The changed worksheet now appears as follows −

changed worksheet