Lotus Notes, Sametime and Java working together

Sametime and Domino solutions
Lotus Notes/Domino and Sametime software made easy

Get notification about


Export Web view to Excel using LotusScript

This LotusScript agent exports all documents from a Domino view to Excel. It can be triggered from web browser so the user can immediately see the results in his Excel application.
User can specify what view he wants to export by appending a view name as an URL parameter to the agent.
Set agent to be run as "Web User" in order to prevent accessing reader-protected documents unavailable to regular users.

Sub Initialize
'This agent export any view to excel using web browser interface.

'To select a default view to export, change the view name in the code below.
'You can also provide view name as parameter to the agent. As the agent runs
'with authority of it's signer make sure the agent can not access the views which
'are not supposed to be exported. Use this function at own risk or remove it, so
'the agent always uses the default view.
'Example of appending a view name parameter to the agent:

'Warning: to avoid errors on columns containing multivalue fields, use following
'column formula to combine multivalues: @Implode(MVField;",")
'Columns containing icons are not exported.
'Columns used for counting totals are not exported.

'Copyright Botstation (www.botstation.com)

Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim colcounter As Integer, currentcol As Integer
Dim expview As String, filename As String
Dim colnames
Dim colnumbers

Redim colnumbers (0 To 100) As Integer
Set db=session.currentdatabase
Set doc=session.DocumentContext

filename="Excel "+db.Title+" "+Format(Now, "yyyy-mmm-d hh-mm")+".xls" 'file name which will be suggested to user to save the resulting Excel file

If Instr(doc.query_string_decoded(0),"expview=")=0 Then ' find out if view is supplied as URL parameter to this agent
Set view = db.GetView("ExportToExcel") ' no view supplied as parameter, use your default export view
If Instr(expview,"&")>0 Then expview=Strleft(expview,"&")
Set view = db.GetView(expview) ' get the view passed as parameter to the agent
End If
If view Is Nothing Then
Print "Export Error - Export view does not exist"
Exit Sub
End If
currentcol=0 'starting column number. 0=first column
Forall c In view.Columns
If c.isIcon<>True Then ' do not include icon columns
If c.Formula<>"""1""" And c.Formula<>"1" Then 'do not include columns which are counting docs (Total)
colnumbers(colcounter)=currentcol 'put into array what columns are valid for exporting
colcounter=colcounter+1 'total nr of valid columns
End If
End If
currentcol=currentcol+1 'increment counter of columns with 1
End Forall

If colcounter=0 Then
Print "Export Error - No valid columns to export"
Exit Sub
End If
Redim Preserve colnumbers (0 To colcounter-1) As Integer 'remove unused array values
' ---- output browser header ------
Print |Content-Type:application/vnd.ms-excel| 'You can also try with "application/octet-stream"
Print |Content-Disposition: Attachment; filename="|+filename+|"| 'Triggers the save/open browser prompt instead of embedding the spreadsheet in the browser
Print ""
' --------------------------------
Print "<table border=1>" 'set border to 1 pixel, otherwise Excel shows no cell separators.
Print tmp 'output view column headers
Set doc = view.GetFirstDocument
While Not ( doc Is Nothing ) 'process all documents in view
tmp=tmp+"<tr>" 'begin a new table row
Forall colnr In colnumbers 'process all previously validated columns
colvalue= doc.ColumnValues(colnr)
tmp=tmp+"<td>"+CStr(colvalue)+" </td>" 'make a new table cell
End Forall
tmp=tmp+"</tr>" 'close row

Print tmp 'output values to browser after each row/document

Set doc = view.GetNextDocument (doc)
Print "</table>"

End Sub

© Copyright 2004. Botstation