Python使用openpyxl读写excel文件

前言

根据官方文档，openpyxl 是一个第三方库, 它可以可以处理 xlsx/xlsm 格式的 Excel 文件(A Python library to read/write Excel 2010 xlsx/xlsm files)。
openpyxl 中主要的三个概念: Workbook(工作表)，Sheet(表页)和Cell(格)。
openpyxl 中主要的操作: 打开 Workbook，定位 Sheet，操作 Cell。
下面介绍 openpyxl 中读写的主要方法。

正文

安装方式

pip 安装: sudo pip install openpyxl

源码安装: python setup.py install (下载链接在最下面)

Workbook 操作

• 读 Workbook

from openpyxl import load_workbook# 加载存在的 excel 文件: 默认可读写wb = load_workbook("sample.xlsx")# 只读模式打开文件wb = load_workbook("sample.xlsx", read_only=True)

• 写 Workbook

from openpyxl import Workbook# 新建一个新的工作表（未保存）。wb = Workbook()# 只写模式wb = Workbook(write_only=True)# 保存文件，若加载路径与保存的路径一致将会被覆盖wb.save(r"F:\sample.xlsx")# 将文件作为模板保存 as_template 默认为 Falsewb.save("template.xltx", as_template=True)

Sheet 操作

• 读 sheet

# 获得所有 sheet 的名称()name_list = wb.get_sheet_names()# 根据 sheet 名字获得 sheetfor name in name_list:    my_sheet = wb.get_sheet_by_name(name)# 获得 sheet 名    print(my_sheet.title)# 获得当前正在显示的 sheet, 或 wb.get_active_sheet()my_sheet = wb.active# 通过索引加载 sheet，index 从0开始my_sheet = wb.worksheets[index]# 最大行my_sheet.max_row# 最大列my_sheet.max_column# 设置标签栏的字体颜色(标签栏背景色默认为白色）my_sheet.sheet_properties.tabColor = "FF0000"

• 写 sheet

# 获得所有 sheet 的名称wb.get_sheet_names()# 改工作表的名称my_sheet.title = "Sheet1"# 新建一个工作表，0是第一个位置wb.create_sheet("Data", index=1)#默认插在工作簿末尾my_sheet = wb.create_sheet() # 删除某个工作表wb.remove(my_sheet)# 删除某个工作表del wb[my_sheet]

Cell 操作

• 读 Cell

# 获取某个单元格的值，观察 excel 发现也是先字母再数字的顺序，即先列再行c3 = my_sheet["C3"]# 列，即 Cc3.column# 行，即 3c3.row# 坐标，即 C3c3.coordinate# 对应的值c3.value# 除了用下标的方式获得，还可以用cell 函数, 换成数字，这个表示 C3c3_cell = my_sheet.cell(row=3, column=3)print(c3_cell.value)# 获得最大列和最大行print(my_sheet.max_row)print(my_sheet.max_column)# 按行读取: 按 A1、B1、C1 顺序返回for row in my_sheet.rows:    for cell in row:        print(cell.value)# 按列读取: 按 A1、A2、A3 顺序返回for column in my_sheet.columns:    for cell in column:        print(cell.value)# 获取某一行的数据，例:获取第三行 tuple 对象for cell in list(my_sheet.rows)[2]:    print(cell.value)# 获取矩形区间数据for i in range(1, 4):    for j in range(1, 3):        print(my_sheet.cell(row=i, column=j))# iter_rows() 方法获得多个单元格for row in ws.iter_rows("A1:C2"):    for cell in row:        print cell# 像切片一样使用        for row_cell in my_sheet["A1":"B3"]:    for cell in row_cell:        print(cell)

• 写 Cell

# 直接给单元格赋值就行my_sheet["A1"] = "test"# B9 处写入平均值my_sheet["B9"] = "=AVERAGE(B2:B8)"# 添加一行row = [1 ,2, 3, 4, 5]my_sheet.append(row)# 添加多行rows = [    ["ID", "data1", "data2"],    [2, 40, 20],    [3, 40, 25],    [4, 40, 30],    [5, 40, 35],    [6, 45, 40],    [7, 40, 45],]my_sheet.append(rows)# 添加多列columns = list(zip(*rows))my_sheet.append(columns)

根据字母获得列号，根据列号返回字母

from openpyxl.utils import get_column_letter, column_index_from_string# 根据列的数字返回字母print(get_column_letter(3))  # C# 根据字母返回列的数字print(column_index_from_string("C"))  # 3

设置单元格风格 Style

from openpyxl.styles import Font, colors, Alignment# 设置字体: 等线 24 号加粗斜体，字体颜色红色bold_itatic_24_font = Font(name="等线", size=24, italic=True, color=colors.RED, bold=True)my_sheet["A1"].font = bold_itatic_24_font# 设置填充色: my_sheet["A2"].fill = PatternFill(fill_type=fills.FILL_SOLID, fgColor="00FF0000", bgColor="00FF0000")# 对齐方式: B1 中的数据垂直居中和水平居中my_sheet["B1"].alignment = Alignment(horizontal="center", vertical="center")# 设置行高和列宽my_sheet.row_dimensions[2].height = 40my_sheet.column_dimensions["C"].width = 30# 合并和拆分单元格# 合并单元格， 往左上角写入数据即可# 合并后只可以往左上角写入数据，也就是区间中:左边的坐标。my_sheet.merge_cells("B1:G1") # 合并一行中的几个单元格my_sheet.merge_cells("A1:C3") # 合并一个矩形区域中的单元格my_sheet.unmerge_cells("A1:C3") #拆分后，值回到A1位置。

其他说明:

• openpyxl 中 row 和 column 为了和 Excel 中的表达方式一致，并不和编程语言的习惯以0表示第一个值, 而是1开始。
• wb.worksheets[index] index 从0开始
• 假设 sheet[“B9”] = “=AVERAGE(B2:B8)” ，当读数据时，data_only=True, 读公式时得到的是 B9 返回的公式，如果不加这个参数，返回的将是公式本身 “=AVERAGE(B2:B8)”
• 如果文字编码是 “gb2312” 读取后就会显示乱码，请先转成 Unicode
• 当一个工作表被创建时，其中不包含单元格。只有当单元格被获取是才被创建。这种方式我们不会创建我们从不会使用的单元格，从而减少了内存消耗。
• 保存时，后缀要保持一致
• xlsm格式的文件保存，需要传参 keep_vba=True
• wb = load_workbook(“sample.xltm”, keep_vba=True) 时，保存为 template document，需要传参 as_template=True， 保存为document，需要传参 as_template=False

链接:

openpyxl 官方文档: http://openpyxl.readthedocs.io/en/default/
常用实例: http://openpyxl.readthedocs.io/en/default/usage.html
BitBucket 地址: https://bitbucket.org/openpyxl/openpyxl
openpyxl 源码下载: https://pypi.python.org/pypi/openpyxl
一个很好的教程 https://automatetheboringstuff.com/chapter12/

---

如有错误，欢迎指出。

email: dxmdxm1992#gmail.com

blog: http://blog.csdn.net/david_dai_1108