New Response

« Return to the main article

You are replying to:

    • avatar
    • leon
    • Posted on Sun 10 Nov 2002


    We ran into a wall recently with exporting views to excel. Then we noticed your code:

    'Generate Report|report.xls:

    Option Public Option Explicit %INCLUDE "lsconst.lss" %INCLUDE "lsxbeerr.lss"

    Sub Initialize

    'This agent creates a excel file and dumps it directly to the web browser. 'The browser interprets it as a file. 'The script runs line by line, first taking the view column headers, then 'all the data itself. Please make sure you update the view name. 'It is currently ExportView

    Dim session As New NotesSession Dim db As NotesDatabase Dim v As NotesView Dim docX As NotesDocument Dim col As Integer Dim lineitem As String Dim View As String Set db = session.CurrentDatabase

    'Sets the download to use Excel Print |Content-Type:application/vnd.ms-excel| 'Triggers the save/open prompt instead of embedding the spreadsheet in the browser Print |Content-Disposition:Attachment; filename="Report.xls"| On Error Goto errorHandler

    View="ExportView" Set v = db.GetView(View$) Call v.refresh col=1 Print |<Table border>| lineitem="" Forall vColumn In v.Columns If col < 1 Then Elseif col=1 Then lineitem=|<th align="center"><FONT SIZE=3 COLOR="000000">|+vColumn.Title Else lineitem=lineitem+|<th align="center"><FONT SIZE=3 COLOR="0000FF">|+vColumn.Title End If col=col+1 End Forall lineitem=lineitem

    Print lineitem Set docX=v.GetFirstDocument lineitem="" While Not docX Is Nothing col=1 Forall cValue In docX.ColumnValues If col=1 Then lineitem=|<tr>| End If If cValue="" Then 'blank value still formats the cell lineitem=lineitem+|<td>&nbsp;</td>| Elseif Isdate(cValue) Then 'date format lineitem=lineitem+|<TD ALIGN="right" STYLE="vnd.ms-excel.numberformat:dd-mmm-yyyy">|+cValue+|</td>| Elseif Isnumeric(cValue) Then If (v.columns(col-1).numberformat=3) Then 'currency format lineitem=lineitem+|<td ALIGN="right" STYLE="vnd.ms-excel.numberformat:$#,##0.00">|+cValue+|</td>| Else 'other number format lineitem=lineitem+|<td ALIGN="right">|+cValue+|</td>| End If Else 'Plain text format lineitem=lineitem+|<td>|+cValue+|</td>| End If

    col=col+1 End Forall Print lineitem+|</tr>| Set docX=v.GetNextDocument(docX) Wend Print |</table>| Exit Sub

    errorHandler: Print "There has been an error " & Err() & " : " & Error() & " - On Line "+Cstr (Erl) & Chr$(13) Exit Sub

    End Sub

    We tried this but it didn't work. The problem we have is that two of our columns have multi values in them. We are both new to notes development and cannot seem to find any step by step documentation/code for dealing with this. Your code works fine apart from the two columns. How would you incorporate a multivalue handler in your routine? Can you please help?

    Can you please reply to BOTH cc addresses?

    Many Thanx,


Your Comments