Esempio n. 1
0
        private void Workbook_BeforeSave(Workbook wb, bool b, ref bool r)
        {
            if (wb.IsVaildWorkBook())
            {
                return;
            }
            var activeSheet = (Worksheet)wb.ActiveSheet;
            var fileName    = Path.GetFileNameWithoutExtension(wb.Name).Replace(Config.Instance.FileSuffix, "");
            var dbDirPath   = Config.Instance.SaveDbPath;
            var dbFilePath  = dbDirPath + fileName;

            if (!Directory.Exists(dbDirPath))
            {
                Directory.CreateDirectory(dbDirPath);
            }
            Range usedRange          = activeSheet.UsedRange;
            var   rowCount           = usedRange.Rows.Count;
            var   columnCount        = usedRange.Columns.Count;
            List <TableStruct> table = new List <TableStruct>();
            bool   haveKey           = false;
            string keyType           = "";
            int    keyIndex          = -1;

            object[,] cells = usedRange.Value2;
            List <int> passColumns = new List <int>(); //跳过列

            for (var index = 1; index < columnCount + 1; index++)
            {
                //从1开始,第0行是策划用来写备注的地方第1行为程序使用的变量名,第2行为变量类型
                string t1 = Convert.ToString(cells[NameRow, index]);
                if (string.IsNullOrWhiteSpace(t1))
                {
                    var cell = ((Range)usedRange.Cells[NameRow, index]).Address;
                    throw new ExcelException("单元格:" + cell + "名称不能为空");
                }
                if (t1.StartsWith("*"))
                {
                    passColumns.Add(index);
                    continue;
                }
                string type = Convert.ToString(cells[TypeRow, index]);
                if (t1 == Key)
                {
                    haveKey = true;
                    keyType = type;
                    if (keyType != "int" && keyType != "string")
                    {
                        throw new ExcelException("表ID的类型不支持,可使用的类型必须为 int,string");
                    }
                    keyIndex = index;
                }
                table.Add(new TableStruct(t1, type));
            }

            if (!haveKey)
            {
                throw new ExcelException("表格中不存在关键Key,你需要新增一列变量名为" + Key + "的变量作为键值");
            }
            try
            {
                //生成C#脚本
                var customClass = new List <GenerateConfigTemplate>();
                var coreClass   = new GenerateConfigTemplate {
                    Class = new GenerateClassTemplate {
                        Name = fileName, Type = keyType
                    }
                };
                for (int i = 0; i < table.Count; i++)
                {
                    var t = table[i];
                    if (!SupportType.Contains(t.Type))
                    {
                        var newCustomType = TableAnalyzer.GenerateCustomClass(t.Type, t.Name);
                        coreClass.Add(new GeneratePropertiesTemplate {
                            Name = t.Name, Type = newCustomType.Class.Name + (t.Type.StartsWith("{") ? "[]" : "")
                        });
                        customClass.Add(newCustomType);
                    }
                    else
                    {
                        var core = new GeneratePropertiesTemplate {
                            Name = t.Name, Type = t.Type
                        };
                        if (t.Type == "enum")
                        {
                            if (((Range)usedRange[TypeRow, i + 1]).Comment != null)
                            {
                                core.Data = ((Range)usedRange[TypeRow, i + 1]).Comment.Text().Replace("\r", "").Split(new[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);
                            }
                        }
                        coreClass.Add(core);
                    }
                }
                CodeGenerate.Start(customClass, coreClass, fileName);
            }
            catch (Exception e)
            {
                throw new ExcelException("生成脚本失败\n" +
                                         "可能使用了不被支持的脚本类型\n" +
                                         "当前仅支持int,int[],float,float[],bool,bool[],string,string[],long,long[]\n" +
                                         "或者自定义类的使用方法错误\n" +
                                         e);
            }
            try
            {
                if (File.Exists(dbFilePath))
                {
                    File.Delete(dbFilePath);
                }
            }
            catch
            {
                throw new ExcelException("无法写入数据库至" + dbFilePath + "请检查是否有任何应用正在使用该文件");
            }

            try
            {
                if (Config.Instance.GeneratorType == "DB")
                {
                    WriteDB(dbFilePath, fileName, keyType, table, columnCount, rowCount, passColumns, cells, usedRange);
                }
                else if (Config.Instance.GeneratorType == "Json")
                {
                    WriteJson(dbFilePath, fileName, table, keyIndex, columnCount, rowCount, passColumns, cells,
                              usedRange);
                }
            }
            catch (Exception e)
            {
                throw new ExcelException("写入数据库失败\n" + e);
            }
        }
Esempio n. 2
0
        private void Workbook_BeforeSave(Workbook wb, bool b, ref bool r)
        {
            if (wb.IsVaildWorkBook())
            {
                return;
            }
            var activeSheet = (Worksheet)wb.ActiveSheet;
            var fileName    = Path.GetFileNameWithoutExtension(wb.Name).Replace(Config.Instance.FileSuffix, "");
            var dbDirPath   = Config.Instance.SaveDbPath;
            var dbFilePath  = dbDirPath + fileName + ".db";

            if (!Directory.Exists(dbDirPath))
            {
                Directory.CreateDirectory(dbDirPath);
            }
            Range usedRange          = activeSheet.UsedRange;
            var   rowCount           = usedRange.Rows.Count;
            var   columnCount        = usedRange.Columns.Count;
            List <TableStruct> table = new List <TableStruct>();
            bool   haveKey           = false;
            string keyType           = "";

            object[,] cells = usedRange.Value2;
            List <int> passColumns = new List <int>(); //跳过列

            for (var index = 1; index < columnCount + 1; index++)
            {
                //从1开始,第0行是策划用来写备注的地方第1行为程序使用的变量名,第2行为变量类型
                string t1 = Convert.ToString(cells[NameRow, index]);
                if (string.IsNullOrWhiteSpace(t1))
                {
                    var cell = ((Range)usedRange.Cells[NameRow, index]).Address;
                    throw new ExcelException("单元格:" + cell + "名称不能为空");
                }
                if (t1.StartsWith("*"))
                {
                    passColumns.Add(index);
                    continue;
                }
                string type = Convert.ToString(cells[TypeRow, index]);
                if (t1 == Key)
                {
                    haveKey = true;
                    keyType = type;
                    if (keyType != "int" && keyType != "string")
                    {
                        throw new ExcelException("表ID的类型不支持,可使用的类型必须为 int,string");
                    }
                }
                table.Add(new TableStruct(t1, type));
            }
            if (!haveKey)
            {
                throw new ExcelException("表格中不存在关键Key,你需要新增一列变量名为" + Key + "的变量作为键值");
            }
            try
            {
                //生成C#脚本
                var customClass = new List <GenerateConfigTemplate>();
                var coreClass   = new GenerateConfigTemplate {
                    Class = new GenerateClassTemplate {
                        Name = fileName, Type = keyType
                    }
                };
                for (int i = 0; i < table.Count; i++)
                {
                    var t = table[i];
                    if (!SupportType.Contains(t.Type))
                    {
                        var newCustomType = TableAnalyzer.GenerateCustomClass(t.Type, t.Name);
                        coreClass.Add(new GeneratePropertiesTemplate {
                            Name = t.Name, Type = newCustomType.Class.Name + (t.Type.StartsWith("{") ? "[]" : "")
                        });
                        customClass.Add(newCustomType);
                    }
                    else
                    {
                        var core = new GeneratePropertiesTemplate {
                            Name = t.Name, Type = t.Type
                        };
                        if (t.Type == "enum")
                        {
                            if (((Range)usedRange[TypeRow, i + 1]).Comment != null)
                            {
                                core.Data = ((Range)usedRange[TypeRow, i + 1]).Comment.Text().Replace("\r", "").Split(new[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);
                            }
                        }
                        coreClass.Add(core);
                    }
                }
                CodeGenerate.Start(customClass, coreClass, fileName);
            }
            catch (Exception e)
            {
                throw new ExcelException("生成脚本失败\n" +
                                         "可能使用了不被支持的脚本类型\n" +
                                         "当前仅支持int,int[],float,float[],bool,bool[],string,string[],long,long[]\n" +
                                         "或者自定义类的使用方法错误\n" +
                                         e);
            }
            try
            {
                if (File.Exists(dbFilePath))
                {
                    File.Delete(dbFilePath);
                }
            }
            catch (Exception e)
            {
                throw new ExcelException("无法写入数据库至" + dbFilePath + "请检查是否有任何应用正在使用该文件");
            }
            using (var conn = new SQLiteConnection(dbFilePath, SQLiteOpenFlags.Create | SQLiteOpenFlags.ReadWrite))
            {
                try
                {
                    StringBuilder sb        = new StringBuilder();
                    var           tableName = fileName;
                    SQLiteCommand sql       = new SQLiteCommand(conn);
                    sql.CommandText = "PRAGMA synchronous = OFF";
                    sql.ExecuteNonQuery();
                    //创建关键Key写入表头
                    sb.Append("create table if not exists " + tableName + " (" + Key + " " + FullTypeSqliteMapping[keyType] + " PRIMARY KEY not null, ");
                    for (int n = 0; n < table.Count; n++)
                    {
                        if (table[n].Name == Key)
                        {
                            continue;
                        }
                        var    t          = FullTypeSqliteMapping.ContainsKey(table[n].Type);
                        string sqliteType = t ? FullTypeSqliteMapping[table[n].Type] : "TEXT";
                        sb.Append(table[n].Name + " " + sqliteType + ",");
                    }
                    sb.Remove(sb.Length - 1, 1);
                    sb.Append(")");
                    sql.CommandText = sb.ToString();
                    sql.ExecuteNonQuery();
                    //准备写入表内容
                    sb.Clear();
                    conn.BeginTransaction();
                    object[] writeInfo = new object[columnCount];
                    for (int i = StartLine; i <= rowCount; i++)
                    {
                        int offset = 1;
                        for (var n = 1; n <= columnCount; n++)
                        {
                            try
                            {
                                if (passColumns.Contains(n))
                                {
                                    offset++;
                                    continue;
                                }
                                var    property = table[n - offset];
                                string cell     = Convert.ToString(cells[i, n]);
                                if (table.Count > n - offset)
                                {
                                    string sqliteType;
                                    if (FullTypeSqliteMapping.TryGetValue(property.Type, out sqliteType)) //常规类型可以使用这种方法直接转换
                                    {
                                        var attr = TableAnalyzer.SplitData(cell);
                                        if (property.Type == "bool")
                                        {
                                            writeInfo[n - offset] = attr[0].ToUpper() == "TRUE" ? 1 : 0;
                                        }
                                        else if (sqliteType != "TEXT")
                                        {
                                            writeInfo[n - offset] = attr[0];
                                        }
                                        else
                                        {
                                            writeInfo[n - offset] = cell;
                                        }
                                    }
                                    else
                                    {
                                        //自定义类型序列化
                                        writeInfo[n - 1] = cell;
                                    }
                                }
                            }
                            catch
                            {
                                throw new Exception("单元格:" + ((Range)usedRange.Cells[i, n]).Address + "存在异常");
                            }
                        }
                        sb.Append("replace into " + fileName + " ");
                        sb.Append("(");
                        foreach (var node in table)
                        {
                            sb.Append(node.Name + ",");
                        }
                        sb.Remove(sb.Length - 1, 1);
                        sb.Append(") values (");
                        for (var index = 0; index < table.Count; index++)
                        {
                            sb.Append("?,");
                        }
                        sb.Remove(sb.Length - 1, 1);
                        sb.Append(")");
                        conn.CreateCommand(sb.ToString(), writeInfo).ExecuteNonQuery();
                        sb.Clear();
                    }
                    conn.Commit();
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.ToString());
                }
                finally
                {
                    conn.Close();
                }
            }
        }