python操作Excel常用代码

使用python做一些excel的办公自动化是很常见的应用，对常用的一些代码做一些总结，方便自己使用。

本文使用的库是Openpyxl，官方文档：https://openpyxl.readthedocs.io/en/stable/

读取

基本读取：

# 读取到的是工作簿对象
wb = openpyxl.load_workbook("文件路径")
# 读取指定worksheet
worksheet = wb["sheet名称"]  # 用sheet名进行读取，适用于sheet比较多的情况
worksheet = wb.worksheets[0]  # 也可以用索引来读取，如果Excel只有一个sheet，推荐这种方式

一些其他常用参数：

• 读取带vba的xlsm格式文件

# 读取xlsm时，包含其中的vba代码；否则保存后，其中的vba不可用
wb = openpyxl.load_workbook("文件路径/a.xlsm,", keep_vba=True)

• 读取带有公式的excel文件

# 读取公式的计算结果，默认值是False，也就是读取公式
wb = openpyxl.load_workbook("文件路径/a.xlsx,", data_only=True)

• 读取Excel中的链接

# 是否保留Excel文件中的链接，默认是True
wb = openpyxl.load_workbook("文件路径/a.xlsx,", keep_links=True)

创建

创建一个空的xlsx文件

from openpyxl import Workbook
wb = Workbook()
# 获取默认的sheet
ws = wb.active
# 将默认sheet重命名
ws.title = "重命名"
# 创建一个自己命名的sheet，插在最前面
ws2 = wb.create_sheet("sheet名", 0)

复制已有sheet

source = wb.active
target = wb.copy_worksheet(source)

读取单元格

获取指定单元格

# 读取A1的值
cell = ws["A1"].value
# 给A2赋值
ws["A2"] = 2
# 用单元格索引的方式，同样可以获取A1的值
ws.cell(1, 1).value
# 输入公式
ws["B2"] = "=COUNTA(Sheet1!A2:A20)"

注意，用索引的方式获取单元格时，初始都是从1开始的。这和编程语言中习惯的从0开始不同，但是更直观。

单元格遍历

• 先获取row，然后遍历row中的每个cell：

# 读取2行，3列数据
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
    for cell in row:
        print(cell.value)

• 根据行列的索引遍历

# 行的索引，从2到最后一行，因为一般不读取标题行数据
for row in range(2, sheet.max_row+1):
    for col in range(1, sheet.max_column+1):
        value = sheet.cell(row, col).value

• 根据所见即所得的方式遍历

for row in range(2, sheet.max_row+1):
    for col in "ABCDEFG":
        value = sheet[f"{col}{row}"].value

设置单元格格式

在实际应用中，建议是先设置好模板excel的样子，然后把数据填进去。因为用代码设置格式，非常繁琐，下面只举一个简单的例子。具体可以参考：https://openpyxl.readthedocs.io/en/stable/styles.html

from openpyxl.styles import PatternFill
from openpyxl.styles import Font

# 首先定义好格式
red_cell = PatternFill("solid", fgColor="FF3030")
green_cell = PatternFill("solid", fgColor="98FB98")
yellow_cell = PatternFill("solid", fgColor="EEEE00")
font_str = Font(name="微软雅黑", size=10)

# 将格式赋值给单元格
sheet["A1"].font = font_str
sheet["A1"].fill = green_cell

保存

保存没啥好说的，不管是读取的还是自己创建的wb对象，直接save就完事了

wb.save("存储路径/文件名.xlsx")

举例说明

以下是一个例子

体温数据表格的操作

读取一个体温表（A列是姓名，自己编一些就行了），然后我们生成随机温度，范围在36到42，写入到B列：

import openpyxl
import random
from openpyxl.workbook.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.styles import Font

wb = openpyxl.load_workbook("resources/体温测量结果.xlsx")  # type:Workbook
# sheet = wb.worksheets[0]  # type:Worksheet
sheet = wb["Sheet1"]  # type:Worksheet

font_int = Font(name="Calibri", size=10, color="FF0000")

for row_num in range(2, sheet.max_row+1):
    sheet[f"B{row_num}"] = float(f"{random.uniform(36, 42):.1f}")
    sheet[f"B{row_num}"].font = font_int
    # 生成36到42之间的随机数，保留一位小数

wb.save("resources/体温测量结果.xlsx")

💡 注意，读取的时候，可以加上`data_only=True`这样读取到的是数值，不是公式。

对该表进行操作，标记样式

import openpyxl
from openpyxl.workbook.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.styles import PatternFill
from openpyxl.styles import Font

wb = openpyxl.load_workbook("resources/体温测量结果.xlsx")  # type:Workbook
sheet = wb["Sheet1"]  # type:Worksheet
sheet_len = sheet.max_row

sheet["C1"] = "结果"
red_cell = PatternFill("solid", fgColor="FF3030")
green_cell = PatternFill("solid", fgColor="98FB98")
yellow_cell = PatternFill("solid", fgColor="EEEE00")
font_str = Font(name="微软雅黑", size=10)

for row_num in range(2, sheet_len + 1):
    if sheet[f"B{row_num}"].value <= 37.5:
        sheet[f"C{row_num}"] = "正常"
        sheet[f"C{row_num}"].font = font_str
        sheet[f"C{row_num}"].fill = green_cell
    elif sheet[f"B{row_num}"].value <= 39:
        sheet[f"C{row_num}"] = "低烧"
        sheet[f"C{row_num}"].fill = yellow_cell
    else:
        sheet[f"C{row_num}"] = "病危"
        sheet[f"C{row_num}"].fill = red_cell

wb.save("resources/体温测量结果.xlsx")

用公式做汇总

import openpyxl
from openpyxl.workbook.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet

wb = openpyxl.load_workbook("resources/体温测量结果.xlsx")  # type:Workbook
sheet = wb.create_sheet("汇总")  # type:Worksheet

sheet["A1"] = "汇总"
sheet["A2"] = "人数"
sheet["B2"] = "=COUNTA(Sheet1!A2:A20)"
sheet["A3"] = "病危"
sheet["B3"] = '=COUNTIF(Sheet1!C2:C28,A3)'
sheet["A4"] = "低烧"
sheet["B4"] = '=COUNTIF(Sheet1!C2:C20,A4)'
sheet["A5"] = "正常"
sheet["B5"] = '=COUNTIF(Sheet1!C2:C20,"正常")'

wb.save("resources/体温测量结果.xlsx")