通过之前写的MonsterDataReg.xml这个reg文件，把我们的MonsterData.xml映射进一个Excel里面，其实本质上就是MonsterData类映射到Excel。
创建读取Reg.Xml的中间类
在DataEditor中添加VarClass中间类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
public class VarClass
{
public string Name { get; set; }
public string Type { get; set; }
public string Col { get; set; }
public string DefaultValue { get; set; }
public string Foreign { get; set; }
public string Split { get; set; }
public string ListClassName { get; set; }
public string ListSheetName { get; set; }
}
|
关于这些变量的含义请参考“类与Excel转换”那一节
在DataEditor中添加SheetClass中间类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
public class SheetClass
{
public VarClass ParentVar { get;set; }
public int Depth { get; set; }
public string Name { get; set; }
public string SheetName { get; set; }
public string MainKey { get; set; }
public string Split { get; set; }
public List<VarClass> VarList = new List<VarClass>();
}
|
关于这些变量的含义请参考“类与Excel转换”那一节
创建写入Excel的中间类
在DataEditor中添加SheetData和RowData中间类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
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>();
}
|
Reg.xml的读取
这里我们封装一个递归读取Reg.xml的方法，因为xml本身是一个树结构，所以利用递归来读取。
在DataEditor中添加ReadRegXmlNode方法，递归读取RegXml，并根据里面的list结点的信息把sheet输出到allSheetClassDic字典里
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
|
private static void ReadRegXmlNode(XmlElement xmlElement,Dictionary<string,SheetClass> allSheetClassDic,int depth)
{
depth++;
foreach (XmlElement xmlEle in xmlElement.ChildNodes)
{
if (xmlEle.GetAttribute("type") == "list")
{
XmlElement listEle = xmlEle.FirstChild as XmlElement;
VarClass parentVar = new VarClass()
{
Name = xmlEle.GetAttribute("name"),
Type = xmlEle.GetAttribute("type"),
Col = xmlEle.GetAttribute("col"),
DefaultValue = xmlEle.GetAttribute("defaultValue"),
Foreign = xmlEle.GetAttribute("foreign"),
Split = xmlEle.GetAttribute("split"),
ListClassName = listEle.GetAttribute("name"),
ListSheetName = listEle.GetAttribute("sheetname"),
};
SheetClass sheetClass = new SheetClass()
{
ParentVar = parentVar,
Depth = depth,
Name = listEle.GetAttribute("name"),
SheetName = listEle.GetAttribute("sheetname"),
Split = listEle.GetAttribute("split"),
MainKey = listEle.GetAttribute("mainKey"),
};
if (!string.IsNullOrEmpty(sheetClass.SheetName))
{
if (!allSheetClassDic.ContainsKey(sheetClass.SheetName))
{
foreach (XmlElement listChildEle in listEle.ChildNodes)
{
VarClass childVar = new VarClass()
{
Name = listChildEle.GetAttribute("name"),
Type = listChildEle.GetAttribute("type"),
Col = listChildEle.GetAttribute("col"),
DefaultValue = listChildEle.GetAttribute("defaultValue"),
Foreign = xmlEle.GetAttribute("foreign"),
Split = listChildEle.GetAttribute("split")
};
if (childVar.Type == "list")
{
childVar.ListClassName = ((XmlElement)listChildEle.FirstChild).GetAttribute("name");
childVar.ListSheetName = ((XmlElement)listChildEle.FirstChild).GetAttribute("sheetname");
}
sheetClass.VarList.Add(childVar);
}
allSheetClassDic.Add(sheetClass.SheetName, sheetClass);
}
}
ReadRegXmlNode(listEle, allSheetClassDic,depth);
}
}
}
|
目前的Reg.xml读取方式只支持外层节点（也就是根节点“data”内部的第一层子节点）type必须都是“list”的情况，所以我们写的数据类MonsterData里面的最外层的变量都是list（MonsterData见“配置表准备”一节），我们必须准备一个方法像递归查询Reg.xml一样递归读取从MonsterData.xml反序列化出来的MonsterData类，就是下面的数据类读取的MapDataToSheet方法
在DataEditor中添加ReadRegXmlNode方法，这个方法用来读取Reg.xml并返回赋值好的allSheetClassDic字典
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
| private static Dictionary<string, SheetClass> ReadRegToDic(string xmlRegName, ref string excelName, ref string xmlName, ref string className)
{
string regPath = RegPath + xmlRegName + ".xml";
if (!File.Exists(regPath))
{
Debug.LogErrorFormat("指定的XmlReg不存在：{0}", xmlRegName);
return null;
}
XmlDocument xmlDocument = new XmlDocument();
XmlReaderSettings xmlReaderSettings = new XmlReaderSettings();
xmlReaderSettings.IgnoreComments = true;
XmlReader xmlReader = XmlReader.Create(regPath, xmlReaderSettings);
xmlDocument.Load(xmlReader);
XmlNode rootNode = xmlDocument.SelectSingleNode("data");
XmlElement rootEle = rootNode as XmlElement;
className = rootEle.GetAttribute("name");
excelName = rootEle.GetAttribute("from");
xmlName = rootEle.GetAttribute("to");
Dictionary<string, SheetClass> allSheetClassDic = new Dictionary<string, SheetClass>();
ReadRegXmlNode(rootEle, allSheetClassDic, 0);
xmlReader.Close();
return allSheetClassDic;
}
|
数据类的读取
将Reg.xml读取出来后，我们也要将反序列化后的有一定原始数据的数据类也读取出来，同样地，数据类可能有list的嵌套，所以我们也要递归读取，将读取出来的数据都写进SheetData中间类里，并保存在sheetDataDic字典中
在DataEditor中添加MapDataToSheet方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
|
private static void MapDataToSheet(object data, SheetClass rootSheet, Dictionary<string, SheetClass> sheetClassDic, Dictionary<string, SheetData> sheetDataDic)
{
List<VarClass> childVars = rootSheet.VarList;
VarClass parentVar = rootSheet.ParentVar;
object dataList = GetMemberValue(data, parentVar.Name);
int listCount =System.Convert.ToInt32(dataList.GetType().InvokeMember("get_Count", BindingFlags.Default | BindingFlags.InvokeMethod, null, dataList, new object[] { }));
SheetData sheetData = new SheetData();
childVars.ForEach(item =>
{
if (!string.IsNullOrEmpty(item.Col))
{
sheetData.AllColName.Add(item.Col);
sheetData.AllType.Add(item.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 rowData = new RowData();
childVars.ForEach(childVar =>
{
if(childVar.Type == "list")
{
SheetClass tempSheetClass = sheetClassDic[childVar.ListSheetName];
MapDataToSheet(item, tempSheetClass, sheetClassDic, sheetDataDic);
}
else
{
object value = GetMemberValue(item, childVar.Name);
if(value != null)
{
rowData.RowDataDic.Add(childVar.Col,value.ToString());
}
else
{
Debug.LogError(childVar.Name + "反射为空，请检查Reg.xml是否出错");
}
}
});
string key = parentVar.ListSheetName;
if(sheetDataDic.ContainsKey(key))
{
sheetDataDic[key].AllRowData.Add(rowData);
}
else
{
sheetData.AllRowData.Add(rowData);
sheetDataDic.Add(key, sheetData);
}
}
}
|
xml转Excel
有了上面的准备，就可以将xml转Excel了
基本流程是
- 首先将对应的Reg.xml文件读取出来，确定写入规则，在这一步我们将得到一个sheetClass字典，以及此reg文件对应的数据类名（className）、Excel文件名（excelName）、xml文件名（xmlName）
- 将xml反序列化，得到一个有初始数据的数据类。
- 配合sheetClass读取数据类，将读取出来的结果写入sheetData字典。
- 将sheetData字典的内容写入Excel
在DataEditor中添加XmlToExcel方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
| [MenuItem("Assets/Xml转Excel")]
public static void AssetsXmlToExcel()
{
UnityEngine.Object[] objs = Selection.objects;
for (int i = 0; i < objs.Length; i++)
{
EditorUtility.DisplayProgressBar("已经选中的xml转成Excel", "正在扫描" + objs[i].name + "……", 1.0f / objs.Length * i);
XmlToExcel(objs[i].name + "Reg");
}
EditorUtility.ClearProgressBar();
}
private static void XmlToExcel(string xmlRegName)
{
string className = string.Empty;
string excelName = string.Empty;
string xmlName = string.Empty;
Dictionary<string, SheetClass> allSheetClassDic = ReadRegToDic(xmlRegName,ref excelName,ref xmlName,ref className);
object data = GetObjFromXml(className);
List<SheetClass> rootSheets = new List<SheetClass>();
foreach (SheetClass sheets in allSheetClassDic.Values)
{
if (sheets.Depth == 1)
{
rootSheets.Add(sheets);
}
}
Dictionary<string, SheetData> sheetDataDic = new Dictionary<string, SheetData>();
rootSheets.ForEach(sheet => { MapDataToSheet(data, sheet,allSheetClassDic,sheetDataDic); });
string excelPath = ExcelPath + excelName;
if (FileIsUsed(excelPath))
{
Debug.LogErrorFormat("{0} Excel文件已经被占用，无法修改",excelName);
return;
}
try
{
FileInfo excelInfo = new FileInfo(excelPath);
if(excelInfo.Exists)
{
excelInfo.Delete();
excelInfo = new FileInfo(excelPath);
}
using(ExcelPackage package = new ExcelPackage(excelInfo))
{
foreach (string sheetName in sheetDataDic.Keys)
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sheetName);
SheetData sheetData = sheetDataDic[sheetName];
for (int i = 0; i < sheetData.AllColName.Count; i++)
{
ExcelRange range = worksheet.Cells[1, i + 1];
range.Value = sheetData.AllColName[i];
range.AutoFitColumns();
}
for (int i = 0; i < sheetData.AllRowData.Count; i++)
{
RowData rowData = sheetData.AllRowData[i];
for (int j = 0; j < rowData.RowDataDic.Count; j++)
{
ExcelRange range = worksheet.Cells[i + 2, j + 1];
string value = rowData.RowDataDic[sheetData.AllColName[j]];
range.Value = value;
range.AutoFitColumns();
if (value.Contains("\n") || value.Contains("\r\n"))
{
range.Style.WrapText = true;
}
}
}
worksheet.Cells.AutoFitColumns();
}
package.Save();
}
}
catch (Exception e)
{
Debug.LogError(e.Message);
return;
}
Debug.Log("生成" + excelPath + "成功");
}
private static bool FileIsUsed(string path)
{
bool result = false;
if(!File.Exists(path))
{
result = false;
}
else
{
FileStream fs = null;
try
{
fs = File.Open(path, FileMode.Open, FileAccess.ReadWrite, FileShare.None);
result = false;
}
catch (Exception e)
{
Debug.LogError(e);
result = true;
}
finally
{
if (fs != null)
{
fs.Close();
}
}
}
return result;
}
|
