Spire.XLS MCP Server

import re from typing import Tuple, Optional, Any import datetime from spire.xls import * def letter_to_column(column_letter: str) -> int: """ Convert Excel column letter to column number. Example: A -> 1, Z -> 26, AA -> 27, etc. Args: column_letter: Excel column letter (A-Z, AA-ZZ, etc.) Returns: Column number (1-based index) """ if not column_letter or not column_letter.isalpha(): try: # If it's already a number, just return it return int(column_letter) except ValueError: raise ValueError(f"Invalid column letter: {column_letter}") column_letter = column_letter.upper() result = 0 for char in column_letter: result = result * 26 + (ord(char) - ord('A') + 1) return result def column_to_letter(column: int) -> str: """ Convert column number to Excel column letter. Example: 1 -> A, 26 -> Z, 27 -> AA, etc. Args: column: Column number (1-based index) Returns: Excel column letter (A-Z, AA-ZZ, etc.) """ if not isinstance(column, int) or column < 1: # If it's already a string, just return it if isinstance(column, str) and column.isalpha(): return column raise ValueError(f"Invalid column number: {column}") result = "" while column > 0: remainder = (column - 1) % 26 result = chr(ord('A') + remainder) + result column = (column - 1) // 26 return result def serialize_cell(cell, preview_only: bool): """Serialize a cell to a JSON-serializable dictionary object with null checks""" try: # Basic properties result = { "address": cell.RangeAddressLocal if hasattr(cell, "RangeAddressLocal") else None, "row": cell.Row if hasattr(cell, "Row") else None, "column": cell.Column if hasattr(cell, "Column") else None, "column_letter": column_to_letter(cell.Column) if hasattr(cell, "Column") else None, "value": None, "text": None, "formula": None, "has_formula": False } # Value handling (handle different types of values) try: result["value"] = cell.Value except: pass try: result["formula_value"] = cell.FormulaValue except: pass try: result["text"] = cell.Text except: pass # Formula handling try: result["has_formula"] = cell.HasFormula if cell.HasFormula: result["formula"] = cell.Formula except: pass if preview_only: return result # Style handling style_dict = {} # Font handling try: font_dict = {} if hasattr(cell, "Style") and hasattr(cell.Style, "Font"): font = cell.Style.Font if hasattr(font, "IsBold"): font_dict["bold"] = font.IsBold if hasattr(font, "IsItalic"): font_dict["italic"] = font.IsItalic if hasattr(font, "FontName"): font_dict["name"] = font.FontName if hasattr(font, "Size"): font_dict["size"] = font.Size if hasattr(font, "Color"): try: font_dict["color"] = { "r": font.Color.R, "g": font.Color.G, "b": font.Color.B } except: pass if hasattr(font, "Underline"): font_dict["underline"] = str(font.Underline) != "None" if font_dict: style_dict["font"] = font_dict except: pass # Alignment handling try: if hasattr(cell, "Style"): if hasattr(cell.Style, "HorizontalAlignment"): style_dict["horizontal_alignment"] = str(cell.Style.HorizontalAlignment) if hasattr(cell.Style, "VerticalAlignment"): style_dict["vertical_alignment"] = str(cell.Style.VerticalAlignment) if hasattr(cell.Style, "WrapText"): style_dict["wrap_text"] = cell.Style.WrapText if hasattr(cell.Style, "Rotation"): style_dict["rotation"] = cell.Style.Rotation if hasattr(cell.Style, "IndentLevel"): style_dict["indent_level"] = cell.Style.IndentLevel except: pass # Fill handling try: if hasattr(cell, "Style") and hasattr(cell.Style, "Interior"): interior_dict = {} interior = cell.Style.Interior if hasattr(interior, "Color"): try: interior_dict["color"] = { "r": interior.Color.R, "g": interior.Color.G, "b": interior.Color.B } except: pass if hasattr(interior, "FillPattern"): interior_dict["pattern"] = str(interior.FillPattern) if interior_dict: style_dict["fill"] = interior_dict except: pass # Borders handling try: if hasattr(cell, "Style") and hasattr(cell.Style, "Borders"): borders_dict = {} borders = cell.Style.Borders if hasattr(borders, "LineStyle"): borders_dict["line_style"] = str(borders.LineStyle) if hasattr(borders, "Color"): try: borders_dict["color"] = { "r": borders.Color.R, "g": borders.Color.G, "b": borders.Color.B } except: pass # Individual borders border_positions = ["Top", "Bottom", "Left", "Right"] for pos in border_positions: try: if hasattr(borders, pos): border = getattr(borders, pos) border_info = {} if hasattr(border, "LineStyle"): border_info["line_style"] = str(border.LineStyle) if hasattr(border, "Color"): try: border_info["color"] = { "r": border.Color.R, "g": border.Color.G, "b": border.Color.B } except: pass if border_info: borders_dict[pos.lower()] = border_info except: pass if borders_dict: style_dict["borders"] = borders_dict except: pass # Number format try: if hasattr(cell, "Style") and hasattr(cell.Style, "NumberFormat"): style_dict["number_format"] = cell.Style.NumberFormat except: pass # Cell protection try: if hasattr(cell, "Style"): protection_dict = {} if hasattr(cell.Style, "HideFormula"): protection_dict["hide_formula"] = cell.Style.HideFormula if protection_dict: style_dict["protection"] = protection_dict except: pass # Add style to result if style_dict: result["style"] = style_dict # Cell type try: if hasattr(cell, "Type"): result["cell_type"] = str(cell.Type) except: pass # Merged cells information try: if hasattr(cell, "IsMerged"): result["is_merged"] = cell.IsMerged if cell.IsMerged and hasattr(cell, "MergeArea"): try: area = cell.MergeArea result["merge_area"] = { "first_row": area.FirstRow, "first_column": area.FirstColumn, "last_row": area.LastRow, "last_column": area.LastColumn } except: pass except: pass return result except Exception as e: # Return basic information when any exception occurs return { "error": f"Failed to serialize cell: {str(e)}", "row": getattr(cell, "Row", None), "column": getattr(cell, "Column", None), "value": getattr(cell, "Value", None) } def parse_cell_range( start_cell: str, end_cell: Optional[str] = None, workbook: Workbook = Workbook() ) -> Tuple[int, int, Optional[int], Optional[int]]: """Parse Excel cell references into row and column numbers.""" try: start_cell = workbook.Worksheets[0].Range[start_cell] except: raise ValueError(f"Invalid start cell reference: {start_cell}") start_col = start_cell.Column start_row = start_cell.Row # Parse end cell if provided end_col = None end_row = None if end_cell: try: end_cell = workbook.Worksheets[0].Range[end_cell] except: raise ValueError(f"Invalid end cell reference: {end_cell}") end_col = end_cell.Column end_row = end_cell.Row return start_row, start_col, end_row, end_col def validate_cell_reference_regex(cell_ref: str) -> bool: """Validate Excel cell reference format.""" if not cell_ref: return False # Basic format validation pattern = r'^[A-Za-z]{1,3}[1-9][0-9]*$' if not re.match(pattern, cell_ref): return False return True class EnumMapper: SUBTOTAL_MAP = { "sum": SubtotalTypes.Sum, "average": SubtotalTypes.Average, "count": SubtotalTypes.Count, "min": SubtotalTypes.Min, "max": SubtotalTypes.Max } # Filter operator type mapping FILTER_OPERATOR_MAP = { "=": FilterOperatorType.Equal, ">": FilterOperatorType.GreaterThan, "<": FilterOperatorType.LessThan, ">=": FilterOperatorType.GreaterOrEqual, "<=": FilterOperatorType.LessOrEqual, "<>": FilterOperatorType.NotEqual } # condition format operator OPERATOR_MAP = { "greater": ComparisonOperatorType.Greater, "gt": ComparisonOperatorType.Greater, ">": ComparisonOperatorType.Greater, "大于": ComparisonOperatorType.Greater, "greater than": ComparisonOperatorType.Greater, "greater_or_equal": ComparisonOperatorType.GreaterOrEqual, ">=": ComparisonOperatorType.GreaterOrEqual, "ge": ComparisonOperatorType.GreaterOrEqual, "大于等于": ComparisonOperatorType.GreaterOrEqual, "less": ComparisonOperatorType.Less, "lt": ComparisonOperatorType.Less, "<": ComparisonOperatorType.Less, "小于": ComparisonOperatorType.Less, "less than": ComparisonOperatorType.Less, "less_or_equal": ComparisonOperatorType.LessOrEqual, "le": ComparisonOperatorType.LessOrEqual, "<=": ComparisonOperatorType.LessOrEqual, "小于等于": ComparisonOperatorType.LessOrEqual, "equal": ComparisonOperatorType.Equal, "eq": ComparisonOperatorType.Equal, "=": ComparisonOperatorType.Equal, "等于": ComparisonOperatorType.Equal, "not_equal": ComparisonOperatorType.NotEqual, "ne": ComparisonOperatorType.NotEqual, "!=": ComparisonOperatorType.NotEqual, "<>": ComparisonOperatorType.NotEqual, "不等于": ComparisonOperatorType.NotEqual, } # alignment ALIGNMENT_MAP = { "left": HorizontalAlignType.Left, "居左": HorizontalAlignType.Left, "center": HorizontalAlignType.Center, "居中": HorizontalAlignType.Center, "right": HorizontalAlignType.Right, "居右": HorizontalAlignType.Right, "justify": HorizontalAlignType.Justify, "两端对齐": HorizontalAlignType.Justify, } # border line style BORDER_STYLE_MAP = { "thin": LineStyleType.Thin, "细线": LineStyleType.Thin, "medium": LineStyleType.Medium, "中线": LineStyleType.Medium, "thick": LineStyleType.Thick, "粗线": LineStyleType.Thick, "double": LineStyleType.Double, "双线": LineStyleType.Double, } # chart type CHART_TYPE_MAP = { "column": ExcelChartType.ColumnClustered, "bar": ExcelChartType.BarClustered, "line": ExcelChartType.Line, "pie": ExcelChartType.Pie, "area": ExcelChartType.Area, "scatter": ExcelChartType.ScatterLine, "doughnut": ExcelChartType.Doughnut, "waterfall": ExcelChartType.WaterFall, "column_stacked": ExcelChartType.ColumnStacked, "column_100_percent_stacked": ExcelChartType.Column100PercentStacked, "bubble": ExcelChartType.Bubble, "funnel": ExcelChartType.Funnel, "treemap": ExcelChartType.TreeMap, "sunburst": ExcelChartType.SunBurst, "histogram": ExcelChartType.Histogram, "box_and_whisker": ExcelChartType.BoxAndWhisker, } # conditional format type CONDITION_TYPE_MAP = { "cell": ConditionalFormatType.CellValue, "单元格值": ConditionalFormatType.CellValue, "text": ConditionalFormatType.ContainsText, "文本": ConditionalFormatType.ContainsText, "date": ConditionalFormatType.TimePeriod, "日期": ConditionalFormatType.TimePeriod, "time_period": ConditionalFormatType.TimePeriod, "时间段": ConditionalFormatType.TimePeriod, "average": ConditionalFormatType.Average, "平均值": ConditionalFormatType.Average, "duplicate": ConditionalFormatType.DuplicateValues, "重复值": ConditionalFormatType.DuplicateValues, "unique": ConditionalFormatType.UniqueValues, "唯一值": ConditionalFormatType.UniqueValues, "formula": ConditionalFormatType.Formula, "公式": ConditionalFormatType.Formula, "top10": ConditionalFormatType.TopBottom, "前10项": ConditionalFormatType.TopBottom, "data_bar": ConditionalFormatType.DataBar, "数据条": ConditionalFormatType.DataBar, "color_scale": ConditionalFormatType.ColorScale, "色阶": ConditionalFormatType.ColorScale, "icon_set": ConditionalFormatType.IconSet, "图标集": ConditionalFormatType.IconSet } @staticmethod def smart_enum_map(input_str: str, mapping: dict, default): if not input_str: return default key = input_str.strip().lower() return mapping.get(key, default) @classmethod def get_operator_enum(cls, op_str: str) -> ComparisonOperatorType: return cls.smart_enum_map(op_str, cls.OPERATOR_MAP, ComparisonOperatorType.Greater) @classmethod def get_alignment_enum(cls, align_str: str) -> HorizontalAlignType: return cls.smart_enum_map(align_str, cls.ALIGNMENT_MAP, HorizontalAlignType.Left) @classmethod def get_border_style_enum(cls, style_str: str) -> LineStyleType: return cls.smart_enum_map(style_str, cls.BORDER_STYLE_MAP, LineStyleType.Thin) @classmethod def get_chart_type_enum(cls, chart_str: str) -> ExcelChartType: return cls.smart_enum_map(chart_str, cls.CHART_TYPE_MAP, ExcelChartType.ColumnClustered) @classmethod def get_condition_enum(cls, type_str: str) -> ConditionalFormatType: return cls.smart_enum_map(type_str, cls.CONDITION_TYPE_MAP, ConditionalFormatType.CellValue) @classmethod def get_subtotal_enum(cls, func_str: str) -> SubtotalTypes: return cls.smart_enum_map(func_str, cls.SUBTOTAL_MAP, SubtotalTypes.Sum) @classmethod def get_filter_operator_enum(cls, op_str: str) -> FilterOperatorType: return cls.smart_enum_map(op_str, cls.FILTER_OPERATOR_MAP, FilterOperatorType.Equal) def create_spire_object(value: Any) -> Any: """ Create corresponding SpireObject based on the input value's type. Args: value: The value to be converted to SpireObject Returns: SpireObject instance of corresponding type Examples: >>> create_spire_object(123) # Returns Int32(123) >>> create_spire_object(123.45) # Returns Double(123.45) >>> create_spire_object("text") # Returns String("text") >>> create_spire_object(True) # Returns Boolean(True) >>> create_spire_object(datetime.datetime(2023, 1, 1)) # Returns DateTime(2023, 1, 1) """ if value is None: return String("") if isinstance(value, bool): return Boolean(value) if isinstance(value, int): if -2147483648 <= value <= 2147483647: return Int32(value) else: return Int64(value) if isinstance(value, float): return Double(value) if isinstance(value, str): return String(value) if isinstance(value, (datetime.datetime, datetime.date)): return DateTime(value.year, value.month, value.day, getattr(value, 'hour', 0), getattr(value, 'minute', 0), getattr(value, 'second', 0), getattr(value, 'microsecond', 0)) return String(str(value))