之前配置表中用的Excel,然后转成ProtoBuffer,这个格式虽然跨平台,但限制也不少,而且对U3D不友好,所以考虑是否有其他替代方案。网上搜了之后发现FlatBuffer不错,所以就想把配置表的数据转换成FlatBuffer。思路和实现步骤如下:
1. 读取Excel
目前比较流行的是NPOI(以前用OLEDB,但Eexcel升级到365之后就不好用了!),所以最好是使用NPOI读取,代码如下:
private static void ReadExcelNPOI() {
string[] files = Directory.GetFiles(_excelPath, "*.xlsx");
foreach (string file in files) {
string excelName = Path.GetFileNameWithoutExtension(file);
// 正在编辑的Excel文件会生成一个临时文件,并以~$开头
if (excelName.StartsWith("~$"))
continue;
XSSFWorkbook wk = new XSSFWorkbook(file);
int count = wk.Count;
for(int n = 0; n < count; ++n) {
ISheet sheet = wk[n];
string sheetName = sheet.SheetName;
// 忽略描述页
if (sheetName == "description")
continue;
if (!sheetName.EndsWith("Config")) {
Console.WriteLine(string.Format("\n{0}.xlsx中的{1}标签必须以Config结尾!", excelName, sheetName));
continue;
}
ExcelData data = new ExcelData();
data.excelName = excelName;
data.sheetName = sheetName;
#if USE_NPOI
data.sheet = sheet;
#endif
_excelDatas.Add(data);
}
}
}
2. 根据数据表信息生成FlatBuffer使用的fbs文件(用来生成不同语言的代码)
这一步的思路是每个表对应一个fbs文件,先根据表信息生成对应的fbs数据结构,再添加一个该数据结构的数组表示整个数据表。代码如下:
private void GenFBS() {
Console.WriteLine("\n----------生成FBS文件----------");
foreach (ExcelData data in _excels) {
string fileName = AppConfigs.Instance.FBSPath + "/" + data.sheetName + ".fbs";
EnsureDirectory(AppConfigs.Instance.FBSPath);
StringBuilder sb = new StringBuilder();
string dataClass = "Single" + data.sheetName + "Data";
sb.Append("namespace Config;" + NewLine);
sb.Append(NewLine);
sb.Append("table " + data.sheetName + "{" + NewLine);
sb.Append(Tab + "data:[" + dataClass + "];" + NewLine);
sb.Append("}" + NewLine);
sb.Append(NewLine);
sb.Append("table " + dataClass + "{" + NewLine);
foreach (ExcelFieldInfo info in data.filedInfos.Values) {
sb.Append(Tab + info.name + ":" + info.type + ";" + NewLine);
}
sb.Append("}" + NewLine);
sb.Append(NewLine);
sb.Append("root_type " + data.sheetName + ";" + NewLine);
sb.Append("file_identifier \"WHAT\";");
FileStream fs = new FileStream(fileName, FileMode.Create);
StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
sw.Write(sb.ToString());
sw.Close();
fs.Close();
}
}
3. 使用FlatBuffer生成对应的操作代码,并生成对应的保存数据的代码
这一步其实是两步,首先使用FlatBuffer将fbs文件转换成对应的操作代码,我这里用的是C#,然后再生成对应的保存数据到文件的代码,代码如下:
private void GenCode() {
Console.WriteLine("\n----------生成C#代码----------");
foreach (ExcelData data in _excels) {
string fbsName = AppConfigs.Instance.FBSPath + "/" + data.sheetName + ".fbs";
EnsureDirectory(AppConfigs.Instance.GenerateCodePath);
// 配置操作配置文件的代码
string arguments = string.Format("--csharp -o {0} {1} --gen-onefile", AppConfigs.Instance.GenerateCodePath, fbsName);
ProcCmd(AppConfigs.Instance.FlatC, arguments, AppConfigs.Instance.FBSPath, false);
// 生成将配置文件存储成文件的代码
string dataClass = "Single" + data.sheetName + "Data";
string className = data.sheetName + ConfigSaveFileEx;
StringBuilder sb = new StringBuilder();
sb.Append("using System;" + NewLine);
sb.Append("using System.IO;" + NewLine);
sb.Append("using System.Collections.Generic;" + NewLine);
sb.Append("using FlatBuffers;" + NewLine);
sb.Append("using Config;" + NewLine);
sb.Append(NewLine);
sb.Append("public class " + className + " {" + NewLine);
sb.Append(NewLine);
sb.Append(Tab + "public class Data {" + NewLine);
foreach (ExcelFieldInfo info in data.filedInfos.Values) {
sb.Append(Tab + Tab + "public " + FieldToType(info.type) + " " + info.name + ";" + NewLine);
}
sb.Append(Tab + "}" + NewLine);
sb.Append(NewLine);
sb.Append(Tab + "public void Save(List datas, string path) {" + NewLine);
sb.Append(Tab + Tab + "FlatBufferBuilder fbb = new FlatBufferBuilder(1);" + NewLine);
sb.Append(Tab + Tab + "int count = datas.Count;" + NewLine);
sb.Append(string.Format(Tab + Tab + "Offset<{0}>[] offsets = new Offset<{1}>[count];", dataClass, dataClass) + NewLine);
sb.Append(Tab + Tab + "for (int n = 0; n < count; ++n) {" + NewLine);
sb.Append(Tab + Tab + Tab + "Data data = datas[n];" + NewLine);
sb.Append(string.Format(Tab + Tab + Tab + "offsets[n] = {0}.Create{1}(fbb,", dataClass, dataClass) + NewLine);
int index = 0;
foreach (ExcelFieldInfo info in data.filedInfos.Values) {
string type = FieldToType(info.type);
string end = ",";
if (index == data.filedInfos.Count - 1) {
end = ");";
}
if (type == "string") {
sb.Append(string.Format(Tab + Tab + Tab + "fbb.CreateString(data.{0})", info.name) + end + NewLine);
}
else {
sb.Append(Tab + Tab + Tab + "data." + info.name + end + NewLine);
}
index++;
}
sb.Append(Tab + Tab + "}" + NewLine);
sb.Append(Tab + Tab + string.Format("VectorOffset dataOff = {0}.CreateDataVector(fbb, offsets);", data.sheetName) + NewLine);
sb.Append(string.Format(Tab + Tab + "var configOff = {0}.Create{1}(fbb, dataOff);", data.sheetName, data.sheetName) + NewLine);
sb.Append(string.Format(Tab + Tab + "{0}.Finish{1}Buffer(fbb, configOff);", data.sheetName, data.sheetName) + NewLine);
sb.Append(Tab + Tab + "using (var ms = new MemoryStream(fbb.DataBuffer.Data, fbb.DataBuffer.Position, fbb.Offset)) {" + NewLine);
sb.Append(Tab + Tab + Tab + "File.WriteAllBytes(path, ms.ToArray());" + NewLine);
sb.Append(Tab + Tab + "}" + NewLine);
sb.Append(Tab + "}" + NewLine);
sb.Append("}" + NewLine);
string fileName = AppConfigs.Instance.GenerateCodePath + className + ".cs";
FileStream fs = new FileStream(fileName, FileMode.Create);
StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
sw.Write(sb.ToString());
sw.Close();
fs.Close();
}
}
4. 动态编译生成的C#代码
这里不多说,直接上代码:
private void CompileCSharp() {
// 休息一会儿,避免上一步生成文件的操作还没有完成
Thread.Sleep(1000);
Console.WriteLine("\n----------编译C#代码----------");
string[] files = Directory.GetFiles(AppConfigs.Instance.GenerateCodePath);
List compileFiles = new List();
// 过滤文件
for (int n = 0; n < files.Length; ++n) {
if (Path.GetExtension(files[n]) != ".cs") {
continue;
}
compileFiles.Add(files[n]);
}
string flatRefPath = AppConfigs.Instance.CompileRefPath + "FlatBuffers.dll";
_flatAssmbly = CompileCS(flatRefPath, null, null, compileFiles.ToArray());
}
private Assembly CompileCS(string refer, string output, string options, params string[] code) {
CodeDomProvider domProvider = CodeDomProvider.CreateProvider("CSharp");
CompilerParameters compileParams = new CompilerParameters();
compileParams.GenerateExecutable = false;
compileParams.GenerateInMemory = true;
compileParams.ReferencedAssemblies.Add(AppConfigs.Instance.CompileRefPath + "System.dll");
if (!string.IsNullOrEmpty(refer)) {
compileParams.ReferencedAssemblies.Add(refer);
}
if (!string.IsNullOrEmpty(output)) {
compileParams.OutputAssembly = output;
}
if (!string.IsNullOrEmpty(options)) {
compileParams.CompilerOptions = options;
}
CompilerResults compileResults = domProvider.CompileAssemblyFromFile(compileParams, code);
if (compileResults.Errors.Count > 0) {
Console.WriteLine("compile error!");
foreach (CompilerError error in compileResults.Errors) {
Console.WriteLine(string.Format(" {0}", error.ToString()));
Console.WriteLine("");
}
}
return compileResults.CompiledAssembly;
}
5. 生成最终的bytes文件
用C#的反射动态生成对应的数据结构,然后将excel的数据给数据赋值,最终调用保存方法就成了,代码如下:
private void GenDataFiles() {
Console.WriteLine("\n----------生成数据文件----------");
foreach (ExcelData data in _excels) {
string classTypeName = data.sheetName + ConfigSaveFileEx;
// 反射生成类中类要用+连接
string dataTypeName = data.sheetName + ConfigSaveFileEx + "+Data";
object datas = Utils.CreateGeneric(typeof(List<>), _flatAssmbly.GetType(dataTypeName));
#if USE_NPOI
for (int n = 1; n <= data.sheet.LastRowNum; ++n) {
IRow dataRow = data.sheet.GetRow(n);
// 第一个字段为空,则认为整个数据表已经结束
if ("" == dataRow.GetCell(0).ToString().Trim()) {
break;
}
#else
for (int n = 1; n < data.table.Rows.Count; ++n) {
DataRow dataRow = data.table.Rows[n];
// 第一个字段为空,则认为整个数据表已经结束
if ("" == dataRow[0].ToString().Trim()) {
break;
}
#endif
object dataInst = _flatAssmbly.CreateInstance(dataTypeName);
System.Reflection.FieldInfo[] fields = dataInst.GetType().GetFields();
foreach (System.Reflection.FieldInfo pi in fields) {
ExcelFieldInfo fieldInfo = null;
string infoValue = null;
object value = null;
try {
fieldInfo = data.filedInfos[pi.Name];
#if USE_NPOI
infoValue = dataRow.GetCell(fieldInfo.index).ToString();
#else
infoValue = dataRow[fieldInfo.index].ToString();
#endif
value = GetFieldValue(fieldInfo, infoValue, _flatAssmbly);
pi.SetValue(dataInst, value);
}
catch (Exception e) {
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(string.Format("{0}, 行: {1}, 列: {2}", e.Message, n + 1, fieldInfo.desc));
Console.ResetColor();
}
}
datas.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, datas, new object[] { dataInst });
}
object classInst = _flatAssmbly.CreateInstance(classTypeName);
object[] parameters = new object[2];
parameters[0] = datas;
parameters[1] = AppConfigs.Instance.ClientDataPath + data.sheetName + ".bytes";
Utils.CallMethod(classInst, "Save", parameters);
}
}