Python XmlTool

 XML To Xlsx

import xml.etree.ElementTree as ET
import re

import pandas as pd


def main():
    tree = ET.parse("in/strings.xml")
    root = tree.getroot()
    patternKey = re.compile(r"\: \'(.+?)\'")
    data_list = []
    for child in root:
        data = ["", ""]
        attrib = str(child.attrib)
        valueStr = child.text
        keyStr = patternKey.search(attrib).group(1)
        data[0] = keyStr
        data[1] = valueStr
        data_list.append(data)
    # 数据读出
    data = pd.DataFrame(data_list)
    # 保存为xls格式
    data.to_excel('dataZN.xls', encoding='utf-8')


class Bean:
    def __init__(self, key, value):
        self.key = key
        self.value = value


if __name__ == "__main__":
    main()

 

Xlsx To XML

import xlrd
import json
import pymysql


def main():
    # list = {'cs', 'de', 'en', 'es', 'fr', 'it', 'ja', 'ko', 'pl', 'pt', 'ru', 'uk', 'zh'}
    list = {'pl'}
    list = {'nl','sv'}
    returnData = {}
    db = pymysql.connect(host="localhost", user="root", password="root", db="oclean")
    cursor = db.cursor()
    for item in list:
        print("item = " + item)
        # 读取Excel文件
        data = xlrd.open_workbook('in/oclean_t_language_hl.xls')
        table = data.sheets()[0]
        # 读取excel第一行数据
        rowstag = table.row_values(0)
        nrows = table.nrows
        f = open("./out/string_" + item + ".xml", "w", encoding='utf8')
        f2 = open("./out/string_error_" + item + ".xml", "w", encoding='utf8')
        # f.write("<resources>\n")
        for i in range(1, nrows):
            # 将字段名和excel数据存储为字典形式，并转换为json格式
            returnData[i] = json.dumps(dict(zip(rowstag, table.row_values(i))))
            # 通过编解码还原数据
            returnData[i] = json.loads(returnData[i])
            valueStr = returnData[i][item]
            zh = returnData[i]["zh"]
            sql = "SELECT keyId FROM t_language where zh= '%s' " % zh
            cursor.execute(sql)
            results = cursor.fetchall()
            if len(results) == 0:
                # 没有匹配上
                print("results = " )
                f2.write("<string name='" + str(zh + "'>" + str(valueStr.strip() + "</string>\n")))
            for row in results:
                print(row)
                print("zh = " + zh)
                print("valueStr = " + valueStr)
                f.write("<string name='" + str(row[0] + "'>" + str(valueStr.strip() + "</string>\n")))
        f.close()
        f2.close()
    print("结束")


if __name__ == "__main__":
    main()

  XML To DB

import xml.etree.ElementTree as ET
import re
import pymysql
import os
from pymysql.converters import escape_string


def main():
    db = pymysql.connect(host="xx.xx.xx.xx", user="xxx", password="xxx", db="YourDbName")
    cursor = db.cursor(B
    path = 'D:\\Codes\\rc\\main\\res\\'
    sql = 'truncate table t_language'
    cursor.execute(sql)
    db.commit()
    for file_name in os.listdir(path):
        if "values-" not in file_name:
            continue
        if "night" in file_name:
            continue
        print(">>> " + file_name)
        item = str(file_name.replace('values-', '').replace('-', '_'))
        tree = ET.parse(path + file_name + "/strings.xml")
        root = tree.getroot()
        patternKey = re.compile(r"\: \'(.+?)\'")

        for child in root:
            attrib = str(child.attrib)
            valueStr = child.text
            keyStr = patternKey.search(attrib).group(1)
            # print("keyStr ='%s' " % keyStr)
            try:
                sql = "SELECT * FROM t_language WHERE keyId = '%s'" % keyStr
                cursor.execute(sql)
                results = cursor.fetchone()
                if results:
                    # print("更新数据" + keyStr)
                    sql = "UPDATE t_language SET %s = '%s' WHERE keyId ='%s'" % (item, escape_string(valueStr), keyStr)
                    # print(sql)
                    cursor.execute(sql)
                    db.commit()
                else:
                    # print("插入数据")
                    sql = "INSERT INTO t_language(keyId,%s) VALUES ('%s', '%s')" % (
                        item, keyStr, escape_string(valueStr))
                    # print(sql)
                    cursor.execute(sql)
                    db.commit()
            except Exception as e:
                print("出现如下异常'%s'" % e)
                return
                # 关闭数据库连接
    db.close()
    print("结束")


if __name__ == "__main__":
    main()