AutoCAD 3DMAX C语言 Pro/E UG JAVA编程 PHP编程 Maya动画 Matlab应用 Android
Photoshop Word Excel flash VB编程 VC编程 Coreldraw SolidWorks A Designer Unity3D
 首页 > MySQL

Word域与数据库的结合编程

51自学网 http://www.wanshiok.com

 

  插入多值域时要考虑表格行数够不够,不够时要增加表格行数,且每插入一个值,光标要下移一行。这里对应的是数据库的"校核"表。实现的方法如下:

'插入多值域
    Public Function InsertCollection() As Boolean
        Dim i, j, Count As Integer
        Dim KeyWord As String
        Dim mySelection As Selection
        Dim rec() As Object
        Dim recCount As Integer

        Count = wdDoc.Fields.Count
        For i = 1 To Count
            If wdDoc.Fields.Item(i).Type = 81 Then
                KeyWord = Trim(wdDoc.Fields.Item(i).Data)
                If Right(KeyWord, 1) = "F" Then
                    KeyWord = Left(KeyWord, Len(KeyWord) - 1)
                    rec = clsDB.GetFieldValues("校核", KeyWord)
                    '选择有域的单元格
                    wdDoc.Fields.Item(i).Select()
                    mySelection = wdApp.Selection      '插入点
                    mySelection.Cells.Item(1).Select()
                    '清除原值
                    mySelection.Delete()
                    '还原原域并更新值
                    InsertField(KeyWord + "F")
                    wdDoc.Fields.Item(i).Result.Text = rec(0).itemarray(0)
                    '光标下移
                    mySelection.Select()
                    mySelection.MoveDown(Unit:=wdLine, Count:=1)
                    With rec
                        recCount =.GetLength(0) - 1
                        For j = 1 To recCount - 1
                            If IsInsertRow = False Then
                                Call InsertRow(mySelection, recCount)
                            End If
                            mySelection.Cells.Item(1).Select()
                            mySelection.Delete()
                            mySelection.TypeText(Text:=rec(j).itemarray(0))
                            mySelection.MoveDown(Unit:=wdLine, Count:=1)
                        Next
                    End With
                End If
            End If
        Next
    End Function
    Private Function InsertRow(ByVal mySelection As Selection,
    ByVal recCount As Integer) As Boolean
        Dim InsertRowCount As Integer           '插入的行数
        Dim CurrCell As Cell
        CurrCell = mySelection.Cells.Item(1)
        InsertRowCount = recCount - mySelection.Tables.Item(1).Rows.Count + 1
        If InsertRowCount > 0 Then
            mySelection.InsertRows(InsertRowCount)
            CurrCell.Select()
            mySelection.MoveUp(Unit:=wdLine, Count:=InsertRowCount)
            IsInsertRow = True
        End If
End Function

  如果表格行数少于表数据的行数则应该增加表格行数。增加多少表格行数由表数据的行数减去现有表格行数加入计算出来。当插入了行,光标一定下移了几行,这里要还原光标至原来位置,方法是先保留插入点,插入行后,重新选择插入点,使光标上移几行。表格已经插入行以后就不要再插入了,所以至IsInsertRow为真。

'向表格中增加行数。
    Private Function IsInsert(ByVal mySelection As Selection,
    ByVal recCount As Integer) As Boolean
      Dim InsertRowCount As Integer           '插入的行数
      Dim CurrCell As Cell
      CurrCell = mySelection.Cells.Item(1)
      InsertRowCount = recCount - mySelection.Tables.Item(1).Rows.Count + 1
        If InsertRowCount > 0 Then
            mySelection.InsertRows(InsertRowCount)
            CurrCell.Select()
            mySelection.MoveUp(Unit:=wdLine, Count:=InsertRowCount)
            IsInsertRow = True
        End If
End Function


  Word的菜单和工具箱很多,用户操作时先关掉这些不必要的东西,最后恢复Word环境的原状。

'恢复Word环境的所有命令及菜单。
    Public Sub OpenCommandBar()
        Dim i As Integer
        For i = 0 To UBound(CommandBarIndex) - 1
            wdDoc.CommandBars(i + 1).Visible = True
        Next
        For i = 0 To UBound(SaveCommandBarMenuIndex) - 1
            wdDoc.CommandBars.Item("Menu Bar").Controls(i + 1)。Visible = True
        Next
    End Sub
    '关闭Word环境的所有命令及菜单。
    Public Sub CloseCommandBar()
        Dim i As Integer
        Dim cBar
        ReDim CommandBarIndex(1)
        ReDim SaveCommandBarMenuIndex(1)
        i = 0
        For Each cBar In wdDoc.CommandBars
            If cBar.Type = 0 And cBar.Enabled = True Then
                If cBar.Visible = True Then
                    ReDim CommandBarIndex(i + 1)
                    CommandBarIndex(i) = cBar.Index
                    i = i + 1
                    cBar.Visible = False
                End If
            End If
        Next
        i = 0
        For Each cBar In wdDoc.CommandBars("Menu Bar").Controls
            If cBar.Visible = True Then
                ReDim SaveCommandBarMenuIndex(i + 1)
                SaveCommandBarMenuIndex(i) = cBar.Index
                i = i + 1
                cBar.Visible = False
            End If
        Next
End Sub



2.增加一个处理数据库的类COleDataAccess。

  COleDataAccess类很简单,包含连接数据库的方法ConnAccess,打开静态表的方法GetDataTable,由字段名得到字段值的方法GetFieldValues。

Public Class COleDataAccess
Private mOleCnnDB As New OleDbConnection()
'连接Access数据库:DBName数据库名。
    Public Sub ConnAccess(ByVal DBName As String)
        mOleCnnDB.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""
";User ID=Admin;" & _
         "Data Source='" & DBName & "'"
      mOleCnnDB.Open()
    End Sub
    '获取数据表.strSql查询条件。
    Public Overloads Function GetDataTable(ByVal strSql As String) As DataTable
        Dim myDataSet As New DataSet()
        Dim myOleDataAdapter As New OleDbDataAdapter()
        myOleDataAdapter.SelectCommand = New OleDbCommand(strSql, mOleCnnDB)
        Try
            myOleDataAdapter.Fill(myDataSet)
        Catch er As Exception
            MsgBox(er.ToString)
        End Try
        Return myDataSet.Tables(0)
    End Function
'由字段名得到字段值的方法:. TableName表名 ; FieldName 字段名
    Public Overloads Function GetFieldValues(ByVal TableName As String,
    ByVal FieldName As String) As Object()
        Dim dr As DataTable
        Dim sql As String
        sql = "select " + FieldName + " from " + TableName
        dr = GetDataTable(sql)
        Dim al(dr.Rows.Count) As Object
        dr.Rows.CopyTo(al, 0)
        Return al
    End Function
End Class



3.增加模块Module1

  模块Module1定义COleDataAccess类的变量clsDB,并连接数据库,显示用户窗体。

Module Module1
    Public clsDB As New COleDataAccess()
    Sub main()
        clsDB.ConnAccess("工程数据。mdb")
        Dim frm As New frmUserWord()
        frm.ShowDialog()
    End Sub
End Module


4.增加用户操作窗体

  向工程中增加一窗体frmUserWord,窗体标题为"处理Word文档",在frmUserWord上加入3个CommandButton,用于打开文件(cmdOpenFile)、填充数据(cmdFill)、保存文件(cmdSave)用的命令按钮;2个ComboBox,用于所插入的字段名;2个 Label;2个CommonDialog,用于执行打开文件和保存文件;打开文件的对话框OpenFileDialog1、保存文件的对话框SaveFileDialog1。界面如下:



  窗体frmUserWord类事实很简单,frmUserWord 类的代码如下:
 
Public Class frmUserWord
    Inherits System.Windows.Forms.Form
Dim clsDoc As New CWordDoc()
'打开Word文件。并使处理界面位于Word最顶端,适当调整Word位置,关闭Word其它功能。
Private Sub cmdOpenFile_Click(ByVal sender As System。Object,
ByVal e As System。EventArgs) Handles cmdOpenFile。Click
        Dim FileName As String
        OpenFileDialog1.ShowDialog()
        FileName = OpenFileDialog1.FileName
        If FileName = "" Then
            Exit Sub
        End If
        clsDoc.OpenWordDocument(FileName)
        clsDoc.SetWordSize(0, 50, 2000, 2000)
       clsDoc.CloseCommandBar()
        Me.Top = 0
        Me.Left = 0
        Me.Width = 10000
        Me.Height = 80
        ComboBox1.Enabled = True
        ComboBox2.Enabled = True
        cmdSave.Enabled = True
        cmdFill.Enabled = True
End Sub


  在Form的Load事件中定义打开和保存文件的格式,并填充ComboBox数据。ComboBox数据对应数据库表的字段名。
 
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
        Dim i As Integer
        OpenFileDialog1.Title = "打开文件"
        OpenFileDialog1.Filter = "Word文档(*.doc)|*.doc|Word文档模板(*.dot)|*.dot"
        SaveFileDialog1.Title = "保存文件"
        SaveFileDialog1.Filter = "Word文档(*.doc)|*.doc|Word文档模板(*.dot)|*.dot"
        Dim dt As New DataTable()
        dt = clsDB.GetDataTable("select * from 工程")
        For i = 0 To dt.Columns.Count - 1
            ComboBox1.Items.Add(dt.Columns.Item(i).ColumnName)
        Next
        dt = clsDB.GetDataTable("select * from 校核")
        For i = 0 To dt.Columns.Count - 1
            ComboBox2.Items.Add(dt.Columns.Item(i).ColumnName)
        Next
    End Sub
   '填充数据
    Private Sub cmdFill_Click(ByVal sender As System.Object, ByVal e As
System。EventArgs) Handles cmdFill.Click
        clsDoc.InsertValue()
        clsDoc.InsertCollection()
    End Sub
'用户选择所插入域的域名,并在光标处插入单值域。
    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object,
    ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        clsDoc.InsertField(sender.Text)
    End Sub
'用户选择所插入域的域名,并在光标处插入多值域。
域所对应多值时,域只能插入表格中。且要与单值域区分,标记为多值插入。
    Private Sub ComboBox2_SelectedValueChanged(ByVal sender As Object,
    ByVal e As System.EventArgs) Handles ComboBox2.SelectedValueChanged
        Dim KeyWord As String
        KeyWord = sender.Text + "F" '标记是多值
        clsDoc.InsertField(KeyWord)
    End Sub
'保存Word文件。
    Private Sub cmdSave_Click(ByVal sender As System.Object,
    ByVal e As System.EventArgs) Handles cmdSave.Click
        clsDoc.SaveWordDocument()
End Sub
'打开Word的命令菜单及工具箱。
Private Sub frmUserWord_Closed(ByVal sender As Object,
ByVal e As System.EventArgs) Handles MyBase.Closed
        clsDoc.OpenCommandBar()
    End Sub
End Class


5.应用与测试

  一般,此种应用是作为一个软件的一部分,我们可以把它生成组件来用,方法把项目的输出类型改为类库后,把它生成DLL文件就可。这里为了方便测试就直接把它生成应用程序。

  下面对它运行时的结果如下:


  打开一Word文件运行后的界面如下:



结论

  上面方法介绍了Word域的利用,利用VBA编写Word域的方法。Word域所对应的数据库的字段的单值和多值的关系,极有普遍性,很有实用价值。

 
 

上一篇:MySQL数据库安全配置指南  下一篇:Mysql自定义函数