运营的 Python 指南 - Python 操作 Excel

这是一份写给运营人员的 Python 指南。本文主要讲述如何使用 Python 操作 Excel。完成 Excel 的创建，查询和修改操作。
相关代码请参考 https://github.com/RustFisher/python-playground

本文链接：https://www.rustfisher.com/2019/11/05/Python/Python-op-excel_openpyxl_use/

开发工具，环境

• PyCharm - 方便好用的 IDE
• Python3

这里默认你的电脑上已经装有 python3.x，环境变量已经配置好。

引入 openpyxl

这里使用 openpyxl 库来操作 Excel。类似的库还有 xlrd。

penpyxl 的基础使用方法请参见 Python openpyxl 处理 Excel 使用指南。

pip install openpyxl

数据准备

假定数据来自百度指数，以常见的一些搜索指数为例。给后面的操作准备一些数据，把数据写入 Excel 中。

创建 Workbook 对象，获取到当前可用的表格（sheet）。
直接使用 append 方法把一行数据追加写入。最后调用 Workbook 的保存方法，存储数据。

def create_excel_demo(file_path):
    """
    创建Excel文件，并写入数据
    :param file_path: 目标文件路径
    :return none
    """
    wb = Workbook()
    ws = wb.active
    ws.title = '搜索指数概览'

    ws.append(['关键词', '整体日均值', '移动日均值', '整体同比', '整体环比', '移动同比', '移动环比'])
    ws.append(['excel', 27782, 18181, -0.11, -2, 0.21, 0.02])
    ws.append(['python', 24267, 8204, 0.27, 0.06, 0.56, 0.01])
    ws.append(['文案', 2411, 1690, 0.56, 0.33, 0.91, 0.46])
    ws.append(['okr', 1928, 880, 0.38, 0.15, 0.29, 0.09])
    ws.append(['kpi', 4212, 2784, 0.21, -0.19, 0.36, -0.22])
    wb.save(file_path)

创建出来的表格，示例数据如下

读取数据

访问整个表格的数据

访问表格中所有的数据，并打印出来。
首先我们要知道表格中有数据的单元格的范围，使用 sheet.max_row 与 sheet.max_column 获取表格的行列数量。

def read_xlsx_basic(file_path):
    """
    读取Excel的数据并打印出来
    """
    wb = load_workbook(file_path)
    st = wb.active
    end_row = st.max_row + 1
    end_column = st.max_column + 1
    print(st.title, '有', end_row, '行', end_column, '列')
    for row in range(1, end_row):
        for col in range(1, end_column):
            print('{:10}'.format(st.cell(row=row, column=col).value), end='')
        print()

值得注意的是，单元格下标是从 1 开始的。如果使用了不当的下标，报错信息

ValueError: Row or column values must be at least 1

参考： https://stackoverflow.com/questions/34492322/how-to-scan-all-sheet-cells

修改表格

有了 Excel 表格后，我们可以修改表格的一些格式和数据。

st 表示当前表。

调整列的宽度

用 column_dimensions 来获取列。例如 column_dimensions['A']获取到的是第 A 列。
ord 方法是将字符转换为 ascii 码。ord('A')得到 65。

这里调整的是 A 列到 G 列的宽度。

    for col in range(ord('A'), ord('G') + 1):
        st.column_dimensions[chr(col)].width = 15

设置单元格格式

格式，比如字体大小，对齐模式，粗体斜体等。

字体

首先我们要拿到单元格 cell，这里使用 st.cell(row=1, column=col)来获取某一个格子。
Cell 持有的 font 是不可修改的。不能使用如 cell.font.size = 13 这样的操作，会报异常。
copy 方法是复制一个对象。这里复制的是 font。

    cell = st.cell(row=1, column=col)
    font = copy(cell.font)
    font.size = 13
    font.bold = True
    cell.font = font

对齐

对齐模式。使用 alignment 属性。

cell.alignment = Alignment(horizontal="center", vertical="center")

数据显示

操作 Excel 时，我们可以设置单元格数据显示的方式，比如常规，数值，货币，百分比等等。
这里使用的是 number_format 属性。

如果数据（value）是 0.02，经过下面的设置后，Excel 中显示的是 2%。

cell.number_format = '0%'

修改数据

修改单元格的数值（value）。
修改数据首先要拿到那个单元格 cell，然后对其 value 赋值。

st.cell(row=2, column=7).value = 0.42  # 修改数值

修改了单元格的样式和数据后，可以打开表格看看效果。

参考：

• https://stackoverflow.com/questions/12387212/openpyxl-setting-number-format
• https://stackoverflow.com/questions/30050716/how-to-put-two-decimals-in-cell-with-type-of-percent