openpyxl读取Excel的常用指令

目标文件 学生成绩单.xlsx

 

Excel相关的名词

工作簿Workbook：双击某个.xlsx文件后在Excel中呈现的内容。例如：学生成绩单.xlsx双击打开后是一个工作簿

工作表Sheet：一个工作簿由多个工作表组成。同一时间，只能有一个工作表处于可编辑状态，这个工作表也称为当前工作表(active sheet)

行Row：工作表中横向为行，一行代表工作表中的一条数据

列Column：工作表中纵向为列，一列代表数据的一个属性（特征）

单元格Cell：工作表中横纵相交的部分就是一个一个单元格。描述一个单元格的位置时，可以用Excel的单元格坐标形式，例如'A1'、'C6'、'E29'，也可以使用行号列号。

利用Openpyxl操作Excel

1. 加载Excel工作簿 

import openpyxl
book = openpyxl.load_workbook('学生成绩单.xlsx')

 2. 获取工作簿中所有工作表的名称

import openpyxl
book = openpyxl.load_workbook('学生成绩单.xlsx')
print(book.sheetnames)

运行结果：

book的sheetnames属性以字符串列表的形式包含了所有工作簿中工作表的名称。

3. 获取某一个工作表Sheet

import openpyxl
book = openpyxl.load_workbook('学生成绩单.xlsx')
sheet = book['成绩单']

变量sheet引用了工作簿中的"成绩单"工作表对象，后续代码中围绕sheet变量展开的操作，保存后就都会反映到"成绩单"工作表上。

4. 当前工作表的行列范围

import openpyxl
book = openpyxl.load_workbook('学生成绩单.xlsx')
sheet = book['成绩单']
print(f'区域：{sheet.dimensions}')
print(f'行数：{sheet.max_row}')
print(f'列数：{sheet.max_column}')

运行结果：

注意，sheet还有两个属性sheet.rows和sheet.columns，它们是行或列的生成器，在遍历行或列的时候会用到。

import openpyxl
book = openpyxl.load_workbook('学生成绩单.xlsx')
sheet = book['成绩单']
print(f'rows：{sheet.rows}')
print(f'columns：{sheet.columns}')

运行结果：

 5. 获取单元格中的内容

获取单元格有两种方式，一种是sheet['Excel单元格坐标']，另一种是sheet.cell(行号，列号)

import openpyxl
book = openpyxl.load_workbook('学生成绩单.xlsx')
sheet = book['成绩单']
print(sheet['A1'])
print(sheet.cell(2, 2))

sheet['A1']是用Excel表示单元格的方式，取A列和第一行相交的单元格A1,sheet.cell(2,2)是用更符合编程的方式获取第二行和第二列相交的单元格。

运行结果：

无论使用哪种方式，获得的都是单元格对象而非单元格里面的内容。要获取单元格的内容要使用单元格对象的value属性：

import openpyxl
book = openpyxl.load_workbook('学生成绩单.xlsx')
sheet = book['成绩单']
print(sheet['A1'].value)
print(sheet.cell(2, 2).value)

运行结果：

习惯Excel的人可能更常用第一种方式，而习惯编程的人可能更常用第二种方式。当然，两种表示方式也可以互相转化：

import openpyxl
book = openpyxl.load_workbook('学生成绩单.xlsx')
sheet = book['成绩单']
c1 = sheet['E48']
c2 = sheet.cell(13, 4)
print(f'{c1.row},{c1.column}')
print(c2.coordinate)

例如，想知道E48单元格是第几行第几列相交，可以用单元格的row属性和column属性获得；反之，想知道第13行第4列相交的单元格对应的Excel坐标是什么，可以用单元格的coordinate属性。

7. 获取一片区域的单元格

有时需要获取一片连续区域内的单元格

import openpyxl
book = openpyxl.load_workbook('学生成绩单.xlsx')
sheet = book['成绩单']
print(sheet['A2:G5'])

sheet['A2:G5']是获取从A2~G5的所有单元格，程序运行结果为：

 sheet['A2:G5']是一个4*7的二维元组。二维元组的中的四个一维元组代表了A2~G5这片连续区域共有四行，而这4个一维元组中各有7个元素，代表了每一行中有7个单元格。sheet['A2:G5']中的每一个元素都是单元格对象，而非单元格中的内容。要想取出单元格中的内容，需要额外的处理：

import openpyxl
book = openpyxl.load_workbook('学生成绩单.xlsx')
sheet = book['成绩单']
cells = sheet['A2：G5']
values = [[cell.value for cell in rows] for rows in cells]

这里利用了列表推导式将二维元组中单元格的内容取出放入了二维列表。

在成绩单工作表中，‘A2:G5'这片连续区域中的单元格也是第2行到第5行的单元格，所以也可以按行来取。sheet[行号]是取一行的所有单元格，而sheet[开始行号:结束行号]从取连续几行的单元格。

import openpyxl
book = openpyxl.load_workbook('学生成绩单.xlsx')
sheet = book['成绩单']
print(f'第二行: {sheet[2]}')
print(f'第三行至第五行：{sheet[3:5]}')

运行结果：

sheet[2]是一维元组，里面的7个单元格对象代表了第二行的7个单元格。sheet[3:5]是一个3×7的二维元素，里面的21个单元格对象代表了第三行到第五行每一行的7个单元格。同样，为了获得单元格中的值，需要进行数据的转化。

import openpyxl
book = openpyxl.load_workbook('学生成绩单.xlsx')
sheet = book['成绩单']
row2_values = [cell.value for cell in sheet[2]]
print(row2_values)
rows_values = [[cell.value for cell in row] for row in sheet[3:5]]
print(rows_values)

运行结果：

 既然可以按行获取单元格也可以按列获取单元格。sheet[列名]是获取一列，sheet[开始列名:结束列名]是获取连续的列。

import openpyxl
book = openpyxl.load_workbook('学生成绩单.xlsx')
sheet = book['成绩单']
print(f'第二列：{sheet["B"]}')
print(f'第三列：第五列{sheet["C:E"]}')

代码的运行结果与上面按行取类似，也是以一维元组盛放一列中的单元格，以二维元组盛放多列的单元格。内容较多就不贴图了。同样为了获得单元格中的值，也需要进行转化。

8. 按行或列遍历全表

按行遍历可以使用sheet.rows。rows属性是一个生成器，生成一个个由单元格对象组成的代表行的元组。

import openpyxl
book = openpyxl.load_workbook('学生成绩单.xlsx')
sheet = book['成绩单']
for row in sheet.rows:
    for cell in row:
        print(cell.value, end='\t')
    print()

 现在如果要使用rows对连续的某几行进行遍历，就需要进行额外的切片处理：

import openpyxl
book = openpyxl.load_workbook('学生成绩单.xlsx')
sheet = book['成绩单']
rows = list(sheet.rows)[6:10]
for row in rows:
    for cell in row:
        print(cell.value, end='\t')
    print()

将生成器的内容一次性读取到列表中，然后对列表进行切片处理。需要特别注意的是，工作表第一行在列表中下标为0，切片[6:10]是取下标6~9的四行数据，对应工作表就是第7行~第10行。

columns属性与rows属性功能类似，是对列的生成器。

9. 万能的遍历函数iter_rows和iter_cols函数

openpyxl还提供了iter_rows按行遍历函数和iter_cols按列遍历函数。

iter_rows函数的返回值是生成器，其作用是生成一个个由单元格对象组成代表行的元组。

import openpyxl
book = openpyxl.load_workbook('学生成绩单.xlsx')
sheet = book['成绩单']
for row in sheet.iter_rows():
    for cell in row:
        print(cell.value, end='\t')
    print()

通过指定参数，可以使用iter_rows函数遍历部分连续的行。

例如遍历工作表第7行~第10行：

import openpyxl
book = openpyxl.load_workbook('学生成绩单.xlsx')
sheet = book['成绩单']
for row in sheet.iter_rows(min_row=7, max_row=10):
    for cell in row:
        print(cell.value, end='\t')
    print()

另外，iter_rows还提供了values_only参数，如果设置values_only=True，会直接取出单元格中的数值组成元组，而非单元格对象了：

import openpyxl
book = openpyxl.load_workbook('学生成绩单.xlsx')
sheet = book['成绩单']
for row in sheet.iter_rows(min_row=7, max_row=10, values_only=True):
    for cell in row:
        print(cell, end='\t')
    print()

使用了values_only=True的参数后，直接打印的是cell而非cell.value。

另外，iter_rows还提供了限制列范围的参数，这样在遍历的行的时候只会取行中指定范围的列：

import openpyxl
book = openpyxl.load_workbook('学生成绩单.xlsx')
sheet = book['成绩单']
for row in sheet.iter_rows(min_row=7,
                           max_row=10,
                           min_col=1,
                           max_col=2,
                           values_only=True):
    for cell in row:
        print(cell, end='\t')
    print()

这样，打印出来的内容就是第7行~第10行的学生编号和姓名这两列的内容。

iter_cols()函数与iter_rows()函数类似，只不过提供的是列生成器。

在下一篇博文中，会介绍如何使用openpyxl对Excel中相关内容进行修改或写入。