sheet1.vb

.net中word的扩展应用。用ado.net对word,excel进行存取_ADO.NET应用

Imports System.Data.OleDb
Public Class Sheet1

    Public ReportDetail As New ArrayList
    Public strChange(1, 200) As String
    Public ReportDetailUnit As New ArrayList
    Private ReportTitle As New ArrayList


    Private DBChange As DBClass
    Private NULL As System.DBNull
    Public tbSheet As DataTable
    Private tbUnit As DataTable
    Private strConnection As String
    Private sql As String
    Private adapterDB As OleDbDataAdapter
    Private ds As DataSet

    Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup

        Try
            If ConnectionDataTable() Then
                BindNamedRange()
                BindUserControl()
            Else
                MessageBox.Show("数据库连接失败！")
                Exit Try
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub


    '连接数据库的表
    Private Function ConnectionDataTable() As Boolean
        Try
            DBChange = New DBClass
            DBChange.OpenFile("E:\OFFice\tt.mdb")



            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\OFFice\tt.mdb;Persist Security Info=False"
            sql = "select * from Unit"
            adapterDB = New OleDbDataAdapter(sql, strConnection)
            ds = New DataSet
            tbUnit = New DataTable
            adapterDB.Fill(ds, "Unit")
            tbUnit = ds.Tables("Unit")
            Dim keys(1) As DataColumn
            keys(0) = tbUnit.Columns.Item("UnitID")
            tbUnit.PrimaryKey = keys

            sql = "select * from report"
            adapterDB = New OleDbDataAdapter(sql, strConnection)
            'ds = New DataSet
            tbSheet = New DataTable
            adapterDB.Fill(ds, "report")
            tbSheet = ds.Tables("report")
            Dim i As Integer
            For i = 0 To tbSheet.Columns.Count - 1
                strChange(0, i) = DBChange.tdfNew(i).Properties("Caption").Value
                strChange(1, i) = DBChange.tdfNew(i).Name
            Next

        Catch ex As Exception
            Return False
        End Try
        Return True
    End Function



    '绑定UserControl控件
    Private Sub BindUserControl()

        Try
            Dim myUserControl As New UserControl1
            Globals.ThisWorkbook.ActionsPane.Controls.Add(myUserControl)
            Globals.ThisWorkbook.ActionsPane.Controls.Add(myUserControl)
            myUserControl.LstReport.ClearSelected()
            Dim i As Integer
            For i = 0 To 5
                myUserControl.CboDecimalDigits.Items.Add(i)
            Next
            myUserControl.CboDecimalDigits.SelectedItem = 2
            'lstReport控件绑定
            For i = 0 To tbSheet.Columns.Count - 1
                'myUserControl.LstReport.Items.Add(DBChange.tdfNew(i).Properties("Caption").Value)
                myUserControl.LstReport.Items.Add(strChange(0, i))
                'myUserControl.ListBox1.Items.Add(tbSheet.Columns.Item(i).Caption)
                ' myUserControl.LstReport.Items.Add
                'Dim strField(1) As String
                'strField(0) = DBChange.tdfNew(i).Name
                'strField(1) = DBChange.tdfNew(i).Properties("Caption").Value
                'Dim lv As New ListViewItem(strField)
                'myUserControl.LvField.Items.Add(lv)
            Next
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub

    '读表里的内容，并显示
    Public Function TableRead(ByVal strFormat As String) As Boolean
        Dim i, j
        Dim rowTempt As DataRow
        Dim tempt As Integer
        tempt = ReportDetail(0).Cells.Row + 2
        i = 0
        Try
            TableUntil()
            For Each rowTempt In tbSheet.Rows
                For j = 15 To ReportDetail.Count - 1
                    If ReportDetail(j).ToString <> "" Then
                        If Convert.IsDBNull(rowTempt(ReportDetail(j).ToString)) Then
                        Else
                            If rowTempt(ReportDetail(j).ToString).GetType().ToString = "System.String" Or rowTempt(ReportDetail(j).ToString).GetType().ToString = "System.DateTime" Then
                                Me.Range(Chr(Asc("A") + j - 15) & tempt + i).Value2() = rowTempt(ReportDetail(j).ToString)
                            Else
                                Me.Range(Chr(Asc("A") + j - 15) & tempt + i).Value2() = Format(rowTempt(ReportDetail(j).ToString), strFormat)
                            End If
                        End If
                    End If
                Next
                i = i + 1
            Next
        Catch ex As ArgumentException
            MessageBox.Show("ArgumentException" & ex.Message)
        Catch ex As Exception
            MessageBox.Show("Exception" & ex.Message)
            Return False
        End Try
        Return True
    End Function
    '详细部分的单位绑定
    Private Sub TableUntil()
        Dim i As Integer
        Dim temp As Integer
        Dim rowDB As DataRow
        Try
            For i = 15 To ReportDetail.Count - 1
                If ReportDetail(i).ToString <> "" Then
                    temp = DBChange.GetUnitID(ReportDetail(i).ToString)
                    rowDB = tbUnit.Rows.Find(temp)
                    If rowDB Is Nothing Then
                    Else
                        ReportDetailUnit(i - 15).value2 = rowDB("Label")
                    End If
                End If
            Next
        Catch ex As ArgumentException
            MessageBox.Show("找不到　数据库字段　" & ReportDetail(i).ToString & "！")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub

    '函数计算，strFunction　输入Excel里标准的函数名称，strName需要显示的函数名字，在Ａ列显示，listRows在第几行开始显示。
    Public Sub sheetResult(ByVal strName As String, ByVal strFunction As String, ByVal listRows As Integer)
        Dim i, j
        'Dim resultNRange As New ArrayList
        For i = 0 To ReportDetail.Count / 2 - 1
            Dim strResult As String
            strResult = ""
            If ReportDetail(i).value2 <> Nothing Then
                For j = 0 To tbSheet.Rows.Count - 1
                    strResult = strResult & "R" & (j + ReportDetail(0).Cells.Row + 2) & "C" & (i + 1) & ","
                Next
                If strResult <> "" Then
                    strResult = strResult.Remove(strResult.Length - 1, 1)
                End If
                If i = 0 Then
                    Me.Range(Chr(Asc("A") + i) & listRows).Value2() = strName
                Else
                    Me.Range(Chr(Asc("A") + i) & listRows).FormulaR1C1 = "=" & strFunction & "(" & strResult & ")"
                End If
            End If
        Next
    End Sub
    '绑定标题部分的控件,绑定详细部分的控件，数据显示的标题，如＂最大力、最大变形＂等, 绑定单位
    Private Sub BindNamedRange()

        '绑定标题部分的控件
        ReportTitle.Add(NamedRange1)
        ReportTitle.Add(NamedRange2)
        ReportTitle.Add(NamedRange3)
        ReportTitle.Add(NamedRange4)
        ReportTitle.Add(NamedRange5)
        ReportTitle.Add(NamedRange6)
        ReportTitle.Add(NamedRange7)
        ReportTitle.Add(NamedRange8)
        ReportTitle.Add(NamedRange9)
        ReportTitle.Add(NamedRange10)
        ReportTitle.Add(NamedRange11)
        ReportTitle.Add(NamedRange12)
        ReportTitle.Add(NamedRange13)
        ReportTitle.Add(NamedRange14)
        ReportTitle.Add(NamedRange15)
        ReportTitle.Add(NamedRange16)
        ReportTitle.Add(NamedRange17)
        ReportTitle.Add(NamedRange18)
        ReportTitle.Add(NamedRange19)
        ReportTitle.Add(NamedRange20)
        ReportTitle.Add(NamedRange21)
        ReportTitle.Add(NamedRange22)
        ReportTitle.Add(NamedRange23)
        ReportTitle.Add(NamedRange24)
        ReportTitle.Add(NamedRange25)
        ReportTitle.Add(NamedRange26)
        ReportTitle.Add(NamedRange27)
        ReportTitle.Add(NamedRange28)
        ReportTitle.Add(NamedRange29)
        ReportTitle.Add(NamedRange30)
        ReportTitle.Add(NamedRange31)
        ReportTitle.Add(NamedRange32)
        ReportTitle.Add(NamedRange33)
        ReportTitle.Add(NamedRange34)
        ReportTitle.Add(NamedRange35)
        ReportTitle.Add(NamedRange36)
        ReportTitle.Add(NamedRange37)
        ReportTitle.Add(NamedRange38)
        ReportTitle.Add(NamedRange39)
        ReportTitle.Add(NamedRange40)
        ReportTitle.Add(NamedRange41)
        ReportTitle.Add(NamedRange42)
        ReportTitle.Add(NamedRange43)
        ReportTitle.Add(NamedRange44)
        ReportTitle.Add(NamedRange45)
        ReportTitle.Add(NamedRange46)
        ReportTitle.Add(NamedRange47)
        ReportTitle.Add(NamedRange48)
        ReportTitle.Add(NamedRange49)
        ReportTitle.Add(NamedRange50)
        ReportTitle.Add(NamedRange51)
        ReportTitle.Add(NamedRange52)
        ReportTitle.Add(NamedRange53)
        ReportTitle.Add(NamedRange54)
        ReportTitle.Add(NamedRange55)
        ReportTitle.Add(NamedRange56)
        ReportTitle.Add(NamedRange57)
        ReportTitle.Add(NamedRange58)
        ReportTitle.Add(NamedRange59)
        ReportTitle.Add(NamedRange60)

        ReportTitle.Add(NamedRange61)
        ReportTitle.Add(NamedRange62)
        ReportTitle.Add(NamedRange63)
        ReportTitle.Add(NamedRange64)
        ReportTitle.Add(NamedRange65)
        ReportTitle.Add(NamedRange66)
        ReportTitle.Add(NamedRange67)
        ReportTitle.Add(NamedRange68)
        ReportTitle.Add(NamedRange69)
        ReportTitle.Add(NamedRange70)
        ReportTitle.Add(NamedRange71)
        ReportTitle.Add(NamedRange72)
        ReportTitle.Add(NamedRange73)
        ReportTitle.Add(NamedRange74)
        ReportTitle.Add(NamedRange75)


        '绑定详细部分的控件，数据显示的标题，如＂最大力、最大变形＂等
        Dim strName As String = ""

        ReportDetail.Add(NamedRange76)
        ReportDetail.Add(NamedRange77)
        ReportDetail.Add(NamedRange78)
        ReportDetail.Add(NamedRange79)
        ReportDetail.Add(NamedRange80)
        ReportDetail.Add(NamedRange81)
        ReportDetail.Add(NamedRange82)
        ReportDetail.Add(NamedRange83)
        ReportDetail.Add(NamedRange84)
        ReportDetail.Add(NamedRange85)
        ReportDetail.Add(NamedRange86)
        ReportDetail.Add(NamedRange87)
        ReportDetail.Add(NamedRange88)
        ReportDetail.Add(NamedRange89)
        ReportDetail.Add(NamedRange90)

        '判断控件是否为空，为空是赋值给ReportDetail空字符串。
        CheckNamedRange(NamedRange76)
        CheckNamedRange(NamedRange77)
        CheckNamedRange(NamedRange78)
        CheckNamedRange(NamedRange79)
        CheckNamedRange(NamedRange80)
        CheckNamedRange(NamedRange81)
        CheckNamedRange(NamedRange82)
        CheckNamedRange(NamedRange83)
        CheckNamedRange(NamedRange84)
        CheckNamedRange(NamedRange85)
        CheckNamedRange(NamedRange86)
        CheckNamedRange(NamedRange87)
        CheckNamedRange(NamedRange88)
        CheckNamedRange(NamedRange89)
        CheckNamedRange(NamedRange90)

        '绑定单位
        ReportDetailUnit.Add(NamedRange91)
        ReportDetailUnit.Add(NamedRange92)
        ReportDetailUnit.Add(NamedRange93)
        ReportDetailUnit.Add(NamedRange94)
        ReportDetailUnit.Add(NamedRange95)
        ReportDetailUnit.Add(NamedRange96)
        ReportDetailUnit.Add(NamedRange97)
        ReportDetailUnit.Add(NamedRange98)
        ReportDetailUnit.Add(NamedRange99)
        ReportDetailUnit.Add(NamedRange100)
        ReportDetailUnit.Add(NamedRange101)
        ReportDetailUnit.Add(NamedRange102)
        ReportDetailUnit.Add(NamedRange103)
        ReportDetailUnit.Add(NamedRange104)
        ReportDetailUnit.Add(NamedRange105)

        '绑定标题部分的控件读取数据库中的数据
        Dim temp As Integer
        Dim rowDB As DataRow
        Dim i As Integer
        Dim j As Integer
        Try
            'For i = 0 To 74 Step 3
            '    j = i
            '    If Trim(ReportTitle(i).value2) <> "" Then
            '        Me.ReportTitle(i + 1).DataBindings.Add("value2", tbSheet, Trim(ReportTitle(i).value2))
            '        temp = DBChange.GetUnitID(ReportTitle(i).value2.ToString)
            '        rowDB = tbUnit.Rows.Find(temp)
            '        If rowDB Is Nothing Then
            '        Else
            '            ReportTitle(i + 2).value2 = rowDB("Label")
            '        End If
            '    End If
            'Next

            For i = 0 To 74 Step 3
                If Trim(ReportTitle(i).value2) <> "" Then
                    For j = 0 To tbSheet.Columns.Count
                        If ReportTitle(i).value2 = strChange(0, j) Then
                            Me.ReportTitle(i + 1).DataBindings.Add("value2", tbSheet, strChange(1, j))
                            temp = DBChange.GetUnitID(strChange(1, j))
                            rowDB = tbUnit.Rows.Find(temp)
                            If rowDB Is Nothing Then
                            Else
                                ReportTitle(i + 2).value2 = rowDB("Label")
                            End If
                        End If
                    Next
                End If
            Next

        Catch ex As ArgumentException
            MessageBox.Show("数据库没有   " & ReportTitle(i).value2 & " 字段!请重新输入后保存再打开文件！" & ex.Message)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        '绑定详细部分的控件读取数据库中的数据
        Try
            If TableRead("#######0.00") Then
                Dim listRows As Integer = tbSheet.Rows.Count
                listRows = ReportDetail(0).Cells.Row + 2 + listRows
                '  在数据后面添加统计值，如“最大值”“最小值”“平均值”等
                sheetResult("最大值", "Max", listRows)
                sheetResult("中值", "Median", listRows + 1)
                sheetResult("最小值", "Min", listRows + 2)
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub
    '判断控件是否为空，为空是赋值给ReportDetail空字符串。
    Private Sub CheckNamedRange(ByVal temp As Microsoft.Office.Tools.Excel.NamedRange)
        Dim strName As String
        If temp.Value2 Is Nothing Then
            strName = ""
        Else
            strName = temp.Value2
        End If
        ReportDetail.Add(strName)
    End Sub

    Private Sub Sheet1_Shutdown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shutdown

    End Sub

End Class