from typing import List
import openpyxl
from googletrans import Translator
from pydantic import BaseModel

class Cell(BaseModel):
    value: str
    row: int
    column: int

class Sheet(BaseModel):
    name: str
    cells: List[Cell]

class ExcelTranslator:
    def __init__(self, src_language: str, dest_language: str):
        self.translator = Translator()
        self.src_language = src_language
        self.dest_language = dest_language

    def translate_text(self, text: str) -> str:
        translated = self.translator.translate(text, src=self.src_language, dest=self.dest_language)
        return translated.text

    def translate_sheet(self, sheet: Sheet) -> Sheet:
        translated_cells = []
        for cell in sheet.cells:
            translated_value = self.translate_text(cell.value)
            translated_cells.append(Cell(value=translated_value, row=cell.row, column=cell.column))
        return Sheet(name=sheet.name, cells=translated_cells)

    def translate_workbook(self, file_path: str) -> None:
        workbook = openpyxl.load_workbook(file_path)
        translated_workbook = openpyxl.Workbook()

        for sheet_name in workbook.sheetnames:
            sheet = workbook[sheet_name]
            translated_sheet = translated_workbook.create_sheet(title=sheet_name)

            for row in sheet.iter_rows():
                for cell in row:
                    translated_value = self.translate_text(cell.value) if cell.value else ''
                    translated_sheet[cell.coordinate].value = translated_value
                    # Preserve cell formatting
                    if cell.has_style:
                        translated_sheet[cell.coordinate]._style = cell._style

        translated_file_path = file_path.replace('.xlsx', '_translated.xlsx')
        translated_workbook.save(translated_file_path)