excel_tool/brand_add_url_link.py

import csv
from urllib.parse import quote
from pathlib import Path

def create_hyperlink(value, base_url):
    """为给定的值创建亚马逊搜索页面的超链接"""
    return f'=HYPERLINK("{base_url}{quote(value)}&__mk_zh_CN=%E4%BA%9A%E9%A9%AC%E9%80%8A%E7%BD%91%E7%AB%99", "{value}")'

def create_asin_link(asin):
    """为ASIN创建产品详情页链接"""
    return f'=HYPERLINK("https://www.amazon.co.jp/dp/{asin}", "{asin}")'

def process_row(row, brand_indices, asin_indices, base_url):
    """为指定索引的单元格添加超链接"""
    # 处理品牌列
    for index in brand_indices:
        if index < len(row):  # 确保索引在范围内
            row[index] = create_hyperlink(row[index], base_url)
    
    # 处理ASIN列
    for index in asin_indices:
        if index < len(row) and row[index]:  # 确保索引在范围内且值不为空
            row[index] = create_asin_link(row[index])
    
    return row

def column_letter_to_index(col_letter):
    """将Excel列字母转换为0基索引"""
    return sum((ord(c) - ord('A') + 1) * (26 ** i) for i, c in enumerate(reversed(col_letter.upper()))) - 1

def read_and_process_csv(input_file, output_file, brand_columns, asin_columns, use_letters=False, base_url='', start_row=0):
    """读取CSV文件并处理指定列的数据，然后写入新文件"""
    # 确保输出目录存在
    output_dir = Path(output_file).parent
    output_dir.mkdir(parents=True, exist_ok=True)
    
    with open(input_file, 'r', encoding='utf-8', errors='ignore') as csvfile, \
            open(output_file, 'w', newline='', encoding='utf-8') as new_csvfile:

        reader = csv.reader(csvfile)
        writer = csv.writer(new_csvfile)
        headers = next(reader)  # 读取标题行

        # 写入标题行到新的CSV文件
        writer.writerow(headers)

        # 获取要处理的列的索引
        if use_letters:  # 如果使用列字母
            brand_indices = [column_letter_to_index(col) for col in brand_columns]
            asin_indices = [column_letter_to_index(col) for col in asin_columns]
        else:  # 如果使用列索引（假设从0开始）
            brand_indices = [int(col) for col in brand_columns]
            asin_indices = [int(col) for col in asin_columns]

        # 遍历每一行并处理
        for i, row in enumerate(reader):
            if i < start_row:
                writer.writerow(row)  # 直接写入未处理的行
            else:
                processed_row = process_row(row, brand_indices, asin_indices, base_url)
                writer.writerow(processed_row)

# 定义要处理的CSV文件路径、输出文件路径以及需要添加超链接的列索引或列字母和亚马逊搜索的基础URL
output_dir = Path('temp')
input_csv_path = output_dir / '测试_utf8.csv'
output_csv_path = output_dir / '测试_processed.csv'
brand_columns = ['C', 'D', 'E']  # 品牌列
asin_columns = ['H', 'L', 'P']   # ASIN列
use_letters = True  # 是否使用列字母代替索引
amazon_search_base_url = 'https://www.amazon.co.jp/s?k='
start_row = 1  # 从第2行开始处理（索引从0开始）

# 执行CSV处理
read_and_process_csv(input_csv_path, output_csv_path, brand_columns, asin_columns, use_letters, amazon_search_base_url, start_row)