跳转至

工作表操作指南

本页面详细介绍Worksheet(工作表)类的所有操作。

创建工作表

基本创建

from symphra_excel import Workbook

wb = Workbook()

sheet = wb.create_worksheet("销售数据")

sheet1 = wb.create_worksheet()

获取工作表

sheet = wb.get_worksheet("销售数据")

first_sheet = wb.worksheets[0]

for sheet in wb.worksheets:
    print(sheet.title)

工作表属性

设置标题

sheet.title = "新标题"

sheet.title = "2024年销售数据"

工作表可见性

sheet.visible = False

sheet.visible = True

工作表保护

sheet.protect(password="123456")

sheet.unprotect(password="123456")

单元格操作

设置单元格值

sheet.set_cell_value("A1", "Hello")
sheet.set_cell_value("B1", 123)
sheet.set_cell_value("C1", 3.14)

from datetime import datetime
sheet.set_cell_value("D1", datetime.now())

获取单元格值

value = sheet.get_cell_value("A1")

if sheet.get_cell_value("B1") is not None:
    print(f"B1的值: {sheet.get_cell_value('B1')}")

批量设置值

data = [
    ["姓名", "年龄", "城市"],
    ["张三", 25, "北京"],
    ["李四", 30, "上海"],
]

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)

行列操作

插入行

sheet.insert_rows(2, 1)

sheet.insert_rows(5, 3)

插入列

sheet.insert_columns("B", 1)

sheet.insert_columns("D", 2)

删除行

sheet.delete_rows(2, 1)

sheet.delete_rows(5, 3)

删除列

sheet.delete_columns("B", 1)

sheet.delete_columns("D", 2)

设置行高

sheet.set_row_height(1, 25)

for row in range(1, 11):
    sheet.set_row_height(row, 20)

设置列宽

sheet.set_column_width("A", 15)

for col in ["A", "B", "C"]:
    sheet.set_column_width(col, 12)

合并单元格

合并单元格

sheet.merge_cells("A1:C1")

sheet.merge_cells("A2:A5")

取消合并

sheet.unmerge_cells("A1:C1")

完整示例

示例1: 创建数据表

from symphra_excel import Workbook
from symphra_excel.styles import PredefinedStyles

with Workbook() as wb:
    sheet = wb.create_worksheet("员工信息")

    headers = ["工号", "姓名", "部门", "工资"]
    for col_idx, header in enumerate(headers, start=1):
        cell = f"{chr(64+col_idx)}1"
        sheet.set_cell_value(cell, header)
        sheet.apply_style(cell, PredefinedStyles.header_style())

    employees = [
        ["E001", "张三", "销售部", 15000],
        ["E002", "李四", "技术部", 18000],
        ["E003", "王五", "人事部", 12000],
    ]

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

    sheet.set_column_width("A", 10)
    sheet.set_column_width("B", 12)
    sheet.set_column_width("C", 12)
    sheet.set_column_width("D", 12)

    wb.save("employees.xlsx")

示例2: 使用公式

from symphra_excel import Workbook

with Workbook() as wb:
    sheet = wb.create_worksheet("计算")

    sheet.set_cell_value("A1", "数量")
    sheet.set_cell_value("B1", "单价")
    sheet.set_cell_value("C1", "总价")

    sheet.set_cell_value("A2", 10)
    sheet.set_cell_value("B2", 5.5)
    sheet.set_cell_value("C2", "=A2*B2")

    sheet.set_cell_value("A3", 20)
    sheet.set_cell_value("B3", 3.2)
    sheet.set_cell_value("C3", "=A3*B3")

    sheet.set_cell_value("C4", "=SUM(C2:C3)")

    wb.save("formulas.xlsx")

最佳实践

✅ 推荐做法

sheet.title = "有效的名称"

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

data = [[1, 2, 3], [4, 5, 6]]
for row_idx, row in enumerate(data, start=1):
    for col_idx, value in enumerate(row, start=1):
        sheet.set_cell_value(f"{chr(64+col_idx)}{row_idx}", value)

❌ 避免的做法

sheet.title = "包含/非法:字符*的名称"

for i in range(100000):
    sheet.set_cell_value(f"A{i}", i)

sheet.set_column_width("A", 1000)

下一步