一、官方文档 https://openpyxl.readthedocs.io 二 openpyxl常用属性函数 常用函数或者属性 说明 openpyxl.load_workbook() 加载excel工作本 Workbook.active 获得默认sheet Workbook.create_sheet() 创建sheet Workbook.get_sheet_names() 已过时, 获得所有sheet名称 workbook.sheetnames 获得所有sheet名称 workbook.get_sheet_by_name(name) 已过时获得指定的sheet对象 workbook[sheetname] 获得指定的sheet对象 workbook.copy_worksheet(soure) 复制sheet sheet[cell] 获取单个单元格 sheet.cell(self, row, column, value=None) 获取单个单元格 sheet[cell,cell] 访问多个单元格 sheet.iter_rows(min_row, max_col, max_row) 返回多行，用于访问多个单元格 sheet.iter_cols(min_row, max_col, max_row) 返回多列，用于访问多个单元格 sheet.rows 获取所有行 sheet.columns 获取所有列 cell.value 获取属性值 sheet.merge_cells() 合并单元格 sheet.unmerge_cells() 取消合并单元格 三 读取excel 读取整体流程如下： 加载工作本 获取标签页 获取指定区域的单元格 获取单个单元格对象 通过单元格对象获取值 3.1 创建一个名为zszxz.xlsx excel文件；其中标签sheet名为zszxz; 列A B C 如下； id num name 1 100 zszxz 2 101 smile 3 102 kitty 4 103 wolf 5 104 cloud 6 105 water 3.2 支持操作excel和图像(安装第三方库即可) pip install openpyxl pip install pillow 3.3 获取所有标签页名称 # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz.xlsx' # 加载工作本 workbook = openpyxl.load_workbook(path) # 获得所有sheet标签页 sheet_names = workbook.get_sheet_names() print(sheet_names) 输出 ['zszxz'] 正确的获取方式 # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz.xlsx' # 加载工作本 workbook = openpyxl.load_workbook(path) # 获得所有标签页名称 print(workbook.sheetnames) 3.4 获取指定标签页对象 不建议通过方法获得标签页对象，建议通过属性方式获取； # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz.xlsx' # 加载工作本 workbook = openpyxl.load_workbook(path) # 获得指定的sheet sheet = workbook.get_sheet_by_name('zszxz') print(sheet) 输出 <Worksheet "zszxz"> 正确的获取方式 # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz.xlsx' # 加载工作本 workbook = openpyxl.load_workbook(path) # 获取sheet对象 sheet = workbook['zszxz'] print(sheet) 输出 <Worksheet "zszxz"> ==>>注：也可以通过循环工作本方式获取sheet 3.5 复制sheet 在已有的sheet上可以进行复制一个副本； # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz.xlsx' # 加载工作本 workbook = openpyxl.load_workbook(path) # 获取sheet对象 sheet = workbook['zszxz'] # 复制sheet cp_sheet = workbook.copy_worksheet(sheet) print(cp_sheet) 输出 <Worksheet "zszxz Copy"> 3.5 获取指定一个单元格对象 指定获取A1单元格对象 # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz.xlsx' # 加载工作本 workbook = openpyxl.load_workbook(path) # 获取sheet对象 sheet = workbook['zszxz'] # 获取指定单元格 cell = sheet['A1'] print(cell) 输出 <Cell 'zszxz'.A1> 方式二 # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz.xlsx' # 加载工作本 workbook = openpyxl.load_workbook(path) # 获取sheet对象 sheet = workbook['zszxz'] # 获取指定单元格 cell = sheet.cell(row=1, column=1) print(cell) 输出 <Cell 'zszxz'.A1> 3.6 访问多个单元格 访问单元格获取单元格对象； # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz.xlsx' # 加载工作本 workbook = openpyxl.load_workbook(path) # 获取sheet对象 sheet = workbook['zszxz'] cells_range = sheet['A1':'C1'] for cells in cells_range: for cell in cells: print(cell) 输出 <Cell 'zszxz'.A1> <Cell 'zszxz'.B1> <Cell 'zszxz'.C1> 方式二 # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz.xlsx' # 加载工作本 workbook = openpyxl.load_workbook(path) # 获取sheet对象 sheet = workbook['zszxz'] # 获取指定范围的行 row = sheet.iter_rows(min_row=1, max_col=3, max_row=1) for cell in row: print(cell) 输出 (<Cell 'zszxz'.A1>, <Cell 'zszxz'.B1>, <Cell 'zszxz'.C1>) sheet.iter_cols(min_row, max_col, max_row)通用的 3.7 获取全部行 # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz.xlsx' # 加载工作本 workbook = openpyxl.load_workbook(path) # 获取sheet对象 sheet = workbook['zszxz'] # 获取所有行 for row in sheet.rows: print(row) 输出 (<Cell 'zszxz'.A1>, <Cell 'zszxz'.B1>, <Cell 'zszxz'.C1>) (<Cell 'zszxz'.A2>, <Cell 'zszxz'.B2>, <Cell 'zszxz'.C2>) (<Cell 'zszxz'.A3>, <Cell 'zszxz'.B3>, <Cell 'zszxz'.C3>) (<Cell 'zszxz'.A4>, <Cell 'zszxz'.B4>, <Cell 'zszxz'.C4>) (<Cell 'zszxz'.A5>, <Cell 'zszxz'.B5>, <Cell 'zszxz'.C5>) (<Cell 'zszxz'.A6>, <Cell 'zszxz'.B6>, <Cell 'zszxz'.C6>) (<Cell 'zszxz'.A7>, <Cell 'zszxz'.B7>, <Cell 'zszxz'.C7>) 同理 sheet.columns获取所有列 3.8 获取值 cell.value获取属性值 # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz.xlsx' # 加载工作本 workbook = openpyxl.load_workbook(path) # 获取sheet对象 sheet = workbook['zszxz'] cells_range = sheet['A1':'C1'] for cells in cells_range: for cell in cells: # 获取属性值 print(cell.value) 输出 id num name 四 写入 excel 4.1 写入文本 # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz_write.xlsx' # 创建一个工作本 workbook = openpyxl.Workbook() # 创建一个 sheet sheet = workbook.create_sheet('zszxz') # 写入文本形式 sheet['A1'] = 'zszxz666' # 读取 print(sheet['A1'].value) # 保存 workbook.save(path) 输出 zszxz666 4.2 写入数字 # -*- coding: utf-8 -*- import openpyxl import datetime path = r'C:xxzszxz_write.xlsx' # 创建一个工作本 workbook = openpyxl.Workbook() # 创建一个 sheet sheet = workbook.create_sheet('zszxz') # 写入值数字形式 sheet['A2'] = datetime.datetime(2010, 7, 21) print(sheet['A2'].value) # 保存 workbook.save(path) 输出 2010-07-21 00:00:00 4.3 写入公式 # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz_write.xlsx' # 创建一个工作本 workbook = openpyxl.Workbook() # 创建一个 sheet sheet = workbook.create_sheet('zszxz') # 写入公式 sheet['A3'] = '=SUM(1, 1)' print(sheet['A3'].value) # 保存 workbook.save(path) 输出 =SUM(1, 1) 实际中A3单元格值为2 4.4 合并单元格 # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz_write.xlsx' # 创建一个工作本 workbook = openpyxl.Workbook() # 创建一个 sheet sheet = workbook.create_sheet('zszxz') # 合并单元格 sheet.merge_cells('A2:D2') # 保存 workbook.save(path) 4.4.1取消合并单元格 # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz_write.xlsx' # 创建一个工作本 workbook = openpyxl.Workbook() # 创建一个 sheet sheet = workbook.create_sheet('zszxz') # 取消合并单元格 sheet.unmerge_cells('A2:D2') # 保存 workbook.save(path) 4.5 插入图片 # -*- coding: utf-8 -*- import openpyxl from openpyxl.drawing.image import Image path = r'C:xxzszxz_write.xlsx' # 创建一个工作本 workbook = openpyxl.Workbook() # 创建一个 sheet sheet = workbook.create_sheet('zszxz') # 设置图像 img = Image(r'C:xxmain.jpg') # 设置图像单元格说明 sheet['A1'] = 'you are my angel' # 插入图片 sheet.add_image(img, 'A1') # 保存 workbook.save(path) 4.6 隐藏轮廓 # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz_write.xlsx' wb = openpyxl.Workbook() ws = wb.create_sheet() # 隐藏行 A-B ws.column_dimensions.group('A', 'B', hidden=True) # 隐藏 列 1 -5 ws.row_dimensions.group(1, 5, hidden=True) wb.save(path) 4.7 设置行高列高 # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz_write.xlsx' # 创建一个工作本 workbook = openpyxl.Workbook() # 创建一个 sheet sheet = workbook.create_sheet('zszxz') # 写入文本形式 sheet['A1'] = 'zszxz666' # 设置行高 sheet.row_dimensions[1].height = 50 # 设置列高 sheet.column_dimensions['A'].width = 30 workbook.save(path) 五 删除sheet # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz_write.xlsx' # 加载工作本 workbook = openpyxl.load_workbook(path) # 获得sheet sheet = workbook['Sheet'] # 移除sheet workbook.remove(sheet) # 保存 workbook.save(path) 六 更新工作表 # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz_write.xlsx' # 加载工作本 workbook = openpyxl.load_workbook(path) # 获得sheet sheet = workbook['zszxz'] # 获得值 val = sheet['A1'].value print(val) # 重新赋值 new_val = sheet['A1'].value = 'zszxz' print(new_val) # 保存 workbook.save(path) # -*- coding: utf-8 -*- import openpyxl path = r'C:xxzszxz_write.xlsx' # 加载工作本 workbook = openpyxl.load_workbook(path) # 获得sheet sheet = workbook['zszxz'] # 获得值 val = sheet['A1'].value print(val) # 重新赋值 new_val = sheet['A1'].value = 'zszxz' print(new_val) # 保存 workbook.save(path) 输出 zszxz666 zszxz