/// <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);
}
private static void ReadDataToClass(object objClass, SheetClass sheetClass, SheetData sheetData, Dictionary <string, SheetClass> allSheetClassDic, Dictionary <string, SheetData> sheetDataDic, object keyValue)
{
object item = CreateClass(sheetClass.Name);//只是为了得到变量类型
object list = CreateList(item.GetType());
for (int i = 0; i < sheetData.AllData.Count; i++)
{
if (keyValue != null && !string.IsNullOrEmpty(sheetData.AllData[i].ParnetVlue))
{
if (sheetData.AllData[i].ParnetVlue != keyValue.ToString())
{
continue;
}
}
object addItem = CreateClass(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], sheetDataDic[varClass.ListSheetName], allSheetClassDic, sheetDataDic, 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" || varClass.Type == "listUshort")
{
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.DefaultValue))
{
value = varClass.DefaultValue;
}
if (string.IsNullOrEmpty(value))
{
Debug.LogError("表格中有空数据，或者Reg文件未配置defaultValue！" + sheetData.AllName[j]);
continue;
}
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="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"),
DefaultValue = 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"),
DefaultValue = 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);
}
}
}
/// <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" || varList[j].Type == "listUshort")
{
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);
}
}
}
private static void ExcelToXml(string name)
{
Dictionary <string, SheetData> sheetDataDic = new Dictionary <string, SheetData>();
string className = "";
string xmlName = "";
string excelName = "";
//第一步，读取Reg文件，确定类的结构
Dictionary <string, SheetClass> allSheetClassDic = ReadReg(name, ref excelName, ref xmlName, ref className);
//第二步，读取excel里面的数据
string excelPath = ExcelPath + excelName;
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();
if (string.IsNullOrEmpty(GetNameFormCol(sheetClass.VarList, colValue)))
{
continue;
}
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();
}
