sheet_operations.py•9.6 kB
"""
工作表操作模块
处理工作表的创建、删除、重命名等操作
"""
import logging
from typing import Dict, Any, List, Optional
from .file_manager import FileManager
class SheetOperations:
"""工作表操作器"""
def __init__(self, file_manager: FileManager):
self.file_manager = file_manager
def create_sheet(self, file_path: str, sheet_name: str, position: int = None) -> Dict[str, Any]:
"""创建新工作表"""
try:
workbook = self.file_manager.load_workbook(file_path)
if not workbook:
return {"error": "无法加载工作簿"}
# 检查工作表名称是否已存在
if sheet_name in workbook.sheetnames:
return {"error": f"工作表 '{sheet_name}' 已存在"}
# 创建新工作表
if position is not None and 0 <= position <= len(workbook.sheetnames):
# 在指定位置插入
sheet = workbook.create_sheet(title=sheet_name, index=position)
else:
# 在末尾添加
sheet = workbook.create_sheet(title=sheet_name)
# 保存工作簿
if self.file_manager.save_workbook(workbook, file_path):
return {
"success": True,
"message": f"成功创建工作表 '{sheet_name}'",
"sheet_name": sheet_name,
"position": workbook.sheetnames.index(sheet_name)
}
else:
return {"error": "保存工作簿失败"}
except Exception as e:
logging.error(f"创建工作表失败: {e}")
return {"error": str(e)}
def delete_sheet(self, file_path: str, sheet_name: str) -> Dict[str, Any]:
"""删除工作表"""
try:
workbook = self.file_manager.load_workbook(file_path)
if not workbook:
return {"error": "无法加载工作簿"}
# 检查工作表是否存在
if sheet_name not in workbook.sheetnames:
return {"error": f"工作表 '{sheet_name}' 不存在"}
# 检查是否为唯一工作表
if len(workbook.sheetnames) == 1:
return {"error": "不能删除唯一的工作表"}
# 删除工作表
sheet = workbook[sheet_name]
workbook.remove(sheet)
# 保存工作簿
if self.file_manager.save_workbook(workbook, file_path):
return {
"success": True,
"message": f"成功删除工作表 '{sheet_name}'",
"remaining_sheets": workbook.sheetnames
}
else:
return {"error": "保存工作簿失败"}
except Exception as e:
logging.error(f"删除工作表失败: {e}")
return {"error": str(e)}
def rename_sheet(self, file_path: str, old_name: str, new_name: str) -> Dict[str, Any]:
"""重命名工作表"""
try:
workbook = self.file_manager.load_workbook(file_path)
if not workbook:
return {"error": "无法加载工作簿"}
# 检查原工作表是否存在
if old_name not in workbook.sheetnames:
return {"error": f"工作表 '{old_name}' 不存在"}
# 检查新名称是否已存在
if new_name in workbook.sheetnames:
return {"error": f"工作表 '{new_name}' 已存在"}
# 重命名工作表
sheet = workbook[old_name]
sheet.title = new_name
# 保存工作簿
if self.file_manager.save_workbook(workbook, file_path):
return {
"success": True,
"message": f"成功将工作表 '{old_name}' 重命名为 '{new_name}'",
"old_name": old_name,
"new_name": new_name
}
else:
return {"error": "保存工作簿失败"}
except Exception as e:
logging.error(f"重命名工作表失败: {e}")
return {"error": str(e)}
def list_sheets(self, file_path: str) -> Dict[str, Any]:
"""列出所有工作表"""
try:
info = self.file_manager.get_file_info(file_path)
if not info:
return {"error": "无法获取文件信息"}
return {
"success": True,
"sheet_names": info.get("sheet_names", []),
"sheet_count": info.get("sheet_count", 0),
"sheets_info": info.get("sheets_info", {})
}
except Exception as e:
logging.error(f"列出工作表失败: {e}")
return {"error": str(e)}
def copy_sheet(self, file_path: str, sheet_name: str, new_name: str) -> Dict[str, Any]:
"""复制工作表"""
try:
workbook = self.file_manager.load_workbook(file_path)
if not workbook:
return {"error": "无法加载工作簿"}
# 检查源工作表是否存在
if sheet_name not in workbook.sheetnames:
return {"error": f"工作表 '{sheet_name}' 不存在"}
# 检查新名称是否已存在
if new_name in workbook.sheetnames:
return {"error": f"工作表 '{new_name}' 已存在"}
# 复制工作表
source_sheet = workbook[sheet_name]
copied_sheet = workbook.copy_worksheet(source_sheet)
copied_sheet.title = new_name
# 保存工作簿
if self.file_manager.save_workbook(workbook, file_path):
return {
"success": True,
"message": f"成功复制工作表 '{sheet_name}' 为 '{new_name}'",
"source_name": sheet_name,
"new_name": new_name
}
else:
return {"error": "保存工作簿失败"}
except Exception as e:
logging.error(f"复制工作表失败: {e}")
return {"error": str(e)}
def move_sheet(self, file_path: str, sheet_name: str, position: int) -> Dict[str, Any]:
"""移动工作表到指定位置"""
try:
workbook = self.file_manager.load_workbook(file_path)
if not workbook:
return {"error": "无法加载工作簿"}
# 检查工作表是否存在
if sheet_name not in workbook.sheetnames:
return {"error": f"工作表 '{sheet_name}' 不存在"}
# 检查位置是否有效
if position < 0 or position >= len(workbook.sheetnames):
return {"error": f"位置 {position} 无效，有效范围: 0-{len(workbook.sheetnames)-1}"}
# 移动工作表
sheet = workbook[sheet_name]
workbook.move_sheet(sheet, offset=position - workbook.sheetnames.index(sheet_name))
# 保存工作簿
if self.file_manager.save_workbook(workbook, file_path):
return {
"success": True,
"message": f"成功移动工作表 '{sheet_name}' 到位置 {position}",
"sheet_name": sheet_name,
"new_position": position,
"sheet_order": workbook.sheetnames
}
else:
return {"error": "保存工作簿失败"}
except Exception as e:
logging.error(f"移动工作表失败: {e}")
return {"error": str(e)}
def get_sheet_info(self, file_path: str, sheet_name: str) -> Dict[str, Any]:
"""获取工作表详细信息"""
try:
workbook = self.file_manager.load_workbook(file_path, read_only=True)
if not workbook:
return {"error": "无法加载工作簿"}
if sheet_name not in workbook.sheetnames:
return {"error": f"工作表 '{sheet_name}' 不存在"}
sheet = workbook[sheet_name]
# 计算非空单元格数量
non_empty_cells = 0
for row in sheet.iter_rows():
for cell in row:
if cell.value is not None:
non_empty_cells += 1
info = {
"success": True,
"sheet_name": sheet_name,
"title": sheet.title,
"max_row": sheet.max_row,
"max_column": sheet.max_column,
"total_cells": sheet.max_row * sheet.max_column,
"non_empty_cells": non_empty_cells,
"position": workbook.sheetnames.index(sheet_name),
"sheet_state": sheet.sheet_state.value if hasattr(sheet, 'sheet_state') else 'visible'
}
workbook.close()
return info
except Exception as e:
logging.error(f"获取工作表信息失败: {e}")
return {"error": str(e)}