VSTO学习笔记（八）向 Word 2010 中写入表结构

原文:VSTO学习笔记（八）向 Word 2010 中写入表结构

前几天公司在做CMMI 3级认证，需要提交一系列的Word文档，其中有一种文档要求添加公司几个系统的数据库中的表结构。我临时接到了这项任务，辅助相关人员完成这种文档。

示例代码下载

本系列所有示例代码均在 Visual Studio 2010 Ultimate RTM + Office 2010 Professional Plus RTM x64 中测试通过

注：本次使用的数据库为 SQL Server 2008 R2 DataCenter RTM x64 

世界杯到了，首先献上一张对阵表：

CMMI文档中表结构要求格式如下：

XX表

公司系统数据库非常庞大，表也很多，如果一个个手工处理，那将非常麻烦，于是考虑写一个程序，来自动处理。

既然是生成Word文档，我就想做一个VSTO Word插件，通过选择的数据库中的表，来生成表结构。

1. 创建一个Word 2010 AddIn项目：

1. 在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实例的代码为：

代码

            this.txt服务器名.Items.Clear();

            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

获取所选择服务器上的所有的数据库：

SELECT * FROM sys.databases

附加数据库：

代码

--=====================================
-- 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验证，可以点击【测试连接】测试一下连接是否正常。选择或附加一个数据库后，点击确定，会获取指定数据库中的所有表，选择要生成表结构的表（默认全选），点击【确定】生成：

获取指定数据库中的所有表：

SELECT * FROM sys.tables

1. 在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

代码

IF NOT EXISTS(SELECT * FROM sys.table_types WHERE name = 'SelectTables' AND is_user_defined = 1)
BEGIN
   CREATE TYPE SelectTables AS TABLE
   (
      Choice BIT NOT NULL,
      TableName VARCHAR(200) NOT NULL
   )
END
GO

然后前台使用DataTable类型的参数：

代码

        private void fn生成数据结构(string v_strConn,  System.Data.DataTable v_dt选择表)
        {
            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中打印表格、控制显示样式的方法，也是很实用的。