frmbookquery.vb

The Management Information System of Library using .NET

        Me.Panel2.Controls.Add(Me.txtBook_code)
        Me.Panel2.Controls.Add(Me.Label5)
        Me.Panel2.Controls.Add(Me.Label4)
        Me.Panel2.Controls.Add(Me.Label3)
        Me.Panel2.Controls.Add(Me.Label2)
        Me.Panel2.Controls.Add(Me.Label1)
        Me.Panel2.Controls.Add(Me.dtpBook_pubdate)
        Me.Panel2.Controls.Add(Me.txtBook_isbn)
        Me.Panel2.Controls.Add(Me.txtBook_pub)
        Me.Panel2.Controls.Add(Me.Label7)
        Me.Panel2.Dock = System.Windows.Forms.DockStyle.Fill
        Me.Panel2.Location = New System.Drawing.Point(0, 0)
        Me.Panel2.Name = "Panel2"
        Me.Panel2.Size = New System.Drawing.Size(798, 108)
        Me.Panel2.TabIndex = 0
        '
        'GroupBox2
        '
        Me.GroupBox2.Controls.Add(Me.cboBook_sort)
        Me.GroupBox2.Controls.Add(Me.Label11)
        Me.GroupBox2.Controls.Add(Me.Label9)
        Me.GroupBox2.Controls.Add(Me.radAllBook)
        Me.GroupBox2.Controls.Add(Me.radInlibBook)
        Me.GroupBox2.Location = New System.Drawing.Point(0, 64)
        Me.GroupBox2.Name = "GroupBox2"
        Me.GroupBox2.Size = New System.Drawing.Size(592, 40)
        Me.GroupBox2.TabIndex = 56
        Me.GroupBox2.TabStop = False
        '
        'cboBook_sort
        '
        Me.cboBook_sort.ItemHeight = 12
        Me.cboBook_sort.Location = New System.Drawing.Point(72, 14)
        Me.cboBook_sort.Name = "cboBook_sort"
        Me.cboBook_sort.Size = New System.Drawing.Size(128, 20)
        Me.cboBook_sort.TabIndex = 53
        Me.cboBook_sort.Text = "ComboBox2"
        '
        'Label11
        '
        Me.Label11.Location = New System.Drawing.Point(8, 16)
        Me.Label11.Name = "Label11"
        Me.Label11.Size = New System.Drawing.Size(64, 16)
        Me.Label11.TabIndex = 52
        Me.Label11.Text = "图书分类:"
        '
        'Label9
        '
        Me.Label9.AutoSize = True
        Me.Label9.Location = New System.Drawing.Point(200, 16)
        Me.Label9.Name = "Label9"
        Me.Label9.Size = New System.Drawing.Size(60, 17)
        Me.Label9.TabIndex = 36
        Me.Label9.Text = "查询范围:"
        '
        'radAllBook
        '
        Me.radAllBook.Checked = True
        Me.radAllBook.Location = New System.Drawing.Point(272, 14)
        Me.radAllBook.Name = "radAllBook"
        Me.radAllBook.Size = New System.Drawing.Size(72, 18)
        Me.radAllBook.TabIndex = 0
        Me.radAllBook.TabStop = True
        Me.radAllBook.Text = "全部图书"
        '
        'radInlibBook
        '
        Me.radInlibBook.Location = New System.Drawing.Point(360, 14)
        Me.radInlibBook.Name = "radInlibBook"
        Me.radInlibBook.Size = New System.Drawing.Size(72, 18)
        Me.radInlibBook.TabIndex = 1
        Me.radInlibBook.Text = "在馆图书"
        '
        '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.dbgBookList)
        Me.GroupBox1.Location = New System.Drawing.Point(0, 168)
        Me.GroupBox1.Name = "GroupBox1"
        Me.GroupBox1.Size = New System.Drawing.Size(802, 367)
        Me.GroupBox1.TabIndex = 58
        Me.GroupBox1.TabStop = False
        Me.GroupBox1.Text = "查询结果"
        '
        'Label8
        '
        Me.Label8.Font = New System.Drawing.Font("宋体", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(134, Byte))
        Me.Label8.ForeColor = System.Drawing.Color.Red
        Me.Label8.Location = New System.Drawing.Point(320, 16)
        Me.Label8.Name = "Label8"
        Me.Label8.Size = New System.Drawing.Size(264, 23)
        Me.Label8.TabIndex = 59
        Me.Label8.Text = "请键入查询关键字"
        '
        'diaSave
        '
        Me.diaSave.Filter = "|*.xls"
        '
        'frmBookQuery
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.ClientSize = New System.Drawing.Size(802, 535)
        Me.Controls.Add(Me.Label8)
        Me.Controls.Add(Me.GroupBox1)
        Me.Controls.Add(Me.Panel1)
        Me.Controls.Add(Me.ToolBar1)
        Me.Name = "frmBookQuery"
        Me.Text = "图书综合查询"
        CType(Me.dbgBookList, System.ComponentModel.ISupportInitialize).EndInit()
        Me.Panel1.ResumeLayout(False)
        Me.Panel2.ResumeLayout(False)
        Me.GroupBox2.ResumeLayout(False)
        Me.GroupBox1.ResumeLayout(False)
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub frmBookQuery_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        With Me.dbgBookList
            .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
        BindComboBox1()
        Me.cboBook_sort.Text = ""
    End Sub
    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
            If Me.radAllBook.Checked = True Then
                .CommandText = "searchBook"
            Else
                .CommandText = "searchBookInlib"
            End If
            .CommandType = CommandType.StoredProcedure
            .Connection = cn
        End With

        Dim mBook_code As New SqlParameter("@Book_code", SqlDbType.NVarChar, 50) '1
        Dim mBook_name As New SqlParameter("@Book_name", SqlDbType.NVarChar, 50) '2
        Dim mBook_pub As New SqlParameter("@Book_pub", SqlDbType.NVarChar, 50) '3
        Dim mBook_isbn As New SqlParameter("@Book_isbn", SqlDbType.NVarChar, 50) '4
        Dim mBook_pubdate As New SqlParameter("@Book_pubdate", SqlDbType.SmallDateTime, 4) '5
        Dim mBook_author As New SqlParameter("@Book_author", SqlDbType.NVarChar, 50) '6
        'Dim mBook_page As New SqlParameter("@Book_page", SqlDbType.Int, 4) '7
        ' Dim mBook_price As New SqlParameter("@Book_price", SqlDbType.Money, 8) '8
        'Dim mBook_adddate As New SqlParameter("@Book_adddate", SqlDbType.SmallDateTime, 4) '9
        'Dim mBook_place As New SqlParameter("@Book_place", SqlDbType.NVarChar, 50) '10
        Dim mBook_sort As New SqlParameter("@Book_sort", SqlDbType.NVarChar, 50) '11
        'Dim mBook_remarks As New SqlParameter("@Book_remarks", SqlDbType.NVarChar, 50) '12
        With cmd.Parameters
            .Add(mBook_code)
            .Add(mBook_name)
            .Add(mBook_pub)
            .Add(mBook_isbn)
            .Add(mBook_pubdate)
            .Add(mBook_author)
            '.Add(mBook_page)
            ' .Add(mBook_price)
            '.Add(mBook_adddate)
            '.Add(mBook_place)
            .Add(mBook_sort)
            ' .Add(mBook_remarks)
        End With
        '赋值
        mBook_code.Value = Me.txtBook_code.Text.Trim  '1
        mBook_name.Value = Me.txtBook_name.Text.Trim  '2
        mBook_pub.Value = Me.txtBook_pub.Text.Trim  '3
        mBook_isbn.Value = Me.txtBook_isbn.Text.Trim  '4 
        mBook_pubdate.Value = Me.dtpBook_pubdate.Value '5
        mBook_author.Value = Me.txtBook_author.Text.Trim   '6
        'mBook_page.Value = Val(Me.txtBook_page.Text) '7
        'mBook_price.Value = Val(Me.txtBook_price.Text) '8
        'mBook_adddate.Value = Me.dtpBook_adddate.Value  '9
        'mBook_place.Value = Me.cboBook_place.Text  '10
        mBook_sort.Value = Me.cboBook_sort.Text.Trim   '11
        ' mBook_remarks.Value = Me.txtBook_remarks.Text  '12


        da = New SqlDataAdapter(cmd)

        Try
            da.Fill(ds)
            ds.Tables(0).Columns(0).Caption = "图书条码号"
            Me.dbgBookList.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
    Function BindComboBox1()
        Dim cnLib2004 As SqlConnection
        Dim cmdbooksort As SqlCommand
        Dim drBooksort As SqlDataReader

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

        Try
            cnLib2004.Open()
            drBooksort = cmdbooksort.ExecuteReader
            Dim myarr As New ArrayList
            While drBooksort.Read
                myarr.Add(drBooksort.Item(0))
            End While
            Me.cboBook_sort.DataSource = myarr
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Me.Close()
        Finally
            cnLib2004.Close()
        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.cboBook_sort.Text = ""
        Me.radAllBook.Checked = True
        Me.dtpBook_pubdate.Value = "1990-1-1"
        Me.txtBook_code.Focus()
        Me.dbgBookList.DataSource = Nothing
    End Sub
    Sub excel()

        Dim tableAu As DataTable
        tableAu = CType(Me.dbgBookList.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)
            'workbook.SaveAs(myfile)
            sheet.Application.Visible = True
            'exl.Quit()
            myfrm.Close()
            '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.dbgBookList.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 crtBook
        rtd.SetDataSource(tableAu)
        myfrm.Text = "报表输出窗口..."
        myfrm.rpt.ReportSource = rtd
        myfrm.rpt.DisplayGroupTree = False
        myfrm2.Close()
        myfrm.ShowDialog()

    End Sub
 
End Class