Excel办公自动化
介绍
Python操作Excel的模块有很多，但是好几个都好久都不更新了，不能很好的支持最新版的Excel。
目前应用最广的是openpyxl。
官方文档（英文）：https://openpyxl.readthedocs.io/en/stable/
功能与优化：模块的开发者认为本模块的功能性要大于性能优化，也就说，为了功能的开发可以牺牲性能的下降，毕竟现在的计算机的计算能力已经都足够高，使用本模块操作一个50MB的Excel文件大约需要2.5GB的内存。
安装
windows、Linux、MacOS在命令行下执行：
pip install openpyxl
pip install pillow # 当需要在Excel中插入图片的时候需要安装注意：如果需要在Excel中插入图片，那么还需要安装pillow。学习准备
简而言之，一个Excel工作簿workbook由一个或者多个工作表sheet组成，一个sheet可以看作是多个行row组成，也可以看作是多个列column组成，而每一行每一列都由多个单元格cell组成！
创建工作簿（表）
# -*- coding:utf-8 -*-
# author: Man Yacan
# Email: myxc@live.cn
# Website: https://www.manyacan.com
# datetime: 2020/9/2 20:39
# software: PyCharm
from openpyxl import Workbook # 导入模块
wb = Workbook() # 创建一个工作簿
ws_0 = wb.active # 创建一个工作表（当工作簿内没有工作表时）
ws_1 = wb.create_sheet("sheet_01") # 增加一个工作表（在最后插入）
ws_2 = wb.create_sheet("sheet_02", 0) # 增加一个工作表（在第一个位置插入工作表）
ws_3 = wb.create_sheet("sheet_03", -1) # 增加一个工作表（在到数第二的位置插入工作表）
wb.save('balances.xlsx') # 保存文件
wb.save(filename = 'balances.xlsx') # 保存文件的另一种方式注意：wb.save('balances.xlsx')操作会覆盖同文件夹内的文件！（官方文档是这么说的，但是我实际操作发现在文件夹内存在同名文件时，不能够保存，控制台报错）工作表属性的修改
ws_0.title = "New Title" # 修改表名
ws_0.sheet_properties.tabColor = "3498DB" # 修改表名背景色
ws3 = wb["New Title"] # 当工作表有名字之后可以像列表一样调用工作表属性的输出
print(wb.sheetnames) # 以list方式输出工作簿内所有工作表的名
# ['sheet_02', 'New Title', 'sheet_03', 'sheet_01']
for sheet in wb: # 循环工作表名的另一种方式
print(sheet.title)工作表的复制
source = wb.active
target = wb.copy_worksheet(source)注意：
- 只有当工作簿内只有一个工作表时可以进行该操作；
- 工作簿不能执行复制操作；
- 当工作簿的属性为只读或者只写的时候不能复制；
- 工作表内的图表、图片不能得到复制。
单元格操作
单个单元格
c = ws['A4'] # 读取A4单元格内容（得到的是一个对象<Cell 'New Title'.A4>）
print(c.value) # 输出单元格的值
ws['A4'] = 4 # 单元格的赋值
print(ws['A4'].value) # 输出单元格的值
d = ws.cell(row=4, column=2, value=10) # 将10赋值给2行4列的单元格，同时将该单元格以对象的形式返回给d
print(d.value) # 输出单元格的值
e = ws.cell(row=5, column=2) # 将单元格5行2列以对象的形式返回给e
print(e.value) # 输出单元格的值多个单元格
cell_range = ws['A1':'C2'] # 得到A1到C2单元格
colC = ws['C'] # 得到3行单元格
col_range = ws['C:D'] # 得到3~4行单元格
row10 = ws[10] # 得到10列
row_range = ws[5:10] # 得到5~10列遍历一块地方的单元格：
>>> 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>注意：在只读模式下，该方法不能使用。
工作表的遍历：
>>> 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.B7>,
<Cell Sheet.B8>,
<Cell Sheet.B9>),
(<Cell Sheet.C1>,
<Cell Sheet.C2>,
<Cell Sheet.C3>,
<Cell Sheet.C4>,
<Cell Sheet.C5>,
<Cell Sheet.C6>,
<Cell Sheet.C7>,
<Cell Sheet.C8>,
<Cell Sheet.C9>))遍历输出单元格的值：
for row in ws.values:
for value in row:
print(value)>>> 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)读取文件
from openpyxl import load_workbook # 加载模块
wb = load_workbook(filename = 'empty_book.xlsx') # 打开文件
sheet_ranges = wb['range names'] # 选择工作表
print(sheet_ranges['D18'].value) # 读取单元格的值合并&拆分单元格
from openpyxl.workbook import Workbook
wb = Workbook()
ws = 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=4, end_column=4)
ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)插入多行&列
ws4 = wb.create_sheet(title="sheet_4") # 创建第二个工作表，名为：Pi
ws4["B2"] = 'sheet_04'
ws4.insert_cols(idx=2) # 在第二列处插入空列
ws4.insert_cols(idx=2, amount=5) # 在第二列前插入5列
ws4.insert_rows(idx=2) # 在第二行处插入空列
ws4.insert_rows(idx=2, amount=5) # 在第二行前插入5行删除多行&列
ws4.delete_cols(idx=7) # 删除第七列
ws4.delete_cols(idx=2, amount=5) # 删除第六列前五列（包括第六列）
ws4.delete_rows(idx=7) # 删除第七行
ws4.delete_rows(idx=2, amount=5) # 删除第六行前五行（包括第六行）移动单元格
ws4.move_range('C1:D2', rows=2, cols=-2) # 将C1:D2处单元格向下移动2行，向左移动2列字体样式
字体
from openpyxl import styles
font = styles.Font(name='微软雅黑', size=12, bold=True, italic=True, color="3498DB") # 创建一个字体对象
ws5["A1"].font = fontFont类可以接受的属性：
def __init__(self, name=None, sz=None, b=None, i=None, charset=None,
u=None, strike=None, color=None, scheme=None, family=None, size=None,
bold=None, italic=None, strikethrough=None, underline=None,
vertAlign=None, outline=None, shadow=None, condense=None,
extend=None):对齐方式
from openpyxl import styles
alignment = styles.Alignment(horizontal='center', vertical='center', text_rotation=45, wrap_text=True) # 设置单元格对齐方式：左右居中，上下居中，文字旋转45度，自动换行
ws5["A1"].alignment = alignmentAlignment类可以接受的属性：
def __init__(self, horizontal=None, vertical=None,
textRotation=0, wrapText=None, shrinkToFit=None, indent=0, relativeIndent=0,
justifyLastLine=None, readingOrder=0, text_rotation=None,
wrap_text=None, shrink_to_fit=None, mergeCell=None):- 水平对齐:
distributed, justify, center, left, fill, centerContinuous, right, general - 垂直对齐:
bottom, distributed, justify, center, top
边框样式
from openpyxl import styles
border = styles.Border(left=side, right=side, top=side, bottom=side)
ws5["A1"].border = borderBorder类可以接受的属性：
def __init__(self, left=Side(), right=Side(), top=Side(),
bottom=Side(), diagonal=Side(), diagonal_direction=None,
vertical=None, horizontal=None, diagonalUp=False, diagonalDown=False,
outline=True, start=None, end=None):Side类可以接受的属性：
def __init__(self, style=None, color=None, border_style=None):单元格填充颜色
from openpyxl import styles
fill = styles.PatternFill(fill_type='solid', fgColor='58d68d') # 单色填充
ws5["A1"].fill = fill
fill_2 = styles.GradientFill(stop=('FFFFFF', '99ccff', '000000')) # 渐变填充
ws5["A2"].fill = fill_2单元格行宽&高
sheet.row_dimensions[1].height = 50
sheet.column_dimensions['C'].width = 20 超链接的插入
ws6 = wb.create_sheet(title="sheet_6") # 创建第二个工作表，名为：Pi
# 第一种方式
ws6["A1"].hyperlink = "https://baidu.com"
ws6["A1"].value = "百度"
ws6["A2"].value = '=HYPERLINK("{}", "{}")'.format('https://baidu.com', '百度') # 第二种方式小练习
# -*- coding:utf-8 -*-
# author: Man Yacan
# Email: myxc@live.cn
# Website: https://www.manyacan.com
# datetime: 2020/9/2 20:39
# software: PyCharm
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" # 设置第一个工作表的名字为：range names
for row in range(1, 40): # 在1~39行内每行填充1~599
ws1.append(range(600))
ws2 = wb.create_sheet(title="Pi") # 创建第二个工作表，名为：Pi
ws2['F5'] = 3.14 # 第二个工作表的F5单元格赋值为3.14
ws2.cell(column=6, row=6, value=10) # 第二个工作表的F6单元格赋值为10
ws3 = wb.create_sheet(title="Data") # 创建第三个工作表，名为：Data
for row in range(10, 20): # 在10~19行，27~53列内单元格内填充列号
for col in range(27, 54):
_ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
print(ws3['AA10'].value) # 打印第10行第27列单元格的值
wb.save(filename=dest_filename) # 保存文件