Python操作Excel的简明教程

在生活和工作中，我们不可避免地会跟数据“打交道”，用 Excel 存储测试数据以及测试结果是非常常见的。

其实，Python 中有很多专门针对 Excel 进行数据处理的库，比如 xlrd、xlwt、xlutils、openpyxl 以及大数据中常用的 pandas 等，它们的侧重点各有不同，常见的操作 Excel 的库和其作用如表1所示。

表1：常见的操作 Excel 的 Python 库和其作用

库名	作用
xlrd	从 Excel 中读取数据，支持 XLS、XLSX 格式。
xwt	对 Excel 进行修改操作，不支持对 XLSX 格式的修改。
xlutils	在 xlrd 和 xlwt 中，对一个已存在的文件进行修改。
openpyxl	主要针对 XLSX 格式的 Excel 进行读取和编辑。
pandas	可对 CSV 文件进行操作，主要用于大数据分析。

对于上表中的库，我们只需要了解，以后可以根据不同的需要调用不同的库，这里不多解释。本文主要讲解的是用 openpyxl 对 Excel 中的数据进行处理。

openpyxl 的安装命令如下所示：

pip install openpyxl

首先介绍 Excel 的一些基本概念，如图1所示。


Workbook 相当于一个文件，WorkSheet 就是文件里面的每个具体的表格，比如新建 Excel 文件里面的 Sheet1，一个 Workbook 里面有一个或多个 WorkSheet。WorkSheet 中的每个单元格都是一个 Cell，Cell 有行和列的坐标。

1. 操作 Workbook 对象

获取 Workbook 对象的方式有两种，一种是创建一个新的，另一种是导入一个已存在的。

1) 创建Workbook：

# 导入模块
from openpyxl import Workbook
# 创建一个Workbook
wb = Workbook() # 默认生成一个名为Sheet的WorkSheet

2) 导入Workbook：

# 导入模块
from openpyxl import load_workbook
# 导入一个Workbook
wb = load_workbook(filename = './empty_book.xlsx')

3) Workbook 的属性：

• sheetnames：返回所有 WorkSheet 的名称列表，类型为 list。
• worksheets：返回所有 WorkSheet 的列表，类型为 list。
• active：返回当前默认选中的 WorkSheet。

4) Workbook 的方法：

• get_sheet_names：同 sheetnames 属性。
• get_active_sheet：同 active 属性。
• get_sheet_by_name(name)：根据名称获取 WorkSheet。
• remove(worksheet)：删除一个 WorkShee，注意是 WorkSheet 对象，不是名称。
• save(filename)：保存到文件，有写入操作要记得保存。

2. 操作 WorkSheet

1) 获取 WorkSheet 对象：

# 获取默认打开的WorkSheet
ws1 = wb.active
# 创建一个WorkSheet
ws2 = wb.create_sheet() # 可传title和index两个参数
# 通过名称获取WorkSheet
ws3=wb['Sheet1']

2) WorkSheet 的属性:

• rows：返回所有有效数据行，有数据时类型为 generator，无数据时类型为 tuple。
• columns：返回所有有效数据列，类型同 rows。
• max_column：有效数据最大列。
• max_row：有效数据最大行。
• min_column：有效数据最小列,起始为 1。
• min_row：有效数据最大行,起始为 1。
• values：返回所有单元格的值的列表,类型为 tuple。
• title：WorkSheet 的名称。

3) WorkSheet 的方法：

• cell(coordinate=None, row=None, column=None, value=None): 获取指定单元格或设置单元格的值。

3. 操作Cell

1) 获取 Cell 对象：

# 使用WorkSheet的cell方法
c1=ws.cell('A1')
c2=ws.cell(row=1,column=1) # 获取A1单元格
# 通过坐标获取Cell
c3=ws['A1']
# 获取多个
c3=ws['A1:E5'] # 返回多行数据，类型为tuple

2) 设置 Cell 的值：

# 直接使用WorkSheet的cell方法设置
ws.cell(row=1,column=1,value=10)
# 设置Cell对象的value属性
c1=ws.cell('A1')
c1.value=100

3) Cell 的属性：

• column：所在列，起始为1。
• row：所在行，起始为1。
• coordinate：所在坐标，如'A1'。
• parent： 所属的WorkSheet。
• value：单元格的值。

4) Cell 的方法：

• offset(row=0, column=0)：偏移。

4. 完整示例

下面看一个使用 Python 操作 Excel 的完整示例。

from openpyxl import load_workbook,Workbook
from openpyxl.worksheet.worksheet import Worksheet
import os
import random
from datetime import datetime


class ExcelManual:
    def _ _init_ _(self, file_path):
        self.file_path = file_path
        if os.path.exists(file_path):
            self.wb = load_workbook(file_path)
        else:
            self.wb = Workbook()

    def select_sheet(self, name):
        """
        选择表单
        :param name: 表单名称
        :return:
        """
        if name in self.wb.sheetnames:
            self.live_sheet = self.wb[name]
        else:
            self.live_sheet = self.wb.create_sheet(name)
        return self.live_sheet

    def read_cell_value(self, row, column):
        """
        读取一个单元格的数据内容
        :param row: 行
        :param column: 列
        :return:
        """
        if isinstance(self.live_sheet, Worksheet):
            return self.live_sheet.cell(row, column).value

    def write_value_in_cell(self, row, column, value):
        """
        往单元格中写入数据
        :param row:行
        :param column:列
        :param value:值
        :return:
        """
        self.live_sheet.cell(row, column, value)

    def read_row_value(self, row_num):
        """
        读取一行的数据功能
        :return:
        """
        if isinstance(self.live_sheet, Worksheet):
            max_row = self.live_sheet.max_row
            if row_num > max_row:
                print("行数超过表单中的最大行数")
                return
            max_column = self.live_sheet.max_column
            data_list = []
            for i in range(max_column):
                data_list.append(self.live_sheet.cell(row_num, i + 1).value)
            return data_list

    def read_value_by_sheet(self, sheet_name):
        """
        获取表单中的所有数据
        :param sheet_name:
        :return:
        """
        current_sheet = self.wb[sheet_name]
        if isinstance(current_sheet, Worksheet):
            return list(current_sheet.values)

    def close(self):
        """操作完一定要保存并关闭才有效"""
        self.wb.save(self.file_path)
        self.wb.close()

if _ _name_ _ == '_ _main_ _':
    myExcel = ExcelManual("./data.xlsx")
    myExcel.select_sheet("data")
    for i in range(1,3):
        for j in range(1,11):
            if i==1:
                myExcel.write_value_in_cell(j,i,str(random.randint(1,1000)))
            else:
                myExcel.write_value_in_cell(j, i, datetime.now().strftime("%Y-%m-%d"))
    myExcel.close()

    print(myExcel.read_value_by_sheet("data"))

运行结果：
[('954', '2020-10-07'), ('961', '2020-10-07'), ('191', '2020-10-07'), ('419', '2020-10-07'), ('916', '2020-10-07'), ('475', '2020-10- 07'), ('716', '2020-10-07'), ('219', '2020-10-07'), ('713', '2020-10-07'), ('686', '2020-10-07')]

查看生成的数据，如图2所示。

范例分析

1) 范例代码中的 ExcelManual 对 Python 操作进行简单的封装：

• _ _init_ _ 方法中如果路径存在就是加载 Excel 文件，否则是创建一个新的 Excel 文件。
• select_sheet 方法中如果表单名称存在就是选择此表单，否则是创建新的表单。
• read_cell_value 根据 row 和 column 获取指定的 Cell 值，row 和 column 是从 1 开始的。
• write_value_in_cell 根据 row 和 column 写入指定的值。
• read_row_value 根据 row_num 指定一行数据。
• read_value_by_sheet 读取表单中所有的数据。

2) 范例代码的第 82~87 行，循环写入数据。

3) 范例代码的第 90 行，读取表单中所有的数据。