openpyxl读写excel

前面介绍了利用Python中xlrd与xlwt读写Excel的基本操作（ 《使用Python xlrd与xlwt模块读写Excel》），这两个模块可以很方便地对Excel进行处理，非常实用。但由于xlwt只支持Excel2007之前的版本，所以无法生成.xlsx后缀的文件，而且其单个sheet支持的最大行数为65535，数据量一旦超过限制行数，会遇到如下错误

ValueError: row index was 65536, not allowed by .xls format

这是xlwt很大的短板，遇到这种情况只能用其他包代替，openpyxl 是一个不错的选择，它可用于读写Excel2010 xlsx/xlsm/xltx/xltm 文件，同时支持 Pandas 和 NumPy 等包，能够绘制图表，并且同样支持格式控制等，详细文档可以参考：
https://openpyxl.readthedocs.io/en/default/index.html#
下面开始介绍它的一些基本操作：

首先通过pip安装

pip install openpyxl

或者手动安装：https://pypi.python.org/pypi/openpyxl

Excel的读取

#导入openpyxl package
import openpyxl

#打开一个已有工作簿
f = openpyxl.load_workbook('test.xlsx')

sheet相关操作

#获取sheet名称列表
name_list = f.sheetnames
name_list = f.get_sheet_names()

#通过名称调用sheet
table = f['name']
table = f.get_sheet_by_name('name')

#通过检索调用sheet
table = f.get_sheet_by_name(sheet_names[index])

#调用正在运行的sheet，一般为最后一个
table = f.active        

#改变sheet的名字
table.title = 'newname'    

单元格相关操作

#读取单元格或范围切片
c = table['A1']         #获取'A1'，返回class
c = table.cell(row = 1,column = 1)  #按行列数获读取

row5 = table[5]         #获取第5行，返回元组
colA = table['C']       #获取C列
cell_range = table['A1':'B4']    #获取范围切片元组
row_range = table[5:8]  #5到8行
col_range['A:D']        #A到D列

#按行列数读取范围切片
for row in table.iter_rows(min_row=1, max_col=3, max_row=2):    #按行读取
for col in table.iter_cols(min_row=1, max_col=3, max_row=2):    #按列读取

table.max_row    #最大行数
table.max_column #最大列数
table.rows       #按行遍历
table.columns    #按列遍历

#读取单元格的值
c = table['A2'].value   #按位置读取
c = table.cell(row = 1,column = 1).value    #按行列数读取 

需要注意的是openpyxl中按行列数检索时参数名‘row =’及‘column =’ 不能省略，而且均从1开始计数，这与xlrd有所不同。

一个例子

利用openpyxl读出图1所示表中的一些信息，代码及结果如下：

[图片上传失败...(image-ad4b83-1569340188843)] 图1.成绩单

import openpyxl             #导入openpyxl

f = openpyxl.load_workbook('demo.xlsx')         #打开工作簿
print(f.sheetnames)         #打印sheet名称列表
table = f['成绩单']          #调用成绩单sheet
print(table.max_column)     #打印最大列数
print(table.cell(row = 3,column = 3).value)     #打印C3的值
for i in table['A']:        #依次打印A列的值
    print(i.value)

运行结果如下：

[‘名单’, ‘成绩单’]
6
80
姓名
None
小明
小红
李华

openpyxl在读取合并格时也将其值当做左上角单元格的值，剩余单元格则返回None，而在xlrd中其他单元格则为empty: ‘’,有所区别。

Excel的创建及更改

#导入openpyxl package
import openpyxl

#创建一个工作簿
f = openpyxl.Workbook()

每次新建一个工作簿会默认生成一个名称为“Sheet1”的工作表，可以先将其利用起来，操作与前面相同，比如

table = f.active
#or
table = f['Sheet1']

当需要更多的工作表时，可以创建新的sheet

#创建sheet
table = f.create_sheet('AD')        #创建并插入末尾
table = f.create_sheet('AD',0)      #创建并插入首位

#可以通过RGB色值设置sheet标签颜色
table.sheet_properties.tabColor = 'RRGGBB'

#复制一个工作表
target = f.copy_worksheet(table)    

写入数据

table['A2'] = 4              #单元格写入
table['A1'] = '=SUM(1,1)'    #按公式写入

#按行列数写入
table.cell(row = 3,column = 1,value = 10)
table.cell(row = 3,column = 1).value = 10

#单元格合并与分解
table.merge_cells('A1:B1')    #按位置
table.unmerge_cells('A1:B1')

table.merge_cells(star_row = 2,star_column = 1,end_row = 2,end_column = 4)  #按行列号
table.unmerge_cells(start_row = 2,star_column = 1,end_row = 2,end_column = 4)

#插入图片
img = openpyxl.drawing.image.Image('demo.png')
table.add_image(img,'A1')

#保存文件
f.template = True    #存为模板
f.save('demo.xlsx', as_template = False)

openpyxl的写入语法与xlwt差别不大，主要区别在于它对单元格的定位主要根据坐标进行，如 ‘A5’,‘C3’，而xlrd则完全依靠单元格的行列位置，如cell(0,4),cell(2,2)。除了常规的读写操作，openpyxl也提供了完善的格式控制的方法，下面进行简单介绍。

单元格的格式控制

openpyxl中提供的Style方法用于调整表格的外观，如字体格式、边框、对齐方式、单元格背景、保护等。
字体格式：

# 导入相关模块
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font

#字体格式
font0 = Font(name='Calibri',
            size=11,
            bold=False,
            italic=False,
            vertAlign=None,    #Maybe：'baseline', 'superscript', 'subscript'
            underline='none',  #Maybe：'single','double','singleAccounting','doubleAccounting'
            strike=False,
            color='FF00FF00')

#单元格填充
fill0 = PatternFill(fill_type=None,
#Maybe：'lightUp', 'darkVertical', 'darkGrid', 'solid', 'darkHorizontal', 'darkUp', 'lightVertical', 'lightGray', 'darkTrellis', 'lightDown', 'gray125', 'gray0625', 'mediumGray', 'lightTrellis', 'darkGray', 'darkDown', 'lightHorizontal', 'lightGrid'
            start_color='FFFFFFFF',
            end_color='FF000000')

#边框
border0 = Border(left=Side(border_style=None,color='FF000000'),
#style Maybe：'mediumDashDotDot', 'dotted', 'thick', 'medium', 'dashDotDot', 'double', 'dashed', 'mediumDashed', 'dashDot', 'mediumDashDot', 'hair', 'slantDashDot', 'thin'
                right=Side(border_style=None,color='FF000000'),
                top=Side(border_style=None,color='FF000000'),
                bottom=Side(border_style=None,color='FF000000'),
                diagonal=Side(border_style=None,color='FF000000'),
                diagonal_direction=0,
                outline=Side(border_style=None,color='FF000000'),
                vertical=Side(border_style=None,color='FF000000'),
                horizontal=Side(border_style=None,color='FF000000')
                )

#对齐方式
alignment0 = Alignment(horizontal='general',    #Maybe:'centerContinuous', 'fill', 'right', 'distributed', 'justify', 'general', 'center', 'left'
                    vertical='bottom',
                    text_rotation=0,
                    wrap_text=False,
                    shrink_to_fit=False,
                    indent=0)

#表格保护
protection0 = Protection(locked=True,
                         hidden=False)

格式应用：

# 导入相关模块
from openpyxl.styles import NamedStyle

# 创建格式
style0 = NamedStyle(name = 'style_example')

#格式赋值
style0.font = font0
style0.alignment = alignment0
style0.border = border0
style0.fill = fill0
style0.Protection = protection0

#格式调用
#单属性调用
table['A1'].font = font0
table['A1'].alignment = alignment0
table.cell(row = 1,column = 1).border = border0

#按名称调用
table['A1'].style = style0
table['A1'].style = 'style_example'
table.cell(row = 1,column = 1).style = style0

由上可知，openpyxl提供的格式控制方法可以实现对单元格属性所有基本操作。下面还是以实现图 1 所示表格为例进行演示。

第二个例子

import openpyxl
from openpyxl.styles import NamedStyle, Border, Side, Alignment

# 生成工作簿及sheet
f = openpyxl.Workbook()
table = f.active
table.title = '成绩单'

# 信息列表
subject_list = ['语文','思想品德','数学','科学']
info_list = [
['小明',22,80,85,90,77],
['小红',23,91,88,95,90],
['李华',24,75,70,98,100]
]

# 创建表头
table.merge_cells('A1:A2')
table.cell(row = 1,column = 1,value = '姓名')
table.merge_cells('B1:B2')
table.cell(row = 1,column = 2,value = '学号')
table.merge_cells('C1:D1')
table.cell(row = 1,column = 3,value = '文科')
table.merge_cells('E1:F1')
table.cell(row = 1,column = 5,value = '理科')

for i in range(4):
    table.cell(row = 2,column = i+3,value = subject_list[i])

# 写入信息
for obs in range(3):
    for info in range(6):
        table.cell(row = obs+3,column = info+1,value = info_list[obs][info])

# 设置单元格格式，其中字体及背景为默认
style0 = NamedStyle('style0')
style0.border = Border(left=Side(border_style='thin'),
                right=Side(border_style='thin'),
                top=Side(border_style='thin'),
                bottom=Side(border_style='thin'))
style0.alignment = Alignment(horizontal='center',vertical='center',)

# 格式应用
for row in table.rows:
    for cell in row:
        cell.style = style0

# 保存文件
f.save("score.xlsx")

生成结果如下：

图2.score

图2.使用openpyxl生成的成绩单
  对比后发现，使用openpyxl做出表格的效果与office界面制作的完全相同。当然，若只是制作类似简单的表格，写代码的效率显然不如office界面操作高，编程处理表格的优势在于处理人工难以完成的工作，下期将分享两个用Python处理Excel的实例。