public IList <DataTableColumnDefCollection> GetTableStruct(List <string> tables) { var strSql = @" select cols.table_schema tableschema ,quote_ident(cols.table_name) tablename ,cols.ordinal_position ordinal ,cols.column_name colname ,col_description((cols.table_schema || '.' ||cols.table_name)::regclass::oid,cols.ordinal_position ) as description ,case when position('_' in cols.udt_name) > 0 then regexp_replace(cols.udt_name,'(_)(.*)','\2[]') else cols.udt_name end datatype ,case cols.data_type when 'character varying' then cols.character_maximum_length when 'numeric' then cols.numeric_precision else null end length ,cols.numeric_scale as precision , CASE WHEN position( 'extval(' in cols.column_default) > 1 THEN '√' ELSE '' END as identity , case when EXISTS ( select a.table_schema,a.table_name,b.constraint_name,a.ordinal_position as position,a.column_name as key_column from information_schema.table_constraints b inner join information_schema.key_column_usage a on a.constraint_name = b.constraint_name and a.constraint_schema = b.constraint_schema and a.constraint_name = b.constraint_name where b.constraint_type = 'PRIMARY KEY' and a.table_schema = cols.table_schema and a.table_name = cols.table_name and a.column_name = cols.column_name) then '√' ELSE '' END primaykey ,case when cols.is_nullable = 'YES' THEN '√' ELSE '' END as isnull ,cols.column_default coldefault ,'' as memo from information_schema.columns cols inner join information_schema.tables tbs on cols.TABLE_NAME = tbs.TABLE_NAME where tbs.table_type = 'BASE TABLE' ORDER BY 1, 2 "; var result = help.Query <DataTableColumnDef>(strSql).ToList(); IList <DataTableColumnDefCollection> result11 = new List <DataTableColumnDefCollection>(); List <DataTable> lst = new List <DataTable>(); var result11grp = result.GroupBy(p => new { p.tableschema, p.tablename }); foreach (var table in tables) { var dtdefs = result11grp.Where(p => table == $"{p.Key.tableschema}.{p.Key.tablename}").FirstOrDefault(); if (dtdefs != null) { DataTableColumnDefCollection col = new DataTableColumnDefCollection(); col.TableName = table; col.AddRange(dtdefs.AsEnumerable()); result11.Add(col); } } return(result11); }
/// <summary> /// 导出表数据为html格式 居中表格样式 /// </summary> /// <param name="dt">DataTable,需要给TableName赋值</param> /// <param name="KeepNull">保持Null为Null值,否则为空</param> /// <param name="Path">保存路径</param> /// <param name="hasReturn">携带返回目录链接</param> /// <param name="tableDesc">携带返回目录链接</param> /// <param name="alternateColor">是否隔行变色</param> public static void CreateHtml(DataTableColumnDefCollection dt, bool KeepNull, string Path, bool hasReturn = true, string tableDesc = "", bool alternateColor = true) { var code = new StringBuilder(); code.AppendLine("<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">"); code.AppendLine("<html xmlns=\"http://www.w3.org/1999/xhtml\">"); code.AppendLine("<head>"); code.AppendLine(" <META http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\"> "); code.AppendLine($" <title>{dt.TableName}</title>"); code.AppendLine(" <style type=\"text/css\">"); code.AppendLine(" body"); code.AppendLine(" {"); code.AppendLine(" font-size: 9pt;"); code.AppendLine(" }"); code.AppendLine(" .styledb"); code.AppendLine(" {"); code.AppendLine(" font-size: 14px;"); code.AppendLine(" }"); code.AppendLine(" .styletab"); code.AppendLine(" {"); code.AppendLine(" font-size: 14px;"); code.AppendLine(" padding-top: 15px;"); code.AppendLine(" }"); code.AppendLine(" a"); code.AppendLine(" {"); code.AppendLine(" color: #015FB6;"); code.AppendLine(" }"); code.AppendLine(" a:link, a:visited, a:active"); code.AppendLine(" {"); code.AppendLine(" color: #015FB6;"); code.AppendLine(" text-decoration: none;"); code.AppendLine(" }"); code.AppendLine(" a:hover"); code.AppendLine(" {"); code.AppendLine(" color: #E33E06;"); code.AppendLine(" }"); if (alternateColor) { code.AppendLine(" tr:nth-child(even) {bgcolor: #CCC}"); code.AppendLine(" tr:nth-child(odd) {bgcolor: #FFF}"); } code.AppendLine(" </style>"); code.AppendLine("</head>"); code.AppendLine("<body>"); code.AppendLine(" <div style=\"text-align: center\">"); code.AppendLine(" <div>"); code.AppendLine(" <table border=\"0\" cellpadding=\"5\" cellspacing=\"0\" width=\"95%\" style=\"text-align: left\">"); code.AppendLine(" <tr>"); code.AppendLine(" <td bgcolor=\"#FBFBFB\">"); code.AppendLine(" <table cellspacing=\"0\" cellpadding=\"5\" border=\"1\" width=\"100%\" bordercolorlight=\"#D7D7E5\" bordercolordark=\"#D3D8E0\">"); code.AppendLine(" <caption>"); code.AppendLine($" <div class=\"styletab\">{dt.TableName}{(string.IsNullOrEmpty(tableDesc) ? string.Empty : " (" + tableDesc + ") ")}{(hasReturn ? "<a href ='../../数据库表目录.html' style = 'float: left; margin-top: 6px;'>返回目录</a>" : string.Empty)}</div>"); //.FormatString(dt.TableName, //tableDesc.Length == 0 ? string.Empty : " (" + tableDesc + ") ", //(hasReturn ? "<a href='../数据库表目录.html' style='float: right; margin-top: 6px;'>返回目录</a>" : string.Empty)); code.AppendLine(" </caption>"); code.AppendLine(" <tr bgcolor=\"#DEEBF7\">"); //bgcolor="#DEEBF7" //构建表头 Type itemtype = typeof(DataTableColumnDef); var dtStructProps = itemtype.GetProperties().Where(p => p.CustomAttributes.Count() > 0).ToArray(); for (int i = 0; i < dtStructProps.Length; i++) { var dc = dtStructProps[i]; var prop = dc.GetCustomAttribute <DescriptionAttribute>(); code.AppendLine($" <td>{prop.Description}</td>");//属性描述 作为列名 } //foreach (DataColumn dc in dt.Columns) //{ // code.AppendLine($" <td>{dc.ColumnName}</td>");//.FormatString(dc.ColumnName)); //} code.AppendLine(" </tr>"); //构建数据行 var dtsort = dt.OrderBy(p => p.ordinal); foreach (var dr in dtsort) { code.AppendLine(" <tr>"); for (int i = 0; i < dtStructProps.Length; i++) { var dc = dtStructProps[i]; if (KeepNull && dc.GetValue(dr) == DBNull.Value) { code.AppendLine(" <td> </td>"); } else { code.AppendLine($" <td>{(!string.IsNullOrWhiteSpace(dc.GetValue(dr)?.ToString()) ? dc.GetValue(dr)?.ToString() : " ")}</td>"); //.FormatString( //dr[dc.ColumnName].ToString().Trim().Length > 0 ? dr[dc.ColumnName].ToString() : " ")); } } code.AppendLine(" </tr>"); } code.AppendLine(" </table>"); code.AppendLine(" </td>"); code.AppendLine(" </tr>"); code.AppendLine(" </table>"); code.AppendLine(" </div>"); code.AppendLine(" </div>"); code.AppendLine("</body>"); code.AppendLine("</html>"); File.WriteAllText(Path, code.ToString(), Encoding.GetEncoding("gb2312")); //File.WriteAllText(Path, code.ToString(), Encoding.UTF8); }