Excel转Xml

添加的方法

在DataEditor中添加如下方法

    [MenuItem("Tools/Xml/所有Excel转Xml")]
    public static void AllExcelToXml()
    {
        string[] regPaths = Directory.GetFiles(RegPath, "*", SearchOption.AllDirectories);

        for (int i = 0; i < regPaths.Length; i++)
        {
            if (Path.GetExtension(regPaths[i]) != ".xml")
            {
                continue;
            }
            EditorUtility.DisplayProgressBar("查找文件夹下的Reg", regPaths[i], 1.0f / regPaths.Length * i);
            string regName = Path.GetFileNameWithoutExtension(regPaths[i]);
            ExcelToXml(regName);
        }
        AssetDatabase.Refresh();
        EditorUtility.ClearProgressBar();
    }
    private static void ExcelToXml(string name)
    {
        string className = string.Empty;
        string excelName = string.Empty;
        string xmlName = string.Empty;
        //第一步，读取Reg文件，确定类的结构
        Dictionary<string, SheetClass> allSheetClassDic = ReadRegToDic(name, ref excelName, ref xmlName, ref className);
        //第二步，读取Excel数据
        string excelPath = ExcelPath + excelName;
        Dictionary<string,SheetData> sheetDataDic = new Dictionary<string,SheetData>();
        try
        {
            using(FileStream fs = new FileStream(excelPath,FileMode.Open,FileAccess.Read,FileShare.ReadWrite))
            {
                using(ExcelPackage package = new ExcelPackage(fs))
                {
                    ExcelWorksheets worksheetArray = package.Workbook.Worksheets;
                    for (int i = 0; i < worksheetArray.Count; i++)
                    {
                        SheetData sheetData = new SheetData();
                        ExcelWorksheet worksheet = worksheetArray[i + 1];//从1开始
                        SheetClass sheetClass = allSheetClassDic[worksheet.Name];
                        int colCount = worksheet.Dimension.End.Column;
                        int rowCount = worksheet.Dimension.End.Row;
                        for (int n = 0; n < sheetClass.VarList.Count; n++)
                        {
                            sheetData.AllColName.Add(sheetClass.VarList[n].Name);//在xml转Excel中，这里存的是Col名字，注意这里使用变量名
                            sheetData.AllType.Add(sheetClass.VarList[n].Type);
                        }
                        for (int m = 1; m < rowCount; m++)
                        {
                            RowData rowData = new();
                            int n = 0;
                            if (string.IsNullOrEmpty(sheetClass.Split) && 
                                sheetClass.ParentVar != null &&
                                !string.IsNullOrEmpty(sheetClass.ParentVar.Foreign))
                            {
                                rowData.ParentValue = worksheet.Cells[m+1,1].Value.ToString().Trim();
                                n = 1;//从下一列开始才一一对应
                            }
                            for (; n < colCount; n++)//注意把n提出来的写法，保留分号
                            {
                                ExcelRange range = worksheet.Cells[m+1, n+1];
                                //按照Col名获取变量名，再将变量名作为key值放进rowdata里
                                //这样做是因为varList中的变量不一定都会在当前的worksheet中，比如说list类型的变量。
                                string rangeValue = "";
                                if(range.Value != null)
                                {
                                    rangeValue = range.Value.ToString().Trim().Replace(" ", "");//去除左右的空格和内部的空格,不使用正则表达式是因为它会去除“\r\n”
                                }
                                string colName = worksheet.Cells[1,n+1].Value.ToString().Trim();
                                string varName = GetNameFormCol(sheetClass.VarList, colName);
                                rowData.RowDataDic.Add(varName,rangeValue);

                            }
                            sheetData.AllRowData.Add(rowData);
                        }
                        sheetDataDic.Add(worksheet.Name, sheetData);
                    }
                } 
            }
        }
        catch (Exception e)
        {

            Debug.LogError(e.Message);
            return;
        }
        //第三步，根据类的结构，反射创建类，并且赋值
        object objClass = CreateClassInstance(className);

        List<string> outKeyList = new List<string>();//获取最外层的List
        foreach (string sheetName in allSheetClassDic.Keys)
        {
            SheetClass sheetClass = allSheetClassDic[sheetName];
            if(sheetClass.Depth == 1)
            {
                outKeyList.Add(sheetName);
            }
        }
        for (int i = 0; i < outKeyList.Count; i++)
        {
            ReadDataToClass(objClass, allSheetClassDic[outKeyList[i]], sheetDataDic[outKeyList[i]], allSheetClassDic, sheetDataDic, null);
        }
        //第四步将类序列化为xml
        BinarySerializeOpt.XmlSerialize(XmlPath + xmlName, objClass);
        Debug.Log(excelName + " 表导入unity完成");
        AssetDatabase.Refresh();
    }

/// <summary>
    /// 根据列名获取变量名
    /// </summary>
    /// <param name="varList">变量列表</param>
    /// <param name="col">列名</param>
    /// <returns>变量名</returns>
    private static string GetNameFormCol(List<VarClass> varList,string col)
    {
        foreach (VarClass varClass in varList)
        {
            if (varClass.Col == col)
            {
                return varClass.Name;
            }
        }
        return null;
    }

    private static void ReadDataToClass(object objClass, SheetClass sheetClass, SheetData sheetData, Dictionary<string, SheetClass> sheetClassDic, Dictionary<string, SheetData> sheetDataDic,object subListMainKey)
    {
        object item = CreateClassInstance(sheetClass.Name);//获取Type，用来创建泛型list
        object listGeneric = CreateListGenericType(item.GetType());

        for (int i = 0; i < sheetData.AllRowData.Count; i++)// 填充泛型list
        {
            if (subListMainKey != null || !string.IsNullOrEmpty(sheetData.AllRowData[i].ParentValue))
            {
                if(sheetData.AllRowData[i].ParentValue != subListMainKey.ToString())
                {
                    continue;
                }
            }
            object addItem = CreateClassInstance(sheetClass.Name);
            for (int j = 0; j < sheetClass.VarList.Count; j++)
            {
                VarClass varClass = sheetClass.VarList[j];
                if (varClass.Type == "list" && string.IsNullOrEmpty(varClass.Split))
                {
                    ReadDataToClass(addItem, sheetClassDic[varClass.ListSheetName], sheetDataDic[varClass.ListSheetName], sheetClassDic, sheetDataDic,GetMemberValue(addItem,sheetClass.MainKey));
                }
                else if(varClass.Type == "list")
                {
                    string value = sheetData.AllRowData[i].RowDataDic[sheetData.AllColName[j]];
                    SetSplitCustomClass(addItem, sheetClassDic[varClass.ListSheetName],value);
                }
                else if(varClass.Type == "listStr" || varClass.Type == "listFlost" || varClass.Type == "listInt" || varClass.Type == "listBool")
                {
                    string value = sheetData.AllRowData[i].RowDataDic[sheetData.AllColName[j]];
                    SetSplitBaseClass(addItem,varClass,value);
                }
                else
                {
                    string value = sheetData.AllRowData[i].RowDataDic[sheetData.AllColName[j]];
                    if (string.IsNullOrEmpty(value) && !string.IsNullOrEmpty(varClass.DefaultValue))
                    {
                        value = varClass.DefaultValue;
                    }
                    if(string.IsNullOrEmpty(value))
                    {
                        Debug.LogError(sheetClass.SheetName + " 表格中有空数据，或者Reg文件未配置defaultValue");
                        continue;
                    }
                    PropertyInfo propertyInfo = addItem.GetType().GetProperty(sheetData.AllColName[j]);//sheetData.AllColName[j]和sheetClass.VarList[j].Name是一一对应的，都一样
                    string type = sheetData.AllType[j];
                    SetValue(propertyInfo, addItem, value, type);
                }
            }
            listGeneric.GetType().InvokeMember("Add",BindingFlags.Default | BindingFlags.InvokeMethod,null,listGeneric,new object[] {addItem});
        }
        //将list添加到主数据类
        objClass.GetType().GetProperty(sheetClass.ParentVar.Name).SetValue(objClass, listGeneric);

    }
    /// <summary>
    /// 将基础List还原回单独的值并赋值
    /// </summary>
    /// <param name="item">需要添加值的对象</param>
    /// <param name="varClass">变量类型信息</param>
    /// <param name="value">变量值</param>
    private static void SetSplitBaseClass(object item,VarClass varClass,string value)
    {
        Type type = null;
        if (varClass.Type == "listStr")
        {
            type = typeof(string);
        }
        else if (varClass.Type == "listFloat")
        {
            type= typeof(float);
        }
        else if (varClass.Type == "listInt")
        {
            type=typeof(int);
        }
        else if (varClass.Type == "listBool")
        {
            type = typeof(bool);
        }
        object list = CreateListGenericType(type);
        string[] rowArray = value.Split(new string[] {varClass.Split},StringSplitOptions.None);
        for (int i = 0; i < rowArray.Length; i++)
        {
            object addItem = rowArray[i].Trim();
            try
            {
                list.GetType().InvokeMember("Add",BindingFlags.Default|BindingFlags.InvokeMethod,null,list,new object[] {addItem});

            }
            catch (Exception)
            {
                Debug.Log(varClass.ListSheetName + " 里" + varClass.Name + " 列表添加失败！具体数值是：" + addItem);
            }
        }
        item.GetType().GetProperty(varClass.Name).SetValue(item, list);
    }
    /// <summary>
    /// 将自定义类的List还原回单独的值并赋值
    /// </summary>
    /// <param name="item">需要添加的对象</param>
    /// <param name="sheetClass">list类型信息和变量信息</param>
    /// <param name="value">整体的值</param>
    private static void SetSplitCustomClass(object item, SheetClass sheetClass, string value)
    {
        object itemForType = CreateClassInstance(sheetClass.Name);
        object list = CreateListGenericType(itemForType.GetType());

        if (string.IsNullOrEmpty(value))//如果有空值
        {
            Debug.Log("Excel里面自定义List的列里有空值！" + sheetClass.Name);
            return;
        }
        else
        {

            string listsSplit = sheetClass.ParentVar.Split.Replace("\\n","\n").Replace("\\r","\r");//这是从xml读出来的，必须要替换
            string varSplit = sheetClass.Split;
            string[] rowArray = value.Split(new string[] {listsSplit},StringSplitOptions.None);
            for (int i = 0; i < rowArray.Length; i++)
            {
                object addItem = CreateClassInstance(sheetClass.Name);
                string[] valueList = rowArray[i].Trim().Split(new string[] {varSplit},StringSplitOptions.None);
                for (int j = 0; j < valueList.Length; j++)
                {
                    SetValue(addItem.GetType().GetProperty(sheetClass.VarList[j].Name), addItem, valueList[j].Trim(), sheetClass.VarList[j].Type);
                }
                list.GetType().InvokeMember("Add",BindingFlags.Default|BindingFlags.InvokeMethod,null,list,new object[] {addItem});
            }
        }
        item.GetType().GetProperty(sheetClass.ParentVar.Name).SetValue(item, list);
    }