打开数据库编辑器,输入以下代码并执行(F5)
SELECT
--表名 = case when a.colorder=1 then d.name else '' end,
--表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end,
序号 = a.colorder,
列名 = a.name,
--数据类型 = b.name,
数据类型 = b.name+'('+CONVERT(VARCHAR,COLUMNPROPERTY(a.id,a.name,'PRECISION'))+')',
--长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
--小数位 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
--标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '是'else '' end,
--主键 = 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 '是' else '' end,
--占用字节数 = a.length,
允许空 = case when a.isnullable=1 then '是'else '否' end,
--默认值 = isnull(e.text,''),
说明 = 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='B_Table' --如果只查询指定表,加上此条件
order by
a.id,a.colorder
全选查询结果,鼠标右键“连同标题一起复制”
新建 Excel 文件,粘贴刚才复制的内容,Excel表格中会出现粘贴的内容,再选中Excel表格的内容,复制
新建 Word 文件,依次列出表名称标题,依次选中每个表名称标题,并设置样式(右键也可弹出样式菜单),导航菜单栏 —》引用 —》目录 —》自动目录 —》即可生成目录菜单。
根据粘贴的表结构内容,粘贴到与表名称相对应的位置。导航菜单栏 —》引用 —》目录 —》更新目录。
*
*
生成Word文档 npoi
SqlSugar查询数据库信息
using System;
using System.Collections.Generic;
using AutoMapper;
using POS.Entity;
namespace POS.Repository
{
public interface IBulletinRepository : IRepository<BulletinDo>
{
List<SysteTable> GetAllTable();
List<TableField> GetTableField(string tableName);
}
public class BulletinRepository : BaseRepository<BulletinDo, POSDbContext>, IBulletinRepository
{
public BulletinRepository(POSDbContext context) : base(context)
{
}
public List<SysteTable> GetAllTable()
{
string sql = "select name from SysObjects where XType='U'";
return Context.Ado.SqlQuery<SysteTable>(sql);
}
public List<TableField> GetTableField(string tableName)
{
string sql = @"SELECT
--表名 = case when a.colorder=1 then d.name else '' end,
--表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end,
序号 = a.colorder,
列名 = a.name,
--标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '是'else '' end,
--主键 = 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 '是' else '' end,
数据类型 = b.name+'('+CONVERT(VARCHAR,COLUMNPROPERTY(a.id,a.name,'PRECISION'))+')',
--占用字节数 = a.length,
--长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
--小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空 = case when a.isnullable=1 then '是'else '否' end,
--默认值 = isnull(e.text,''),
说明 = 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= '" + tableName +
@"'order by a.id,a.colorder";
return Context.Ado.SqlQuery<TableField>(sql);
}
}
public class SysteTable
{
public string name { get; set; }
}
public class TableField
{
public string 序号 { get; set; }
public string 列名 { get; set; }
public string 数据类型 { get; set; }
public string 允许空 { get; set; }
public string 说明 { get; set; }
}
}
*
*
Controller(直接查看Index页面即可下载Word文件)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using POS.Web.Filter;
using NPOI.XWPF.UserModel;
using System.Text;
using System.IO;
using NPOI.OpenXmlFormats.Wordprocessing;
using POS.Repository;
namespace POS.Web.Controllers
{
[UserInfo]
public class BulletinController : ControllerBase
{
private readonly IBulletinRepository _bulletinRepo;
public BulletinController(IBulletinRepository bulletinRepo)
{
_bulletinRepo = bulletinRepo;
}
public ActionResult Index()
{
//return View();
return File(CreateWordNew(), "application/octet-stream", $"文件名_{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.docx");
}
private void CreateWord()
{
//创建document对象
var doc = new XWPFDocument();
//创建段落对象1
var p1 = doc.CreateParagraph();
p1.Alignment = ParagraphAlignment.CENTER; //字体居中
//创建run对象
//本节提到的所有样式都是基于XWPFRun的,
//你可以把XWPFRun理解成一小段文字的描述对象,
//这也是Word文档的特征,即文本描述性文档。
//来自Tony Qu http://tonyqus.sinaapp.com/archives/609
var runTitle = p1.CreateRun();
runTitle.IsBold = true;
runTitle.SetText("军检验收单");
runTitle.FontSize = 16;
runTitle.SetFontFamily("宋体", FontCharRange.None); //设置雅黑字体
//创建段落对象2
var p2 = doc.CreateParagraph();
var run1 = p2.CreateRun();
run1.SetText(" 军检项目号:");
run1.FontSize = 12;
run1.SetFontFamily("华文楷体", FontCharRange.None); //设置雅黑字体
#region 头部(6 rows)
//基本row12,列5;头部6行,4列
var tableTop = doc.CreateTable(6, 5);
tableTop.Width = 1000 * 5;
tableTop.SetColumnWidth(0, 1300); /* 设置列宽 */
tableTop.SetColumnWidth(1, 500); /* 设置列宽 */
tableTop.SetColumnWidth(2, 1000); /* 设置列宽 */
tableTop.SetColumnWidth(3, 500); /* 设置列宽 */
tableTop.SetColumnWidth(4, 1700); /* 设置列宽 */
tableTop.GetRow(0).MergeCells(1, 4); /* 合并行 */
tableTop.GetRow(0).GetCell(0).SetParagraph(SetCellText(doc, tableTop, "产品名称"));
tableTop.GetRow(0).GetCell(1).SetParagraph(SetCellText(doc, tableTop, " "));
tableTop.GetRow(1).MergeCells(1, 4);
tableTop.GetRow(1).GetCell(0).SetParagraph(SetCellText(doc, tableTop, "项目名称"));
tableTop.GetRow(1).GetCell(1).SetParagraph(SetCellText(doc, tableTop, " "));
tableTop.GetRow(2).MergeCells(1, 4);
tableTop.GetRow(2)
.GetCell(0)
.SetParagraph(SetCellText(doc, tableTop, "施工依据", ParagraphAlignment.CENTER, 45));
tableTop.GetRow(2)
.GetCell(1)
.SetParagraph(SetCellText(doc, tableTop, " ", ParagraphAlignment.CENTER, 45));
tableTop.GetRow(3).GetCell(0).SetParagraph(SetCellText(doc, tableTop, "检验方式"));
tableTop.GetRow(3).GetCell(1).SetParagraph(SetCellText(doc, tableTop, "独立检验"));
tableTop.GetRow(3).GetCell(2).SetParagraph(SetCellText(doc, tableTop, " "));
tableTop.GetRow(3).GetCell(3).SetParagraph(SetCellText(doc, tableTop, "联合检验"));
tableTop.GetRow(3).GetCell(4).SetParagraph(SetCellText(doc, tableTop, " "));
tableTop.GetRow(4).MergeCells(3, 4);
tableTop.GetRow(4).GetCell(0).SetParagraph(SetCellText(doc, tableTop, "设备名称及编号"));
tableTop.GetRow(4).GetCell(1).SetParagraph(SetCellText(doc, tableTop, " "));
tableTop.GetRow(4).GetCell(2).SetParagraph(SetCellText(doc, tableTop, "设备制造厂"));
tableTop.GetRow(4).GetCell(3).SetParagraph(SetCellText(doc, tableTop, " "));
//tableTop.GetRow(4).GetCell(3).SetBorderBottom(XWPFtableTop.XWPFBorderType.NONE,0,0,"");
tableTop.GetRow(5).MergeCells(0, 4);
var para = new CT_P();
var pCell = new XWPFParagraph(para, tableTop.Body);
pCell.Alignment = ParagraphAlignment.LEFT; //字体居中
var r1c1 = pCell.CreateRun();
r1c1.SetText("检验要素共9项");
r1c1.FontSize = 12;
r1c1.SetFontFamily("华文楷体", FontCharRange.None); //设置雅黑字体
tableTop.GetRow(5).GetCell(0).SetParagraph(pCell);
#endregion
#region 检验要素列表部分(数据库读取循环显示)
var tableContent = doc.CreateTable(45, 5);
tableContent.Width = 1000 * 5;
tableContent.SetColumnWidth(0, 300); /* 设置列宽 */
tableContent.SetColumnWidth(1, 1000); /* 设置列宽 */
tableContent.SetColumnWidth(2, 1000); /* 设置列宽 */
tableContent.SetColumnWidth(3, 1000); /* 设置列宽 */
tableContent.SetColumnWidth(4, 1700); /* 设置列宽 */
tableContent.GetRow(0).GetCell(0).SetParagraph(SetCellText(doc, tableContent, "序号"));
tableContent.GetRow(0).GetCell(1).SetParagraph(SetCellText(doc, tableContent, "检验要素"));
tableContent.GetRow(0).GetCell(2).SetParagraph(SetCellText(doc, tableContent, "指标要求"));
tableContent.GetRow(0).GetCell(3).SetParagraph(SetCellText(doc, tableContent, "实测值"));
tableContent.GetRow(0).GetCell(4).SetParagraph(SetCellText(doc, tableContent, "测量工具编号及有效期"));
for (var i = 1; i < 45; i++)
{
tableContent.GetRow(i)
.GetCell(0)
.SetParagraph(SetCellText(doc, tableContent, i.ToString(), ParagraphAlignment.CENTER, 50));
tableContent.GetRow(i)
.GetCell(1)
.SetParagraph(SetCellText(doc, tableContent, "检验要素", ParagraphAlignment.CENTER, 50));
tableContent.GetRow(i)
.GetCell(2)
.SetParagraph(SetCellText(doc, tableContent, "指标要求", ParagraphAlignment.CENTER, 50));
tableContent.GetRow(i)
.GetCell(3)
.SetParagraph(SetCellText(doc, tableContent, "实测值", ParagraphAlignment.CENTER, 50));
tableContent.GetRow(i)
.GetCell(4)
.SetParagraph(SetCellText(doc, tableContent, "测量工具编号及有效期", ParagraphAlignment.CENTER, 50));
}
#endregion
#region 底部内容
var tableBottom = doc.CreateTable(5, 4);
tableBottom.Width = 1000 * 5;
tableBottom.SetColumnWidth(0, 1000); /* 设置列宽 */
tableBottom.SetColumnWidth(1, 1500); /* 设置列宽 */
tableBottom.SetColumnWidth(2, 1000); /* 设置列宽 */
tableBottom.SetColumnWidth(3, 1500); /* 设置列宽 */
tableBottom.GetRow(0).MergeCells(0, 3); /* 合并行 */
tableBottom.GetRow(0)
.GetCell(0)
.SetParagraph(SetCellText(doc, tableBottom, "附件:", ParagraphAlignment.LEFT, 80));
tableBottom.GetRow(0).Height = 30;
tableBottom.GetRow(1).MergeCells(0, 3); /* 合并行 */
tableBottom.GetRow(1)
.GetCell(0)
.SetParagraph(SetCellText(doc, tableBottom, "检验结论:", ParagraphAlignment.LEFT, 80));
tableBottom.GetRow(1).Height = 30;
tableBottom.GetRow(2).GetCell(0).SetParagraph(SetCellText(doc, tableBottom, "施工部门"));
tableBottom.GetRow(2).GetCell(1).SetParagraph(SetCellText(doc, tableBottom, " "));
tableBottom.GetRow(2).GetCell(2).SetParagraph(SetCellText(doc, tableBottom, "报验日期"));
tableBottom.GetRow(2).GetCell(3).SetParagraph(SetCellText(doc, tableBottom, " "));
tableBottom.GetRow(3).GetCell(0).SetParagraph(SetCellText(doc, tableBottom, "军检次数"));
tableBottom.GetRow(3).GetCell(1).SetParagraph(SetCellText(doc, tableBottom, " "));
tableBottom.GetRow(3).GetCell(2).SetParagraph(SetCellText(doc, tableBottom, "军检日期"));
tableBottom.GetRow(3).GetCell(3).SetParagraph(SetCellText(doc, tableBottom, " "));
tableBottom.GetRow(4).GetCell(0).SetParagraph(SetCellText(doc, tableBottom, "检验员"));
tableBottom.GetRow(4).GetCell(1).SetParagraph(SetCellText(doc, tableBottom, " "));
tableBottom.GetRow(4).GetCell(2).SetParagraph(SetCellText(doc, tableBottom, "军代表"));
tableBottom.GetRow(4).GetCell(3).SetParagraph(SetCellText(doc, tableBottom, " "));
#endregion
#region 保存导出WebForm
var ms = new MemoryStream();
doc.Write(ms);
Response.AddHeader("Content-Disposition",
string.Format("attachment; filename={0}.doc",
HttpUtility.UrlEncode("文件名" + "_" + DateTime.Now.ToString("yyyyMMddHHmmssfff"),
Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
Response.End();
ms.Close();
ms.Dispose();
#endregion
}
private byte[] CreateWordNew()
{
var tableList = _bulletinRepo.GetAllTable();
//创建document对象
var doc = new XWPFDocument();
//创建段落对象1
var p1 = doc.CreateParagraph();
p1.Alignment = ParagraphAlignment.CENTER;
var runTitle = p1.CreateRun();
runTitle.IsBold = true;
runTitle.SetText("数据库名:dbHospital");
runTitle.FontSize = 12;
runTitle.SetFontFamily("宋体", FontCharRange.None);
foreach (var item in tableList)
{
//创建段落对象2
var pFor = doc.CreateParagraph();
var runFor = pFor.CreateRun();
runFor.SetText($"表名:{item.name}");
runFor.FontSize = 10;
runFor.IsBold = true;
runFor.SetFontFamily("宋体", FontCharRange.None);
pFor.Alignment = ParagraphAlignment.CENTER;
#region 检验要素列表部分(数据库读取循环显示)
var result = _bulletinRepo.GetTableField(item.name);
int total = result.Count() + 1;
var tableContent = doc.CreateTable(total, 5);
tableContent.Width = 1000 * 5;
tableContent.SetColumnWidth(0, 300); /* 设置列宽 */
tableContent.SetColumnWidth(1, 1000); /* 设置列宽 */
tableContent.SetColumnWidth(2, 1000); /* 设置列宽 */
tableContent.SetColumnWidth(3, 1000); /* 设置列宽 */
tableContent.SetColumnWidth(4, 1700); /* 设置列宽 */
tableContent.GetRow(0).GetCell(0).SetParagraph(SetCellText(doc, tableContent, "序号"));
tableContent.GetRow(0).GetCell(1).SetParagraph(SetCellText(doc, tableContent, "列名"));
tableContent.GetRow(0).GetCell(2).SetParagraph(SetCellText(doc, tableContent, "数据类型"));
tableContent.GetRow(0).GetCell(3).SetParagraph(SetCellText(doc, tableContent, "允许空"));
tableContent.GetRow(0).GetCell(4).SetParagraph(SetCellText(doc, tableContent, "说明"));
int i = 1;
foreach (var fieldItem in result)
{
tableContent.GetRow(i)
.GetCell(0)
.SetParagraph(SetCellText(doc, tableContent, fieldItem.序号, ParagraphAlignment.LEFT, 24));
tableContent.GetRow(i)
.GetCell(1)
.SetParagraph(SetCellText(doc, tableContent, fieldItem.列名, ParagraphAlignment.LEFT, 24));
tableContent.GetRow(i)
.GetCell(2)
.SetParagraph(SetCellText(doc, tableContent, fieldItem.数据类型, ParagraphAlignment.LEFT, 24));
tableContent.GetRow(i)
.GetCell(3)
.SetParagraph(SetCellText(doc, tableContent, fieldItem.允许空, ParagraphAlignment.LEFT, 24));
tableContent.GetRow(i)
.GetCell(4)
.SetParagraph(SetCellText(doc, tableContent, fieldItem.说明, ParagraphAlignment.LEFT, 24));
i++;
}
i = 0;
#endregion
}
#region 保存导出WebForm
var ms = new MemoryStream();
doc.Write(ms);
ms.Flush();
ms.Close();
ms.Dispose();
#endregion
return ms.ToArray();
}
/// <summary>
/// 设置字体格式
/// </summary>
/// <param name="doc"></param>
/// <param name="table"></param>
/// <param name="setText"></param>
/// <returns></returns>
public XWPFParagraph SetCellText(XWPFDocument doc, XWPFTable table, string setText)
{
//table中的文字格式设置
var para = new CT_P();
var pCell = new XWPFParagraph(para, table.Body);
pCell.Alignment = ParagraphAlignment.CENTER; //字体居中
pCell.VerticalAlignment = TextAlignment.CENTER; //字体居中
pCell.FillBackgroundColor = "#D3D3D3";
var r1c1 = pCell.CreateRun();
r1c1.SetText(setText);
r1c1.FontSize = 9;
r1c1.SetFontFamily("宋体", FontCharRange.None); //设置雅黑字体
r1c1.SetTextPosition(24); //设置高度
return pCell;
}
/// <summary>
/// 设置单元格格式
/// </summary>
/// <param name="doc">doc对象</param>
/// <param name="table">表格对象</param>
/// <param name="setText">要填充的文字</param>
/// <param name="align">文字对齐方式</param>
/// <param name="textPos">rows行的高度</param>
/// <returns></returns>
public XWPFParagraph SetCellText(XWPFDocument doc, XWPFTable table, string setText, ParagraphAlignment align,
int textPos)
{
var para = new CT_P();
var pCell = new XWPFParagraph(para, table.Body);
//pCell.Alignment = ParagraphAlignment.LEFT;//字体
pCell.Alignment = align;
pCell.VerticalAlignment = TextAlignment.CENTER; //字体居中
var r1c1 = pCell.CreateRun();
r1c1.SetText(setText);
r1c1.FontSize = 9;
r1c1.SetFontFamily("宋体", FontCharRange.None); //设置雅黑字体
r1c1.SetTextPosition(textPos); //设置高度
return pCell;
}
}
}
查询【数据库】所有【表】名称
select o.name as TableName,isnull(p.value, '') AS TableMemo
from sysobjects o
left join sys.extended_properties p On o.id = p.major_id and p.minor_id = 0 and p.name = 'MS_Description'
where xtype = 'U' and o.name not in ('sysdiagrams', 'sp_upgraddiagrams')
order by o.name
*
*
*
*
*