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

前言

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

一、效果图

二、代码

实际需求：

# -*- codeing = utf-8 -*-
# @time: 2025/1/16  16:32
# @Author : Mikasa
#
# @Aim：自动将客户发的货物清单里的商品名称，翻译成英文；
#       并在当前excel中，品名后面加入一列，并将翻译后的英文写入到excel里

from translate import Translator
import pandas as pd

file_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_data


def 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 translation


def translate_packaging_list():
    """
    1、翻译后的值生成一个list
    2、在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()