Python数据持久性 Openpyxl模块

微软的Excel是最流行的电子表格应用程序。它从过去的25年中一直在使用。后来的Excel版本使用 Office Open XML （OOXML）文件格式。因此，通过其他编程环境访问电子表格文件已经成为可能。

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

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

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

数据持久性--Openpyxl模块

pip工具足以安装openpyxl包。

pip install openpyxl

工作簿类代表了一个有一个空白工作表的空工作簿。我们需要激活它，这样一些数据就可以被添加到工作表中。

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

我们知道，工作表中的单元格是以ColumnNameRownumber格式命名的。因此，左上角的单元格是A1。我们给这个单元格分配一个字符串为：

sheet1['A1']= 'Student List'

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

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应用程序打开，它显示如下

数据持久性--Openpyxl模块

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

例子

以下代码用工作表数据填充一个列表。

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)

输出

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