Python酷库之旅-第三方库openpyxl(16)-CSDN博客

# ['DEBUG', 'DEFUSEDXML', 'LXML', 'NUMPY', 'Workbook', '__author__', '__author_email__', '__builtins__', '__cached__', 
# '__doc__', '__file__', '__license__', '__loader__', '__maintainer_email__', '__name__', '__package__', '__path__',
# '__spec__', '__url__', '__version__', '_constants', 'cell', 'chart', 'chartsheet', 'comments', 'compat', 'constants',
# 'descriptors', 'drawing', 'formatting', 'formula', 'load_workbook', 'open', 'packaging', 'pivot', 'reader', 'styles',
# 'utils', 'workbook', 'worksheet', 'writer', 'xml']

2、获取openpyxl库的帮助信息

用help()函数获取openpyxl库的帮助信息

Help on package openpyxl:

NAME
    openpyxl - # Copyright (c) 2010-2024 openpyxl

PACKAGE CONTENTS
    _constants
    cell (package)
    chart (package)
    chartsheet (package)
    comments (package)
    compat (package)
    descriptors (package)
    drawing (package)
    formatting (package)
    formula (package)
    packaging (package)
    pivot (package)
    reader (package)
    styles (package)
    utils (package)
    workbook (package)
    worksheet (package)
    writer (package)
    xml (package)

SUBMODULES
    constants

DATA
    DEBUG = False
    DEFUSEDXML = False
    LXML = True
    NUMPY = True
    __author_email__ = 'charlie.clark@clark-consulting.eu'
    __license__ = 'MIT'
    __maintainer_email__ = 'openpyxl-users@googlegroups.com'
    __url__ = 'https://openpyxl.readthedocs.io'

VERSION
    3.1.3

AUTHOR
    See AUTHORS

FILE
    e:\python_workspace\pythonproject\lib\site-packages\openpyxl\__init__.py

3、实战案例

3-161、引用隐藏的行或列

# 3-161、引用隐藏的行或列
import openpyxl
# 打开已有的Excel文件
workbook = openpyxl.load_workbook('example.xlsx')
# 选择一个工作表
sheet = workbook.active
# 引用隐藏的行
hidden_rows = []
for row in sheet.row_dimensions:
    if sheet.row_dimensions[row].hidden:
        hidden_rows.append(row)
# 引用隐藏的列
hidden_columns = []
for col in sheet.column_dimensions:
    if sheet.column_dimensions[col].hidden:
        hidden_columns.append(col)
print("Hidden Rows:", hidden_rows)
print("Hidden Columns:", hidden_columns)
# 操作隐藏的行或列，例如取消隐藏
for row in hidden_rows:
    sheet.row_dimensions[row].hidden = False
for col in hidden_columns:
    sheet.column_dimensions[col].hidden = False
# 保存修改后的Excel文件
workbook.save('modified_example.xlsx')

3-162、引用单元格区域中未隐藏的单元格

# 3-162、引用单元格区域中未隐藏的单元格
from openpyxl import Workbook
# 创建一个新的工作簿和工作表
wb = Workbook()
ws = wb.active
# 填充一些数据
for row in range(1, 6):
    for col in range(1, 6):
        ws.cell(row=row, column=col, value=f"R{row}C{col}")
# 隐藏第二列和第四行
ws.column_dimensions['B'].hidden = True
ws.row_dimensions[4].hidden = True
# 遍历单元格区域，并只处理未隐藏的单元格
for row in range(1, 6):
    for col in range(1, 6):
        cell = ws.cell(row=row, column=col)
        # 检查行和列是否隐藏
        if not ws.row_dimensions[row].hidden and not ws.column_dimensions[cell.column_letter].hidden:
            print(cell.value)
# 保存工作簿
wb.save("example_with_hidden.xlsx")

3-163、引用锁定的单元格

# 3-163、引用锁定的单元格
from openpyxl import Workbook
from openpyxl.styles import Protection
# 创建一个新的工作簿和工作表
wb = Workbook()
ws = wb.active
# 填充一些数据
for row in range(1, 6):
    for col in range(1, 6):
        ws.cell(row=row, column=col, value=f"R{row}C{col}")
# 锁定指定单元格
locked_cell = ws['A1']
locked_cell.protection = Protection(locked=True)
# 锁定整列
for row in range(1, 6):
    ws.cell(row=row, column=2).protection = Protection(locked=True)
# 锁定整行
for col in range(1, 6):
    ws.cell(row=3, column=col).protection = Protection(locked=True)
# 保护工作表，默认所有单元格都是锁定状态，除非明确设置为 unlocked
ws.protection.sheet = True
# 保存工作簿
wb.save("locked_cells_example.xlsx")

3-164、引用不连续单元格区域集合内的各个连续的单元格区域

# 3-164、引用不连续单元格区域集合内的各个连续的单元格区域
from openpyxl import Workbook
from openpyxl.utils import range_boundaries
# 创建一个新的工作簿和工作表
wb = Workbook()
ws = wb.active
# 填充一些数据
for row in range(1, 10):
    for col in range(1, 10):
        ws.cell(row=row, column=col, value=f"R{row}C{col}")
# 定义多个不连续的单元格区域
ranges = ['A1:B2', 'C3:D4', 'E5:F6']
# 遍历每个区域并将其合并
for cell_range in ranges:
    ws.merge_cells(cell_range)
# 保存工作簿
wb.save("merged_cells_example.xlsx")

3-165、引用工作簿窗口范围内所有的单元格

# 3-165、引用工作簿窗口范围内所有的单元格
from openpyxl import Workbook
# 创建一个新的工作簿和工作表
wb = Workbook()
ws = wb.active
# 假设我们要引用的范围
min_row, min_col = 1, 1
max_row, max_col = 30, 23
# 遍历指定范围内的所有单元格
for row in range(min_row, max_row + 1):
    for col in range(min_col, max_col + 1):
        cell = ws.cell(row=row, column=col)
        # 这里可以对每个单元格进行操作
        cell.value = f"R{row}C{col}"
# 保存工作簿
wb.save("all_cells_in_range.xlsx")

3-166、引用设定了允许滚动区域的单元格区域

略，openpyxl库暂不支持此功能，需要借助其他库实现

3-167、引用指定了左上角和右下角的单元格区域

# 3-167、引用指定了左上角和右下角的单元格区域
from openpyxl import Workbook
# 创建一个新的工作簿和工作表
wb = Workbook()
ws = wb.active
# 为示例填充一些数据
for row in range(1, 11):
    for col in range(1, 5):
        cell = ws.cell(row=row, column=col)
        cell.value = f"R{row}C{col}"
# 定义左上角和右下角的单元格
top_left = "B2"
bottom_right = "D5"
# 引用指定区域
cell_range = ws[f"{top_left}:{bottom_right}"]
# 遍历该区域的所有单元格并打印它们的值
for row in cell_range:
    for cell in row:
        print(cell.value)
# 保存工作簿
wb.save("specified_area_example.xlsx")

3-168、引用以单一编号指定的单元格

# 3-168、引用以单一编号指定的单元格
from openpyxl import Workbook
# 创建一个新的工作簿和工作表
wb = Workbook()
ws = wb.active
# 为示例填充一些数据
for row in range(1, 11):
    for col in range(1, 5):
        cell = ws.cell(row=row, column=col)
        cell.value = f"R{row}C{col}"
# 引用指定单元格
row_number = 3
column_number = 2
# 获取单个单元格
cell = ws.cell(row=row_number, column=column_number)
# 打印该单元格的值
print(cell.value)
# 修改该单元格的值
cell.value = "New Value"
# 打印修改后的单元格值
print(cell.value)
# 保存工作簿
wb.save("single_cell_example.xlsx")

3-169、引用工作表中任意对象左上角/右下角的单元格

# 3-169、引用工作表中任意对象左上角/右下角的单元格
from openpyxl import Workbook
# 创建一个新的工作簿和工作表
wb = Workbook()
ws = wb.active
# 填充一些示例数据
for row in range(1, 11):
    for col in range(1, 5):
        cell = ws.cell(row=row, column=col)
        cell.value = f"R{row}C{col}"
# 定义区域的左上角和右下角坐标
top_left_row = 2
top_left_col = 2
bottom_right_row = 5
bottom_right_col = 4
# 引用左上角单元格
top_left_cell = ws.cell(row=top_left_row, column=top_left_col)
print(f"Top Left Cell: {top_left_cell.coordinate} = {top_left_cell.value}")
# 引用右下角单元格
bottom_right_cell = ws.cell(row=bottom_right_row, column=bottom_right_col)
print(f"Bottom Right Cell: {bottom_right_cell.coordinate} = {bottom_right_cell.value}")
# 修改左上角单元格的值
top_left_cell.value = "Top Left"
print(f"Modified Top Left Cell: {top_left_cell.coordinate} = {top_left_cell.value}")
# 修改右下角单元格的值
bottom_right_cell.value = "Bottom Right"
print(f"Modified Bottom Right Cell: {bottom_right_cell.coordinate} = {bottom_right_cell.value}")
# 保存工作簿
wb.save("corner_cells_example.xlsx")

3-170、引用输入了数组公式的所有单元格区域

略，openpyxl库暂不支持此功能，需要借助其他库实现

3-171、引用输入了某数组公式的单元格区域

略，openpyxl库暂不支持此功能，需要借助其他库实现

3-172、引用输入了某个函数的全部单元格

# 3-172、引用输入了某个函数的全部单元格
from openpyxl import Workbook, load_workbook
# 示例：创建一个包含公式的工作簿
wb = Workbook()
ws = wb.active
ws['A1'] = '=SUM(B1:B10)'
ws['A2'] = '=AVERAGE(B1:B10)'
ws['A3'] = '=SUM(B11:B20)'
ws['B1'] = 1
ws['B2'] = 2
# ... 填充更多数据
wb.save('example.xlsx')
# 加载工作簿
wb = load_workbook('example.xlsx')
ws = wb.active
# 指定要查找的函数
target_function = 'SUM'
# 查找包含指定函数的所有单元格
cells_with_function = []
for row in ws.iter_rows():
    for cell in row:
        if cell.value and isinstance(cell.value, str) and cell.value.startswith('='):
            if target_function in cell.value:
                cells_with_function.append(cell.coordinate)
# 打印包含指定函数的所有单元格
print(f"Cells with function '{target_function}':")
for cell in cells_with_function:
    print(cell)

3-173、引用含有特定文本字符串的所有单元格

# 3-173、引用含有特定文本字符串的所有单元格
from openpyxl import load_workbook
# 加载工作簿
wb = load_workbook('example.xlsx')
ws = wb.active
# 指定要查找的文本字符串
target_string = 'myelsa'
# 查找包含指定文本字符串的所有单元格
cells_with_string = []
for row in ws.iter_rows():
    for cell in row:
        if cell.value and isinstance(cell.value, str):
            if target_string in cell.value:
                cells_with_string.append(cell.coordinate)
# 打印包含指定文本字符串的所有单元格
print(f"Cells with string '{target_string}':")
for cell in cells_with_string:
    print(cell)

3-174、引用设置了数据有效性的所有单元格

略，openpyxl库暂不支持此功能，需要借助其他库实现

3-175、获取单元格地址

# 3-175、获取单元格地址
from openpyxl import load_workbook
# 加载Excel文件
wb = load_workbook('example.xlsx')
ws = wb.active
# 遍历工作表中的所有单元格并打印其地址
for row in ws.iter_rows():
    for cell in row:
        print(f"Cell address: {cell.coordinate}")
# 关闭工作簿
wb.close()

3-176、获取单元格区域内的单元格数

# 3-176、获取单元格区域内的单元格数
from openpyxl import load_workbook
# 加载Excel文件
wb = load_workbook('example.xlsx')
ws = wb.active
# 指定单元格区域
cell_range = ws['A1:C3']
# 计算单元格数
cell_count = 0
for row in cell_range:
    for cell in row:
        cell_count += 1
print(f"Number of cells in the specified range: {cell_count}")
# 关闭工作簿
wb.close()

3-177、获取单元格区域的行数

# 3-177、获取单元格区域的行数
from openpyxl import load_workbook
# 加载Excel文件
wb = load_workbook('example.xlsx')
ws = wb.active
# 指定单元格区域
cell_range = ws['A1:C3']
# 计算行数
row_count = len(cell_range)
print(f"Number of rows in the specified range: {row_count}")
# 关闭工作簿
wb.close()

3-178、获取单元格区域的列数

# 3-178、获取单元格区域的列数
from openpyxl import load_workbook
# 加载Excel文件
wb = load_workbook('example.xlsx')
ws = wb.active
# 指定单元格区域
cell_range = ws['A1:C3']
# 计算列数
col_count = len(cell_range[0])
print(f"Number of columns in the specified range: {col_count}")
# 关闭工作簿
wb.close()

3-179、获取单元格的行号

# 3-179、获取单元格的行号
from openpyxl import load_workbook
# 加载Excel文件
wb = load_workbook('example.xlsx')
ws = wb.active
# 指定单元格
cell = ws['B2']
# 获取行号
row_number = cell.row
print(f"The row number of the cell B2 is: {row_number}")
# 关闭工作簿
wb.close()

3-180、获取单元格的列号

# 3-180、获取单元格的列号
from openpyxl import load_workbook
# 加载Excel文件
wb = load_workbook('example.xlsx')
ws = wb.active
# 指定单元格
cell = ws['B2']
# 获取列号
column_number = cell.column
print(f"The column number of the cell B2 is: {column_number}")
# 关闭工作簿
wb.close()