format_range
Apply formatting to Excel cell ranges including font styles, colors, borders, alignment, and conditional formatting to enhance spreadsheet readability and presentation.
Instructions
Apply formatting to a range of cells.
Args:
sheet_name: Name of worksheet
start_cell: Starting cell
session_id: Session ID from open_workbook (preferred)
filepath: Path to Excel file (legacy, deprecated)
end_cell: Ending cell (optional)
bold: Apply bold formatting
italic: Apply italic formatting
underline: Apply underline formatting
font_size: Font size
font_color: Font color
bg_color: Background color
border_style: Border style
border_color: Border color
number_format: Number format
alignment: Text alignment
wrap_text: Enable text wrapping
merge_cells: Merge cells in range
protection: Cell protection settings (optional)
conditional_format: Conditional formatting settings (optional)
Note: Use session_id for better performance. filepath parameter is deprecated.Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sheet_name | Yes | ||
| start_cell | Yes | ||
| session_id | No | ||
| filepath | No | ||
| end_cell | No | ||
| bold | No | ||
| italic | No | ||
| underline | No | ||
| font_size | No | ||
| font_color | No | ||
| bg_color | No | ||
| border_style | No | ||
| border_color | No | ||
| number_format | No | ||
| alignment | No | ||
| wrap_text | No | ||
| merge_cells | No | ||
| protection | No | ||
| conditional_format | No |
Implementation Reference
- src/xlwings_mcp/server.py:319-433 (registration)MCP tool registration and primary handler for 'format_range'. Dispatches to session-based or legacy filepath-based implementations based on parameters provided.
@mcp.tool() def format_range( sheet_name: str, start_cell: str, session_id: Optional[str] = None, filepath: Optional[str] = None, end_cell: Optional[str] = None, bold: bool = False, italic: bool = False, underline: bool = False, font_size: Optional[int] = None, font_color: Optional[str] = None, bg_color: Optional[str] = None, border_style: Optional[str] = None, border_color: Optional[str] = None, number_format: Optional[str] = None, alignment: Optional[str] = None, wrap_text: bool = False, merge_cells: bool = False, protection: Optional[Dict[str, Any]] = None, conditional_format: Optional[Dict[str, Any]] = None ) -> str: """ Apply formatting to a range of cells. Args: sheet_name: Name of worksheet start_cell: Starting cell session_id: Session ID from open_workbook (preferred) filepath: Path to Excel file (legacy, deprecated) end_cell: Ending cell (optional) bold: Apply bold formatting italic: Apply italic formatting underline: Apply underline formatting font_size: Font size font_color: Font color bg_color: Background color border_style: Border style border_color: Border color number_format: Number format alignment: Text alignment wrap_text: Enable text wrapping merge_cells: Merge cells in range protection: Cell protection settings (optional) conditional_format: Conditional formatting settings (optional) Note: Use session_id for better performance. filepath parameter is deprecated. """ try: # Support both new (session_id) and old (filepath) API if session_id: # New API: use session session = SESSION_MANAGER.get_session(session_id) if not session: return ERROR_TEMPLATES['SESSION_NOT_FOUND'].format( session_id=session_id, ttl=10 # Default TTL is 10 minutes (600 seconds) ) with session.lock: from xlwings_mcp.xlwings_impl.formatting_xlw import format_range_xlw_with_wb result = format_range_xlw_with_wb( session.workbook, sheet_name=sheet_name, start_cell=start_cell, end_cell=end_cell, bold=bold, italic=italic, underline=underline, font_size=font_size, font_color=font_color, bg_color=bg_color, border_style=border_style, border_color=border_color, number_format=number_format, alignment=alignment, wrap_text=wrap_text, merge_cells=merge_cells ) elif filepath: # Legacy API: backwards compatibility logger.warning("Using deprecated filepath parameter. Please use session_id instead.") full_path = get_excel_path(filepath) from xlwings_mcp.xlwings_impl.formatting_xlw import format_range_xlw result = format_range_xlw( filepath=full_path, sheet_name=sheet_name, start_cell=start_cell, end_cell=end_cell, bold=bold, italic=italic, underline=underline, font_size=font_size, font_color=font_color, bg_color=bg_color, border_style=border_style, border_color=border_color, number_format=number_format, alignment=alignment, wrap_text=wrap_text, merge_cells=merge_cells ) else: return ERROR_TEMPLATES['PARAMETER_MISSING'].format( param1='session_id', param2='filepath' ) return result.get("message", "Range formatted successfully") if "error" not in result else f"Error: {result['error']}" except (ValidationError, FormattingError) as e: return f"Error: {str(e)}" except Exception as e: logger.error(f"Error formatting range: {e}") raise - Core implementation of range formatting using xlwings for legacy filepath mode. Applies font, color, borders, alignment, etc. to specified range.
def format_range_xlw( filepath: str, sheet_name: str, start_cell: str, end_cell: Optional[str] = None, bold: bool = False, italic: bool = False, underline: bool = False, font_size: Optional[int] = None, font_color: Optional[str] = None, bg_color: Optional[str] = None, border_style: Optional[str] = None, border_color: Optional[str] = None, number_format: Optional[str] = None, alignment: Optional[str] = None, wrap_text: bool = False, merge_cells: bool = False ) -> Dict[str, Any]: """ Apply formatting to a range of cells using xlwings. Args: filepath: Path to Excel file sheet_name: Name of worksheet start_cell: Starting cell for formatting end_cell: Ending cell for formatting (optional, defaults to start_cell) bold: Apply bold formatting italic: Apply italic formatting underline: Apply underline formatting font_size: Font size in points font_color: Font color (hex code or color name) bg_color: Background color (hex code or color name) border_style: Border style (thin, medium, thick, double) border_color: Border color (hex code or color name) number_format: Number format string (e.g., "0.00", "#,##0", "mm/dd/yyyy") alignment: Text alignment (left, center, right, justify) wrap_text: Enable text wrapping merge_cells: Merge the cell range Returns: Dict with success message or error """ app = None wb = None # Initialize COM for thread safety (Windows) _com_initialize() try: logger.info(f"Applying formatting to range {start_cell}:{end_cell or start_cell} in {sheet_name}") # Check if file exists if not os.path.exists(filepath): return {"error": f"File not found: {filepath}"} # Open Excel app and workbook app = xw.App(visible=False, add_book=False) wb = app.books.open(filepath) # Check if sheet exists sheet_names = [s.name for s in wb.sheets] if sheet_name not in sheet_names: return {"error": f"Sheet '{sheet_name}' not found"} sheet = wb.sheets[sheet_name] # Get the range to format if end_cell: range_obj = sheet.range(f"{start_cell}:{end_cell}") else: range_obj = sheet.range(start_cell) # Apply font formatting if bold: range_obj.font.bold = True if italic: range_obj.font.italic = True if underline: range_obj.font.underline = True if font_size: range_obj.font.size = font_size # Apply font color if font_color: try: rgb = parse_color(font_color) range_obj.font.color = rgb logger.info(f"Applied font color: {font_color} -> RGB{rgb}") except ValueError as e: return {"error": str(e)} # Apply background color if bg_color: try: rgb = parse_color(bg_color) range_obj.color = rgb logger.info(f"Applied background color: {bg_color} -> RGB{rgb}") except ValueError as e: return {"error": str(e)} # Apply borders using COM API if border_style: range_com = range_obj.api # Map border styles to Excel constants border_map = { 'thin': 1, # xlThin 'medium': -4138, # xlMedium 'thick': 4, # xlThick 'double': -4119, # xlDouble 'dotted': -4118, # xlDot 'dashed': -4115 # xlDash } style_constant = border_map.get(border_style.lower(), 1) # Apply to all borders for border_index in [7, 8, 9, 10]: # xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight border = range_com.Borders(border_index) border.LineStyle = style_constant if border_color: try: if border_color.startswith('#'): hex_color = border_color.lstrip('#') rgb_val = int(hex_color[:2], 16) + (int(hex_color[2:4], 16) << 8) + (int(hex_color[4:6], 16) << 16) border.Color = rgb_val except: pass # Apply number format if number_format: range_obj.number_format = number_format # Apply alignment if alignment: alignment_map = { 'left': -4131, # xlLeft 'center': -4108, # xlCenter 'right': -4152, # xlRight 'justify': -4130 # xlJustify } if alignment.lower() in alignment_map: range_obj.api.HorizontalAlignment = alignment_map[alignment.lower()] # Apply text wrapping if wrap_text: range_obj.api.WrapText = True # Merge cells if requested if merge_cells: range_obj.merge() # Save the workbook wb.save() logger.info(f"✅ Successfully applied formatting to range") return { "message": f"Successfully applied formatting to range {start_cell}:{end_cell or start_cell}", "range": f"{start_cell}:{end_cell or start_cell}", "sheet": sheet_name, "formatting_applied": { "bold": bold, "italic": italic, "underline": underline, "font_size": font_size, "font_color": font_color, "bg_color": bg_color, "border_style": border_style, "number_format": number_format, "alignment": alignment, "wrap_text": wrap_text, "merged": merge_cells } } except Exception as e: logger.error(f"❌ Error applying formatting: {str(e)}") return {"error": str(e)} finally: if wb: wb.close() if app: app.quit() - Core implementation of range formatting using xlwings for session (workbook object) mode. Identical logic to legacy but takes existing workbook.
def format_range_xlw_with_wb( wb, sheet_name: str, start_cell: str, end_cell: Optional[str] = None, bold: bool = False, italic: bool = False, underline: bool = False, font_size: Optional[int] = None, font_color: Optional[str] = None, bg_color: Optional[str] = None, border_style: Optional[str] = None, border_color: Optional[str] = None, number_format: Optional[str] = None, alignment: Optional[str] = None, wrap_text: bool = False, merge_cells: bool = False ) -> Dict[str, Any]: """ Session-based range formatting using existing workbook object. Args: wb: Workbook object from session sheet_name: Name of worksheet start_cell: Starting cell for formatting end_cell: Ending cell for formatting (optional, defaults to start_cell) bold: Apply bold formatting italic: Apply italic formatting underline: Apply underline formatting font_size: Font size in points font_color: Font color (hex code or color name) bg_color: Background color (hex code or color name) border_style: Border style (thin, medium, thick, double) border_color: Border color (hex code or color name) number_format: Number format string (e.g., "0.00", "#,##0", "mm/dd/yyyy") alignment: Text alignment (left, center, right, justify) wrap_text: Enable text wrapping merge_cells: Merge the cell range Returns: Dict with success message or error """ try: logger.info(f"🎨 Applying formatting to range {start_cell}:{end_cell or start_cell} in {sheet_name}") # Check if sheet exists sheet_names = [s.name for s in wb.sheets] if sheet_name not in sheet_names: return {"error": f"Sheet '{sheet_name}' not found"} sheet = wb.sheets[sheet_name] # Get the range to format if end_cell: range_obj = sheet.range(f"{start_cell}:{end_cell}") else: range_obj = sheet.range(start_cell) # Apply font formatting if bold: range_obj.font.bold = True if italic: range_obj.font.italic = True if underline: range_obj.font.underline = True if font_size: range_obj.font.size = font_size # Apply font color if font_color: try: rgb = parse_color(font_color) range_obj.font.color = rgb logger.info(f"Applied font color: {font_color} -> RGB{rgb}") except ValueError as e: return {"error": str(e)} # Apply background color if bg_color: try: rgb = parse_color(bg_color) range_obj.color = rgb logger.info(f"Applied background color: {bg_color} -> RGB{rgb}") except ValueError as e: return {"error": str(e)} # Apply borders using COM API if border_style: range_com = range_obj.api # Map border styles to Excel constants border_map = { 'thin': 1, # xlThin 'medium': -4138, # xlMedium 'thick': 4, # xlThick 'double': -4119, # xlDouble 'dotted': -4118, # xlDot 'dashed': -4115 # xlDash } style_constant = border_map.get(border_style.lower(), 1) # Apply to all borders for border_index in [7, 8, 9, 10]: # xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight border = range_com.Borders(border_index) border.LineStyle = style_constant if border_color: try: if border_color.startswith('#'): hex_color = border_color.lstrip('#') rgb_val = int(hex_color[:2], 16) + (int(hex_color[2:4], 16) << 8) + (int(hex_color[4:6], 16) << 16) border.Color = rgb_val except: pass # Apply number format if number_format: range_obj.number_format = number_format # Apply alignment if alignment: alignment_map = { 'left': -4131, # xlLeft 'center': -4108, # xlCenter 'right': -4152, # xlRight 'justify': -4130 # xlJustify } if alignment.lower() in alignment_map: range_obj.api.HorizontalAlignment = alignment_map[alignment.lower()] # Apply text wrapping if wrap_text: range_obj.api.WrapText = True # Merge cells if requested if merge_cells: range_obj.merge() # Save the workbook wb.save() logger.info(f"✅ Successfully applied formatting to range") return { "message": f"Successfully applied formatting to range {start_cell}:{end_cell or start_cell}", "range": f"{start_cell}:{end_cell or start_cell}", "sheet": sheet_name, "formatting_applied": { "bold": bold, "italic": italic, "underline": underline, "font_size": font_size, "font_color": font_color, "bg_color": bg_color, "border_style": border_style, "number_format": number_format, "alignment": alignment, "wrap_text": wrap_text, "merged": merge_cells } } except Exception as e: logger.error(f"❌ Error applying formatting: {str(e)}") return {"error": str(e)} - Utility function to parse various color formats (hex, standard names, RGB strings) into RGB tuples used by both format_range handlers.
def parse_color(color_input: str) -> Tuple[int, int, int]: """ Parse color input to RGB tuple. Args: color_input: Color as hex (#RRGGBB), name (yellow), or RGB string Returns: RGB tuple (r, g, b) Raises: ValueError: If color format is invalid """ if not color_input: raise ValueError("Color input is empty") # Handle hex colors if color_input.startswith('#'): hex_color = color_input.lstrip('#') if len(hex_color) != 6: raise ValueError(f"Invalid hex color format: {color_input}. Use #RRGGBB") try: return tuple(int(hex_color[i:i+2], 16) for i in (0, 2, 4)) except ValueError: raise ValueError(f"Invalid hex color values: {color_input}") # Handle standard color names color_lower = color_input.lower() if color_lower in STANDARD_COLORS: return STANDARD_COLORS[color_lower] # Try to parse as RGB tuple string "(r,g,b)" if ',' in color_input: try: # Remove parentheses and spaces clean = color_input.strip('() ') parts = [int(x.strip()) for x in clean.split(',')] if len(parts) == 3 and all(0 <= p <= 255 for p in parts): return tuple(parts) except: pass # If nothing worked, provide helpful error available_colors = ', '.join(STANDARD_COLORS.keys()) raise ValueError( f"COLOR_FORMAT_ERROR: '{color_input}' is not recognized. " f"Use hex format (#RRGGBB) or standard colors: {available_colors}" ) - src/xlwings_mcp/server.py:341-366 (schema)Input schema and documentation for the format_range tool, defining all parameters, their types, and descriptions.
""" Apply formatting to a range of cells. Args: sheet_name: Name of worksheet start_cell: Starting cell session_id: Session ID from open_workbook (preferred) filepath: Path to Excel file (legacy, deprecated) end_cell: Ending cell (optional) bold: Apply bold formatting italic: Apply italic formatting underline: Apply underline formatting font_size: Font size font_color: Font color bg_color: Background color border_style: Border style border_color: Border color number_format: Number format alignment: Text alignment wrap_text: Enable text wrapping merge_cells: Merge cells in range protection: Cell protection settings (optional) conditional_format: Conditional formatting settings (optional) Note: Use session_id for better performance. filepath parameter is deprecated. """