def prettify_xml(elem):
    """格式化 XML 輸出"""
    rough_string = ET.tostring(elem, "utf-8")
    reparsed = minidom.parseString(rough_string)
    return reparsed.toprettyxml(indent="    ")

def main():
    # 1. 打開(kāi) Excel 文件
    excel_file_path = r"C:\Projects\Translation\test_data.xlsx"
    wb = xw.Book(excel_file_path)
    # 2. 選擇指定工作表
    try:
        sheet = wb.sheets["TranslationData"]
    except KeyError:
        print("? 錯(cuò)誤：指定工作表不存在！")
        wb.close()
        return
    # 3. 定義列映射關(guān)系
    column_mapping = {
        'full_name': 'B',    # 標(biāo)簽名稱(chēng)
        'categories': 'F',   # 分類(lèi)信息
        'short_desc': 'G',   # 簡(jiǎn)短描述
        'value': 'C'         # 實(shí)際值
    }
    # 4. 構(gòu)建 XML 根元素
    root = ET.Element("CustomLabels", 
                     xmlns="http://soap.sforce.com/2006/04/metadata")
    # 5. 遍歷數(shù)據(jù)行
    row_index = 2  # 從第二行開(kāi)始（跳過(guò)標(biāo)題行）
    processed_count = 0
    
    while True:
        full_name = sheet.range(f"{column_mapping['full_name']}{row_index}").value
        
        # 檢查是否到達(dá)數(shù)據(jù)末尾
        if full_name is None:
            break
        # 讀取其他字段數(shù)據(jù)
        categories = sheet.range(f"{column_mapping['categories']}{row_index}").value
        short_desc = sheet.range(f"{column_mapping['short_desc']}{row_index}").value
        value = sheet.range(f"{column_mapping['value']}{row_index}").value
        # 創(chuàng)建 XML 標(biāo)簽元素
        label_elem = ET.SubElement(root, "labels")
        ET.SubElement(label_elem, "fullName").text = str(full_name)
        ET.SubElement(label_elem, "categories").text = str(categories)
        ET.SubElement(label_elem, "language").text = "en_US"
        ET.SubElement(label_elem, "protected").text = "false"
        ET.SubElement(label_elem, "shortDescription").text = str(short_desc)
        ET.SubElement(label_elem, "value").text = str(value)
        row_index += 1
        processed_count += 1
    # 6. 生成并保存 XML
    xml_str = prettify_xml(root)
    
    output_xml_path = "output_custom_labels.xml"
    with open(output_xml_path, "w", encoding="utf-8") as f:
        f.write(xml_str)
    print(f"? 處理完成！共轉(zhuǎn)換 {processed_count} 條記錄")
    print(f"? XML 文件已生成：{output_xml_path}")
    
    wb.close()

<?xml version="1.0" encoding="utf-8"?>
<CustomLabels xmlns="http://soap.sforce.com/2006/04/metadata">
    <labels>
        <fullName>Welcome_Message</fullName>
        <categories>UI_Labels</categories>
        <language>en_US</language>
        <protected>false</protected>
        <shortDescription>歡迎消息</shortDescription>
        <value>歡迎使用系統(tǒng)</value>
    </labels>
    <labels>
        <fullName>Error_404</fullName>
        <categories>Error_Messages</categories>
        <language>en_US</language>
        <protected>false</protected>
        <shortDescription>404錯(cuò)誤提示</shortDescription>
        <value>頁(yè)面不存在</value>
    </labels>
</CustomLabels>

<PYTHON>
# 添加自定義字段示例
ET.SubElement(label_elem, "customField").text = "custom_value"

import logging
logging.basicConfig(level=logging.INFO, 
                   format='%(asctime)s - %(levelname)s - %(message)s')

# 檢測(cè)并處理不同的文件編碼
def detect_encoding(file_path):
    import chardet
    with open(file_path, 'rb') as f:
        result = chardet.detect(f.read())
    return result['encoding']

def process_multiple_files(input_folder, output_folder):
    # 處理整個(gè)文件夾中的Excel文件
    pass