將view的畫面匯出成Excel

Sub Initialize
    Dim session As New NotesSession
    Dim cdoc As NotesDocument
    Dim doc As NotesDocument
    Dim ddoc As NotesDocument
    Dim strr As String
    Dim db As NotesDatabase
    Dim sdate As String
    Dim edate As String
    Dim htmlstr As String  '表格的html
    Dim dateTime As New NotesDateTime("1911/01/01")
    Dim docColl As NotesDocumentCollection
    Dim wdoc As NotesDocument
    Dim view As notesview


    Set db = session.CurrentDatabase
    Set view = db.GetView("vProcessByEmpId")
    Set wdoc = db.GetProfileDocument("fWinDoc")
    On Error Goto h
    Set cdoc = session.DocumentContext

    sdate =cdoc.datStart(0)
    edate =cdoc.datEnd(0)

    If cdoc.DataForm(0) <> "All" Then
        strr = {Form = "}+cdoc.DataForm(0)  + {"}
    Else
        strr = {(Form = "LeaveDoc" | Form = "CancelLeaveFlow") }
    End If

    If cdoc.DataStatus(0)="" Or  cdoc.DataStatus(0) ="All" Then
        strr = strr + { & state != "Draf" & state != "Dept99" & DeleteFlag!="1" }
    Else
        strr = strr + { & state = "}+cdoc.DataStatus(0)+{" & DeleteFlag!="1"}
    End If

    If cdoc.DataEmpno(0)<>"" Then
        strr = strr + {& ApplyEmpNo="}+cdoc.DataEmpno(0)+{"}
    End If

    If cdoc.DataType(0)<>"" Then
        strr = strr + {& VacationTypeList="}+cdoc.DataType(0)+{"}
    End If

    If cdoc.DataDept(0)<>"" Then
        strr = strr + {& ApplyDept="}+cdoc.DataDept(0)+{"}
    End If

    If sdate="" And edate="" Then
    Elseif edate="" Then
        strr = strr + {& @TextToTime("}+sdate+{")<= datStart }
    Elseif sdate=""  Then
        strr = strr + {& datEnd<= @TextToTime("}+edate+{")}
    Else
        strr = strr + {& @TextToTime("}+sdate+{")<= datStart & datEnd<= @TextToTime("}+edate+{")}
    End If

    If  cdoc.DataSign(0)="1" Then
        Msgbox "excel"
        strr = strr+{ & Author="}+wdoc.GM(0)+{"}
    End If

    Msgbox STRR
    Set docColl = db.Search(strr,dateTime,0)
    Msgbox Cstr(doccoll.Count)
    Set doc = docColl.GetFirstDocument


    If  Cstr(doccoll.Count)<>"0" Then
        Print |Content-Disposition:attachment;filename=LeaveList.xls|
        Print |Content-type:application/vnd.ms-excel;charset=utf-8|
        If cdoc.DataSign(0)="1" Then
            Print |<table id='bgTab' width='805' align='center' border='1' cellspacing='0' cellpadding='0'><tr><td  align='center'>申請日期</td><td align='center'>單位名稱</td><td  align='center'>員工編號</td><td  align='center'>員工姓名</td><td  align='center'>職稱</td><td  align='center'>假別</td><td  align='center'>開始時間</td><td  align='center'>結束時間</td><td  align='center'>總時數</td><td  align='center'>請假原因(備註)</td><td  align='center'>主管簽核紀錄</td><td  align='center'>呈核總經理</td></tr>|
        Else
            Print |<table id='bgTab' width='805' align='center' border='1' cellspacing='0' cellpadding='0'><tr><td  align='center'>申請日期</td><td  align='center'>申請狀態</td><td align='center'>單位代碼</td><td align='center'>單位名稱</td><td  align='center'>員工編號</td><td  align='center'>員工姓名</td><td  align='center'>假別代碼</td><td  align='center'>假別</td><td  align='center'>開始時間</td><td  align='center'>結束時間</td><td  align='center'>總時數</td><td  align='center'>人資簽核日期</td><td  align='center'>請假原因(備註)</td></tr>|
        End If
        While Not doc Is Nothing
            Msgbox Format(doc.CreateDate(0),"yyyy/mm/dd")
            Msgbox Format(doc.applydeptno(0))
            Msgbox Format(doc.applyEmpno(0))
            Msgbox Format(doc.VacationTypeList(0))
            Msgbox Format(Cstr(doc.datstart(0)))
            Msgbox Format(Cstr(doc.datend(0)))
            Msgbox Format(doc.leavehours(0))
            Msgbox Format(Format(doc.CreateDate(0),"yyyy/mm/dd"))
            signcommdat = ""
            If doc.state(0) = "Complet" Then signcommdat = Cstr(doc.LAppDate(0))
            If cdoc.DataSign(0)="1" Then
                tmpcomment=Evaluate({@replacesubstring(SignComm;@newline;"<br>")},doc)
                Print |<tr><td  align='center'>|+Format(doc.CreateDate(0),"yyyy/mm/dd")+|</td><td  align='center'>|+doc.applydept(0)+|</td><td  align='center'>|+doc.applyEmpno(0)+|</td><td  align='center'>|+doc.applycname(0)+|</td><td  align='center'>|+doc.ApplyTitle(0)+|</td><td  align='center'>|+doc.VacationType(0)+|</td><td  align='center'>|+Cstr(doc.datstart(0))+" "+doc.FromTimeS0(0)+":"+doc.FromTimeS1(0)+|</td><td  align='center'>|+Cstr(doc.datend(0))+" "+doc.ToTimeE0(0)+":"+doc.ToTimeE1(0)+|</td><td  align='center'>|+Cstr(doc.leavehours(0))+|</td><td  align='center'>|+doc.FormMemo(0)+|</td><td  align='center'>|+tmpcomment(0)+|</td><td  align='center'>|+""+|</td></tr>|
                If doc.othapply(0)<>"" Then
                    Msgbox Cstr(Ubound(doc.othapply))
                    For i = 0 To Ubound(doc.othapply)
                        Set ddoc = view.GetDocumentByKey(doc.othapply(i),True)
                        If Not ddoc Is Nothing Then
                            tmpcomment=Evaluate({@replacesubstring(SignComm;@newline;"<br>")},ddoc)
                            Print |<tr><td  align='center'>|+Format(ddoc.CreateDate(0),"yyyy/mm/dd")+|</td><td  align='center'>|+ddoc.applydept(0)+|</td><td  align='center'>|+ddoc.applyEmpno(0)+|</td><td  align='center'>|+ddoc.applycname(0)+|</td><td  align='center'>|+ddoc.ApplyTitle(0)+|</td><td  align='center'>|+ddoc.VacationType(0)+|</td><td  align='center'>|+Cstr(ddoc.datstart(0))+" "+ddoc.FromTimeS0(0)+":"+ddoc.FromTimeS1(0)+|</td><td  align='center'>|+Cstr(ddoc.datend(0))+" "+ddoc.ToTimeE0(0)+":"+ddoc.ToTimeE1(0)+|</td><td  align='center'>|+Cstr(ddoc.leavehours(0))+|</td><td  align='center'>|+ddoc.FormMemo(0)+|</td><td  align='center'>|+tmpcomment(0)+|</td><td  align='center'>|+"連續假單"+|</td></tr>|
                        End If
                    Next
                End If
            Else
                If doc.Form(0)="CancelLeaveFlow" Then
                    Print |<tr><td  align='center'>|+Format(doc.CreateDate(0),"yyyy/mm/dd")+|</td><td  align='center'>銷假</td><td  align='center'>|+doc.applydeptno(0)+|</td><td  align='center'>|+doc.applydept(0)+|</td><td  align='center'>|+doc.applyEmpno(0)+|</td><td  align='center'>|+doc.applycname(0)+|</td><td  align='center'>|+doc.VacationTypeList(0)+|</td><td  align='center'>|+doc.VacationType(0)+|</td><td  align='center'>|+Cstr(doc.datstart(0))+" "+doc.FromTimeS0(0)+":"+doc.FromTimeS1(0)+|</td><td  align='center'>|+Cstr(doc.datend(0))+" "+doc.ToTimeE0(0)+":"+doc.ToTimeE1(0)+|</td><td  align='center'>|+Cstr(doc.leavehours(0))+|</td><td  align='center'></td><td  align='center'>|+doc.FormMemo(0)+|</td></tr>|
                Else
                    Print |<tr><td  align='center'>|+Format(doc.CreateDate(0),"yyyy/mm/dd")+|</td><td  align='center'>|+doc.DocStatus(0)+|</td><td  align='center'>|+doc.applydeptno(0)+|</td><td  align='center'>|+doc.applydept(0)+|</td><td  align='center'>|+doc.applyEmpno(0)+|</td><td  align='center'>|+doc.applycname(0)+|</td><td  align='center'>|+doc.VacationTypeList(0)+|</td><td  align='center'>|+doc.VacationType(0)+|</td><td  align='center'>|+Cstr(doc.datstart(0))+" "+doc.FromTimeS0(0)+":"+doc.FromTimeS1(0)+|</td><td  align='center'>|+Cstr(doc.datend(0))+" "+doc.ToTimeE0(0)+":"+doc.ToTimeE1(0)+|</td><td  align='center'>|+Cstr(doc.leavehours(0))+|</td><td  align='center'>|+signcommdat+|</td><td  align='center'>|+doc.FormMemo(0)+|</td></tr>|
                End If
            End If
            Set doc = docColl.GetNextDocument(doc)
        Wend
    Else
        Print |Content-Disposition:attachment;filename=LeaveList.xls|
        Print |Content-type:application/vnd.ms-excel;charset=big5|
        'Print |Content-type:application/vnd.ms-excel;charset=utf-8|
        If cdoc.DataSign(0)="1" Then
            Print |<table id='bgTab' width='805' align='center' border='1' cellspacing='0' cellpadding='0'><tr><td  align='center'>申請日期</td><td align='center'>單位名稱</td><td  align='center'>員工編號</td><td  align='center'>員工姓名</td><td  align='center'>職稱</td><td  align='center'>假別</td><td  align='center'>開始時間</td><td  align='center'>結束時間</td><td  align='center'>總時數</td><td  align='center'>請假原因(備註)</td><td  align='center'>主管簽核紀錄</td><td  align='center'>呈核總經理</td></tr>|
        Else
            Print |<table id='bgTab' width='805' align='center' border='1' cellspacing='0' cellpadding='0'><tr><td  align='center'>申請日期</td><td  align='center'>申請狀態</td><td align='center'>單位代碼</td><td align='center'>單位名稱</td><td  align='center'>員工編號</td><td  align='center'>員工姓名</td><td  align='center'>假別代碼</td><td  align='center'>假別</td><td  align='center'>開始時間</td><td  align='center'>結束時間</td><td  align='center'>總時數</td><td  align='center'>人資簽核日期</td><td  align='center'>請假原因(備註)</td></tr>|
        End If
    End If

h:
    Msgbox "Error: "&Cstr(Err)&" : "&Error$&" at line: "& Cstr(Erl)

End Sub