工作簿操作指南¶
本页面详细介绍Workbook（工作簿）类的所有操作。
创建工作簿¶
创建新工作簿¶
from symphra_excel import Workbook
# 方式1: 直接创建
wb = Workbook()
# 方式2: 使用上下文管理器（推荐）
with Workbook() as wb:
# ... 操作工作簿
pass
# 自动关闭和清理资源
加载现有工作簿¶
# 加载Excel文件
wb = Workbook("existing_file.xlsx")
# 指定完整路径
wb = Workbook("/path/to/file.xlsx")
# 相对路径
wb = Workbook("../data/report.xlsx")
工作表管理¶
创建工作表¶
# 创建默认名称的工作表
sheet = wb.create_worksheet()
# 创建指定名称的工作表
sheet = wb.create_worksheet("销售数据")
# 创建多个工作表
sheet1 = wb.create_worksheet("一月")
sheet2 = wb.create_worksheet("二月")
sheet3 = wb.create_worksheet("三月")
获取工作表¶
# 通过名称获取
sheet = wb.get_worksheet("销售数据")
# 获取第一个工作表
sheet = wb.worksheets[0]
# 遍历所有工作表
for sheet in wb.worksheets:
print(sheet.title)
删除工作表¶
重命名工作表¶
保存工作簿¶
保存到文件¶
# 保存到新文件
wb.save("output.xlsx")
# 保存到指定路径
wb.save("/path/to/output.xlsx")
# 覆盖原文件
wb.save("original.xlsx")
保存到内存¶
# 获取字节数据
data = wb.save_to_memory()
# 可以用于网络传输或其他用途
with open("output.xlsx", "wb") as f:
f.write(data)
另存为¶
工作簿属性¶
元数据设置¶
# 设置文档属性
wb.properties.title = "销售报表"
wb.properties.author = "张三"
wb.properties.company = "ABC公司"
wb.properties.comments = "2024年度销售数据"
wb.properties.keywords = "销售,报表,2024"
获取信息¶
# 获取工作表数量
count = len(wb.worksheets)
# 获取所有工作表名称
names = [sheet.title for sheet in wb.worksheets]
# 检查工作表是否存在
if "销售数据" in wb.worksheet_names:
sheet = wb.get_worksheet("销售数据")
高级功能¶
复制工作簿¶
批量创建工作簿¶
# 批量创建多个报表
departments = ["销售部", "技术部", "人事部"]
for dept in departments:
with Workbook() as wb:
sheet = wb.create_worksheet(dept)
sheet.set_cell_value("A1", f"{dept}数据")
wb.save(f"{dept}_report.xlsx")
模板模式¶
# 使用模板创建多个文件
template = Workbook("template.xlsx")
for i in range(10):
wb = template.copy()
sheet = wb.worksheets[0]
sheet.set_cell_value("A1", f"报表 {i+1}")
wb.save(f"report_{i+1}.xlsx")
完整示例¶
示例1: 创建多工作表报表¶
from symphra_excel import Workbook
from symphra_excel.styles import PredefinedStyles
with Workbook() as wb:
# 创建三个工作表
for month in ["一月", "二月", "三月"]:
sheet = wb.create_worksheet(month)
# 添加表头
headers = ["日期", "销售额", "利润"]
for col_idx, header in enumerate(headers):
cell = f"{chr(65+col_idx)}1"
sheet.set_cell_value(cell, header)
sheet.apply_style(cell, PredefinedStyles.header_style())
# 添加示例数据
for day in range(1, 6):
sheet.set_cell_value(f"A{day+1}", f"{month}{day}日")
sheet.set_cell_value(f"B{day+1}", day * 1000)
sheet.set_cell_value(f"C{day+1}", day * 300)
wb.save("quarterly_report.xlsx")
示例2: 从模板批量生成¶
from symphra_excel import Workbook
# 准备数据
employees = [
{"name": "张三", "dept": "销售部", "salary": 15000},
{"name": "李四", "dept": "技术部", "salary": 18000},
{"name": "王五", "dept": "人事部", "salary": 12000},
]
# 为每个员工生成工资单
for emp in employees:
with Workbook() as wb:
sheet = wb.create_worksheet("工资单")
sheet.set_cell_value("A1", "姓名")
sheet.set_cell_value("B1", emp["name"])
sheet.set_cell_value("A2", "部门")
sheet.set_cell_value("B2", emp["dept"])
sheet.set_cell_value("A3", "工资")
sheet.set_cell_value("B3", emp["salary"])
wb.save(f"salary_{emp['name']}.xlsx")
最佳实践¶
✅ 推荐做法¶
# 使用上下文管理器
with Workbook() as wb:
sheet = wb.create_worksheet("数据")
sheet.set_cell_value("A1", "Hello")
wb.save("output.xlsx")
# 批量操作前检查
if "数据" not in wb.worksheet_names:
wb.create_worksheet("数据")
# 使用try-except处理异常
try:
wb.save("output.xlsx")
except Exception as e:
print(f"保存失败: {e}")
❌ 避免的做法¶
# 不要忘记关闭
wb = Workbook()
# ... 操作
# 忘记调用 wb.close()
# 不要重复创建同名工作表
wb.create_worksheet("数据")
wb.create_worksheet("数据") # 可能导致错误
# 不要在循环中不必要地保存
for i in range(1000):
wb.save("output.xlsx") # 太慢了！
错误处理¶
常见错误¶
from symphra_excel.utils.exceptions import (
WorksheetNotFoundError,
InvalidWorkbookError
)
try:
wb = Workbook("non_existent.xlsx")
except FileNotFoundError:
print("文件不存在")
try:
sheet = wb.get_worksheet("不存在的表")
except WorksheetNotFoundError:
print("工作表不存在")
sheet = wb.create_worksheet("不存在的表")
性能优化¶
大文件处理¶
from symphra_excel.memory_ops import MemoryOptimizedWorkbook
# 处理大文件时使用内存优化版本
with MemoryOptimizedWorkbook() as wb:
sheet = wb.create_worksheet("大数据")
# 批量写入大量数据
for i in range(100000):
sheet.set_cell_value(f"A{i+1}", f"Data {i}")
wb.save("large_file.xlsx")