import wx
import pandas as pd
import os
from googletrans import Translator

class ExcelTransposer(wx.Frame):
    def __init__(self, *args, **kwargs):
        super(ExcelTransposer, self).__init__(*args, **kwargs)
        
        # Set up the frame
        self.InitUI()
        self.translator = Translator()  # 初始化翻譯器
        
    def InitUI(self):
        panel = wx.Panel(self)
        vbox = wx.BoxSizer(wx.VERTICAL)
        
        # File selection
        file_box = wx.BoxSizer(wx.HORIZONTAL)
        self.file_path = wx.TextCtrl(panel)
        file_btn = wx.Button(panel, label="選擇xlsx文件")
        file_btn.Bind(wx.EVT_BUTTON, self.OnSelectFile)
        file_box.Add(self.file_path, proportion=1, flag=wx.EXPAND|wx.ALL, border=5)
        file_box.Add(file_btn, flag=wx.ALL, border=5)
        
        # Output folder selection
        save_box = wx.BoxSizer(wx.HORIZONTAL)
        self.save_path = wx.TextCtrl(panel)
        save_btn = wx.Button(panel, label="選擇保存路徑")
        save_btn.Bind(wx.EVT_BUTTON, self.OnSelectSavePath)
        save_box.Add(self.save_path, proportion=1, flag=wx.EXPAND|wx.ALL, border=5)
        save_box.Add(save_btn, flag=wx.ALL, border=5)
        
        # Convert button
        convert_btn = wx.Button(panel, label="轉(zhuǎn)換并翻譯")
        convert_btn.Bind(wx.EVT_BUTTON, self.OnConvert)
        
        # Add to vbox
        vbox.Add(file_box, flag=wx.EXPAND|wx.ALL, border=5)
        vbox.Add(save_box, flag=wx.EXPAND|wx.ALL, border=5)
        vbox.Add(convert_btn, flag=wx.ALIGN_CENTER|wx.ALL, border=10)
        
        panel.SetSizer(vbox)
        
        self.SetTitle("Excel Sheet 轉(zhuǎn)置和翻譯器")
        self.SetSize((400, 200))
        self.Centre()
        
    def OnSelectFile(self, event):
        with wx.FileDialog(self, "選擇 Excel 文件", wildcard="Excel files (*.xlsx)|*.xlsx",
                           style=wx.FD_OPEN | wx.FD_FILE_MUST_EXIST) as fileDialog:
            if fileDialog.ShowModal() == wx.ID_CANCEL:
                return
            path = fileDialog.GetPath()
            self.file_path.SetValue(path)
    
    def OnSelectSavePath(self, event):
        with wx.DirDialog(self, "選擇保存文件夾",
                          style=wx.DD_DEFAULT_STYLE) as dirDialog:
            if dirDialog.ShowModal() == wx.ID_CANCEL:
                return
            path = dirDialog.GetPath()
            self.save_path.SetValue(path)
    
    def OnConvert(self, event):
        excel_path = self.file_path.GetValue()
        save_folder = self.save_path.GetValue()
        
        if not excel_path or not save_folder:
            wx.MessageBox("請(qǐng)選擇文件和保存路徑", "錯(cuò)誤", wx.ICON_ERROR)
            return
        
        try:
            # Load the Excel file
            xlsx = pd.ExcelFile(excel_path)
            
            for sheet_name in xlsx.sheet_names:
                # Read each sheet
                df = xlsx.parse(sheet_name)
                
                if df.shape[0] < 2:
                    wx.MessageBox(f"Sheet {sheet_name} 數(shù)據(jù)不足", "警告", wx.ICON_WARNING)
                    continue
                
                # Transpose first column starting from second row (ignoring first row)
                transposed_data = df.iloc[1:, 0].values.T
                
                # Translate the English text to Chinese
                translated_data = [self.translator.translate(text, src='en', dest='zh-cn').text for text in transposed_data]
                
                # Convert translated data to DataFrame
                translated_df = pd.DataFrame([translated_data])
                
                # Save to a new Excel file
                output_file = os.path.join(save_folder, f"{sheet_name}_translated.xlsx")
                with pd.ExcelWriter(output_file) as writer:
                    translated_df.to_excel(writer, index=False, header=False, sheet_name=sheet_name)
                
            wx.MessageBox("轉(zhuǎn)換并翻譯完成", "成功", wx.ICON_INFORMATION)
        
        except Exception as e:
            wx.MessageBox(f"出現(xiàn)錯(cuò)誤: {e}", "錯(cuò)誤", wx.ICON_ERROR)


if __name__ == '__main__':
    app = wx.App()
    frame = ExcelTransposer(None)
    frame.Show()
    app.MainLoop()

import wx
import pandas as pd
import os
from googletrans import Translator

class ExcelTransposer(wx.Frame):
    # ... (代碼略) ...

pip install wxPython pandas googletrans==4.0.0-rc1