excel_tool/process_data.py

import csv
import chardet
import sys
import logging
from pathlib import Path
from mylib.new_col_translate import insert_empty_columns, process_batch_translations
from brand_add_url_link import create_hyperlink, create_asin_link
from mylib.logging_config import setup_logging

# Setup custom logging
setup_logging()
logger = logging.getLogger(__name__)


def add_brand_asin_links(data, brand_indices, asin_indices):
    """为品牌和ASIN列添加链接"""
    try:
        for row in data[2:]:  # 从第三行开始处理
            # 处理品牌列
            for index in brand_indices:
                if index < len(row) and row[index]:
                    row[index] = create_hyperlink(row[index], 'https://www.amazon.co.jp/s?k=')
            
            # 处理ASIN列
            for index in asin_indices:
                if index < len(row) and row[index]:
                    row[index] = create_asin_link(row[index])
        return data
    except Exception as e:
        logger.error(f"Error adding brand/ASIN links: {e}")
        sys.exit(1)

def save_csv(data, file_path):
    try:
        with open(file_path, 'w', encoding='utf-8-sig', newline='') as f:
            writer = csv.writer(f)
            writer.writerows(data)
    except Exception as e:
        logger.error(f"Error saving CSV to {file_path}: {e}")
        sys.exit(1)

def main(input_file, output_file):
    try:
        # Read CSV with proper encoding
        data = read_csv(input_file)
        
        # 定义需要处理的列索引
        search_term_index = 1  # 搜索词列
        brand_indices = [2, 3, 4]  # 品牌列
        asin_indices = [7, 11, 15]  # ASIN列
        category_indices = [5, 6, 7]  # 类别列
        
        # 插入空列用于翻译
        insert_indices = [search_term_index] + category_indices
        data = insert_empty_columns(data, insert_indices)
        
        # 更新标题行
        data[0][search_term_index + 1] = "中文翻译"
        for index in category_indices:
            data[0].insert(index + 1, "中文翻译")
        
        # 处理翻译
        data = process_batch_translations(data, search_term_index, category_indices)
        
        # 添加品牌和ASIN链接
        data = add_brand_asin_links(data, brand_indices, asin_indices)
        
        # 保存处理后的数据
        save_csv(data, output_file)
        logger.info(f"Successfully processed and saved to {output_file}")
        
    except Exception as e:
        logger.error(f"Error processing file: {e}")
        sys.exit(1)

if __name__ == "__main__":
    output_dir = Path('temp')
    input_file = output_dir/"测试.csv"
    output_file = output_dir/"processed_测试.csv"
    main(input_file, output_file)