Python初学四记之操作Excel_openpyxl 重点标记-优快云博客

本文链接：https://blog.youkuaiyun.com/Link2Points/article/details/108504545

本文详细介绍如何使用Python的openpyxl库进行Excel文件的创建、查询、数据处理及样式设置。包括创建新工作簿、查询特定课程信息、创建查询结果工作表、标记选修人数等实用操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、openpyxl创建Excel

【一】、安装openpyxl

pip install openpyxl

【二】、创建Excel操作

import openpyxl
book = openpyxl.workbook()  #创建工作簿
sheet1 = book.active        #创建工作表
sheet1.title = "TEST"       #表名
sheet1.append([1,2,3])      #添加数据
book.save("demo1.xlsx")     #保存为demo1

2、openpyxl查询Excel记录

【一】、查询与课程名相关的信息

import openpyxl
book = openpyxl.load_workbook("课程.xlsx")
sheet = book["专业课"]
search = "Android程序设计与应用"

#打印表头
pos = sheet['A1']
while pos.value != None:
    print(pos.value, end = "|")
    pos = pos.offset(0, 1)

print()
pos = sheet['C2']
while pos.value != None:
    if search in pos.value:
        # print(pos.coordinate + " " + pos.value)
        # C2349 Android程序设计与应用

        cur = 'A' + str(pos.row)
        pos = sheet[cur]
        while pos.value != None:
            print(pos.value, end = "|")
            pos = pos.offset(0, 1)
        break
    pos = pos.offset(1, 0)

3、openpyxl新建查询结果工作表

【一】、新建查询结果工作表及对该工作表单元格调整

import openpyxl
from openpyxl.styles import PatternFill, Alignment

book = openpyxl.load_workbook("课程.xlsx")
sheet1 = book["专业课"]
sheet2 = book.create_sheet(index=1, title="查询结果")   # 新建工作表

# 输出结果
def result(search, cur):
    found = False
    pos1 = sheet1['C2']
    # 打印定位
    pos2 = sheet2['A' + str(cur)]
    while pos1.value != None:
        if search in pos1.value:
            found =True
            # 查询定位
            temp1 = 'A' + str(pos1.row)
            pos1 = sheet1[temp1]

            while pos1.value != None:
                pos2.value = pos1.value
                pos1 = pos1.offset(0, 1)
                pos2 = pos2.offset(0, 1)
            break
        pos1 = pos1.offset(1, 0)
    # 未查到
    if found == False:
        pos2.value = "未查到"
        temp = 'A' + str(cur) + ':N' + str(cur)
        sheet2.merge_cells(temp)     # 合并该行单元格
        sheet2['A' + str(cur)].alignment = Alignment(horizontal='center', vertical='center')      # 居中
# 表头信息
pos = sheet1['A1']
while pos.value != None:
    temp = str(pos.coordinate)
    sheet2[temp] = pos.value
    # 表头颜色
    sheet2[temp].fill = PatternFill("solid", fgColor="66ccfe")
    pos = pos.offset(0, 1)


cur = 2     # 打印游标

# Android程序设计与应用
# Python应用程序设计
# 算法设计与分析
while True:
    print("输入查询课程(0退出)：")
    search = input()
    if search == '0':
        break
    else:
        result(search, cur)
        cur += 1

# 调整单元格宽
sheet2.column_dimensions['B'].width = 17
sheet2.column_dimensions['C'].width = 24
sheet2.column_dimensions['E'].width = 30
# 设置工作表颜色
sheet2.sheet_properties.tabColor = '0094ff'
# 冻结单元格
sheet2.freeze_panes = 'A2'

book.save("课程.xlsx")

【二】、输入及结果

【三】、文档链接

Working with styles — openpyxl 3.0.9 documentation

4、openpyxl选修人数标记

【一】、选修人数不满20标红，超过20标绿

import openpyxl

book = openpyxl.load_workbook("课程.xlsx")
sheet = book["专业课"]

pos = 'I2'
i = 2
while sheet[pos].value != None:
    # 选修人数不满20标红，超过20标绿
    sheet[pos].number_format = "[<20][RED];[>=20][GREEN]"
    i += 1
    pos = 'I' + str(i)

book.save("课程.xlsx")