用 Python 处理Excel表格的几个常规库：
- xlrd 用来读
- xlwt 用来写
- xlutils 用于做复制、筛选等针对文档文件的操作
这些库都不如OpenPyXL 强大， OpenPyXL 即可以读也可以写 Excel 2010+ 的 xlsx xlsm xltx xltm 文件。不过， OpenPyXL 库也是比较吃内存的，大约是原始文件的5倍左右。例如，一个50M大小的Excel文件，需要2.5G大小的内存运行。关于以上几个库的性能比对，请移步OpenPyXL性能测试。
Python学习群：556370268，有大牛答疑，有资源共享！有想学习python编程的，或是转行，或是大学生，还有工作中想提升自己能力的，正在学习的小伙伴欢迎加入学习。
安装
在终端中输入命令，如下：
pip install openpyxl
openpyxl 在保存Excel表格时候会丢失原文件的图片和图表。如果要操作图片，则需要 pillow 库，安装如下：
pip install pillow
由于最近军运会要在天朝举行，因此必须翻-墙才能安装。
使用方法
新建Excel表格
新建Excel表格，默认有一个名为 Sheet 的表格，如下：
from openpyxl import Workbook
wb = Workbook() #创建文件对象
ws = wb.active #获取默认sheet
wb.save("sample.xlsx")
打开已有的Excel表格
对已有的Excel表格进行操作，如下：
from openpyxl import Workbook, load_workbook
wb = load_workbook('sample.xlsx')
wb.save("sample.xlsx")
新建/获取Sheet表格
使用 Workbook.create_sheet() 方法新建Sheet表格。第一个参数是sheet名称，若不填，则默认以 Sheet1 Sheet2 Sheet3 ...方式命名；第二个参数是插入Sheet表格的位置，以 0 为第一个位置，若不填，则置于最后。如下：
ws1 = wb.create_sheet("Mysheet") #默认在最后插入
ws2 = wb.create_sheet("Mysheet", 0) #在第一个位置插入
也可以后期随时修改sheet的名字，如下：
ws.title = "New Title"
修改sheet标签颜色，如下：
ws.sheet_properties.tabColor = "1072BA"
若知道sheet的名字，可以用如下方式获取sheet ：
ws = wb.get_sheet_by_name("New Title")
ws = wb["New Title"]
也可获取全部sheet的名字，遍历sheet名字，如下：
sheets = wb.sheetnames
for sheet in sheets:
print(sheets)
for sheet in wb:
print sheet.title
['Sheet1', 'New Title', 'Sheet2']
复制Sheet表格
仅能复制 单元格的值 样式 超链接 注释块 等，而 图片 和 表格 等是无法复制的，如下：
source = wb.active
target = wb.copy_worksheet(source)
操作单元格
由 worksheet 获取单元格，或直接给单元格赋值，如下：
cell = ws['A4'] #获取第4行第A列的单元格
ws['A4'] = 4 #给第4行第A列的单元格赋值为4
ws.cell(row=4, column=2, value=10) #给第4行第2列的单元格赋值为10
ws.cell(4, 2, 10) #同上
获取区域内的单元格，如下：
cell_range = ws['A1':'C2'] #获取A1-C2内的区域
colC = ws['C'] #获取第C列
col_range = ws['C:D'] #获取第C-D列
row10 = ws[10] #获取第10列
row_range = ws[5:10] #获取第5-10列
一行行的获取数据，如下：
>>> 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.iter_cols() 方法不能在只读模式下使用。
获取所有的列或行，如下：
rows = ws.rows
columns = ws.columns
因为性能的原因， Worksheet.columns 方法不能在只读模式下使用。
如果只想从worksheet中获取值，可以使用 Worksheet.values 属性，如下：
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)
如果得到单元格，可以给赋值，如下：
cell.value = 'hello, world'
保存文件
使用 Workbook.save() 方法保存workbook，这个方法会不加提示的覆盖原文件，如下：
wb = Workbook()
wb.save('balances.xlsx')
获取单元格类型
from openpyxl import Workbook, load_workbook
import datetime
wb = load_workbook('sample.xlsx')
ws=wb.active
wb.guess_types = True #开启获取单元格类型
ws["A1"]=datetime.datetime(2010, 7, 21)
print ws["A1"].number_format
ws["A2"]="12%"
print ws["A2"].number_format
ws["A3"]= 1.1
print ws["A4"].number_format
ws["A4"]= "中国"
print ws["A5"].number_format
wb.save("sample.xlsx")
# 执行结果：
# yyyy-mm-dd h:mm:ss
# 0%
# General
# General
# 如果是常规，显示general,如果是数字，显示'0.00_ '，如果是百分数显示0%
# 数字需要在Excel中设置数字类型，直接写入的数字是常规类型
使用公式
from openpyxl import Workbook, load_workbook
wb = load_workbook('sample.xlsx')
ws1=wb.active
ws1["A1"]=1
ws1["A2"]=2
ws1["A3"]=3
ws1["A4"] = "=SUM(1, 1)"
ws1["A5"] = "=SUM(A1:A3)"
print ws1["A4"].value #打印的是公式内容，不是公式计算后的值,程序无法取到计算后的值
print ws1["A5"].value #打印的是公式内容，不是公式计算后的值,程序无法取到计算后的值
wb.save("sample.xlsx")
合并单元格
from openpyxl import Workbook, load_workbook
wb = load_workbook('sample.xlsx')
ws1=wb.active
ws.merge_cells('A2:D2')
ws.unmerge_cells('A2:D2') #合并后的单元格，脚本单独执行拆分操作会报错，需要重新执行合并操作再拆分
# or equivalently
ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
ws.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
wb.save("sample.xlsx")
插入一个图片
需要 pillow 库，安装如下：
pip install pillow
复制代码
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
wb = load_workbook('sample.xlsx')
ws1=wb.active
img = Image('1.png')
ws1.add_image(img, 'A1')
wb.save("sample.xlsx")
隐藏单元格
from openpyxl import load_workbook
wb = load_workbook('sample.xlsx')
ws = wb.active
ws.column_dimensions.group('A', 'D', hidden=True) # 隐藏A到D列
ws.row_dimensions.group(1, 10, hidden=True) # 隐藏1到10行
wb.save("sample.xlsx")
优化模式
在处理非常大的 XLSX 文件时， openpyxl 的常规模式无法处理这种负载。幸运的是，有两种模式可以在(几乎)恒定内存消耗的情况下读写无限量的数据。
Python学习群：556370268，有大牛答疑，有资源共享！有想学习python编程的，或是转行，或是大学生，还有工作中想提升自己能力的，正在学习的小伙伴欢迎加入学习。
只读模式
from openpyxl import load_workbook
wb = load_workbook(filename='large_file.xlsx', read_only=True)
ws = wb['big_data']
for row in ws.rows:
for cell in row:
print(cell.value)
只写模式
from openpyxl import Workbook
wb = Workbook(write_only=True)
ws = wb.create_sheet()
# now we'll fill it with 100 rows x 200 columns
for irow in range(100):
... ws.append(['%d' % i for i in range(200)])
# save the file
wb.save('new_big_file.xlsx') # doctest: +SKIP
- 与普通工作簿不同，新创建的只写工作簿不包含任何工作表；必须使用 create_sheet() 方法专门创建工作表。
- 在只写的工作簿中，只能使用 append() 添加行。使用 cell() 或 iter_rows() 在任意位置写(或读)单元格是不可能的。
- 它能够导出无限数量的数据(甚至比Excel实际能够处理的更多)，同时将内存使用量保持在10Mb以下。
插入/删除行/列，移动区域单元格
插入行/列
在第7行之上插入一行，如下：
ws.insert_rows(7)
在第7列的左边插入一列，如下：
ws.insert_cols(7)
删除行/列
从第6列开始，删除3列，即删除6、7、8列，如下：
ws.delete_cols(6, 3)
移动区域单元格
将 D4:F10 区域向上移动一行向右移动2列，如下：
ws.move_range("D4:F10", rows=-1, cols=2)
如果区域内包含 公式 ，则如下方法可以连同公式一起挪动：
ws.move_range("G4:H10", rows=1, cols=1, translate=True)
使用 Pandas 和 NumPy
详情请移步 Working with Pandas and NumPy
图表
图表由至少一个系列的一个或多个单元格区域数据点组成。更多内容请移步图表介绍
注释
openpyxl 可读/写注释，但格式信息会被丢失。在 只读模式 下不支持操作注释。注释必须包括 内容 和 作者 。
读注释，如下：
comment = ws["A1"].comment
comment.text # 注释内容
comment.author # 注释作者
写注释，如下：
comment = Comment("Text", "Author")
comment.width = 300 # 设置宽度
comment.height = 50 # 设置高度
ws["A1"].comment = comment
ws["B2"].comment = comment
表格样式
字体样式
字体名称、字体大小、字体颜色、加粗、斜体、纵向对齐方式（有三种： baseline ， superscript ， subscript ）、下划线、删除线，如下：
from openpyxl.styles import Font
font = Font(name='Calibri',
size=11,
color='FF000000',
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False)
ws['A1'].font = font
字体颜色可以用 RGB 或 aRGB ，如下：
font = Font(color="FFBB00")
font = Font(color="FFFFBB00")
继承并重写样式，如下：
ft1 = Font(name='Arial', size=14)
ft2 = copy(ft1)
ft2.name = "Tahoma"
填充样式
详情请移步填充样式
from openpyxl.styles import PatternFill
fill = PatternFill(fill_type=None,
start_color='FFFFFFFF',
end_color='FF000000')
边框样式
详情请移步边框样式
from openpyxl.styles import Border, Side
border = Border(left=Side(border_style=None, color='FF000000'),
right=Side(border_style=None, color='FF000000'),
top=Side(border_style=None, color='FF000000'),
bottom=Side(border_style=None, color='FF000000'),
diagonal=Side(border_style=None, color='FF000000'),
diagonal_direction=0,
outline=Side(border_style=None, color='FF000000'),
vertical=Side(border_style=None, color='FF000000'),
horizontal=Side(border_style=None, color='FF000000')
)
对齐样式
horizontal 的值有： distributed , justify , center , left , fill , centerContinuous , right , general
vertical 的值有： bottom , distributed , justify , center , top
from openpyxl.styles import Alignment
alignment=Alignment(horizontal='general',
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
保护样式
锁定、隐藏
from openpyxl.styles import Protection
protection = Protection(locked=True, hidden=False)
整行或整列应用样式
col = ws.column_dimensions['A']
col.font = Font(bold=True)
row = ws.row_dimensions[1]
row.font = Font(underline="single")
更改合并的单元格样式
合并的单元格可以想想成为左上角的那个单元格来操作。
筛选和排序
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
data = [
["Fruit", "Quantity"],
["Kiwi", 3],
["Grape", 15],
["Apple", 3],
["Peach", 3],
["Pomegranate", 3],
["Pear", 3],
["Tangerine", 3],
["Blueberry", 3],
["Mango", 3],
["Watermelon", 3],
["Blackberry", 3],
["Orange", 3],
["Raspberry", 3],
["Banana", 3]
]
for r in data:
ws.append(r)
ws.auto_filter.ref = "A1:B15"
ws.auto_filter.add_filter_column(0, ["Kiwi", "Apple", "Mango"])
ws.auto_filter.add_sort_condition("B2:B15")
wb.save("filtered.xlsx")
生成的Excel表格，有筛选排序的操作，但是没有实际表现出效果，如下图：
image
需要手动点击 重写应用 才能显示出效果，如下图：
image
image
密码保护
该功能仅能提供一个很基础的密码保护，没有进行加密处理，网上普通的破解软件都可以破解密码。不过，日常使用还是可以的。
该功能仅可用于新建excel表格，不能用于已存在的excel表格。
workbook工作薄保护
防止查看隐藏sheet，避免增加、移动、删除、隐藏或重命名sheet等操作，可以保护workbook的结构，如下：
wb.security.workbookPassword = '...'
wb.security.lockStructure = True
网友评论