public void CreateToWord(List<
string> list,
string conStr,
string db)
{
XWPFDocument doc =
new XWPFDocument();
//创建新的word文档
XWPFParagraph p1 = doc.CreateParagraph();
//向新文档中添加段落
p1.Alignment =
ParagraphAlignment.CENTER;
XWPFRun r1 =
p1.CreateRun();
r1.FontFamily =
"微软雅黑";
r1.FontSize =
22;
r1.IsBold =
true;
//向该段落中添加文字
r1.SetText(db+
"数据库说明文档");
//XWPFParagraph p2 = doc.CreateParagraph();
//XWPFRun r2 = p2.CreateRun();
//r2.SetText("测试段落二");
#region 创建一个表格
if (list.Count >
0)
{
foreach (
var item
in list)
{
XWPFParagraph p3 = doc.CreateParagraph();
//向新文档中添加段落
p3.Alignment =
ParagraphAlignment.LEFT;
XWPFRun r3 = p3.CreateRun();
//向该段落中添加文字
r3.FontFamily =
"微软雅黑";
r3.FontSize =
18;
r3.IsBold =
true;
r3.SetText("表名:"+
item);
//从第二行开始 因为第一行是表头
int i =
1;
var tabledetaillist =
service.GetTableDetail(item, conStr);
XWPFTable table = doc.CreateTable(tabledetaillist.Count +
1,
9);
table.Width =
5000;
#region 设置表头
//table.GetRow(0).GetCell(0).SetText("数据库名称");
XWPFParagraph pI = table.GetRow(
0).GetCell(
0).AddParagraph();
XWPFRun rI =
pI.CreateRun();
rI.FontFamily =
"微软雅黑";
rI.FontSize =
12;
rI.IsBold =
true;
rI.SetText("序号");
XWPFParagraph pI1 = table.GetRow(
0).GetCell(
1).AddParagraph();
XWPFRun rI1 =
pI1.CreateRun();
rI1.FontFamily =
"微软雅黑";
rI1.FontSize =
12;
rI1.IsBold =
true;
rI1.SetText("字段名称");
XWPFParagraph pI2 = table.GetRow(
0).GetCell(
2).AddParagraph();
XWPFRun rI2 =
pI2.CreateRun();
rI2.FontFamily =
"微软雅黑";
rI2.FontSize =
12;
rI2.IsBold =
true;
rI2.SetText("标识");
XWPFParagraph pI3 = table.GetRow(
0).GetCell(
3).AddParagraph();
XWPFRun rI3 =
pI3.CreateRun();
rI3.FontFamily =
"微软雅黑";
rI3.FontSize =
12;
rI3.IsBold =
true;
rI3.SetText("主键");
XWPFParagraph pI4 = table.GetRow(
0).GetCell(
4).AddParagraph();
XWPFRun rI4 =
pI4.CreateRun();
rI4.FontFamily =
"微软雅黑";
rI4.FontSize =
12;
rI4.IsBold =
true;
rI4.SetText("字段类型");
XWPFParagraph pI5 = table.GetRow(
0).GetCell(
5).AddParagraph();
XWPFRun rI5 =
pI5.CreateRun();
rI5.FontFamily =
"微软雅黑";
rI5.FontSize =
12;
rI5.IsBold =
true;
rI5.SetText("字段长度");
XWPFParagraph pI6 = table.GetRow(
0).GetCell(
6).AddParagraph();
XWPFRun rI6 =
pI6.CreateRun();
rI6.FontFamily =
"微软雅黑";
rI6.FontSize =
12;
rI6.IsBold =
true;
rI6.SetText("允许空");
XWPFParagraph pI7 = table.GetRow(
0).GetCell(
7).AddParagraph();
XWPFRun rI7 =
pI7.CreateRun();
rI7.FontFamily =
"微软雅黑";
rI7.FontSize =
12;
rI7.IsBold =
true;
rI7.SetText("字段默认值");
XWPFParagraph pI8 = table.GetRow(
0).GetCell(
8).AddParagraph();
XWPFRun rI8 =
pI8.CreateRun();
rI8.FontFamily =
"微软雅黑";
rI8.FontSize =
12;
rI8.IsBold =
true;
rI8.SetText("字段说明");
#endregion
if (tabledetaillist !=
null && tabledetaillist.Count >
0)
{
foreach (
var itm
in tabledetaillist)
{
//第一列
XWPFParagraph pIO = table.GetRow(i).GetCell(
0).AddParagraph();
XWPFRun rIO =
pIO.CreateRun();
//rIO.FontFamily = "微软雅黑";
rIO.FontSize =
12;
rIO.IsBold =
true;
rIO.SetText(itm.index.ToString());
//第二列
XWPFParagraph pIO2 = table.GetRow(i).GetCell(
1).AddParagraph();
XWPFRun rIO2 =
pIO2.CreateRun();
//rIO2.FontFamily = "微软雅黑";
rIO2.FontSize =
12;
rIO2.IsBold =
true;
rIO2.SetText(itm.Title);
XWPFParagraph pIO3 = table.GetRow(i).GetCell(
2).AddParagraph();
XWPFRun rIO3 =
pIO3.CreateRun();
//rIO3.FontFamily = "微软雅黑";
rIO3.FontSize =
12;
rIO3.IsBold =
true;
rIO3.SetText(itm.isMark.ToString());
XWPFParagraph pIO4 = table.GetRow(i).GetCell(
3).AddParagraph();
XWPFRun rIO4 =
pIO4.CreateRun();
//rIO4.FontFamily = "微软雅黑";
rIO4.FontSize =
12;
rIO4.IsBold =
true;
rIO4.SetText(itm.isPK.ToString());
XWPFParagraph pIO5 = table.GetRow(i).GetCell(
4).AddParagraph();
XWPFRun rIO5 =
pIO5.CreateRun();
//rIO5.FontFamily = "微软雅黑";
rIO5.FontSize =
12;
rIO5.IsBold =
true;
rIO5.SetText(itm.FieldType);
XWPFParagraph pIO6 = table.GetRow(i).GetCell(
5).AddParagraph();
XWPFRun rIO6 =
pIO6.CreateRun();
//rIO6.FontFamily = "微软雅黑";
rIO6.FontSize =
12;
rIO6.IsBold =
true;
rIO6.SetText(itm.fieldLenth.ToString());
XWPFParagraph pIO7 = table.GetRow(i).GetCell(
6).AddParagraph();
XWPFRun rIO7 =
pIO7.CreateRun();
//rIO7.FontFamily = "微软雅黑";
rIO7.FontSize =
12;
rIO7.IsBold =
true;
rIO7.SetText(itm.isAllowEmpty.ToString());
XWPFParagraph pIO8 = table.GetRow(i).GetCell(
7).AddParagraph();
XWPFRun rIO8 =
pIO8.CreateRun();
//rIO8.FontFamily = "微软雅黑";
rIO8.FontSize =
12;
rIO8.IsBold =
true;
rIO8.SetText(itm.defaultValue.ToString());
XWPFParagraph pIO9 = table.GetRow(i).GetCell(
8).AddParagraph();
XWPFRun rIO9 =
pIO9.CreateRun();
//rIO9.FontFamily = "微软雅黑";
rIO9.FontSize =
12;
rIO9.IsBold =
true;
rIO9.SetText(itm.fieldDesc);
i++
;
}
}
}
}
#endregion
#region 存储过程
XWPFParagraph p2 =
doc.CreateParagraph();
XWPFRun r2 =
p2.CreateRun();
r2.FontSize =
16;
r2.SetText("存储过程");
List<ProcModel> proclist =
new List<ProcModel>
();
proclist =
service.GetProcList(conStr);
if(proclist.Count>
0)
{
foreach(
var item
in proclist)
{
//存储过程名称
XWPFParagraph pro1 =
doc.CreateParagraph();
XWPFRun rpro1 =
pro1.CreateRun();
rpro1.FontSize =
14;
rpro1.IsBold =
true;
rpro1.SetText("存储过程名称:"+
item.procName);
//存储过程 详情
XWPFParagraph pro2 =
doc.CreateParagraph();
XWPFRun rpro2 =
pro2.CreateRun();
rpro2.FontSize =
12;
rpro2.SetText(item.proDerails);
}
}
#endregion
#region 试图
XWPFParagraph v2 =
doc.CreateParagraph();
XWPFRun vr2 =
v2.CreateRun();
vr2.FontSize =
16;
vr2.SetText("视图");
List<ViewModel> viewlist =
new List<ViewModel>
();
viewlist =
service.GetViewList(conStr);
if (proclist.Count >
0)
{
foreach (
var item
in viewlist)
{
//存储过程名称
XWPFParagraph vro1 =
doc.CreateParagraph();
XWPFRun vpro1 =
vro1.CreateRun();
vpro1.FontSize =
14;
vpro1.IsBold =
true;
vpro1.SetText("视图名称:" +
item.viewName);
//存储过程 详情
XWPFParagraph vro2 =
doc.CreateParagraph();
XWPFRun vpro2 =
vro2.CreateRun();
vpro2.FontSize =
12;
vpro2.SetText(item.viewDerails);
}
}
#endregion
FileStream sw = File.Create(
"../../Doc/db.docx");
//...
doc.Write(sw);
//...
sw.Close();
//在服务端生成文件
FileInfo file =
new FileInfo(
"../../Doc/db.docx");
//文件保存路径及名称
}
操作Word
NOPI操作word
/// <summary>
/// 测试连接数据库是否成功
/// </summary>
/// <returns></returns>
public bool ConnectionTest(string conStr)
{
//创建连接对象
mySqlConnection = new SqlConnection(conStr);
try
{
//Open DataBase
//打开数据库
mySqlConnection.Open();
IsCanConnectioned = true;
}
catch
{
//Can not Open DataBase
//打开不成功 则连接不成功
IsCanConnectioned = false;
}
finally
{
//Close DataBase
//关闭数据库连接
mySqlConnection.Close();
}
//mySqlConnection is a SqlConnection object
if (mySqlConnection.State == ConnectionState.Closed || mySqlConnection.State == ConnectionState.Broken)
{
//Connection is not available
return IsCanConnectioned;
}
else
{
//Connection is available
return IsCanConnectioned;
}
}
测试服务器是否连接成功
测试数据库是否连接成功
/// <summary>
/// 获取数据库列表
/// </summary>
/// <param name="conStr"></param>
/// <returns></returns>
public List<string> GetDBNameList(string conStr)
{
//List<DBName> list =new List<DBName>();
string sql = "select [name] from master.dbo.sysdatabases where DBId>6 Order By [Name] ";
try
{
using (SqlConnection connection = new SqlConnection(conStr))
{
var list = connection.Query<string>(sql).ToList();
return list;
}
}
catch
{
return null;
}
}
获取数据库列表
获取数据库列表
/// <summary>
/// 获取字段的信息
/// </summary>
/// <param name="tableName"></param>
/// <param name="conStr"></param>
/// <returns></returns>
public List<TableDetail> GetTableDetail(string tableName, string conStr)
{
var list = new List<TableDetail>();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT [index] = a.colorder, Title = a.name, isMark = CASE WHEN COLUMNPROPERTY(a.id, a.name, ‘IsIdentity‘) = 1 THEN ‘1‘ ELSE ‘0‘ END, ");
sb.Append("isPK = 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 ‘1‘ ELSE ‘0‘ END, ");
sb.Append(" FieldType = b.name,fieldLenth = COLUMNPROPERTY(a.id, a.name, ‘PRECISION‘),isAllowEmpty = CASE WHEN a.isnullable = 1 THEN ‘1‘ ELSE ‘0‘ END, defaultValue = ISNULL(e.text, ‘‘), fieldDesc = ISNULL(g.[value], ‘‘) ");
sb.Append("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 ");
sb.Append("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条件,tablename是要查询的表名;去除红色where条件查询说有的表信息
sb.Append("WHERE d.name = ‘"+ tableName + "‘ ORDER BY a.id, a.colorder, d.name");
try
{
using (SqlConnection connection = new SqlConnection(conStr))
{
list = connection.Query<TableDetail>(sb.ToString()).ToList();
}
}
catch
{ }
return list;
}
获取表字段详情
获取字段信息
/// <summary>
/// 获取特定数据库里面的存储过程
/// </summary>
/// <param name="conStr"></param>
/// <param name="db"></param>
/// <returns></returns>
public List<ProcModel> GetProcList(string conStr)
{
var list = new List<ProcModel>();
string sql = @" select name as procName, (select text from syscomments where id=OBJECT_ID(name)) as proDerails
from dbo.sysobjects o where OBJECTPROPERTY(id, N‘IsProcedure‘) = 1 order by name ";
try
{
// http://www.cnblogs.com/minideas/archive/2009/10/29/1591891.html
using (SqlConnection connection = new SqlConnection(conStr))
{
list = connection.Query<ProcModel>(sql).ToList();
}
}
catch
{
}
return list;
}
获取特定数据库里面的存储过程
获取特定数据库里面的存储过程
实现思路:
1、首先获取数据库的字符串,测试链接是否成功,
2、通过脚本获取该服务器的数据库列表。
3、根据数据库找到该数据库的所有数据表
4、通过脚本找到该数据表所有的字段信息
5、使用Npoi技术把信息导出到Word中去。
数据库文档生成工具
标签:思路 微软雅黑 region field style 数据 说明文档 ons sid