exceloperation.vb

管理毕业设计的管理系统 VB+SQL2000....

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Core

Public Class ExcelOperation
    Public Shared Function ExcelSave(ByVal dmode As Integer, ByVal filepath As String) As Boolean
        Dim exapp As New Excel.Application                      '定义excel应用程序
        Dim exbook As Excel.Workbook = exapp.Workbooks.Add      '定义工作簿
        Dim exsheet As Excel.Worksheet = exbook.Sheets(1)       '定义工作表

        Dim strSQL As String = "select s.suser, s.sname, t.tname, s.sschedule from student s, teacher t where s.tid=t.tid"
        Dim UserTable As DataTable = DBOperation.Search(strSQL)
        Dim UserRow As DataRow
        Dim strSchedule As String
        Dim strSubSchedule As String

        exsheet.Cells(1, 1) = "学生学号"
        exsheet.Cells(1, 2) = "学生姓名"
        exsheet.Cells(1, 3) = "指导老师"
        exsheet.Cells(1, 4) = "完成情况"

        Dim i As Integer = 2

        For Each UserRow In UserTable.Rows
            exsheet.Cells(i, 1) = UserRow("suser")
            exsheet.Cells(i, 2) = UserRow("sname")
            exsheet.Cells(i, 3) = UserRow("tname")
            strSchedule = UserRow("sschedule")
            strSubSchedule = strSchedule.Substring(dmode - 1, 1)
            If strSubSchedule = "1" Then
                exsheet.Cells(i, 4) = "√"
            Else
                exsheet.Cells(i, 4) = "×"
            End If
            i = i + 1
        Next

        '保存
        Select Case dmode
            Case DocumentMode.ChooseMode
                exsheet.SaveAs(filepath + "\选题情况汇总.xls")
            Case DocumentMode.StartMode
                exsheet.SaveAs(filepath + "\开题报告完成情况汇总.xls")
            Case DocumentMode.LiteratureMode
                exsheet.SaveAs(filepath + "\文献综述完成情况汇总.xls")
            Case DocumentMode.MiddleMode
                exsheet.SaveAs(filepath + "\中期检查完成情况汇总.xls")
            Case DocumentMode.GuidanceMode
                exsheet.SaveAs(filepath + "\指导记录完成情况汇总.xls")
            Case DocumentMode.ThesisMode
                exsheet.SaveAs(filepath + "\毕业论文完成情况汇总.xls")
            Case Else
                MsgBox("Select Error!", MsgBoxStyle.Exclamation, "Excel保存")
                Return False
        End Select

        exsheet = Nothing
        exbook.Close()
        exbook = Nothing
        exapp.Quit()
        exapp = Nothing

        Return True
    End Function
End Class