C# SQL数据库生成WORD文档

1.创建一个窗体
添加数据库链接相关控件：数据库服务器IP、数据库名、数据库帐号、数据库密码
2.将下列代码复制到代码区
private SqlConnectionStringBuilder CreateConnectionString()
{
SqlConnectionStringBuilder csbuilder = new SqlConnectionStringBuilder();
csbuilder.DataSource = cbServer.Text.Trim();
csbuilder.UserID = tbUserID.Text.Trim();
csbuilder.Password = tbPwd.Text.Trim();
csbuilder.InitialCatalog = cbDatabase.Text.Trim();
return (csbuilder);
}
private DataView GetTables()
{
SqlConnection sqlCon = new SqlConnection(CreateConnectionString().ConnectionString);
sqlCon.Open();
//DataTable dt = sqlCon.GetSchema(SqlClientMetaDataCollectionNames.Tables);
string strsql = "SELECT name as table_name FROM sysobjects WHERE xtype = 'U' and name <> 'dtproperties'";
SqlCommand sqlComm = new SqlCommand(strsql, sqlCon);
DataTable dt = new DataTable("Tables");
try
{
SqlDataAdapter sdap = new SqlDataAdapter();
sdap.SelectCommand = sqlComm;
sdap.Fill(dt);
sdap.Dispose();
}
catch
{
}
dt.DefaultView.Sort = "table_name";
sqlCon.Close();
return dt.DefaultView;
}
private DataView GetColumns()
{
SqlConnection sqlCon = new SqlConnection(CreateConnectionString().ConnectionString);
sqlCon.Open();
//查询字段
string strsql = "SELECT d.name AS table_name, a.colorder AS ordinal_position, a.name AS COLUMN_NAME, " +
" (CASE WHEN (SELECT COUNT(*) FROM sysobjects WHERE (name IN " +
" (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid IN (SELECT indid" +
" FROM sysindexkeys WHERE (id = a.id) AND (colid IN (SELECT colid FROM syscolumns " +
" WHERE (id = a.id) AND (name = a.name))))))) AND " +
" (xtype = 'PK')) > 0 THEN 'Pk' ELSE '' END) AS COLUMN_key, b.name AS DATA_TYPE, " +
" a.length AS CHARACTER_MAXIMUM_LENGTH, (CASE WHEN a.isnullable = 1 THEN 'Yes' ELSE 'No' END) " +
" AS IS_NULLABLE, ISNULL(e.text, '') AS COLUMN_DEFAULT, ISNULL(g.[value], '') AS COLUMN_Description " +
" FROM syscolumns a LEFT OUTER JOIN systypes b ON a.xtype = b.xusertype INNER JOIN " +
" sysobjects d ON a.id = d.id AND d.xtype = 'U' AND " +
" d.name <> 'dtproperties' LEFT OUTER JOIN " +
" syscomments e ON a.cdefault = e.id LEFT OUTER JOIN " +
" sysproperties g ON a.id = g.id AND a.colid = g.smallid ";
SqlCommand sqlComm = new SqlCommand(strsql, sqlCon);
DataTable dt = new DataTable("Columns");
try
{
SqlDataAdapter sdap = new SqlDataAdapter();
sdap.SelectCommand = sqlComm;
sdap.Fill(dt);
sdap.Dispose();

}
catch { }
sqlCon.Close();
return dt.DefaultView;
}
3.双击"转换"按扭,转到代码区
private void btnConvert_Click(object sender, EventArgs e)
{
OperateWord word = new OperateWord();
word.CreateWord(GetTables(), GetColumns());
}

4.新建一个类,命名为SqlToWord.cs,将复制下列代码
using System.Data;
///添加word引用
using Word = Microsoft.Office.Interop.Word;
namespace SqlToWord
{
public class OperateWord
{
private void CreateWordTable(int index, string tableName, DataView dv,
Word._Document oDoc, object oMissing, object oEndOfDoc,
Word._Application oWord)
{
///创建表标题
Word.Paragraph oPara3;
object oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
oPara3 = oDoc.Content.Paragraphs.Add(ref oRng);
oPara3.Range.Text = index.ToString() + ". " + tableName + "表";
oPara3.Range.Font.Bold = 0;
oPara3.Format.SpaceAfter = 6;
oPara3.Range.InsertParagraphAfter();
///创建表格
Word.Table oTable;
Word.Range wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
oTable = oDoc.Tables.Add(wrdRng, dv.Count + 1, 6, ref oMissing, ref oMissing);
oTable.Range.ParagraphFormat.SpaceAfter = 6;
///表头字体加粗
oTable.Rows[1].Range.Font.Bold = 1;
///设置表头的背景颜色
oTable.Rows[1].Range.Shading.BackgroundPatternColor = Microsoft.Office.Interop.Word.WdColor.wdColorGray05;
///设置边框为1
oTable.Borders.Enable = 1;
///表格宽度适合文本
oTable.AllowAutoFit = true;
oTable.ApplyStyleHeadingRows = true;
///创建表的表头
oTable.Cell(1, 1).Range.Text = "字段名称";
oTable.Cell(1, 2).Range.Text = "数据类型";
oTable.Cell(1, 3).Range.Text = "是否为空";
oTable.Cell(1, 4).Range.Text = "键引用";
oTable.Cell(1, 5).Range.Text = "默认值";
oTable.Cell(1, 6).Range.Text = "说明";
int i = 2;
foreach (DataRowView row in dv)
{ ///添加列名称
oTable.Cell(i, 1).Range.Text = row["COLUMN_NAME"].ToString();
///添加列的数据类型
if (row["DATA_TYPE"].ToString() != "text" && row["CHARACTER_MAXIMUM_LENGTH"].ToString() != "")
{
oTable.Cell(i, 2).Range.Text = row["DATA_TYPE"].ToString()
+ "(" + row["CHARACTER_MAXIMUM_LENGTH"].ToString() + ")";
}
else
{
oTable.Cell(i, 2).Range.Text = row["DATA_TYPE"].ToString();
}
///添加列的非空字段标识
oTable.Cell(i, 3).Range.Text = row["IS_NULLABLE"].ToString();
///添加键引用标识
oTable.Cell(i, 4).Range.Text = row["COLUMN_key"].ToString();
//默认值
oTable.Cell(i, 5).Range.Text = row["COLUMN_DEFAULT"].ToString();
//描述
oTable.Cell(i, 6).Range.Text = row["COLUMN_Description"].ToString();
i++;
}
}
public void CreateWord(DataView dvTable, DataView dvColumn)
{
try
{
object oMissing = System.Reflection.Missing.Value;
object oEndOfDoc = "\endofdoc"; /* \endofdoc is a predefined bookmark */
///创建一个word文档
Word._Application oWord;
Word._Document oDoc;
oWord = new Word.Application();
oWord.Visible = true;
oDoc = oWord.Documents.Add(ref oMissing, ref oMissing,
ref oMissing, ref oMissing);
///创建表格
int i = 1;
foreach (DataRowView row in dvTable)
{ ///创建一个表格
CreateWordTable(
i,
row["table_name"].ToString(),
GetColumnByTable(dvColumn, row["table_name"].ToString()),
oDoc,
oMissing,
oEndOfDoc,
oWord);
i++;
}
}
catch
{ }
}
private DataView GetColumnByTable(DataView dvColumn, string tableName)
{
dvColumn.Sort = "table_name,ordinal_position";
dvColumn.RowFilter = "table_name = '" + tableName + "'";
return dvColumn;
}
}
}
5.编译,生成,运行