Python 读取 Excel 详解（openyxl）

1 概述

1.1 图示

1.2 安装第三方库

若执行 pip install openpyxl 提示失败，则请看以下博客
CSDN 博客：https://blog.csdn.net/qq_34745941/article/details/106341898

2 工作簿 workbook

2.1 创建：Workbook()

import openpyxl


class Excel(object):
    def __init__(self):
        pass

    def create_workbook(self, filename):
        """
        创建工作簿对象：workbook
        :param filename: 文件名（相对路径 或 绝对路径）
        :return:
        """
        # 1.声明 工作簿 对象
        workbook = openpyxl.Workbook()

        # 2.保存 工作簿（若已存在，则覆盖）
        workbook.save(filename)


if __name__ == '__main__':
    test = Excel()
    # C:\Users\Administrator\Desktop\Temp\1.xlsx
    test.create_workbook('1.xlsx')

在这里插入图片描述

2.2 常用属性：load_workbook()

import openpyxl


class Excel(object):
    def __init__(self):
        pass

    def load_workbook(self, filename):
        # 1.获取工作簿对象（获取属性前，先要加载工作簿）
        workbook = openpyxl.load_workbook(filename)

        # 2.查看工作簿支持的方法和属性
        # print(help(workbook))

        # 3.常用的属性
        print(f'active: {workbook.active}')  # 当前活动的 sheet 页
        print(f'read_only: {workbook.read_only}')  # 是否以只读方式打开
        print(f'encoding: {workbook.encoding}')  # 编码
        print(f'properties: {workbook.properties}')  # 属性类，如：标题、作者、创建日期 等
        # 单个的属性信息，可通过下列形式 "." 出来
        print(f'title: {workbook.properties.title}')  # 标题
        print(f'creator: {workbook.properties.creator}')  # 作者
        print(f'created: {workbook.properties.created}')  # 创建日期


if __name__ == '__main__':
    test = Excel()
    # C:\Users\Administrator\Desktop\Temp\1.xlsx
    test.load_workbook('1.xlsx')

2.3 获取 sheet

import openpyxl


class Excel(object):
    def __init__(self, filename):
        self.workbook = openpyxl.load_workbook(filename)

    def get_sheets(self):
        # 1.获取 sheet 列表
        print(self.workbook.sheetnames)  # 如：['Sheet', 'Sheet1']
        print(self.workbook.worksheets)  # 如：[<Worksheet "Sheet">, <Worksheet "Sheet1">]
        # print(self.workbook.get_sheet_names())  # deprecated function 已弃用

        # 2.获取单个 sheet
        print('------------')
        print(self.workbook['Sheet'])  # 根据名称
        print(self.workbook.sheetnames[0])  # 根据下标，如：Sheet
        print(self.workbook.worksheets[0])  # 根据下标，如：<Worksheet "Sheet">


if __name__ == '__main__':
    test = Excel('1.xlsx')
    # C:\Users\Administrator\Desktop\Temp\1.xlsx
    test.get_sheets()

2.4 创建 sheet

import openpyxl


class Excel(object):
    def __init__(self, filename):
        self.workbook = openpyxl.load_workbook(filename)
        self.filename = filename

    def create_sheet(self):
        # 1.末尾追加
        self.workbook.create_sheet('Sheet3')

        # 2.指定位置添加（index 从 0 开始，默认末尾）
        self.workbook.create_sheet('Sheet4', 0)

        # 3.注意：若 Sheet 已存在，会默认在名称后面追加数字，如：Sheet31、Sheet32
        self.workbook.create_sheet('Sheet3')

        # 4.保存（只有保存后，才会生效哦）
        self.workbook.save(self.filename)


if __name__ == '__main__':
    test = Excel('1.xlsx')
    # C:\Users\Administrator\Desktop\Temp\1.xlsx
    test.create_sheet()

2.5 复制 sheet

import openpyxl


class Excel(object):
    def __init__(self, filename):
        self.workbook = openpyxl.load_workbook(filename)
        self.filename = filename

    def copy_sheet(self):
        # 1.获取 sheet 对象
        sheet = self.workbook['Sheet']

        # 2.复制 sheet（默认名称 + copy，如：Sheet Copy）
        self.workbook.copy_worksheet(sheet)

        # 3.保存（只有保存后，才会生效哦）
        self.workbook.save(self.filename)


if __name__ == '__main__':
    test = Excel('1.xlsx')
    # C:\Users\Administrator\Desktop\Temp\1.xlsx
    test.copy_sheet()

2.6 删除 sheet

import openpyxl


class Excel(object):
    def __init__(self, filename):
        self.workbook = openpyxl.load_workbook(filename)
        self.filename = filename

    def delete_sheet(self):
        # 1.删除 sheet
        del self.workbook['Sheet Copy']

        # 2.保存后生效
        self.workbook.save(self.filename)


if __name__ == '__main__':
    test = Excel('1.xlsx')
    # C:\Users\Administrator\Desktop\Temp\1.xlsx
    test.delete_sheet()

2.7 移动 sheet

import openpyxl


class Excel(object):
    def __init__(self, filename):
        self.workbook = openpyxl.load_workbook(filename)
        self.filename = filename

    def move_sheet(self):
        # 1.移动 sheet（负数=向左移动、正数=向右移动、数值=移动位置的个数）
        self.workbook.move_sheet('Sheet', -1)

        # 2.保存后生效
        self.workbook.save(self.filename)


if __name__ == '__main__':
    test = Excel('1.xlsx')
    # C:\Users\Administrator\Desktop\Temp\1.xlsx
    test.move_sheet()

2.8 重命名 Sheet

import openpyxl


class Excel(object):
    def __init__(self, filename):
        self.workbook = openpyxl.load_workbook(filename)
        self.filename = filename

    def rename_sheet(self):
        # 1.重命名 sheet
        self.workbook['Sheet1'].title = 'Sheet111'

        # 2.保存后生效
        self.workbook.save(self.filename)


if __name__ == '__main__':
    test = Excel('1.xlsx')
    # C:\Users\Administrator\Desktop\Temp\1.xlsx
    test.rename_sheet()

3 工作表 sheet

3.1 追加数据

import openpyxl
from openpyxl.worksheet.worksheet import Worksheet


class Excel(object):
    def __init__(self, filename):
        self.workbook = openpyxl.load_workbook(filename)
        self.filename = filename

    def append_data(self):
        # 1.获取要添加数据的 sheet 对象
        sheet: Worksheet = self.workbook['Sheet']
        # 与下列写法等价，只是多了 "代码提示"
        # sheet = self.workbook['Sheet']

        # 2.追加数据
        sheet.append(['姓名', '性别', '年龄'])
        sheet.append(['张三', '女', 18])
        sheet.append(['李四', '男', 19])
        sheet.append(['王五', '女', 20])

        # 3.保存后生效
        self.workbook.save(self.filename)


if __name__ == '__main__':
    test = Excel('1.xlsx')
    # C:\Users\Administrator\Desktop\Temp\1.xlsx
    test.append_data()

在这里插入图片描述

3.2 查询数据

import openpyxl
from openpyxl.worksheet.worksheet import Worksheet


class Excel(object):
    def __init__(self, filename):
        self.workbook = openpyxl.load_workbook(filename)
        self.filename = filename

    def select_data(self):
        # 1.获取 sheet 对象
        sheet: Worksheet = self.workbook['Sheet']
        # 与下列写法等价，只是多了 "代码提示"
        # sheet = self.workbook['Sheet']

        # 2.查询属性
        print(f'title: {sheet.title}')  # 标题，如：Sheet
        print(f'dimensions: {sheet.dimensions}')  # 表格大小，如：A1:B3
        print(f'min_row: {sheet.min_row}')  # 最小行 1
        print(f'max_row: {sheet.max_row}')  # 最大行 3
        print(f'rows: {sheet.rows}')  # 行对象

        print(f'min_column: {sheet.min_column}')  # 最小列 1
        print(f'max_column: {sheet.max_column}')  # 最大列 2
        print(f'columns: {sheet.columns}')  # 列对象

        # 3.查询单元格数据
        print(sheet['A1'].value)

        # 4.保存后生效
        self.workbook.save(self.filename)


if __name__ == '__main__':
    test = Excel('1.xlsx')
    # C:\Users\Administrator\Desktop\Temp\1.xlsx
    test.select_data()

3.3 修改数据

import openpyxl
from openpyxl.worksheet.worksheet import Worksheet


class Excel(object):
    def __init__(self, filename):
        self.workbook = openpyxl.load_workbook(filename)
        self.filename = filename

    def update_data(self):
        # 1.获取 sheet 对象
        sheet: Worksheet = self.workbook['Sheet']
        # 与下列写法等价，只是多了 "代码提示"
        # sheet = self.workbook['Sheet']

        # 2.修改数据
        sheet['A1'] = '我是A1'
        sheet.cell(row=2, column=1).value = '我是A2'
        sheet.cell(row=3, column=1, value='我是A3')

        # 3.保存后生效
        self.workbook.save(self.filename)


if __name__ == '__main__':
    test = Excel('1.xlsx')
    # C:\Users\Administrator\Desktop\Temp\1.xlsx
    test.update_data()

3.4 删除数据

import openpyxl
from openpyxl.worksheet.worksheet import Worksheet


class Excel(object):
    def __init__(self, filename):
        self.workbook = openpyxl.load_workbook(filename)
        self.filename = filename

    def update_data(self):
        # 1.获取 sheet 对象
        sheet: Worksheet = self.workbook['Sheet']
        # 与下列写法等价，只是多了 "代码提示"
        # sheet = self.workbook['Sheet']

        # 2.删除数据
        sheet.delete_rows(idx=1, amount=1)  # idx 行开始(含)，往下删除 amount 行
        sheet.delete_cols(idx=2, amount=2)  # idx 列开始(含)，往右删除 amount 行

        # 3.保存后生效
        self.workbook.save(self.filename)


if __name__ == '__main__':
    test = Excel('1.xlsx')
    # C:\Users\Administrator\Desktop\Temp\1.xlsx
    test.update_data()

3.5 获取表头

import openpyxl
from openpyxl.worksheet.worksheet import Worksheet


class Excel(object):
    def __init__(self, filename):
        self.workbook = openpyxl.load_workbook(filename)
        self.filename = filename

    def get_sheet_head(self):
        # 1.获取 sheet 对象
        sheet: Worksheet = self.workbook['Sheet']
        # 与下列写法等价，只是多了 "代码提示"
        # sheet = self.workbook['Sheet']

        # 2.获取表头，其中：values_only=True 表示只获取值
        for i in sheet.iter_cols(min_col=1, max_row=1, values_only=True):
            print(i)


if __name__ == '__main__':
    test = Excel('1.xlsx')
    # C:\Users\Administrator\Desktop\Temp\1.xlsx
    test.get_sheet_head()

3.6 遍历数据

import openpyxl
from openpyxl.worksheet.worksheet import Worksheet


def read_excel(filename):
    """
    遍历 sheet 中的数据
    :param filename: 文件名
    """
    lwb = openpyxl.load_workbook(filename)

    # sheet 列表
    # sheet = lwb['Sheet']
    sheet: Worksheet = lwb['Sheet']  # 手动指定类型，可用代码提示

    # 遍历方式1：按行遍历
    for row in sheet.iter_rows():
        for cell in row:
            print(cell.row, cell.column, cell.value)

    print('------------------ 分割线1 -----------------')

    # 遍历方式2：按列遍历
    for col in sheet.iter_cols():
        for cell in col:
            print(cell.row, cell.column, cell.value)


if __name__ == '__main__':
    fileName = '1.xlsx'
    read_excel(fileName)

4 单元格 cell

4.1 查询

import openpyxl
from openpyxl.worksheet.worksheet import Worksheet


class Excel(object):
    def __init__(self, filename):
        self.workbook = openpyxl.load_workbook(filename)
        self.filename = filename

    def get_cell(self):
        # 1.获取 sheet 对象
        sheet: Worksheet = self.workbook['Sheet']
        # 与下列写法等价，只是多了 "代码提示"
        # sheet = self.workbook['Sheet']

        # 2.获取单个单元格数据
        cell1 = sheet['A1']  # A1 单元格
        cell2 = sheet.cell(1, 1)  # 效果同上
        print(cell1.value)
        print(cell2.value)

        # 3.查询多个单元格数据
        cells = sheet['A1:B2']

        for item in cells:
            for cell in item:
                print(cell.row, cell.column, cell.value)


if __name__ == '__main__':
    test = Excel('1.xlsx')
    # C:\Users\Administrator\Desktop\Temp\1.xlsx
    test.get_cell()

4.2 修改

import openpyxl
from openpyxl.worksheet.worksheet import Worksheet


class Excel(object):
    def __init__(self, filename):
        self.workbook = openpyxl.load_workbook(filename)
        self.filename = filename

    def update_cell(self):
        # 1.获取 sheet 对象
        sheet: Worksheet = self.workbook['Sheet']
        # 与下列写法等价，只是多了 "代码提示"
        # sheet = self.workbook['Sheet']

        # 3.修改 单元格 A1 的值为 'A1'
        cell1 = sheet['A1']  # A1 单元格
        cell1.value = 'A1'  # 赋值

        # 3.保存后生效
        self.workbook.save(self.filename)


if __name__ == '__main__':
    test = Excel('1.xlsx')
    # C:\Users\Administrator\Desktop\Temp\1.xlsx
    test.update_cell()

文章目录

1 概述

1.1 图示

1.2 安装第三方库

2 工作簿 workbook

2.1 创建：Workbook()

2.2 常用属性：load_workbook()

2.3 获取 sheet

2.4 创建 sheet

2.5 复制 sheet

2.6 删除 sheet

2.7 移动 sheet

2.8 重命名 Sheet

3 工作表 sheet

3.1 追加数据

3.2 查询数据

3.3 修改数据

3.4 删除数据

3.5 获取表头

3.6 遍历数据

4 单元格 cell

4.1 查询

4.2 修改