Visual Basic 2010 数据库开发之家庭理财03

续上

6.添加数据源，把那个数据加进项目里

7.添加一个Module模块

Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.Office.Interop.Excel
Module Module1
    '设置连接对象
    Public conn As OleDb.OleDbConnection
    Public Function GetConnection() As OleDb.OleDbConnection
        Return New OleDb.OleDbConnection(My.Settings.mydataConnectionString)
    End Function

    '设置捆绑的控件都不可用
    Public Sub SetTextboxFalse()
        Form1.DateTimePicker1.Enabled = False
        Form1.cmbXM.Enabled = False
        Form1.txtJe.Enabled = False
        Form1.txtBz.Enabled = False
        Form1.cmbJsr.Enabled = False
        Form1.RadioButton1.Enabled = False
        Form1.RadioButton2.Enabled = False
    End Sub

    '设置捆绑的控件都可用
    Public Sub SetTextboxTrue()
        Form1.DateTimePicker1.Enabled = True
        Form1.cmbXM.Enabled = True
        Form1.txtJe.Enabled = True
        Form1.txtBz.Enabled = True
        Form1.cmbJsr.Enabled = True
        Form1.RadioButton1.Enabled = True
        Form1.RadioButton2.Enabled = True
    End Sub

    '清空控件
    Public Sub ClearText()
        Form1.DateTimePicker1.Value = Now
        'Form1.cmbXM.Enabled = True
        Form1.txtJE.Text = ""
        Form1.txtBZ.Text = ""
        'Form1.cmbJSR.Enabled = True
        'Form1.RadioButton1.Enabled = True
        'Form1.RadioButton2.Enabled = True
    End Sub

    '求出支收总金额和显示在状态栏1
    Public Sub QiuJinEHe()
        Dim conSum As Single

        conn = GetConnection()
        conn.Open()

        Dim strSql As String = "SELECT sum(金额)-(select sum(金额) from feiyong where 类型=false) from feiyong where 类型=true"
        Dim myComm As New OleDb.OleDbCommand(strSql, conn)
        Dim myReader As oledb.oledbDataReader
        myReader = myComm.ExecuteReader()
        While myReader.Read
            Try
                conSum = myReader.GetValue(0)
            Catch ex As Exception
                MsgBox(Err.Description)
            End Try
        End While

        conn.Close()
        Form1.ToolStripStatusLabel1.Text = "收支总额：" & conSum.ToString

    End Sub

    '求出收入总金额和在状态栏2
    Public Sub QiuShouRuHe()

        Dim conSum2 As Single
        conn = GetConnection()
        conn.Open()

        Dim strSql As String = "SELECT sum(金额) from feiyong where 类型=true"
        Dim myComm2 As New OleDb.OleDbCommand(strSql, conn)
        Dim myReader2 As OleDb.OleDbDataReader
        myReader2 = myComm2.ExecuteReader
        If myReader2.Read Then
            If myReader2.IsDBNull(0) Then
                conSum2 = 0
            Else
                conSum2 = myReader2.GetValue(0)
            End If
        End If

        conn.Close()
        Form1.ToolStripStatusLabel2.Text = "收入总额：" & conSum2.ToString
    End Sub

    '求出支出总金额在状态栏3
    Public Sub QiuZhiChuHe()
        Dim conSum1 As Single
        conn = GetConnection()
        conn.Open()

        Dim strSql As String = "SELECT sum(金额) from feiyong where 类型=false"
        Dim myComm1 As New OleDb.OleDbCommand(strSql, conn)
        Dim myReader1 As OleDb.OleDbDataReader
        myReader1 = myComm1.ExecuteReader
        If myReader1.Read Then
            If myReader1.IsDBNull(0) Then
                conSum1 = 0
            Else
                conSum1 = myReader1.GetValue(0)
            End If
        End If

        conn.Close()

        Form1.ToolStripStatusLabel3.Text = "支出总额：" & conSum1
    End Sub
    '导出Excel表
    Public Sub ExportXLsD(ByVal dgv As DataGridView)
        Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application
        Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
        xlApp.Visible = True
        xlBook = xlApp.Workbooks.Add()
        xlSheet = xlBook.Sheets.Add()
        'xlSheet.Name = "Sample Worksheet"
        'xlApp.DisplayAlerts = False

        '在Excel表里显示数据  
        Dim i As Integer
        For i = 0 To dgv.RowCount - 1
            Dim j As Integer
            For j = 0 To dgv.ColumnCount - 1
                If dgv(j, i).Value Is System.DBNull.Value Then
                    xlSheet.Cells(i + 2, j + 1) = ""
                Else
                    xlSheet.Cells(i + 2, j + 1) = dgv(j, i).Value
                End If
            Next j
        Next i
        xlBook.Save()
        xlBook.Close()
    End Sub

    Public Function BaoHanFu(ByVal ListIndex As Integer) As String
        If ListIndex = 0 Then
            Return "%"
        Else
            Return ""
        End If
    End Function


End Module

8.添加一个类

Imports System.Data
Imports System.Data.OleDb
Public Class balance
    Dim _编号 As Integer
    '编号
    Public Property 编号() As Integer
        Get
            Return _编号
        End Get
        Set(ByVal value As Integer)
            _编号 = value
        End Set
    End Property
    '日期
    Dim _日期 As Date
    Public Property 日期() As Date
        Get
            Return _日期
        End Get
        Set(ByVal value As Date)
            _日期 = value
        End Set
    End Property
    '项目
    Dim _项目 As String
    Public Property 项目() As String
        Get
            Return _项目
        End Get
        Set(ByVal value As String)
            _项目 = value
        End Set
    End Property
    '金额
    Dim _金额 As Single
    Public Property 金额() As Single
        Get
            Return _金额
        End Get
        Set(ByVal value As Single)
            _金额 = value
        End Set
    End Property
    '经手人
    Dim _经手人 As String
    Public Property 经手人 As String
        Get
            Return _经手人