1.安装
pip install openpyxl
2.模块openpyxl的基本使用
import openpyxl import datetime # 实例化对象excel对象 excel_obj = openpyxl.Workbook() # excel 内当前活跃的sheet工作表 excel_obj_sheet = excel_obj.active # 给单元格赋值 excel_obj_sheet['A1'] = 4 excel_obj_sheet.append([1, 2, 3]) excel_obj_sheet['A3'] = datetime.datetime.now() # 文件保存 excel_obj.save('sample.xlsx')
运行后，文件内容：
3.模块openpyxl详细
3.1创建工作簿
不需要提前在文件系统上创建文件，直接就可以使用 openpyxl 开始创建表格。先导入 Workbook 类，再使用 Workbook.active 方法获取一个工作表：
from openpyxl import Workbook wb = Workbook() ws = wb.active
默认情况下 Workbook.active(value) 方法中的 value 为 0，即使用此方法获得第一个工作表，我们可以修改此值。也可以使用 Workbook.create_sheet() 方法创建新的工作表：
# 在末尾插入（默认） ws1 = wb.create_sheet("Mysheet") # 插入第一个位置 ws2 = wb.create_sheet("Mysheet", 0) # 倒数第二个位置插入 ws3 = wb.create_sheet("Mysheet", -1)
我们可以随时通过 Worksheet.title 属性更改工作表名称：
ws.title = "New Title"
默认情况下，工作表选项卡的背景颜色为白色，我们可以通过 Worksheet.sheet_properties.tabColor 属性修改颜色：
ws.sheet_properties.tabColor = "1072BA"
给工作表命名后，就可以将其作为工作簿的键值，以指向对应的工作表，并可以使用 Workbook.sheetname 属性查看工作簿中所有工作表的名称，亦可以遍历工作表：
ws3 = wb["New Title"] print(wb.sheetnames) # ['Sheet2', 'New Title', 'Sheet1'] for sheet in wb: print(sheet.title)
我们可以复制某个工作簿，创建一个副本。该行为仅复制单元格（值、样式、超链接、注释）和某些工作表属性（尺寸、格式、属性），如果工作簿以 read-only 或 write-only 只读模式打开，则不能复制工作表：
source = wb.active
target = wb.copy_worksheet(source)
3.2操作数据
单元格可以直接作为工作表中的键值进行访问，例如返回 A4 处的单元格，如果不存在则创建一个单元格，可以直接分配值：
c = ws['A4'] ws['A4'] = 4
一个单元格
通过 Worksheet.cell() 方法可以使用 行 和 列 定位要访问的单元格：
d = ws.cell(row=4, column=2, value=10)
多个单元格
我们可以通过切片访问单元格范围，行或列的范围可以用类似方法获得：
cell_range = ws['A1':'C2'] colC = ws['C'] col_range = ws['C:D'] row10 = ws[10] row_range = ws[5:10]
也可以使用 Worksheet.iter_rows() 或 Worksheet.iter_cols() 方法获取行、列，但是由于性能原因，这两个方法在 只读 模式下不可用：
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2): for cell in row: print(cell) # <Cell Sheet1.A1> # <Cell Sheet1.B1> # <Cell Sheet1.C1> # <Cell Sheet1.A2> # <Cell Sheet1.B2> # <Cell Sheet1.C2> for col in ws.iter_cols(min_row=1, max_col=3, max_row=2): for cell in col: print(cell) # <Cell Sheet1.A1> # <Cell Sheet1.A2> # <Cell Sheet1.B1> # <Cell Sheet1.B2> # <Cell Sheet1.C1> # <Cell Sheet1.C2>
如果需要遍历所有行或列，则可以使用 Worksheet.rows 或 Worksheet.columns 属性，但是同样在 只读 模式下不可用：
ws = wb.active ws['C9'] = 'hello world' tuple(ws.rows) # ((<Cell Sheet.A1>, <Cell Sheet.B1>, <Cell Sheet.C1>), # (<Cell Sheet.A2>, <Cell Sheet.B2>, <Cell Sheet.C2>), # (<Cell Sheet.A3>, <Cell Sheet.B3>, <Cell Sheet.C3>), # (<Cell Sheet.A4>, <Cell Sheet.B4>, <Cell Sheet.C4>), # (<Cell Sheet.A5>, <Cell Sheet.B5>, <Cell Sheet.C5>), # (<Cell Sheet.A6>, <Cell Sheet.B6>, <Cell Sheet.C6>), # (<Cell Sheet.A7>, <Cell Sheet.B7>, <Cell Sheet.C7>), # (<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>), # (<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>)) tuple(ws.columns) # ((<Cell Sheet.A1>, # <Cell Sheet.A2>, # <Cell Sheet.A3>, # <Cell Sheet.A4>, # <Cell Sheet.A5>, # <Cell Sheet.A6>, # ... # <Cell Sheet.C9>))
仅值
如果只需要工作表中的值，则可以使用 Worksheet.columns 属性，这会遍历工作表中的所有行，但仅返回单元格的值：
for row in ws.values: for value in row: print(value)
通过 Worksheet.iter_rows() 并 Worksheet.iter_cols() 可以获取 values_only 参数，只返回单元格的值：
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2, values_only=True): print(row) # (None, None, None) # (None, None, None)
如果我们只需要工作表的 最大行数 和 最大列数，可以使用 max_row 和 max_column 属性获取，例如一个4行15列的工作表：
print(ws.max_row) # 4 print(ws.max_column) # 15
3.3数据存储
有了 Cell 就可以为其分配一个值：
c.value = 'hello, world' print(c.value) # 'hello, world' d.value = 3.14 print(d.value) # 3.14
保存到文件
保存工作簿的最简单、安全的方法是使用对象的 Workbook.save() 方法：
wb = Workbook() wb.save('balances.xlsx')
如果文件已经存在，此操作将覆盖现有文件，不会抛出异常或警告。
另存为流
如果要将文件保存到流中，例如在使用Web应用程序（Pyramid、Flask、Django）时，只需使用 NamedTemporaryFile() 方法即可：
from tempfile import NamedTemporaryFile from openpyxl import Workbook wb = Workbook() with NamedTemporaryFile() as tmp: wb.save(tmp.name) tmp.seek(0) stream = tmp.read()
我们也可以指定属性 template=True，将工作簿另存为模板：
wb = load_workbook('document.xlsx') wb.template = True wb.save('document_template.xltx')
或将 template 属性设置为 False（默认），以另存为文档：
wb = load_workbook('document_template.xltx') wb.template = False wb.save('document.xlsx', as_template=False)
3.4从文件加载
可以通过 openpyxl.load_workbook() 打开现有的工作簿：
from openpyxl import load_workbook wb2 = load_workbook('test.xlsx') print(wb2.sheetnames) # ['Sheet2', 'New Title', 'Sheet1']
3.5实例
写工作簿
from openpyxl import Workbook from openpyxl.utils import get_column_letter wb = Workbook() dest_filename = 'empty_book.xlsx' ws1 = wb.active ws1.title = "range names" for row in range(1, 40): ws1.append(range(600)) ws2 = wb.create_sheet(title="Pi") ws2['F5'] = 3.14 ws3 = wb.create_sheet(title="Data") for row in range(10, 20): for col in range(27, 54): _ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col))) print(ws3['AA10'].value) # AA wb.save(filename = dest_filename)
读工作簿
from openpyxl import load_workbook wb = load_workbook(filename = 'empty_book.xlsx') sheet_ranges = wb['range names'] print(sheet_ranges['D18'].value) # 3
使用数字格式
import datetime from openpyxl import Workbook wb = Workbook() ws = wb.active # 使用 Python datetime 设置日期 ws['A1'] = datetime.datetime(2010, 7, 21) ws['A1'].number_format # 'yyyy-mm-dd h:mm:ss'
使用公式
from openpyxl import Workbook wb = Workbook() ws = wb.active # 添加一个简单的公式 ws["A1"] = "=SUM(1, 1)" wb.save("formula.xlsx")
需要注意，函数名称必须为 英文 名称，函数参数必须用 逗号 分隔。openpyxl 不会评估公式，但是可以检查公式的名称：
from openpyxl.utils import FORMULAE print("HEX2DEC" in FORMULAE) # True
合并单元格
from openpyxl.workbook import Workbook wb = Workbook() ws = wb.active ws.merge_cells('A2:D2') ws.unmerge_cells('A2:D2') # 或者 ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4) ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
合并单元格时，除左上角以外的所有单元格都将从工作表中被删除。
插入图像
from openpyxl import Workbook from openpyxl.drawing.image import Image wb = Workbook() ws = wb.active ws['A1'] = 'You should see three logos below' # 创建图像 img = Image('logo.png') # 添加到工作表并锚定在单元格旁边 ws.add_image(img, 'A1') wb.save('logo.xlsx')
折叠与轮廓
import openpyxl wb = openpyxl.Workbook() ws = wb.create_sheet() ws.column_dimensions.group('A','D', hidden=True) ws.row_dimensions.group(1,10, hidden=True) wb.save('group.xlsx')
4.通用方案读取类
from os.path import isfile from openpyxl import load_workbook class ExcelToDict: """ 将Excel文件对象转成Python字典对象 """ def __init__(self, file_dir, title_row=0): # 工作簿文件的路径 self.file_dir = file_dir # 标题行位于第几行 self.title_row = int(title_row) self.data_dict = {} self.work_book = None def open_object(self): """打开工作簿对象""" valid = isfile(self.file_dir) # file_dir指向的文件是否不存在 if not valid: raise Exception('文件路径 {0} 不存在'.format(self.file_dir)) self.work_book = load_workbook(filename=self.file_dir) def read_excel(self): """读取工作簿数据""" if not self.work_book: raise Exception('需要先调用 open_object() 方法以打开工作簿对象') for sheet_name in self.work_book.sheetnames: # 每个工作表的字典 data_dict_sheet = {'title_row': [], 'value_row': {}} # 获取工作表对象 ws = self.work_book[sheet_name] # 预先创建工作表中每一行的字典 for i in range(ws.max_row - 1 - self.title_row): data_dict_sheet['value_row'][i] = {} # 遍历所有列 columns = tuple(ws.columns) for column in columns: # 每一列的标题 title = column[self.title_row].value # 记录每列的标题 data_dict_sheet['title_row'].append(title) row_num = 0 # 遍历每一列中的所有值 for col in column: # 忽略每一列的标题行 if column.index(col) <= self.title_row: continue data_dict_sheet['value_row'][row_num][title] = col.value row_num += 1 # 记录每个工作表的数据字段 self.data_dict[sheet_name] = data_dict_sheet def check(self, check_item=None, sheet_name=None, sheet_index=0): """ 在所选工作表中校验是否包含业务需要的所有标题名称 :param check_item: 所选工作表中需要校验的标题列表 :param sheet_name: 以名称形式选择工作表（优先） :param sheet_index: 以下标形式选择工作表 :return: {'result': True, 'exception': None} """ if not self.data_dict: return {'result': False, 'exception': '需要先调用 read_excel() 方法以读取工作簿数据'} if check_item is None: check_item = [] if sheet_name: if sheet_name not in self.data_dict: return {'result': False, 'exception': '不存在名为 {0} 的工作表'.format(sheet_name)} # 直接获得对应的工作表数据 data_sheet = self.data_dict[sheet_name] else: # 通过下标获取对应的工作表名称 data_dict_keys = tuple(self.data_dict.keys()) if len(data_dict_keys) <= int(sheet_index): return {'result': False, 'exception': '不存在下标为 {0} 的工作表'.format(sheet_index)} _sheet_name = data_dict_keys[int(sheet_index)] # 间接获得对应的工作表数据 data_sheet = self.data_dict[_sheet_name] # 判断工作表中是否包含业务需要的所有标题 if not set(check_item).issubset(set(data_sheet['title_row'])): return {'result': False, 'exception': '工作表中未包含业务需要的 {0} 标题'.format(check_item)} return {'result': True, 'exception': None} if __name__ == '__main__': excel_to_dict = ExcelToDict('C:/Users/hekaiyou/Desktop/新建 Microsoft Excel 工作表.xlsx') excel_to_dict.open_object() print('工作簿对象：', excel_to_dict.work_book) excel_to_dict.read_excel() print('工作簿数据：', excel_to_dict.data_dict) print('工作簿校验（异常演示）：', excel_to_dict.check(['标题四'])) print('工作簿校验（正常演示）：', excel_to_dict.check(['标题一', '标题二']))
5.参考代码
#coding=utf-8 import openpyxl from openpyxl import Workbook # 默认可读写，若有需要可以指定write_only和read_only为True # 新建Excel表格 wb = Workbook('new_excel.xlsx') # 加载本地已有文件 wb2 = openpyxl.load_workbook('excel.xlsx') # 获取所有sheet的名称 # 老版openpyxl支持方法，新版建议直接使用属性sheetnames # print(wb.get_sheet_names()) # print(wb2.get_sheet_names()) print('sheetnames') print(wb.sheetnames) print(wb2.sheetnames) # 根据sheet名字获得sheet # 老版办法，建议使用新版本方法 # a_sheet = wb2.get_sheet_by_name('Sheet1') a_sheet = wb2['Sheet1'] # 获取sheet的标题 print(a_sheet.title) # 获取当前显示的sheet页，两种方式都可以 sheet = wb2.active # sheet = wb2.get_active_sheet # 获取单元格 a4 = sheet['A4'] print('(%s,%s) is %s.' % (a4.column, a4.row, a4.value)) # 获得最大列和最大行 print('获得最大列和最大行') print(sheet.max_column) print(sheet.max_row) # 获取行和列 # sheet.rows为生成器, 里面是每一行的数据，每一行又由一个tuple包裹。 # sheet.columns类似，不过里面是每个tuple是每一列的单元格。 # 因为按行，所以返回A1, B1, C1这样的顺序 for row in sheet.rows: for cell in row: print('(%s,%s) is %s.' % (cell.column, cell.row, cell.value)) print('================') # A1, A2, A3这样的顺序 for column in sheet.columns: for cell in column: print('(%s,%s) is %s.' % (cell.column, cell.row, cell.value)) # 上面的代码就可以获得所有单元格的数据。如果要获得某行的数据呢？给其一个索引就行了，因为sheet.rows是生成器类型，不能使用索引，转换成list之后再使用索引，list(sheet.rows)[2]这样就获取到第三行的tuple对象。 print('================') for cell in list(sheet.rows)[2]: print('(%s,%s) is %s.' % (cell.column, cell.row, cell.value)) # 如何获得任意区间的单元格？ # # 可以使用range函数，下面的写法，获得了以A1为左上角，B3为右下角矩形区域的所有单元格。注意range从1开始的，因为在openpyxl中为了和Excel中的表达方式一致，并不和编程语言的习惯以0表示第一个值。 for i in range(1, 4): for j in range(1, 3): print(sheet.cell(row=i, column=j)) # 还可以像使用切片那样使用。sheet['A1':'B3'] # 返回一个tuple，该元组内部还是元组，由每行的单元格构成一个元组。 for row_cell in sheet['A1':'B3']: for cell in row_cell: print(cell) for cell in sheet['A1':'B3']: print(cell) # 根据字母获得列号，根据列号返回字母 # 需要导入， 这两个函数存在于openpyxl.utils from openpyxl.utils import get_column_letter, column_index_from_string # 根据列的数字返回字母 print(get_column_letter(2)) # B # 根据字母返回列的数字 print(column_index_from_string('D')) # 4