Python(十七)excel指定列自动翻译成英文

前言

本章主要讲述在excel的指定列后面添加一列，并翻译成英文

一、效果图

二、代码

实际需求：

# -*- codeing = utf-8 -*-
# @time: 2025/1/16  16:32
# @Author : Mikasa
#
# @Aim：自动将客户发的货物清单里的商品名称，翻译成英文；
#       并在当前excel中，品名后面加入一列，并将翻译后的英文写入到excel里from translate import Translator
import pandas as pdfile_path = "./translate_packaging_list/test.xlsx"
sheet_name = "Sheet1"
column_name = "品名"
new_file_path = "./translate_packaging_list/test_translate.xlsx"def extract_column_as_array(file_path, sheet_name, column_name):"""获取excel某列数据，并返回一个list:param file_path::param sheet_name::param column_name::return: 去掉空行的lsit"""df = pd.read_excel(file_path, sheet_name=sheet_name)column_data = df[column_name].tolist()return column_datadef translate_text(text, src='zh-cn', dest='en'):"""将中文翻译成英文:param text::param src::param dest::return:"""translator = Translator(from_lang=src, to_lang=dest)translation = translator.translate(text)return translationdef translate_packaging_list():"""1、翻译后的值生成一个list2、在excel【品名】后加入一列【goods】列3、将翻译后的list写入到该列:return:"""result_array = extract_column_as_array(file_path, sheet_name, column_name)  # 获取列表df = pd.read_excel(file_path, sheet_name=sheet_name)new_column_name = "goods"new_loc = df.columns.get_loc('品名') + 1 #获取指定列后面的坐标english_cargo_name_list = []for good_name in result_array:if type(good_name) == str:translated_text = translate_text(good_name)print(f"{good_name} -> {translated_text}")english_cargo_name_list.append(translated_text)else:english_cargo_name_list.append(good_name)print("english_cargo_name_list:", english_cargo_name_list)print("new_loc:", new_loc)df.insert(loc=new_loc, column=new_column_name, value=english_cargo_name_list)df.to_excel(new_file_path, index=False)if __name__ == '__main__':translate_packaging_list()