printreturnform.vb

这是一个固定资产管理系统的源代码

'文件名：PrintReturnForm.vb
Imports System.Data.SqlClient
Imports Microsoft.Office.Interop.Excel
Imports System.Reflection
Imports Microsoft.Office.Core
Public Class PrintReturnForm
    Public MyCompany As String
    Public MyQueryTable As System.Data.DataTable
    Private Sub 查询Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 查询Button.Click
        Dim MySQLConnectionString As String = My.Settings.MyAssetsConnectionString
        Dim MyConnection As New SqlConnection(MySQLConnectionString)
        MyConnection.Open()
        MyQueryTable = New System.Data.DataTable()
        Dim MySQL As String = "SELECT dbo.固定资产明细.使用部门, dbo.固定资产租借记录.资产编号,dbo.固定资产租借记录.名称, dbo.固定资产租借记录.数量,dbo.固定资产租借记录.单位, dbo.固定资产租借记录.租借公司,dbo.固定资产租借记录.租借日期, dbo.固定资产租借记录.归还日期,dbo.固定资产租借记录.实际收益, dbo.固定资产租借记录.经办人,dbo.固定资产租借记录.说明 FROM dbo.固定资产租借记录 INNER JOIN dbo.固定资产明细 ON dbo.固定资产租借记录.资产编号 = dbo.固定资产明细.资产编号 WHERE 归还日期 BETWEEN '" + Me.开始日期DateTimePicker.Value + "' AND '" + Me.结束日期DateTimePicker.Value + "'"
        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