import io import warnings from typing import Optional, Union, Tuple, BinaryIO from openpyxl import load_workbook from openpyxl.worksheet.worksheet import Worksheet from openpyxl.cell.cell import MergedCell from openpyxl.utils import get_column_letter from PIL import Image, ImageDraw, ImageFont # Suppress warnings warnings.filterwarnings('ignore') class ExcelRenderer: def __init__(self, file_content: Union[bytes, BinaryIO], font_path_regular: str = "assets/simsun.ttc", font_path_bold: str = "assets/simhei.ttf"): """ Initialize the renderer with Excel file content and font paths. """ self.file_content = file_content if isinstance(file_content, io.BytesIO) else io.BytesIO(file_content) self.font_path_regular = font_path_regular self.font_path_bold = font_path_bold self._load_fonts() def _load_fonts(self): """ Load fonts with fallback mechanisms. """ # Cache for loaded fonts to avoid reloading for same size self.font_cache = {} def _get_font(self, font_name: Optional[str], is_bold: bool, size: int) -> ImageFont.FreeTypeFont: """ Get font with specific properties, using cache. """ key = (font_name, is_bold, size) if key in self.font_cache: return self.font_cache[key] # Determine font file path based on font name # Default to regular (simsun) if not specified or not found font_path = self.font_path_regular if font_name: font_name_lower = font_name.lower() if "黑体" in font_name_lower or "simhei" in font_name_lower or "heiti" in font_name_lower: font_path = self.font_path_bold # Use SimHei for Heiti elif "宋体" in font_name_lower or "simsun" in font_name_lower or "songti" in font_name_lower: font_path = self.font_path_regular # Use SimSun for Songti # Add more mappings here if needed (e.g., Arial -> arial.ttf) # If is_bold is True but we selected a regular font (like SimSun), PIL can fake bold but it's better to use a bold font file if available. # However, for Chinese fonts like SimHei, it's already "bold-like" (sans-serif bold). # If we are using SimSun (Regular) and want bold, we might want to check if we have a Bold version of SimSun (usually we don't in this simple setup). # Current logic: self.font_path_bold is SimHei. # So if is_bold is True, we often prefer SimHei over SimSun if no specific font is requested. if not font_name and is_bold: font_path = self.font_path_bold try: font = ImageFont.truetype(font_path, size) except OSError: # Fallback try: fallback_name = "arialbd.ttf" if is_bold else "arial.ttf" font = ImageFont.truetype(fallback_name, size) except OSError: font = ImageFont.load_default() self.font_cache[key] = font return font def render_to_bytes(self, sheet_name: Optional[str] = None, dpi: int = 300, padding: int = 20, scale: int = 2) -> bytes: """ Render the specified sheet to a PNG image and return bytes. :param scale: Internal scaling factor for high-DPI rendering (default 2x). """ img = self._render_image(sheet_name, padding, scale) output = io.BytesIO() img.save(output, format='PNG', dpi=(dpi, dpi)) output.seek(0) return output.getvalue() def _render_image(self, sheet_name: Optional[str], padding: int, scale: int) -> Image.Image: """ Internal method to draw the Excel sheet onto a PIL Image. """ wb = load_workbook(self.file_content, data_only=True) if sheet_name is None: sheet = wb.active else: if sheet_name in wb.sheetnames: sheet = wb[sheet_name] else: raise ValueError(f"Sheet '{sheet_name}' not found. Available sheets: {wb.sheetnames}") return self._draw_sheet(sheet, padding, scale) def _draw_sheet(self, sheet: Worksheet, padding: int, scale: int) -> Image.Image: cell_height = 40 * scale # Scaled cell height padding = padding * scale # Scaled padding max_row = sheet.max_row max_col = sheet.max_column # Calculate column widths and image dimensions col_widths_pixels = [] img_width = 2 * padding for col in range(1, max_col + 1): col_letter = get_column_letter(col) # Get column width (approximate conversion) col_dim = sheet.column_dimensions[col_letter] col_width_excel = col_dim.width if col_dim.width else 10 # Excel width to pixels (approximate factor ~7 + padding) # Apply scale factor width_px = int((col_width_excel * 7 + 5) * scale) col_widths_pixels.append(width_px) img_width += width_px img_height = max_row * cell_height + 2 * padding # Create image img = Image.new('RGB', (img_width, img_height), color='white') draw = ImageDraw.Draw(img) # Pre-calculate column x-positions col_x_positions = [padding] current_x = padding for width in col_widths_pixels: current_x += width col_x_positions.append(current_x) # Parse merged cells ranges # Format: {(min_row, min_col): (max_row, max_col)} merged_ranges = {} for merged_range in sheet.merged_cells.ranges: merged_ranges[(merged_range.min_row, merged_range.min_col)] = (merged_range.max_row, merged_range.max_col) # Draw cells for row in range(1, max_row + 1): for col in range(1, max_col + 1): cell = sheet.cell(row=row, column=col) # Check if this cell is the top-left of a merged range if (row, col) in merged_ranges: max_r, max_c = merged_ranges[(row, col)] x1 = col_x_positions[col - 1] y1 = padding + (row - 1) * cell_height # Use the max_col of the merged range to determine x2 x2 = col_x_positions[max_c] # Use the max_row of the merged range to determine y2 y2 = padding + max_r * cell_height self._draw_cell(draw, cell, x1, y1, x2, y2, scale) # Skip if it is a merged cell but NOT the top-left (already handled above or by MergedCell check) elif isinstance(cell, MergedCell): continue else: # Normal cell x1 = col_x_positions[col - 1] y1 = padding + (row - 1) * cell_height x2 = col_x_positions[col] y2 = y1 + cell_height self._draw_cell(draw, cell, x1, y1, x2, y2, scale) return img def _draw_cell(self, draw: ImageDraw.ImageDraw, cell, x1, y1, x2, y2, scale: int): # Skip MergedCells that are not the top-left cell if isinstance(cell, MergedCell): return # Background color fill_color = cell.fill.start_color.rgb bg_color = self._parse_color(fill_color, default=(255, 255, 255)) # Draw background and border # Scale border width: at least 1px, roughly 1px per scale unit but kept thin for aesthetics default_border_width = max(1, scale) # Draw background draw.rectangle([x1, y1, x2, y2], fill=bg_color) # Handle borders # Default thin grey border if no specific border is set (or for gridlines) # Note: OpenPyXL borders are complex. We'll simplify: check each side. border_color = (200, 200, 200) # Default gridline color if cell.border: # Helper to draw a side def draw_side(side_obj, coords): if side_obj and side_obj.style: # Parse color if available, else default black for set borders b_color = (0, 0, 0) if side_obj.color: b_color = self._parse_color(side_obj.color, default=(0, 0, 0)) # Determine width based on style # 'thin', 'medium', 'thick', 'double', 'hair', 'dashed', 'dotted' width = default_border_width if side_obj.style in ['medium', 'thick', 'double']: width = default_border_width * 2 draw.line(coords, fill=b_color, width=width) else: # Draw default gridline draw.line(coords, fill=border_color, width=default_border_width) draw_side(cell.border.left, [x1, y1, x1, y2]) draw_side(cell.border.right, [x2, y1, x2, y2]) draw_side(cell.border.top, [x1, y1, x2, y1]) draw_side(cell.border.bottom, [x1, y2, x2, y2]) else: # Fallback to simple rectangle outline draw.rectangle([x1, y1, x2, y2], outline=border_color, width=default_border_width) # Content cell_value = cell.value if cell_value is None: return text = self._format_cell_value(cell, cell_value) if not text: return # Font handling is_bold = cell.font and cell.font.bold font_name = cell.font.name # Get font family name # Excel font size is in points. 12 is default. font_size = int(cell.font.sz) if (cell.font and cell.font.sz) else 12 # Scale the font size scaled_font_size = int(font_size * scale) current_font = self._get_font(font_name, is_bold, scaled_font_size) # Font color # Excel's Color object can be complex. We pass the whole object to _parse_color. font_color_obj = cell.font.color if (cell.font and cell.font.color) else None text_color = self._parse_color(font_color_obj, default=(0, 0, 0)) # Alignment h_align = cell.alignment.horizontal if (cell.alignment and cell.alignment.horizontal) else 'left' v_align = cell.alignment.vertical if (cell.alignment and cell.alignment.vertical) else 'center' # Text rendering with simple truncation self._draw_text(draw, text, x1, y1, x2, y2, current_font, text_color, h_align, v_align, scaled_font_size) def _parse_color(self, color_obj, default=(0, 0, 0)) -> Tuple[int, int, int]: """ Parse Excel color object to RGB tuple. """ if not color_obj: return default color_code = None # If it's a string, treat as hex if isinstance(color_obj, str): color_code = color_obj # If it's a Color object elif hasattr(color_obj, 'type'): if color_obj.type == 'rgb': color_code = color_obj.rgb elif color_obj.type == 'theme': # Use hardcoded common theme colors as a fallback for MVP # Theme 0: Light 1 (White) - FFFFFF # Theme 1: Dark 1 (Black) - 000000 # Theme 2: Light 2 (EEECE1) # Theme 3: Dark 2 (1F497D) if color_obj.theme == 0: color_code = "FFFFFFFF" # White elif color_obj.theme == 1: color_code = "FF000000" # Black else: # Attempt to check if rgb is populated even for theme if hasattr(color_obj, 'rgb') and color_obj.rgb: color_code = color_obj.rgb elif hasattr(color_obj, 'rgb'): color_code = color_obj.rgb if not color_code or color_code == '00000000': return default # Handle ARGB if len(color_code) > 6: if color_code.startswith('FF') or len(color_code) == 8: color_code = color_code[2:] try: return tuple(int(color_code[i:i + 2], 16) for i in (0, 2, 4)) except: return default def _format_cell_value(self, cell, value) -> str: if isinstance(value, (int, float)): # Simple number formatting if cell.number_format: if '0.00' in str(cell.number_format): return format(value, '.2f') elif '0.0' in str(cell.number_format): return format(value, '.1f') return str(value) return str(value) def _draw_text(self, draw, text, x1, y1, x2, y2, font, color, h_align, v_align, font_size): # Calculate available width max_width = x2 - x1 - 10 text_width = draw.textlength(text, font=font) # Simple truncation if too long if text_width > max_width and len(text) > 3: # Estimate chars that fit char_ratio = max_width / text_width keep_chars = int(len(text) * char_ratio) - 2 if keep_chars > 0: text = text[:keep_chars] + "..." text_width = draw.textlength(text, font=font) # Re-measure # Horizontal Position if h_align == 'center': text_x = x1 + (x2 - x1 - text_width) / 2 elif h_align == 'right': text_x = x2 - text_width - 5 else: # left text_x = x1 + 5 # Vertical Position (Approximate, using fixed height) # Use font_size as a proxy for height (approximation) font_height = font_size if v_align == 'top': text_y = y1 + 5 elif v_align == 'bottom': text_y = y2 - font_height - 5 else: # center text_y = y1 + (y2 - y1 - font_height) / 2 draw.text((text_x, text_y), text, fill=color, font=font)