import pandas as pd

# 定义文件路径和标题行位置
INPUT_FILE_PATH = 'C:\\Users\\74262\\Desktop\\excel\\18.JP家具.csv'  # Replace with your actual file path
OUTPUT_FILE_PATH = 'C:\\Users\\74262\\Desktop\\excel\\18.JP家具_processed.csv'  # Replace with your desired output file path
HEADER_ROW = 1  # 标题行位置，默认为第一行（索引从0开始）


def load_data(file_path, header_row):
    """加载数据表，并根据指定行作为标题"""
    try:
        if file_path.endswith('.csv'):
            df = pd.read_csv(file_path, header=header_row)
        elif file_path.endswith(('.xls', '.xlsx')):
            df = pd.read_excel(file_path, header=header_row)
        else:
            raise ValueError("Unsupported file format.")
        return df
    except Exception as e:
        print(f"Failed to load data from {file_path}. Error: {e}")
        return None


def find_keyword_columns(df, keyword):
    """查找包含关键词的列"""
    keyword_columns = [col for col in df.columns if keyword.lower() in str(col).lower()]
    return keyword_columns


def mock_translate(text):
    """模拟翻译函数，返回测试文本"""
    return f"{text} (translated)"


def translate_column(df, column_name):
    """翻译指定列的内容"""
    df[column_name] = df[column_name].apply(lambda x: mock_translate(x) if isinstance(x, str) else x)
    return df


def process_and_save(df, output_file_path):
    """处理并保存数据到新文件"""
    try:
        if output_file_path.endswith('.csv'):
            df.to_csv(output_file_path, index=False)
        elif output_file_path.endswith(('.xls', '.xlsx')):
            df.to_excel(output_file_path, index=False)
        else:
            raise ValueError("Unsupported file format for saving.")
        print(f"Translated table saved to {output_file_path}")
    except Exception as e:
        print(f"Failed to save data to {output_file_path}. Error: {e}")


def main():
    # 加载数据
    df = load_data(INPUT_FILE_PATH, HEADER_ROW)
    if df is None:
        return

    # 查找包含关键词的列
    keyword = "类别"  # 替换为实际的关键词
    keyword_columns = find_keyword_columns(df, keyword)

    if not keyword_columns:
        print("No columns found with the specified keyword.")
        return

    print(f"Found columns: {keyword_columns}")

    # 翻译找到的列
    for column in keyword_columns:
        print(f"Translating column: {column}")
        df = translate_column(df, column)

    # 保存处理后的数据到新的文件
    process_and_save(df, OUTPUT_FILE_PATH)


if __name__ == "__main__":
    main()