usercontrol1.vb

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

Imports System.Math

Public Class UserControl1
    '更新按钮
    Private Sub BtnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnUpdate.Click
        Dim i, j
        Dim temp As Integer
        temp = Globals.Sheet1.ReportDetail(0).Cells.Row + 1
        Try
            '报告详细部分修改,清除详细部分ReportDetail的内容,清除单元格内容
            If RbtnDetail.Checked = True Then
                For i = 0 To Globals.ThisWorkbook.tbSheet.Rows.Count
                    '清除详细部分ReportDetail的内容
                    If i < 15 And i > 2 Then
                        Globals.Sheet1.ReportDetail(i).Value2 = ""
                        Globals.Sheet1.ReportDetailUnit(i).value2 = ""
                    End If
                    '清除单元格内容
                    For j = 3 To (Globals.Sheet1.ReportDetail.Count - 1)
                        Globals.Sheet1.Range(Chr(Asc("A") + j) & temp + i).Value2() = ""
                    Next
                Next
                '
                For i = 0 To LstField.Items.Count - 1
                    If i < Globals.Sheet1.ReportDetail.Count - 2 Then
                        Globals.Sheet1.ReportDetail(i + 1).Value2 = LstField.Items(i).ToString
                    Else
                        Exit For
                    End If
                Next

                '  读取数据
                If Globals.Sheet1.TableRead("#######0.00") Then
                    Dim listRows As Integer = Globals.ThisWorkbook.tbSheet.Rows.Count
                    listRows = Globals.Sheet1.ReportDetail(0).Cells.Row + 1 + listRows
                    temp = listRows

                    '  在数据后面添加统计值，如“最大值”“最小值”“平均值”等
                    If CkbMaxValue.Checked = True Then
                        listRows = listRows + 1
                        Globals.Sheet1.sheetResult("最大值", "Max", listRows)
                    End If
                    If CkbMedValue.Checked = True Then
                        listRows = listRows + 1
                        Globals.Sheet1.sheetResult("中值", "Median", listRows)
                    End If
                    If CkbMinValue.Checked = True Then
                        listRows = listRows + 1
                        Globals.Sheet1.sheetResult("最小值", "Min", listRows)
                    End If
                    If CkbAvgValue.Checked = True Then
                        listRows = listRows + 1
                        Globals.Sheet1.sheetResult("平均值", "AVERAGE", listRows)
                    End If
                    If CkbStdValue.Checked = True Then
                        listRows = listRows + 1
                        Globals.Sheet1.sheetResult("标准差", "STDEV", listRows)
                    End If
                    For i = listRows To temp + 4
                        ClearRange(i + 1)
                    Next
                End If
            Else
                '报告标题部分修改,标题部分清空。
                For i = 0 To Globals.Sheet1.ReportTitle.Count - 2
                    Globals.Sheet1.ReportTitle(i).Value2 = ""
                Next
                For i = 0 To LstField.Items.Count - 1
                    Globals.Sheet1.ReportTitle(i * 3).DataBindings.clear()
                    Globals.Sheet1.ReportTitle(i * 3).Value2 = LstField.Items(i).ToString
                Next
                Globals.Sheet1.ReportTitle(63).DataBindings.clear()
                Globals.Sheet1.ReportTitleRead()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub
    Private Sub ClearRange(ByVal listrows As Integer)
        Dim j As Integer
        For j = 0 To (Globals.Sheet1.ReportDetail.Count - 1)
            Globals.Sheet1.Range(Chr(Asc("A") + j) & listrows).Value2() = ""
        Next
    End Sub


    Private Sub BtnReturn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnReturn.Click
        Try
            If LstField.Items.Count < 21 Then
                With LstReport
                    .BeginUpdate()
                    While (.SelectedIndex <> -1)
                        LstField.Items.Add(.SelectedItem)
                        .Items.RemoveAt(.SelectedIndex)
                    End While
                    .EndUpdate()
                End With
            End If
        Catch ex As Exception
        End Try
    End Sub

    Private Sub BtnBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnBack.Click
        Try
            With LstField
                .BeginUpdate()
                While (.SelectedIndex <> -1)
                    LstReport.Items.Add(.SelectedItem)
                    .Items.RemoveAt(.SelectedIndex)

                End While
                .EndUpdate()
            End With
        Catch ex As Exception
        End Try
    End Sub

    '设置小位数事件
    Private Sub CboDecimalDigits_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CboDecimalDigits.SelectedValueChanged
        Select Case CboDecimalDigits.SelectedItem
            Case "100"
                Globals.Sheet1.TableRead("#####000")
            Case "10"
                Globals.Sheet1.TableRead("######00")
            Case "1"
                Globals.Sheet1.TableRead("#######0")
            Case "0.1"
                Globals.Sheet1.TableRead("#######0.0")
            Case "0.01"
                Globals.Sheet1.TableRead("#######0.00")
            Case "0.001"
                Globals.Sheet1.TableRead("#######0.000")
            Case "0.0001"
                Globals.Sheet1.TableRead("#######0.0000")
            Case "0.00001"
                Globals.Sheet1.TableRead("#######0.00000")
            Case Else
                MessageBox.Show("对不起！选择有误！")
        End Select
    End Sub

    Private Sub UserControl1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim i As Integer
        Dim strDecimal() As String = {"0.01", "0.001", "0.0001", "0.00001", "0.1", "1", "10", "100", "RoundGB228R", "RoundGB228Ae", "RoundGB228A", "RoundGB228Z"}
        CboDecimalDigits.DataSource = strDecimal
        'CboDecimalDigits.SelectedIndex = 4

        RbtnDetail.Checked = True
        'CkbMaxValue.Checked = True
        'CkbMinValue.Checked = True
        'CkbMedValue.Checked = True

        'lstReport控件绑定
        For i = 0 To Globals.ThisWorkbook.tbSheet.Columns.Count - 1
            'myUserControl.LstReport.Items.Add(DBChange.tdfNew(i).Properties("Caption").Value)
            LstReport.Items.Add(Globals.ThisWorkbook.strChange(0, i))
            'myUserControl.ListBox1.Items.Add(tbSheet.Columns.Item(i).Caption)
        Next
        For i = 1 To Globals.Sheet1.ReportDetail.Count - 1
            If Globals.Sheet1.ReportDetail(i).value2 <> "" Then
                With LstReport
                    .BeginUpdate()
                    LstField.Items.Add(Globals.Sheet1.ReportDetail(i).value2)
                    .Items.Remove(Globals.Sheet1.ReportDetail(i).value2)
                    .EndUpdate()
                End With
            End If
        Next
    End Sub

End Class