将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);
            }
        }