Python酷库之旅-第三方库openpyxl(14)

# ['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-121、通过Range属性引用不连续的多行

# 3-121、通过Range属性引用不连续的多行
import openpyxl
from openpyxl.utils import range_boundaries
# 创建一个新的工作簿和工作表
wb = openpyxl.Workbook()
ws = wb.active
# 填充一些数据
for i in range(1, 11):
    for j in range(1, 11):
        ws.cell(row=i, column=j, value=f'{i},{j}')
# 定义不连续的范围
ranges = ['A1:B3', 'D1:E3']
# 获取所有单元格对象
cells = []
for range_string in ranges:
    min_col, min_row, max_col, max_row = range_boundaries(range_string)
    for row in ws.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):
        for cell in row:
            cells.append(cell)
# 打印所有获取的单元格的值
for cell in cells:
    print(cell.value)
# 保存工作簿
wb.save('example.xlsx')

3-122、通过定义名称引用单元格区域

# 3-122、通过定义名称引用单元格区域
import openpyxl
from openpyxl.utils import range_boundaries
# 打开工作簿
wb = openpyxl.load_workbook('example.xlsx')
ws = wb.active
# 获取定义名称的单元格区域
defined_range = wb.defined_names['myelsa_1']
range_string = defined_range.attr_text
dest_sheet, cell_range = range_string.split('!')
min_col, min_row, max_col, max_row = range_boundaries(cell_range)
# 访问和打印单元格区域内的值
for row in ws.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):
    for cell in row:
        print(cell.value)

3-123、引用单元格的快捷方式

# 3-123、引用单元格的快捷方式
# 3-123-1、使用字母和数字引用单元格
import openpyxl
# 创建一个新的工作簿和工作表
wb = openpyxl.Workbook()
ws = wb.active
# 填充数据
ws['A1'] = 'Hello'
ws['B2'] = 'World'
# 保存工作簿
wb.save('shortcut_example.xlsx')

# 3-123-2、使用行和列索引引用单元格
# 设置单元格值
ws.cell(row=3, column=3, value='Openpyxl')
# 获取单元格值
cell_value = ws.cell(row=3, column=3).value
print(cell_value)  # 输出: Openpyxl

# 3-123-3、使用迭代器快速访问多个单元格
# 填充一些数据
for i in range(1, 6):
    for j in range(1, 6):
        ws.cell(row=i, column=j, value=f'{i},{j}')
# 迭代行
for row in ws.iter_rows(min_row=1, max_row=5, min_col=1, max_col=5):
    for cell in row:
        print(cell.value, end=' ')
    print()
# 迭代列
for col in ws.iter_cols(min_row=1, max_row=5, min_col=1, max_col=5):
    for cell in col:
        print(cell.value, end=' ')
    print()

# 3-123-4、使用名称引用单元格区域
import openpyxl
from openpyxl.utils import range_boundaries
# 打开工作簿
wb = openpyxl.load_workbook('example.xlsx')
ws = wb.active
# 获取定义名称的单元格区域
defined_range = wb.defined_names['myelsa_1']
range_string = defined_range.attr_text
dest_sheet, cell_range = range_string.split('!')
min_col, min_row, max_col, max_row = range_boundaries(cell_range)
# 访问和打印单元格区域内的值
for row in ws.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):
    for cell in row:
        print(cell.value)

3-124、通过Cells属性引用某个单元格

# 3-124、通过Cells属性引用某个单元格
import openpyxl
# 打开工作簿
wb = openpyxl.load_workbook('example.xlsx')
ws = wb.active
def get_cell(sheet, row, col):
    return sheet.cell(row=row, column=col)
# 使用自定义的 get_cell 函数
cell = get_cell(ws, 3, 3)
cell.value = 'Custom Cells Reference'
# 保存工作簿
wb.save('example_with_custom_cells.xlsx')

3-125、通过Cells属性引用工作表的全部单元格

# 3-125、通过Cells属性引用工作表的全部单元格
import openpyxl
# 创建一个新的工作簿和工作表
wb = openpyxl.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}')
# 定义一个模拟的Cells属性函数
def get_all_cells(sheet):
    cells = []
    for row in sheet.iter_rows():
        cells.extend(row)
    return cells
# 获取所有单元格
all_cells = get_all_cells(ws)
# 打印所有单元格的值
for cell in all_cells:
    print(f'{cell.coordinate}: {cell.value}')
# 保存工作簿
wb.save('example_with_all_cells.xlsx')

3-126、通过Cells属性引用连续的单元格区域

# 3-126、通过Cells属性引用连续的单元格区域
import openpyxl
# 创建一个新的工作簿和工作表
wb = openpyxl.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}')
# 定义一个函数来引用连续的单元格区域
def get_cell_range(sheet, start_cell, end_cell):
    return sheet[start_cell:end_cell]
# 获取连续的单元格区域
cell_range = get_cell_range(ws, 'A1', 'C3')
# 打印连续单元格区域的值
for row in cell_range:
    for cell in row:
        print(f'{cell.coordinate}: {cell.value}')
# 修改连续单元格区域的值
for row in cell_range:
    for cell in row:
        cell.value = 'Updated'
# 保存工作簿
wb.save('example_with_cell_range.xlsx')

3-127、通过输入框(InputBox)引用所选择的单元格

# 3-127、通过输入框(InputBox)引用所选择的单元格
import openpyxl
from tkinter import Tk, Label, Entry, Button
# 创建一个新的工作簿和工作表
wb = openpyxl.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}')
def get_cell_range(sheet, start_cell, end_cell):
    return sheet[start_cell:end_cell]
def update_cells():
    # 获取用户输入的单元格范围
    start_cell = start_entry.get()
    end_cell = end_entry.get()
    # 获取连续的单元格区域
    cell_range = get_cell_range(ws, start_cell, end_cell)
    # 打印连续单元格区域的值
    for row in cell_range:
        for cell in row:
            print(f'{cell.coordinate}: {cell.value}')
    # 修改连续单元格区域的值
    for row in cell_range:
        for cell in row:
            cell.value = 'Updated'
    # 保存工作簿
    wb.save('example_with_input_box.xlsx')
# 创建Tkinter窗口
root = Tk()
root.title("Cell Range Input")
# 添加标签和输入框
Label(root, text="Start Cell (e.g., A1):").grid(row=0, column=0)
start_entry = Entry(root)
start_entry.grid(row=0, column=1)
Label(root, text="End Cell (e.g., C3):").grid(row=1, column=0)
end_entry = Entry(root)
end_entry.grid(row=1, column=1)
# 添加提交按钮
submit_button = Button(root, text="Update Cells", command=update_cells)
submit_button.grid(row=2, columnspan=2)
# 运行Tkinter主循环
root.mainloop()

3-128、引用活动单元格

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

3-129、引用已选定的单元格

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

3-130、引用已使用的单元格区域

# 3-130、引用已使用的单元格区域
import openpyxl
# 加载现有工作簿
wb = openpyxl.load_workbook('example.xlsx')
ws = wb.active
# 获取已使用的单元格区域
used_range = ws.dimensions
print(f'Used range: {used_range}')
# 解析已使用的单元格区域范围
min_cell, max_cell = used_range.split(':')
min_row = ws[min_cell].row
min_col = ws[min_cell].column
max_row = ws[max_cell].row
max_col = ws[max_cell].column
# 遍历已使用的单元格区域
for row in ws.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):
    for cell in row:
        print(f'Cell {cell.coordinate}: {cell.value}')
        # 在这里进行具体操作，例如修改单元格的值
        # cell.value = 'Updated Value'
# 保存修改后的工作簿
wb.save('example_updated.xlsx')

3-131、引用被空白行和列包围的单元格区域

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

3-132、利用Office属性动态引用某个单元格

# 3-132、利用Offset属性动态引用某个单元格
import openpyxl
# 加载现有工作簿
wb = openpyxl.load_workbook('example.xlsx')
ws = wb.active
# 选择初始单元格
initial_cell = ws['A1']
# 定义偏移量
row_offset = 2  # 向下偏移2行
col_offset = 3  # 向右偏移3列
# 计算目标单元格
target_cell = ws.cell(row=initial_cell.row + row_offset, column=initial_cell.column + col_offset)
# 输出引用的单元格内容
print(f'Referenced cell {target_cell.coordinate}: {target_cell.value}')
# 对引用的单元格进行操作，例如修改其值
target_cell.value = 'Updated based on offset'
# 保存修改后的工作簿
wb.save('example_updated.xlsx')

3-133、利用Office属性动态引用单元格区域

# 3-133、利用Offset属性动态引用单元格区域
import openpyxl
from openpyxl.utils import range_boundaries
# 加载现有工作簿
wb = openpyxl.load_workbook('example.xlsx')
ws = wb.active
# 选择初始单元格区域，例如 "A1:C3"
initial_range = "A1:C3"
# 定义偏移
row_offset = 2  # 向下偏移2行
col_offset = 3  # 向右偏移3列
# 计算目标单元格区域的边界
min_col, min_row, max_col, max_row = range_boundaries(initial_range)
target_min_row = min_row + row_offset
target_max_row = max_row + row_offset
target_min_col = min_col + col_offset
target_max_col = max_col + col_offset
# 输出引用的单元格区域内容
for row in ws.iter_rows(min_row=target_min_row, max_row=target_max_row, min_col=target_min_col, max_col=target_max_col):
    for cell in row:
        print(f'Referenced cell {cell.coordinate}: {cell.value}')
# 对引用的单元格区域进行操作，例如修改其值
for row in ws.iter_rows(min_row=target_min_row, max_row=target_max_row, min_col=target_min_col, max_col=target_max_col):
    for cell in row:
        if cell.value is not None:  # 只修改有值的单元格，或者你可以根据需要去掉这个条件
            cell.value = 'Updated based on offset'
# 保存修改后的工作簿
wb.save('example_updated.xlsx')

3-134、利用Office属性动态引用变更为指定大小的单元格区域

# 3-134、利用resize属性动态引用变更为指定大小的单元格区域
import openpyxl
from openpyxl.utils import range_boundaries
# 加载现有工作簿
wb = openpyxl.load_workbook('example.xlsx')
ws = wb.active
# 选择初始单元格区域，例如 "A1:C3"
initial_range = "A1:C3"
# 定义新的尺寸
new_num_rows = 5  # 新的行数
new_num_cols = 4  # 新的列数
# 计算调整后的单元格区域
min_col, min_row, max_col, max_row = range_boundaries(initial_range)
new_max_row = min_row + new_num_rows - 1
new_max_col = min_col + new_num_cols - 1
resized_range = ws.iter_rows(
    min_row=min_row,
    max_row=new_max_row,
    min_col=min_col,
    max_col=new_max_col
)
# 输出引用的单元格区域内容
for row in ws.iter_rows(min_row=min_row, max_row=new_max_row, min_col=min_col, max_col=new_max_col):
    for cell in row:
        print(f'Referenced cell {cell.coordinate}: {cell.value}')
# 对引用的单元格区域进行操作，例如修改其值
for row in ws.iter_rows(min_row=min_row, max_row=new_max_row, min_col=min_col, max_col=new_max_col):
    for cell in row:
        cell.value = 'Resized area'
# 保存修改后的工作簿
wb.save('example_resized.xlsx')

3-135、引用不包含标题行的单元格区域

# 3-135、引用不包含标题行的单元格区域
import openpyxl
from openpyxl.utils import range_boundaries
# 加载现有工作簿
wb = openpyxl.load_workbook('example.xlsx')
ws = wb.active
# 选择初始单元格区域，例如 "A1:C10"
initial_range = "A1:C10"
# 定义新的尺寸（例如排除标题行后的新尺寸）
new_num_rows = 9  # 总行数减去标题行数
new_num_cols = 3  # 列数保持不变
# 计算调整后的单元格区域，排除第一行（标题行）
min_col, min_row, max_col, max_row = range_boundaries(initial_range)
new_min_row = min_row + 1  # 跳过标题行
new_max_row = new_min_row + new_num_rows - 1
resized_range = ws.iter_rows(
    min_row=new_min_row,
    max_row=new_max_row,
    min_col=min_col,
    max_col=max_col
)
# 输出引用的单元格区域内容
for row in resized_range:
    for cell in row:
        print(f'Referenced cell {cell.coordinate}: {cell.value}')
# 对引用的单元格区域进行操作，例如修改其值
for row in ws.iter_rows(min_row=new_min_row, max_row=new_max_row, min_col=min_col, max_col=max_col):
    for cell in row:
        cell.value = 'Non-header area'
# 保存修改后的工作簿
wb.save('example_non_header.xlsx')

3-136、引用不包含标题列的单元格区域

# 3-136、引用不包含标题列的单元格区域
import openpyxl
from openpyxl.utils import range_boundaries
# 加载现有工作簿
wb = openpyxl.load_workbook('example.xlsx')
ws = wb.active
# 选择初始单元格区域，例如 "A1:C10"
initial_range = "A1:C10"
# 定义新的尺寸（例如排除标题列后的新尺寸）
new_num_rows = 10  # 行数保持不变
new_num_cols = 2   # 总列数减去标题列数
# 计算调整后的单元格区域，排除第一列（标题列）
min_col, min_row, max_col, max_row = range_boundaries(initial_range)
new_min_col = min_col + 1  # 跳过标题列
new_max_col = new_min_col + new_num_cols - 1
resized_range = ws.iter_rows(
    min_row=min_row,
    max_row=max_row,
    min_col=new_min_col,
    max_col=new_max_col
)
# 输出引用的单元格区域内容
for row in resized_range:
    for cell in row:
        print(f'Referenced cell {cell.coordinate}: {cell.value}')
# 对引用的单元格区域进行操作，例如修改其值
for row in ws.iter_rows(min_row=min_row, max_row=max_row, min_col=new_min_col, max_col=new_max_col):
    for cell in row:
        cell.value = 'Non-header area'
# 保存修改后的工作簿
wb.save('example_non_header.xlsx')

3-137、引用任意单元格区域的右下角单元格

# 3-137、引用任意单元格区域的右下角单元格
import openpyxl
from openpyxl.utils import range_boundaries
# 加载现有工作簿
wb = openpyxl.load_workbook('example.xlsx')
ws = wb.active
# 选择初始单元格区域，例如 "A1:C10"
initial_range = "A1:C10"
# 获取单元格区域的边界
min_col, min_row, max_col, max_row = range_boundaries(initial_range)
# 引用右下角的单元格
right_bottom_cell = ws.cell(row=max_row, column=max_col)
# 输出右下角单元格的信息
print(f'Right bottom cell {right_bottom_cell.coordinate}: {right_bottom_cell.value}')
# 对右下角单元格进行操作，例如修改其值
right_bottom_cell.value = 'Right Bottom Cell'
# 保存修改后的工作簿
wb.save('example_right_bottom.xlsx')

3-138、引用输入了计算公式的所有单元格

# 3-138、引用输入了计算公式的所有单元格
import openpyxl
# 加载现有工作簿
wb = openpyxl.load_workbook('example.xlsx', data_only=False)
ws = wb.active
# 创建一个列表来存储包含公式的单元格
formula_cells = []
# 遍历所有单元格
for row in ws.iter_rows():
    for cell in row:
        # 检查单元格是否包含公式
        if cell.data_type == 'f':
            formula_cells.append(cell)
# 输出包含公式的单元格信息
for cell in formula_cells:
    print(f'Cell {cell.coordinate} contains formula: {cell.value}')
# 对包含公式的单元格进行操作，例如修改其值
for cell in formula_cells:
    cell.value = f"Updated formula in {cell.coordinate}"
# 保存修改后的工作簿
wb.save('example_with_formulas.xlsx')

3-139、引用输入了常量的所有单元格

# 3-139、引用输入了常量的所有单元格
import openpyxl
# 加载现有工作簿
wb = openpyxl.load_workbook('example.xlsx', data_only=False)
ws = wb.active
# 创建一个列表来存储包含常量的单元格
constant_cells = []
# 遍历所有单元格
for row in ws.iter_rows():
    for cell in row:
        # 检查单元格是否包含常量（数值或字符串）
        if cell.data_type in ['n', 's']:
            constant_cells.append(cell)
# 输出包含常量的单元格信息
for cell in constant_cells:
    print(f'Cell {cell.coordinate} contains constant: {cell.value}')
# 对包含常量的单元格进行操作，例如修改其值
for cell in constant_cells:
    cell.value = f"Updated value in {cell.coordinate}"
# 保存修改后的工作簿
wb.save('example_with_constants.xlsx')

3-140、引用输入了数字的全部单元格

# 3-140、引用输入了数字的全部单元格
import openpyxl
# 加载现有工作簿
wb = openpyxl.load_workbook('example.xlsx', data_only=False)
ws = wb.active
# 创建一个列表来存储包含数字的单元格
number_cells = []
# 遍历所有单元格
for row in ws.iter_rows():
    for cell in row:
        # 检查单元格是否包含数字
        if cell.data_type == 'n':
            number_cells.append(cell)
# 输出包含数字的单元格信息
for cell in number_cells:
    print(f'Cell {cell.coordinate} contains number: {cell.value}')
# 对包含数字的单元格进行操作，例如修改其值
for cell in number_cells:
    cell.value = cell.value * 2  # 例如，将数字值加倍
# 保存修改后的工作簿
wb.save('example_with_numbers.xlsx')