frmsearchau.vb

The Management Information System of Library using .NET

        '
        'Label3
        '
        Me.Label3.Location = New System.Drawing.Point(16, 40)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(64, 16)
        Me.Label3.TabIndex = 61
        Me.Label3.Text = "读者性别:"
        '
        'Label2
        '
        Me.Label2.Location = New System.Drawing.Point(224, 8)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(64, 16)
        Me.Label2.TabIndex = 59
        Me.Label2.Text = "读者姓名:"
        '
        'Label9
        '
        Me.Label9.ForeColor = System.Drawing.Color.Red
        Me.Label9.Location = New System.Drawing.Point(16, 8)
        Me.Label9.Name = "Label9"
        Me.Label9.Size = New System.Drawing.Size(64, 16)
        Me.Label9.TabIndex = 56
        Me.Label9.Text = "图书证号:"
        '
        'Label7
        '
        Me.Label7.ForeColor = System.Drawing.Color.Red
        Me.Label7.Location = New System.Drawing.Point(424, 72)
        Me.Label7.Name = "Label7"
        Me.Label7.Size = New System.Drawing.Size(40, 16)
        Me.Label7.TabIndex = 55
        Me.Label7.Text = "以后!!"
        '
        'diaSave
        '
        Me.diaSave.Filter = "|*.xls"
        '
        'GroupBox1
        '
        Me.GroupBox1.Anchor = CType((((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
                    Or System.Windows.Forms.AnchorStyles.Left) _
                    Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
        Me.GroupBox1.Controls.Add(Me.dbg)
        Me.GroupBox1.Location = New System.Drawing.Point(0, 168)
        Me.GroupBox1.Name = "GroupBox1"
        Me.GroupBox1.Size = New System.Drawing.Size(870, 360)
        Me.GroupBox1.TabIndex = 62
        Me.GroupBox1.TabStop = False
        Me.GroupBox1.Text = "查询结果"
        '
        'dbg
        '
        Me.dbg.Anchor = CType((((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
                    Or System.Windows.Forms.AnchorStyles.Left) _
                    Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
        Me.dbg.DataMember = ""
        Me.dbg.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.dbg.Location = New System.Drawing.Point(3, 17)
        Me.dbg.Name = "dbg"
        Me.dbg.Size = New System.Drawing.Size(864, 340)
        Me.dbg.TabIndex = 54
        '
        'frmSearchAu
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.ClientSize = New System.Drawing.Size(782, 535)
        Me.Controls.Add(Me.Label8)
        Me.Controls.Add(Me.Panel1)
        Me.Controls.Add(Me.GroupBox1)
        Me.Controls.Add(Me.ToolBar1)
        Me.Name = "frmSearchAu"
        Me.Text = "读者综合查询"
        Me.Panel1.ResumeLayout(False)
        Me.Panel2.ResumeLayout(False)
        Me.GroupBox1.ResumeLayout(False)
        CType(Me.dbg, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub frmSearchAu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        With Me.dbg
            .Anchor = CType((((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
                Or System.Windows.Forms.AnchorStyles.Left) _
                Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
            .BackColor = System.Drawing.Color.Snow
            .BackgroundColor = System.Drawing.SystemColors.ActiveCaptionText
            .CaptionVisible = False
            .HeaderForeColor = System.Drawing.SystemColors.ControlText
            .ReadOnly = True
        End With
        Me.BindComboBox1()
        Me.BindComboBoxSex()
        Me.cboAu_sex.Text = ""
        Me.cboAu_sort.Text = ""
    End Sub
    Function BindComboBox1()
        Dim cnLib2004 As SqlConnection
        Dim cmdAusort As SqlCommand
        Dim drAusort As SqlDataReader

        cnLib2004 = New SqlConnection(cnstr)
        cmdAusort = New SqlCommand
        With cmdAusort
            .CommandType = CommandType.StoredProcedure
            .Connection = cnLib2004
            .CommandText = "selectAuSort"
        End With

        Try
            cnLib2004.Open()
            drAusort = cmdAusort.ExecuteReader
            Dim myarr As New ArrayList
            While drAusort.Read
                myarr.Add(drAusort.Item(0))
            End While
            Me.cboAu_sort.DataSource = myarr
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Me.Close()
        Finally
            cnLib2004.Close()
        End Try
    End Function
    Function BindComboBoxSex()
        Dim arrSex As New ArrayList
        arrSex.Add("男")
        arrSex.Add("女")
        Me.cboAu_sex.DataSource = arrSex
    End Function
    Function search()

        Dim cn As New SqlClient.SqlConnection(cnstr)
        Dim cmd As New SqlCommand
        Dim da As SqlDataAdapter
        Dim ds As New DataSet
        With cmd
            .CommandText = "searchAllAu"
            .CommandType = CommandType.StoredProcedure
            .Connection = cn
        End With


        Dim mAu_id As New SqlParameter("@Au_id", SqlDbType.NVarChar, 50) '1
        Dim mAu_name As New SqlParameter("@Au_name", SqlDbType.NVarChar, 50) '2
        Dim mAu_sex As New SqlParameter("@Au_sex", SqlDbType.NVarChar, 2) '3
        Dim mAu_sort As New SqlParameter("@Au_sort", SqlDbType.NVarChar, 50) '4
        Dim mAu_adddate As New SqlParameter("@Au_adddate", SqlDbType.SmallDateTime, 4) '5
        Dim mAu_adr As New SqlParameter("@Au_adr", SqlDbType.NVarChar, 50) '6
        'Dim mAu_password As New SqlParameter("@Au_password", SqlDbType.NVarChar, 12) '7
        'Dim mAu_email As New SqlParameter("@Au_email", SqlDbType.NVarChar, 50)  '8
        'Dim mAu_remarks As New SqlParameter("@Au_remarks", SqlDbType.NVarChar, 4000)  '9

        With cmd.Parameters
            .Add(mAu_id)
            .Add(mAu_name)
            .Add(mAu_sex)
            .Add(mAu_sort)
            .Add(mAu_adddate)
            .Add(mAu_adr)
            ' .Add(mAu_password)
            ' .Add(mAu_email)
            '.Add(mAu_remarks)
        End With
        '赋值
        mAu_id.Value = Me.txtAu_id.Text.Trim  '1
        mAu_name.Value = Me.txtAu_name.Text.Trim  '2
        mAu_sex.Value = Me.cboAu_sex.Text.Trim  '3
        mAu_sort.Value = Me.cboAu_sort.Text.Trim  '4 
        mAu_adddate.Value = Me.dtpAu_adddate.Value '5
        mAu_adr.Value = Me.txtAu_adr.Text.Trim   '6
        ' mAu_password.Value = Me.txtAu_password.Text.Trim '7
        ' mAu_email.Value = Me.txtAu_email.Text.Trim  '8
        ' mAu_remarks.Value = Me.txtAu_remarks.Text.Trim    '9


        da = New SqlDataAdapter(cmd)

        Try
            da.Fill(ds, "Au")
            'MsgBox(ds.Tables("Au").TableName)           
            Me.dbg.DataSource = ds.Tables(0)
            Me.Label8.Text = "共检索到" + ds.Tables(0).Rows.Count.ToString + "条记录"
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            cn.Close()
            If Not cn Is Nothing Then
                cn.Dispose()
            End If
        End Try
    End Function

    Private Sub ToolBar1_ButtonClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ToolBarButtonClickEventArgs) Handles ToolBar1.ButtonClick
        If e.Button Is Me.tbtnQuery Then
            search()
        ElseIf e.Button Is Me.tbtnExit Then
            Me.Close()
        ElseIf e.Button Is Me.tbtnReset Then
            frmClear()
        ElseIf e.Button Is Me.tbtnExcel Then
            excel()
        ElseIf e.Button Is Me.tbtnPrint Then
            print()
        End If
    End Sub
    Private Sub frmClear()
        Dim p1 As Control
        Dim u As New Label
        For Each p1 In Me.Panel2.Controls
            If Not p1.GetType Is u.GetType Then
                p1.Text = ""
            End If
        Next
        Me.dtpAu_adddate.Value = "1990-1-1"
        Me.txtAu_id.Focus()
        Me.dbg.DataSource = Nothing
    End Sub
    Sub excel()

        Dim tableAu As DataTable
        tableAu = CType(Me.dbg.DataSource, DataTable)
        If tableAu Is Nothing Then
            MessageBox.Show("没有任何记录可以导出!", "error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        'Me.diaSave.InitialDirectory = System.Windows.Forms.Application.StartupPath
        'Me.diaSave.Filter = "Excel 文件(*.xls)|*.xls"
        'Me.diaSave.OverwritePrompt = False
        'Me.diaSave.ShowDialog()
        'Dim myfile As String
        'If Me.diaSave.FileNames.Length <> 0 Then
        '    myfile = diaSave.FileNames(0)
        'Else
        '    Exit Sub
        'End If

        Dim myfrm As New frmShowProgress
        myfrm.Show("正在建立excel对象...")
        Try
            Dim exl As New Microsoft.Office.Interop.Excel.ApplicationClass
            Dim workbook As Microsoft.Office.Interop.Excel.Workbook
            Dim sheet As Microsoft.Office.Interop.Excel.Worksheet

            workbook = CType(exl.Workbooks.Add(), Microsoft.Office.Interop.Excel.Workbook)
            sheet = CType(workbook.Sheets(1), Microsoft.Office.Interop.Excel.Worksheet)


            Dim mycount, mycolumn As Int16
            With tableAu
                myfrm.setMsg("正在导出数据,请等待......")
                For mycount = 0 To .Rows.Count - 1
                    For mycolumn = 0 To .Columns.Count - 1
                        With sheet
                            .Cells(mycount + 2, mycolumn + 1) = tableAu.Rows(mycount).Item(mycolumn)
                        End With
                    Next
                    'MsgBox(CType(mycount / .Rows.Count * 100, Int16))
                    myfrm.setProgress(CType(mycount / .Rows.Count * 100, Int16))
                Next
                myfrm.setMsg("正在设定excel表头格式...")
                For mycolumn = 0 To .Columns.Count - 1
                    exl.Workbooks(1).Worksheets(1).cells(1, mycolumn + 1) = .Columns(mycolumn).ColumnName
                    exl.Workbooks(1).Worksheets(1).cells(1, mycolumn + 1).Font.Bold = True
                Next
            End With
            myfrm.setProgress(100)
            myfrm.Close()
            'workbook.SaveAs(myfile)
            sheet.Application.Visible = True
            'exl.Quit()

            'MessageBox.Show("导出成功!", "success", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            'MessageBox.Show("导出失败!", "fail", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            MsgBox(ex.Message)
            myfrm.Close()
        End Try

        'Shell("c:\winnt\notepad.exe", AppWinStyle.MaximizedFocus, True)
    End Sub
    Private Sub print()

        Dim tableAu As DataTable
        tableAu = CType(Me.dbg.DataSource, DataTable)
        If tableAu Is Nothing Then
            MessageBox.Show("没有任何记录可以导出!", "error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        Dim myfrm2 As New frmStatus
        myfrm2.Show("正在导出报表...")
        Dim myfrm As New frmShowReport
        Dim rtd As New crtSearchAu
        rtd.SetDataSource(tableAu)
        myfrm.Text = "报表输出窗口..."
        myfrm.rpt.ReportSource = rtd
        myfrm.rpt.DisplayGroupTree = False
        myfrm2.Close()
        myfrm.ShowDialog()

    End Sub
End Class