reportwarehouseinform.vb

一个商品销售管理系统

'文件名：ReportWarehouseInForm.vb
Imports System.Data.SqlClient
Imports Microsoft.Office.Interop.Excel
Imports System.Reflection
Imports Microsoft.Office.Core
Public Class ReportWarehouseInForm
    Public MyCompany As String
    Private MyQueryTable As System.Data.DataTable
    Private Sub ReportWarehouseInForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '设置商品名称
        Dim MyConnection As New SqlConnection()
        MyConnection.ConnectionString = My.Settings.MySaleConnectionString
        MyConnection.Open()
        Dim MyCommand As New SqlCommand("Select DISTINCT 商品名称 From 商品信息  ", MyConnection)
        Dim MyReader As SqlDataReader = MyCommand.ExecuteReader()
        Me.商品名称ComboBox.Items.Add("%%")
        While (MyReader.Read())
            Me.商品名称ComboBox.Items.Add(MyReader.GetString(0))
        End While
        If (MyConnection.State = ConnectionState.Open) Then
            MyConnection.Close()
        End If
    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 采购入库明细视图 WHERE 入库日期 BETWEEN '" + Me.采购开始日期DateTimePicker.Value.ToString() + "' AND '" + Me.采购结束日期DateTimePicker.Value.ToString() + "' AND 商品名称 LIKE '" + Me.商品名称ComboBox.Text + "'"
        Dim MyAdapter As New SqlDataAdapter(MySQL, MyConnection)
        MyAdapter.Fill(MyQueryTable)
        Me.采购入库明细DataGridView.DataSource = MyQueryTable
        If (MyConnection.State = ConnectionState.Open) Then
            MyConnection.Close()
        End If
    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.MyCompany + "商品采购入库统计表"
        MyWorkSheet.Cells(4, 1) = "开始日期：" + Me.采购开始日期DateTimePicker.Value.ToShortDateString() + "　　　　　　　　结束日期：" + Me.采购结束日期DateTimePicker.Value.ToShortDateString()
    End Sub
End Class