VSTO学习笔记（八）向 Word 2010 中写入表结构
前几天公司在做CMMI 3级认证，需要提交一系列的Word文档，其中有一种文档要求添加公司几个系统的数据库中的表结构。我临时接到了这项任务，辅助相关人员完成这种文档。
本系列所有示例代码均在 Visual Studio 2010 Ultimate RTM + Office 2010 Professional Plus RTM x64 中测试通过
注：本次使用的数据库为 SQL Server 2008 R2 DataCenter RTM x64
世界杯到了，首先献上一张对阵表：
|
小组赛日期 |
时间 |
对阵 |
组别 |
比赛地 |
场次 |
|
6月11日 星期五 |
22:00 |
南非 VS 墨西哥 |
A1-A2 |
约翰内斯堡(足) |
01 |
|
6月12日 星期六 |
02:30 |
乌拉圭 VS 法国 |
A3-A4 |
开普敦 |
02 |
|
6月12日 星期六 |
19:30 |
韩国 VS 希腊 |
B3-B4 |
伊丽莎白港 |
04 |
|
6月12日 星期六 |
22:00 |
阿根廷 VS 尼日利亚 |
B1-B2 |
约翰内斯堡(埃) |
03 |
|
6月13日 星期日 |
02:30 |
英格兰 VS 美国 |
C1-C2 |
鲁斯腾堡 |
05 |
|
6月13日 星期日 |
19:30 |
阿尔及利亚 VS 斯洛文尼亚 |
C3-C4 |
保罗瓜尼 |
06 |
|
6月13日 星期日 |
22:00 |
塞尔维亚 VS 加纳 |
D3-D4 |
比勒陀利亚 |
08 |
|
6月14日 星期一 |
02:30 |
德国 VS 澳大利亚 |
D1-D2 |
德班 |
07 |
|
6月14日 星期一 |
19:30 |
荷兰 VS 丹麦 |
E1-E2 |
约翰内斯堡(足) |
09 |
|
6月14日 星期一 |
22:00 |
日本 VS 喀麦隆 |
E3-E4 |
布隆冯丹 |
10 |
|
6月15日 星期二 |
02:30 |
意大利 VS 巴拉圭 |
F1-F2 |
开普敦 |
11 |
|
6月15日 星期二 |
19:30 |
新西兰 VS 斯洛伐克 |
F3-F4 |
鲁斯腾堡 |
12 |
|
6月15日 星期二 |
22:00 |
科特迪瓦 VS 葡萄牙 |
G3-G4 |
伊丽莎白港 |
13 |
|
6月16日 星期三 |
02:30 |
巴西 VS 朝鲜 |
G1-G2 |
约翰内斯堡(埃) |
14 |
|
6月16日 星期三 |
19:30 |
洪都拉斯 VS 智利 |
H3-H4 |
内斯普利特 |
15 |
|
6月16日 星期三 |
22:00 |
西班牙 VS 瑞士 |
H1-H2 |
德班 |
16 |
|
6月17日 星期四 |
02:30 |
南非 VS 乌拉圭 |
A1-A3 |
比勒陀利亚 |
17 |
|
6月17日 星期四 |
19:30 |
阿根廷 VS 韩国 |
B1-B3 |
约翰内斯堡(足) |
20 |
|
6月17日 星期四 |
22:00 |
尼日利亚 VS 希腊 |
B2-B4 |
布隆冯丹 |
19 |
|
6月18日 星期五 |
02:30 |
墨西哥 VS 法国 |
A2-A4 |
保罗瓜尼 |
18 |
|
6月18日 星期五 |
19:30 |
德国 VS 塞尔维亚 |
D1-D3 |
伊丽莎白港 |
21 |
|
6月18日 星期五 |
22:00 |
斯洛文尼亚 VS 美国 |
C4-C2 |
约翰内斯堡(埃) |
22 |
|
6月19日 星期六 |
02:30 |
英格兰 VS 阿尔及利亚 |
C1-C3 |
开普敦 |
23 |
|
6月19日 星期六 |
19:30 |
荷兰 VS 日本 |
E1-E3 |
德班 |
25 |
|
6月19日 星期六 |
22:00 |
加纳 VS 澳大利亚 |
D4-D2 |
鲁斯腾堡 |
24 |
|
6月20日 星期日 |
02:30 |
丹麦 VS 喀麦隆 |
E2-E4 |
比勒陀利亚 |
26 |
|
6月20日 星期日 |
19:30 |
巴拉圭 VS 斯洛伐克 |
F2-F4 |
布隆冯丹 |
27 |
|
6月20日 星期日 |
22:00 |
意大利 VS 新西兰 |
F1-F3 |
内斯普利特 |
28 |
|
6月21日 星期一 |
02:30 |
巴西 VS 科特迪瓦 |
G1-G3 |
约翰内斯堡(足) |
29 |
|
6月21日 星期一 |
19:30 |
朝鲜 VS 葡萄牙 |
G2-G4 |
开普敦 |
30 |
|
6月21日 星期一 |
22:00 |
智利 VS 瑞士 |
H4-H2 |
伊丽莎白港 |
31 |
|
6月22日 星期二 |
02:30 |
西班牙 VS 洪都拉斯 |
H1-H3 |
约翰内斯堡(埃) |
32 |
|
6月22日 星期二 |
22:00 |
墨西哥 VS 乌拉圭 |
A2-A3 |
鲁斯腾堡 |
33 |
|
6月22日 星期二 |
22:00 |
南非 VS 法国 |
A1-A4 |
布隆冯丹 |
34 |
|
6月23日 星期三 |
02:30 |
尼日利亚 VS 韩国 |
B2-B3 |
德班 |
35 |
|
6月23日 星期三 |
02:30 |
阿根廷 VS 希腊 |
B1-B4 |
保罗瓜尼 |
36 |
|
6月23日 星期三 |
22:00 |
英格兰 VS 斯洛文尼亚 |
C1-C4 |
伊丽莎白港 |
37 |
|
6月23日 星期三 |
22:00 |
美国 VS 阿尔及利亚 |
C2-C3 |
比勒陀利亚 |
38 |
|
6月24日 星期四 |
02:30 |
德国 VS 加纳 |
D1-D4 |
约翰内斯堡(足) |
39 |
|
6月24日 星期四 |
02:30 |
塞尔维亚 VS 澳大利亚 |
D3-D2 |
内斯普利特 |
40 |
|
6月24日 星期四 |
22:00 |
意大利 VS 斯洛伐克 |
F1-F4 |
约翰内斯堡(埃) |
41 |
|
6月24日 星期四 |
22:00 |
巴拉圭 VS 新西兰 |
F2-F3 |
保罗瓜尼 |
42 |
|
6月25日 星期五 |
02:30 |
丹麦 VS 日本 |
E2-E3 |
鲁斯腾堡 |
43 |
|
6月25日 星期五 |
02:30 |
荷兰 VS 喀麦隆 |
E1-E4 |
开普敦 |
44 |
|
6月25日 星期五 |
22:00 |
巴西 VS 葡萄牙 |
G1-G4 |
德班 |
45 |
|
6月25日 星期五 |
22:00 |
朝鲜 VS 科特迪瓦 |
G2-G3 |
内斯普利特 |
46 |
|
6月26日 星期六 |
02:30 |
西班牙 VS 智利 |
H1-H4 |
比勒陀利亚 |
47 |
|
6月26日 星期六 |
02:30 |
瑞士 VS 洪都拉斯 |
H2-H3 |
布隆冯丹 |
48 |
CMMI文档中表结构要求格式如下：
XX表
|
字段名 |
名称 |
主键 |
数据类型（精度） |
空/非空 |
约束条件 |
默认值 |
备注 |
|
说明 |
XX表 | ||||||
公司系统数据库非常庞大，表也很多，如果一个个手工处理，那将非常麻烦，于是考虑写一个程序，来自动处理。
既然是生成Word文档，我就想做一个VSTO Word插件，通过选择的数据库中的表，来生成表结构。
- 创建一个Word 2010 AddIn项目：
- 在Word的Ribbon中添加一个按钮，点击按钮后选择数据库、表：
测试数据库使用的SQL Server 2008 R2 Sample Databases，可以在CodePlex上下载。
这个添加连接窗体我是仿微软的做的，本来想找一个第三方组件，没有找到，就自己实现了一个，功能上有所裁剪（只支持SQL Server），够用就好，微软的如下：
首先选择或输入一个SQL Server服务器名，支持"."、"(local)"、IP地址、SQL Server实例等。本意设计的是点击下拉列表时获取局域网内所有的SQL Server实例，但是始终未能实现，若有朋友知道，请指点一下。
PS：我本机是一台域控制器，操作系统为Windows Server 2008 R2 DataCenter，数据库为SQL Server 2008 R2 DataCenter X64，获取局域网内所有的SQL Server实例的代码为：
代码
DataTable __dtServers = SqlClientFactory.Instance.CreateDataSourceEnumerator().GetDataSources();
foreach (DataRow __dr in __dtServers.Rows)
{
this.txt服务器名.Items.Add(__dr["InstanceName"].ToString());
}
先简单说说常用系统数据库对象：
sys.all_columns
sys.tables
sys.objects
sys.key_constraints
sys.types
sys.indexes
sys.default_constraints
sys.all_objects
sys.schemas
注意SQL Server 2000、2005不受支持，因为使用了一些新特性，所以如果要测试代码，请使用SQL Server 2008 或更新版本。
从名字上就可以看出这些系统表中包含的内容，通过这些表之间的关联我们可以取得绝大多数数据库的元数据，在某些情况下是非常有用的。
另外常用系统视图：
sys.all_objects
sys.databases
sys.servers
常用系统存储过程：
Sp_help
Sp_helptext
Sp_PKeys
Sp_rename
获取所选择服务器上的所有的数据库：
附加数据库：
代码
-- Attach database template
--=====================================
IF NOT EXISTS(
SELECT *
FROM sys.databases
WHERE name = N'<database_name, sysname, your_database_name>'
)
CREATE DATABASE <database_name, sysname, your_database_name>
ON PRIMARY (FILENAME = '<database_primary_file_path,,C:\Program files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\your_database_name.MDF>')
LOG ON (FILENAME = '<database_primary_file_path,,C:\Program files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\your_database_name.ldf>')
FOR ATTACH
GO
可以选择Windows或SQL Server验证，可以点击【测试连接】测试一下连接是否正常。选择或附加一个数据库后，点击确定，会获取指定数据库中的所有表，选择要生成表结构的表（默认全选），点击【确定】生成：
获取指定数据库中的所有表：
- 在Word中插入表格
数据库、表选择好了之后，剩下的工作就要在Word中处理了。【约束条件】、【默认值】、【备注】三列忽略，
在剩下的列中，【字段名】、【名称】（也取的字段名）、【数据类型】、【空、非空】比较好取得，只有一个【主键】颇费了一番功夫。
代码
--SELECT * FROM sys.all_columns
--SELECT * FROM sys.tables
--SELECT * FROM sys.objects
--SELECT * FROM sys.key_constraints
--SELECT * FROM sys.types
--SELECT * FROM sys.indexes
--SELECT * FROM sys.default_constraints
--SELECT * FROM sys.all_objects
--SELECT * FROM sys.schemas
--常用系统视图
--SELECT * FROM sys.all_objects
--SELECT * FROM sys.databases
--SELECT * FROM sys.servers
--默认值测试
--SELECT col.column_id,col.name,d.definition
--FROM sys.columns AS col
--INNER JOIN sys.default_constraints AS d ON col.column_id = d.parent_column_id
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' and name = 'sp_生成数据结构')
BEGIN
DROP PROCEDURE dbo.sp_生成数据结构
END
GO
CREATE PROCEDURE dbo.sp_生成数据结构
(
@TableNames SelectTables READONLY
)
AS
SET XACT_ABORT ON
BEGIN TRANSACTION
CREATE TABLE #TableNames
(
ID INT IDENTITY(1, 1),
TableName VARCHAR(200)
)
INSERT INTO #TableNames(TableName)
SELECT TableName
FROM @TableNames
DECLARE @table_owner sysname = null,
@table_qualifier sysname = null,
@table_id int,
-- quotename() returns up to 258 chars
@full_table_name nvarchar(517) -- 258 + 1 + 258
DECLARE @i INT, @Count INT, @Name VARCHAR(200)
SET @i = 1
SET @Count = (SELECT COUNT(*) FROM #TableNames)
WHILE @i <= @Count
BEGIN
SET @Name = (SELECT TableName FROM #TableNames WHERE ID = @i)
SET @table_owner =
(
SELECT s.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t ON s.schema_id = t.schema_id
WHERE t.name = @Name
)
if @table_owner is null
begin -- If unqualified table name
select @full_table_name = quotename(@Name)
end
else
begin -- Qualified table name
if @table_owner = ''
begin -- If empty owner name
select @full_table_name = quotename(@table_owner)
end
else
begin
select @full_table_name = quotename(@table_owner) + '.' + quotename(@Name)
end
end
select @table_id = object_id(@full_table_name)
SELECT col.name AS 字段名,
col.name AS 名称,
CASE WHEN col.name IN
(
select
COLUMN_NAME = c.name
from
sys.indexes i,
sys.all_columns c,
sys.all_objects o
where
o.object_id = @table_id and
o.object_id = c.object_id and
o.object_id = i.object_id and
i.is_primary_key = 1 and
(c.name = index_col (@full_table_name, i.index_id, 1) or
c.name = index_col (@full_table_name, i.index_id, 2) or
c.name = index_col (@full_table_name, i.index_id, 3) or
c.name = index_col (@full_table_name, i.index_id, 4) or
c.name = index_col (@full_table_name, i.index_id, 5) or
c.name = index_col (@full_table_name, i.index_id, 6) or
c.name = index_col (@full_table_name, i.index_id, 7) or
c.name = index_col (@full_table_name, i.index_id, 8) or
c.name = index_col (@full_table_name, i.index_id, 9) or
c.name = index_col (@full_table_name, i.index_id, 10) or
c.name = index_col (@full_table_name, i.index_id, 11) or
c.name = index_col (@full_table_name, i.index_id, 12) or
c.name = index_col (@full_table_name, i.index_id, 13) or
c.name = index_col (@full_table_name, i.index_id, 14) or
c.name = index_col (@full_table_name, i.index_id, 15) or
c.name = index_col (@full_table_name, i.index_id, 16))
) THEN '√'
WHEN col.name NOT IN
(
select
COLUMN_NAME = convert(sysname,c.name)
from
sys.indexes i,
sys.all_columns c,
sys.all_objects o
where
o.object_id = @table_id and
o.object_id = c.object_id and
o.object_id = i.object_id and
i.is_primary_key = 1 and
(c.name = index_col (quotename(@full_table_name), i.index_id, 1) or
c.name = index_col (quotename(@full_table_name), i.index_id, 2) or
c.name = index_col (quotename(@full_table_name), i.index_id, 3) or
c.name = index_col (quotename(@full_table_name), i.index_id, 4) or
c.name = index_col (quotename(@full_table_name), i.index_id, 5) or
c.name = index_col (quotename(@full_table_name), i.index_id, 6) or
c.name = index_col (quotename(@full_table_name), i.index_id, 7) or
c.name = index_col (quotename(@full_table_name), i.index_id, 8) or
c.name = index_col (quotename(@full_table_name), i.index_id, 9) or
c.name = index_col (quotename(@full_table_name), i.index_id, 10) or
c.name = index_col (quotename(@full_table_name), i.index_id, 11) or
c.name = index_col (quotename(@full_table_name), i.index_id, 12) or
c.name = index_col (quotename(@full_table_name), i.index_id, 13) or
c.name = index_col (quotename(@full_table_name), i.index_id, 14) or
c.name = index_col (quotename(@full_table_name), i.index_id, 15) or
c.name = index_col (quotename(@full_table_name), i.index_id, 16))
) THEN '' END AS 主键,
ty.name + '(' + LTRIM(RTRIM(STR(col.max_length))) + ')' AS[ 数据类型(精度)],
CASE WHEN col.is_nullable = 1 THEN ''
WHEN col.is_nullable = 0 THEN 'NOT NULL' END AS [空/非空],
'' AS 约束条件,
'' AS 默认值,
'' AS 备注,
@name AS 表名
FROM sys.all_columns AS col
INNER JOIN
(
SELECT t.object_id,
t.name AS name
FROM sys.tables AS t
WHERE t.name = @Name
) AS t ON col.object_id = t.object_id
INNER JOIN sys.types AS ty ON col.user_type_id = ty.user_type_id
SET @i = @i + 1
END
DROP TABLE #TableNames
COMMIT TRANSACTION
SET XACT_ABORT OFF
GO
这段代码的灵感来自于系统存储过程：sp_Pkeys，获取一个表中的主键字段。输入：sp_helptext 'sp_Pkeys'来查看源代码，我对其进修了修改，用在了我自己的存储过程中，目的就是判断一个字段是不是主键。
为了将用户选择的表传递给存储过程，创建一个用户自定义类型：SelectTables
代码
BEGIN
CREATE TYPE SelectTables AS TABLE
(
Choice BIT NOT NULL,
TableName VARCHAR(200) NOT NULL
)
END
GO
然后前台使用DataTable类型的参数：
代码{
this.__ds.Clear();
SqlConnection _cn = new SqlConnection(v_strConn);
SqlCommand _cmd = _cn.CreateCommand();
_cmd.CommandType = System.Data.CommandType.StoredProcedure;
_cmd.CommandText = "sp_生成数据结构";
SqlParameter _p = _cmd.Parameters.AddWithValue("@TableNames", v_dt选择表);
SqlDataAdapter _da = new SqlDataAdapter(_cmd);
_da.Fill(this.__ds);
}
存储过程【sp_生成数据结构】返回的是一个数据集DataSet，DataSet中的每一个DataTable对应着一个物理表，下面就在Word中循环打印出来：
打印表格private void fn生成表格(System.Data.DataSet v_ds)
{
Word.Range r;
foreach (System.Data.DataTable dt in v_ds.Tables)
{
r = this.Application.ActiveDocument.Paragraphs.Last.Range;
r.MoveEnd();
Word.Table t = r.Tables.Add(r, dt.Rows.Count, dt.Columns.Count - 1);
for (int i = 1; i <= dt.Rows.Count; i++)
{
for (int j = 1; j <= dt.Columns.Count - 1; j++)
{
t.Rows[i].Cells[j].Range.Text = dt.Rows[i - 1][j - 1].ToString();
}
}
t.Rows.Add();
t.Rows[t.Rows.Count].Cells[1].Range.Text = "说明";
t.Rows[t.Rows.Count].Cells[2].Range.Text = dt.Rows[0][dt.Columns.Count - 1].ToString() + "表";
object beforeRow = t.Rows[1];
t.Rows.Add(ref beforeRow);
t.Rows[1].Cells[1].Range.Text = "字段名";
t.Rows[1].Cells[2].Range.Text = "名称";
t.Rows[1].Cells[3].Range.Text = "主键";
t.Rows[1].Cells[4].Range.Text = "数据类型(精度)";
t.Rows[1].Cells[5].Range.Text = "空/非空";
t.Rows[1].Cells[6].Range.Text = "约束条件";
t.Rows[1].Cells[7].Range.Text = "默认值";
t.Rows[1].Cells[8].Range.Text = "备注";
t.Columns[1].Width = 100;
t.Columns[2].Width = 100;
t.Columns[3].Width = 40;
t.Columns[4].Width = 100;
t.Columns[5].Width = 50;
t.Columns[6].Width = 20;
t.Columns[7].Width = 20;
t.Columns[8].Width = 20;
object title = t.Rows[1];
t.Rows.Add(ref title);
t.Rows[1].Cells[1].Range.Text = dt.Rows[0][dt.Columns.Count - 1].ToString();
t.Rows[1].Cells.Merge();
t.Rows[2].Select();
this.Application.Selection.Cells.VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;//垂直居中
this.Application.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居中
t.Rows[2].Range.Font.Bold = 2;
//感谢 sunfny 的指点，用下面的方法设置表格单元格的背景颜色：
t.Rows[2].Shading.BackgroundPatternColor = Word.WdColor.wdColorSkyBlue;
t.Borders[Word.WdBorderType.wdBorderTop].LineStyle = Word.WdLineStyle.wdLineStyleSingle;
t.Borders[Word.WdBorderType.wdBorderLeft].LineStyle = Word.WdLineStyle.wdLineStyleSingle;
t.Borders[Word.WdBorderType.wdBorderRight].LineStyle = Word.WdLineStyle.wdLineStyleSingle;
t.Borders[Word.WdBorderType.wdBorderBottom].LineStyle = Word.WdLineStyle.wdLineStyleSingle;
t.Borders[Word.WdBorderType.wdBorderHorizontal].LineStyle = Word.WdLineStyle.wdLineStyleSingle;
t.Borders[Word.WdBorderType.wdBorderVertical].LineStyle = Word.WdLineStyle.wdLineStyleSingle;
t.Rows[1].Borders[Word.WdBorderType.wdBorderTop].LineStyle = Word.WdLineStyle.wdLineStyleNone;
t.Rows[1].Borders[Word.WdBorderType.wdBorderLeft].LineStyle = Word.WdLineStyle.wdLineStyleNone;
t.Rows[1].Borders[Word.WdBorderType.wdBorderRight].LineStyle = Word.WdLineStyle.wdLineStyleNone;
t.AllowAutoFit = true;
this.Application.ActiveDocument.Paragraphs.Add();
}
}
Word中的Range对象比较特殊，感觉比Excel的Range难操作一些。
小结
本次在通过用户选择一个数据库，若干表的方式在Word中打印出来这些表的表结构，其中用到了很多系统表、系统存储过程，掌握这些系统对象还是很有必要的。其次我们练习了在Word中打印表格、控制显示样式的方法，也是很实用的。
