跳转至

单元格操作指南

本页面详细介绍单元格的各种操作。

单元格引用

A1 表示法

sheet.set_cell_value("A1", "Hello")
sheet.set_cell_value("B2", 123)
sheet.set_cell_value("Z10", 3.14)

行列索引

sheet.set_cell_value_by_index(1, 1, "Hello")
sheet.set_cell_value_by_index(2, 2, 123)

数据类型

文本

sheet.set_cell_value("A1", "文本内容")
sheet.set_cell_value("B1", "Hello World")

数字

sheet.set_cell_value("A2", 123)
sheet.set_cell_value("B2", 3.14159)
sheet.set_cell_value("C2", -99.99)

日期和时间

from datetime import datetime, date, time

sheet.set_cell_value("A3", datetime.now())
sheet.set_cell_value("B3", date.today())
sheet.set_cell_value("C3", time(14, 30, 0))

布尔值

sheet.set_cell_value("A4", True)
sheet.set_cell_value("B4", False)

公式

sheet.set_cell_value("A5", 10)
sheet.set_cell_value("B5", 20)
sheet.set_cell_value("C5", "=A5+B5")
sheet.set_cell_value("D5", "=SUM(A5:B5)")

读取单元格

获取值

value = sheet.get_cell_value("A1")

if value is not None:
    print(f"单元格值: {value}")

获取类型

value = sheet.get_cell_value("A1")
value_type = type(value)

if isinstance(value, str):
    print("这是文本")
elif isinstance(value, (int, float)):
    print("这是数字")

批量操作

批量设置

data = [
    ["A", "B", "C"],
    [1, 2, 3],
    [4, 5, 6],
]

for row_idx, row in enumerate(data, start=1):
    for col_idx, value in enumerate(row, start=1):
        cell = f"{chr(64+col_idx)}{row_idx}"
        sheet.set_cell_value(cell, value)

批量读取

values = []
for row in range(1, 11):
    row_values = []
    for col in ["A", "B", "C"]:
        value = sheet.get_cell_value(f"{col}{row}")
        row_values.append(value)
    values.append(row_values)

单元格范围

设置范围值

for row in range(1, 6):
    for col in range(1, 4):
        cell = f"{chr(64+col)}{row}"
        sheet.set_cell_value(cell, f"{col}x{row}")

读取范围值

range_data = []
for row in range(1, 6):
    row_data = []
    for col_letter in ["A", "B", "C"]:
        value = sheet.get_cell_value(f"{col_letter}{row}")
        row_data.append(value)
    range_data.append(row_data)

完整示例

示例1: 九九乘法表

from symphra_excel import Workbook

with Workbook() as wb:
    sheet = wb.create_worksheet("乘法表")

    for i in range(1, 10):
        for j in range(1, 10):
            cell = f"{chr(64+j)}{i}"
            sheet.set_cell_value(cell, i * j)

    wb.save("multiplication_table.xlsx")

示例2: 数据导入

from symphra_excel import Workbook

data = {
    "姓名": ["张三", "李四", "王五"],
    "年龄": [25, 30, 28],
    "城市": ["北京", "上海", "深圳"],
}

with Workbook() as wb:
    sheet = wb.create_worksheet("数据")

    headers = list(data.keys())
    for col_idx, header in enumerate(headers, start=1):
        cell = f"{chr(64+col_idx)}1"
        sheet.set_cell_value(cell, header)

    num_rows = len(data[headers[0]])
    for row_idx in range(num_rows):
        for col_idx, header in enumerate(headers, start=1):
            cell = f"{chr(64+col_idx)}{row_idx+2}"
            sheet.set_cell_value(cell, data[header][row_idx])

    wb.save("imported_data.xlsx")

最佳实践

✅ 推荐做法

if sheet.get_cell_value("A1") is not None:
    value = sheet.get_cell_value("A1")

try:
    value = int(sheet.get_cell_value("A1"))
except (ValueError, TypeError):
    print("无法转换为整数")

for row_idx, row_data in enumerate(data, start=1):
    for col_idx, value in enumerate(row_data, start=1):
        sheet.set_cell_value(f"{chr(64+col_idx)}{row_idx}", value)

❌ 避免的做法

value = sheet.get_cell_value("A1")
result = value + 10

for i in range(1000000):
    sheet.set_cell_value(f"A{i}", i)
    sheet.set_cell_value(f"B{i}", i * 2)

下一步