Python使用openpyxl讀取Excel的操作詳解

更新時(shí)間：2025年07月20日 10:43:04 作者：魚(yú)丸丶粗面

本文介紹了使用Python的openpyxl庫(kù)進(jìn)行Excel文件的創(chuàng)建、讀寫(xiě)、數(shù)據(jù)操作、工作簿與工作表管理,包括創(chuàng)建工作簿、加載工作簿、操作Sheet、追加數(shù)據(jù)、查詢(xún)與修改單元格等內(nèi)容,需要的朋友可以參考下

1 概述

1.1 圖示

1.2 安裝第三方庫(kù)

pip install openpyxl

2 工作簿 workbook

2.1 創(chuàng)建：Workbook()

import openpyxl


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

    def create_workbook(self, filename):
        """
        創(chuàng)建工作簿對(duì)象：workbook
        :param filename: 文件名（相對(duì)路徑 或 絕對(duì)路徑）
        :return:
        """
        # 1.聲明 工作簿 對(duì)象
        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.獲取工作簿對(duì)象（獲取屬性前，先要加載工作簿）
        workbook = openpyxl.load_workbook(filename)

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

        # 3.常用的屬性
        print(f'active: {workbook.active}')  # 當(dāng)前活動(dòng)的 sheet 頁(yè)
        print(f'read_only: {workbook.read_only}')  # 是否以只讀方式打開(kāi)
        print(f'encoding: {workbook.encoding}')  # 編碼
        print(f'properties: {workbook.properties}')  # 屬性類(lèi)，如：標(biāo)題、作者、創(chuàng)建日期 等
        # 單個(gè)的屬性信息，可通過(guò)下列形式 "." 出來(lái)
        print(f'title: {workbook.properties.title}')  # 標(biāo)題
        print(f'creator: {workbook.properties.creator}')  # 作者
        print(f'created: {workbook.properties.created}')  # 創(chuàng)建日期


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.獲取單個(gè) sheet
        print('------------')
        print(self.workbook['Sheet'])  # 根據(jù)名稱(chēng)
        print(self.workbook.sheetnames[0])  # 根據(jù)下標(biāo)，如：Sheet
        print(self.workbook.worksheets[0])  # 根據(jù)下標(biāo)，如：<Worksheet "Sheet">


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

2.4 創(chuàng)建 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 開(kāi)始，默認(rèn)末尾）
        self.workbook.create_sheet('Sheet4', 0)

        # 3.注意：若 Sheet 已存在，會(huì)默認(rèn)在名稱(chēng)后面追加數(shù)字，如：Sheet31、Sheet32
        self.workbook.create_sheet('Sheet3')

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


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

2.5 復(fù)制 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 對(duì)象
        sheet = self.workbook['Sheet']

        # 2.復(fù)制 sheet（默認(rèn)名稱(chēng) + copy，如：Sheet Copy）
        self.workbook.copy_worksheet(sheet)

        # 3.保存（只有保存后，才會(huì)生效哦）
        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 移動(dòng) sheet

import openpyxl


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

    def move_sheet(self):
        # 1.移動(dòng) sheet（負(fù)數(shù)=向左移動(dòng)、正數(shù)=向右移動(dòng)、數(shù)值=移動(dòng)位置的個(gè)數(shù)）
        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 追加數(shù)據(jù)

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.獲取要添加數(shù)據(jù)的 sheet 對(duì)象
        sheet: Worksheet = self.workbook['Sheet']
        # 與下列寫(xiě)法等價(jià)，只是多了 "代碼提示"
        # sheet = self.workbook['Sheet']

        # 2.追加數(shù)據(jù)
        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 查詢(xún)數(shù)據(jù)

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 對(duì)象
        sheet: Worksheet = self.workbook['Sheet']
        # 與下列寫(xiě)法等價(jià)，只是多了 "代碼提示"
        # sheet = self.workbook['Sheet']

        # 2.查詢(xún)屬性
        print(f'title: {sheet.title}')  # 標(biāo)題，如：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}')  # 行對(duì)象

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

        # 3.查詢(xún)單元格數(shù)據(jù)
        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 修改數(shù)據(jù)

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 對(duì)象
        sheet: Worksheet = self.workbook['Sheet']
        # 與下列寫(xiě)法等價(jià)，只是多了 "代碼提示"
        # sheet = self.workbook['Sheet']

        # 2.修改數(shù)據(jù)
        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 刪除數(shù)據(jù)

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 對(duì)象
        sheet: Worksheet = self.workbook['Sheet']
        # 與下列寫(xiě)法等價(jià)，只是多了 "代碼提示"
        # sheet = self.workbook['Sheet']

        # 2.刪除數(shù)據(jù)
        sheet.delete_rows(idx=1, amount=1)  # idx 行開(kāi)始(含)，往下刪除 amount 行
        sheet.delete_cols(idx=2, amount=2)  # idx 列開(kāi)始(含)，往右刪除 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 對(duì)象
        sheet: Worksheet = self.workbook['Sheet']
        # 與下列寫(xiě)法等價(jià)，只是多了 "代碼提示"
        # 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 遍歷數(shù)據(jù)

import openpyxl
from openpyxl.worksheet.worksheet import Worksheet


def read_excel(filename):
    """
    遍歷 sheet 中的數(shù)據(jù)
    :param filename: 文件名
    """
    lwb = openpyxl.load_workbook(filename)

    # sheet 列表
    # sheet = lwb['Sheet']
    sheet: Worksheet = lwb['Sheet']  # 手動(dòng)指定類(lèi)型，可用代碼提示

    # 遍歷方式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 查詢(xún)

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 對(duì)象
        sheet: Worksheet = self.workbook['Sheet']
        # 與下列寫(xiě)法等價(jià)，只是多了 "代碼提示"
        # sheet = self.workbook['Sheet']

        # 2.獲取單個(gè)單元格數(shù)據(jù)
        cell1 = sheet['A1']  # A1 單元格
        cell2 = sheet.cell(1, 1)  # 效果同上
        print(cell1.value)
        print(cell2.value)

        # 3.查詢(xún)多個(gè)單元格數(shù)據(jù)
        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 對(duì)象
        sheet: Worksheet = self.workbook['Sheet']
        # 與下列寫(xiě)法等價(jià)，只是多了 "代碼提示"
        # 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()

以上就是Python使用openpyxl讀取Excel的操作詳解的詳細(xì)內(nèi)容，更多關(guān)于Python openpyxl讀取Excel的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: