将Excel数据转换成FlatBuffer数据的方法

之前配置表中用的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);            }        }