最终效果

所有流程全部在Unity中进行,且对策划友好

  • 类可以生成Xml
  • Xml文件可以生成Excel
  • Excel可以反向生成Xml
  • Xml生成二进制文件(游戏中实际读取二进制)

支持List的多层嵌套,可以嵌套任意对象

实际流程

  • 编写class
  • 生成模拟xml数据
  • 编写Xml和Excel中间转换规则reg文件
  • 根据Xml文件和reg规则生成Excel初始表
  • 策划填写数值
  • Excel反向生成Xml文件
  • 打包流程嵌入Xml转二进制
  • 游戏运行读取二进制文件

实现思路

Reg文件规则设定

Reg文件是一个我们自己编写的xml 导入、导出规则文件

一个普通List的Reg文件举例:

<?xml version="1.0" encoding="utf-8"?>
<data name = "类名" from = "Excel文件名.xlsx" to = "xml文件名.xml">
    <variable name = "List变量名" type = "类型(list)">
        <list name = "对象名" sheetname = "sheet名" mainKey = "主key">
            <variable name = "变量名" col = "Excel中列名" type = "类型(int, string, class, etc...)"/>
        </list>
    </variable>
</data>

特殊情况:

如果在这个List对象中嵌套了一个List<基本类型>变量时,我们使用Split关键字做区分,在Excel中会放在同一个单元格中,用自定义的符号隔开

这里的type可以为 listBoollistStringlistIntlistFloat
如果要增加其他基本类型的支持可以自行添加

<variable name = "List对象名" col = "xxx" type = "list类型" split = ";" />

如果这个List对象中嵌套了一个List<class>时,我们用foregin 外键来区分,在Excel会把这个对象放到一个新的sheet中,转Xml时会根据外键添加到从属List对象中

<variable name = "List变量名" type = "list" foregin = "ID">    ID 为外键
    <list name = "对象名" sheetname = "sheet名">
        <variable name = "ID" col = "Excel 列名" type = "int"/>
        <variable name = "Name" col = "Excel 列名" type = "string"/>
    </list>
</variable>

Excel 文件读取

我们使用Epplus库对Excel进行解析

需要注意的是, 这个库数组是从1开始的

基本用法举例:

using (ExcelPackage package = new ExcelPackage(xlsxFile)) {
     ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("测试配置");
    worksheet.DefaultColWidth = 10;//sheet页面默认行宽度
    worksheet.DefaultRowHeight = 30;//sheet页面默认列高度
    worksheet.Cells.Style.WrapText = true;//设置所有单元格的自动换行
    worksheet.InsertColumn();//插入行,从某一行开始插入多少行
    worksheet.InsertRow();//插入列,从某一列开始插入多少列
    worksheet.DeleteColumn();//删除行,从某一行开始删除多少行
    worksheet.DeleteRow();//删除列,从某一列开始删除多少列
    worksheet.Column(1).Width = 10;//设定第几行宽度
    worksheet.Row(1).Height = 30;//设定第几列高度
    worksheet.Column(1).Hidden = true;//设定第几行隐藏
    worksheet.Row(1).Hidden = true;//设定第几列隐藏
    worksheet.Column(1).Style.Locked = true;//设定第几行锁定
    worksheet.Row(1).Style.Locked = true;//设定第几列锁定
    worksheet.Cells.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;//设定所有单元格对齐方式

    worksheet.Cells.AutoFitColumns();
    ExcelRange range = worksheet.Cells[1, 1];
    range.Value = " 测试sadddddddddddddd\ndddddddddddddddddddasda";
    range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.None;
    range.Style.Fill.BackgroundColor.SetColor();//设置单元格内背景颜色
    range.Style.Font.Color.SetColor();//设置单元格内字体颜色
    range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;//对齐方式 
    range.AutoFitColumns();
    range.Style.WrapText = true;

    ExcelRange range1 = worksheet.Cells[1, 2];
    range1.Value = " test";


    package.Save();
}

反射

获得对象的一个属性

这里我们只用到FieldProperty

public static object GetMemberValue(object obj,
                                    string memberName,
                                    BindingFlags bindingFlags =
                                        BindingFlags.Public | BindingFlags.Static | BindingFlags.Instance) {
    Type         type    = obj.GetType();
    MemberInfo[] members = type.GetMember(memberName, bindingFlags);

    if(members.Length <= 0){
        Debuger.LogError($"Reflect error, no {memberName} in {obj}");
        return null;
    }

    switch(members[0].MemberType){
        case MemberTypes.All:
        case MemberTypes.Constructor:
        case MemberTypes.Custom:
        case MemberTypes.Event:
        case MemberTypes.Method:
        case MemberTypes.NestedType:
        case MemberTypes.TypeInfo:
            return null;
        case MemberTypes.Field:    return type.GetField(memberName, bindingFlags).GetValue(obj);
        case MemberTypes.Property: return type.GetProperty(memberName, bindingFlags).GetValue(obj);
        default:                   return null;
    }
}

通过字符串创建类对象

因为主逻辑写在Editor下,反射涉及到跨域反射,需要传入class的domain 和assemb的名字

public static object CreateInstance(string domain, string name, string assetmbName) {
    Type type = Type.GetType($"{domain}.{name}, {assetmbName}");

    if(type != null) return Activator.CreateInstance(type);

    Debuger.LogError("Can't find " + name);
    return null;
}

反射变量根据字符串赋值

public static void SetValue(PropertyInfo info, object obj, string value, string type) {
    object setValue = value;
    switch(type){
        case"int":
            setValue = Convert.ToInt32(setValue);
            break;
        case"bool":
            setValue = Convert.ToBoolean(setValue);
            break;
        case"float":
            setValue = Convert.ToSingle(setValue);
            break;
        case"double":
            setValue = Convert.ToDouble(setValue);
            break;
        case"enum":
            setValue = TypeDescriptor.GetConverter(info.PropertyType).
                                      ConvertFromInvariantString(setValue.ToString());
            break;
    }

    info.SetValue(obj, setValue);
}

反射创建List对象

public static object CreateList(Type type) {
    Type listType = typeof(List<>);
    Type specType = listType.MakeGenericType(type);

    return Activator.CreateInstance(specType, new object[] { });
}

反射获得object list 的长度

int count = Convert.ToInt32(dataList.GetType().InvokeMember(
                            "get_Count",
                            BindingFlags.Default | BindingFlags.InvokeMethod,
                            null,
                            dataList,
                            new object[] { }
)

数据结构

public class VariableBase {
    public string Name          { get; set; } // 原类变量名
    public string Type          { get; set; } // 变量类型
    public string Col           { get; set; } // 变量对应的Excel 列名
    public string DefaultValue  { get; set; } // 变量默认值
    public string Foregin       { get; set; } // 变量为List 时 外链部分的列
    public string Split         { get; set; } // 分隔符
    public string ListName      { get; set; } // 如果自己是list,则对应list类名
    public string ListSheetName { get; set; } // 如果自己是list,则对应sheet名
}

public class VariableSheet {
    public int                Depth          { get; set; }             // 深度值
    public VariableBase       ParentVariable { get; set; }             // 父级Reg 类
    public string             Name           { get; set; }             // 类名
    public string             SheetName      { get; set; }             // sheet 名
    public string             MainKey        { get; set; }             // 主键
    public string             Split          { get; set; }             // 分隔符
    public List<VariableBase> VariableList = new List<VariableBase>(); // 包含的变量
}

public class SheetData {
    public List<string>  AllColName = new List<string>();  // 所有列名
    public List<string>  AllType    = new List<string>();  // 所有类型
    public List<RowData> AllRowData = new List<RowData>(); // 所有行数据
}


public class RowData {
    public string                     ParentValue = "";
    public Dictionary<string, string> RowDataDic  = new Dictionary<string, string>();
}

其他工具

Xml 序列化、反序列化存储

public static bool XmlSerialize(string savePath, Object obj) {
    try{
        using(FileStream fs =
            new FileStream(savePath, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite)){
            using(StreamWriter sw = new StreamWriter(fs, Encoding.UTF8)){
                XmlSerializerNamespaces namespaces = new XmlSerializerNamespaces();
                namespaces.Add(String.Empty, string.Empty);

                XmlSerializer xmlSerializer = new XmlSerializer(obj.GetType());
                xmlSerializer.Serialize(sw, obj, namespaces);
            }
        }

        return true;
    }
    catch(Exception e){
        Debuger.LogError("Serialize error: " + e);
        return false;
    }
}
public static Object JsonDeserialize(string loadPath) {
    Object obj = null;

    try{
        using(FileStream fs =
            new FileStream(loadPath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite)){
            using(StreamReader sr = new StreamReader(fs, Encoding.UTF8)){
                obj = JsonConvert.DeserializeObject(sr.ReadToEnd());
            }
        }
    }
    catch(Exception e){
        Debuger.LogError("Xml deserialize exception : " + e);
    }

    return obj;
}

二进制序列化、反序列化存储

public static bool BinarySerlize(string savePath, Object obj) {
    try{
        using(FileStream fs =
            new FileStream(savePath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite)){
            BinaryFormatter bf = new BinaryFormatter();
            bf.Serialize(fs, obj);
            return true;
        }
    }
    catch(Exception e){
        Debuger.LogError("Serlize exception : " + e);
        return false;
    }
}
public static T BinaryDeserialize<T>(string loadPath) where T : class {
    T t = null;

    TextAsset textAsset = ResourceManager.Instance.LoadResource<TextAsset>(loadPath);

    if(textAsset == null){
        Debuger.LogError("Can't load TextAsset at " + loadPath);
        return null;
    }

    try{
        using(MemoryStream ms = new MemoryStream(textAsset.bytes)){
            BinaryFormatter bf = new BinaryFormatter();
            t = bf.Deserialize(ms) as T;
        }

        ResourceManager.Instance.ReleaseResource(textAsset, true);
    }
    catch(Exception e){
        Debuger.LogError("Deserialize exception : " + e);
    }

    return t;
}

具体代码实现

类转Xml工具

[MenuItem("Assets/Excel 工具/类转Xml")]
public static void Class2Xml() {
    Debuger.EnableLog = true;
    Object[] objs = Selection.objects;
    for(int i = 0; i < objs.Length; i++){
        EditorUtility.DisplayProgressBar("Class to Xml", $"Scaning {objs[i].name} ...", 1.0f / objs.Length * i);
        SaveXmlFile(objs[i].name);
    }

    AssetDatabase.Refresh();
    EditorUtility.ClearProgressBar();
}

这里的ConfigDataBase 是所有Config文件的基类,方便我们写入一些原始数据生成Xml

private static void SaveXmlFile(string name) {
     if(string.IsNullOrEmpty(name)) return;
     try{

         var temp = ReflectionUtil.CreateInstance(_domain, name, _assembName); 
         (temp as ConfigDataBase)?.Construction();

         FileSerializeUtil.XmlSerialize(PathHelper.CONFIG_XML_PATH + name + ".xml", temp);
         Debuger.Log($"Finish convert {name} to xml, path = {PathHelper.CONFIG_XML_PATH}");
     }
     catch(Exception e){ Debuger.LogError("Failed to convert, e = {0}", e.ToString()); }
 }

Xml转二进制

[MenuItem("Assets/Excel 工具/Xml转二进制")]
public static void Xml2Binary() {
    Debuger.EnableLog = true;
    Object[] objs = Selection.objects;
    for(int i = 0; i < objs.Length; i++){
        EditorUtility.DisplayProgressBar(
            "Xml to binary",
            $"Scaning {objs[i].name} ...",
            1.0f / objs.Length * i
        );
        SaveBinaryFile(objs[i].name);
    }

    AssetDatabase.Refresh();
    EditorUtility.ClearProgressBar();
}
private static void SaveBinaryFile(string name) {
    if(string.IsNullOrEmpty(name)) return;

    try{
        string filePath       = PathHelper.CONFIG_XML_PATH + name    + ".xml";
        string binaryFilePath = PathHelper.CONFIG_BINARY_PATH + name + ".bytes";
        Type   type           = Type.GetType($"{_domain}.{name}, {_assembName}");

        object obj = FileSerializeUtil.XmlDeserialize(filePath, type);

        FileSerializeUtil.BinarySerlize(binaryFilePath, obj);

        Debuger.Log($"Finish convert {name} xml to binary, path = {PathHelper.CONFIG_BINARY_PATH}");
    }
    catch(Exception e){ Debuger.LogError("Failed to convert, e = {0}", e.ToString()); }
}

Xml转Excel

[MenuItem("Assets/Excel 工具/Xml转Excel")]
public static void AssetsXml2Excel() {
    Debuger.EnableLog = true;

    Object[] objs = Selection.objects;
    for(int i = 0; i < objs.Length; i++){
        EditorUtility.DisplayProgressBar(
            "Transform xml to excel",
            $"Scaning {objs[i].name}......",
            1.0f / objs.Length * i
        );
        Xml2Excel(objs[i].name);
    }

    AssetDatabase.Refresh();
    EditorUtility.ClearProgressBar();
}
private static void Xml2Excel(string name) {
     string className = String.Empty;
     string xmlName   = String.Empty;
     string excelName = String.Empty;

     Dictionary<string, VariableSheet> allSheetDic = ReadRegFile(
         name,
         ref excelName,
         ref xmlName,
         ref className
     );

     object data = GetObjFromXml(className);

     List<VariableSheet> outSheets = allSheetDic.Values.Where(sheet => sheet.Depth == 1).ToList();

     Dictionary<string, SheetData> sheetDataDic = new Dictionary<string, SheetData>();

     foreach(var sheet in outSheets){ ReadData(data, sheet, allSheetDic, sheetDataDic); }

     string xlsxPath = PathHelper.EXCEL_PATH + excelName;

     if(IsFileUsed(xlsxPath)){
         Debuger.LogError("File is using! Close excel file.");
         return;
     }

     try{
         FileInfo xlsxFile = new FileInfo(xlsxPath);

         if(xlsxFile.Exists){
             xlsxFile.Delete();
             xlsxFile = new FileInfo(xlsxPath);
         }

         using(ExcelPackage package = new ExcelPackage(xlsxFile)){
             foreach(string key in sheetDataDic.Keys){
                 ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(key);
                 SheetData      sheetData = sheetDataDic[key];

                 for(int i = 0; i < sheetData.AllColName.Count; i++){
                     ExcelRange range = worksheet.Cells[1, i + 1];
                     range.Value = sheetData.AllColName[i];
                 }

                 for(int i = 0; i < sheetData.AllRowData.Count; i++){
                     RowData rowData = sheetData.AllRowData[i];

                     for(int j = 0; j < sheetData.AllRowData[i].RowDataDic.Count; j++){
                         ExcelRange range = worksheet.Cells[i + 2, j + 1];
                         string     value = rowData.RowDataDic[sheetData.AllColName[j]];
                         range.Value = value;
                         if(value.Contains("\n") || value.Contains("\r\n")){ range.Style.WrapText = true; }
                     }
                 }
             }

             package.Save();
         }
     }
     catch(Exception e){
         Debuger.LogError(e);
         return;
     }

     Debuger.Log("Export excel at {0}", xlsxPath);
 }
private static Dictionary<string, VariableSheet> ReadRegFile(string     name,
                                                             ref string excelName,
                                                             ref string xmlName,
                                                             ref string className) {
    string regPath = $"{PathHelper.EXCEL_REG_XML_PATH}{name}.xml";

    if(!File.Exists(regPath)){ Debuger.LogError("No file at {0}", regPath); }

    XmlDocument       xmlDocument = new XmlDocument();
    XmlReader         reader      = XmlReader.Create(regPath);
    XmlReaderSettings settings    = new XmlReaderSettings {IgnoreComments = true};

    xmlDocument.Load(reader);

    XmlNode    node    = xmlDocument.SelectSingleNode("data");
    XmlElement element = node as XmlElement;

    className = element.GetAttribute("name");
    xmlName   = element.GetAttribute("to");
    excelName = element.GetAttribute("from");

    Dictionary<string, VariableSheet> allSheetDic = new Dictionary<string, VariableSheet>();

    ReadXmlNodeRecursive(element, allSheetDic, 0);

    reader.Close();

    return allSheetDic;
}
 private static object GetObjFromXml(string name) {
     Type type = Type.GetType($"{_domain}.{name}, {_assembName}");

     if(type == null){
         Debuger.LogError("Can't find " + name);
         return null;
     }

     string xmlPath = $"{PathHelper.CONFIG_XML_PATH}{name}.xml";
     return FileSerializeUtil.XmlDeserialize(xmlPath, type);
 }
private static void ReadData(object                            data,
                             VariableSheet                     sheet,
                             Dictionary<string, VariableSheet> allSheetDic,
                             Dictionary<string, SheetData>     sheetDataDic,
                             string                            mainKey = "") {
    List<VariableBase> variableList = sheet.VariableList;
    VariableBase       variableBase = sheet.ParentVariable;


    object dataList = ReflectionUtil.GetMemberValue(data, variableBase.Name);
    int count = Convert.ToInt32(
        dataList.GetType()
                .InvokeMember(
                    "get_Count",
                    BindingFlags.Default | BindingFlags.InvokeMethod,
                    null,
                    dataList,
                    new object[] { }
                )
    );

    SheetData sheetData = new SheetData();

    if(!string.IsNullOrEmpty(variableBase.Foregin)){
        sheetData.AllColName.Add(variableBase.Foregin);
        sheetData.AllType.Add(variableBase.Type);
    }

    foreach(var reg in variableList.Where(reg => !string.IsNullOrEmpty(reg.Col))){
        sheetData.AllColName.Add(reg.Col);
        sheetData.AllType.Add(reg.Type);
    }

    string tempKey = mainKey;

    for(int i = 0; i < count; i++){
        object item = dataList.GetType()
                              .InvokeMember(
                                  "get_Item",
                                  BindingFlags.Default | BindingFlags.InvokeMethod,
                                  null,
                                  dataList,
                                  new object[] {i}
                              );
        RowData rowData = new RowData();

        if(!string.IsNullOrEmpty(variableBase.Foregin) && !string.IsNullOrEmpty(tempKey)){
            rowData.RowDataDic.Add(variableBase.Foregin, tempKey);
        }

        if(!string.IsNullOrEmpty(sheet.MainKey)){
            mainKey = ReflectionUtil.GetMemberValue(item, sheet.MainKey).ToString();
        }

        for(int j = 0; j < variableList.Count; j++){
            if(variableList[j].Type == "list" && string.IsNullOrEmpty(variableList[j].Split)){
                VariableSheet variableSheet = allSheetDic[variableList[j].ListSheetName];

                ReadData(item, variableSheet, allSheetDic, sheetDataDic);
            }
            else if(variableList[j].Type == "list"){
                VariableSheet tempSheet = allSheetDic[variableList[j].ListSheetName];
                string        value     = GetSplitList(item, variableList[j], tempSheet);
                rowData.RowDataDic.Add(variableList[j].Col, value);
            }
            else if(variableList[j].Type == "listString" ||
                    variableList[j].Type == "listFloat"  ||
                    variableList[j].Type == "listInt"    ||
                    variableList[j].Type == "listBool"){
                string value = GetSpliteBaseList(item, variableList[j]);
                rowData.RowDataDic.Add(variableList[j].Col, value);
            }
            else{
                object value = ReflectionUtil.GetMemberValue(item, variableList[j].Name);
                if(value != null){ rowData.RowDataDic.Add(variableList[j].Col, value.ToString()); }
                else{ Debuger.LogError($"{variableList[j].Name} reflect is null"); }
            }
        }

        string key = variableBase.ListSheetName;

        if(sheetDataDic.ContainsKey(key)){ sheetDataDic[key].AllRowData.Add(rowData); }
        else{
            sheetData.AllRowData.Add(rowData);
            sheetDataDic.Add(key, sheetData);
        }
    }
}
private static string GetSpliteBaseList(object data, VariableBase variableBase) {
    string str = "";
    if(string.IsNullOrEmpty(variableBase.Split)){
        Debuger.LogError("基础List的分隔符为空!");
        return str;
    }

    object dataList = ReflectionUtil.GetMemberValue(data, variableBase.Name);
    int listCount = Convert.ToInt32(
        dataList.GetType()
                .InvokeMember(
                    "get_Count",
                    BindingFlags.Default | BindingFlags.InvokeMethod,
                    null,
                    dataList,
                    new object[] { }
                )
    );

    for(int i = 0; i < listCount; i++){
        object item = dataList.GetType()
                              .InvokeMember(
                                  "get_Item",
                                  BindingFlags.Default | BindingFlags.InvokeMethod,
                                  null,
                                  dataList,
                                  new object[] {i}
                              );
        str += item.ToString();
        if(i != listCount - 1){ str += variableBase.Split.Replace("\\n", "\n").Replace("\\r", "\r"); }
    }

    return str;
}
private static string GetSplitList(object data, VariableBase variableBase, VariableSheet sheet) {
    string split      = variableBase.Split;
    string classSplit = sheet.Split;
    string str        = "";
    if(string.IsNullOrEmpty(split) || string.IsNullOrEmpty(classSplit)){
        Debuger.LogError("Class spilt or variable split is null!");
        return str;
    }

    object dataList = ReflectionUtil.GetMemberValue(data, variableBase.Name);
    int listCount = Convert.ToInt32(
        dataList.GetType()
                .InvokeMember(
                    "get_Count",
                    BindingFlags.Default | BindingFlags.InvokeMethod,
                    null,
                    dataList,
                    new object[] { }
                )
    );
    for(int i = 0; i < listCount; i++){
        object item = dataList.GetType()
                              .InvokeMember(
                                  "get_Item",
                                  BindingFlags.Default | BindingFlags.InvokeMethod,
                                  null,
                                  dataList,
                                  new object[] {i}
                              );
        for(int j = 0; j < sheet.VariableList.Count; j++){
            object value = ReflectionUtil.GetMemberValue(item, sheet.VariableList[j].Name);
            str += value.ToString();
            if(j != sheet.VariableList.Count - 1){
                str += classSplit.Replace("\\n", "\n").Replace("\\r", "\r");
            }
        }

        if(i != listCount - 1){ str += split.Replace("\\n", "\n").Replace("\\r", "\r"); }
    }

    return str;
}

Excel转Xml

[MenuItem("Tools/Excel转Xml")]
 public static void AllExcel2Xml() {
     Debuger.EnableLog = true;

     string[] filePaths = Directory.GetFiles(PathHelper.EXCEL_REG_XML_PATH, "*", SearchOption.AllDirectories);
     for(int i = 0; i < filePaths.Length; i++){
         if(!filePaths[i].EndsWith(".xml")) continue;
         EditorUtility.DisplayProgressBar(
             "Find class",
             $"Scaning {filePaths[i]}...",
             1.0f / filePaths.Length * i
         );
         string path = filePaths[i].Substring(filePaths[i].LastIndexOf("/") + 1);
         Excel2Xml(path.Replace(".xml", ""));
     }

     AssetDatabase.Refresh();
     EditorUtility.ClearProgressBar();
 }
 private static void Excel2Xml(string name) {
     string className = String.Empty;
     string xmlName   = String.Empty;
     string excelName = String.Empty;

     Dictionary<string, VariableSheet> allSheetDic = ReadRegFile(
         name,
         ref excelName,
         ref xmlName,
         ref className
     );

     string excelPath = PathHelper.EXCEL_PATH + excelName;

     Dictionary<string, SheetData> sheetDataDic = new Dictionary<string, SheetData>();

     try{
         using(FileStream stream = new FileStream(
             excelPath,
             FileMode.Open,
             FileAccess.Read,
             FileShare.ReadWrite
         )){
             using(ExcelPackage package = new ExcelPackage(stream)){
                 ExcelWorksheets workSheetArray = package.Workbook.Worksheets;

                 for(int i = 0; i < workSheetArray.Count; i++){
                     SheetData      sheetData     = new SheetData();
                     ExcelWorksheet workSheet     = workSheetArray[i + 1];
                     VariableSheet  variableSheet = allSheetDic[workSheet.Name];

                     int colCount = workSheet.Dimension.End.Column;
                     int rowCount = workSheet.Dimension.End.Row;

                     foreach(var sheet in variableSheet.VariableList){
                         sheetData.AllColName.Add(sheet.Name);
                         sheetData.AllType.Add(sheet.Type);
                     }

                     for(int r = 1; r < rowCount; r++){
                         RowData rowData = new RowData();
                         int     c       = 0;
                         if(string.IsNullOrEmpty(variableSheet.Split) &&
                            variableSheet.ParentVariable != null      &&
                            !string.IsNullOrEmpty(variableSheet.ParentVariable.Foregin)){
                             rowData.ParentValue = workSheet.Cells[r + 1, 1].Value.ToString().Trim();
                             c                   = 1;
                         }

                         for(; c < colCount; c++){
                             ExcelRange range = workSheet.Cells[r + 1, c + 1];
                             // TODO 如果需要前后空格, 需要删除Trim
                             string value        = range.Value.ToString().Trim();
                             string colValue     = workSheet.Cells[1, c + 1].Value.ToString().Trim();
                             string variableName = GetNameFromCol(variableSheet.VariableList, colValue);

                             rowData.RowDataDic.Add(variableName, value);
                         }

                         sheetData.AllRowData.Add(rowData);
                     }

                     sheetDataDic.Add(workSheet.Name, sheetData);
                 }
             }
         }
     }
     catch(Exception e){
         Debuger.LogError(e);
         return;
     }

     object objClass = ReflectionUtil.CreateInstance(_domain, className, _assembName);

     List<string> outKeyList =
         (from key in allSheetDic.Keys let sheet = allSheetDic[key] where sheet.Depth == 1 select key).ToList();

     foreach(var key in outKeyList){
         ReadData2Class(objClass, allSheetDic[key], sheetDataDic[key], allSheetDic, sheetDataDic, null);
     }

     FileSerializeUtil.XmlSerialize(PathHelper.CONFIG_XML_PATH + xmlName, objClass);
     Debuger.Log($"{excelName} import finish");
     AssetDatabase.Refresh();
 }
private static void ReadData2Class(object                            objClass,
                                    VariableSheet                     variableSheet,
                                    SheetData                         sheetData,
                                    Dictionary<string, VariableSheet> allSheetDic,
                                    Dictionary<string, SheetData>     sheetDataDic,
                                    object                            keyValue) {
     object item = ReflectionUtil.CreateInstance(_domain, variableSheet.Name, _assembName); //只是为了得到变量类型
     object list = ReflectionUtil.CreateList(item.GetType());
     foreach(var row in sheetData.AllRowData){
         if(keyValue != null && !string.IsNullOrEmpty(row.ParentValue)){
             if(row.ParentValue != keyValue.ToString()) continue;
         }

         object addItem = ReflectionUtil.CreateInstance(_domain, variableSheet.Name, _assembName);
         for(int j = 0; j < variableSheet.VariableList.Count; j++){
             VariableBase varClass = variableSheet.VariableList[j];
             switch(varClass.Type){
                 case "list" when string.IsNullOrEmpty(varClass.Split):
                     ReadData2Class(
                         addItem,
                         allSheetDic[varClass.ListSheetName],
                         sheetDataDic[varClass.ListSheetName],
                         allSheetDic,
                         sheetDataDic,
                         ReflectionUtil.GetMemberValue(addItem, variableSheet.MainKey)
                     );
                     break;
                 case "list":
                 {
                     string value = row.RowDataDic[sheetData.AllColName[j]];
                     SetSplitClass(addItem, allSheetDic[varClass.ListSheetName], value);
                     break;
                 }
                 case "listString":
                 case "listFloat":
                 case "listInt":
                 case "listBool":
                 {
                     string value = row.RowDataDic[sheetData.AllColName[j]];
                     SetSplitBaseClass(addItem, varClass, value);
                     break;
                 }
                 default:
                 {
                     string value = row.RowDataDic[sheetData.AllColName[j]];
                     if(string.IsNullOrEmpty(value) && !string.IsNullOrEmpty(varClass.DefaultValue)){
                         value = varClass.DefaultValue;
                     }

                     if(string.IsNullOrEmpty(value)){
                         Debug.LogError("表格中有空数据,或者Reg文件未配置defaultValue!" + sheetData.AllColName[j]);
                         continue;
                     }

                     ReflectionUtil.SetValue(
                         addItem.GetType().GetProperty(sheetData.AllColName[j]),
                         addItem,
                         value,
                         sheetData.AllType[j]
                     );
                     break;
                 }
             }
         }

         list.GetType()
             .InvokeMember(
                 "Add",
                 BindingFlags.Default | BindingFlags.InvokeMethod,
                 null,
                 list,
                 new object[] {addItem}
             );
     }

     objClass.GetType().GetProperty(variableSheet.ParentVariable.Name).SetValue(objClass, list);
 }
 private static void SetSplitBaseClass(object objClass, VariableBase variableBase, string value) {
     Type type = null;
     switch(variableBase.Type){
         case"listString":
             type = typeof(string);
             break;
         case"listFloat":
             type = typeof(float);
             break;
         case"listInt":
             type = typeof(int);
             break;
         case"listBool":
             type = typeof(bool);
             break;
     }

     object   list     = ReflectionUtil.CreateList(type);
     string[] rowArray = value.Split(new[] {variableBase.Split}, StringSplitOptions.None);
     foreach(var row in rowArray){
         object addItem = row.Trim();
         try{
             list.GetType()
                 .InvokeMember(
                     "Add",
                     BindingFlags.Default | BindingFlags.InvokeMethod,
                     null,
                     list,
                     new object[] {addItem}
                 );
         }
         catch{
             Debuger.Log($"Failed add {variableBase.Name} to {variableBase.ListSheetName}, value is {addItem}");
         }
     }

     objClass.GetType().GetProperty(variableBase.Name).SetValue(objClass, list);
 }
private static void SetSplitClass(object objClass, VariableSheet variableSheet, string value) {
    object item = ReflectionUtil.CreateInstance(_domain, variableSheet.Name, _assembName);
    object list = ReflectionUtil.CreateList(item.GetType());
    if(string.IsNullOrEmpty(value)){
        Debuger.Log("There is null value in excel list, value = {0}", variableSheet.Name);
        return;
    }
    else{
        string   splitStr = variableSheet.ParentVariable.Split.Replace("\\n", "\n").Replace("\\r", "\r");
        string[] rowArray = value.Split(new[] {splitStr}, StringSplitOptions.None);
        foreach(var row in rowArray){
            object   addItem   = ReflectionUtil.CreateInstance(_domain, variableSheet.Name, _assembName);
            string[] valueList = row.Trim().Split(new string[] {variableSheet.Split}, StringSplitOptions.None);
            for(int j = 0; j < valueList.Length; j++){
                ReflectionUtil.SetValue(
                    addItem.GetType().GetProperty(variableSheet.VariableList[j].Name),
                    addItem,
                    valueList[j].Trim(),
                    variableSheet.VariableList[j].Type
                );
            }

            list.GetType()
                .InvokeMember(
                    "Add",
                    BindingFlags.Default | BindingFlags.InvokeMethod,
                    null,
                    list,
                    new[] {addItem}
                );
        }
    }

    objClass.GetType().GetProperty(variableSheet.ParentVariable.Name).SetValue(objClass, list);
}

小结

看到了这个Xml方案还是很受启发的,打通了策划和程序间大量表格的工作流程。

不足的地方还有二进制加载其实还可以优化成protobuf的方式,而且这个方案,每一个类都需要对应一个reg文件。

已经在Mac环境下的 Unity 2018.4.3f1 中验证。

如果你有更好的解决方案也欢迎分享~ 如果有帮到你,也欢迎点个赞。


What doesn’t kill you makes you stronger.