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