reportcollectform.vb

一个商品销售管理系统

'文件名：ReportCollectForm.vb
Imports System.Data.SqlClient
Imports Microsoft.Office.Interop.Excel
Imports System.Reflection
Imports Microsoft.Office.Core
Public Class ReportCollectForm
    Public MyCompany As String
    Public MyTitle As String
    Private MyQueryTable As System.Data.DataTable
    Private Sub 累计入库商品Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 累计入库商品Button.Click
        MyQueryTable = New System.Data.DataTable()
        Dim MyConnection As New SqlConnection()
        MyConnection.ConnectionString = My.Settings.MySaleConnectionString
        MyConnection.Open()
        Dim MySQL As String = "SELECT * FROM 累计采购商品视图"
        Dim MyAdapter As New SqlDataAdapter(MySQL, MyConnection)
        MyAdapter.Fill(MyQueryTable)
        Me.汇总信息DataGridView.DataSource = MyQueryTable
        If (MyConnection.State = ConnectionState.Open) Then
            MyConnection.Close()
        End If
        Me.MyTitle = Me.MyCompany + "分类商品累计入库汇总表"
    End Sub
    Private Sub 累计出库商品Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 累计出库商品Button.Click
        MyQueryTable = New System.Data.DataTable()
        Dim MyConnection As New SqlConnection()
        MyConnection.ConnectionString = My.Settings.MySaleConnectionString
        MyConnection.Open()
        Dim MySQL As String = "SELECT * FROM 累计销售商品视图"
        Dim MyAdapter As New SqlDataAdapter(MySQL, MyConnection)
        MyAdapter.Fill(MyQueryTable)
        Me.汇总信息DataGridView.DataSource = MyQueryTable
        If (MyConnection.State = ConnectionState.Open) Then
            MyConnection.Close()
        End If
        Me.MyTitle = Me.MyCompany + "分类商品累计出库汇总表"
    End Sub
    Private Sub 当前库存商品Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 当前库存商品Button.Click
        MyQueryTable = New System.Data.DataTable()
        Dim MyConnection As New SqlConnection()
        MyConnection.ConnectionString = My.Settings.MySaleConnectionString
        MyConnection.Open()
        Dim MySQL As String = "SELECT 商品编号,商品名称,规格型号,单位,累计采购量,累计销售量,当前库存量 FROM 商品信息"
        Dim MyAdapter As New SqlDataAdapter(MySQL, MyConnection)
        MyAdapter.Fill(MyQueryTable)
        Me.汇总信息DataGridView.DataSource = MyQueryTable
        If (MyConnection.State = ConnectionState.Open) Then
            MyConnection.Close()
        End If
        Me.MyTitle = Me.MyCompany + "分类商品当前库存汇总表"
    End Sub
    Private Sub 打印汇总报表Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 打印汇总报表Button.Click
        If (Me.MyQueryTable.Rows.Count < 1) Then
            Return
        End If
        '导出Excel表格数据文件
        Dim MyExcel As ApplicationClass
        Dim MyWorkBooks As Workbooks
        Dim MyWorkBook As Workbook
        Dim MyWorkSheet As Worksheet
        Dim MyColumns As Char
        Dim MyRange As Range
        Dim MyData(500, 35) As Object
        Dim i, j As Integer
        MyExcel = New ApplicationClass()
        MyExcel.Visible = True
        If (MyExcel Is Nothing) Then
            MessageBox.Show("Excel程序无法启动！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Return
        End If
        MyWorkBooks = MyExcel.Workbooks
        MyWorkBook = MyWorkBooks.Add(Missing.Value)
        MyWorkSheet = MyWorkBook.Worksheets(1)
        Dim MyChar As Integer = Me.MyQueryTable.Columns.Count + 64
        MyColumns = Convert.ToChar(MyChar)
        MyRange = MyWorkSheet.Range("A5", MyColumns.ToString() + "5")
        Dim Count As Integer = 0
        For Each MyNewColumn As DataColumn In Me.MyQueryTable.Columns
            MyData(0, Count) = MyNewColumn.ColumnName
            Count = Count + 1
        Next
        j = 1
        '输出数据库记录
        For Each MyRow As DataRow In Me.MyQueryTable.Rows
            For i = 0 To Me.MyQueryTable.Columns.Count - 1 Step 1
                MyData(j, i) = MyRow(i).ToString()
            Next
            j = j + 1
        Next
        MyRange = MyRange.Resize(Me.MyQueryTable.Rows.Count + 1, Me.MyQueryTable.Columns.Count)
        MyRange.Value2 = MyData
        MyRange.EntireColumn.AutoFit()
        MyWorkSheet.Cells(2, 2) = Me.MyTitle
        MyWorkSheet.Cells(4, 1) = "打印日期：" + DateTime.Now.ToShortDateString()
    End Sub
End Class