Excel办公自动化

曼亚灿

发布于 2023-05-17 21:27:11

40200

代码可运行

文章被收录于专栏：亚灿网志亚灿网志

运行总次数：0

代码可运行

做工具人是不可能的，这辈子都不会去做工具人

请注意，本文编写于 986 天前，最后修改于 986 天前，其中某些信息可能已经过时。

介绍

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。

学习准备

转载自：微信公众号-Python猫

简而言之，一个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 = font

Font类可以接受的属性：

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 = alignment

Alignment类可以接受的属性：

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 = border

Border类可以接受的属性：

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)  # 保存文件

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2020-09-03，如有侵权请联系 cloudcommunity@tencent.com 删除

cell