GHZMRSpaceXR/Assets/Studio/Language/ExcelTool/Editor/ExcelToXml.cs

using Excel;
using PublicTools.XMLDataBase;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using UnityEditor;
using UnityEngine;
namespace XRTool.Util
{
    /// <summary>
    /// Execl转XML对象文件
    /// </summary>
    public class ExcelToXml : EditorWindow
    {
        /// <summary>
        /// excel文件路径
        /// </summary>
        private string excelPath = "External/Studio2配置表.xlsx";
        /// <summary>
        /// 输出路径
        /// </summary>
        //private string outPath;
        /// <summary>
        /// 转化的对象名称
        /// </summary>
        //private string objectName;
        /// <summary>
        /// 转化规则
        /// 键值对分别采用','和'|'分割
        /// 例如  1,PriKey|3,Message
        /// </summary>
        private string rule = "0,PriKey|2,Message|3,AudioPath";
        private int startRow = 1;
        private bool isReadEnd;
        private int endRow = int.MaxValue;
        /// <summary>
        /// 强制更新
        /// </summary>
        private bool isForeceUpdate;

        [MenuItem("XRTools/ExcelToXML")]
        static void ExcelToXML()
        {
            if (BuildConfigMgr.Instance.IsInit)
            {
                if (BuildConfig.Instance)
                {
                    ExcelToXml bw = GetWindow(typeof(ExcelToXml), false, "Excel转XML", true) as ExcelToXml;
                    bw.ShowPopup();
                    bw.autoRepaintOnSceneChange = true;
                }
            }
        }

        /// <summary>
        /// 转换器
        /// </summary>
        private void OnGUI()
        {
            if (!BuildConfigMgr.Instance.IsInit)
            {
                return;
            }
            ///Excel的路径
            excelPath = EditorGUILayout.TextField("Excel路径", excelPath);
            ///输出路径
            BuildConfig.Instance.languagePath = EditorGUILayout.TextField("输出路径",
                BuildConfig.Instance.languagePath);

            //objectName = EditorGUILayout.TextField("转化对象", objectName);

            //targetObj = EditorGUILayout.ObjectField("转化对象", targetObj, typeof(UnityEngine.Object), null) as UnityEngine.Object;

            rule = EditorGUILayout.TextField("转化规则", rule);
            startRow = EditorGUILayout.IntField("起始行", startRow);
            isReadEnd = EditorGUILayout.BeginToggleGroup("启用终止行", isReadEnd);
            endRow = EditorGUILayout.IntField("终止行", endRow);
            EditorGUILayout.EndToggleGroup();
            BuildConfig.Instance.prefix = EditorGUILayout.TextField("主键前缀", BuildConfig.Instance.prefix);

            isForeceUpdate = EditorGUILayout.ToggleLeft("强制刷新", isForeceUpdate);
            ///刷新指定路径下的所有资源的标签
            if (GUILayout.Button("转换", GUILayout.Width(100)))
            {
                ConversionToXML();
            }
        }

        /// <summary>
        /// 开始转换
        /// </summary>
        public void ConversionToXML()
        {
            string path = Application.dataPath;
            path = path.Substring(0, path.Length - ("Assets".Length + 1));
            path = path + "/" + excelPath;
            if (!File.Exists(path))
            {
                UnityLog.Instance.LogError(path + " is null");
                return;
            }

            ///打开或者创建Excel
            FileStream stream = File.Open(path, FileMode.OpenOrCreate);

            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

            DataSet dataSet = excelReader.AsDataSet();
            stream.Close();
            excelReader.Close();
            if (dataSet == null)
            {
                UnityLog.Instance.LogError(path + " 读取失败！");
                stream.Close();
                excelReader.Close();
                return;
            }
            ///获取列
            DataColumnCollection columns = dataSet.Tables[0].Columns;
            ///获取行
            DataRowCollection rows = dataSet.Tables[0].Rows;

            ///解析出对应的元素，元素包含要解析的列和对应的映射
            List<string> elementList = new List<string>(rule.Split('|'));
            Dictionary<int, string> elementHash = new Dictionary<int, string>();
            for (int i = 0; i < elementList.Count; i++)
            {
                ///对应的列和映射的key
                string[] keys = elementList[i].Split(',');

                elementHash.Add(int.Parse(keys[0]), keys[1]);
            }
            foreach (var item in elementHash)
            {
                UnityLog.Instance.Log(item.Key + "" + item.Value);
            }

            ///每一行取指定列
            int maxRow = isReadEnd && endRow < rows.Count ? endRow : rows.Count;
            //List<Dictionary<string, string>> excelData = new List<Dictionary<string, string>>();


            string realDataPath = Path.Combine(Application.streamingAssetsPath, BuildConfig.Instance.languagePath);
            if (!Directory.Exists(realDataPath))
            {
                Directory.CreateDirectory(realDataPath);
            }
            //return;
            //MySql.WorkPath = realDataPath;
            try
            {
                //bool isOpen = MySql.Open<LanguageConf>(true);
                if (true)
                {
                    //Debug.Log( dataSet.Tables[0].Rows[0][elementHash[0]].ToString());
                    string languagePack = dataSet.Tables[0].Rows[0][elementHash.Keys.ToArray()[1]].ToString();
                    LanguagePackConf conf = AutoAddPack(realDataPath, languagePack);
                    if (conf == null)
                    {
                        UnityLog.Instance.LogError("转换语言包失败" + languagePack);
                        return;
                    }
                    string tableName = typeof(LanguageConf).Name;
                    XSql.Instance.CloseTable(tableName);
                    var table = XSql.Instance.OpenTable(realDataPath + "/" + conf.PackagePath, tableName, ".xml", true);
                    if (!table.Open())
                    {
                        table.Create(tableName);
                    }

                    for (int i = startRow; i < maxRow; i++)
                    {
                        ///以行为单位开始遍历取值
                        Dictionary<string, string> valuePairs = new Dictionary<string, string>();
                        foreach (var item in elementHash)
                        {
                            //Debug.Log(item.Value + " -- " + dataSet.Tables[0].Rows[i][item.Key].ToString());
                            string keyValue = dataSet.Tables[0].Rows[i][item.Key].ToString();
                            if (string.IsNullOrEmpty(keyValue))
                            {
                                break;
                            }
                            if (item.Value == "PriKey")
                            {
                                int num = -1;
                                if (int.TryParse(keyValue, out num))
                                {
                                    keyValue = BuildConfig.Instance.prefix + keyValue;
                                }
                            }
                            valuePairs.Add(item.Value, keyValue);
                        }

                        //excelData.Add(valuePairs);

                        LanguageConf t = ClazzFactory.ClazzPack<LanguageConf>(valuePairs);
                        if (t == null)
                        {
                            UnityLog.Instance.LogError(i + " cant insert!");
                            continue;
                        }
                        table.InsertData(t);
                    }
                    table.Close();
                }
                else
                {
                    Debug.LogError("Error open " + realDataPath);
                }
            }
            catch (Exception ex)
            {
                Debug.LogError(ex.ToString());
            }

            //MySql.Close();
            AssetDatabase.Refresh();
        }

        public LanguagePackConf AutoAddPack(string path, string key)
        {
            string[] msg = key.Split('/');
            string tableName = typeof(LanguagePackConf).Name;
            var table = XSql.Instance.OpenTable(path, tableName, ".xml", true);
            if (!table.Open())
            {
                table.Create(tableName);
            }
            LanguagePackConf pack = table.FindData<LanguagePackConf>(key);
            if (pack == null)
            {
                pack = new LanguagePackConf();
                pack.Language = msg[0];
                pack.PackagePath = msg[1];
                table.InsertData(pack);
            }
            else
            {
                table.UpdateData(pack);
            }
            table.Save();
            return pack;
        }
    }
}