| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 |
- import csv
- from urllib.parse import quote
- def create_hyperlink(value, base_url):
- """为给定的值创建亚马逊搜索页面的超链接"""
- return f'=HYPERLINK("{base_url}{quote(value)}&__mk_zh_CN=%E4%BA%9A%E9%A9%BC%E9%80%8A%E7%BD%91%E7%AB%99", "{value}")'
- def process_row(row, indices, base_url):
- """为指定索引的单元格添加超链接"""
- for index in indices:
- if index < len(row): # 确保索引在范围内
- row[index] = create_hyperlink(row[index], base_url)
- 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, columns_to_process, use_letters=False, base_url='', start_row=0):
- """读取CSV文件并处理指定列的数据，然后写入新文件"""
- 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: # 如果使用列字母
- indices = [column_letter_to_index(col) for col in columns_to_process]
- else: # 如果使用列索引（假设从0开始）
- indices = [int(col) for col in columns_to_process]
- # 遍历每一行并处理
- for i, row in enumerate(reader):
- if i < start_row:
- writer.writerow(row) # 直接写入未处理的行
- else:
- processed_row = process_row(row, indices, base_url)
- writer.writerow(processed_row)
- # 定义要处理的CSV文件路径、输出文件路径以及需要添加超链接的列索引或列字母和亚马逊搜索的基础URL
- input_csv_path = 'C:\\Users\\74262\\Desktop\\excel\\18.JP家具.csv'
- output_csv_path = 'C:\\Users\\74262\\Desktop\\excel\\18.JP家具_processed.csv'
- columns_to_process = ['C', 'D', 'E'] # 使用列字母
- 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, columns_to_process, use_letters, amazon_search_base_url, start_row)
|
