编程部落

Python使用openpyxl读取Excel的操作详解

作者：

风君子

在

文章目录
    • 目录
    • 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 修改

    pip install openpyxl

    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:UsersAdministratorDesktopTemp1.xlsx
    test.create_workbook('1.xlsx')


    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:UsersAdministratorDesktopTemp1.xlsx
    test.load_workbook('1.xlsx')


    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:UsersAdministratorDesktopTemp1.xlsx
    test.get_sheets()


    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:UsersAdministratorDesktopTemp1.xlsx
    test.create_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:UsersAdministratorDesktopTemp1.xlsx
    test.copy_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:UsersAdministratorDesktopTemp1.xlsx
    test.delete_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:UsersAdministratorDesktopTemp1.xlsx
    test.move_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:UsersAdministratorDesktopTemp1.xlsx
    test.rename_sheet()


    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:UsersAdministratorDesktopTemp1.xlsx
    test.append_data()


    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:UsersAdministratorDesktopTemp1.xlsx
    test.select_data()


    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:UsersAdministratorDesktopTemp1.xlsx
    test.update_data()


    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:UsersAdministratorDesktopTemp1.xlsx
    test.update_data()


    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:UsersAdministratorDesktopTemp1.xlsx
    test.get_sheet_head()


    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)


    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:UsersAdministratorDesktopTemp1.xlsx
    test.get_cell()


    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:UsersAdministratorDesktopTemp1.xlsx
    test.update_cell()

    以上就是Python使用openpyxl读取Excel的操作详解的详细内容，更多关于Python openpyxl读取Excel的资料请关注风君子博客其它相关文章！

    您可能感兴趣的文章:

    • Python使用pandas和openpyxl读取Excel表格的方法详解
    • python使用openpyxl打开及读取excel表格过程
    • python使用openpyxl库读取Excel文件数据
    • 使用Python读取和修改Excel文件(基于xlrd、xlwt和openpyxl模块)
    • python 使用openpyxl读取excel数据

    更多文章

    站内搜索

    标签云

    iphone 三星 信用卡 信贷 华为 小米 微软 快科技 手机 投资理财 支付宝 特斯拉 理财知识 电脑 科技 程序 股票 芯片 苹果 贷款 路由器 银行 银行卡 额度

    热门文章

    友情链接