public string GeneratorModel(MTableDefine table, string namespaceStr) { StringBuilder sb = new StringBuilder(); return(sb.ToString()); }
/// <summary> /// 获取表结构定义 /// </summary> /// <param name="tb">DataTable</param> /// <returns>结果</returns> private MTableDefine GetTableDefine(DataTable tb) { MTableDefine ret = new MTableDefine(); if (tb == null) { return(null); } if (tb.TableName.Contains("首页") || tb.TableName.Contains("数据表一栏")) { return(null); } // 估计带超链接,还真不准,先注释掉 //if (tb.Rows[0][0].ToString() != "返回一览表") //{ // return null; //} if (tb.Rows[1][0].ToString() != "TableName") { return(null); } ret.TableName = tb.Rows[2][0].ToString(); ret.TableNameCH = tb.Rows[2][1].ToString(); ret.TableDescrption = tb.Rows[2][2].ToString(); ret.FieldList = new List <MFieldDefine>(); for (int i = 5; i < tb.Rows.Count; i++) { MFieldDefine col = new MFieldDefine(); col.FieldNameCH = tb.Rows[i][1].ToString(); col.FieldName = tb.Rows[i][2].ToString(); col.DataType = tb.Rows[i][3].ToString(); string tempstr = tb.Rows[i][4].ToString(); int tempint = 0; if (!string.IsNullOrEmpty(tempstr) && int.TryParse(tempstr, out tempint)) { col.Length = tempint; } tempstr = tb.Rows[i][5].ToString(); if (!string.IsNullOrEmpty(tempstr) && tempstr == "○") { col.IsNullable = false; } tempstr = tb.Rows[i][6].ToString(); if (!string.IsNullOrEmpty(tempstr) && int.TryParse(tempstr, out tempint)) { col.PrimaryKeyIndex = tempint; } col.ForeignRelation = tb.Rows[i][7].ToString(); tempstr = tb.Rows[i][7].ToString(); if (!string.IsNullOrEmpty(tempstr) && tempstr == "○") { col.IsUniqueIndex = true; } tempstr = tb.Rows[i][8].ToString(); if (!string.IsNullOrEmpty(tempstr) && int.TryParse(tempstr, out tempint)) { col.IndexNo = tempint; } tempstr = tb.Rows[i][9].ToString(); if (!string.IsNullOrEmpty(tempstr) && tempstr == "○") { col.IsAutoIncrement = true; } col.FieldFormat = tb.Rows[i][10].ToString(); col.DefaultValue = tb.Rows[i][11].ToString(); col.ValueConstraint = tb.Rows[i][12].ToString(); col.ProjectSignificance = tb.Rows[i][13].ToString(); ret.FieldList.Add(col); } return(ret); }
private string GetCreateTableSQL(MTableDefine tb) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("/*建立数表{0}*/", tb.TableName).AppendLine(); sb.AppendFormat("CREATE TABLE {0}", tb.TableName).AppendLine(); sb.Append("("); // 字段 foreach (var field in tb.FieldList) { if (field.FieldName.ToUpper() == "MODIFYTIME" && field.DefaultValue == "CURRENT_TIMESTAMP") { sb.AppendFormat("{0} {1} NOT NULL DEFAULT {2} ON UPDATE {2} COMMENT '{3}',", field.FieldName, field.DataType, field.DefaultValue, field.FieldNameCH + ";" + field.ValueConstraint).AppendLine(); } else if (field.DataType.ToUpper() == "DATETIME") { sb.AppendFormat("{0} {1} NOT NULL DEFAULT {2} COMMENT '{3}',", field.FieldName, field.DataType, field.DefaultValue, field.FieldNameCH + ";" + field.ValueConstraint).AppendLine(); } else { if (field.Length == 0) { // 没有长度 sb.AppendFormat("{0} {1} NOT NULL DEFAULT '{2}' COMMENT '{3}',", field.FieldName, field.DataType, field.DefaultValue, field.FieldNameCH + ";" + field.ValueConstraint).AppendLine(); } else { sb.AppendFormat("{0} {1}({2}) NOT NULL DEFAULT '{3}' COMMENT '{4}',", field.FieldName, field.DataType, field.Length, field.DefaultValue, field.FieldNameCH + ";" + field.ValueConstraint).AppendLine(); } } } var primarkeys = (from p in tb.FieldList where p.PrimaryKeyIndex > 0 orderby p.PrimaryKeyIndex ascending select p.FieldName); string primaryKeyStr = string.Join(",", primarkeys.ToArray()); // 主键 sb.AppendFormat("PRIMARY KEY({0}),", primaryKeyStr).AppendLine(); // 唯一索引 if (tb.FieldList.Exists(sa => sa.IsUniqueIndex == true)) { var uniqueIndex = (from sa in tb.FieldList where sa.IsUniqueIndex select sa.FieldName); sb.AppendFormat("UNIQUE INDEX {0}_{1} ({1}),", tb.TableName, uniqueIndex.ToArray()[0]).AppendLine(); } // 其他索引列,如果有的话 if (tb.FieldList.Exists(sa => sa.IndexNo > 0)) { var indexNoList = (from sa in tb.FieldList where sa.IndexNo > 0 orderby sa.IndexNo ascending select sa.FieldName); string indexNoStr = string.Join(",", indexNoList.ToArray()); string indexNoListName = string.Join("_", indexNoList.ToArray()); sb.AppendFormat("INDEX {0}_{1} ({2}),", tb.TableName, indexNoListName, indexNoStr).AppendLine(); } // 去掉最后一个逗号 sb = new StringBuilder(sb.ToString().Trim()); sb = sb.Remove(sb.Length - 1, 1); sb.AppendFormat(")ENGINE=InnoDB default charset=utf8 COMMENT='{0}';", tb.TableNameCH); return(sb.ToString()); }
private MTableDefine GetTableDefine(System.Data.IDbConnection conn, string tableName) { MTableDefine table = new MTableDefine(); table.TableName = tableName; string sql = string.Format(@" SELECT TableName = case when a.colorder=1 then d.name else '' end, TableNameCH = case when a.colorder=1 then isnull(f.value,'') else '' end, FieldIndex = a.colorder, FieldName = a.name, IsIdentity = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 'true'else 'false' end, IsPrimaryKey = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 'true' else 'false' end, DataType = b.name, DataTypeLength = COLUMNPROPERTY(a.id,a.name,'PRECISION'), DigitalLength = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), IsNullable = case when a.isnullable=1 then 'true'else 'false' end, DefaultValue = isnull(e.text,''), FieldNameCH = isnull(g.[value],'') FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=G.major_id and a.colid=g.minor_id left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 where d.name='{0}' --如果只查询指定表,加上此红色where条件,tablename是要查询的表名;去除红色where条件查询说有的表信息 order by a.id,a.colorder;", tableName); SqlCommand comm = new SqlCommand(sql, (SqlConnection)conn); SqlDataReader reader = comm.ExecuteReader(); List <MFieldDefine> fieldList = new List <MFieldDefine>(); while (reader.Read()) { MFieldDefine field = new MFieldDefine(); table.TableNameCH = reader["TableNameCH"] == DBNull.Value ? string.Empty : reader["TableNameCH"].ToString(); field.Index = DbUtil.GetDBValueInt(reader, "FieldIndex", 0); field.FieldName = DbUtil.GetDBValueStr(reader, "FieldName", string.Empty); field.IsAutoIncrement = DbUtil.GetDBValueBool(reader, "IsIdentity", false); bool IsPrimaryKey = DBUtil.DbUtil.GetDBValueBool(reader, "IsPrimaryKey", false); int maxPrimaryKeyIndex = fieldList != null && fieldList.Count > 0 ? fieldList.Max(sa => sa.PrimaryKeyIndex) : 0; if (IsPrimaryKey) { field.PrimaryKeyIndex = maxPrimaryKeyIndex + 1; } field.IsPrimaryKey = IsPrimaryKey; field.DataType = DbUtil.GetDBValueStr(reader, "DataType", string.Empty); field.Length = DbUtil.GetDBValueInt(reader, "DataTypeLength", 0); field.DigitalLength = DbUtil.GetDBValueInt(reader, "DigitalLength", 0); field.IsNullable = DbUtil.GetDBValueBool(reader, "IsNullable", false); field.DefaultValue = DbUtil.GetDBValueStr(reader, "DefaultValue", string.Empty); // 处理默认值 if (!string.IsNullOrEmpty(field.DefaultValue)) { string[] twoBracketsSpecial = { "int", "tinyint" }; if (twoBracketsSpecial.Contains(field.DataType.ToLower())) { field.DefaultValue = field.DefaultValue.Replace("(", string.Empty).Replace(")", string.Empty); } else { // 只有一个括号 field.DefaultValue = field.DefaultValue.Substring(1, field.DefaultValue.Length - 2); } } field.FieldNameCH = DbUtil.GetDBValueStr(reader, "FieldNameCH", string.Empty); fieldList.Add(field); } table.FieldList = fieldList; return(table); }
private void SetTableNamePageList(IWorkbook workbook, MTableDefine table, string filename) { ISheet sheet = workbook.CreateSheet(table.TableName); // 第0行:返回一览表 IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); cell.SetCellValue("返回一览表"); HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);//建一个HSSFHyperlink实体,指明链接类型为URL(这里是枚举,可以根据需求自行更改) link.Address = "#数据表一栏!A1"; cell.Hyperlink = link; this.SetHyperLinkCellStyle(workbook, cell, false); HSSFColor gold = new HSSFColor.Gold(); // 第一行、表介绍的的表头 row = sheet.CreateRow(1); cell = row.CreateCell(0); cell.CellStyle = this.GetCellStyle(workbook, gold); cell.SetCellValue("TableName"); cell = row.CreateCell(1); cell.CellStyle = this.GetCellStyle(workbook, gold); cell.SetCellValue("表明"); cell = row.CreateCell(2); cell.CellStyle = this.GetCellStyle(workbook, gold); cell.SetCellValue("表说明"); // 表介绍的内容 row = sheet.CreateRow(2); cell = row.CreateCell(0); cell.CellStyle = this.GetCellStyle(workbook, null); cell.SetCellValue(table.TableName); cell = row.CreateCell(1); cell.CellStyle = this.GetCellStyle(workbook, null); cell.SetCellValue(table.TableNameCH); cell = row.CreateCell(2); cell.CellStyle = this.GetCellStyle(workbook, null); cell.SetCellValue(table.TableNameCH); // 一、表定义 row = sheet.CreateRow(3); cell = row.CreateCell(0); cell.CellStyle = this.GetCellStyle(workbook, null, "微软雅黑", 10, false); cell.SetCellValue("一、表定义"); // 表字段定义表头 row = sheet.CreateRow(4); cell = row.CreateCell(0); cell.CellStyle = this.GetCellStyle(workbook, gold); cell.SetCellValue("序号"); cell = row.CreateCell(1); cell.CellStyle = this.GetCellStyle(workbook, gold); cell.SetCellValue("字段中文名"); cell = row.CreateCell(2); cell.CellStyle = this.GetCellStyle(workbook, gold); cell.SetCellValue("字段英文名"); cell = row.CreateCell(3); cell.CellStyle = this.GetCellStyle(workbook, gold); cell.SetCellValue("数据类型"); cell = row.CreateCell(4); cell.CellStyle = this.GetCellStyle(workbook, gold); cell.SetCellValue("位数"); cell = row.CreateCell(5); cell.CellStyle = this.GetCellStyle(workbook, gold); cell.SetCellValue("非空"); cell = row.CreateCell(6); cell.CellStyle = this.GetCellStyle(workbook, gold); cell.SetCellValue("主键"); cell = row.CreateCell(7); cell.CellStyle = this.GetCellStyle(workbook, gold); cell.SetCellValue("外部关系"); cell = row.CreateCell(8); cell.CellStyle = this.GetCellStyle(workbook, gold); cell.SetCellValue("唯一索引"); cell = row.CreateCell(9); cell.CellStyle = this.GetCellStyle(workbook, gold); cell.SetCellValue("索引"); cell = row.CreateCell(10); cell.CellStyle = this.GetCellStyle(workbook, gold); cell.SetCellValue("自增"); cell = row.CreateCell(11); cell.CellStyle = this.GetCellStyle(workbook, gold); cell.SetCellValue("默认值"); cell = row.CreateCell(12); cell.CellStyle = this.GetCellStyle(workbook, gold); cell.SetCellValue("字段格式"); cell = row.CreateCell(13); cell.CellStyle = this.GetCellStyle(workbook, gold); cell.SetCellValue("值约束"); cell = row.CreateCell(14); cell.CellStyle = this.GetCellStyle(workbook, gold); cell.SetCellValue("项目意义"); int i = 5; // 字段定义 foreach (var field in table.FieldList) { row = sheet.CreateRow(i); cell = row.CreateCell(0); cell.CellStyle = this.GetCellStyle(workbook, null); cell.SetCellValue(field.Index); cell = row.CreateCell(1); cell.CellStyle = this.GetCellStyle(workbook, null); cell.SetCellValue(field.FieldNameCH); cell = row.CreateCell(2); cell.CellStyle = this.GetCellStyle(workbook, null); cell.SetCellValue(field.FieldName); cell = row.CreateCell(3); cell.CellStyle = this.GetCellStyle(workbook, null); cell.SetCellValue(field.DataType); cell = row.CreateCell(4); cell.CellStyle = this.GetCellStyle(workbook, null); cell.SetCellValue(field.Length); cell = row.CreateCell(5); cell.CellStyle = this.GetCellStyle(workbook, null, "宋体"); if (!field.IsNullable) { cell.SetCellValue("○"); } else { cell.SetCellValue(string.Empty); } cell = row.CreateCell(6); cell.CellStyle = this.GetCellStyle(workbook, null); if (field.PrimaryKeyIndex > 0) { cell.SetCellValue(field.PrimaryKeyIndex); } else { cell.SetCellValue(string.Empty); } // 外部关系先不填写 cell = row.CreateCell(7); cell.CellStyle = this.GetCellStyle(workbook, null); cell.SetCellValue(string.Empty); cell = row.CreateCell(8); cell.CellStyle = this.GetCellStyle(workbook, null, "宋体"); if (field.IsUniqueIndex) { cell.SetCellValue("○"); } else { cell.SetCellValue(string.Empty); } cell = row.CreateCell(9); cell.CellStyle = this.GetCellStyle(workbook, null); if (field.IndexNo > 0) { cell.SetCellValue(field.IndexNo); } else { cell.SetCellValue(string.Empty); } cell = row.CreateCell(10); cell.CellStyle = this.GetCellStyle(workbook, null, "宋体"); if (field.IsAutoIncrement) { cell.SetCellValue("○"); } else { cell.SetCellValue(string.Empty); } cell = row.CreateCell(11); cell.CellStyle = this.GetCellStyle(workbook, null); cell.SetCellValue(field.DefaultValue); // 字段格式,暂时不写 cell = row.CreateCell(12); cell.CellStyle = this.GetCellStyle(workbook, null); cell.SetCellValue(string.Empty); // 值约束 cell = row.CreateCell(13); cell.CellStyle = this.GetCellStyle(workbook, null); cell.SetCellValue(field.ValueConstraint); // 项目意义 cell = row.CreateCell(14); cell.CellStyle = this.GetCellStyle(workbook, null); cell.SetCellValue(string.Empty); i++; } for (int j = 0; j <= 14; j++) { sheet.AutoSizeColumn(j); } }
/// <summary> /// 绘制表详细 /// </summary> /// <param name="wordApp">Word App</param> /// <param name="wordDoc">Word Doc</param> /// <param name="tableIndex">表格序号</param> /// <param name="tableDefine">表定义对象</param> private void DrawTableDetailInfo(MSWord.Application wordApp, MSWord.Document wordDoc, int tableIndex, MTableDefine tableDefine) { // 这一句与下一句的顺序不能颠倒,原因还没搞透 // wordDoc.Content.InsertAfter("\n"); // 这一句不加,有时候好像也不出问题,不过还是加了安全 wordApp.Selection.EndKey(ref unite, ref Nothing); this.AddHeading4("2." + tableIndex + " " + tableDefine.TableName + "表的卡片", wordDoc); // 这一句与下一句的顺序不能颠倒,原因还没搞透 // wordDoc.Content.InsertAfter("\n"); // 将光标移动到文档末尾 wordApp.Selection.EndKey(ref unite, ref Nothing); wordApp.Selection.ParagraphFormat.Alignment = MSWord.WdParagraphAlignment.wdAlignParagraphLeft; int tableRow = tableDefine.FieldList.Count + 2; int tableColumn = 9; // 定义一个Word中的表格对象 MSWord.Table table = wordDoc.Tables.Add(wordApp.Selection.Range, tableRow, tableColumn, ref Nothing, ref Nothing); // 默认创建的表格没有边框,这里修改其属性,使得创建的表格带有边框 // 这个值可以设置得很大,例如5、13等等 table.Borders.Enable = 1; // 设置 每一列的 宽度 table.Columns[1].Width = 30; table.Columns[2].Width = 100; table.Columns[3].Width = 90; table.Columns[4].Width = 50; table.Columns[5].Width = 30; table.Columns[6].Width = 30; table.Columns[7].Width = 30; table.Columns[8].Width = 55; table.Columns[9].Width = 75; // 横向合并 table.Cell(1, 1).Merge(table.Cell(1, 9)); table.Cell(1, 1).Range.Text = tableDefine.TableName; table.Cell(1, 1).Range.Shading.BackgroundPatternColor = Microsoft.Office.Interop.Word.WdColor.wdColorGray25; //表格的索引是从1开始的。 table.Cell(2, 1).Range.Text = "是否主键"; table.Cell(2, 2).Range.Text = "字段名"; table.Cell(2, 3).Range.Text = "字段描述"; table.Cell(2, 4).Range.Text = "数据类型"; table.Cell(2, 5).Range.Text = "长度"; table.Cell(2, 6).Range.Text = "可空"; table.Cell(2, 7).Range.Text = "约束"; table.Cell(2, 8).Range.Text = "缺省值"; table.Cell(2, 9).Range.Text = "备注"; for (int i = 3; i <= tableRow; i++) { int row = i; var field = tableDefine.FieldList[i - 3]; // 是否主键 if (field.IsPrimaryKey) { table.Cell(row, 1).Range.Text = "是"; } else { table.Cell(row, 1).Range.Text = ""; } // 字段名 table.Cell(row, 2).Range.Text = field.FieldName; if (string.IsNullOrEmpty(field.FieldNameCH) && field.FieldName == "ID") { // 字段描述 table.Cell(row, 3).Range.Text = "主键ID"; } else { // 字段描述 table.Cell(row, 3).Range.Text = field.FieldNameCH; } // 数据类型 table.Cell(row, 4).Range.Text = field.DataType; if (this.digitalList.Contains(field.DataType)) { table.Cell(row, 5).Range.Text = field.Length.ToString() + "," + field.DigitalLength; } else if (this.useLengthList.Contains(field.DataType)) { // 长度 table.Cell(row, 5).Range.Text = field.Length.ToString(); } else { table.Cell(row, 5).Range.Text = string.Empty; } // 是否可空 if (field.IsNullable) { table.Cell(row, 6).Range.Text = "是"; } else { table.Cell(row, 6).Range.Text = "否"; } // 约束 table.Cell(row, 7).Range.Text = field.ValueConstraint; // 缺省值 table.Cell(row, 8).Range.Text = field.DefaultValue; // 备注 table.Cell(row, 9).Range.Text = field.ProjectSignificance; } //设置table样式 //table.Rows.HeightRule = MSWord.WdRowHeightRule.wdRowHeightAtLeast;//高度规则是:行高有最低值下限? //table.Rows.Height = wordApp.CentimetersToPoints(float.Parse("0.8"));// table.Range.Font.Size = 9.5F; table.Range.Font.Bold = 0; table.Range.Font.Name = "新宋体"; table.Range.ParagraphFormat.Alignment = MSWord.WdParagraphAlignment.wdAlignParagraphLeft; //表格文本居中 table.Range.Cells.VerticalAlignment = MSWord.WdCellVerticalAlignment.wdCellAlignVerticalBottom; //文本垂直贴到底部 // 设置table边框样式 table.Borders.OutsideLineStyle = MSWord.WdLineStyle.wdLineStyleSingle; //表格外框是双线 table.Borders.InsideLineStyle = MSWord.WdLineStyle.wdLineStyleSingle; //表格内框是单线 // 加粗 table.Rows[1].Range.Font.Bold = 1; table.Rows[1].Range.Font.Size = 9.5F; // 加粗 table.Rows[2].Range.Font.Bold = 1; table.Rows[2].Range.Font.Size = 9.5F; }