/// <summary>
/// List类型转化成分隔的字符串
/// </summary>
/// <param name="data"></param>
/// <param name="varClass"></param>
/// <returns></returns>
private static string GetSplitStrList(object data, VarClass varClass, SheetClass sheetClass)
{
if (string.IsNullOrEmpty(varClass.SplitStr) || string.IsNullOrEmpty(sheetClass.SplitStr))
{
Debug.LogError("GetSplitStrList: List分隔符为空");
return(null);
}
object dataList = GetMemberValue(data, varClass.Name);
int count = Convert.ToInt32(dataList.GetType().GetProperty("Count").GetValue(dataList));
string str = "";
for (int i = 0; i < count; i++)
{
object value = dataList.GetType().GetProperty("Item").GetValue(dataList, new object[] { i });
for (int j = 0; j < sheetClass.VarList.Count; j++)
{
VarClass curVarClass = sheetClass.VarList[j];
object varValue = GetMemberValue(value, curVarClass.Name);
str += varValue.ToString();
if (j != sheetClass.VarList.Count - 1)
{
str += sheetClass.SplitStr;
}
}
if (i != count - 1)
{
str += varClass.SplitStr;
}
}
return(str);
}
/// <summary>
/// 自定义类List赋值
/// </summary>
/// <param name="objClass"></param>
/// <param name="sheetClass"></param>
/// <param name="value"></param>
private static void SetSplitClass(object objClass, SheetClass sheetClass, string value)
{
object item = CreateClass(sheetClass.Name);
object list = CreateList(item.GetType());
if (string.IsNullOrEmpty(value))
{
Debug.Log("excel里面自定义list的列里有空值！" + sheetClass.Name);
return;
}
else
{
string splitStr = sheetClass.ParentVar.SplitStr.Replace("\\n", "\n").Replace("\\r", "\r");
string[] rowArray = value.Split(new string[] { splitStr }, StringSplitOptions.None);
for (int i = 0; i < rowArray.Length; i++)
{
object addItem = CreateClass(sheetClass.Name);
string[] valueList = rowArray[i].Trim().Split(new string[] { sheetClass.SplitStr }, 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 });
}
}
objClass.GetType().GetProperty(sheetClass.ParentVar.Name).SetValue(objClass, list);
}
/// <summary>
/// 获取本身是一个类的列表，但是数据比较少；（没办法确定父级结构的）
/// </summary>
/// <returns></returns>
private static string GetSplitStrList(object data, VarClass varClass, SheetClass sheetClass)
{
string split = varClass.SplitStr;
string classSplit = sheetClass.SplitStr;
string str = "";
if (string.IsNullOrEmpty(split) || string.IsNullOrEmpty(classSplit))
{
Debug.LogError("类的列类分隔符或变量分隔符为空！！！");
return(str);
}
object dataList = GetMemberValue(data, varClass.Name);
int listCount = System.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 < sheetClass.VarList.Count; j++)
{
object value = GetMemberValue(item, sheetClass.VarList[j].Name);
str += value.ToString();
if (j != sheetClass.VarList.Count - 1)
{
str += classSplit.Replace("\\n", "\n").Replace("\\r", "\r");
}
}
if (i != listCount - 1)
{
str += split.Replace("\\n", "\n").Replace("\\r", "\r");
}
}
return(str);
}
/// <summary>
/// 集合数据转类
/// </summary>
/// <param name="addItem"></param>
/// <param name="sheetClass"></param>
/// <param name="value"></param>
private static void SetSplitClass(object addItem, SheetClass sheetClass, string value)
{
if (string.IsNullOrEmpty(value))
{
Debug.Log("SetSplitClass(): Excel里面自定义list的列里有空值！" + sheetClass.Name);
return;
}
string[] strArray = value.Split(new string[] { sheetClass.ParentVar.SplitStr }, StringSplitOptions.None);
object tempClass = CreateClass(sheetClass.Name);
//集合数据的list
object list = CreateList(tempClass.GetType());
for (int i = 0; i < strArray.Length; i++)
{
string[] itemValue = strArray[i].Trim().Split(new string[] { sheetClass.SplitStr }, StringSplitOptions.None);
object item = CreateClass(sheetClass.Name);
for (int j = 0; j < itemValue.Length; j++)
{
//集合数据的元素
VarClass curVar = sheetClass.VarList[j];
SetMemberValue(item, curVar.Name, curVar.Type, itemValue[j].Trim());
}
list.GetType().InvokeMember("Add", BindingFlags.Default | BindingFlags.InvokeMethod, null, list, new object[] { item });
}
addItem.GetType().GetProperty(sheetClass.ParentVar.Name).SetValue(addItem, list);
}
/// <summary>
/// 获得自定义类的list的字符串,通过分隔符连接, 比如List<TestCustom>
/// </summary>
private static string GetCustomListStr(object obj, SheetClass shtClass)
{
object listObj = GetMemberValue(obj, shtClass.ParentVarClass.Name);
int listCount = (int)listObj.GetType().InvokeMember("get_Count", BindingFlags.Default | BindingFlags.InvokeMethod, null,
listObj, new object[] { });
string str = "";
for (int i = 0; i < listCount; i++)
{
object item = listObj.GetType().InvokeMember("get_Item", BindingFlags.Default | BindingFlags.InvokeMethod,
null, listObj, new object[] { i });
for (int j = 0; j < shtClass.AllVariableList.Count; j++)
{
object value = GetMemberValue(item, shtClass.AllVariableList[j].Name);
str += value.ToString();
if (j != shtClass.AllVariableList.Count - 1)
{
str += shtClass.ParentVarClass.SplitStr;
}
}
if (i != listCount - 1)
{
str += "\n";
}
}
return(str);
}
public static void ReadDataToClass(object obj, SheetClass sheetClass, SheetData sheetData,
Dictionary <string, SheetClass> allSheetClassDic, Dictionary <string, SheetData> allSheetDataDic, object mainKey = null)
{
object temp = CreateClass(sheetClass.Name);
object list = CreateList(temp.GetType());
for (int i = 0; i < sheetData.AllRowData.Count; i++)
{
if (mainKey != null && !string.IsNullOrEmpty(sheetData.AllRowData[i].ParentKey))
{
//如果传进来的主键值 和保存的不同则跳过
if (mainKey.ToString() != sheetData.AllRowData[i].ParentKey)
{
continue;
}
}
object addItem = CreateClass(sheetClass.Name);
//一行的数据
Dictionary <string, string> rowDic = sheetData.AllRowData[i].RowDataDic;
foreach (VarClass varClass in sheetClass.VarList)
{
if (varClass.Type == "list")
{
if (string.IsNullOrEmpty(varClass.SplitStr))
{
//变量名获取
object curKey = rowDic[sheetClass.MainKey];
ReadDataToClass(addItem, allSheetClassDic[varClass.SheetName], allSheetDataDic[varClass.SheetName], allSheetClassDic, allSheetDataDic, curKey);
}
else
{
SetSplitClass(addItem, allSheetClassDic[varClass.SheetName], rowDic[varClass.Name]);
}
}
//xml基础类型
else if (XML_BASE_TYPE.Contains(varClass.Type))
{
SetSplitBaseClass(addItem, varClass, rowDic[varClass.Name]);
}
else
{
string value = rowDic[varClass.Name];
//设置默认值
if (string.IsNullOrEmpty(value) && !string.IsNullOrEmpty(varClass.DefaultValue))
{
value = varClass.DefaultValue;
}
if (value == null)
{
Debug.LogError("Excel表未填值 Reg也没有默认值!" + varClass.Col);
continue;
}
SetMemberValue(addItem, varClass.Name, varClass.Type, value);
}
}
list.GetType().InvokeMember("Add", BindingFlags.InvokeMethod | BindingFlags.Default, null, list, new object[] { addItem });
}
obj.GetType().GetProperty(sheetClass.ParentVar.Name).SetValue(obj, list);
}
/// <summary>
/// 将数据转换为类
/// </summary>
/// <param name="objClass"></param>
/// <param name="sheetClass"></param>
/// <param name="sheetData"></param>
/// <param name="allSheetClassDic"></param>
/// <param name="allSheetDataDic"></param>
private static void ReadDataToClass(object objClass, SheetClass sheetClass, SheetData sheetData,
Dictionary <string, SheetClass> allSheetClassDic, Dictionary <string, SheetData> allSheetDataDic, object keyValue)
{
object item = CreatClass(sheetClass.Name);//获取列表变量类型
object list = CreateList(item.GetType());
for (int i = 0; i < sheetData.AllData.Count; i++)
{
if (keyValue != null && !string.IsNullOrEmpty(sheetData.AllData[i].parentValue))
{
if (sheetData.AllData[i].parentValue != keyValue.ToString())
{
continue;
}
}
object addItem = CreatClass(sheetClass.Name);
for (int j = 0; j < sheetClass.VarList.Count; j++)
{
VarClass varClass = sheetClass.VarList[j];
if (varClass.Type == "list" && string.IsNullOrEmpty(varClass.SplitStr))
{
ReadDataToClass(addItem, allSheetClassDic[varClass.ListSheetName],
allSheetDataDic[GetSheetName(varClass.ListSheetName)], allSheetClassDic, allSheetDataDic,
GetMemberValue(addItem, sheetClass.MainKey));
}
else if (varClass.Type == "list")
{
string value = sheetData.AllData[i].RowDataDic[sheetData.AllName[j]];
SetSplitClass(addItem, allSheetClassDic[varClass.ListSheetName], value);
}
else if (varClass.Type == "listStr" || varClass.Type == "listFloat" ||
varClass.Type == "listInt" || varClass.Type == "listBool")
{
string value = sheetData.AllData[i].RowDataDic[sheetData.AllName[j]];
SetSplitBaseClass(addItem, varClass, value);
}
else
{
string value = sheetData.AllData[i].RowDataDic[sheetData.AllName[j]];
if (string.IsNullOrEmpty(value) && !string.IsNullOrEmpty(varClass.DeafultValue))
{
value = varClass.DeafultValue;
}
else if (string.IsNullOrEmpty(value))
{
Debug.Log("表格中有空数据或者该属性的DeafultValue在reg中未配置" + varClass.Name);
}
SetValue(addItem, sheetData.AllName[j], value, sheetData.AllType[j]);
}
}
list.GetType().InvokeMember("Add", BindingFlags.Default | BindingFlags.InvokeMethod, null, list,
new object[] { addItem });
}
objClass.GetType().GetProperty(sheetClass.ParentVar.Name).SetValue(objClass, list);
}
public ActionResult DetalhesClasse(int id)
{
SheetClass classe = _dbContext.SheetClass.Where(x => x.Id == id).FirstOrDefault();
if (classe == null)
{
return(HttpNotFound());
}
return(View(classe));
}
/// <summary>
/// 读取Excel信息到类
/// </summary>
/// <param name="objClass">类对象</param>
/// <param name="sheetClass"></param>
/// <param name="sheetData"></param>
/// <param name="allSheetDict"></param>
/// <param name="sheetDataDict"></param>
private static void ReadDataToClass(object objClass, SheetClass sheetClass, SheetData sheetData, Dictionary <string, SheetClass> allSheetDict,
Dictionary <string, SheetData> sheetDataDict, object keyValue)
{
object list = CreateList(sheetClass.Name);
for (int i = 0; i < sheetData.AllRowData.Count; i++)
{
Type type = GetType(sheetClass.Name);
object addItem = System.Activator.CreateInstance(type);
if (keyValue != null && !string.IsNullOrEmpty(sheetData.AllRowData[i].ForeignValue))
{
if (sheetData.AllRowData[i].ForeignValue != keyValue.ToString())
{
continue;
}
}
for (int j = 0; j < sheetClass.VarList.Count; j++)
{
VarClass varClass = sheetClass.VarList[j];
if (varClass.Type == "list")
{
ReadDataToClass(addItem, allSheetDict[varClass.ListSheetName], sheetDataDict[varClass.ListSheetName], allSheetDict,
sheetDataDict, GetMemberValue(addItem, sheetClass.MainKey));
}
else if (varClass.Type.Contains("list"))
{
string value = sheetData.AllRowData[i].RowDataDict[sheetData.AllName[j]];
SetBaseListValue(addItem, varClass, value);
}
else
{
string value = sheetData.AllRowData[i].RowDataDict[sheetData.AllName[j]];
if (string.IsNullOrEmpty(value) && !string.IsNullOrEmpty(varClass.DeafultValue))
{
value = varClass.DeafultValue;
}
SetValue(addItem.GetType().GetProperty(sheetData.AllName[j]), addItem, value, sheetData.AllType[j]);
}
}
list.GetType().InvokeMember("Add", BindingFlags.Default | BindingFlags.InvokeMethod, null, list, new object[] { addItem });
}
objClass.GetType().GetProperty(sheetClass.ParentVar.Name).SetValue(objClass, list);
}
/// <summary>
/// 将列表中的特殊类转换为字符串返回
/// </summary>
/// <param name="obj"></param>
/// <param name="sheet"></param>
/// <param name="varClass"></param>
/// <returns></returns>
private static string GetSpliteClassList(object obj, SheetClass sheet, VarClass varClass)
{
string str = "";
if (string.IsNullOrEmpty(varClass.SplitStr) || string.IsNullOrEmpty(varClass.Col))
{
Debug.LogError("字符分隔符或列名为空，请检查:" + varClass.Name);
return(str);
}
int listCount = System.Convert.ToInt32(obj.GetType().InvokeMember("get_Count", BindingFlags.Default | BindingFlags.InvokeMethod, null,
obj, new object[] { }));
for (int i = 0; i < listCount; i++)
{
object item = obj.GetType().InvokeMember("get_Item", BindingFlags.Default | BindingFlags.InvokeMethod, null,
obj, new object[] { i });
string tempStr = "";
for (int j = 0; j < sheet.VarList.Count; j++)
{
if (j != sheet.VarList.Count - 1)
{
tempStr += GetMemberValue(item, sheet.VarList[j].Name) + sheet.SplitStr;
}
else
{
tempStr += GetMemberValue(item, sheet.VarList[j].Name);
}
}
tempStr = tempStr.Replace("\\n", "\n").Replace("\\r", "\r");
if (i != listCount - 1)
{
str += tempStr + varClass.SplitStr;
}
else
{
str += tempStr;
}
}
str = str.Replace("\\n", "\n");
return(str);
}
/// <summary>
/// 解析xml配置
/// </summary>
/// <param name="attrElement"></param>
public static void ReadXmlNode(int depth, XmlElement attrElement, Dictionary <string, SheetClass> allSheetClassDic, SheetClass parentSheet)
{
VarClass attrVar = new VarClass()
{
Name = attrElement.GetAttribute("name"),
Col = attrElement.GetAttribute("col"),
Type = attrElement.GetAttribute("type"),
DeafultValue = attrElement.GetAttribute("defaultValue"),
Foregin = attrElement.GetAttribute("foregin"),
SplitStr = attrElement.GetAttribute("split"),
};
if (parentSheet != null)
{
parentSheet.VarList.Add(attrVar);
attrVar.ParentSheet = parentSheet;
}
if (attrElement.GetAttribute("type") == "list")
{
XmlElement listElement = attrElement.FirstChild as XmlElement;
SheetClass sheet = new SheetClass();
sheet.Name = listElement.GetAttribute("name");
sheet.SheetName = listElement.GetAttribute("sheetname");
sheet.MainKey = listElement.GetAttribute("mainKey");
sheet.SplitStr = listElement.GetAttribute("split");
sheet.ParentVar = attrVar;
sheet.Depth = depth;
if (!string.IsNullOrEmpty(sheet.SheetName))
{
if (!allSheetClassDic.ContainsKey(ParseDicKey(sheet)))
{
foreach (XmlElement itemElement in listElement.ChildNodes)
{
ReadXmlNode(++depth, itemElement, allSheetClassDic, sheet);
}
allSheetClassDic.Add(ParseDicKey(sheet), sheet);
attrVar.ListSheetName = ParseDicKey(sheet);
}
}
}
}
/// <summary>
/// 返回转换后的key
/// </summary>
private static string ParseDicKey(SheetClass sheet)
{
return(sheet.ParentVar.Name + "-" + sheet.SheetName);
}
/// <summary>
/// 读取一张sheet的数据保存起来
/// </summary>
/// <param name="obj"></param>
/// <param name="sClass"></param>
/// <param name="nameSheetClassDic"></param>
/// <param name="nameSheetDataDic"></param>
private static void ReadData(object obj, SheetClass sClass, Dictionary <string, SheetClass> nameSheetClassDic,
Dictionary <string, SheetData> nameSheetDataDic, string theMainKey)
{
List <VariableClass> allVarList = sClass.AllVariableList;
VariableClass parentVar = sClass.ParentVarClass;
//通过反射获取list
object dataList = GetMemberValue(obj, parentVar.Name);
int listCount = (int)dataList.GetType().InvokeMember("get_Count", BindingFlags.Default | BindingFlags.InvokeMethod,
null, dataList, new object[] { });
SheetData shtData = new SheetData();
string tmpMainKey = theMainKey;
//在循环之前就要判断当前sheetclass是否是外联表,
if (!string.IsNullOrEmpty(parentVar.Foreign))
{
//如果是外联表，就需要在这张新的页签添加多一列（在第一列添加）
//值对应的是parentVar的上一级list的mainKey对应的值
shtData.AllNameList.Add(parentVar.Foreign);
//TODO，这里添加的类型应该是mainKey对应的类型
shtData.AllTypeList.Add(parentVar.Type);
}
for (int i = 0; i < allVarList.Count; i++)
{
if (!string.IsNullOrEmpty(allVarList[i].Col))
{
shtData.AllNameList.Add(allVarList[i].Col);
shtData.AllTypeList.Add(allVarList[i].Type);
}
}
for (int i = 0; i < listCount; i++)
{
object item = dataList.GetType().InvokeMember("get_Item", BindingFlags.Default | BindingFlags.InvokeMethod,
null, dataList, new object[] { i });
RowData rData = new RowData();
if (!string.IsNullOrEmpty(tmpMainKey))
{
rData.OneRowDataDic.Add(parentVar.Foreign, tmpMainKey);
}
else
{
theMainKey = GetMemberValue(item, sClass.MainKey).ToString();
}
for (int j = 0; j < allVarList.Count; j++)
{
VariableClass varClass = allVarList[j];
if (varClass.Type == "list")
{
//这种情况处理自定义类的List，（有两种方式处理：1。用分隔符， 2.用主外键的方式，为附加的sheet添加多一列（放在第一列））
//根据有没有外键值来决定用那种方式处理自定义类的list
if (string.IsNullOrEmpty(varClass.SplitStr))
{
SheetClass _shtC = nameSheetClassDic[varClass.ListSheetName];
ReadData(item, _shtC, nameSheetClassDic, nameSheetDataDic, theMainKey);
/*
* <data name = "CustomData" from = "自定义配置.xlsx" to = "CustomData.xml">
* <variable name = "customDataList" type = "list">
* <list name = "CustomDataStructure" sheetname = "用户配置" mainKey = "Id">
* <variable name = "Id" col = "ID" type = "int"/>
* <variable name = "Name" col = "名字" type = "string"/>
* <variable name = "TestCustomList" type = "list" foreign = "ID">
* <list name = "TestCustomData" sheetname = "id对应子表">
* <variable name = "Id" col = "SubID" type = "int"/>
* <variable name = "Name" col = "名字" type = "string"/>
* </list>
* </variable>
* </list>
* </variable>
* </data>
*/
}
else
{
#region 1。用分隔符 对应的结构 以及处理方式
SheetClass _shtC = nameSheetClassDic[varClass.ListSheetName];
string str = GetCustomListStr(item, _shtC);
rData.OneRowDataDic.Add(varClass.Col, str);
/*
* <data name = "CustomData" from = "自定义配置.xlsx" to = "CustomData.xml">
* <variable name = "customDataList" type = "list">
* <list name = "CustomDataStructure" sheetname = "用户配置" mainKey = "Id">
* <variable name = "Id" col = "ID" type = "int"/>
* <variable name = "Name" col = "名字" type = "string"/>
* <variable name = "TestCustomList" col = "TestCustomList" type = "list" split = "|">
* <list name = "TestCustomData" sheetname = "id对应子表">
* <variable name = "Id" col = "ID" type = "int"/>
* <variable name = "Name" col = "名字" type = "string"/>
* </list>
* </variable>
* </list>
* </variable>
* </data>
*/
#endregion
}
}
else if (varClass.Type == "listStr" || varClass.Type == "listInt" ||
varClass.Type == "listFloat" || varClass.Type == "listBool")
{
//处理基础数据类型的list变量 比如List<string>, 处理方式是用分隔符连接所有的值，然后返回一个字符串
string str = GetBaseListStr(item, varClass);
rData.OneRowDataDic.Add(varClass.Col, str);
}
else
{
object tempObj = GetMemberValue(item, varClass.Name);
rData.OneRowDataDic.Add(varClass.Col, tempObj.ToString());
}
}
string sheetKey = sClass.SheetName;
if (nameSheetDataDic.ContainsKey(sheetKey))
{
nameSheetDataDic[sheetKey].AllRowDataList.Add(rData);
}
else
{
shtData.AllRowDataList.Add(rData);
nameSheetDataDic.Add(sheetKey, shtData);
}
}
}
/// <summary>
/// 读取reg文件夹的xml文件， 主要是为了确定excel的数据结构
/// </summary>
private static void ReadRegXmlNode(XmlElement xmlEle, Dictionary <string, SheetClass> nameSheetClassDic, int depth)
{
depth++;
foreach (XmlNode node in xmlEle.ChildNodes)
{
XmlElement xe = (XmlElement)node;
if (xe.GetAttribute("type") == "list")
{
XmlElement subEle = (XmlElement)xe.FirstChild;
VariableClass varClass = new VariableClass()
{
Name = xe.GetAttribute("name"),
Col = xe.GetAttribute("col"),
Type = xe.GetAttribute("type"),
SplitStr = xe.GetAttribute("split"),
Foreign = xe.GetAttribute("foreign"),
DefaultValue = xe.GetAttribute("defaultvalue"),
ListName = ((XmlElement)xe.FirstChild).GetAttribute("name"),
ListSheetName = ((XmlElement)xe.FirstChild).GetAttribute("sheetname"),
};
SheetClass _sheetClass = new SheetClass()
{
ParentVarClass = varClass,
Name = subEle.GetAttribute("name"),
SheetName = subEle.GetAttribute("sheetname"),
MainKey = subEle.GetAttribute("mainKey"),
SplitStr = subEle.GetAttribute("split"),
Depth = depth,
};
if (!string.IsNullOrEmpty(_sheetClass.SheetName))
{
if (!nameSheetClassDic.ContainsKey(_sheetClass.SheetName))
{
foreach (XmlNode inNode in subEle.ChildNodes)
{
XmlElement inEle = (XmlElement)inNode;
VariableClass inVarClass = new VariableClass()
{
Name = inEle.GetAttribute("name"),
Col = inEle.GetAttribute("col"),
Type = inEle.GetAttribute("type"),
SplitStr = inEle.GetAttribute("split"),
Foreign = inEle.GetAttribute("foreign"),
DefaultValue = inEle.GetAttribute("defaultvalue"),
};
if (inVarClass.Type == "list")
{
inVarClass.ListName = ((XmlElement)inEle.FirstChild).GetAttribute("name");
inVarClass.ListSheetName = ((XmlElement)inEle.FirstChild).GetAttribute("sheetname");
}
_sheetClass.AllVariableList.Add(inVarClass);
}
nameSheetClassDic.Add(_sheetClass.SheetName, _sheetClass);
}
}
ReadRegXmlNode(subEle, nameSheetClassDic, depth);
}
}
}
/// <summary>
/// XmlToExcel 将sheetClass数据读取到类
/// </summary>
/// <param name="data">Xml转化的类</param>
/// <param name="sheetClass">需要读取的sheetClass</param>
/// <param name="allSheetClassDic">读取</param>
/// <param name="allSheetDataDic">储存</param>
private static void ReadData(object data, SheetClass sheetClass, Dictionary <string, SheetClass> allSheetClassDic, Dictionary <string, SheetData> allSheetDataDic, string mainKey = "")
{
VarClass parentClass = sheetClass.ParentVar;
string name = parentClass.Name;
//外部主体
object dataList = GetMemberValue(data, name);
int count = Convert.ToInt32(dataList.GetType().GetProperty("Count").GetValue(dataList));
List <VarClass> varList = sheetClass.VarList;//sheet下所有的variable
//储存name和type
SheetData sheetData = new SheetData();
if (!string.IsNullOrEmpty(parentClass.Foreign))
{
sheetData.AllName.Add(parentClass.Foreign);
sheetData.AllType.Add(parentClass.Type);
}
for (int i = 0; i < varList.Count; i++)
{
if (!string.IsNullOrEmpty(varList[i].Col))
{
//这里看看是否存 Col
sheetData.AllName.Add(varList[i].Col);
sheetData.AllType.Add(varList[i].Type);
}
}
string tempKey = mainKey;
//储存rowdata
for (int i = 0; i < count; i++)
{
//
object item = dataList.GetType().GetProperty("Item").GetValue(dataList, new object[] { i });
//反射rowdata
RowData rowData = new RowData();
if (!string.IsNullOrEmpty(parentClass.Foreign) && !string.IsNullOrEmpty(tempKey))
{
rowData.RowDataDic.Add(parentClass.Foreign, tempKey);
}
if (!string.IsNullOrEmpty(sheetClass.MainKey))
{
mainKey = GetMemberValue(item, sheetClass.MainKey).ToString();
}
//
for (int j = 0; j < varList.Count; j++)
{
VarClass curVarClass = varList[j];
if (curVarClass.Type == "list")
{
if (string.IsNullOrEmpty(curVarClass.SplitStr))
{
SheetClass temp = allSheetClassDic[curVarClass.SheetName];
ReadData(item, temp, allSheetClassDic, allSheetDataDic, mainKey);
}
else
{
SheetClass temp = allSheetClassDic[curVarClass.SheetName];
string split = GetSplitStrList(item, curVarClass, temp);
rowData.RowDataDic.Add(curVarClass.Col, split);
}
}
//xml基础list类型
else if (XML_BASE_TYPE.Contains(curVarClass.Type))
{
string split = GetSplitBaseList(item, curVarClass);
rowData.RowDataDic.Add(curVarClass.Col, split);
}
else
{
object value = GetMemberValue(item, curVarClass.Name);
if (value != null)
{
rowData.RowDataDic.Add(curVarClass.Col, value.ToString());
}
else
{
Debug.LogError("ReadData ：" + curVarClass.Name + " 反射为空 请检查配置");
}
}
}
//储存进字典(Excel表的sheet)
string key = parentClass.SheetName;
if (allSheetDataDic.ContainsKey(key))
{
allSheetDataDic[key].AllRowData.Add(rowData);
}
else
{
sheetData.AllRowData.Add(rowData);
allSheetDataDic.Add(key, sheetData);
}
}
}
/// <summary>
/// 递归读取配置
/// </summary>
/// <param name="xe"></param>
private static void ReadXmlNode(XmlElement xmlElement, Dictionary <string, SheetClass> allSheetClassDic, int depth)
{
depth++;
foreach (XmlNode node in xmlElement.ChildNodes)
{
XmlElement xe = (XmlElement)node;
if (xe.GetAttribute("type") == "list")
{
XmlElement listEle = (XmlElement)node.FirstChild;
VarClass parentVar = new VarClass()
{
Name = xe.GetAttribute("name"),
Type = xe.GetAttribute("type"),
Col = xe.GetAttribute("col"),
DeafultValue = xe.GetAttribute("defaultValue"),
Foregin = xe.GetAttribute("foregin"),
SplitStr = xe.GetAttribute("split"),
};
if (parentVar.Type == "list")
{
parentVar.ListName = ((XmlElement)xe.FirstChild).GetAttribute("name");
parentVar.ListSheetName = ((XmlElement)xe.FirstChild).GetAttribute("sheetname");
}
SheetClass sheetClass = new SheetClass()
{
Name = listEle.GetAttribute("name"),
SheetName = listEle.GetAttribute("sheetname"),
SplitStr = listEle.GetAttribute("split"),
MainKey = listEle.GetAttribute("mainKey"),
ParentVar = parentVar,
Depth = depth,
};
if (!string.IsNullOrEmpty(sheetClass.SheetName))
{
if (!allSheetClassDic.ContainsKey(sheetClass.SheetName))
{
//获取该类下面所有变量
foreach (XmlNode insideNode in listEle.ChildNodes)
{
XmlElement insideXe = (XmlElement)insideNode;
VarClass varClass = new VarClass()
{
Name = insideXe.GetAttribute("name"),
Type = insideXe.GetAttribute("type"),
Col = insideXe.GetAttribute("col"),
DeafultValue = insideXe.GetAttribute("defaultValue"),
Foregin = insideXe.GetAttribute("foregin"),
SplitStr = insideXe.GetAttribute("split"),
};
if (varClass.Type == "list")
{
varClass.ListName = ((XmlElement)insideXe.FirstChild).GetAttribute("name");
varClass.ListSheetName = ((XmlElement)insideXe.FirstChild).GetAttribute("sheetname");
}
sheetClass.VarList.Add(varClass);
}
allSheetClassDic.Add(sheetClass.SheetName, sheetClass);
}
}
ReadXmlNode(listEle, allSheetClassDic, depth);
}
}
}
//递归读取xml中的数据
private static void ReadXmlElement(XmlElement xe, Dictionary <string, SheetClass> allSheetClassDic, int depth)
{
depth++;//sheet表的深度
foreach (XmlElement item in xe.ChildNodes)
{
string type = item.GetAttribute("type");
if (type == "list")
{
XmlElement first = (XmlElement)item.FirstChild;
VarClass parentVar = new VarClass()
{
Name = item.GetAttribute("name"),
Type = item.GetAttribute("type"),
Col = item.GetAttribute("col"),
DefaultValue = item.GetAttribute("default"),
Foreign = item.GetAttribute("foreign"),
SplitStr = item.GetAttribute("split"),
};
//如果是list储存 和sheet的关联信息
if (parentVar.Type == "list")
{
parentVar.ListName = first.GetAttribute("name");
parentVar.SheetName = first.GetAttribute("sheetname");
}
string sheetName = first.GetAttribute("sheetname");
if (!string.IsNullOrEmpty(sheetName))
{
if (!allSheetClassDic.ContainsKey(sheetName))
{
//如果未储存的SheetName 则新建一个储存
SheetClass sheetClass = new SheetClass()
{
ParentVar = parentVar,
Name = first.GetAttribute("name"),
SheetName = first.GetAttribute("sheetname"),
MainKey = first.GetAttribute("mainkey"),
SplitStr = first.GetAttribute("split"),
Depth = depth,
};
//遍历包含的变量
foreach (XmlElement xmlEle in first.ChildNodes)
{
VarClass eleVar = new VarClass()
{
Name = xmlEle.GetAttribute("name"),
Type = xmlEle.GetAttribute("type"),
Col = xmlEle.GetAttribute("col"),
DefaultValue = xmlEle.GetAttribute("deafult"),
Foreign = xmlEle.GetAttribute("foreign"),
SplitStr = xmlEle.GetAttribute("split"),
};
//如果是list储存 和sheet的关联信息
if (eleVar.Type == "list")
{
eleVar.ListName = ((XmlElement)xmlEle.FirstChild).GetAttribute("name");
eleVar.SheetName = ((XmlElement)xmlEle.FirstChild).GetAttribute("sheetname");
}
sheetClass.VarList.Add(eleVar); //添加到sheet的变量list
}
allSheetClassDic.Add(sheetName, sheetClass); //储存到sheet字典
}
}
ReadXmlElement(first, allSheetClassDic, depth);
}
}
}
/// <summary>
/// 递归读取类里面的数据
/// </summary>
/// <param name="data"></param>
/// <param name="sheetClass"></param>
/// <param name="allSheetClassDic"></param>
/// <param name="sheetDataDic"></param>
private static void ReadData(object data, SheetClass sheetClass, Dictionary <string, SheetClass> allSheetClassDic, Dictionary <string, SheetData> sheetDataDic, string mainKey)
{
List <VarClass> varList = sheetClass.VarList;
VarClass varClass = sheetClass.ParentVar;
object dataList = GetMemberValue(data, varClass.Name);
int listCount = System.Convert.ToInt32(dataList.GetType().InvokeMember("get_Count", BindingFlags.Default | BindingFlags.InvokeMethod, null, dataList, new object[] { }));
SheetData sheetData = new SheetData();
if (!string.IsNullOrEmpty(varClass.Foregin))
{
sheetData.AllName.Add(varClass.Foregin);
sheetData.AllType.Add(varClass.Type);
}
for (int i = 0; i < varList.Count; i++)
{
if (!string.IsNullOrEmpty(varList[i].Col))
{
sheetData.AllName.Add(varList[i].Col);
sheetData.AllType.Add(varList[i].Type);
}
}
string tempKey = mainKey;
for (int i = 0; i < listCount; i++)
{
object item = dataList.GetType().InvokeMember("get_Item", BindingFlags.Default | BindingFlags.InvokeMethod, null, dataList, new object[] { i });
RowData rowData = new RowData();
if (!string.IsNullOrEmpty(varClass.Foregin) && !string.IsNullOrEmpty(tempKey))
{
rowData.RowDataDic.Add(varClass.Foregin, tempKey);
}
if (!string.IsNullOrEmpty(sheetClass.MainKey))
{
mainKey = GetMemberValue(item, sheetClass.MainKey).ToString();
}
for (int j = 0; j < varList.Count; j++)
{
if (varList[j].Type == "list" && string.IsNullOrEmpty(varList[j].SplitStr))
{
SheetClass tempSheetClass = allSheetClassDic[varList[j].ListSheetName];
ReadData(item, tempSheetClass, allSheetClassDic, sheetDataDic, mainKey);
}
else if (varList[j].Type == "list")
{
SheetClass tempSheetClass = allSheetClassDic[varList[j].ListSheetName];
string value = GetSplitStrList(item, varList[j], tempSheetClass);
rowData.RowDataDic.Add(varList[j].Col, value);
}
else if (varList[j].Type == "listStr" || varList[j].Type == "listFloat" || varList[j].Type == "listInt" || varList[j].Type == "listBool")
{
string value = GetSpliteBaseList(item, varList[j]);
rowData.RowDataDic.Add(varList[j].Col, value);
}
else
{
object value = GetMemberValue(item, varList[j].Name);
if (varList != null)
{
rowData.RowDataDic.Add(varList[j].Col, value.ToString());
}
else
{
Debug.LogError(varList[j].Name + "反射出来为空！");
}
}
}
string key = varClass.ListSheetName;
if (sheetDataDic.ContainsKey(key))
{
sheetDataDic[key].AllData.Add(rowData);
}
else
{
sheetData.AllData.Add(rowData);
sheetDataDic.Add(key, sheetData);
}
}
}
/// <summary>
/// 反射解析列表返回Data
/// </summary>
/// <param name="dataObj"></param>
/// <param name="sheet"></param>
/// <param name="allSheetClassDic"></param>
/// <param name="sheetData"></param>
private static void ReflectParseList(object dataObj, SheetClass sheet, Dictionary <string, SheetClass> allSheetClassDic,
ref Dictionary <string, SheetData> sheetDataDic, string foreginKey = null, string foreginValue = null)
{
SheetData sheetData = null;
if (sheetDataDic.ContainsKey(ParseDicKey(sheet)))
{
sheetData = sheetDataDic[ParseDicKey(sheet)];
}
else
{
sheetData = new SheetData();
}
if (sheetData.AllName.Count == 0)
{
if (foreginKey != null)
{
sheetData.AllName.Add(foreginKey);
}
for (int j = 0; j < sheet.VarList.Count; j++)
{
sheetData.AllName.Add(sheet.VarList[j].Col);
}
}
object list = GetMemberValue(dataObj, sheet.ParentVar.Name);
int count = System.Convert.ToInt32(list.GetType().InvokeMember("get_Count", BindingFlags.Default | BindingFlags.InvokeMethod, null,
list, new object[] { }));
object itemObj = CreatClass(sheet.Name);
for (int i = 0; i < count; i++)
{
object item = list.GetType().InvokeMember("get_Item", BindingFlags.Default | BindingFlags.InvokeMethod, null,
list, new object[] { i });
RowData data = new RowData();
if (foreginValue != null)
{
data.RowDataDic.Add(foreginKey, foreginValue);
}
for (int j = 0; j < sheet.VarList.Count; j++)
{
string value = null;
if (sheet.VarList[j].Type == "listStr" || sheet.VarList[j].Type == "listFloat" ||
sheet.VarList[j].Type == "listInt" || sheet.VarList[j].Type == "listBool")
{
value = GetSpliteBaseList(GetMemberValue(item, sheet.VarList[j].Name), sheet.VarList[j]);
}
else if (sheet.VarList[j].Type == "list" && !string.IsNullOrEmpty(sheet.VarList[j].SplitStr))
{
value = GetSpliteClassList(GetMemberValue(item, sheet.VarList[j].Name), allSheetClassDic[sheet.VarList[j].ListSheetName], sheet.VarList[j]);
}
else if (sheet.VarList[j].Type == "list" && !string.IsNullOrEmpty(sheet.VarList[j].Foregin))
{
string tempForValue = data.RowDataDic[sheet.VarList[j].Foregin];
ReflectParseList(item, allSheetClassDic[sheet.VarList[j].ListSheetName], allSheetClassDic, ref sheetDataDic, sheet.VarList[j].Foregin, tempForValue);
if (sheetData.AllName.Contains(sheet.VarList[j].Col))
{
sheetData.AllName.Remove(sheet.VarList[j].Col);
}
}
else
{
value = GetMemberValue(item, sheet.VarList[j].Name).ToString();
}
data.RowDataDic.Add(sheet.VarList[j].Col, value);
}
sheetData.AllData.Add(data);
}
if (!sheetDataDic.ContainsKey(ParseDicKey(sheet)))
{
sheetDataDic.Add(ParseDicKey(sheet), sheetData);
}
}
private static void ExcelToXml(string name)
{
string className = "";
string xmlName = "";
string excelName = "";
//第一步，读取Reg文件，确定类的结构
Dictionary <string, SheetClass> allSheetClassDic = ReadReg(name, ref excelName, ref xmlName, ref className);
//第二步，读取excel里面的数据
string excelPath = ExcelPath + 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];
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.AllName.Add(sheetClass.VarList[n].Name);
sheetData.AllType.Add(sheetClass.VarList[n].Type);
}
for (int m = 1; m < rowCount; m++)
{
RowData rowData = new RowData();
int n = 0;
if (string.IsNullOrEmpty(sheetClass.SplitStr) && sheetClass.ParentVar != null &&
!string.IsNullOrEmpty(sheetClass.ParentVar.Foregin))
{
rowData.ParnetVlue = worksheet.Cells[m + 1, 1].Value.ToString().Trim();
n = 1;
}
for (; n < colCount; n++)
{
ExcelRange range = worksheet.Cells[m + 1, n + 1];
string value = "";
if (range.Value != null)
{
value = range.Value.ToString().Trim();
}
string colValue = worksheet.Cells[1, n + 1].Value.ToString().Trim();
rowData.RowDataDic.Add(GetNameFormCol(sheetClass.VarList, colValue), value);
}
sheetData.AllData.Add(rowData);
}
sheetDataDic.Add(worksheet.Name, sheetData);
}
}
}
}
catch (Exception e)
{
Debug.LogError(e);
return;
}
//根据类的结构，创建类，并且给每个变量赋值（从excel里读出来的值）
object objClass = CreateClass(className);
List <string> outKeyList = new List <string>();
foreach (string str in allSheetClassDic.Keys)
{
SheetClass sheetClass = allSheetClassDic[str];
if (sheetClass.Depth == 1)
{
outKeyList.Add(str);
}
}
for (int i = 0; i < outKeyList.Count; i++)
{
ReadDataToClass(objClass, allSheetClassDic[outKeyList[i]], sheetDataDic[outKeyList[i]], allSheetClassDic, sheetDataDic, null);
}
BinarySerializeOpt.Xmlserialize(XmlPath + xmlName, objClass);
//BinarySerializeOpt.BinarySerilize(BinaryPath + className + ".bytes", objClass);
Debug.Log(excelName + "表导入unity完成！");
AssetDatabase.Refresh();
}
private static void ExcelToXml(string className)
{
string path = REGXMLPATH + className + ".xml";
Dictionary <string, SheetClass> allSheetDict = ReadRegXml(path);
string excelPath = EXCELPATH + className + ".xlsx";
Dictionary <string, SheetData> sheetDataDict = new Dictionary <string, SheetData>();
try
{
using (FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (ExcelPackage package = new ExcelPackage(fs))
{
ExcelWorksheets excelWorksheets = package.Workbook.Worksheets;
for (int i = 0; i < excelWorksheets.Count; i++)
{
ExcelWorksheet worksheet = excelWorksheets[i + 1];
SheetData sheetData = new SheetData();
SheetClass sheetClass = allSheetDict[worksheet.Name];
for (int j = 0; j < sheetClass.VarList.Count; j++)
{
sheetData.AllName.Add(sheetClass.VarList[j].Name);
sheetData.AllType.Add(sheetClass.VarList[j].Type);
}
int rowCount = worksheet.Dimension.End.Row;
int colCount = worksheet.Dimension.End.Column;
for (int m = 1; m < rowCount; m++)
{
RowData rowData = new RowData();
int n = 0;
if (!string.IsNullOrEmpty(sheetClass.ParentVar.Foreign))
{
rowData.ForeignValue = worksheet.Cells[m + 1, 1].Value.ToString().Trim();
n = 1;
}
for (; n < colCount; n++)
{
ExcelRange range = worksheet.Cells[m + 1, n + 1];
string propertyName = GetNameByCol(sheetClass.VarList, worksheet.Cells[1, n + 1].Value.ToString().Trim());
string value = range.Value.ToString().Trim();
if (string.IsNullOrEmpty(value))
{
Debug.LogError("Excel存在数据为空:[" + (m + 1) + "," + (n + 1) + "]");
continue;
}
rowData.RowDataDict.Add(propertyName, value);
}
sheetData.AllRowData.Add(rowData);
}
sheetDataDict.Add(worksheet.Name, sheetData);
}
}
}
}
catch (Exception e)
{
Debug.LogError(e);
return;
}
Type type = GetType(className);
object objClass = null;
if (type != null)
{
objClass = System.Activator.CreateInstance(type);
}
List <string> outKeyList = new List <string>();
foreach (string key in allSheetDict.Keys)
{
if (allSheetDict[key].Depth == 1)
{
outKeyList.Add(key);
}
}
for (int i = 0; i < outKeyList.Count; i++)
{
string key = outKeyList[i];
ReadDataToClass(objClass, allSheetDict[key], sheetDataDict[key], allSheetDict, sheetDataDict, null);
}
Serialize.XmlSerialize(XMLPATH + className + ".xml", objClass);
Debug.Log(excelPath + "表导入Unity成功！");
AssetDatabase.Refresh();
}
/// <summary>
/// 读取xml配置表中所有节点
/// </summary>
/// <param name="xmlElement"></param>
/// <param name="allSheetDict"></param>
/// <param name="depth"></param>
private static void ReadXmlNode(XmlElement xmlElement, Dictionary <string, SheetClass> allSheetDict, int depth)
{
depth++;
foreach (XmlNode tempNode in xmlElement.ChildNodes)
{
XmlElement tempElement = tempNode as XmlElement;
string type = tempElement.GetAttribute("type");
if (type == "list")
{
XmlElement listEle = tempElement.FirstChild as XmlElement;
VarClass parentVar = new VarClass
{
Name = tempElement.GetAttribute("name"),
Type = tempElement.GetAttribute("type"),
Col = tempElement.GetAttribute("col"),
DeafultValue = tempElement.GetAttribute("defultvalue"),
Foreign = tempElement.GetAttribute("foreign"),
SplitStr = tempElement.GetAttribute("split"),
};
if (parentVar.Type == "list")
{
parentVar.ListName = listEle.GetAttribute("name");
parentVar.ListSheetName = listEle.GetAttribute("sheetname");
}
SheetClass sheet = new SheetClass
{
ParentVar = parentVar,
Name = listEle.GetAttribute("name"),
SheetName = listEle.GetAttribute("sheetname"),
MainKey = listEle.GetAttribute("mainkey"),
SplitStr = listEle.GetAttribute("split"),
Depth = depth,
};
if (!string.IsNullOrEmpty(sheet.SheetName))
{
if (!allSheetDict.ContainsKey(sheet.SheetName))
{
sheet.VarList = new List <VarClass>();
foreach (XmlNode node in listEle.ChildNodes)
{
XmlElement xe = node as XmlElement;
VarClass varClass = new VarClass
{
Name = xe.GetAttribute("name"),
Type = xe.GetAttribute("type"),
Col = xe.GetAttribute("col"),
DeafultValue = xe.GetAttribute("defultvalue"),
Foreign = xe.GetAttribute("foreign"),
SplitStr = xe.GetAttribute("split"),
};
if (varClass.Type == "list")
{
varClass.ListName = ((XmlElement)xe.FirstChild).GetAttribute("name");
varClass.ListSheetName = ((XmlElement)xe.FirstChild).GetAttribute("sheetname");
}
sheet.VarList.Add(varClass);
}
allSheetDict.Add(sheet.SheetName, sheet);
}
}
ReadXmlNode(listEle, allSheetDict, depth);
}
else
{
}
}
}
private static void ExcelToXml(string name)
{
string className = "";
string from = "";
string to = "";
//储存所有变量 sheet名 SheetClass
Dictionary <string, SheetClass> allSheetClassDic = ReadReg(name, ref className, ref from, ref to);
//储存所有data sheet名 sheetdata
Dictionary <string, SheetData> allSheetDataDic = new Dictionary <string, SheetData>();
string excelPath = EXCEL_DATA_PATH + from;
// string xmlPath = XML_DATA_PATH+to;
//读取excel文件
try{
using (FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
using (ExcelPackage package = new ExcelPackage(fs)){
ExcelWorksheets workSheets = package.Workbook.Worksheets;
for (int i = 1; i <= workSheets.Count; i++)
{
ExcelWorksheet workSheet = workSheets[i];
int colCount = workSheet.Dimension.End.Column;
int rowCount = workSheet.Dimension.End.Row;
SheetData sheetData = new SheetData();//储存这一页的信息
//Reg中sheet的信息
SheetClass sheetClass = allSheetClassDic[workSheet.Name];
for (int j = 0; j < sheetClass.VarList.Count; j++)
{
VarClass curVarClass = sheetClass.VarList[j];
//储存 变量名 类型
sheetData.AllName.Add(curVarClass.Name);
sheetData.AllType.Add(curVarClass.Type);
}
//读取excel中的数据
//第一行是标题 所以跳过
for (int row = 1; row < rowCount; row++)
{
RowData data = new RowData();
int col = 0;
//如果这页sheet是外键数据 则第一列对应mainKey 数据从第二列开始
if (string.IsNullOrEmpty(sheetClass.SplitStr) && !string.IsNullOrEmpty(sheetClass.ParentVar.Foreign))
{
data.ParentKey = workSheet.Cells[row + 1, 1].Value.ToString().Trim();
col = 1;
}
for (; col < colCount; col++)
{
//每一行的信息
ExcelRange range = workSheet.Cells[row + 1, col + 1];
string colName = workSheet.Cells[1, col + 1].Value.ToString();
string value = range.Value != null?range.Value.ToString().Trim() : "";
data.RowDataDic.Add(GetNameFormCol(sheetClass.VarList, colName), value);
}
sheetData.AllRowData.Add(data);
}
allSheetDataDic.Add(workSheet.Name, sheetData);
}
}
}
catch (System.Exception e) {
Debug.LogError("XmlToExcel:Excel写入错误 " + e);
return;
}
//创建类 并赋值
object objClass = CreateClass(className);
List <string> outKeyList = new List <string>();
foreach (var item in allSheetClassDic)
{
SheetClass sheetClass = item.Value;
if (sheetClass.Depth == 1)
{
outKeyList.Add(item.Key);
}
}
for (int i = 0; i < outKeyList.Count; i++)
{
string key = outKeyList[i];
ReadDataToClass(objClass, allSheetClassDic[key], allSheetDataDic[key], allSheetClassDic, allSheetDataDic);
}
string xmlPath = XML_DATA_PATH + name + ".xml";
object obj = BinarySerializeOpt.XmlSerialize(xmlPath, objClass);
//转成二进制
// BinarySerializeOpt.BinarySerialize(BINARY_DATA_PATH, obj);
Debug.Log("Excel转Xml完成！" + from + "-->" + to);
AssetDatabase.Refresh();
}
private static void ExcelToXml(string regName)
{
string regPath = PathConfig.OuterDataRegPath + regName;
if (!File.Exists(regPath))
{
Debug.LogError("reg文件不存在" + regPath);
return;
}
XmlDocument xmlDoc = new XmlDocument();
XmlReader reader = XmlReader.Create(regPath);
xmlDoc.Load(reader);
XmlNode rootNode = xmlDoc.SelectSingleNode("data");
XmlElement rootEle = (XmlElement)rootNode;
string className = rootEle.GetAttribute("name");
string excelName = rootEle.GetAttribute("from");
string xmlName = rootEle.GetAttribute("to");
Dictionary <string, SheetClass> nameSheetClassDic = new Dictionary <string, SheetClass>();
ReadRegXmlNode(rootEle, nameSheetClassDic, 0);
Dictionary <string, SheetData> _nameSheetDataDic = new Dictionary <string, SheetData>();
string excelPath = PathConfig.OuterDataExcelPath + excelName;
//读取excel的数据
using (FileStream fs = File.Open(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) {
using (ExcelPackage package = new ExcelPackage(fs)) {
ExcelWorksheets sheets = package.Workbook.Worksheets;
for (int i = 0; i < sheets.Count; i++)
{
//注意从excel读取/写入数据时，下标是从1开始的
ExcelWorksheet workSheet = sheets[i + 1];
SheetData _shtData = new SheetData();
SheetClass _shtClass = nameSheetClassDic[workSheet.Name];
int colCount = workSheet.Dimension.End.Column;
int rowCount = workSheet.Dimension.End.Row;
for (int m = 0; m < _shtClass.AllVariableList.Count; m++)
{
//保存变量名和类型
_shtData.AllNameList.Add(_shtClass.AllVariableList[m].Name);
_shtData.AllTypeList.Add(_shtClass.AllVariableList[m].Type);
}
//保存每一行的数据，从第三行开始是实际的数据
for (int m = 3; m <= rowCount; m++)
{
RowData _rowData = new RowData();
int n = 1;
if (_shtClass.ParentVarClass != null && !string.IsNullOrEmpty(_shtClass.ParentVarClass.Foreign))
{
//如果是子表就从第二列开始读取数据
_shtData.IsSubSheet = true;
_rowData.ForeignKey = workSheet.Cells[m, 1].Value.ToString().Trim();
n = 2;
}
for (; n <= colCount; n++)
{
string key = workSheet.Cells[1, n].Value.ToString().Trim();
string value = "";
if (workSheet.Cells[m, n].Value != null)
{
value = workSheet.Cells[m, n].Value.ToString().Trim();
}
_rowData.OneRowDataDic.Add(key, value);
}
_shtData.AllRowDataList.Add(_rowData);
}
_nameSheetDataDic.Add(workSheet.Name, _shtData);
}
}
}
//写入到类中
object classObj = CreateClassObjByName(className);
if (classObj != null)
{
//首先获取到外层表的表名
List <string> outerKeyList = new List <string>();
foreach (string str in nameSheetClassDic.Keys)
{
SheetClass _shtClass = nameSheetClassDic[str];
if (_shtClass.Depth == 1)
{
outerKeyList.Add(str);
}
}
foreach (string str in outerKeyList)
{
WriteToClass(classObj, str, nameSheetClassDic, _nameSheetDataDic, "");
}
//xml序列化
GameDataConvert.ClassToXml(classObj);
AssetDatabase.Refresh();
}
}
public static void ExcelToXml(string xmlRegName)
{
string className = "";
string excelName = "";
string xmlName = "";
//读取reg文件数据
Dictionary <string, SheetClass> allSheetClassDic = ReadReg(xmlRegName, ref className, ref excelName, ref xmlName);
//读取excel文件数据
string excelPath = EXCELPATH + excelName;
Dictionary <string, SheetData> allSheetDataDic = new Dictionary <string, SheetData>();
try
{
using (FileStream stream = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (ExcelPackage excelPackage = new ExcelPackage(stream))
{
ExcelWorksheets worksheetArray = excelPackage.Workbook.Worksheets;
for (int i = 0; i < worksheetArray.Count; i++)
{
SheetData sheetData = new SheetData();
ExcelWorksheet worksheet = worksheetArray[i + 1];//索引从1开始
string key = GetDicKey(allSheetClassDic, worksheet.Name);
if (string.IsNullOrEmpty(key))
{
Debug.Log("配置表在reg文件中不存在!请检查");
return;
}
SheetClass sheetClass = allSheetClassDic[key];
int colCount = worksheet.Dimension.End.Column;
int rowCount = worksheet.Dimension.End.Row;
for (int j = 0; j < sheetClass.VarList.Count; j++)
{
sheetData.AllName.Add(sheetClass.VarList[j].Name);
sheetData.AllType.Add(sheetClass.VarList[j].Type);
}
for (int j = 1; j < rowCount; j++)
{
RowData rowData = new RowData();
int k = 0;
if (string.IsNullOrEmpty(sheetClass.SplitStr) && sheetClass.ParentVar != null &&
!string.IsNullOrEmpty(sheetClass.ParentVar.Foregin))
{
rowData.parentValue = worksheet.Cells[j + 1, 1].Value.ToString().Trim();
k = 1;
}
for (; k < colCount; k++)
{
ExcelRange range = worksheet.Cells[j + 1, k + 1];//索引从1开始
string value = "";
if (range.Value != null)
{
value = range.Value.ToString().Trim();
}
string colValue = worksheet.Cells[1, k + 1].Value.ToString().Trim();
rowData.RowDataDic.Add(GetNameFormCol(sheetClass.VarList, colValue), value);
}
sheetData.AllData.Add(rowData);
}
allSheetDataDic.Add(worksheet.Name, sheetData);
}
}
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
//根据类结构创建类，并将execl数据将变量赋值，然后调用xml序列化
object objClass = CreatClass(className);
List <SheetClass> outKeyList = new List <SheetClass>();
foreach (string key in allSheetClassDic.Keys)
{
SheetClass sheet = allSheetClassDic[key];
if (sheet.Depth == 1)
{
outKeyList.Add(sheet);
}
}
for (int i = 0; i < outKeyList.Count; i++)
{
ReadDataToClass(objClass, allSheetClassDic[ParseDicKey(outKeyList[i])],
allSheetDataDic[outKeyList[i].SheetName], allSheetClassDic, allSheetDataDic, null);
}
BinarySerializeOpt.XmlSerialize(XMLPATH + xmlName, objClass);
Debug.Log(excelName + "表导入完成！");
AssetDatabase.Refresh();
}
private static void WriteToClass(object classObj, string shtName, Dictionary <string, SheetClass> nameSheetClassDic,
Dictionary <string, SheetData> nameSheetDataDic, string mainKeyValue)
{
SheetClass shtClass = nameSheetClassDic[shtName];
SheetData shtData = nameSheetDataDic[shtName];
object tempObj = CreateClassObjByName(shtClass.Name);
object listObj = CreateListByType(tempObj.GetType());
for (int i = 0; i < shtData.AllRowDataList.Count; i++)
{
if (!string.IsNullOrEmpty(shtData.AllRowDataList[i].ForeignKey))
{
if (shtData.AllRowDataList[i].ForeignKey != mainKeyValue)
{
//如果是子表，并且这个子表的ForeignKey不等于主表的mainKey，就continue
//也就是不写进tObj中
continue;
}
}
object tObj = CreateClassObjByName(shtClass.Name);
for (int j = 0; j < shtClass.AllVariableList.Count; j++)
{
VariableClass varClass = shtClass.AllVariableList[j];
if (varClass.Type.Trim() == "list")
{
if (string.IsNullOrEmpty(varClass.SplitStr))
{
string tmpMainKey = GetMemberValue(tObj, shtClass.MainKey).ToString();
WriteToClass(tObj, varClass.ListSheetName, nameSheetClassDic, nameSheetDataDic, tmpMainKey);
}
else
{
//用分隔符保存的excel数据
SheetClass tmpShtClass = nameSheetClassDic[varClass.ListSheetName];
object customClassObj = CreateClassObjByName(tmpShtClass.Name);
object customListObj = CreateListByType(customClassObj.GetType());
string tmpStr = shtData.AllRowDataList[i].OneRowDataDic[varClass.Col];
if (string.IsNullOrEmpty(tmpStr))
{
Debug.LogError("excel中没有值，对应列名： " + varClass.Col);
return;
}
string[] tmpStrArray = tmpStr.Split('\n');
for (int tmpI = 0; tmpI < tmpStrArray.Length; tmpI++)
{
string oneStr = tmpStrArray[tmpI];
string[] oneStrArray = oneStr.Split(new string[] { varClass.SplitStr.Trim() }, StringSplitOptions.None);
object oneCusClassObj = CreateClassObjByName(tmpShtClass.Name);
for (int oneI = 0; oneI < oneStrArray.Length; oneI++)
{
PropertyInfo tmpInfo = oneCusClassObj.GetType().GetProperty(tmpShtClass.AllVariableList[oneI].Name);
SetValueByType(tmpInfo, oneCusClassObj, oneStrArray[oneI], tmpShtClass.AllVariableList[oneI].Type);
}
customListObj.GetType().InvokeMember("Add", BindingFlags.Default | BindingFlags.InvokeMethod,
null, customListObj, new object[] { oneCusClassObj });
}
tObj.GetType().GetProperty(varClass.Name).SetValue(tObj, customListObj, new object[] { });
}
}
else if (varClass.Type == "listStr" || varClass.Type == "listInt" ||
varClass.Type == "listFloat" || varClass.Type == "listBool")
{
Type _tmpType = GetBaseTypeByType(varClass.Type);
if (_tmpType == null)
{
Debug.LogError(varClass.Type + " 在已存在的类型中没有找到");
return;
}
object inlistObj = CreateListByType(_tmpType);
string invalue = shtData.AllRowDataList[i].OneRowDataDic[varClass.Col];
if (string.IsNullOrEmpty(invalue))
{
Debug.LogError("excel中没有值，对应列名： " + varClass.Col);
return;
}
string[] strArray = invalue.Split(System.Convert.ToChar(varClass.SplitStr));
for (int strcount = 0; strcount < strArray.Length; strcount++)
{
string inlistValue = strArray[strcount].Trim();
if (!string.IsNullOrEmpty(inlistValue))
{
inlistObj.GetType().InvokeMember("Add", BindingFlags.Default | BindingFlags.InvokeMethod,
null, inlistObj, new object[] { inlistValue });
}
else
{
Debug.Log("基础数据类型的List的值为null");
}
}
tObj.GetType().GetProperty(varClass.Name).SetValue(tObj, inlistObj, new object[] { });
}
else
{
PropertyInfo pInfo = tObj.GetType().GetProperty(varClass.Name);
string value = shtData.AllRowDataList[i].OneRowDataDic[varClass.Col];
if (string.IsNullOrEmpty(value) && !string.IsNullOrEmpty(varClass.DefaultValue))
{
value = varClass.DefaultValue;
}
if (string.IsNullOrEmpty(value))
{
Debug.LogError(string.Format("当前列< {0} >下的值是空的，并且reg文件中也没有设置默认值", varClass.Col));
return;
}
SetValueByType(pInfo, tObj, value, shtData.AllTypeList[j]);
}
}
listObj.GetType().InvokeMember("Add", BindingFlags.Default | BindingFlags.InvokeMethod,
null, listObj, new object[] { tObj });
}
classObj.GetType().GetProperty(shtClass.ParentVarClass.Name).SetValue(classObj, listObj, new object[] { });
}
/// <summary>
/// 读取xml中所有表数据
/// </summary>
/// <param name="data"></param>
/// <param name="sheetClass"></param>
/// <param name="allSheetDict"></param>
/// <param name="sheetDataDict"></param>
private static void ReadSheetData(object data, SheetClass sheetClass, Dictionary <string, SheetClass> allSheetDict, Dictionary <string, SheetData> sheetDataDict, string mainKey)
{
List <VarClass> varList = sheetClass.VarList;
object dataList = GetMemberValue(data, sheetClass.ParentVar.Name);
int listCount = System.Convert.ToInt32(dataList.GetType().InvokeMember("get_Count", BindingFlags.Default | BindingFlags.InvokeMethod, null, dataList, new object[] { }));
SheetData sheetData = new SheetData();
if (!string.IsNullOrEmpty(sheetClass.ParentVar.Foreign))
{
sheetData.AllName.Add(sheetClass.ParentVar.Foreign);
sheetData.AllType.Add(sheetClass.ParentVar.Type);
}
for (int i = 0; i < varList.Count; i++)
{
if (!string.IsNullOrEmpty(varList[i].Col))
{
sheetData.AllName.Add(varList[i].Col);
sheetData.AllType.Add(varList[i].Type);
}
}
string tempKey = mainKey;
for (int i = 0; i < listCount; i++)
{
object item = dataList.GetType().InvokeMember("get_Item", BindingFlags.Default | BindingFlags.InvokeMethod, null, dataList, new object[] { i });
RowData rowData = new RowData();
if (!string.IsNullOrEmpty(sheetClass.ParentVar.Foreign) && !string.IsNullOrEmpty(tempKey))
{
rowData.RowDataDict.Add(sheetClass.ParentVar.Foreign, tempKey);
}
if (!string.IsNullOrEmpty(sheetClass.MainKey))
{
mainKey = GetMemberValue(item, sheetClass.MainKey).ToString();
}
for (int j = 0; j < varList.Count; j++)
{
if (varList[j].Type == "list")
{
SheetClass tempSheet = allSheetDict[varList[j].ListSheetName];
ReadSheetData(item, tempSheet, allSheetDict, sheetDataDict, mainKey);
}
else if (varList[j].Type.Contains("list") && varList[j].Type != "list")
{
string value = GetListBaseStr(item, varList[j]);
rowData.RowDataDict.Add(varList[j].Col, value);
}
else
{
object value = GetMemberValue(item, varList[j].Name);
if (value != null)
{
rowData.RowDataDict.Add(varList[j].Col, value.ToString());
}
else
{
Debug.LogError(varList[j].Name + "反射出来为空，请查看配置表！");
}
}
}
if (sheetDataDict.ContainsKey(sheetClass.SheetName))
{
sheetDataDict[sheetClass.SheetName].AllRowData.Add(rowData);
}
else
{
sheetData.AllRowData.Add(rowData);
sheetDataDict.Add(sheetClass.SheetName, sheetData);
}
}
}